Java通过poi创建Excel文件并分页追加数据
以下的main函数，先生成一个excel文件，并设置sheet的名称，设置excel头；而后，以分页的方式，向文件中追加数据
maven依赖
<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>
代码示例
package com.**.**.**.common.utils; import org.apache.commons.io.FileUtils; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.*; /** * @author zyydd * @date 2019/3/15 15:00 */ public class ExcelUtils { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class); /** * 以下测试方法，先生成一个excel文件，并设置sheet的名称，设置excel头 * 之后，以分页的方式，向文件中增加数据 * * @param args */ public static void main(String[] args) throws IOException { String fileAbsolutePath = "D:\\test.xlsx"; Map<String, List<DataForExcel>> dataMap = initTestDataHead(); ExcelUtils.generateExcelWithManySheets(fileAbsolutePath, dataMap); for (int i = 0; i < 3; i++) { List<String[]> testData = new ArrayList<>(); for (int k = 1; k < 11; k++) { String[] oneRow = new String[6]; oneRow[0] = (i * 10 + k) + ""; oneRow[1] = "张三" + oneRow[0]; oneRow[2] = "男"; oneRow[3] = "北京市朝阳区"; oneRow[4] = "北京市大兴区"; oneRow[5] = (System.currentTimeMillis() % 10000000000L) + ""; testData.add(oneRow); } ExcelUtils.addExcel(fileAbsolutePath, 0, testData); } } private static Map<String, List<DataForExcel>> initTestDataHead() { Map<String, List<DataForExcel>> dataMap = new HashMap<String, List<DataForExcel>>(); List<DataForExcel> dataForExcelList1 = new ArrayList<DataForExcel>(); dataForExcelList1.add(new DataForExcel(0, 0, "序号")); dataForExcelList1.add(new DataForExcel(0, 1, "姓名")); dataForExcelList1.add(new DataForExcel(0, 2, "性别")); dataForExcelList1.add(new DataForExcel(0, 3, "家庭住址")); dataForExcelList1.add(new DataForExcel(0, 4, "通信地址")); dataForExcelList1.add(new DataForExcel(0, 5, "手机号")); dataMap.put("人员明细", dataForExcelList1); return dataMap; } /** * 传入数据，在指定路径下生成Excel文件 支持生成多个sheet，并为sheet命名 * * @param absolutePath 生成文件的绝对路径，例如"C:\\Users\\...\\out.xlsx" * @param dataForExcelMap key：sheet名； value：传入的数据 名字相同时会覆盖之前的文件 * @return */ public static boolean generateExcelWithManySheets(String absolutePath, Map<String, List<DataForExcel>> dataForExcelMap) { boolean flag = false; try { XSSFWorkbook workbook = new XSSFWorkbook(); for (Map.Entry<String, List<DataForExcel>> entry : dataForExcelMap.entrySet()) { XSSFSheet sheet = workbook.createSheet(entry.getKey()); List<DataForExcel> dataForExcel = entry.getValue(); Collections.sort(dataForExcel, (arg0, arg1) -> arg0.getRow().compareTo(arg1.getRow())); XSSFRow nrow = null; for (DataForExcel data : dataForExcel) { if (dataForExcel.indexOf(data) == 0 || !data.getRow().equals(dataForExcel.get(dataForExcel.indexOf(data) - 1).getRow())) { nrow = sheet.createRow(data.getRow()); } XSSFCell ncell = nrow.createCell(data.getColumn()); ncell.setCellValue(data.getValue()); } } File file = new File(absolutePath); file.createNewFile(); FileOutputStream stream = FileUtils.openOutputStream(file); workbook.write(stream); stream.close(); flag = true; } catch (IOException ie) { LOGGER.error(ie.getMessage()); } catch (Exception e) { LOGGER.error(e.getMessage()); } return flag; } /** * 向已存在的excel中追加数据 * * @param absolutePath 已存在的excel绝对路径 * @param sheetIndex sheet的序号，从0开始 * @param dataList cell数据 * @return * @throws IOException */ public static Boolean addExcel(String absolutePath, int sheetIndex, List<String[]> dataList) throws IOException { int columnsNum = dataList.get(0).length; FileInputStream fs = new FileInputStream(absolutePath); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetIndex); XSSFRow row; int lastRowNum = sheet.getLastRowNum(); FileOutputStream out = new FileOutputStream(absolutePath); for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow(++lastRowNum); String[] addOneRowData = dataList.get(i); for (int j = 0; j < addOneRowData.length; j++) { String str = addOneRowData[j]; row.createCell(j).setCellValue(str); } } setSheetStyle(sheet, columnsNum - 1); wb.write(out); out.flush(); out.close(); return true; } private static XSSFSheet setSheetStyle(XSSFSheet sheet, int columnsNum) { sheet.createFreezePane(0, 1, 0, 1); String columnRange = "A1:" + (char) (65 + columnsNum) + "1"; sheet.setAutoFilter(CellRangeAddress.valueOf(columnRange)); for (int i = 0; i <= columnsNum; i++) { sheet.autoSizeColumn(i); } return sheet; } }
package com.**.**.**.common.utils; /** * @author zyydd * @date 2019/3/15 15:00 */ public class DataForExcel { /** * excel的行号 从0开始 例如excel一个表格行号为0，列号也为0 */ private Integer row; /** * excel的列号 从0开始 例如excel一个表格行号为0，列号也为0 */ private Integer column; /** * 插入的值 */ private String value; public DataForExcel() { } public DataForExcel(Integer row, Integer column, String value) { this.row = row; this.column = column; this.value = value; } public Integer getRow() { return row; } public void setRow(Integer row) { this.row = row; } public Integer getColumn() { return column; } public void setColumn(Integer column) { this.column = column; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } }
执行截图
