利用poi将Excel表格中的数据填充到Word模板中

1.poi相关依赖

        <dependency>
            <groupId>com.deepoove</groupId>
            <artifactId>poi-tl</artifactId>
            <version>1.9.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

2.读取Excel数据

package com.example.demo.hutoolTest;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * 读取Excel数据的工具类
 * @Author JCB
 * @Date  2021/1/29
 **/
@Slf4j
public class ReadExcelUtil {

    /**
     * 读取某区域数据
     * @Author JCB
     * @Date  2021/1/29
     **/
    public static List<List> getAreaFromExcel(String path, String sheet, int startRow, int endRow) throws Exception {
        File xlsx = new File(path);
        Workbook workbook = WorkbookFactory.create(xlsx);
        Sheet sheet1 = workbook.getSheet(sheet);
        //Sheet sheet1 = workbook.getSheetAt(sheet);
        List<List> totalList = new ArrayList<>();
        for (int i = startRow-1; i < endRow-startRow+1; i++) {
            Row row1 = sheet1.getRow(i);
            List<String> list = new ArrayList<>();
            int rowNum = row1.getLastCellNum();
            for (int j = 0; j < rowNum; j++) {
                list.add(getCellValueByCell(row1.getCell(j)));
            }
            totalList.add(list);
        }
        workbook.close();
        log.info("文件名：{}，sheet：{}，startRow：{}，endRow：{}，取值：{}", path, sheet, startRow, endRow, totalList.toString());
        return totalList;
    }
    /**
     * 读取某行数据
     * @Author JCB
     * @Date  2021/1/29
     **/
    public static List<String> getRowFromExcel(String path, int sheet, int row) throws Exception {
        File xlsx = new File(path);
        Workbook workbook = WorkbookFactory.create(xlsx);
        Sheet sheet1 = workbook.getSheetAt(sheet);
        Row row1 = sheet1.getRow(row);
        List<String> list = new ArrayList<>();
        int rowNum = row1.getLastCellNum();
        for (int i = 0; i < rowNum; i++) {
            list.add(getCellValueByCell(row1.getCell(i)));
        }
        log.info("文件名：{}，sheet：{}，row：{}，取值：{}", path, sheet, row, list.toString());
        return list;
    }

    /**
     * 读取某单元格数据
     * @Author JCB
     * @Date  2021/1/29
     **/
    public static String getCellFromExcel(String path, int sheet, int row, int col) throws Exception {
        File xlsx = new File(path);
        Workbook workbook = WorkbookFactory.create(xlsx);
        Sheet sheet1 = workbook.getSheetAt(sheet);
        Row row1 = sheet1.getRow(row);
        String cell = getCellValueByCell(row1.getCell(col));
        log.info("文件名：{}，sheet：{}，row：{}，col：{}，取值：{}", path, sheet, row, col, cell);
        return cell;
    }

    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();

        // 以下是判断数据的类型
        switch (cellType) {
            case NUMERIC: // 数字

                if (CellType.NUMERIC == cell.getCellType()) {//判断单元格的类型是否则NUMERIC类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat(
                                "yyyy-MM-dd HH:mm");
                        cellValue = formater.format(date);
                    } else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;

        }
        return cellValue;
    }

    public static void main(String[] args) throws Exception{
        String path = "D:/test/数据.xlsx";
        HashMap<String, Object> map;
        List<List> areaFromExcel = getAreaFromExcel(path, "503", 8, 50);
        for (List row : areaFromExcel) {
            map = getMapByList(row);
            WriteWordUtil.templateWrite2("D:/test/模板.docx", map, "D:/test/data/" + map.get("name")+".docx");
        }
    }

    /**
     * 根据某行数据生成需要填充数据的map
     * @Author JCB
     * @Date  2021/1/29
     **/
    private static HashMap<String, Object> getMapByList(List<String> row) {
        HashMap<String, Object> map = new HashMap<>();
        String name = row.get(1);
        String h = row.get(3);
        String w = row.get(4);
        String l = row.get(6);
        String r = row.get(7);
        String c = row.get(8);
        map.put("name", name);
        map.put("h", h);
        map.put("w", w);
        map.put("l", l);
        map.put("r", r);
        map.put("c", c);
        return map;
    }
}

4.写入Word模板

package com.example.demo.hutoolTest;

import com.deepoove.poi.XWPFTemplate;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * 填充word模板数据的工具类
 * @Author JCB
 * @Date  2021/1/29
 **/
public class WriteWordUtil {

    /**
     * word占位用{{object}}比较完美可以填充图片
     * @param filePath
     * @param params
     * @param outFilePath
     * @return
     * @throws Exception
     */
    public static String templateWrite2(String filePath, Map<String, Object> params,String outFilePath)throws Exception{
        XWPFTemplate template = XWPFTemplate.compile(filePath).render(params);
        FileOutputStream out = new FileOutputStream(outFilePath);
        template.write(out);
        out.flush();
        out.close();
        template.close();
        return "";
    }
    public static void main(String[] args) throws Exception {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("name", "小小");//
        params.put("c", "1111");//
        params.put("h", "175");//
        params.put("w", "60");//
        params.put("l", "5.0");//
        params.put("r", "5.1");//
        templateWrite2("D:/test/2.docx", params, "D:/test/"+params.get("name")+".docx");
    }
}