依赖:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.9</version> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.51</version> </dependency> <!-- ############ poi ############## --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency>
//
java用POI设置Excel的列宽
HSSFSheet.setColumnWidth(int columnIndex, int width);
eg:
sheet.setColumnWidth(0, 252*width+323);//width=35
PoiExportUtils:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.icil.esolution.utils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @ClassName: PoiExportUtils * @Description: use export excel , some common code * @Author: Sea * @Date: 15 Oct 2018 2:26:38 PM * @Copyright: 2018 ICIL All rights reserved. */ public class PoiExportUtils { private static String STANDARD_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss"; public Workbook workbook = new XSSFWorkbook(); DataFormat format = null; { format = workbook.createDataFormat(); } public Sheet createXSheet(String sheetName) { // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称 Sheet sheet = null; if (StringUtils.isNotBlank(sheetName)) { sheet = workbook.createSheet(sheetName); } else { workbook.createSheet(); } //Freeze the title row /** * cellNum:表示要冻结的列数; rowNum:表示要冻结的行数; firstCellNum:表示被固定列右边第一列的列号; firstRollNum :表示被固定行下边第一列的行号; */ sheet.createFreezePane( 0, 1, 0, 1 ); return sheet; } public CellStyle getTitleCellStyle() { // 用于格式化单元格的数据 // DataFormat format = workbook.createDataFormat(); // 设置字体 Font font = workbook.createFont(); // font.setFontHeightInPoints((short) 20); // 字体高度 // font.setColor(Font.COLOR_RED); // 字体颜色 font.setFontName("黑体"); // 字体 font.setBold(true); // 加粗 // font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度 font.setItalic(true); // 是否使用斜体 font.setStrikeout(true); //是否使用划线 // 设置单元格类型 CellStyle titleCellStyle = workbook.createCellStyle(); titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框 titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框 titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框 titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框 // titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index); // // titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式 titleCellStyle.setFont(font); titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局:居中 titleCellStyle.setWrapText(true); return titleCellStyle; } public CellStyle getDateCellStyle() { CellStyle cellStyle1 = workbook.createCellStyle(); cellStyle1.setDataFormat(format.getFormat(STANDARD_TIME_FORMAT)); return cellStyle1; } /** * @ such as 0.000 | yyyy-MM-dd hh:mm:ss * @param formats * @return */ public CellStyle getDataCellStyle(String formats) { CellStyle cellStyle1 = workbook.createCellStyle(); cellStyle1.setDataFormat(format.getFormat(formats)); return cellStyle1; } }
POIUtils
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.icil.report.utils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; /** * ************************************************************************* * <PRE> * @ClassName: : POIUtils * * @Description: : * * @Creation Date : 8 May 2019 1:58:29 PM * * @Author : Sea * * * </PRE> ************************************************************************** */ public class POIUtils { public static CellStyle getTitleCellStyle(Workbook workbook) { // 用于格式化单元格的数据 // DataFormat format = workbook.createDataFormat(); // 设置字体 Font font = workbook.createFont(); // font.setFontHeightInPoints((short) 20); // 字体高度 // font.setColor(Font.COLOR_RED); // 字体颜色 font.setFontName("黑体"); // 字体 font.setBold(true); // 加粗 // font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度 font.setItalic(true); // 是否使用斜体 // font.setStrikeout(true); //是否使用划线 // 设置单元格类型 CellStyle titleCellStyle = workbook.createCellStyle(); titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框 titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框 titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框 titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框 // titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index); // // titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式 titleCellStyle.setFont(font); titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局:居中 titleCellStyle.setWrapText(true); return titleCellStyle; } /** * @font "黑体" "加粗" “斜体” * @param workbook * @return */ public static CellStyle getFontStyle(Workbook workbook,boolean isItalic) { // 设置字体 Font font = workbook.createFont(); // font.setFontHeightInPoints((short) 20); // 字体高度 // font.setColor(Font.COLOR_RED); // 字体颜色 font.setFontName("黑体"); // 字体 font.setBold(true); // 加粗 // font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度 font.setItalic(true); // 是否使用斜体 CellStyle titleCellStyle = workbook.createCellStyle(); titleCellStyle.setFont(font); titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局:居中 titleCellStyle.setWrapText(true); return titleCellStyle; } /** * @param sheet * @param rownum * @param cellColNum * @param cellValue * @param cellstyle */ public static void setCellValue(SXSSFSheet sheet, int rownum, int cellColNum, String cellValue, CellStyle cellstyle) { SXSSFRow row = sheet.getRow(rownum); if(null==sheet.getRow(rownum)){ row= sheet.createRow(rownum); } SXSSFCell cell= row.getCell(cellColNum); if(null==row.getCell(cellColNum)){ cell = row.createCell(cellColNum); } cell.setCellStyle(cellstyle); cell.setCellValue(cellValue); } }
test
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.sea.shan.poi; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; import com.sea.shan.utils.POIUtils; import com.sea.shan.utils.PoiExportUtils; public class POIUtilsTest { @Test public void readExcel() throws Exception { Workbook workBook = POIUtils.getWorkBook("/home/sea/Desktop/Test/airline-airport-country-code.xlsx"); Sheet sheetAt0 = workBook.getSheetAt(0); int lastRowNum = sheetAt0.getLastRowNum(); for (int i = 1; i <= lastRowNum; i++) { // get per row Row row = sheetAt0.getRow(i); if (row == null) { continue; } // String cellValue0 = POIUtils.getCellValue(row.getCell(0)); // String cellValue1 = POIUtils.getCellValue(row.getCell(1)); // String cellValue0 = POIUtils.getCellValues(row.getCell(0)); // String cellValue1 = POIUtils.getCellValues(row.getCell(1)); String cellValue0 = new DataFormatter().formatCellValue(row.getCell(0)); String cellValue1 = new DataFormatter().formatCellValue(row.getCell(1)); System.err.println(cellValue0 + "=" + cellValue1); } } @Test public void writeExcel() throws Exception { String sheetName = "Inventory"; PoiExportUtils poiExportUtils = new PoiExportUtils(); Sheet sheet = poiExportUtils.createXSheet(sheetName); // 2. set title //"seqId","partNo","partDesc","qtyInv","storeInDtLoc" String[] title = { "商品編號 ", " 商品描述 ", " 數量 ", " 數量單位 ", "入庫時間 " }; // set order by sheet.setAutoFilter(CellRangeAddress.valueOf("A1:E1")); // set content for (int contentColumn = 0; contentColumn <= 100; contentColumn++) { Row contentRow = sheet.createRow(contentColumn); // set title sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度 if (contentColumn == 0) { for (int titleColumn = 0; titleColumn < title.length; titleColumn++) { Cell titleCell = contentRow.createCell(titleColumn); titleCell.setCellStyle(poiExportUtils.getTitleCellStyle()); titleCell.setCellValue(title[titleColumn]); } continue; } // set content body int i = 0; contentRow.createCell(i++).setCellValue("cell" + i); contentRow.createCell(i++).setCellValue("cell" + i++); Cell cell2 = contentRow.createCell(i++); cell2.setCellValue("cell" + i++); contentRow.createCell(i++).setCellValue("cell" + i++); contentRow.createCell(i++).setCellValue("cell" + i++); } Workbook workbook = poiExportUtils.workbook; // 保存 String filename = "/home/sea/Desktop/workbook0oo1.xls"; if (workbook instanceof XSSFWorkbook) { filename = filename + "x"; } FileOutputStream out = new FileOutputStream(filename); workbook.write(out); out.close(); } /** * test 导出大量的数据 * @throws Exception */ @Test public void testWriteExcel() throws Exception { // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 // Workbook workbook = new XSSFWorkbook(5000); long start = System.currentTimeMillis(); SXSSFWorkbook workbook = new SXSSFWorkbook(10000);//内存中保留 10000 条数据,以免内存溢出,其余写入 硬盘 String sheetName = "test"; // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet SXSSFSheet sheet = workbook.createSheet(sheetName); Sheet sheet1 = workbook.createSheet("sa1"); Sheet sheet2 = workbook.createSheet("sa2"); Sheet sheet3 = workbook.createSheet("sa3"); // Freeze the title row /** * cellNum:表示要冻结的列数; rowNum:表示要冻结的行数; firstCellNum:表示被固定列右边第一列的列号; * firstRollNum :表示被固定行下边第一列的行号; */ sheet.createFreezePane(0, 1, 0, 1); sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1")); String[] title = { "商品編號 ", " 商品描述", " 數量", " 數量單位 ", "入庫時間 " }; // set content for (int contentColumn = 0; contentColumn <= 1040000; contentColumn++) { Row contentRow = sheet.createRow(contentColumn); // sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度 // ################# set title ################ if (contentColumn == 0) { for (int titleColumn = 0; titleColumn < title.length; titleColumn++) { Cell titleCell = contentRow.createCell(titleColumn); titleCell.setCellStyle(getTitleCellStyle(workbook)); titleCell.setCellValue(title[titleColumn]); } continue; } // ################# set title end ################ //********************* set body content ************************************** for (int titleColumn = 0; titleColumn < title.length+10; titleColumn++) { contentRow.createCell(titleColumn).setCellValue("cell" + contentColumn); } //********************* set body content ************************************** } FileOutputStream out = new FileOutputStream("/home/sea/Desktop/seatest.xlsx"); workbook.write(out); System.out.println("total cost time:"+(System.currentTimeMillis()-start)); } @Test public void testWriteExcel01() throws Exception { // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 // Workbook workbook = new XSSFWorkbook(5000); long start = System.currentTimeMillis(); SXSSFWorkbook workbook = new SXSSFWorkbook(10000);//内存中保留 10000 条数据,以免内存溢出,其余写入 硬盘 String sheetName = "test"; // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet String[] title = { "商品編號 ", " 商品描述", " 數量", " 數量單位 ", "入庫時間 " }; for(int i=0;i<6;i++) { SXSSFSheet sheet =workbook.createSheet(sheetName+i);; // Freeze the title row /** * cellNum:表示要冻结的列数; rowNum:表示要冻结的行数; firstCellNum:表示被固定列右边第一列的列号; * firstRollNum :表示被固定行下边第一列的行号; */ sheet.createFreezePane(0, 1, 0, 1); sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1")); // set content for (int contentColumn = 0; contentColumn <= 1040000; contentColumn++) { Row contentRow = sheet.createRow(contentColumn); // sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度 // ################# set title ################ if (contentColumn == 0) { for (int titleColumn = 0; titleColumn < title.length; titleColumn++) { Cell titleCell = contentRow.createCell(titleColumn); titleCell.setCellStyle(getTitleCellStyle(workbook)); titleCell.setCellValue(title[titleColumn]); } continue; } // ################# set title end ################ //********************* set body content ************************************** for (int titleColumn = 0; titleColumn < title.length+10; titleColumn++) { contentRow.createCell(titleColumn).setCellValue("cell" + contentColumn); } //********************* set body content ************************************** } } FileOutputStream out = new FileOutputStream("/home/sea/Desktop/seatest.xlsx"); workbook.write(out); System.out.println("total cost time:"+(System.currentTimeMillis()-start)); } public CellStyle getTitleCellStyle(Workbook workbook) { // 用于格式化单元格的数据 // DataFormat format = workbook.createDataFormat(); // 设置字体 Font font = workbook.createFont(); // font.setFontHeightInPoints((short) 20); // 字体高度 // font.setColor(Font.COLOR_RED); // 字体颜色 font.setFontName("黑体"); // 字体 font.setBold(true); // 加粗 // font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度 font.setItalic(true); // 是否使用斜体 // font.setStrikeout(true); //是否使用划线 // 设置单元格类型 CellStyle titleCellStyle = workbook.createCellStyle(); titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框 titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框 titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框 titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框 // titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index); // // titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式 titleCellStyle.setFont(font); titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局:居中 titleCellStyle.setWrapText(true); return titleCellStyle; } }
读取模板,填写数据
public XSSFWorkbook genManifestReport(List<SmallPkgDetailOdsDO> smallPkgs) throws Exception { InputStream ipts = this.getClass().getClassLoader().getResourceAsStream("template\\Sea.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(ipts); XSSFSheet sheet = workbook.getSheetAt(0); //set value for 3 行 4 列 date sheet.getRow(2).getCell(3).setCellValue(DateFormatUtils.format(System.currentTimeMillis(),TIME_PATTERN, Locale.CHINA)); int index = 0; for (int line = 6; line < smallPkgs.size()+6; line++) { Row contentRow = sheet.createRow(line); // sheet.autoSizeColumn((short) line); // 自动调整该列的宽度 for (int col = 0; col <10 ; col++) { contentRow.createCell(col).setCellValue(col+line); } } // FileOutputStream out = new FileOutputStream("seatest1.xlsx"); // workbook.write(out); return workbook; }
controller:
@GetMapping("/seaReport") public ResponseEntity getSeaReportIds(@RequestParam List<String> bagIds, HttpServletRequest request, HttpServletResponse response) throws Exception{ JSONObject result = seaReportHandler.getReportByBagIds(bagIds); if((ResponseCode.SUCCESS.getCode()+"").equalsIgnoreCase(result.getString(ResponseUtil.RESPONSE_CODE))){ XSSFWorkbook workbook = (XSSFWorkbook)result.get(ResponseUtil.RESPONSE_DATA); OutputStream output=response.getOutputStream(); response.reset(); String fileName="sea_Report" + DateUtils.formatDate(new Date(),DateUtils.STANDARD_NO_BLANK); response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx"); response.setContentType("application/msexcel"); workbook.write(output); return ResponseEntity.status(200).body(""); }else { // 失败,响应失败原因 return ResponseEntity.status(Integer.valueOf(result.getString(ResponseUtil.RESPONSE_CODE))).body(result); } }
通过Maping.json 填充数据:
public XSSFWorkbook genRpt(List<JSONObject> datas) throws Exception { //List<JSONObject> dataFlat = flatmapData(datas); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("sea"); CellStyle boldCellStyle = POIUtils.getBoldCellStyle(workbook); //title for (int col = 0; col < title.length; col++) { sheet.autoSizeColumn((short) 0); // 自动调整该列的宽度 // contentRow.createCell(col).setCellValue(title[col]); POIUtils.setCellValue(sheet,0,col,title[col],boldCellStyle); } // content for (int line = 0; line < datas.size(); line++) { Row contentRow = sheet.createRow(line+1); setValueByMap(datas.get(line),contentRow,title.length); } // FileOutputStream out = new FileOutputStream("seaRpt.xlsx"); // workbook.write(out); return workbook; }
private void setValueByMap(JSONObject data, Row contentRow,int colMax){ String OR = "\\|"; String AND = "&"; String EQ = "=="; JSONObject mapping = getMapping("templates/lazadaMainfestNewMapping.json"); //set value for(int col=0;col<colMax;col++) { String key = mapping.getJSONObject(col + "").keySet().stream().findFirst().orElseGet(() -> " "); String value = ""; //check default value if((key+"").contains(EQ)) { value = key.replaceAll(EQ,""); } // 可能需要匹配多个字段的值, 多个字段使用 or | 分割 else if((key+"").contains("|")) { for (String k : key.split(OR)) { Object vobj = JSONPath.eval(data, "$."+k.trim()); value = (vobj == null ? value : (vobj + "")); } }else { //多个字段使用 & 分割 for (String k : key.split(AND)) { Object vobj = JSONPath.eval(data, "$."+k.trim()); value += (vobj == null ? "" : vobj + "") + " "; } } contentRow.createCell(col).setCellValue(value); } }
seaNewMapping.json
mapping :
{ "注释": { "说明1": " 数字表示 excel 的 列数 , key: 我们DO字段(lazada small detail), value: excel 中具体的列的名字 (没有用到)", "说明2": " 如果一个字段需要多个字段匹配 用&连接 eg: sender.address1&sender.address2: SHRP ADD 1 ", "说明": " 如果需要给定默认值, 用 ==default_value 对于 or 用 |连接 , 默认后面的如果有值会覆盖前面的 " }, "0": { "orderCode": " xpl_code" }, "1": { "id": "ID == index ++ " }, "2": { "trackingNumber": "HAWB NO" }, "3": { "package.packageCode": "pkg ORD NO" }, "4": { "package.packageOrd": "REF_ORD_NO" }}
private static JSONObject getMapping(String path) { try { InputStream resourceAsStream = LAZADANewManifestReportHandler.class.getClassLoader().getResourceAsStream(path); String str = IOUtils.toString(resourceAsStream, "utf-8"); return JSON.parseObject(str); } catch (IOException e) { e.printStackTrace(); } return new JSONObject(); }