java導(dǎo)入導(dǎo)出數(shù)據(jù)到給定多sheet的excel文件

JAVA程序?qū)崿F(xiàn)導(dǎo)入導(dǎo)出數(shù)據(jù)到給定的EXCEL文件模板中

首先創(chuàng)建一個(gè)maven項(xiàng)目，在pox文件中引入需要的包(這里3.14版本的poi會(huì)有一些問(wèn)題，在導(dǎo)入07版本的excel時(shí)xmlbeans使用2.6.0及以上版本)：

<dependency>
    <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.12</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.12</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.12</version>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>

準(zhǔn)備好需要導(dǎo)出的excel模板文件：D://a.xlxs 關(guān)鍵代碼：

//把查詢出的數(shù)據(jù)導(dǎo)出到給定的excel模板中
public byte[] exportExcel(int startIndex, String[] keys, Map<String, Object> data){
    //獲取到模板文件
    File excel = PropertiesUtil.getFile("D://a.xlxs");
    FileInputStream fis = new FileInputStream(excel);
    //創(chuàng)建excel
    XSSFWorkbook book = new XSSFWorkbook(fis);
    //keys是對(duì)應(yīng)的導(dǎo)出數(shù)據(jù)字段，map中的key列表
    for(int i=0; i<keys.length; k++){
        String[] key = keys[i];
        XSSFSheet sheet = book.getSheetAt(i);
        List<Map<String, Object>> list = data.get("list"+i);
    for(int j=0; j<list.size(); k++){
        Row row = sheet.createRow(j+startIndex);
        Map<String, Object> map = list.get(j);
                for(int k=0; k<key.length; k++){
                    String s = map.get(key[k])+"";
                    row.createCell(k).setCellValue(s);
                    }
    }
    }
}
    ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wbs.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    return os.toByteArray();
}   

//文件導(dǎo)入

public Map<String, Object> importSeries(InputStream is, String[] keys, int startIndex) throws Exception{
        Map<String, Object> map = new HashMap<String, Object>();
        Workbook book = null;
        try {
            book = new XSSFWorkbook(is);
        } catch (IOException e) {
            book = new HSSFWorkbook(is);
        }
        for(int i=0; i<keys.length; i++){
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            String[] key = keys[i];
            Sheet sheet = book.getSheetAt(i);
            int totalRows = sheet.getLastRowNum();
            for(int j = 0; j<totalRows; j++){
                Row row = sheet.getRow(startIndex+j);
                if(row == null){
                    continue;
                }
                int totalCells = row.getLastCellNum();
                Map<String, Object> map1 = new HashMap<String, Object>();
                for(int k=0; k<totalCells; k++){
                    Cell cell = row.getCell(k);
                    if(cell!=null){
                        map1.put(key[k], row.getCell(k).getStringCellValue());
                    }
                }
                list.add(map1);
            }
            map.put("list"+i, list);
        }
        return map;
    }

//導(dǎo)出到文件

private static void exportExcel(List<ZSMM_SRM_MAT_SYNC> list) throws IOException{
        List<ZSMM_SRM_MAT_SYNC> list0 = list.subList(0, 20000);
        List<ZSMM_SRM_MAT_SYNC> list1 = list.subList(20000, 40000);
        List<ZSMM_SRM_MAT_SYNC> list2 = list.subList(40000, list.size());
        List<Object> listx = new ArrayList<Object>();
        listx.add(list0);
        listx.add(list1);
        listx.add(list2);
        for(int j=0; j<3; j++){
        System.out.println(j);
            @SuppressWarnings("unchecked")
            List<ZSMM_SRM_MAT_SYNC> lists= (List<ZSMM_SRM_MAT_SYNC>) listx.get(j);
        XSSFWorkbook wb = new XSSFWorkbook();
         XSSFSheet sheet = wb.createSheet("物料主數(shù)據(jù)"+j);
         sheet.setDefaultColumnWidth(20);
            //XSSFCellStyle style = wb.createCellStyle();
            XSSFRow row = sheet.createRow(0);
           // style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("物料號(hào)"); 
            cell = row.createCell(1);  
            cell.setCellValue("物料描述");  
            cell = row.createCell(2);  
            cell.setCellValue("創(chuàng)建日期");  
            cell = row.createCell(3);  
            cell.setCellValue("刪除標(biāo)識(shí)符");  
            cell = row.createCell(4);  
            cell.setCellValue("跨工廠物料狀態(tài) ");  
            cell = row.createCell(5);  
            cell.setCellValue("物料類型");  
            cell = row.createCell(6);  
            cell.setCellValue("物料組");  
            cell = row.createCell(7);  
            cell.setCellValue("外部物料組");  
            cell = row.createCell(8);  
            cell.setCellValue("基本計(jì)量單位");  
            cell = row.createCell(9);  
            cell.setCellValue("采購(gòu)訂單的計(jì)量單位 ");  
            cell = row.createCell(10);  
            cell.setCellValue("實(shí)驗(yàn)室/設(shè)計(jì)室 ");  
            cell = row.createCell(11);  
            cell.setCellValue("毛重 ");  
            cell = row.createCell(12);  
            cell.setCellValue("凈重 ");  
            cell = row.createCell(13);  
            cell.setCellValue("重量單位 ");  
            cell = row.createCell(14);  
            cell.setCellValue("業(yè)務(wù)量 ");  
            cell = row.createCell(15);  
            cell.setCellValue("體積單位 ");  
            cell = row.createCell(16);  
            cell.setCellValue("長(zhǎng) ");  
            cell = row.createCell(17);  
            cell.setCellValue("寬 ");  
            cell = row.createCell(18);  
            cell.setCellValue("高");  
            cell = row.createCell(19);  
            cell.setCellValue("物料描述-英 ");  

             
        for (int i = 0; i < lists.size(); i++) {
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(lists.get(i).getMATNR());
            row.createCell(1).setCellValue(lists.get(i).getMAKTX());
            row.createCell(2).setCellValue(lists.get(i).getERSDA()+"");
            row.createCell(3).setCellValue(lists.get(i).getLVORM());
            row.createCell(4).setCellValue(lists.get(i).getMSTAE());
            row.createCell(5).setCellValue(lists.get(i).getMTART());
            row.createCell(6).setCellValue(lists.get(i).getMATKL());
            row.createCell(7).setCellValue(lists.get(i).getEXTWG());
            row.createCell(8).setCellValue(lists.get(i).getMEINS());
            row.createCell(9).setCellValue(lists.get(i).getBSTME());
            row.createCell(10).setCellValue(lists.get(i).getLABOR());
            row.createCell(11).setCellValue(lists.get(i).getBRGEW()+"");
            row.createCell(12).setCellValue(lists.get(i).getNTGEW()+"");
            row.createCell(13).setCellValue(lists.get(i).getGEWEI());
            row.createCell(14).setCellValue(lists.get(i).getVOLUM()+"");
            row.createCell(15).setCellValue(lists.get(i).getVOLEH());
            row.createCell(16).setCellValue(lists.get(i).getLENGTH());
            row.createCell(17).setCellValue(lists.get(i).getWIDTH());
            row.createCell(18).setCellValue(lists.get(i).getHEIGHT());
            row.createCell(19).setCellValue(lists.get(i).getMAKTX_E());
        }
                FileOutputStream out = new FileOutputStream("E://物料主數(shù)據(jù)"+j+".xlsx");
                wb.write(out);
                out.close();
                System.out.println("ok"+j);


    }