先来看看效果
直接上代码
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());
//这里是设置可以编辑的单元格,也就是图中看到的可以编辑的cell
if(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);
}
}
跳过结束-----------------------------------------------------------------------------------
有疑问的可以给我留言,有时间会回复!