精华内容
参与话题
问答
  • Java导出Excel(Poi详解)

    万次阅读 多人点赞 2019-04-17 17:50:23
    直接可用的导出,自己根据下边的讲解自行修改,不修改直接用也可以! https://blog.csdn.net/w893932747/article/details/89370894 一、POI简介: Apache POI是Apache软件基金会的开放源码函式库,POI提供API给...

    福利:

    直接可用的导出,自己根据下边的讲解自行修改,不修改直接用也可以!

    https://blog.csdn.net/w893932747/article/details/89370894

    一、POI简介:

    Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

    HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。

    HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。

    二、POI结构说明

    包名称说明

    1. HSSF提供读写Microsoft Excel XLS格式档案的功能。
    2. XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
    3. HWPF提供读写Microsoft Word DOC格式档案的功能。
    4. HSLF提供读写Microsoft PowerPoint格式档案的功能。
    5. HDGF提供读Microsoft Visio格式档案的功能。
    6. HPBF提供读Microsoft Publisher格式档案的功能。
    7. HSMF提供读Microsoft Outlook格式档案的功能。

    三、POI常用类说明

            类名                            说明

    HSSFWorkbook           Excel的文档对象

    HSSFSheet                  Excel的表单

    HSSFRow                    Excel的行

    HSSFCell                     Excel的格子单元

    HSSFFont                    Excel字体

    HSSFDataFormat        格子单元的日期格式

    HSSFHeader               Excel文档Sheet的页眉

    HSSFFooter                Excel文档Sheet的页脚

    HSSFCellStyle            格子单元样式

    HSSFDateUtil             日期

    HSSFPrintSetup         打印

    HSSFErrorConstants   错误信息表

    四、Excel的基本操作

    • 得到Excel常用对象
    //获得Excel对象
        @Test
        public void testExcel1(){
            try {
                //获取系统文档
                POIFSFileSystem fspoi=new POIFSFileSystem(new FileInputStream("/Users/wangjun/temp/demo1.xls"));
                //创建工作薄对象
                HSSFWorkbook workbook=new HSSFWorkbook(fspoi);
                //创建工作表对象
                HSSFSheet sheet=workbook.getSheet("sheet1");
                //得到Excel表格
                HSSFRow row = sheet.getRow(1);
                //得到Excel工作表指定行的单元格
                HSSFCell cell = row.getCell(1);
                System.out.println(cell);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    • 创建Excel文档
        //创建Excel对象
        @Test
        public void testExcel2() throws IOException {
            //创建工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
            //创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            //创建工作表的行
            HSSFRow row = sheet.createRow(0);//设置第一行,从零开始
            row.createCell(2).setCellValue("aaaaaaaaaaaa");//第一行第三列为aaaaaaaaaaaa
            row.createCell(0).setCellValue(new Date());//第一行第一列为日期
            workbook.setSheetName(0,"sheet的Name");//设置sheet的Name
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }
    • 创建Excel文档,添加摘要信息

    这个信息添加完是有的,但是在mac下一直找不到怎么查看,有知道的童鞋希望不吝赐教,我是放到Win中查看的。

        //创建文档摘要信息
        @Test
        public void testExcel3() throws IOException {
            //创建HSSFWorkbook工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建HSSFSheet对象
            HSSFSheet sheet=workbook.createSheet("sheet1");
            //创建行的单元格,从0开始
            HSSFRow row = sheet.createRow(0);
            //创建单元格,从0开始
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("a");
            //一下为简写
            row.createCell(1).setCellValue("aa");
            row.createCell(2).setCellValue("aaa");
            row.createCell(3).setCellValue("aaaa");
    
            //创建文档信息
            workbook.createInformationProperties();
            //获取DocumentSummaryInformation对象
            DocumentSummaryInformation documentSummaryInformation = workbook.getDocumentSummaryInformation();
            documentSummaryInformation.setCategory("类别:Excel文件");//类别
            documentSummaryInformation.setManager("管理者:王军");//管理者
            documentSummaryInformation.setCompany("公司:Action");//公司
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }
    • 创建批注
        //创建批注
        @Test
        public void testExcel4() throws IOException {
            //创建Excel工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建Excel工作表对象
            HSSFSheet sheet = workbook.createSheet("wj");
            HSSFPatriarch patr = sheet.createDrawingPatriarch();
            //创建批注位置(row1-row3:直接理解为高度,col1-col2:直接理解为宽度)
            HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8, 3);
            //创建批注
            HSSFComment comment = patr.createCellComment(anchor);
            //设置批注内容
            comment.setString(new HSSFRichTextString("这是一个批注段落!"));
            //设置批注作者
            comment.setAuthor("wangjun");
            //设置批注默认显示
            comment.setVisible(true);
            HSSFCell cell = sheet.createRow(2).createCell(1);
            cell.setCellValue("测试");
            //把批注赋值给单元格
            cell.setCellComment(comment);
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }

    创建批注位置HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法参数说明:

    1. dx1 第1个单元格中x轴的偏移量
    2. dy1 第1个单元格中y轴的偏移量
    3. dx2 第2个单元格中x轴的偏移量
    4. dy2 第2个单元格中y轴的偏移量
    5. col1 第1个单元格的列号
    6. row1 第1个单元格的行号
    7. col2 第2个单元格的列号
    8. row2 第2个单元格的行号
    • 创建页眉和页脚
        //创建页眉页脚
        @Test
        public void testExcel5() throws IOException {
            //创建Excel工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
            HSSFHeader header =sheet.getHeader();//得到页眉
            header.setLeft("页眉左边");
            header.setRight("页眉右边");
            header.setCenter("页眉中间");
            HSSFFooter footer =sheet.getFooter();//得到页脚
            footer.setLeft("页脚左边");
            footer.setRight("页脚右边");
            footer.setCenter("页脚中间");
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }

    五、Excel的单元格操作

        //Excel的单元格操作
        @Test
        public void testExcel6() throws IOException {
            //创建Excel工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建Excel工作表对象
            HSSFSheet sheet = workbook.createSheet("wj");
            //创建行的单元格,从0开始
            HSSFRow row = sheet.createRow(0);
            //创建单元格
            HSSFCell cell=row.createCell(0);
            //设置值
            cell.setCellValue(new Date());
            //创建单元格样式
            HSSFCellStyle style=workbook.createCellStyle();
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            cell.setCellStyle(style);
            //设置保留2位小数--使用Excel内嵌的格式
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(12.3456789);
            style=workbook.createCellStyle();
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
            cell1.setCellStyle(style);
            //设置货币格式--使用自定义的格式
            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(12345.6789);
            style=workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("¥#,##0"));
            cell2.setCellStyle(style);
            //设置百分比格式--使用自定义的格式
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(0.123456789);
            style=workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
            cell3.setCellStyle(style);
            //设置中文大写格式--使用自定义的格式
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(12345);
            style=workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("[DbNum2][$-804]0"));
            cell4.setCellStyle(style);
            //设置科学计数法格式--使用自定义的格式
            HSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(12345);
            style=workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00"));
            cell5.setCellStyle(style);
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }

    HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的区别: 

           当使用Excel内嵌的(或者说预定义)的格式时,直接用HSSFDataFormat.getBuiltinFormat静态方法即可。当使用自己定义的格式时,必须先调用HSSFWorkbook.createDataFormat(),因为这时在底层会先找有没有匹配的内嵌FormatRecord,如果没有就会新建一个FormatRecord,所以必须先调用这个方法,然后你就可以用获得的HSSFDataFormat实例的getFormat方法了,当然相对而言这种方式比较麻烦,所以内嵌格式还是用HSSFDataFormat.getBuiltinFormat静态方法更加直接一些。

    合并单元格

        //合并单元格
        @Test
        public void testExcel7() throws IOException {
            //创建Excel工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建Excel工作表对象
            HSSFSheet sheet = workbook.createSheet("wj");
            //创建行的单元格,从0开始
            HSSFRow row = sheet.createRow(0);
            //创建单元格
            HSSFCell cell=row.createCell(0);
            //设置值
            cell.setCellValue(new Date());
            //合并列
            cell.setCellValue("合并列");
            CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5);
            sheet.addMergedRegion(region);
            //合并行
            HSSFCell cell1 = row.createCell(6);
            cell1.setCellValue("合并行");
            region=new CellRangeAddress(0, 5, 6, 6);
            sheet.addMergedRegion(region);
    
            //文档输出
            FileOutputStream out = new FileOutputStream("/Users/wangjun/temp/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
            workbook.write(out);
            out.close();
        }

    CellRangeAddress对象其实就是表示一个区域,其构造方法如下:

    CellRangeAddress(firstRow, lastRow, firstCol, lastCol),参数的说明:

    1. firstRow 区域中第一个单元格的行号
    2. lastRow 区域中最后一个单元格的行号
    3. firstCol 区域中第一个单元格的列号
    4. lastCol 区域中最后一个单元格的列号

    提示: 即使你没有用CreateRow和CreateCell创建过行或单元格,也完全可以直接创建区域然后把这一区域合并,Excel的区域合并信息是单独存储的,和RowRecord、ColumnInfoRecord不存在直接关系。

    (3)单元格对齐

    1. HSSFCell cell=row.createCell(0);
    2. cell.setCellValue("单元格对齐");
    3. HSSFCellStyle style=workbook.createCellStyle();
    4. style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
    5. style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
    6. style.setWrapText(true);//自动换行
    7. style.setIndention((short)5);//缩进
    8. style.setRotation((short)60);//文本旋转,这里的取值是从-90到90,而不是0-180度。
    9. cell.setCellStyle(style);

         水平对齐相关参数

    1. 如果是左侧对齐就是 HSSFCellStyle.ALIGN_FILL;
    2. 如果是居中对齐就是 HSSFCellStyle.ALIGN_CENTER;
    3. 如果是右侧对齐就是 HSSFCellStyle.ALIGN_RIGHT;
    4. 如果是跨列举中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION;
    5. 如果是两端对齐就是 HSSFCellStyle.ALIGN_JUSTIFY;
    6. 如果是填充就是 HSSFCellStyle.ALIGN_FILL;

         垂直对齐相关参数

    1. 如果是靠上就是 HSSFCellStyle.VERTICAL_TOP;
    2. 如果是居中就是 HSSFCellStyle.VERTICAL_CENTER;
    3. 如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM;
    4. 如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY;

    (4)使用边框

         边框和其他单元格设置一样也是调用CellStyle接口,CellStyle有2种和边框相关的属性,分别是:

    边框相关属性

    说明

    范例

    Border+ 方向

    边框类型

    BorderLeft, BorderRight 等

    方向 +BorderColor

    边框颜色

    TopBorderColor,BottomBorderColor 等

    1. HSSFCell cell=row.createCell(1);
    2. cell.setCellValue("设置边框");
    3. HSSFCellStyle style=workbook.createCellStyle();
    4. style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
    5. style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
    6. style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
    7. style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框
    8. style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色
    9. style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色
    10. style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色
    11. style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色
    12. cell.setCellStyle(style);

         其中边框类型分为以下几种:

    边框范例图

    对应的静态值

    HSSFCellStyle. BORDER_DOTTED

    HSSFCellStyle. BORDER_HAIR

    HSSFCellStyle. BORDER_DASH_DOT_DOT

    HSSFCellStyle. BORDER_DASH_DOT

    HSSFCellStyle. BORDER_DASHED

    HSSFCellStyle. BORDER_THIN

    HSSFCellStyle. BORDER_MEDIUM_DASH_DOT_DOT

    HSSFCellStyle. BORDER_SLANTED_DASH_DOT

    HSSFCellStyle. BORDER_MEDIUM_DASH_DOT

    HSSFCellStyle. BORDER_MEDIUM_DASHED

    HSSFCellStyle. BORDER_MEDIUM

    HSSFCellStyle. BORDER_THICK

    HSSFCellStyle. BORDER_DOUBLE

    (5)设置字体

    1. HSSFCell cell = row.createCell(1);
    2. cell.setCellValue("设置字体");
    3. HSSFCellStyle style = workbook.createCellStyle();
    4. HSSFFont font = workbook.createFont();
    5. font.setFontName("华文行楷");//设置字体名称
    6. font.setFontHeightInPoints((short)28);//设置字号
    7. font.setColor(HSSFColor.RED.index);//设置字体颜色
    8. font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
    9. font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
    10. font.setStrikeout(true);//设置删除线
    11. style.setFont(font);
    12. cell.setCellStyle(style);

    下划线选项值:

    单下划线 FontFormatting.U_SINGLE

    双下划线 FontFormatting.U_DOUBLE

    会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING

    会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING

    无下划线 FontFormatting.U_NONE

         上标下标选项值:

    上标 FontFormatting.SS_SUPER

    下标 FontFormatting.SS_SUB

    普通,默认值 FontFormatting.SS_NONE

    (6)背景和纹理

    1. HSSFCellStyle style = workbook.createCellStyle();
    2. style.setFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜色
    3. style.setFillBackgroundColor(HSSFColor.RED.index);//设置图案背景色
    4. style.setFillPattern(HSSFCellStyle.SQUARES);//设置图案样式
    5. cell.setCellStyle(style);

         图案样式及其对应的值:

    图案样式

    常量

    HSSFCellStyle. NO_FILL

    HSSFCellStyle. ALT_BARS

    HSSFCellStyle. FINE_DOTS

    HSSFCellStyle. SPARSE_DOTS

    HSSFCellStyle. LESS_DOTS

    HSSFCellStyle. LEAST_DOTS

    HSSFCellStyle. BRICKS

    HSSFCellStyle. BIG_SPOTS

    HSSFCellStyle. THICK_FORWARD_DIAG

    HSSFCellStyle. THICK_BACKWARD_DIAG

    HSSFCellStyle. THICK_VERT_BANDS

    HSSFCellStyle. THICK_HORZ_BANDS

    HSSFCellStyle. THIN_HORZ_BANDS

    HSSFCellStyle. THIN_VERT_BANDS

    HSSFCellStyle. THIN_BACKWARD_DIAG

    HSSFCellStyle. THIN_FORWARD_DIAG

    HSSFCellStyle. SQUARES

    HSSFCellStyle. DIAMONDS

    (7)设置宽度和高度

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(1);
    3. HSSFCell cell = row.createCell(1);
    4. cell.setCellValue("123456789012345678901234567890");
    5. sheet.setColumnWidth(1, 31 * 256);//设置第一列的宽度是31个字符宽度
    6. row.setHeightInPoints(50);//设置行的高度是50个点

         这里你会发现一个有趣的现象,setColumnWidth的第二个参数要乘以256,这是怎么回事呢?其实,这个参数的单位是1/256个字符宽度,也就是说,这里是把B列的宽度设置为了31个字符。

         设置行高使用HSSFRow对象的setHeight和setHeightInPoints方法,这两个方法的区别在于setHeightInPoints的单位是点,而setHeight的单位是1/20个点,所以setHeight的值永远是setHeightInPoints的20倍。

         你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽或行高。

    (8)判断单元格是否为日期

         判断单元格是否为日期类型,使用DateUtil.isCellDateFormatted(cell)方法,例如:

    1. HSSFCell cell = row.createCell(1);
    2. cell.setCellValue(new Date());//设置日期数据
    3. System.out.println(DateUtil.isCellDateFormatted(cell));//输出:false
    4. HSSFCellStyle style =workbook.createCellStyle();
    5. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    6. cell.setCellStyle(style);//设置日期样式
    7. System.out.println(DateUtil.isCellDateFormatted(cell));//输出:true

    4.使用Excel公式

    (1)基本计算

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. HSSFCell cell = row.createCell(0);
    4. cell.setCellFormula("2+3*4");//设置公式
    5. cell = row.createCell(1);
    6. cell.setCellValue(10);
    7. cell = row.createCell(2);
    8. cell.setCellFormula("A1*B1");//设置公式

    (2)SUM函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue(1);
    4. row.createCell(1).setCellValue(2);
    5. row.createCell(2).setCellValue(3);
    6. row.createCell(3).setCellValue(4);
    7. row.createCell(4).setCellValue(5);
    8. row = sheet.createRow(1);
    9. row.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1"
    10. row.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"

    (3)日期函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFCellStyle style=workbook.createCellStyle();
    3. style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
    4. HSSFRow row = sheet.createRow(0);
    5. Calendar date=Calendar.getInstance();//日历对象
    6. HSSFCell cell=row.createCell(0);
    7. date.set(2011,2, 7);
    8. cell.setCellValue(date.getTime());
    9. cell.setCellStyle(style);//第一个单元格开始时间设置完成
    10. cell=row.createCell(1);
    11. date.set(2014,4, 25);
    12. cell.setCellValue(date.getTime());
    13. cell.setCellStyle(style);//第一个单元格结束时间设置完成
    14. cell=row.createCell(3);
    15. cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")");
    16. cell=row.createCell(4);
    17. cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")");
    18. cell=row.createCell(5);
    19. cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"d\"),\"日\")");

    以上代码中的公式说明:

         DATEDIF(A1,B1,\"y\") :取得 A1 单元格的日期与 B1 单元格的日期的时间间隔。 ( “ y ” : 表示以年为单位 , ” m ”表示以月为单位 ; ” d ”表示以天为单位 ) 。

              CONCATENATE( str1,str2, … ) :连接字符串。

              更多 Excel 的日期函数可参考:http://tonyqus.sinaapp.com/archives/286

    (4)字符串相关函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue("abcdefg");
    4. row.createCell(1).setCellValue("aa bb cc dd ee fF GG");
    5. row.createCell(3).setCellFormula("UPPER(A1)");
    6. row.createCell(4).setCellFormula("PROPER(B1)");

        

    以上代码中的公式说明:

         UPPER( String ) :将文本转换成大写形式。

     

              PROPER( String ) :将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。

              更多 Excel 的字符串函数可参考:http://tonyqus.sinaapp.com/archives/289

    (5)IF函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue(12);
    4. row.createCell(1).setCellValue(23);
    5. row.createCell(3).setCellFormula("IF(A1>B1,\"A1大于B1\",\"A1小于等于B1\")");

         以上代码中的公式说明:

              IF(logical_test,value_if_true,value_if_false)用来用作逻辑判断。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式 ; value_if_true表示当表达式Logical_test的值为TRUE时的返回值;value_if_false表示当表达式Logical_test的值为FALSE时的返回值。

    (6)CountIf和SumIf函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue(57);
    4. row.createCell(1).setCellValue(89);
    5. row.createCell(2).setCellValue(56);
    6. row.createCell(3).setCellValue(67);
    7. row.createCell(4).setCellValue(60);
    8. row.createCell(5).setCellValue(73);
    9. row.createCell(7).setCellFormula("COUNTIF(A1:F1,\">=60\")");
    10. row.createCell(8).setCellFormula("SUMIF(A1:F1,\">=60\",A1:F1)");

         以上代码中的公式说明:

              COUNTIF(range,criteria):满足某条件的计数的函数。参数range:需要进行读数的计数;参数criteria:条件表达式,只有当满足此条件时才进行计数。

              SumIF(criteria_range, criteria,sum_range):用于统计某区域内满足某条件的值的求和。参数criteria_range:条件测试区域,第二个参数Criteria中的条件将与此区域中的值进行比较;参数criteria:条件测试值,满足条件的对应的sum_range项将进行求和计算;参数sum_range:汇总数据所在区域,求和时会排除掉不满足Criteria条件的对应的项。

    (7)Lookup函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue(0);
    4. row.createCell(1).setCellValue(59);
    5. row.createCell(2).setCellValue("不及格");
    6. row = sheet.createRow(1);
    7. row.createCell(0).setCellValue(60);
    8. row.createCell(1).setCellValue(69);
    9. row.createCell(2).setCellValue("及格");
    10. row = sheet.createRow(2);
    11. row.createCell(0).setCellValue(70);
    12. row.createCell(1).setCellValue(79);
    13. row.createCell(2).setCellValue("良好");
    14. row = sheet.createRow(3);
    15. row.createCell(0).setCellValue(80);
    16. row.createCell(1).setCellValue(100);
    17. row.createCell(2).setCellValue("优秀");
    18. row = sheet.createRow(4);
    19. row.createCell(0).setCellValue(75);
    20. row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)");
    21. row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");

        

    以上代码中的公式说明:

         LOOKUP(lookup_value,lookup_vector,result_vector) ,第一个参数:需要查找的内容,本例中指向 A5 单元格,也就是 75 ;第二个参数:比较对象区域,本例中的成绩需要与 $A$1:$A$4 中的各单元格中的值进行比较;第三个参数:查找结果区域,如果匹配到会将此区域中对应的数据返回。如本例中返回$C$1:$C$4 中对应的值。

     

     

    可能有人会问,字典中没有 75 对应的成绩啊,那么 Excel 中怎么匹配的呢?答案是模糊匹配,并且 LOOKUP 函数只支持模糊匹配。 Excel 会在 $A$1:$A$4 中找小于 75 的最大值,也就是 A3 对应的 70 ,然后将对应的 $C$1:$C$4 区域中的 C3 中的值返回,这就是最终结果“良好”的由来。

         VLOOKUP(lookup_value,lookup_area,result_col,is_fuzzy ) ,第一个参数:需要查找的内容,这里是 A5 单元格;第二个参数:需要比较的表,这里是 $A$1:$C$4 ,注意 VLOOKUP 匹配时只与表中的第一列进行匹配。第三个参数:匹配结果对应的列序号。这里要对应的是成绩列,所以为 3 。第四个参数:指明是否模糊匹配。例子中的 TRUE 表示模糊匹配,与上例中一样。匹配到的是第三行。如果将此参数改为 FALSE ,因为在表中的第 1 列中找不到 75 ,所以会报“#N/A ”的计算错误。

     

    另外,还有与 VLOKUP 类似的 HLOOKUP 。不同的是 VLOOKUP 用于在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。而HLOOKUP 用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。

    (8)随机数函数

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellFormula("RAND()");//取0-1之间的随机数
    4. row.createCell(1).setCellFormula("int(RAND()*100)");//取0-100之间的随机整数
    5. row.createCell(2).setCellFormula("rand()*10+10");//取10-20之间的随机实数
    6. row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");//随机小写字母
    7. row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");//随机大写字母
    8. //随机大小写字母
    9. row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");

         以上代码中的公式说明:

              上面几例中除了用到RAND函数以外,还用到了CHAR函数用来将ASCII码换为字母,INT函数用来取整。值得注意的是INT函数不会四舍五入,无论小数点后是多少都会被舍去。

    (9)获得公式的返回值

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(0);
    3. row.createCell(0).setCellValue(7);//A1
    4. row.createCell(1).setCellValue(8);//B1
    5. HSSFCell cell=row.createCell(2);
    6. cell.setCellFormula("A1*B1+14");
    7. HSSFFormulaEvaluator e = newHSSFFormulaEvaluator(workbook);
    8. cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的,则不必调用此方法
    9. System.out.println("公式计算结果:"+cell.getNumericCellValue());

    5.使用图形

    (1)画线

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
    3. HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)1, 0,(short)4, 4);
    4. HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
    5. line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型
    6. line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式
    7. line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

         通常,利用POI画图主要有以下几个步骤:

              1. 创建一个Patriarch(注意,一个sheet中通常只创建一个Patriarch对象);

              2. 创建一个Anchor,以确定图形的位置;

              3. 调用Patriarch创建图形;

              4. 设置图形类型(直线,矩形,圆形等)及样式(颜色,粗细等)。

         关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下:

              dx1:起始单元格的x偏移量,如例子中的0表示直线起始位置距B1单元格左侧的距离;

              dy1:起始单元格的y偏移量,如例子中的0表示直线起始位置距B1单元格上侧的距离;

              dx2:终止单元格的x偏移量,如例子中的0表示直线起始位置距E5单元格左侧的距离;

              dy2:终止单元格的y偏移量,如例子中的0表示直线起始位置距E5单元格上侧的距离;

              col1:起始单元格列序号,从0开始计算;

              row1:起始单元格行序号,从0开始计算,如例子中col1=1,row1=0就表示起始单元格为B1;

              col2:终止单元格列序号,从0开始计算;

              row2:终止单元格行序号,从0开始计算,如例子中col2=4,row2=4就表示起始单元格为E5;

         最后,关于LineStyle属性,有如下一些可选值,对应的效果分别如图所示:

    (2)画矩形

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
    3. HSSFClientAnchor anchor = new HSSFClientAnchor(255,122,255, 122, (short)1, 0,(short)4, 3);
    4. HSSFSimpleShape rec = patriarch.createSimpleShape(anchor);
    5. rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
    6. rec.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//设置边框样式
    7. rec.setFillColor(255, 0, 0);//设置填充色
    8. rec.setLineWidth(25400);//设置边框宽度
    9. rec.setLineStyleColor(0, 0, 255);//设置边框颜色

    (3)画圆形

         更改上例的代码如下:

             rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//设置图片类型

    (4)画Grid

         在POI中,本身没有画Grid(网格)的方法。但我们知道Grid其实就是由横线和竖线构成的,所在我们可以通过画线的方式来模拟画Grid。代码如下:

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. HSSFRow row = sheet.createRow(2);
    3. row.createCell(1);
    4. row.setHeightInPoints(240);
    5. sheet.setColumnWidth(2, 9000);
    6. int linesCount = 20;
    7. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    8. //因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间,这里采用比例的方式
    9. double xRatio = 1023.0 / (linesCount * 10);
    10. double yRatio = 255.0 / (linesCount * 10);
    11. // 画竖线
    12. int x1 = 0;
    13. int y1 = 0;
    14. int x2 = 0;
    15. int y2 = 200;
    16. for (int i = 0; i < linesCount; i++)
    17. {
    18.  HSSFClientAnchor a2 = new HSSFClientAnchor();
    19. a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),
    20.  (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),
    21.  (int) (y2 * yRatio));
    22.  HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
    23. shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    24. x1 += 10;
    25. x2 += 10;
    26. }
    27. // 画横线
    28. x1 = 0;
    29. y1 = 0;
    30. x2 = 200;
    31. y2 = 0;
    32. for (int i = 0; i < linesCount; i++)
    33. {
    34.  HSSFClientAnchor a2 = new HSSFClientAnchor();
    35. a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),
    36.  (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),
    37.  (int) (y2 * yRatio));
    38.  HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
    39. shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    40. y1 += 10;
    41. y2 += 10;
    42. }

    (5)插入图片

    1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
    2. FileInputStream stream=newFileInputStream("d:\\POI\\Apache.gif");
    3. byte[] bytes=new byte[(int)stream.getChannel().size()];
    4. stream.read(bytes);//读取图片到二进制数组
    5. int pictureIdx = workbook.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);
    6. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    7. HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)0, 0, (short)5, 5);
    8. HSSFPicture pict = patriarch.createPicture(anchor,pictureIdx);
    9. //pict.resize();//自动调节图片大小,图片位置信息可能丢失

    (6)从Excel文件提取图片

    1. InputStream inp = new FileInputStream(filePath);
    2. HSSFWorkbook workbook = new HSSFWorkbook(inp);//读取现有的Excel文件
    3. List<HSSFPictureData> pictures = workbook.getAllPictures();
    4. for(int i=0;i<pictures.size();i++)
    5. {
    6.  HSSFPictureData pic=pictures.get(i);
    7.  String ext = pic.suggestFileExtension();
    8.  if (ext.equals("png"))//判断文件格式
    9.  {
    10.  FileOutputStream png=newFileOutputStream("d:\\POI\\Apache.png");
    11. png.write(pic.getData());
    12. png.close();//保存图片
    13.  }
    14. }

    6.Excel表操作

    (1)设置默认工作表

    1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
    2. workbook.createSheet("Test0");// 创建工作表(Sheet)
    3. workbook.createSheet("Test1");// 创建工作表(Sheet)
    4. workbook.createSheet("Test2");// 创建工作表(Sheet)
    5. workbook.createSheet("Test3");// 创建工作表(Sheet)
    6. workbook.setActiveSheet(2);//设置默认工作表

    (2)重命名工作表

    1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
    2. workbook.createSheet("Test0");// 创建工作表(Sheet)
    3. workbook.createSheet("Test1");// 创建工作表(Sheet)
    4. workbook.createSheet("Test2");// 创建工作表(Sheet)
    5. workbook.createSheet("Test3");// 创建工作表(Sheet)
    6. workbook.setSheetName(2, "1234");//重命名工作表

    (3)调整表单显示比例

    1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
    2. HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
    3. HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
    4. HSSFSheet sheet3=workbook.createSheet("Test2");// 创建工作表(Sheet)
    5. sheet1.setZoom(1,2);//50%显示比例
    6. sheet2.setZoom(2,1);//200%显示比例
    7. sheet3.setZoom(1,10);//10%显示比例

    (4)显示/隐藏网格线

    1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
    2. HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
    3. HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
    4. sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
    5. sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false

    (5)遍历Sheet

    1. String filePath = "d:\\users\\lizw\\桌面\\POI\\sample.xls";
    2. FileInputStream stream = new FileInputStream(filePath);
    3. HSSFWorkbook workbook = new HSSFWorkbook(stream);//读取现有的Excel
    4. HSSFSheet sheet= workbook.getSheet("Test0");//得到指定名称的Sheet
    5. for (Row row : sheet)
    6. {
    7.  for (Cell cell : row)
    8.  {
    9.  System.out.print(cell + "\t");
    10.  }
    11.  System.out.println();
    12. }

    7.Excel行列操作

    (1)组合行、列

    1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
    2. sheet.groupRow(1, 3);//组合行
    3. sheet.groupRow(2, 4);//组合行
    4. sheet.groupColumn(2, 7);//组合列

         这里简单的介绍一下什么叫做组合:组合分为行组合和列组合,所谓行组合,就是让n行组合成一个集合,能够进行展开和合拢操作。

         使用POI也可以取消组合,例如:sheet.ungroupColumn(1, 3);//取消列组合

    (2)锁定列

         在Excel中,有时可能会出现列数太多或是行数太多的情况,这时可以通过锁定列来冻结部分列,不随滚动条滑动,方便查看。

    1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
    2. sheet.createFreezePane(2, 3, 15, 25);//冻结行列

         下面对CreateFreezePane的参数作一下说明:

              第一个参数表示要冻结的列数;

              第二个参数表示要冻结的行数,这里只冻结列所以为0;

              第三个参数表示右边区域可见的首列序号,从1开始计算;

              第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;

    (3)上下移动行

    1. FileInputStream stream = new FileInputStream(filePath);
    2. HSSFWorkbook workbook = new HSSFWorkbook(stream);
    3. HSSFSheet sheet = workbook.getSheet("Test0");
    4. sheet.shiftRows(2, 4, 2);//把第3行到第4行向下移动两行

         HSSFSheet.shiftRows(startRow, endRow, n)参数说明

              startRow:需要移动的起始行;

              endRow:需要移动的结束行;

              n:移动的位置,正数表示向下移动,负数表示向上移动;

     

    8.Excel的其他功能

    (1)设置密码

    1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
    2. HSSFRow row=sheet.createRow(1);
    3. HSSFCell cell=row.createCell(1);
    4. cell.setCellValue("已锁定");
    5. HSSFCellStyle locked = workbook.createCellStyle();
    6. locked.setLocked(true);//设置锁定
    7. cell.setCellStyle(locked);
    8. cell=row.createCell(2);
    9. cell.setCellValue("未锁定");
    10. HSSFCellStyle unlocked = workbook.createCellStyle();
    11. unlocked.setLocked(false);//设置不锁定
    12. cell.setCellStyle(unlocked);
    13. sheet.protectSheet("password");//设置保护密码

    (2)数据有效性

    1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
    2. HSSFRow row=sheet.createRow(0);
    3. HSSFCell cell=row.createCell(0);
    4. cell.setCellValue("日期列");
    5. CellRangeAddressList regions = new CellRangeAddressList(1, 65535,0, 0);//选定一个区域
    6. DVConstraint constraint = DVConstraint.createDateConstraint(DVConstraint . OperatorType . BETWEEN , "1993-01-01" ,"2014-12-31" , "yyyy-MM-dd" );
    7. HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
    8. dataValidate.createErrorBox("错误", "你必须输入一个时间!");
    9. sheet.addValidationData(dataValidate);

    CellRangeAddressList类表示一个区域,构造函数中的四个参数分别表示起始行序号,终止行序号,起始列序号,终止列序号。65535是一个Sheet的最大行数。另外,CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外,还可以设置成如下一些值,大家可以自己一个个去试看看效果:

    验证的数据类型也有几种选择,如下:

    (3)生成下拉式菜单

    1. CellRangeAddressList regions = new CellRangeAddressList(0, 65535,0, 0);
    2. DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "C++","Java", "C#" });
    3. HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
    4. sheet.addValidationData(dataValidate);

    (4)打印基本设置

    1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
    2. HSSFPrintSetup print = sheet.getPrintSetup();//得到打印对象
    3. print.setLandscape(false);//true,则表示页面方向为横向;否则为纵向
    4. print.setScale((short)80);//缩放比例80%(设置为0-100之间的值)
    5. print.setFitWidth((short)2);//设置页宽
    6. print.setFitHeight((short)4);//设置页高
    7. print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张设置
    8. print.setUsePage(true);//设置打印起始页码不使用"自动"
    9. print.setPageStart((short)6);//设置打印起始页码
    10. sheet.setPrintGridlines(true);//设置打印网格线
    11. print.setNoColor(true);//值为true时,表示单色打印
    12. print.setDraft(true);//值为true时,表示用草稿品质打印
    13. print.setLeftToRight(true);//true表示“先行后列”;false表示“先列后行”
    14. print.setNotes(true);//设置打印批注
    15. sheet.setAutobreaks(false);//Sheet页自适应页面大小

    更详细的打印设置请参考: http://tonyqus.sinaapp.com/archives/271

    (5)超链接

    1. HSSFSheet sheet = workbook.createSheet("Test0");
    2. CreationHelper createHelper = workbook.getCreationHelper();
    3. // 关联到网站
    4. Hyperlink link =createHelper.createHyperlink(Hyperlink.LINK_URL);
    5. link.setAddress("http://poi.apache.org/");
    6. sheet.createRow(0).createCell(0).setHyperlink(link);
    7. // 关联到当前目录的文件
    8. link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    9. link.setAddress("sample.xls");
    10. sheet.createRow(0).createCell(1).setHyperlink(link);
    11. // e-mail 关联
    12. link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    13. link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    14. sheet.createRow(0).createCell(2).setHyperlink(link);
    15. //关联到工作簿中的位置
    16. link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    17. link.setAddress("'Test0'!C3");//Sheet名为Test0的C3位置
    18. sheet.createRow(0).createCell(3).setHyperlink(link);

    9.POI对Word的基本操作

    (1)POI操作Word简介

    POI读写Excel功能强大、操作简单。但是POI操作时,一般只用它读取word文档,POI只能能够创建简单的word文档,相对而言POI操作时的功能太少。

    (2)POI创建Word文档的简单示例

    1. XWPFDocument doc = new XWPFDocument();// 创建Word文件
    2. XWPFParagraph p = doc.createParagraph();// 新建一个段落
    3. p.setAlignment(ParagraphAlignment.CENTER);// 设置段落的对齐方式
    4. p.setBorderBottom(Borders.DOUBLE);//设置下边框
    5. p.setBorderTop(Borders.DOUBLE);//设置上边框
    6. p.setBorderRight(Borders.DOUBLE);//设置右边框
    7. p.setBorderLeft(Borders.DOUBLE);//设置左边框
    8. XWPFRun r = p.createRun();//创建段落文本
    9. r.setText("POI创建的Word段落文本");
    10. r.setBold(true);//设置为粗体
    11. r.setColor("FF0000");//设置颜色
    12. p = doc.createParagraph();// 新建一个段落
    13. r = p.createRun();
    14. r.setText("POI读写Excel功能强大、操作简单。");
    15. XWPFTable table= doc.createTable(3, 3);//创建一个表格
    16. table.getRow(0).getCell(0).setText("表格1");
    17. table.getRow(1).getCell(1).setText("表格2");
    18. table.getRow(2).getCell(2).setText("表格3");
    19. FileOutputStream out = newFileOutputStream("d:\\POI\\sample.doc");
    20. doc.write(out);
    21. out.close();

    (3)POI读取Word文档里的文字

    1. FileInputStream stream = newFileInputStream("d:\\POI\\sample.doc");
    2. XWPFDocument doc = new XWPFDocument(stream);// 创建Word文件
    3. for(XWPFParagraph p : doc.getParagraphs())//遍历段落
    4. {
    5.  System.out.print(p.getParagraphText());
    6. }
    7. for(XWPFTable table : doc.getTables())//遍历表格
    8. {
    9.  for(XWPFTableRow row : table.getRows())
    10.  {
    11.  for(XWPFTableCell cell : row.getTableCells())
    12.  {
    13.  System.out.print(cell.getText());
    14.  }
    15.  }
    16. }

     

    展开全文
  • Java导出Excel(附完整源码)

    万次阅读 多人点赞 2019-05-12 22:00:11
    导出excel是咱Java开发的必备技能啦,之前项目有这个功能,现在将其独立出来,分享一下。 所用技术就是SpringBoot,然后是MVC架构模式。 废话不多说,直接上代码了,源码点末尾链接就可以下载。 (1)新建一个...

    导出excel是咱Java开发的必备技能啦,之前项目有这个功能,现在将其独立出来,分享一下。
    所用技术就是SpringBoot,然后是MVC架构模式。
    废话不多说,直接上代码了,源码点末尾链接就可以下载。
    (1)新建一个SpringBoot项目(可以官网https://start.spring.io/直接生成下载,然后导入eclipse),项目结构如下:
    在这里插入图片描述
    (2)修改pom文件,添加依赖;

    <dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    
    		<!-- 导出excel -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.14</version>
    		</dependency>
    
    		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.14</version>
    		</dependency>
    
    		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-contrib -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-contrib</artifactId>
    			<version>3.6</version>
    		</dependency>
    		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml-schemas</artifactId>
    			<version>3.17</version>
    		</dependency>
    		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-scratchpad</artifactId>
    			<version>3.17</version>
    		</dependency>
    

    (3)新建一个实体类,命名为User.java;

    package com.twf.springcloud.ExportExcel.po;
    import java.io.Serializable;
    public class User implements Serializable{
    	private static final long serialVersionUID = -9180229310895087286L;
    	private String name; // 姓名
    	private String sex; // 性别
    	private Integer age; // 年龄
    	private String phoneNo; // 手机号
    	private String address; // 地址
    	private String hobby; // 爱好
    	public User(String name, String sex, Integer age, String phoneNo, String address, String hobby) {
    		super();
    		this.name = name;
    		this.sex = sex;
    		this.age = age;
    		this.phoneNo = phoneNo;
    		this.address = address;
    		this.hobby = hobby;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public Integer getAge() {
    		return age;
    	}
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    	public String getPhoneNo() {
    		return phoneNo;
    	}
    	public void setPhoneNo(String phoneNo) {
    		this.phoneNo = phoneNo;
    	}
    	public String getAddress() {
    		return address;
    	}
    	public void setAddress(String address) {
    		this.address = address;
    	}
    	public String getHobby() {
    		return hobby;
    	}
    	public void setHobby(String hobby) {
    		this.hobby = hobby;
    	}
    	@Override
    	public String toString() {
    		return "User [name=" + name + ", sex=" + sex + ", age=" + age + ", phoneNo=" + phoneNo + ", address=" + address
    				+ ", hobby=" + hobby + "]";
    	}
    }
    

    (4)新建一个excel样式工具类;

    package com.twf.springcloud.ExportExcel.utils;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    /**
     * excle样式工具类
     */
    public class ExcelFormatUtil {
        /**
         * 设置报表头样式
         * @param workbook
         * @return
         */
    	public static CellStyle headSytle(SXSSFWorkbook workbook){
    		// 设置style1的样式,此样式运用在第二行
    		CellStyle style1 = workbook.createCellStyle();// cell样式
    		// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
    		style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
    		style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
    		// 设置单元格上、下、左、右的边框线
    		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    		Font font1 = workbook.createFont();// 创建一个字体对象
    		font1.setBoldweight((short) 10);// 设置字体的宽度
    		font1.setFontHeightInPoints((short) 10);// 设置字体的高度
    		font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
    		style1.setFont(font1);// 设置style1的字体
    		style1.setWrapText(true);// 设置自动换行
    		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
    		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
    		return style1;
    	}
    	/**
    	 * 设置报表体样式
    	 * @param wb
    	 * @return
    	 */
    	public static CellStyle contentStyle(SXSSFWorkbook wb){
           // 设置style1的样式,此样式运用在第二行
    		CellStyle style1 = wb.createCellStyle();// cell样式
    		// 设置单元格上、下、左、右的边框线
    		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    		style1.setWrapText(true);// 设置自动换行
    		style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向)
    		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
    		return style1;
    	}
    	/**
    	 * 设置报表标题样式
    	 * @param workbook
    	 * @return
    	 */
    	public static HSSFCellStyle titleSytle(HSSFWorkbook workbook,short color,short fontSize){
    		// 设置style1的样式,此样式运用在第二行
    				HSSFCellStyle style1 = workbook.createCellStyle();// cell样式
    				// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
    				//style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
    				//short fcolor = color;
    				if(color != HSSFColor.WHITE.index){
    					style1.setFillForegroundColor(color);// 设置填充色	
    				}
    				// 设置单元格上、下、左、右的边框线
    				style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    				style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    				style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    				style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    				HSSFFont font1 = workbook.createFont();// 创建一个字体对象
    				font1.setBoldweight(fontSize);// 设置字体的宽度
    				font1.setFontHeightInPoints(fontSize);// 设置字体的高度
    				font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
    				style1.setFont(font1);// 设置style1的字体
    				style1.setWrapText(true);// 设置自动换行
    				style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
    				style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
    				return style1;
    	}
    	/**
    	 *设置表头 
    	 * @param sheet
    	 */
    	public static void initTitleEX(SXSSFSheet sheet, CellStyle header,String title[],int titleLength[]) {
    		SXSSFRow row0 = sheet.createRow(0);
    		row0.setHeight((short) 800);	
    		for(int j = 0;j<title.length; j++) {
    			SXSSFCell cell = row0.createCell(j);
    			//设置每一列的字段名
    			cell.setCellValue(title[j]);
    			cell.setCellStyle(header);
    			sheet.setColumnWidth(j, titleLength[j]);
    		}
    	}
    }
    

    (5)新建一个Service接口;

    package com.twf.springcloud.ExportExcel.sevice;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import org.springframework.http.ResponseEntity;
    public interface ExportService {
    	ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response);
    }
    

    (6)新建一个Service接口实现类;

    package com.twf.springcloud.ExportExcel.sevice.impl;
    
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.http.ResponseEntity;
    import org.springframework.stereotype.Service;
    
    import com.twf.springcloud.ExportExcel.controller.BaseFrontController;
    import com.twf.springcloud.ExportExcel.po.User;
    import com.twf.springcloud.ExportExcel.sevice.ExportService;
    import com.twf.springcloud.ExportExcel.utils.ExcelFormatUtil;
    
    @Service
    public class ExportServiceImpl implements ExportService{
    	Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);
    
    	@Override
    	public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) {
    		try {
    			logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>");
    			
    			// 造几条数据
    			List<User> list = new ArrayList<>();
    			list.add(new User("唐三藏", "男", 30, "13411111111", "东土大唐", "取西经"));
    			list.add(new User("孙悟空", "男", 29, "13411111112", "菩提院", "打妖怪"));
    			list.add(new User("猪八戒", "男", 28, "13411111113", "高老庄", "偷懒"));
    			list.add(new User("沙悟净", "男", 27, "13411111114", "流沙河", "挑担子"));
    			
    			BaseFrontController baseFrontController = new BaseFrontController();
    			return baseFrontController.buildResponseEntity(export((List<User>) list), "用户表.xls");
    		} catch (Exception e) {
    			e.printStackTrace();
    			logger.error(">>>>>>>>>>导出excel 异常,原因为:" + e.getMessage());
    		}
    		return null;
    	}
    
    	private InputStream export(List<User> list) {
    		logger.info(">>>>>>>>>>>>>>>>>>>>开始进入导出方法>>>>>>>>>>");
    		ByteArrayOutputStream output = null;
    		InputStream inputStream1 = null;
    		SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中
    		SXSSFSheet sheet = wb.createSheet();
    		// 设置报表头样式
    		CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式
    		CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式
    		
    		// 每一列字段名
    		String[] strs = new String[] { "姓名", "性别", "年龄", "手机号", "地址","爱好" };
    		
    		// 字段名所在表格的宽度
    		int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 };
    		
    		// 设置表头样式
    		ExcelFormatUtil.initTitleEX(sheet, header, strs, ints);
    		logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");
    		
    		if (list != null && list.size() > 0) {
    			logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
    			for (int i = 0; i < list.size(); i++) {
    				User user = list.get(i);
    				SXSSFRow row = sheet.createRow(i + 1);
    				int j = 0;
    
    				SXSSFCell cell = row.createCell(j++);
    				cell.setCellValue(user.getName()); // 姓名
    				cell.setCellStyle(content);
    
    				cell = row.createCell(j++);
    				cell.setCellValue(user.getSex()); // 性别
    				cell.setCellStyle(content);
    
    				cell = row.createCell(j++);
    				cell.setCellValue(user.getAge()); // 年龄
    				cell.setCellStyle(content);
    
    				cell = row.createCell(j++);
    				cell.setCellValue(user.getPhoneNo()); // 手机号
    				cell.setCellStyle(content);
    
    				cell = row.createCell(j++);
    				cell.setCellValue(user.getAddress()); // 地址
    				cell.setCellStyle(content);
    				
    				cell = row.createCell(j++);
    				cell.setCellValue(user.getHobby()); // 爱好
    				cell.setCellStyle(content);
    			}
    			logger.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>");
    		}
    		try {
    			output = new ByteArrayOutputStream();
    			wb.write(output);
    			inputStream1 = new ByteArrayInputStream(output.toByteArray());
    			output.flush();
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			try {
    				if (output != null) {
    					output.close();
    					if (inputStream1 != null)
    						inputStream1.close();
    				}
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    		return inputStream1;
    	}
    }
    

    (7)新建一个下载文件的通用controller;

    package com.twf.springcloud.ExportExcel.controller;
    
    import java.io.InputStream; 
    import java.net.URLEncoder;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.poi.util.IOUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.ResponseEntity;
    import org.springframework.util.StringUtils;
    import org.springframework.validation.annotation.Validated;
    
    @Validated
    public class BaseFrontController {
    	
    	/**
    	 * slf4j 日志 logger
    	 */
    	protected final Logger logger = LoggerFactory.getLogger(this.getClass());
    
    	/**
    	 * 下载文件,纯SpringMVC的API来完成
    	 *
    	 * @param is 文件输入流
    	 * @param name 文件名称,带后缀名
    	 *
    	 * @throws Exception
    	 */
    	public ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws Exception {
    		logger.info(">>>>>>>>>>>>>>>>>>>>开始下载文件>>>>>>>>>>");
    		if (this.logger.isDebugEnabled())
    			this.logger.debug("download: " + name);
    		HttpHeaders header = new HttpHeaders();
    		String fileSuffix = name.substring(name.lastIndexOf('.') + 1);
    		fileSuffix = fileSuffix.toLowerCase();
    		
    		Map<String, String> arguments = new HashMap<String, String>();
    		arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    		arguments.put("xls", "application/vnd.ms-excel");
    		
    		String contentType = arguments.get(fileSuffix);
    		header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download"));
    		if(is!=null && is.available()!=0){
    			header.add("Content-Length", String.valueOf(is.available()));
    			header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
    			byte[] bs = IOUtils.toByteArray(is);
    			logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-有记录>>>>>>>>>>");
    			logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
    			return new ResponseEntity<>(bs, header, HttpStatus.OK);
    		}else{
    			String string="数据为空";
    			header.add("Content-Length", "0");
    			header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
    			logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-无记录>>>>>>>>>>");
    			logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
    			return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);
    		}
    	}
    }
    

    (8)新建一个controller,作为请求的入口;

    package com.twf.springcloud.ExportExcel.controller;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.http.ResponseEntity;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.twf.springcloud.ExportExcel.sevice.ExportService;
    
    @RestController
    @RequestMapping("/exportExcel/")
    public class ExportController {
    	
    	@Autowired
    	private ExportService exportService;
    
    	// 导出excel
    	@RequestMapping("exportExcel")
    	public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) {
    		return exportService.exportExcel(request,response);
    	}
    }
    

    (9)运行ExportExcelApplication,浏览器访问http://localhost:8080/exportExcel/exportExcel,可以下载excel,打开如下:
    在这里插入图片描述
    (10)项目源码,提取码为:wvfm。

    展开全文
  • java实现Excel的导入导出

    万次阅读 多人点赞 2018-01-03 21:40:25
    一.Excel读写技术 ...1.从数据库将数据导出excel表格 public class JxlExcel { public static void main(String[] args) { //创建Excel文件 String[] title= {"姓名","课程名","分数"}; File

    一.Excel读写技术



    区别:



    二.jxl读写基础代码


    1.从数据库将数据导出到excel表格

    public class JxlExcel {
    public static void main(String[] args) {
    	//创建Excel文件
    	String[] title= {"姓名","课程名","分数"};
    	File file=new File("f:/sheet1.xls");
    	try {
    		file.createNewFile();
    		//创建工作簿
    		WritableWorkbook  workbook=Workbook.createWorkbook(file);
    		//创建Sheet
    		WritableSheet sheet=workbook.createSheet("表格一", 20);
    		//第一行设置列名
    		Label label=null;
    		for (int i = 0; i < title.length; i++) {
    			label=new Label(i, 0, title[i]);//第一个参数为列,第二个为行
    			sheet.addCell(label);
    		}
    		Data data=new Data();
    		ResultSet rs=data.getString();
    		while(rs.next()) {
    			System.out.println(rs.getString(1));
    			label=new Label(0,rs.getRow(),rs.getString(1));
    			sheet.addCell(label);
    			label=new Label(1,rs.getRow(),rs.getString(2));
    			sheet.addCell(label);
    			label=new Label(2,rs.getRow(),rs.getString(3));
    			sheet.addCell(label);
    		}
    		workbook.write();
    		workbook.close();
    	} catch (Exception e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    }
    
    }
    

    2,从Excel表格中读取数据
    public class JxlRead {
    public static void main(String[] args) {
    	//创建workbook
    	try {
    		Workbook workbook=Workbook.getWorkbook(new File("f:/sheet1.xls"));
       //获取第一个表格
    		Sheet sheet=workbook.getSheet(0);
    	//获取数据
    		for (int i = 0; i < sheet.getRows(); i++) {
    			for (int j = 0; j < sheet.getColumns(); j++) {
    				Cell cell=sheet.getCell(j, i);
    				System.out.print(cell.getContents()+" ");
    			}
    			System.out.println();
    		}
    	}  catch (Exception e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    	
    }
    }
    

    三.Poi读写基础代码

    //所需jar包:commons-io-2.2.jar;poi-3.11-20141221.jar
    //通过poi进行excel导入数据
    public class PoiExcel {
    public static void main(String[] args) throws SQLException {
    	String title[]= {"名字","课程","分数"};
    	//1.创建Excel工作簿
    	HSSFWorkbook workbook=new HSSFWorkbook();
    	//2.创建一个工作表
    	HSSFSheet sheet=workbook.createSheet("sheet2");
    	//3.创建第一行
    	HSSFRow row=sheet.createRow(0);
    	HSSFCell cell=null;
    	//4.插入第一行数据
    	for (int i = 0; i < title.length; i++) {
    		cell=row.createCell(i);
    		cell.setCellValue(title[i]);
    	}
    	//5.追加数据
    	Data data=new Data();
    	ResultSet rs=data.getString();
    	while(rs.next()) {
    		HSSFRow row2=sheet.createRow(rs.getRow());
    		HSSFCell cell2=row2.createCell(0);
    		cell2.setCellValue(rs.getString(1));
    		cell2=row2.createCell(1);
    		cell2.setCellValue(rs.getString(2));
    		cell2=row2.createCell(2);
    		cell2.setCellValue(rs.getString(3));
    	}
    	//创建一个文件,将Excel内容存盘
    	File file=new File("e:/sheet2.xls");
    	try {
    		file.createNewFile();
    		FileOutputStream stream=FileUtils.openOutputStream(file);
    		workbook.write(stream);
    		stream.close();
    	} catch (IOException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    	
    }
    }
    

    //将Excel表中内容读取
    public class PoiRead {
    public static void main(String[] args) {
    	//需要解析的Excel文件
    	File file=new  File("e:/sheet2.xls");
    	try {
    		//获取工作簿
    		FileInputStream fs=FileUtils.openInputStream(file);
    		HSSFWorkbook workbook=new HSSFWorkbook(fs);
    	    //获取第一个工作表
    		HSSFSheet hs=workbook.getSheetAt(0);
    		//获取Sheet的第一个行号和最后一个行号
    	   int last=hs.getLastRowNum();
    	   int first=hs.getFirstRowNum();
    	   //遍历获取单元格里的信息
    	   for (int i = first; i <last; i++) {
    		HSSFRow row=hs.getRow(i);
    		int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号
    		int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号
    		for (int j = firstCellNum; j <lastCellNum; j++) {
    			HSSFCell cell=row.getCell(j);
    			String value=cell.getStringCellValue();
    			System.out.print(value+" ");
    		}
    		System.out.println();
    	}
    	} catch (IOException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    }
    }


    如果Excel版本过高则需要改写用XSSF
    public class PoiExpExcel2 {
    
    	/**
    	 * POI生成Excel文件
    	 * @author David
    	 * @param args
    	 */
    	public static void main(String[] args) {
    
    		String[] title = {"id","name","sex"};
    		
    		//创建Excel工作簿
    		XSSFWorkbook workbook = new XSSFWorkbook();
    		//创建一个工作表sheet
    		Sheet sheet = workbook.createSheet();
    		//创建第一行
    		Row row = sheet.createRow(0);
    		Cell cell = null;
    		//插入第一行数据 id,name,sex
    		for (int i = 0; i < title.length; i++) {
    			cell = row.createCell(i);
    			cell.setCellValue(title[i]);
    		}
    		//追加数据
    		for (int i = 1; i <= 10; i++) {
    			Row nextrow = sheet.createRow(i);
    			Cell cell2 = nextrow.createCell(0);
    			cell2.setCellValue("a" + i);
    			cell2 = nextrow.createCell(1);
    			cell2.setCellValue("user" + i);
    			cell2 = nextrow.createCell(2);
    			cell2.setCellValue("男");
    		}
    		//创建一个文件
    		File file = new File("e:/poi_test.xlsx");
    		try {
    			file.createNewFile();
    			//将Excel内容存盘
    			FileOutputStream stream = FileUtils.openOutputStream(file);
    			workbook.write(stream);
    			stream.close();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    }


    四.定制导入模板

    1.首先准备好模板的.xml文件,然后导入所需的jar包
    例子:student.xml文件

    <?xml version="1.0" encoding="UTF-8"?>
    <excel id="student" code="student" name="学生信息导入">
        <colgroup>
            <col index="A" width='17em'></col>
            <col index="B" width='17em'></col>
            <col index="C" width='17em'></col>
            <col index="D" width='17em'></col>
            <col index="E" width='17em'></col>
            <col index="F" width='17em'></col>        
        </colgroup>
        <title>
            <tr height="16px">
                <td rowspan="1" colspan="6" value="学生信息导入" />
            </tr>
        </title>
        <thead>
            <tr height="16px">
            	<th value="编号" />
                <th value="姓名" />
                <th value="年龄" />
                <th value="性别" />
                <th value="出生日期" />
                <th value=" 爱好" />            
            </tr>
        </thead>
        <tbody>
            <tr height="16px" firstrow="2" firstcol="0" repeat="5">
                <td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
                <td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
                <td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
                <td type="enum" format="男,女" isnullable="true" /><!--性别 -->
                <td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
                <td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
            </tr>
        </tbody>
    </excel>

    所需jar包:
    commons-lang3-3.1.jar
    jdom.jar
    poi-3.11-20141221.jar
    commons-io-2.2.jar

    java代码:
    //准备工作:导入相关jar包commons-lang3-3.1.jar,jdom.jar,poi-3.11-20141221.jar
    public class CreateTemp {
    public static void main(String[] args) {
    	//获取解析Xml路径
    	String path=System.getProperty("user.dir")+"/student.xml";
    	File file=new File(path);
    	SAXBuilder builder=new SAXBuilder();
    	//解析xml文件
    	try {
    		Document document=builder.build(file);
    	//创建Excel
    		HSSFWorkbook workbook=new HSSFWorkbook();
    	//创建表格
    		HSSFSheet sheet=workbook.createSheet("sheet0");
    	//获取Xml文件的根节点
    		Element root=document.getRootElement();
    	//获取模板名称
    		String tempName=root.getAttributeValue("name");
    	//设置列宽
    		Element colgroup=root.getChild("colgroup");
    		setColumnWidth(sheet,colgroup);
    	//设置标题
    		int rownum = 0;
    		int column = 0;
    		Element title=root.getChild("title");
    		List<Element> trs=title.getChildren("tr");
    		for (int i = 0; i <trs.size(); i++) {
    			Element tr=trs.get(i);
    			List<Element> tds=tr.getChildren("td");
    			HSSFRow row=sheet.createRow(rownum);
    			HSSFCellStyle cellStyle=workbook.createCellStyle();//创建单元格格式
    			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//标题居中
    			for (int j = 0; j < tds.size(); j++) {
    			   Element td=tds.get(j);
    			   HSSFCell cell=row.createCell(j);
    			   Attribute rowspan=td.getAttribute("rowspan");
    			   Attribute colspan=td.getAttribute("colspan");
    			   Attribute value=td.getAttribute("value");
    			   if (value!=null) {
    				String content=value.getValue();
    			
    				cell.setCellValue(content);
    				int rspan=rowspan.getIntValue()-1;
    				int cspan=colspan.getIntValue()-1;
    				//设置字体
    				HSSFFont font=workbook.createFont();
    				font.setFontName("仿宋_GB2312");
    				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
    //				font.setFontHeight((short)12);
    				font.setFontHeightInPoints((short)12);
    				cellStyle.setFont(font);
    				cell.setCellStyle(cellStyle);
    				//合并单元格居中
    				sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
    			}
    			   
    			}
    			rownum++;
    			
    		}
    		//设置表头
    		Element thead=root.getChild("thead");
    		trs=thead.getChildren("tr");
    		for (int i = 0; i < trs.size(); i++) {
    			Element tr=trs.get(i);
    			HSSFRow row=sheet.createRow(rownum);
    			 List<Element> ths=tr.getChildren("th");
    			 for (int j = 0; j <ths.size(); j++) {
    				Element th=ths.get(j);
    				HSSFCell cell=row.createCell(j);
    			     Attribute value=th.getAttribute("value");
    			     if (value!=null) {
    			    	 String content=value.getValue();
                        cell.setCellValue(content);	
                       
    				}
    			 }
    			 rownum++;
    		}
    		
    		//设置数据区域样式
    		     Element  tbody = root.getChild("tbody");
    		     Element tr=tbody.getChild("tr");
    		     int repeat=tr.getAttribute("repeat").getIntValue();
    		     List<Element> tds=tr.getChildren("td");
    		     for (int i = 0; i < repeat; i++) {
    				HSSFRow row=sheet.createRow(rownum);
    				for (int j = 0; j < tds.size(); j++) {
    					Element td=tds.get(j);
    					HSSFCell cell=row.createCell(j);
    					setType(workbook,cell,td);
    				}
    			}
    		     rownum++;
    		//生成Excel导入模板
    		     File tempFile=new File("e:/"+tempName+".xls");
    		     tempFile.delete();
    		     tempFile.createNewFile();
    		     FileOutputStream fos=FileUtils.openOutputStream(tempFile);
    		     workbook.write(fos);
    		     fos.close();
    		     
    	}  catch (Exception e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    }
    
    private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {
    	Attribute typeAttr = td.getAttribute("type");
    	String type = typeAttr.getValue();
    	HSSFDataFormat format = workbook.createDataFormat();
    	HSSFCellStyle cellStyle = workbook.createCellStyle();
    	if("NUMERIC".equalsIgnoreCase(type)){
    		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    		Attribute formatAttr = td.getAttribute("format");
    		String formatValue = formatAttr.getValue();
    		formatValue = StringUtils.isNotBlank(formatValue)? formatValue : "#,##0.00";
    		cellStyle.setDataFormat(format.getFormat(formatValue));
    	}else if("STRING".equalsIgnoreCase(type)){
    		cell.setCellValue("");
    		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    		cellStyle.setDataFormat(format.getFormat("@"));
    	}else if("DATE".equalsIgnoreCase(type)){
    		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    		cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
    	}else if("ENUM".equalsIgnoreCase(type)){
    		CellRangeAddressList regions = 
    			new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), 
    					cell.getColumnIndex(), cell.getColumnIndex());
    		
    		Attribute enumAttr = td.getAttribute("format");
    		String enumValue = enumAttr.getValue();
    		//加载下拉列表内容
    		DVConstraint constraint = 
    			DVConstraint.createExplicitListConstraint(enumValue.split(","));
    		//数据有效性对象
    		HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
    		workbook.getSheetAt(0).addValidationData(dataValidation);
    	}
    	cell.setCellStyle(cellStyle);
    	
    }
    
    private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
    	List<Element> cols=colgroup.getChildren("col");//获取col的节点
    	for (int i = 0; i < cols.size(); i++) {
    		Element col=cols.get(i);
    		Attribute width=col.getAttribute("width");//获取每列中的width属性
    		String unit = width.getValue().replaceAll("[0-9,\\.]", "");//单位
    		String value = width.getValue().replaceAll(unit, "");//数值
    		int v=0;
    		if(StringUtils.isBlank(unit) || "px".endsWith(unit)){
    			v = Math.round(Float.parseFloat(value) * 37F);
    		}else if ("em".endsWith(unit)){
    			v = Math.round(Float.parseFloat(value) * 267.5F);
    		}//对单位进行判断
    		sheet.setColumnWidth(i, v);
    	}
    	
    }
    }
    



    展开全文
  • 阿里开源(EasyExcel)---导出EXCEL

    万次阅读 多人点赞 2019-03-15 17:36:53
    导出是后台管理系统的常用功能,当数据量特别大的时候会内存溢出和卡顿页面,曾经自己封装过一个导出,POI百万级大数据量EXCEL导出采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决...

    一. 简介

             导出是后台管理系统的常用功能,当数据量特别大的时候会内存溢出和卡顿页面,曾经自己封装过一个导出,POI百万级大数据量EXCEL导出 采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。不过一是存在封装不太友好使用不方便的问题,二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题,以及存在内存溢出的隐患。无意间查询到阿里开源的EasyExcel框架,发现可以将解析的EXCEL的内存占用控制在KB级别,并且绝对不会内存溢出(内部实现待研究),还有就是速度极快, 大概100W条记录,十几个字段, 只需要70秒即可完成下载。遂抛弃自己封装的,转战研究阿里开源的EasyExcel. 不过 说实话,当时自己封装的那个还是有些技术含量的,例如 外观模式,模板方法模式,以及委托思想,组合思想,可以看看。

             EasyExcel的github地址是:https://github.com/alibaba/easyexcel 

    二. 案例

    2.1 POM依赖

            <!-- 阿里开源EXCEL -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>1.1.1</version>
            </dependency>

    2.2 POJO对象

    package com.authorization.privilege.excel;
    
    import java.util.Date;
    
    /**
     * @author qjwyss
     * @date 2019/3/15
     * @description
     */
    public class User {
    
        private String uid;
        private String name;
        private Integer age;
        private Date birthday;
    
        public User() {
        }
    
        public User(String uid, String name, Integer age, Date birthday) {
            this.uid = uid;
            this.name = name;
            this.age = age;
            this.birthday = birthday;
        }
    
        public String getUid() {
            return uid;
        }
    
        public void setUid(String uid) {
            this.uid = uid;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    }
    

     

    2.3 测试环境

    2.3.1 数据量少的(20W以内吧):一个SHEET一次查询导出

        /**
         * 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中
         * 注意: 一次性查询出来的记录数量不宜过大,不会内存溢出即可。
         *
         * @throws IOException
         */
        @Test
        public void writeExcelOneSheetOnceWrite() throws IOException {
    
            // 生成EXCEL并指定输出路径
            OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
            // 设置SHEET
            Sheet sheet = new Sheet(1, 0);
            sheet.setSheetName("sheet1");
    
            // 设置标题
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("用户ID"));
            titles.add(Arrays.asList("名称"));
            titles.add(Arrays.asList("年龄"));
            titles.add(Arrays.asList("生日"));
            table.setHead(titles);
    
            // 查询数据导出即可 比如说一次性总共查询出100条数据
            List<List<String>> userList = new ArrayList<>();
            for (int i = 0; i < 100; i++) {
                userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
            }
    
            writer.write0(userList, sheet, table);
            writer.finish();
        }

     

    2.3.2 数据量适中(100W以内): 一个SHEET分批查询导出

        /**
         * 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
         * 注意:
         * 每次查询出来的记录数量不宜过大,根据内存大小设置合理的每次查询记录数,不会内存溢出即可。
         * 数据量不能超过一个SHEET存储的最大数据量105W
         *
         * @throws IOException
         */
        @Test
        public void writeExcelOneSheetMoreWrite() throws IOException {
    
            // 生成EXCEL并指定输出路径
            OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
            // 设置SHEET
            Sheet sheet = new Sheet(1, 0);
            sheet.setSheetName("sheet1");
    
            // 设置标题
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("用户ID"));
            titles.add(Arrays.asList("名称"));
            titles.add(Arrays.asList("年龄"));
            titles.add(Arrays.asList("生日"));
            table.setHead(titles);
    
            // 模拟分批查询:总记录数50条,每次查询20条,  分三次查询 最后一次查询记录数是10
            Integer totalRowCount = 50;
            Integer pageSize = 20;
            Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
    
            // 注: 此处仅仅为了模拟数据,实用环境不需要将最后一次分开,合成一个即可, 参数为: currentPage = i+1;  pageSize = pageSize
            for (int i = 0; i < writeCount; i++) {
    
                // 前两次查询 每次查20条数据
                if (i < writeCount - 1) {
    
                    List<List<String>> userList = new ArrayList<>();
                    for (int j = 0; j < pageSize; j++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                    }
                    writer.write0(userList, sheet, table);
    
                } else if (i == writeCount - 1) {
    
                    // 最后一次查询 查多余的10条记录
                    List<List<String>> userList = new ArrayList<>();
                    Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
                    for (int j = 0; j < lastWriteRowCount; j++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                    }
                    writer.write0(userList, sheet, table);
                }
            }
    
            writer.finish();
        }

     

    2.3.3 数据量很大(几百万都行): 多个SHEET分批查询导出 

        /**
         * 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中
         * 注意:
         * perSheetRowCount % pageSize要能整除  为了简洁,非整除这块不做处理
         * 每次查询出来的记录数量不宜过大,根据内存大小设置合理的每次查询记录数,不会内存溢出即可。
         *
         * @throws IOException
         */
        @Test
        public void writeExcelMoreSheetMoreWrite() throws IOException {
    
            // 生成EXCEL并指定输出路径
            OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
            // 设置SHEET名称
            String sheetName = "测试SHEET";
    
            // 设置标题
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("用户ID"));
            titles.add(Arrays.asList("名称"));
            titles.add(Arrays.asList("年龄"));
            titles.add(Arrays.asList("生日"));
            table.setHead(titles);
    
            // 模拟分批查询:总记录数250条,每个SHEET存100条,每次查询20条  则生成3个SHEET,前俩个SHEET查询次数为5, 最后一个SHEET查询次数为3 最后一次写的记录数是10
            // 注:该版本为了较少数据判断的复杂度,暂时perSheetRowCount要能够整除pageSize, 不去做过多处理  合理分配查询数据量大小不会内存溢出即可。
            Integer totalRowCount = 250;
            Integer perSheetRowCount = 100;
            Integer pageSize = 20;
            Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
            Integer previousSheetWriteCount = perSheetRowCount / pageSize;
            Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?
                    previousSheetWriteCount :
                    (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));
    
            for (int i = 0; i < sheetCount; i++) {
    
                // 创建SHEET
                Sheet sheet = new Sheet(i, 0);
                sheet.setSheetName(sheetName + i);
    
                if (i < sheetCount - 1) {
    
                    // 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行  2个SHEET合计200行  实用环境:参数: currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize
                    for (int j = 0; j < previousSheetWriteCount; j++) {
                        List<List<String>> userList = new ArrayList<>();
                        for (int k = 0; k < 20; k++) {
                            userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                        }
                        writer.write0(userList, sheet, table);
                    }
    
                } else if (i == sheetCount - 1) {
    
                    // 最后一个SHEET 实用环境不需要将最后一次分开,合成一个即可, 参数为: currentPage = i+1;  pageSize = pageSize
                    for (int j = 0; j < lastSheetWriteCount; j++) {
    
                        // 前俩次查询 每次查询20条
                        if (j < lastSheetWriteCount - 1) {
    
                            List<List<String>> userList = new ArrayList<>();
                            for (int k = 0; k < 20; k++) {
                                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                            }
                            writer.write0(userList, sheet, table);
    
                        } else if (j == lastSheetWriteCount - 1) {
    
                            // 最后一次查询 将剩余的10条查询出来
                            List<List<String>> userList = new ArrayList<>();
                            Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
                            for (int k = 0; k < lastWriteRowCount; k++) {
                                userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
                            }
                            writer.write0(userList, sheet, table);
    
                        }
                    }
                }
            }
    
            writer.finish();
        }

     

    2.4 生产环境

    2.4.0 Excel常量类

    package com.authorization.privilege.constant;
    
    /**
     * @author qjwyss
     * @date 2019/3/18
     * @description EXCEL常量类
     */
    public class ExcelConstant {
    
        /**
         * 每个sheet存储的记录数 100W
         */
        public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    
        /**
         * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
         */
        public static final Integer PER_WRITE_ROW_COUNT = 200000;
    
    }
    

    注: 为了书写方便,此处俩个必须要整除,可以省去很多不必要的判断。  另外如果自己测试,可以改为100,20。 

     

    2.4.1 数据量少的(20W以内吧):一个SHEET一次查询导出 

        @Override
        public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    
            ServletOutputStream out = null;
            try {
                out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
                // 设置EXCEL名称
                String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
    
                // 设置SHEET名称
                Sheet sheet = new Sheet(1, 0);
                sheet.setSheetName("系统列表sheet1");
    
                // 设置标题
                Table table = new Table(1);
                List<List<String>> titles = new ArrayList<List<String>>();
                titles.add(Arrays.asList("系统名称"));
                titles.add(Arrays.asList("系统标识"));
                titles.add(Arrays.asList("描述"));
                titles.add(Arrays.asList("状态"));
                titles.add(Arrays.asList("创建人"));
                titles.add(Arrays.asList("创建时间"));
                table.setHead(titles);
    
                // 查数据写EXCEL
                List<List<String>> dataList = new ArrayList<>();
                List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                    sysSystemVOList.forEach(eachSysSystemVO -> {
                        dataList.add(Arrays.asList(
                                eachSysSystemVO.getSystemName(),
                                eachSysSystemVO.getSystemKey(),
                                eachSysSystemVO.getDescription(),
                                eachSysSystemVO.getState().toString(),
                                eachSysSystemVO.getCreateUid(),
                                eachSysSystemVO.getCreateTime().toString()
                        ));
                    });
                }
                writer.write0(dataList, sheet, table);
    
                // 下载EXCEL
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                writer.finish();
                out.flush();
    
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
    
            return ResultVO.getSuccess("导出系统列表EXCEL成功");
        }

     

    2.4.2 数据量适中(100W以内): 一个SHEET分批查询导出 

        @Override
        public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    
            ServletOutputStream out = null;
            try {
                out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
                // 设置EXCEL名称
                String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
    
                // 设置SHEET名称
                Sheet sheet = new Sheet(1, 0);
                sheet.setSheetName("系统列表sheet1");
    
                // 设置标题
                Table table = new Table(1);
                List<List<String>> titles = new ArrayList<List<String>>();
                titles.add(Arrays.asList("系统名称"));
                titles.add(Arrays.asList("系统标识"));
                titles.add(Arrays.asList("描述"));
                titles.add(Arrays.asList("状态"));
                titles.add(Arrays.asList("创建人"));
                titles.add(Arrays.asList("创建时间"));
                table.setHead(titles);
    
                // 查询总数并 【封装相关变量 这块直接拷贝就行 不要改动】
                Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
                Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
    
                // 写数据 这个i的最大值直接拷贝就行了 不要改
                for (int i = 0; i < writeCount; i++) {
                    List<List<String>> dataList = new ArrayList<>();
    
                    // 此处查询并封装数据即可 currentPage, pageSize这个变量封装好的 不要改动
                    PageHelper.startPage(i + 1, pageSize);
                    List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                    if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                        sysSystemVOList.forEach(eachSysSystemVO -> {
                            dataList.add(Arrays.asList(
                                    eachSysSystemVO.getSystemName(),
                                    eachSysSystemVO.getSystemKey(),
                                    eachSysSystemVO.getDescription(),
                                    eachSysSystemVO.getState().toString(),
                                    eachSysSystemVO.getCreateUid(),
                                    eachSysSystemVO.getCreateTime().toString()
                            ));
                        });
                    }
                    writer.write0(dataList, sheet, table);
                }
    
                // 下载EXCEL
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                writer.finish();
                out.flush();
    
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
    
            return ResultVO.getSuccess("导出系统列表EXCEL成功");
        }

     

    2.4.3 数据里很大(几百万都行): 多个SHEET分批查询导出 

        @Override
        public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    
            ServletOutputStream out = null;
            try {
                out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    
                // 设置EXCEL名称
                String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
    
                // 设置SHEET名称
                String sheetName = "系统列表sheet";
    
                // 设置标题
                Table table = new Table(1);
                List<List<String>> titles = new ArrayList<List<String>>();
                titles.add(Arrays.asList("系统名称"));
                titles.add(Arrays.asList("系统标识"));
                titles.add(Arrays.asList("描述"));
                titles.add(Arrays.asList("状态"));
                titles.add(Arrays.asList("创建人"));
                titles.add(Arrays.asList("创建时间"));
                table.setHead(titles);
    
                // 查询总数并封装相关变量(这块直接拷贝就行了不要改)
                Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
                Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;
                Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
                Integer previousSheetWriteCount = perSheetRowCount / pageSize;
                Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?
                        previousSheetWriteCount :
                        (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));
    
    
                for (int i = 0; i < sheetCount; i++) {
    
                    // 创建SHEET
                    Sheet sheet = new Sheet(i, 0);
                    sheet.setSheetName(sheetName + i);
    
                    // 写数据 这个j的最大值判断直接拷贝就行了,不要改动
                    for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {
                        List<List<String>> dataList = new ArrayList<>();
    
                        // 此处查询并封装数据即可 currentPage, pageSize这俩个变量封装好的 不要改动
                        PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);
                        List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                        if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                            sysSystemVOList.forEach(eachSysSystemVO -> {
                                dataList.add(Arrays.asList(
                                        eachSysSystemVO.getSystemName(),
                                        eachSysSystemVO.getSystemKey(),
                                        eachSysSystemVO.getDescription(),
                                        eachSysSystemVO.getState().toString(),
                                        eachSysSystemVO.getCreateUid(),
                                        eachSysSystemVO.getCreateTime().toString()
                                ));
                            });
                        }
                        writer.write0(dataList, sheet, table);
                    }
                }
    
                // 下载EXCEL
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                writer.finish();
                out.flush();
    
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
    
            return ResultVO.getSuccess("导出系统列表EXCEL成功");
        }

    三、总结

                造的假数据,100W条记录,18个字段,测试导出是70s。  在实际上产环境使用的时候,具体的还是要看自己写的sql的性能。 sql性能快的话,会很快。 有一点推荐一下: 在做分页的时候使用单表查询, 对于所需要处理的外键对应的冗余字段,在外面一次性查出来放到map里面(推荐使用@MapKey注解),然后遍历list的时候根据外键从map中获取对应的名称。一个宗旨:少发查询sql, 才能更快的导出。   

                题外话: 如果数据量过大,在使用count(1)查询总数的时候会很慢,可以通过调整mysql的缓冲池参数来加快查询,请参见博主的另一篇博文MYSQL单表数据量过大查询过慢配置优化innodb_buffer_pool_size。  还有就是遇到了一个问题,使用pagehelper的时候,数据量大的时候,limit 0,20W;  limit 20W,40W,  limit 40W,60W, limit 60W,80W 查询有的时候会很快,有的时候会很慢,待研究。

     

     

     

    展开全文
  • java导出excel的两种方式

    万次阅读 多人点赞 2019-04-05 10:24:31
    一、在后台实现,利用java的poi ...2、在util下写一个公共类,该类主要利用Jakarta POI HSSF API组件(用于操作Excel的组件),主要部分包括Excel对象,样式和格式,还有辅助操作。 常用组件: ...
  • Excel导出

    2018-09-14 15:44:58
    一、jsp页面 &lt;%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%&gt; &lt;%@ taglib prefix="c"... 
  • 自动导出Excel的利器

    千次阅读 2018-08-29 11:01:00
    本文将介绍如何用集算器生成Excel文件,而集算器本身强大的数据计算能力不是本文重点,因此文中只是简单地用文本作为数据源举例,实际应用中可能会从各种各样的数据源中取数,再经过一系列运算得到需要导出的数据。...
  • EXCEL导出汇总

    2018-11-12 10:14:34
    1.EXCEL导出工具类 (1).xlsx后缀的EXCEL导出工具类 XSSFWorkbook (2).xls后缀的EXCEL导出工具类 HSSFWorkbook 2.导出格式为.xlsx后缀的Excel后台导出功能 //接收NEC明细列表(全件、非全件)——未开票的...
  • Java之——导出Excel通用工具类

    万次阅读 多人点赞 2018-03-22 21:11:15
    转载请注明出处:... ... 一、概述 相信大家在工作过程中,都会遇到这样一个需求,就是将相关的数据列表导出excel,那么,有没有通用的导出方式呢,这里,...
  • java实现excel的导入和导出

    万次阅读 2017-10-30 17:14:51
    在网上参考了很多文章,对于excel的导入导出大概看了下,详细的API没有仔细去看,只不过是实现了功能而已。这里贴上代码,一方面自己以后用得时候可以直接拿来用,另一方面有需要实现excel导入导出功能的,也可以...
  • 使用easyexcel导出excel文件

    万次阅读 热门讨论 2019-01-31 04:17:21
    这里是导出一个excel文件,里面有两个sheet 分别是税赋预测表和发票汇总表 controller层 @requestMapping(value = &amp;quot;/exportExcel/taxForecast&amp;quot;, method = Method.GET) public Result&...
  • 在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇...
  • POI百万级大数据量EXCEL导出

    万次阅读 多人点赞 2018-09-25 20:18:05
    excel导出,如果数据量在百万级,会出现俩点内存溢出的问题: 1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决; 2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的...
  • POIexcel导出工具类

    千次阅读 2017-08-05 11:56:58
    import java.io.FileOutputStream; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.List;...import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; i
  • 利用poi3.9做的excel导出工具。 这是一个工程直接压缩而成的。 测试10万行*8列,从查询到生成文件所花时间13620毫秒 测试100万行*8列,从查询到生成文件所花时间121443毫秒 主要目录: src com.util ----DBUtil.java...
  • poi excel导出工具类

    2017-12-04 10:15:51
    基于poi开发的excel导出工具类,一种无样式优化导出,一种自适应cell宽度导出
  • 基于POI实现Excel表的导入导出功能

    万次阅读 多人点赞 2017-06-12 20:43:56
    对于批量数据的操作,在项目中引进Excel的导入和导出功能是个不错的选择。对于Excel表的结构,简单理解可以把它分成三部分(Sheet,Cell,Row),这三部分可以理解为excel表中的页,列,行。因此,我们想要获取到某...
  • 基于POI+XML配置模板Excel导出

    热门讨论 2014-04-14 16:48:27
    基于POI+XML配置模板Excel导出,代码简单易用,模板与html类似,配置极易上手。支持单sheet和多sheet导出。支持导出样式及单元格融合,表头融合
  • 这个是java用poi操作Excel进行导出,并且可以自动换行
  • 在前端jsp页面上有一个链接或按钮,单击之后进入Controller层的接口,然后弹出弹框下载导出Excel,最后将这个excel表格下载到客户端指定路径! 工具:IDEA pom.xml添加依赖: <dependency> <...
  • Java POI 导入导出Excel简单实例源代码

    千次下载 热门讨论 2014-08-28 16:25:39
    Java POI 导入导出Excel简单实例源代码 该源代码的jar包,参见以下博文附录截图 Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 http://blog.csdn.net/evangel_z/article/details/7332535 web页面导出Excel...
  • Excel导出、PDF导出 本文章写的Excel导入、Excel导出、PDF导出, 写的工具类都是适用于很多都能用的,但是我封装了数据,不过只是封装了一层而已,很好解析出来 先导包 <!-- 最新版 4.0.0 --> <...
  • 现在我们需要对前端表格中的数据导出到本地的Excel格式,如下: 实现 1、简介 Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。自2009-09-28...
  • java使用poi实现excel导出

    千次阅读 2019-06-15 20:04:40
    对于刚入门的小伙伴,对于excel的导出需要经常使用到,公司基本上都会有excel导出工具,然而对于还没工作的小伙伴,没有机会接触这些,恰好又不会写,可以借鉴一下,虽然代码有点多,但是简单,也能实现该功能。...
  • 使用Struts和POI实现Excel导出下载

    千次阅读 2017-04-05 12:35:52
    使用Struts和POI实现Excel导出下载功能  网上找了很多关于Excel导出的功能,发现有很多问题,有些导出Excel里面是空白的,并没有数据,有些是通过直接保存在D盘上,很明显这些并不是我们想要的结果,通过东凑西...
  • poi3.8用((SXSSFSheet)sheet).flushRows()方法解决了大数据量的excel导出,但是我在使用过程中,由于每个单元格都要设置不同的颜色和数据格式,发现导出第一张报表时速度还勉强可以接受,但是继续导出另一张报表,...
  • 改资源包含 数据量超过20万以上导出解决方案,还有动态获取数据公共类 1) 把数据分成多个sheet导出。 2) 把数据分成多个excel 导出。 全部都有,还有实例。
  • Poi实现Excel导出工具类封装

    万次阅读 2016-02-28 17:15:25
    工具类代码PoiExcelExport如下:package com.myssm.util.poi; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect....
  • POI4.0 Excel导出工具类

    2019-04-12 10:20:55
    公司很多报表导出,于是自己动手封装了一个Excel导出类,用于导出格式比较简单的数据。 导出的示例图 这是默认的导出格式,大标题,表头,数据。 工具优点 配置灵活,导出字段的顺序,所占宽度,高度等,只需配置...

空空如也

1 2 3 4 5 ... 20
收藏数 200,987
精华内容 80,394
关键字:

excel导出