excel和word的读写

文章目录

excel依赖读入和导出 word导出（基于freamwork）依赖编辑word模板，另存为xml（表格数据需要手动修改xml文件）测试代码

100000 行级别数据的 Excel 导入优化之路

excel

依赖

读入和导出

public class Excel { public static void main(String[] args) throws Exception { // 读取excel List<Map<String, String>> list = readExcel("E:/excelTest.xlsx"); list.forEach(cell -> System.out.println(cell)); // 导出excel String[] head = new String[]{"代偿编号", "姓名", "性别", "日期", "身份证号"}; OutputStream out = new FileOutputStream("E:/excel.xlsx"); writeExcel(list, head, out); } /** * 导出excel数据 * * @param list 写出的表格内容 * @param head 表头 * @param out 输出流 * @throws Exception */ public static void writeExcel(List<Map<String, String>> list, String[] head, OutputStream out) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("学生信息"); // 表头信息 XSSFRow firstRow = sheet.createRow(0); for (int i = 0; i < head.length; i++) { XSSFCell cell = firstRow.createCell(i); cell.setCellValue(head[i]); } // 数据行 for (int i = 0; i < list.size(); i++) { XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < head.length; j++) { XSSFCell cell = row.createCell(j); cell.setCellValue(list.get(i).get(head[j])); } } // 写出表格 workbook.write(out); } /** * 读excel数据 * * @param path * @return * @throws Exception */ public static List<Map<String, String>> readExcel(String path) throws Exception { List<Map<String, String>> list = new ArrayList<>(); InputStream inputStream = new FileInputStream(path); Workbook workbook = WorkbookFactory.create(inputStream); // 得到表 Sheet sheet = workbook.getSheet("Sheet2"); // 得到菜单行 Row firstRow = sheet.getRow(0); // 得到总列数 int physicalNumberOfCells = firstRow.getPhysicalNumberOfCells(); // 得到总行数 int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); // 遍历读取每个单元格的数据 for (int i = 1; i < physicalNumberOfRows; i++) { Map<String, String> map = new HashMap<>(); Row row = sheet.getRow(i); for (int j = 0; j < physicalNumberOfCells; j++) { Cell cell = row.getCell(j); int cellType = cell.getCellType(); String val = ""; if (cellType == Cell.CELL_TYPE_STRING) {// 判断cell数据类型是否是string val = cell.getStringCellValue(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) {// 判断cell数据类型是否是date Date dateCellValue = cell.getDateCellValue(); val = dateFormat(dateCellValue); } else { // 判断cell数据类型是否是double double numericCellValue = cell.getNumericCellValue(); val = ((Integer)new Double(numericCellValue).intValue()).toString(); } } map.put(firstRow.getCell(j).getStringCellValue(), val); } list.add(map); } return list; } // 日期格式化 private static String dateFormat(Date dateCell) { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); return format.format(dateCell); } }

word导出（基于freamwork）

依赖

编辑word模板，另存为xml（表格数据需要手动修改xml文件）

测试代码

public class Word { public static void main(String[] args) throws Exception { Configuration configuration = new Configuration(); // 加载并配置模板内容 configuration.setDefaultEncoding("UTF-8"); configuration.setClassForTemplateLoading(Word.class,"/com/javasm/word"); Template template = configuration.getTemplate("购房合同.xml"); Map<String,String> map = new HashMap<>(); map.put("username","卡卡西"); map.put("IDCard","610548199604051412"); map.put("price","4836452.00"); map.put("date","2020.8.11"); // 输出流 Writer out = new FileWriter("E:/购房合同.docx"); template.process(map,out); } }