POI实现模板导入并插入数据

最新推荐文章于 2023-09-27 10:26:16 发布

Connie_kinkin

最新推荐文章于 2023-09-27 10:26:16 发布

阅读量416

点赞数

文章标签： java spring spring cloud mybatis

本文链接：https://blog.youkuaiyun.com/Connie_kinkin/article/details/131973798

版权

该代码实现了从Excel模板读取并填充数据的功能。它首先根据给定的开始和结束时间创建日期，然后打开模板文件，克隆指定的sheet，设置样式，填充标题，日期和数据。最后，将处理后的Excel保存为新的文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.boyang.project.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.boyang.project.service.ExcelService;
import com.boyang.project.vo.ExcelDkVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.*;
import java.time.LocalDate;
import java.util.List;
import java.util.Objects;

@Service
public class ExcelPoiServiceImpl implements ExcelService {

    @Override
    public void exportKqb(String beginTime, String endTime, List<ExcelDkVO> datas,String title) throws IOException {
        String sheetName = "kqb";
        String[] strings = beginTime.split("-");
       // LocalDate month = LocalDate.of(2023, 7, 1);
        LocalDate month = LocalDate.of(Integer.parseInt(strings[0]), Integer.parseInt(strings[1], 1);

        InputStream fis = getClass().getResource("/templates/excel/kqb.xlsx").openStream();

        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        // 执行公式
        workbook.setForceFormulaRecalculation(true);
        int lengthOfMonth = month.lengthOfMonth();
        XSSFSheet sheet = workbook.cloneSheet(workbook.getSheetIndex("" + lengthOfMonth), sheetName);

        // 移除模板sheet
        for (int numberOfSheets = workbook.getNumberOfSheets() - 1; numberOfSheets >= 0; numberOfSheets--) {
            Sheet next = workbook.getSheetAt(numberOfSheets);
            if (!sheetName.equals(next.getSheetName())) {
                int sheetIndex = workbook.getSheetIndex(next);
                workbook.removeSheetAt(sheetIndex);
            }
        }

        Font xhFont = workbook.createFont();
        xhFont.setFontName("宋体");
        xhFont.setFontHeightInPoints((short) 14);
        CellStyle xhStyle = workbook.createCellStyle();
        setStyle(xhStyle, xhFont);

        Font xmFont = workbook.createFont();
        xmFont.setFontName("宋体");
        xmFont.setFontHeightInPoints((short) 12);
        CellStyle xmStyle = workbook.createCellStyle();
        setStyle(xmStyle, xmFont);

        Font dataFont = workbook.createFont();
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short) 11);
        CellStyle dataStyle = workbook.createCellStyle();
        setStyle(dataStyle, dataFont);

        // 头
        sheet.getRow(0).getCell(0)
                .setCellValue(title);
        // 星期
        Row weekRow = sheet.getRow(2);
        for (int i = 0; i < lengthOfMonth; i++) {
            weekRow.getCell(i + 2)
                    .setCellValue(month.withDayOfMonth(i + 1).getDayOfWeek().getValue());
        }

        // 插入数据
        int rowLine = 1;
        for (ExcelDkVO item : datas) {
            int colLine = 0;
            Row dataRow = createRow(sheet, 3 + (rowLine - 1));
            // 序号
            Cell xhCell = dataRow.createCell(colLine++);
            xhCell.setCellValue(rowLine++);
            xhCell.setCellStyle(xhStyle);

            // 姓名
            Cell xmCell = dataRow.createCell(colLine++);
            xmCell.setCellValue(item.getXm());
            xmCell.setCellStyle(xmStyle);

            // 每日数据
            List<String> list = item.getXjqk();
            for (int i = 0; i < list.size(); i++) {
                String value = list.get(i);
                Cell weekCell = dataRow.createCell(colLine++);
                weekCell.setCellValue(value);
                weekCell.setCellStyle(dataStyle);
            }
            // 考勤情况
            addKqqk(dataRow, dataStyle, colLine++, item.getXj(), "xj");
            addKqqk(dataRow, dataStyle, colLine++, item.getBj(), "bj");
            addKqqk(dataRow, dataStyle, colLine++, item.getSj(), "sj");
            addKqqk(dataRow, dataStyle, colLine++, item.getHj(), "hj");
            addKqqk(dataRow, dataStyle, colLine++, item.getCj(), "cj");
            addKqqk(dataRow, dataStyle, colLine++, item.getKg(), "kg");
            addKqqk(dataRow, dataStyle, colLine++, item.getCq(), "cq");

            // 备注
            Cell bzCell = dataRow.createCell(colLine++);
            bzCell.setCellValue(item.getBz());
            bzCell.setCellStyle(dataStyle);
        }

        FileOutputStream fos = new FileOutputStream("export.xlsx");
        workbook.write(fos);

        fos.close();
        fis.close();
    }

    private static void addKqqk(Row dataRow, CellStyle dataStyle, int column, String item, String key) {
        Cell bjCell = dataRow.createCell(column);
        bjCell.setCellType(CellType.NUMERIC);
        bjCell.setCellValue(item);
        bjCell.setCellStyle(dataStyle);
    }

    private static void setStyle(CellStyle cellStyle, Font font) {
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cellStyle.setFont(font);
    }

    private static Row createRow(Sheet sheet, Integer rowIndex) {
        Row row;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);// rowIndex 当前行 lastRowNo 末尾行 1 往下移动一行 (正数代表往下移动，负数表示往上移动)
        }
        row = sheet.createRow(rowIndex);
        return row;
    }

}