package com.ruoyi.quality.utils;
|
|
import com.ruoyi.quality.pojo.QualityInspect;
|
import jakarta.servlet.http.HttpServletResponse;
|
import org.apache.poi.ss.usermodel.BorderStyle;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.ClientAnchor;
|
import org.apache.poi.ss.usermodel.Font;
|
import org.apache.poi.ss.usermodel.HorizontalAlignment;
|
import org.apache.poi.ss.usermodel.PrintSetup;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.VerticalAlignment;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.RegionUtil;
|
import org.apache.poi.util.Units;
|
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
|
import org.apache.poi.xssf.usermodel.XSSFDrawing;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.math.BigDecimal;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.List;
|
|
/**
|
* 出厂检验导出（按纸质单据样式生成 Excel）
|
*/
|
public class FinalInspectExportUtil {
|
|
// ==================== 可定制区域（改这里即可调整模板） ====================
|
/** LOGO 图片与单元格边框之间的留白（像素） */
|
private static final float LOGO_PADDING_PX = 6f;
|
/** 左上角 LOGO 图片（classpath 路径，放在 resources/static 下） */
|
private static final String LOGO_IMAGE_PATH = "/static/junge-logo.png";
|
/** 本地开发环境 LOGO 绝对路径（优先读取） */
|
private static final String LOGO_ABS_PATH = "D:/develop/XingRuan/product-inventory-management-junge/product-inventory-management/multiple/assets/logo/JunGeJiTuan-logo.png";
|
/** 居中大标题 */
|
private static final String TITLE = "出厂检验记录表";
|
/** 固定数据行数（数据不足补空行，超过自动追加） */
|
private static final int FIXED_ROW_COUNT = 14;
|
/** 表头列（从左到右） */
|
private static final String[] HEADERS = {
|
"序号", "名称", "图号", "材质", "检验依据", "数量", "抽检数",
|
"图纸关键部位尺寸", "实测关键部位尺寸", "检验日期", "入库编号", "所属设备名称", "项目名字", "判定"
|
};
|
/** 列宽自适应：每列在内容宽度基础上额外加的宽度（约等于一个汉字宽度） */
|
private static final int COL_WIDTH_PADDING = 2;
|
/** 单列最大宽度（约等于汉字个数），防止个别超长内容把列撑得过宽 */
|
private static final int COL_WIDTH_MAX = 40;
|
/** 正文/标题字号 */
|
private static final float BODY_FONT_SIZE = 12f;
|
private static final float TITLE_FONT_SIZE = 16f;
|
/** 是否横向打印 */
|
private static final boolean LANDSCAPE = true;
|
// ========================================================================
|
|
public static void export(HttpServletResponse response, List<QualityInspect> list) {
|
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
|
Sheet sheet = workbook.createSheet(TITLE);
|
int colCount = HEADERS.length;
|
|
// 样式
|
CellStyle titleStyle = createStyle(workbook, true, TITLE_FONT_SIZE, HorizontalAlignment.CENTER);
|
CellStyle labelStyle = createStyle(workbook, false, BODY_FONT_SIZE, HorizontalAlignment.LEFT);
|
CellStyle headerStyle = createStyle(workbook, true, BODY_FONT_SIZE, HorizontalAlignment.CENTER);
|
CellStyle centerStyle = createStyle(workbook, false, BODY_FONT_SIZE, HorizontalAlignment.CENTER);
|
CellStyle leftStyle = createStyle(workbook, false, BODY_FONT_SIZE, HorizontalAlignment.LEFT);
|
leftStyle.setWrapText(true);
|
// 数据区样式加边框
|
addThinBorder(headerStyle);
|
addThinBorder(centerStyle);
|
addThinBorder(leftStyle);
|
|
// ---- 第1~2行：页眉 ----
|
Row row1 = sheet.createRow(0);
|
Row row2 = sheet.createRow(1);
|
row1.setHeightInPoints(45);
|
row2.setHeightInPoints(20);
|
|
// 左上：公司 LOGO 图片（A1:C2 合并单元格）
|
CellRangeAddress logoRegion = new CellRangeAddress(0, 1, 0, 2);
|
sheet.addMergedRegion(logoRegion);
|
Cell logoCell = row1.createCell(0);
|
logoCell.setCellStyle(createStyle(workbook, false, 12f, HorizontalAlignment.CENTER));
|
applyRegionBorder(sheet, logoRegion);
|
|
// 居中大标题（D~K）
|
Cell titleCell = row1.createCell(3);
|
titleCell.setCellValue(TITLE);
|
titleCell.setCellStyle(titleStyle);
|
CellRangeAddress titleRegion = new CellRangeAddress(0, 1, 3, colCount - 4);
|
sheet.addMergedRegion(titleRegion);
|
applyRegionBorder(sheet, titleRegion);
|
|
// 右上：文件编号 / 页数（靠左）
|
Cell fileNoCell = row1.createCell(colCount - 3);
|
fileNoCell.setCellValue("文件编号：");
|
fileNoCell.setCellStyle(labelStyle);
|
CellRangeAddress fileNoRegion = new CellRangeAddress(0, 0, colCount - 3, colCount - 1);
|
sheet.addMergedRegion(fileNoRegion);
|
applyRegionBorder(sheet, fileNoRegion);
|
|
Cell pageCell = row2.createCell(colCount - 3);
|
pageCell.setCellValue("页数 / No.：");
|
pageCell.setCellStyle(labelStyle);
|
CellRangeAddress pageRegion = new CellRangeAddress(1, 1, colCount - 3, colCount - 1);
|
sheet.addMergedRegion(pageRegion);
|
applyRegionBorder(sheet, pageRegion);
|
|
// ---- 第3行：表头 ----
|
int headerRowIndex = 2;
|
Row headerRow = sheet.createRow(headerRowIndex);
|
headerRow.setHeightInPoints(24);
|
for (int i = 0; i < colCount; i++) {
|
Cell cell = headerRow.createCell(i);
|
cell.setCellValue(HEADERS[i]);
|
cell.setCellStyle(headerStyle);
|
}
|
|
// ---- 数据行：固定行数，不足补空行 ----
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
int dataRows = Math.max(FIXED_ROW_COUNT, list.size());
|
for (int r = 0; r < dataRows; r++) {
|
Row dataRow = sheet.createRow(headerRowIndex + 1 + r);
|
for (int c = 0; c < colCount; c++) {
|
Cell cell = dataRow.createCell(c);
|
// 文本类列左对齐，其余居中（序号/数量/抽检数/检验日期/判定居中）
|
boolean isTextColumn = c == 1 || c == 2 || c == 3 || c == 4 || c == 7 || c == 8
|
|| c == 10 || c == 11 || c == 12;
|
cell.setCellStyle(isTextColumn ? leftStyle : centerStyle);
|
}
|
// 序号固定编号 1~N
|
dataRow.getCell(0).setCellValue(r + 1);
|
if (r < list.size()) {
|
QualityInspect item = list.get(r);
|
setText(dataRow.getCell(1), item.getProductName()); // 名称
|
setText(dataRow.getCell(2), item.getModel()); // 图号
|
setText(dataRow.getCell(3), item.getMaterial()); // 材质
|
setText(dataRow.getCell(4), item.getAcceptanceBasis()); // 检验依据
|
setText(dataRow.getCell(5), plain(item.getQuantity())); // 数量
|
setText(dataRow.getCell(6), plain(item.getSamplingCount())); // 抽检数
|
setText(dataRow.getCell(7), item.getDrawingKeyDimensions()); // 图纸关键部位尺寸
|
setText(dataRow.getCell(8), item.getMeasuredKeyDimensions()); // 实测关键部位尺寸
|
setText(dataRow.getCell(9), item.getCheckTime() == null ? "" : sdf.format(item.getCheckTime())); // 检验日期
|
setText(dataRow.getCell(10), item.getWarehouseCode()); // 入库编号
|
setText(dataRow.getCell(11), item.getEquipmentName()); // 所属设备名称
|
setText(dataRow.getCell(12), item.getProjectName()); // 项目名字
|
setText(dataRow.getCell(13), item.getVerdict()); // 判定
|
}
|
// 根据文本列换行行数估算行高
|
dataRow.setHeightInPoints(estimateRowHeight(dataRow, colCount));
|
}
|
|
// 列宽按内容自适应
|
autoSizeColumns(sheet, colCount, headerRowIndex, dataRows);
|
// 列宽确定后再嵌入 LOGO
|
addLogoImage(workbook, sheet);
|
|
// ---- 页面设置：A4 ----
|
PrintSetup ps = sheet.getPrintSetup();
|
ps.setPaperSize(PrintSetup.A4_PAPERSIZE);
|
ps.setLandscape(LANDSCAPE);
|
sheet.setMargin(Sheet.TopMargin, 0.4);
|
sheet.setMargin(Sheet.BottomMargin, 0.4);
|
sheet.setMargin(Sheet.LeftMargin, 0.3);
|
sheet.setMargin(Sheet.RightMargin, 0.3);
|
sheet.setFitToPage(true);
|
ps.setFitWidth((short) 1);
|
ps.setFitHeight((short) 0);
|
|
// ---- 输出 ----
|
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
String fileName = URLEncoder.encode(TITLE, "UTF-8");
|
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
|
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
|
OutputStream os = response.getOutputStream();
|
workbook.write(os);
|
os.flush();
|
} catch (Exception e) {
|
throw new RuntimeException("导出失败", e);
|
}
|
}
|
|
/** 在 A1:C2 合并区嵌入公司 LOGO 图片 */
|
private static void addLogoImage(XSSFWorkbook workbook, Sheet sheet) {
|
byte[] bytes = loadLogoBytes();
|
if (bytes == null) {
|
System.err.println("[FinalInspectExport] LOGO 图片未找到，导出将不带 LOGO（资源路径: " + LOGO_IMAGE_PATH + "）");
|
return;
|
}
|
try {
|
int pictureIdx = workbook.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
|
XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
|
// 图片定位：A1:C1 区域内
|
float colAPx = sheet.getColumnWidthInPixels(0);
|
float colBPx = sheet.getColumnWidthInPixels(1);
|
float colCPx = sheet.getColumnWidthInPixels(2);
|
float blockWidthPx = colAPx + colBPx + colCPx;
|
float row0Px = sheet.getRow(0).getHeightInPoints() * 96f / 72f;
|
float row1Px = sheet.getRow(1).getHeightInPoints() * 96f / 72f;
|
float cellHPx = row0Px + row1Px;
|
// 读取图片真实宽高
|
int imgW = 236;
|
int imgH = 190;
|
if (bytes.length > 24) {
|
imgW = readInt(bytes, 16);
|
imgH = readInt(bytes, 20);
|
}
|
// 按单元格大小等比缩放（四周留白），整体居中
|
float availW = blockWidthPx - LOGO_PADDING_PX * 2;
|
float availH = cellHPx - LOGO_PADDING_PX * 2;
|
float scale = Math.min(availW / imgW, availH / imgH);
|
float dispW = imgW * scale;
|
float dispH = imgH * scale;
|
float absLeft = LOGO_PADDING_PX + (availW - dispW) / 2f;
|
float absTop = LOGO_PADDING_PX + (availH - dispH) / 2f;
|
float absRight = absLeft + dispW;
|
float absBottom = absTop + dispH;
|
// 计算 from/to 锚点：所有偏移量保持在所在列/行的合法范围内
|
int fromCol;
|
int fromOffPx;
|
if (absLeft <= colAPx) {
|
fromCol = 0;
|
fromOffPx = (int) absLeft;
|
} else if (absLeft <= colAPx + colBPx) {
|
fromCol = 1;
|
fromOffPx = (int) (absLeft - colAPx);
|
} else {
|
fromCol = 2;
|
fromOffPx = (int) (absLeft - colAPx - colBPx);
|
}
|
int toCol;
|
int toOffPx;
|
if (absRight <= colAPx) {
|
toCol = 0;
|
toOffPx = (int) absRight;
|
} else if (absRight <= colAPx + colBPx) {
|
toCol = 1;
|
toOffPx = (int) (absRight - colAPx);
|
} else {
|
toCol = 2;
|
toOffPx = (int) (absRight - colAPx - colBPx);
|
}
|
int fromRow = 0;
|
float fromRowOff = absTop;
|
if (absTop > row0Px) {
|
fromRow = 1;
|
fromRowOff = absTop - row0Px;
|
}
|
int toRow = 0;
|
float toRowOff = absBottom;
|
if (absBottom > row0Px) {
|
toRow = 1;
|
toRowOff = absBottom - row0Px;
|
}
|
XSSFClientAnchor anchor = new XSSFClientAnchor(
|
fromOffPx * Units.EMU_PER_PIXEL, (int) (fromRowOff * Units.EMU_PER_PIXEL),
|
toOffPx * Units.EMU_PER_PIXEL, (int) (toRowOff * Units.EMU_PER_PIXEL),
|
fromCol, fromRow, toCol, toRow);
|
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
|
drawing.createPicture(anchor, pictureIdx);
|
} catch (Exception e) {
|
// LOGO 加载失败不影响导出，但打印错误便于排查
|
System.err.println("[FinalInspectExport] LOGO 嵌入失败: " + e.getMessage());
|
}
|
}
|
|
/** 读取 LOGO：本地开发优先读绝对路径，其次 classpath，最后项目相对路径 */
|
private static byte[] loadLogoBytes() {
|
try (InputStream is = new java.io.FileInputStream(LOGO_ABS_PATH)) {
|
return is.readAllBytes();
|
} catch (Exception ignored) {
|
}
|
try (InputStream is = FinalInspectExportUtil.class.getResourceAsStream(LOGO_IMAGE_PATH)) {
|
if (is != null) {
|
return is.readAllBytes();
|
}
|
} catch (Exception ignored) {
|
}
|
try (InputStream is = new java.io.FileInputStream("src/main/resources" + LOGO_IMAGE_PATH)) {
|
return is.readAllBytes();
|
} catch (Exception e) {
|
return null;
|
}
|
}
|
|
/** 读取 PNG 头部的宽/高（大端整数） */
|
private static int readInt(byte[] b, int off) {
|
return ((b[off] & 0xFF) << 24) | ((b[off + 1] & 0xFF) << 16) | ((b[off + 2] & 0xFF) << 8) | (b[off + 3] & 0xFF);
|
}
|
|
private static CellStyle createStyle(XSSFWorkbook workbook, boolean bold, float fontSize,
|
HorizontalAlignment align) {
|
CellStyle style = workbook.createCellStyle();
|
Font font = workbook.createFont();
|
font.setFontName("宋体");
|
font.setBold(bold);
|
font.setFontHeightInPoints((short) fontSize);
|
style.setFont(font);
|
style.setAlignment(align);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
return style;
|
}
|
|
private static void addThinBorder(CellStyle style) {
|
style.setBorderTop(BorderStyle.THIN);
|
style.setBorderBottom(BorderStyle.THIN);
|
style.setBorderLeft(BorderStyle.THIN);
|
style.setBorderRight(BorderStyle.THIN);
|
}
|
|
private static void applyRegionBorder(Sheet sheet, CellRangeAddress region) {
|
RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
|
RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
|
RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
|
RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
|
}
|
|
private static void setText(Cell cell, String value) {
|
cell.setCellValue(value == null ? "" : value);
|
}
|
|
private static String plain(BigDecimal value) {
|
return value == null ? "" : value.toPlainString();
|
}
|
|
/** 按文本列内容与列宽估算换行后的行高（点数） */
|
private static float estimateRowHeight(Row row, int colCount) {
|
int maxLines = 1;
|
for (int c = 1; c < colCount; c++) {
|
boolean isTextColumn = c == 1 || c == 2 || c == 3 || c == 4 || c == 7 || c == 8
|
|| c == 10 || c == 11 || c == 12;
|
if (!isTextColumn) {
|
continue;
|
}
|
Cell cell = row.getCell(c);
|
String value = cell == null ? null : cell.getStringCellValue();
|
if (value == null || value.isEmpty()) {
|
continue;
|
}
|
int lines = (int) Math.ceil(displayLen(value) / (COL_WIDTH_MAX * 2d));
|
maxLines = Math.max(maxLines, lines);
|
}
|
return Math.max(22f, maxLines * 15f + 6f);
|
}
|
|
/** 列宽自适应：按表头与数据内容计算每列宽度 */
|
private static void autoSizeColumns(Sheet sheet, int colCount, int headerRowIndex, int dataRows) {
|
for (int c = 0; c < colCount; c++) {
|
double maxLen = displayLen(HEADERS[c]);
|
for (int r = 0; r < dataRows; r++) {
|
Row row = sheet.getRow(headerRowIndex + 1 + r);
|
Cell cell = row == null ? null : row.getCell(c);
|
if (cell != null && cell.getCellType() == CellType.STRING) {
|
maxLen = Math.max(maxLen, displayLen(cell.getStringCellValue()));
|
}
|
}
|
double widthChars = Math.min(maxLen + COL_WIDTH_PADDING, COL_WIDTH_MAX + COL_WIDTH_PADDING);
|
// 中文按两个半角字符宽度计算
|
sheet.setColumnWidth(c, (int) (widthChars * 256));
|
}
|
}
|
|
/** 计算显示宽度：中文等全角字符按 2 个半角宽度计 */
|
private static double displayLen(String s) {
|
if (s == null) {
|
return 0;
|
}
|
double len = 0;
|
for (char ch : s.toCharArray()) {
|
if ((ch >= 0x2E80 && ch <= 0x9FFF) || (ch >= 0x3000 && ch <= 0x303F) || ch >= 0xFF00) {
|
len += 2;
|
} else {
|
len += 1;
|
}
|
}
|
return len;
|
}
|
}
|
