java，excel导出
在导出动态列中新增一行，内容为：“标准（nationalStandardCode）"，数据取自如下代码：
// 查询国标
String nationalStandardCode = iqcInspectDelegate.getNationalStandardCode(); // 国标编码
这一行需合并动态列长度的单元格，左侧固定列仍然要与动态列对齐
要求：必须在我原本的代码上进行修改，不要封装方法，不要给出伪代码，必须要是完整且详细的代码
我原本的代码如下：
@Override
public void exportInspectDelegateReport(String id, HttpServletResponse response) {
try {
// 1. 获取数据
IqcInspectDelegate iqcInspectDelegate = getById(id); // 委托单
// 准备映射
// 报告名称
Map<String, String> reportNameMap = ListUtils.convertListToMap(
dictService.getDictItems("iqc_inspect_report_name"), DictModel::getValue, DictModel::getText);
// 记录表编号
Map<String, String> recordNumberMap = ListUtils.convertListToMap(
dictService.getDictItems("iqc_inspect_record_number"), DictModel::getValue, DictModel::getText);
// 查询供应商名称、物料名称、接收单编号
String arrivalId = iqcInspectDelegate.getArrivalId(); // 到货单主键
IqcArrivalReportForm arrivalReportForm = arrivalReportFormMapper.selectById(arrivalId); // 到货单
// 设置默认值
String vendorName = arrivalReportForm != null ?
StringUtils.defaultString(arrivalReportForm.getVendor(), "") : "";
String specification = arrivalReportForm != null ?
StringUtils.defaultString(arrivalReportForm.getSpecification(), "") : "";
// 翻译检测人员、审核人
String testByName = sysUserService.getNameByUserName(iqcInspectDelegate.getTestBy()) != null ?
sysUserService.getNameByUserName(iqcInspectDelegate.getTestBy()) : "";
String reviewerName = sysUserService.getNameByUserName(iqcInspectDelegate.getReviewer()) != null ?
sysUserService.getNameByUserName(iqcInspectDelegate.getReviewer()) : "";
// 翻译牌号
String pointAssignName = "";
String pointAssignId = iqcInspectDelegate.getPointAssignId(); // 牌号主键
IqcPointAssign iqcPointAssign = pointAssignService.getById(pointAssignId); // 牌号
if (iqcPointAssign != null) {
pointAssignName = StringUtils.defaultString(iqcPointAssign.getPointName(), "");
}
// 获取报告名称
String reportName = reportNameMap.get(iqcInspectDelegate.getExperimentalProject());
reportName = StringUtils.defaultString(reportName, "");
// 获取记录表编号
String recordNumber = recordNumberMap.get(iqcInspectDelegate.getExperimentalProject());
recordNumber = StringUtils.defaultString(recordNumber, "");
// 处理日期字段
String testTimeStr = "";
if (iqcInspectDelegate.getTestTime() != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
testTimeStr = sdf.format(iqcInspectDelegate.getTestTime());
}
// 查询国标
String nationalStandardCode = iqcInspectDelegate.getNationalStandardCode(); // 国标编码
QueryWrapper<NationalStandardMain> standardMainQueryWrapper = new QueryWrapper<>();
standardMainQueryWrapper.eq("national_standard_code", nationalStandardCode);
standardMainQueryWrapper.eq("is_deleted", 0);
List<NationalStandardMain> standardMainList = standardMainService.list(standardMainQueryWrapper);
List<NationalStandardItem> standardItemList = new ArrayList<>();
if (!CollectionUtils.isEmpty(standardMainList)) {
NationalStandardMain nationalStandardMain = standardMainList.get(0); // 国标主表
String nationalStandardMainId = nationalStandardMain.getId(); // 国标主键
QueryWrapper<NationalStandardItem> standardItemQueryWrapper = new QueryWrapper<>();
standardItemQueryWrapper.eq("main_id", nationalStandardMainId);
standardItemQueryWrapper.eq("is_deleted", 0);
standardItemList = standardItemService.list(standardItemQueryWrapper);
}
// 查询试样
QueryWrapper<IqcDelegateSample> sampleQueryWrapper = new QueryWrapper<>();
sampleQueryWrapper.eq("delegate_id", id); // 委托单主键
sampleQueryWrapper.eq("is_deleted", 0);
List<IqcDelegateSample> sampleList = delegateSampleService.list(sampleQueryWrapper);
List<Map<String, Object>> sampleDataList = new ArrayList<>(); // 用于Excel导出的数据
if (!CollectionUtils.isEmpty(sampleList)) {
for (IqcDelegateSample sample : sampleList) {
String sampleId = sample.getId(); // 试样主键
// 查询试验数据
QueryWrapper<IqcDelegateValue> delegateValueQueryWrapper = new QueryWrapper<>();
delegateValueQueryWrapper.eq("sample_id", sampleId);
delegateValueQueryWrapper.eq("is_deleted", 0);
List<IqcDelegateValue> valueDataList = delegateValueService.list(delegateValueQueryWrapper);
Map<String, Object> sampleData = new HashMap<>();
// 添加试样基本信息
sampleData.put("sampleCode", StringUtils.defaultString(sample.getSampleCode(), ""));
sampleData.put("sampleName", StringUtils.defaultString(sample.getSampleName(), ""));
if (!CollectionUtils.isEmpty(valueDataList)) {
for (IqcDelegateValue valueData : valueDataList) {
// 获取试验项目信息
String standardAssociation = valueData.getStandardAssociation();
if (StringUtils.isNotBlank(standardAssociation)) {
// 查找对应的国标项
for (NationalStandardItem item : standardItemList) {
if ((item.getStandardContent()+item.getStandardValue()).equals(standardAssociation)) {
sampleData.put(
StringUtils.defaultString(item.getStandardContent()+item.getStandardValue(), ""),
StringUtils.defaultString(valueData.getDataValue(), "")
);
break;
}
}
}
}
}
sampleDataList.add(sampleData);
}
}
// 2. 创建Excel
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
// 创建通用边框样式
CellStyle borderStyle = workbook.createCellStyle();
borderStyle.setBorderTop(BorderStyle.THIN);
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);
// 标题样式
CellStyle titleStyle = workbook.createCellStyle();
Font titleFont = workbook.createFont();
titleFont.setBold(true);
titleFont.setFontHeightInPoints((short) 16);
titleStyle.cloneStyleFrom(borderStyle); // 继承边框
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 项目名称样式
CellStyle projectStyle = workbook.createCellStyle();
Font projectFont = workbook.createFont();
projectFont.setBold(true);
projectFont.setFontHeightInPoints((short) 14);
projectStyle.cloneStyleFrom(borderStyle); // 继承边框
projectStyle.setFont(projectFont);
projectStyle.setAlignment(HorizontalAlignment.CENTER);
projectStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 创建记录表编号 - 居右
CellStyle recordStyle = workbook.createCellStyle();
recordStyle.cloneStyleFrom(projectStyle); // 继承项目样式
recordStyle.setAlignment(HorizontalAlignment.RIGHT); // 设置居右
// 表头样式
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 12);
headerStyle.setFont(headerFont);
headerStyle.cloneStyleFrom(borderStyle); // 继承边框
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setWrapText(true); // 自动换行
// 数据样式
CellStyle dataStyle = workbook.createCellStyle();
Font dataFont = workbook.createFont();
dataFont.setFontHeightInPoints((short) 11);
dataStyle.setFont(dataFont);
dataStyle.cloneStyleFrom(borderStyle); // 继承边框
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 信息行样式
CellStyle infoStyle = workbook.createCellStyle();
Font infoFont = workbook.createFont();
infoFont.setFontHeightInPoints((short) 11);
infoStyle.setFont(infoFont);
infoStyle.cloneStyleFrom(borderStyle); // 继承边框
infoStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 创建标题行
// 公司名称
Row titleRow = sheet.createRow(0);
titleRow.setHeight((short) 500);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("眉山中车制动科技股份有限公司");
titleCell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, standardItemList.size() + 3));
// 报告名称行
Row projectRow = sheet.createRow(1);
projectRow.setHeight((short) 400);
Cell projectCell = projectRow.createCell(0);
projectCell.setCellValue(reportName);
projectCell.setCellStyle(projectStyle);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, standardItemList.size() + 3));
// 记录表编号行 - 居右
Row recordRow = sheet.createRow(2);
recordRow.setHeight((short) 400);
Cell recordCell = recordRow.createCell(0);
recordCell.setCellValue(recordNumber);
recordCell.setCellStyle(recordStyle);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, standardItemList.size() + 3));
// 信息行
Row infoRow = sheet.createRow(3);
infoRow.setHeight((short) 400);
// 供应商信息
Cell vendorCell = infoRow.createCell(0);
vendorCell.setCellValue("供应商:" + vendorName);
vendorCell.setCellStyle(infoStyle);
// 试验日期
Cell dateLabelCell = infoRow.createCell(2);
dateLabelCell.setCellValue("试验日期：");
dateLabelCell.setCellStyle(infoStyle);
Cell dateValueCell = infoRow.createCell(3);
dateValueCell.setCellValue(testTimeStr);
dateValueCell.setCellStyle(infoStyle);
// 报告编号
Cell reportLabelCell = infoRow.createCell(4);
reportLabelCell.setCellValue("报告编号："+iqcInspectDelegate.getDelegateCode());
reportLabelCell.setCellStyle(infoStyle);
Cell reportValueCell = infoRow.createCell(5);
reportValueCell.setCellStyle(infoStyle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); // 供应商
sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 3)); // 试验日期
sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, standardItemList.size() + 3)); // 报告编号
Row headerRow1 = sheet.createRow(4);
headerRow1.setHeight((short) 400);
Row headerRow2 = sheet.createRow(5);
headerRow2.setHeight((short) 400);
// 固定列标题
String[] fixedHeaders = {"试样名称", "牌号", "规格", "试样编号"};
for (int i = 0; i < fixedHeaders.length; i++) {
// 第一行创建固定列单元格
Cell cell1 = headerRow1.createCell(i);
cell1.setCellValue(fixedHeaders[i]);
cell1.setCellStyle(headerStyle);
// 第二行创建空单元格
Cell cell2 = headerRow2.createCell(i);
cell2.setCellStyle(headerStyle);
// 合并固定列
sheet.addMergedRegion(new CellRangeAddress(4, 5, i, i));
}
// 动态列标题
for (int j = 0; j < standardItemList.size(); j++) {
int colIndex = fixedHeaders.length + j;
// 第一行：内容
Cell contentCell = headerRow1.createCell(colIndex);
contentCell.setCellValue(standardItemList.get(j).getStandardContent());
contentCell.setCellStyle(headerStyle);
// 第二行：范围
Cell valueCell = headerRow2.createCell(colIndex);
// 范围格式需转换
String convertedStandardValue = convertStandardValue(iqcInspectDelegate.getExperimentalProject(),
standardItemList.get(j).getStandardValue());
valueCell.setCellValue(convertedStandardValue);
valueCell.setCellStyle(headerStyle);
}
for (int i = 0; i < sampleDataList.size(); i++) {
Map<String, Object> sampleData = sampleDataList.get(i);
Row dataRow = sheet.createRow(6 + i);
dataRow.setHeight((short) 400);
// 固定列数据
Cell sampleNameCell = dataRow.createCell(0);
sampleNameCell.setCellValue(sampleData.get("sampleName").toString());
sampleNameCell.setCellStyle(dataStyle);
Cell pointAssignCell = dataRow.createCell(1);
pointAssignCell.setCellValue(pointAssignName);
pointAssignCell.setCellStyle(dataStyle);
Cell specificationCell = dataRow.createCell(2);
specificationCell.setCellValue(specification);
specificationCell.setCellStyle(dataStyle);
Cell sampleCodeCell = dataRow.createCell(3);
sampleCodeCell.setCellValue(sampleData.get("sampleCode").toString());
sampleCodeCell.setCellStyle(dataStyle);
// 动态列数据
for (int j = 0; j < standardItemList.size(); j++) {
String itemKey = standardItemList.get(j).getStandardContent()
+ standardItemList.get(j).getStandardValue();
Object value = sampleData.get(itemKey);
Cell valueCell = dataRow.createCell(4 + j);
valueCell.setCellValue(value != null ? value.toString() : "");
valueCell.setCellStyle(dataStyle);
}
}
int remarkRowNum = 6 + sampleDataList.size();
Row remarkRow = sheet.createRow(remarkRowNum);
remarkRow.setHeight((short) 400);
Cell remarkCell = remarkRow.createCell(0);
remarkCell.setCellValue("备注：" + iqcInspectDelegate.getRemark());
remarkCell.setCellStyle(infoStyle);
sheet.addMergedRegion(new CellRangeAddress(
remarkRowNum, remarkRowNum, 0, standardItemList.size() + 3));
int lastRow = remarkRowNum + 1;
Row footerRow = sheet.createRow(lastRow);
footerRow.setHeight((short) 400);
Cell footerCell = footerRow.createCell(0);
footerCell.setCellValue("分析：" + testByName + " 审批人：" + reviewerName);
footerCell.setCellStyle(infoStyle);
sheet.addMergedRegion(new CellRangeAddress(
lastRow, lastRow, 0, standardItemList.size() + 3));
CellStyle verticalCenterStyle = workbook.createCellStyle();
verticalCenterStyle.cloneStyleFrom(headerStyle);
verticalCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
for (int i = 0; i < fixedHeaders.length; i++) {
headerRow1.getCell(i).setCellStyle(verticalCenterStyle);
}
// 设置列宽
sheet.setColumnWidth(0, 4000); // 试样名称
sheet.setColumnWidth(1, 4000); // 牌号
sheet.setColumnWidth(2, 3000); // 规格
sheet.setColumnWidth(3, 4000); // 试样编号
// 动态列宽度
for (int i = 0; i < standardItemList.size(); i++) {
sheet.setColumnWidth(4 + i, 4000); // 每个动态列
}
// 3. 为所有单元格添加边框
int lastRowNum = sheet.getLastRowNum();
int lastCellNum = standardItemList.size() + 3;
for (int r = 0; r <= lastRowNum; r++) {
Row row = sheet.getRow(r) != null ? sheet.getRow(r) : sheet.createRow(r);
for (int c = 0; c <= lastCellNum; c++) {
Cell cell = row.getCell(c) != null ? row.getCell(c) : row.createCell(c);
// 保留原有样式基础上添加边框
CellStyle currentStyle = cell.getCellStyle();
CellStyle newStyle = workbook.createCellStyle();
newStyle.cloneStyleFrom(currentStyle);
newStyle.setBorderTop(BorderStyle.THIN);
newStyle.setBorderBottom(BorderStyle.THIN);
newStyle.setBorderLeft(BorderStyle.THIN);
newStyle.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(newStyle);
}
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=inspectDelegateReport.xlsx");
workbook.write(response.getOutputStream());
workbook.close();
} catch (Exception e) {
log.error("导出Excel时发生错误", e);
throw new RuntimeException("导出Excel失败: " + e.getMessage());
}
}
最新发布
博客展示了一段ASP代码，包含一个DataList控件，设置了宽度和单元格间距。在ItemTemplate中，对FileName数据项进行处理，根据其长度进行不同操作，如替换特殊字符、截取字符串等。
扫一扫
2758
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
