• POIUtils 导出 poi Test 100w 600w 条数据


     依赖:

            <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>
    View Code

     //

    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;
        }
    
    }
    View Code

    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);
            }
    
        
        
    }
    View Code

     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;
        }
    
    }
    View Code
  • 相关阅读:
    521.最长特殊序列 I
    520.检查大写字母
    459.重复的子字符串
    Java 读取 .properties 文件的几种方式
    Idea 使用教程
    db2 with用法
    DB2 alter 新增/删除/修改列
    Bootstrap treegrid 实现树形表格结构
    Mysql 递归查询
    navicat for mysql 下载安装教程
  • 原文地址:https://www.cnblogs.com/lshan/p/10756437.html
Copyright © 2020-2023  润新知