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);
|
}
|
|
}
|
