導出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("公司領導");} elsecellArray[2].setCellValue(stockOutDto.getPrincipalDepartment());cellArray[4].setCellValue(stockOutDto.getPrincipal());if (null == stockOutDto.getStockOutDate())cellArray[6].setCellValue("");elsecellArray[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("");elseitemCellArray[9].setCellValue(outSdf.format(stockInOutItemDtoList.get(i).getProductionDate()));itemCellArray[10].setCellValue(stockInOutItemDtoList.get(i).getValidPeriod());if (null == stockInOutItemDtoList.get(i).getValidEndDate())itemCellArray[11].setCellValue("");elseitemCellArray[11].setCellValue(outSdf.format(stockInOutItemDtoList.get(i).getValidEndDate()));if (null == stockInOutItemDtoList.get(i).getValidState()) {itemCellArray[12].setCellValue("");} elseitemCellArray[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();} }

總結

以上是生活随笔為你收集整理的导出excel模板的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。