首页 > 其他 > 详细

POI excel导出

时间:2018-01-19 20:13:10      阅读:283      评论:0      收藏:0      [点我收藏+]

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

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);
    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));
    createExcel(workbook, targetPath);
}

 

——结果展示

技术分享图片

 

POI excel导出

原文:https://www.cnblogs.com/antlord/p/8318010.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!