导出excel模板

导出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();
    }
}