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; } }
POI实现模板导入并插入数据
最新推荐文章于 2024-07-05 14:30:10 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)