【Java SE】POI操作文件

POI Jar

Apache POI Common
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

Apache POI API Based On OPC and OOXML Schemas
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

Apache POI
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
</dependency>

各个Jar的作用

Component	Application type	Maven artifactId	Notes
POIFS	OLE2 Filesystem	poi	Required to work with OLE2 / POIFS based files
HPSF	OLE2 Property Sets	poi
HSSF	Excel XLS	poi	For HSSF only, if common SS is needed see below
HSLF	PowerPoint PPT	poi-scratchpad
HWPF	Word DOC	poi-scratchpad
HDGF	Visio VSD	poi-scratchpad
HPBF	Publisher PUB	poi-scratchpad
HSMF	Outlook MSG	poi-scratchpad
OpenXML4J	OOXML	poi-ooxml plus one of poi-ooxml-schemas, ooxml-schemas	Only one schemas jar is needed, see below for differences
XSSF	Excel XLSX	poi-ooxml
XSLF	PowerPoint PPTX	poi-ooxml
XWPF	Word DOCX	poi-ooxml
Common SS	Excel XLS and XLSX	poi-ooxml	WorkbookFactory and friends all require poi-ooxml, not just core poi

 

说明：

HSSFWorkbook类来处理xls，XSSFWorkbook类来处理xlsx，它们都继承接口Workbook

Sheet名字最多31个字符，* / \不能用

示例：

 

/**
 * 导出数据
 *
 * @return
 */
@GetMapping("/exportData")
@ApiOperation(value = "导出数据", notes = "导出数据")
public void exportData(HttpServletRequest request, HttpServletResponse response) throws Exception {
    String filename = new String("导出数据".getBytes("gb2312"), "ISO8859-1") + DatePattern.PURE_DATETIME_MS_FORMAT.format(new Date()) + ".xls";
    response.setHeader("Content-Disposition", "attachment;filename=" + filename);
    response.setContentType(request.getServletContext().getMimeType(filename));
    OutputStream output = response.getOutputStream();
    errorSensitiveScanResultService.exportData(result, output);
}

/**
 * 导出数据
 *
 * @param result
 * @param output
 */
@Override
public void exportData(OutputStream output) {
    try {
        // 测试数据
        List<String> typeList = new ArrayList<>();
        typeList.add("语文");
        typeList.add("数学");
        List<String> yuwen = new ArrayList<>();
        yuwen.add("姓名,成绩");
        yuwen.add("张三,82");
        yuwen.add("李四,95");
        List<String> shuxue = new ArrayList<>();
        shuxue.add("姓名,成绩");
        shuxue.add("张三,99");
        shuxue.add("李四,81");
        Map<String, List<String>> score = new HashMap<>();
        score.put("语文", yuwen);
        score.put("数学", shuxue);
        // 创建xls格式工作簿
        HSSFWorkbook wb = new HSSFWorkbook(); // XSSFWorkbook 创建xlsx格式工作簿
        try {
            for (String name : typeList) {
                // 创建工作表
                HSSFSheet sheet = wb.createSheet(name);
                List<String> rowList = score.get(name);
                for (int rowIndex = 0; rowIndex < rowList.size(); rowIndex++) {
                    // 创建行
                    HSSFRow row = sheet.createRow(rowIndex);
                    List<String> colList = Arrays.asList(rowList.get(rowIndex).split(","));
                    for (int colIndex = 0; colIndex < colList.size(); colIndex++) {
                        // 创建单元格
                        HSSFCell cell = row.createCell(colIndex);
                        // 设置单元格值
                        cell.setCellValue(colList.get(colIndex));
                    }
                }
            }
        } finally {
            wb.write(output);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

设置边框

// 创建xls格式工作簿
HSSFWorkbook wb = new HSSFWorkbook(); // XSSFWorkbook 创建xlsx格式工作簿
// 定义样式
HSSFCellStyle style = wb.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 设置样式
cell.setCellStyle(style);

 

hutool到处excel

    /**
     * 数据导出
     *
     * @param outputStream
     */
    public void exportData(OutputStream outputStream) {
        List<Department> list = list();
        if (CollectionUtil.isEmpty(list)) {
            return;
        }
        List<String> nameList = new ArrayList<>();
        nameList.add("部门名称");
        nameList.add("部门简称");
        nameList.add("信息填报名称");
        nameList.add("部门编码");
        nameList.add("区划编码");
        ExcelWriter excelWriter = new ExcelWriter();
        excelWriter.writeRow(nameList);
        setCellStyle(excelWriter, 0, 0);
        setCellStyle(excelWriter, 1, 0);
        setCellStyle(excelWriter, 2, 0);
        setCellStyle(excelWriter, 3, 0);
        setCellStyle(excelWriter, 4, 0);
        for (Department department : list) {
            List<Object> valueList = new ArrayList<>();
            valueList.add(department.getName());
            valueList.add(department.getShortName());
            valueList.add(department.getFillInName());
            valueList.add(department.getCode());
            valueList.add(department.getRegionCode());
            excelWriter.writeRow(valueList);
        }
        for (int i = 0; i < 5; i++) {
            excelWriter.setColumnWidth(i, 20 * 2);
        }
        excelWriter.flush(outputStream);
    }

    private void setCellStyle(ExcelWriter writer, int x, int y) {
        CellStyle cellStyle = writer.createCellStyle(x, y);
        // 顶边栏
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        // 右边栏
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        // 底边栏
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        // 左边栏
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        // 填充前景色
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }