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

2022-05-26 04:40:15

依赖：

        <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.</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);
ｅｇ:
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( , , ,  ); 

        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();

        int lastRowNum = sheetAt0.getLastRowNum();

        for (int i = ; 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());

            String cellValue1 = new DataFormatter().formatCellValue(row.getCell());

            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 = ; contentColumn <= ; contentColumn++) {

            Row contentRow = sheet.createRow(contentColumn);

            // set title

            sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度

            if (contentColumn == ) {

                for (int titleColumn = ; titleColumn < title.length; titleColumn++) {

                    Cell titleCell = contentRow.createCell(titleColumn);

                    titleCell.setCellStyle(poiExportUtils.getTitleCellStyle());

                    titleCell.setCellValue(title[titleColumn]);

                }

                continue;

            }

            // set content body

            int i = ;

            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 条数据，以免内存溢出，其余写入 硬盘

        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(, , , );

        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1"));

        String[] title = { "商品編號 ", " 商品描述", "  數量", " 數量單位  ", "入庫時間 " };

        // set content

        for (int contentColumn = ; contentColumn <= ; contentColumn++)

        {

                Row contentRow = sheet.createRow(contentColumn);

//                sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度

                // ################# set title ################

                if (contentColumn == ) {

                    for (int titleColumn = ; 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 = ; titleColumn < title.length+; 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 条数据，以免内存溢出，其余写入 硬盘

        String sheetName = "test";

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet

        String[] title = { "商品編號 ", " 商品描述", "  數量", " 數量單位  ", "入庫時間 " };

        for(int i=;i<;i++)

        {

             SXSSFSheet sheet =workbook.createSheet(sheetName+i);;

             // Freeze the title row

             /**

              * cellNum:表示要冻结的列数； rowNum:表示要冻结的行数； firstCellNum:表示被固定列右边第一列的列号；

              * firstRollNum :表示被固定行下边第一列的行号;

              */

             sheet.createFreezePane(, , , );

             sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1"));

                                // set content

                                for (int contentColumn = ; contentColumn <= ; contentColumn++)

                                {

                                        Row contentRow = sheet.createRow(contentColumn);

                        //                sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度

                                        // ################# set title ################

                                        if (contentColumn == ) {

                                            for (int titleColumn = ; 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 = ; titleColumn < title.length+; 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;

    }

}
上一篇：VS2013全攻略(安装，技巧，快捷键，插件）！


下一篇：gcc编译命令行依赖库的指定顺序