excel和word的读写

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);

    }
}