package com.nanometer.smartlab.service; import com.nanometer.smartlab.exception.BusinessException; import com.nanometer.smartlab.exception.ExceptionEnumCode; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.net.URLEncoder; @Service("accountInAndOutService") public class AccountInAndOutServiceImpl implements AccountInAndOutService { @Override public void exportExcel(HttpServletResponse resp) { try { // 创建文档对象 HSSFWorkbook wb = new HSSFWorkbook(); // 创建sheet对象 HSSFSheet sheet = wb.createSheet("出入库 台账"); sheet.setDefaultRowHeight((short) (20 * 20)); //设置字体 HSSFFont headFont = wb.createFont(); headFont.setFontName("宋体"); headFont.setFontHeightInPoints((short) 11); headFont.setBold(true); HSSFFont bodyFont = wb.createFont(); bodyFont.setFontName("宋体"); bodyFont.setFontHeightInPoints((short) 10); // 边框颜色和样式 HSSFCellStyle defaultBorderStyle = wb.createCellStyle(); defaultBorderStyle.setBorderBottom(BorderStyle.THIN); defaultBorderStyle.setBottomBorderColor(IndexedColors.BLACK.index); defaultBorderStyle.setBorderTop(BorderStyle.THIN); defaultBorderStyle.setTopBorderColor(IndexedColors.BLACK.index); defaultBorderStyle.setBorderLeft(BorderStyle.THIN); defaultBorderStyle.setLeftBorderColor(IndexedColors.BLACK.index); defaultBorderStyle.setBorderRight(BorderStyle.THIN); defaultBorderStyle.setRightBorderColor(IndexedColors.BLACK.index); // 默认字体和剧中 HSSFCellStyle defaultStyle = wb.createCellStyle(); defaultStyle.setFont(bodyFont); defaultStyle.setWrapText(true); defaultStyle.setAlignment(HorizontalAlignment.CENTER); defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 默认标题头部样式 HSSFCellStyle defaultHeadStyle = wb.createCellStyle(); defaultHeadStyle.setFont(headFont); defaultHeadStyle.setWrapText(true); defaultHeadStyle.setAlignment(HorizontalAlignment.CENTER); defaultHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 固定列数和设置宽度 int[] width = {10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10}; for (int columnIndex = 0; columnIndex < width.length; columnIndex++) { sheet.setColumnWidth(columnIndex, width[columnIndex] * 256); sheet.setDefaultColumnStyle(columnIndex, defaultBorderStyle); } // exc // 0.数据准备 // 1.创建表头 this.createTableHead(sheet, defaultHeadStyle, defaultStyle, "", 0); // 2.创建数据行 // 过滤出数据 // this.createTableBody(sheet, defaultHeadStyle, defaultStyle, 3); // 返回文件 String filename = "2022年易制毒出入库台账.xlsx"; resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8")); OutputStream outputStream = resp.getOutputStream(); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); throw new BusinessException(ExceptionEnumCode.PARAM_ERR, "系统导出失败，请联系管理员"); } } private void setBorder(CellRangeAddress region, HSSFSheet sheet){ RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet); } private void createTableHead(HSSFSheet sheet, HSSFCellStyle defaultHeadStyle, HSSFCellStyle defaultStyle, String title, int i) { // 一月易制毒化学品出入库台账 HSSFRow row0 = sheet.createRow(i); HSSFCell cell00 = row0.createCell(i); // 设置默认行高 不然 row 可以自定义 cell00.setCellStyle(defaultHeadStyle); cell00.setCellValue("一月易制毒化学品出入库台账"); CellRangeAddress cra = new CellRangeAddress(i, i, 0, 9); setBorder(cra, sheet); sheet.addMergedRegion(cra); // 品名：乙醚（单位：L） HSSFCell cell01 = row0.createCell(10); cell01.setCellStyle(defaultStyle); cell01.setCellValue("品名：乙醚（单位：L）"); CellRangeAddress cra0 = new CellRangeAddress(i, i, 10, 13); setBorder(cra0, sheet); sheet.addMergedRegion(cra0); // 换行 HSSFRow row1 = sheet.createRow(i + 1); // row1.setHeight((short) (20 * 20)); // 日期 HSSFCell cell10 = row1.createCell(0); cell10.setCellStyle(defaultStyle); cell10.setCellValue("日期"); CellRangeAddress cra10 = new CellRangeAddress(i + 1, i + 2, 0, 0); setBorder(cra10, sheet); sheet.addMergedRegion(cra10); // 入库 HSSFCell cell11 = row1.createCell(1); cell11.setCellStyle(defaultStyle); cell11.setCellValue("入库"); CellRangeAddress address = new CellRangeAddress(i + 1, i + 1, 1, 5); setBorder(address, sheet); sheet.addMergedRegion(address); // 出库 HSSFCell cell12 = row1.createCell(6); cell12.setCellStyle(defaultStyle); cell12.setCellValue("出库"); CellRangeAddress cra12 = new CellRangeAddress(i + 1, i + 1, 6, 11); setBorder(cra12, sheet); sheet.addMergedRegion(cra12); // 库存数量 HSSFCell cell13 = row1.createCell(12); cell13.setCellStyle(defaultStyle); cell13.setCellValue("库存数量"); CellRangeAddress cra13 = new CellRangeAddress(i + 1, i + 2, 12, 13); setBorder(cra13, sheet); sheet.addMergedRegion(cra13); HSSFRow row2 = sheet.createRow(i + 2); // 入库数量 HSSFCell cell21 = row2.createCell(1); cell21.setCellStyle(defaultStyle); cell21.setCellValue("入库数量"); // 入库单号 HSSFCell cell22 = row2.createCell(2); cell22.setCellStyle(defaultStyle); cell22.setCellValue("入库单号"); CellRangeAddress cra22 = new CellRangeAddress(i + 2, i + 2, 2, 4); setBorder(cra22, sheet); sheet.addMergedRegion(cra22); // 保管员 HSSFCell cell23 = row2.createCell(5); cell23.setCellStyle(defaultStyle); cell23.setCellValue("保管员"); // 出库数量 HSSFCell cell24 = row2.createCell(6); cell24.setCellStyle(defaultStyle); cell24.setCellValue("出库数量"); // 领料单号 HSSFCell cell25 = row2.createCell(7); cell25.setCellStyle(defaultStyle); cell25.setCellValue("领料单号"); CellRangeAddress cra25 = new CellRangeAddress(i + 2, i + 2, 7, 9); setBorder(cra25, sheet); sheet.addMergedRegion(cra25); // 经手人 HSSFCell cell26 = row2.createCell(10); cell26.setCellStyle(defaultStyle); cell26.setCellValue("经手人"); CellRangeAddress cra26 = new CellRangeAddress(i + 2, i + 2, 10, 11); setBorder(cra26, sheet); sheet.addMergedRegion(cra26); } }