Apache POI 导出excel方式

一、创建工作簿

Workbook workbook = new XSSFWorkbook();

二、创建sheet对象

 Sheet sheet = workbook.createSheet("Sheet1");

三、设置样式

1、设置每一列的宽度

sheet.setColumnWidth(0, 25 * 256); // 25 characters * 256
sheet.setColumnWidth(1, 15 * 256); // 30 characters * 256
sheet.setColumnWidth(2, 30 * 256);
...

2、创建样式对象

CellStyle cellStyle = workbook.createCellStyle();

3、设置文本样式

// 设置水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置换行
cellStyle.setWrapText(true);
//设置边框样式
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);

四、应用标题行样式（数据插入excel的方式）

//创建第0行
Row titleRow = sheet.createRow(0);
//对表头的每一列设置样式
for (int i = 0; i < obj.size(); i++) {
      String s1 = obj.get(i);
      //创建第一行的每一列并设置样式
      Cell cell = titleRow.createCell(i);
      cell.setCellValue(s1);
      cell.setCellStyle(cellStyle);
}

五、合并单元格

//合并第0~0行 第0~15列的单元格  起始行，结束行，起始列，结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));

六、输出到文件

//创建文件输出流
FileOutputStream fileOut = new FileOutputStream(filePath);
//写入
workbook.write(fileOut);

七、关闭流

 try {
       if (workbook != null) {
            workbook.close();
       }
     } catch (Exception e) {
                // 处理异常
     }

八、案例

private void writeQuestionExcel(String filePath, List<List<String>> rows) {
        File file = FileUtil.safeInstance(filePath);
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 记录当前行数;
        int row1 = 1;
        try {
            // 设置excel样式
            sheet.setColumnWidth(0, 25 * 256); // 25 characters * 256
            sheet.setColumnWidth(1, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(2, 30 * 256); // 15 characters * 256
            sheet.setColumnWidth(3, 15 * 256); // 15 characters * 256
            sheet.setColumnWidth(4, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(5, 30 * 256); // 30 characters * 256
            sheet.setColumnWidth(6, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(7, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(8, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(9, 40 * 256); // 30 characters * 256
            sheet.setColumnWidth(10, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(11, 20 * 256); // 30 characters * 256
            sheet.setColumnWidth(12, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(13, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(14, 15 * 256); // 30 characters * 256
            sheet.setColumnWidth(15, 35 * 256); // 30 characters * 256

            CellStyle cellStyle = workbook.createCellStyle();
            // 设置居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            // 设置excel标题
            List<String> obj = new ArrayList<String>();
            if (rows.size() > 0) {
                obj = rows.get(0);
            }
            Row titleRow = sheet.createRow(0);
            for (int i = 0; i < obj.size(); i++) {
                String s1 = obj.get(i);
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(s1);
                cell.setCellStyle(cellStyle);
                // 合并第一行标题
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));
            }

            // 设置excel数据行
            for (int j = 1; j < rows.size(); j++) {
                Row dataRow = sheet.createRow(row1);
                int m = 0;
                obj = rows.get(j);
                for (; m < obj.size(); m++) {
                    String every = obj.get(m);
                    Cell cell = dataRow.createCell(m);
                    cell.setCellValue(every);
                    cell.setCellStyle(cellStyle);
                }
                row1++;
            }

            try (FileOutputStream fileOut = new FileOutputStream(file)) {
                workbook.write(fileOut);
            }

        } catch (IOException e) {
            // 处理异常
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e) {
                // 处理异常
            }
        }
    }