POI excel導出

******************************************************************************************

excel表格導出，使用POI實現

******************************************************************************************

實現導出步驟

——配置導出excel模板，推薦使用03版xls格式，可以相容高級版本

——處理導出數據List<Map<String, String>> 

private List<Map<String, String>> getData(){
    List<Map<String, String>> data = new ArrayList<>();
    Map<String, String> map = null;
    for (int i = 0; i < 5; i++) {
        map = new HashMap<String, String>();
        map.put("listNo", i + "");
        map.put("userName", "name" + i);
        map.put("userAge", "" + (i + 20));
        data.add(map);
    }
    return data;
}

——獲取模板xls文件，通過輸入流讀到HSSFWorkbook 對象中

private HSSFWorkbook workbookTemplate() {
    File file = new File("f:/etom/pmms/src/test/resources/bdRoute.xls");
    InputStream is = null;
    HSSFWorkbook workbook = null;
    try {
        is = new FileInputStream(file);
        workbook = new HSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }finally {
        if (null != is) {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }
    return workbook;
}

——通過頁簽名獲取頁簽

private HSSFSheet getSheet(HSSFWorkbook workbook) {
    String sheetName = "用戶信息";
    HSSFSheet sheet = workbook.getSheet(sheetName);
    if (null != sheet) {
        return sheet;
    }
    return null;
}

——獲取規則，如${userName}

private String getValue(HSSFCell cell){
    int type = cell.getCellType();
    switch (type) {
        case Cell.CELL_TYPE_NUMERIC:
            return Double.toString(cell.getNumericCellValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        default:
            return "";
    }
}

——設置規則，如${userName}

private void setCellRule(HSSFRow firstDataRow, HSSFRow newRow) {
    HSSFCell cellInRule = null;
    HSSFCell newCell = null;
    for (int i = 0, cellNum = firstDataRow.getLastCellNum(); i < cellNum; i++) {
        cellInRule = firstDataRow.getCell(i);
        newCell = newRow.createCell(i);
        HSSFCellStyle style = cellInRule.getCellStyle();
        newCell.setCellStyle(style);
        String rule = getValue(cellInRule);
        newCell.setCellValue(rule);
    }
}

——設置頁簽模板，將內容行複製，行數與數據data的條數一致

// loop複製行的根據，rowCount複製多少行，上面截圖中，複製行是從行號為4開始
private void setSheet(HSSFSheet sheet, int loop, int rowCount) {
    HSSFRow newRow = null;
    HSSFRow firstDataRow = sheet.getRow(loop - 1);
    for (int i = loop, maxRow = loop + rowCount; i < maxRow - 1; i++) {
        newRow = sheet.createRow(i);
        setCellRule(firstDataRow, newRow);
    }
}

——具體設置單元格內容

private void setCellValue(HSSFRow row, Map<String, String> rowData) {
    for (int i = 0, cellNum = row.getLastCellNum(); i < cellNum; i++) {
        HSSFCell cell = row.getCell(i);
        if (null == cell) {
            continue;
        }
        String oldValue = getValue(cell);
        Pattern pattern = Pattern.compile(patternRule);
        Matcher matcher = pattern.matcher(oldValue);
        if (matcher.find()) {
            String key = matcher.group(1);
            String value = rowData.getOrDefault(key, "");
            cell.setCellValue(value);
        }
    }
}

——設置行內容（通過設置單元格內容）

// 設置內容，單元的範圍是從第loop行開始，到第loop+data.size()行結束
private void setValue(HSSFSheet sheet, int loop, List<Map<String, String>> data) {
    HSSFRow row = null;
    for (int i = loop - 1, rowNum = loop + data.size(), index = 0; i < rowNum - 1; i++) {
        row = sheet.getRow(i);
        setCellValue(row, data.get(index++));
    }
}

——生成excel文件，格式為xls

private void createExcel(HSSFWorkbook workbook,String targetPath){
    File excelFile = new File(targetPath);
    OutputStream os = null;
    try {
        os = new FileOutputStream(excelFile);
        workbook.write(os);
    } catch (IOException e) {
        e.printStackTrace();
    }finally {
        if (null != os) {
            try {
                os.flush();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

——測試

@Test
public void test(){
    List<Map<String, String>> data = getData(); // 獲取導出的數據
    String templatePath = "f:/etom/pmms/src/test/resources/user.xls"; // 模板的地址
    HSSFWorkbook workbook = workbookTemplate(templatePath); // 將模板轉化為HSSFWorkbook對象
    HSSFSheet sheet = getSheet(workbook); // 獲取模板頁簽
    setSheet(sheet, 5, data.size()); // 設置頁簽
    setValue(sheet, 5, data); // 設置單元格內容
    Long timeMillis = System.currentTimeMillis(); // 以當前時間命名導出文件名
    String targetPath = String.format("f:/etom/pmms/src/test/resources/%s.xls", Long.toString(timeMillis)); // 導出文件xls地址
    createExcel(workbook, targetPath); // 導出文件
}

——結果展示

——合併單元格

private void setRegion(HSSFSheet sheet){
    int firstRow = 1; // 單元起始行
    int lastRow = 2;  // 單元格終止行
    int firstCol = 3; // 單元格起始列
    int lastCol = 4;  // 單元格終止列
    CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    sheet.addMergedRegion(cellRangeAddress);
}

——插入圖片（圖片已保存在資源中）

public void insertBatch(HSSFWorkbook workbook) throws IOException {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 獲取頁簽繪畫組件

　　// 定義位置坐標對象HSSFClientAnchor，param1：向右偏移，param2：向下偏移，param3：向左偏移，param4：向上偏移，param5：起始列，param6：起始行，param7：終點列，param8：終點行

    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 7, (short) 7, (short) 20, (short) 20); 

    ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
    File picture = new File("f:/etom/pmms/pmms/src/test/resources/user.png");
    ImageIO.write(ImageIO.read(picture), "png", baos); // 通過圖片流處理工具ImageIO將圖片讀取後保存在byte流中

    patriarch.createPicture(anchor, workbook.addPicture(baos.toByteArray(), 6)); // 繪畫組件執行插入圖片，param1：坐標對象，param2：圖片信息，param3：圖片格式 6代表png格式
    File target = new File("f:/etom/pmms/pmms/src/test/resources/userPicture.xls");
    workbook.write(new FileOutputStream(target));
}