导出excel模板
所需要的Jar包
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
前期准备
规定好模板
@RequestMapping(value = “/export”, method = RequestMethod.GET)
public void export(String id, HttpServletResponse response) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
SimpleDateFormat outSdf = new SimpleDateFormat("yyyy-MM-dd");
//获取模板路径
String excelFilePath = ConsumableStockOutController.class.getClassLoader().getResource("static/templates/consumable/consumable-stock-out.xlsx").getPath();
File excelFile = new File(excelFilePath);
try {
Workbook wb = new XSSFWorkbook(excelFile);
Sheet sheet = wb.getSheet("Sheet1");
CellStyle cellStyle;
cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
Font font = wb.createFont();
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
int firstRowIndex = sheet.getFirstRowNum() + 2;
int lastRowIndex = sheet.getLastRowNum() + 1;
ConsumableStockOutDTO stockOutDto = stockOutService.getById(id);
List<ConsumableStockOutItemDTO> stockInOutItemDtoList = stockOutService.getStockOutItemList(id);
Row stockOutRow = sheet.createRow(firstRowIndex);
stockOutRow.setHeightInPoints(22);
Cell cellArray[] = new Cell[13];
for (int j = 0; j < 13; j++) {
if (j == 0) {
cellArray[j] = stockOutRow.createCell(j);
cellArray[j].setCellStyle(cellStyle);
} else {
cellArray[j + 1] = stockOutRow.createCell(j + 1);
cellArray[j + 1].setCellStyle(cellStyle);
++j;
}
}
cellArray[0].setCellValue(stockOutDto.getStockOutNumber());
if (null == stockOutDto.getPrincipalDepartment()) {
cellArray[2].setCellValue("公司领导");
} else
cellArray[2].setCellValue(stockOutDto.getPrincipalDepartment());
cellArray[4].setCellValue(stockOutDto.getPrincipal());
if (null == stockOutDto.getStockOutDate())
cellArray[6].setCellValue("");
else
cellArray[6].setCellValue(outSdf.format(stockOutDto.getStockOutDate()));
cellArray[8].setCellValue(stockOutDto.getDescription());
cellArray[10].setCellValue(stockOutDto.getRecordedByFullName());
cellArray[12].setCellValue(sdf.format(stockOutDto.getRecordedOn()));
for (int i = 0; i < stockInOutItemDtoList.size(); i++) {
Row itemRow = sheet.createRow(lastRowIndex + i);
itemRow.setHeightInPoints(22);
Cell itemCellArray[] = new Cell[15];
for (int j = 0; j < 15; j++) {
itemCellArray[j] = itemRow.createCell(j);
itemCellArray[j].setCellStyle(cellStyle);
}
itemCellArray[0].setCellValue(stockInOutItemDtoList.get(i).getBigCategoryName());
itemCellArray[1].setCellValue(stockInOutItemDtoList.get(i).getSmallCategoryName());
itemCellArray[2].setCellValue(stockInOutItemDtoList.get(i).getName());
itemCellArray[3].setCellValue(stockInOutItemDtoList.get(i).getBrand());
itemCellArray[4].setCellValue(stockInOutItemDtoList.get(i).getSpecification());
itemCellArray[5].setCellValue(stockInOutItemDtoList.get(i).getCountingUnitName());
itemCellArray[6].setCellValue(stockInOutItemDtoList.get(i).getQuantity());
itemCellArray[7].setCellValue(stockInOutItemDtoList.get(i).getManufacturerName());
itemCellArray[8].setCellValue(stockInOutItemDtoList.get(i).getSupplierName());
if (null == stockInOutItemDtoList.get(i).getProductionDate())
itemCellArray[9].setCellValue("");
else
itemCellArray[9].setCellValue(outSdf.format(stockInOutItemDtoList.get(i).getProductionDate()));
itemCellArray[10].setCellValue(stockInOutItemDtoList.get(i).getValidPeriod());
if (null == stockInOutItemDtoList.get(i).getValidEndDate())
itemCellArray[11].setCellValue("");
else
itemCellArray[11].setCellValue(outSdf.format(stockInOutItemDtoList.get(i).getValidEndDate()));
if (null == stockInOutItemDtoList.get(i).getValidState()) {
itemCellArray[12].setCellValue("");
} else
itemCellArray[12].setCellValue(stockInOutItemDtoList.get(i).getValidState().getName());
itemCellArray[13].setCellValue(stockInOutItemDtoList.get(i).getSupplierContact());
itemCellArray[14].setCellValue(stockInOutItemDtoList.get(i).getSupplierTelephone());
}
response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode("耗材出库单数据.xlsx", "UTF-8"));
response.setContentType("application/octet-stream;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}