先来看看效果
直接上代码
public void doExport(HttpServletRequest request, HttpServletResponse response, List<ProductionPlanActualIEO> list) {//这是我封装的一个个方法，其目的是设置导出文件的名字，不需要的可以不写ExportHelper.prepareExportXlsx(response, EXPORT_FILE_NAME);//获取模板路径File file = templateFileManager.get(EXPORT_FILE_MODEL_NAME);try (FileInputStream templateStream = new FileInputStream(file);Workbook workbook = WorkbookFactory.create(templateStream);OutputStream outputStream = response.getOutputStream()) {//创建中间内容writeTableData(workbook, list);workbook.write(outputStream);} catch (Exception e) {throw new BusinessException(SystemResultStatus.EXCEL_WRITING_ERROR);}}/***这里面才是锁定单元格，和背景至灰**/private void writeTableData(Workbook workbook, List<ProductionPlanActualIEO> list) {Sheet sheet = workbook.getSheetAt(0);//要先将sheet保护起来。里面输入密码。也可以为“”sheet.protectSheet("");//设置样式，非锁定状态。true为锁定CellStyle unlockStyle = workbook.createCellStyle();unlockStyle.setLocked(false);//设置样式，背景颜色为灰色CellStyle greyStyle = workbook.createCellStyle();greyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);greyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//我从第3行开始将数据导出到Excel中int rowNum = ROW_START_OF_DATA;for(ProductionPlanActualIEO data : list){//一条数据一行，下面会进行++操作Row currentRow = sheet.getRow(rowNum);if (currentRow == null) {currentRow = sheet.createRow(rowNum);}//拿到或者创建单元格Cell cellId = getOrCreateCell(currentRow,COL_ID);//至灰，后面大致相同cellId.setCellValue(data.getId());Cell cellPlanNo = getOrCreateCell(currentRow,COL_PLAN_NO);cellPlanNo.setCellValue(data.getPlanNumber());cellPlanNo.setCellStyle(greyStyle);Cell cellPlanDate = getOrCreateCell(currentRow,COL_PLAN_DATE);cellPlanDate.setCellValue(data.getPlanDate());cellPlanDate.setCellStyle(greyStyle);Cell cellPlanClass = getOrCreateCell(currentRow,COL_PLAN_CLASS);cellPlanClass.setCellValue(data.getPlanShift());cellPlanClass.setCellStyle(greyStyle);Cell cellMaterielNo = getOrCreateCell(currentRow,COL_MATERIEL_NO);cellMaterielNo.setCellValue(data.getMaterialCode());cellMaterielNo.setCellStyle(greyStyle);Cell cellPlanNum = getOrCreateCell(currentRow,COL_PLAN_NUMBER);cellPlanNum.setCellValue(data.getPlanAmount().toString());cellPlanNum.setCellStyle(greyStyle);Cell cellUseTime = getOrCreateCell(currentRow,COL_USE_TIME);cellUseTime.setCellValue(data.getUseTime());cellUseTime.setCellStyle(greyStyle);Cell cellPlanConfirm = getOrCreateCell(currentRow,COL_PLAN_CONFIRM);cellPlanConfirm.setCellValue(data.getConfirmPlanAmount());Cell cellPlanAchievements = getOrCreateCell(currentRow,COL_PLAN_ACHIEVEMENTS);cellPlanAchievements.setCellValue(data.getActualAmount());//这里是设置可以编辑的单元格，也就是图中看到的可以编辑的cellif(data.getStaus().equals("0")){cellPlanConfirm.setCellStyle(unlockStyle);cellPlanAchievements.setCellStyle(unlockStyle);}else{cellPlanConfirm.setCellStyle(greyStyle);cellPlanAchievements.setCellStyle(greyStyle);}rowNum++;}}/*** 新建或获取单元格* @param row* @param colNum* @return*/private Cell getOrCreateCell(Row row, int colNum) {Cell cell = row.getCell(colNum);if (cell == null) {cell = row.createCell(colNum);}return cell;}
这里可以跳过，我自己做的封装（部分）-----------------------------------------
public static void prepareExportXlsx(HttpServletResponse response, String filename) {String formattedFilename = formatFilename(filename, SUFFIX_XLSX);prepareResponse(response, formattedFilename);}public static String formatFilename(String filename, String suffix) {String user = "system";if (Context.isUserContext()) {user = Context.getUsername();}return String.format(FILENAME_PATTERN, filename, user, DateFormatUtils.format(new Date(), DEFAULT_TIME_FORMAT), suffix);}public static void prepareResponse(HttpServletResponse response, String filename) {try {String parsedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());response.setCharacterEncoding(StandardCharsets.UTF_8.name());response.setHeader(HEADER_CONTENT_DISPOSITION,"attachment; filename=" + parsedFilename);response.setHeader(HEADER_FILENAME, parsedFilename);response.setContentType(DEFAULT_CONTENT_TYPE);} catch (UnsupportedEncodingException e) {throw new IllegalStateException(e);}}
跳过结束-----------------------------------------------------------------------------------
有疑问的可以给我留言，有时间会回复！
