依赖:
<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:
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
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
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; } }