Java操作Excel之POI：excel导出文件

向右看齐 2021-11-19 14:24 393阅读 0赞

开发步骤：

\* 1、创建一个工作簿

\* 2、创建一个工作表

\* 3、创建一个行对象

\* 4、创建一个单元格对象，指定它的列

\* 5、给单元格设置内容

\* 6、样式进行修饰（跳过）

\* 7、保存，写文件

\* 8、关闭对象

## 1.基础打印 ##

public void testHSSF_base() throws IOException{
            
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row nRow = sheet.createRow(7);            //第八行
            Cell nCell = nRow.createCell(4);        //第五列
            
            nCell.setCellValue("demo演示案例");
            
            OutputStream os = new FileOutputStream("c:\\testpoi.xls");    
            wb.write(os);
            
            os.flush();
            os.close();
        }

##  2.HSSF无模板打印 ##

## ![1740941-20190719083738186-2091747930.png][] ##

(注:表格来自杰信物流SSM java课程案例上的表格)

public void printNotemplate(String inputDate) throws IOException{
            
            /*
             * POI实现excel打印
             * 1、大标题，合并单元格
             * 2、标题，修饰
             * 3、内容，修饰
             * 
             */
    
            Workbook wb = new HSSFWorkbook();        //创建一个工作簿
            Sheet sheet = wb.createSheet();            //创建一个工作表
            Row nRow = null;
            Cell nCell = null;
            int rowNo = 0;                            //行号，默认从0开始
            int colNo = 1;                            //列号为1 是因为a列为了打印美观空出来
            
            //创建样式和字体对象
            CellStyle curStyle = wb.createCellStyle();
            Font curFont = wb.createFont();
            
            //设置列宽 256，BUG，精度不够，总是差一点
            sheet.setColumnWidth(0, 1*278);                //第1列
            sheet.setColumnWidth(1, 26*278);            //第2列
                    
            
            //处理大标题    sheet.addMergedRegion(new CellRangeAddress(开始行，结束行，开始列，结束列));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));        //合并单元格
            nRow = sheet.createRow(rowNo++);
            nRow.setHeightInPoints(36);//行高
            
            nCell = nRow.createCell(1);
            nCell.setCellStyle(bigTitleStyle(wb));
            
            nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年") + "月份出货表");        //yyyy-MM  2019-05改为2019年5月份 2019-10改为2019年10月份
            
            //处理标题
            String[] title = new String[]{"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};    //标题数组
            nRow = sheet.createRow(rowNo++);
            nRow.setHeightInPoints(26);
            
            for(int i=0;i<title.length;i++){
                nCell = nRow.createCell(i+1);
                nCell.setCellValue(title[i]);
                nCell.setCellStyle(this.titleStyle(wb));
            }
            
            //处理内容
            List<OutProductVO> dataList = outProductService.find(inputDate);
            for(int j=0;j<dataList.size();j++){
                colNo = 1;                //初始化 逐行从第2列开始写数据
                OutProductVO op = dataList.get(j);
                
                nRow = sheet.createRow(rowNo++);
                nRow.setHeightInPoints(24);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCustomName());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getContractNo());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getProductNo());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCnumber());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getFactoryName());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getDeliveryPeriod());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getShipTime());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getTradeTerms());
                nCell.setCellStyle(this.textStyle(wb, curStyle, curFont));
            }
            
            OutputStream os = new FileOutputStream("c:\\outproduct.xls");
            wb.write(os);
            
            os.flush();
            os.close();
        }
    //大标题样式
        private CellStyle bigTitleStyle(Workbook wb){
            //为了防止创建多个样式和字体，并且只覆盖最后一次设置的样式和字体值，需要创建新对象
            CellStyle curStyle = wb.createCellStyle();
            Font curFont = wb.createFont();
            
            curFont.setFontName("宋体");
            curFont.setFontHeightInPoints((short)16);
            curFont.setBoldweight(Font.BOLDWEIGHT_BOLD);                    //字体加粗
            
            curStyle.setFont(curFont);                                        //绑定字体
            
            curStyle.setAlignment(CellStyle.ALIGN_CENTER);                    //横向居中
            curStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        //纵向居中
            
            return curStyle;
        }
        
        //小标题样式
        private CellStyle titleStyle(Workbook wb){
            CellStyle curStyle = wb.createCellStyle();
            Font curFont = wb.createFont();
            
            curFont.setFontName("黑体");
            curFont.setFontHeightInPoints((short)12);
            
            curStyle.setFont(curFont);                                        //绑定字体
            
            curStyle.setAlignment(CellStyle.ALIGN_CENTER);                    //横向居中
            curStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        //纵向居中
            
            
            curStyle.setBorderTop(CellStyle.BORDER_THIN);                    //设置四周边线，细线
            curStyle.setBorderBottom(CellStyle.BORDER_THIN);
            curStyle.setBorderLeft(CellStyle.BORDER_THIN);
            curStyle.setBorderRight(CellStyle.BORDER_THIN);
            
            return curStyle;
        }
        
        //文字样式
        private CellStyle textStyle(Workbook wb, CellStyle curStyle, Font curFont){
            
            curFont.setFontName("Times New Roman");
            curFont.setFontHeightInPoints((short)10);
            
            curStyle.setFont(curFont);                                        //绑定字体
            
            curStyle.setAlignment(CellStyle.ALIGN_LEFT);                    //横向居左
            curStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        //纵向居中
            
            
            curStyle.setBorderTop(CellStyle.BORDER_THIN);                    //设置四周边线，细线
            curStyle.setBorderBottom(CellStyle.BORDER_THIN);
            curStyle.setBorderLeft(CellStyle.BORDER_THIN);
            curStyle.setBorderRight(CellStyle.BORDER_THIN);
            
            return curStyle;
        }

##  3.HSSF含模板打印 ##

public void printHSSF(String inputDate, HttpServletRequest request, HttpServletResponse response) throws IOException{
            //linux下jdk1.8 方法获取时，不会拼接自己写的目录 
            String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
            InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xls"));
            
            Workbook wb = new HSSFWorkbook(is);        //打开一个模板文件，工作簿
            Sheet sheet = wb.getSheetAt(0);            //获取到第一个工作表
            
            Row nRow = null;
            Cell nCell = null;
            int rowNo = 0;                            //行号
            int colNo = 1;                            //列号
            
            //获取模板上的单元格样式
            nRow = sheet.getRow(2);
            
            //客户的样式
            nCell = nRow.getCell(1);
            CellStyle customStyle = nCell.getCellStyle();        
            
            //订单号的样式
            nCell = nRow.getCell(2);
            CellStyle contractNoStyle = nCell.getCellStyle();        
            
            //货号的样式
            nCell = nRow.getCell(3);
            CellStyle productNoStyle = nCell.getCellStyle();        
            
            //数量的样式
            nCell = nRow.getCell(4);
            CellStyle numStyle = nCell.getCellStyle();        
            
            //生产厂家的样式
            nCell = nRow.getCell(5);
            CellStyle factoryStyle = nCell.getCellStyle();        
            
            //日期的样式
            nCell = nRow.getCell(6);
            CellStyle dateStyle = nCell.getCellStyle();        
            
            //贸易条款的样式
            nCell = nRow.getCell(8);
            CellStyle tradeStyle = nCell.getCellStyle();        
                    
            
            //处理大标题
            nRow = sheet.getRow(rowNo++);            //获取一个行对象
            nCell = nRow.getCell(colNo);            //获取一个单元格对象
            nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年") + "月份出货表");        //yyyy-MM
            
            rowNo++;                                //跳过静态表格头
            
            //处理内容
            List<OutProductVO> dataList = outProductService.find(inputDate);
            for(int j=0;j<dataList.size();j++){
                colNo = 1;                //初始化
                OutProductVO op = dataList.get(j);
                
                nRow = sheet.createRow(rowNo++);
                nRow.setHeightInPoints(24);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCustomName());
                nCell.setCellStyle(customStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getContractNo());
                nCell.setCellStyle(contractNoStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getProductNo());
                nCell.setCellStyle(productNoStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCnumber());
                nCell.setCellStyle(numStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getFactoryName());
                nCell.setCellStyle(factoryStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getDeliveryPeriod());
                nCell.setCellStyle(dateStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getShipTime());
                nCell.setCellStyle(dateStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getTradeTerms());
                nCell.setCellStyle(tradeStyle);
            }
            
    //        OutputStream os = new FileOutputStream("c:\\outproduct.xls");
    //        wb.write(os);
    //        
    //        os.flush();
    //        os.close();
            
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            
            DownloadUtil downloadUtil = new DownloadUtil();                //直接弹出下载框，用户可以打开，可以保存
            downloadUtil.download(os, response, "出货表.xls");
            
            os.flush();
            os.close();
        }

## 4.XSSF打印 ##

@RequestMapping("/cargo/outproduct/print.action")
        public void print(String inputDate, HttpServletRequest request, HttpServletResponse response) throws IOException{
            //linux下jdk1.8 方法获取时，不会拼接自己写的目录 
            String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
            InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xlsx"));
            
            Workbook wb = new XSSFWorkbook(is);        //打开一个模板文件，工作簿 2007以上版本
            Sheet sheet = wb.getSheetAt(0);            //获取到第一个工作表
            
            Row nRow = null;
            Cell nCell = null;
            int rowNo = 0;                            //行号
            int colNo = 1;                            //列号
            
            //获取模板上的单元格样式
            nRow = sheet.getRow(2);
            
            //客户的样式
            nCell = nRow.getCell(1);
            CellStyle customStyle = nCell.getCellStyle();        
            
            //订单号的样式
            nCell = nRow.getCell(2);
            CellStyle contractNoStyle = nCell.getCellStyle();        
            
            //货号的样式
            nCell = nRow.getCell(3);
            CellStyle productNoStyle = nCell.getCellStyle();        
            
            //数量的样式
            nCell = nRow.getCell(4);
            CellStyle numStyle = nCell.getCellStyle();        
            
            //生产厂家的样式
            nCell = nRow.getCell(5);
            CellStyle factoryStyle = nCell.getCellStyle();        
            
            //日期的样式
            nCell = nRow.getCell(6);
            CellStyle dateStyle = nCell.getCellStyle();        
            
            //贸易条款的样式
            nCell = nRow.getCell(8);
            CellStyle tradeStyle = nCell.getCellStyle();        
            
            
            //处理大标题
            nRow = sheet.getRow(rowNo++);            //获取一个行对象
            nCell = nRow.getCell(colNo);            //获取一个单元格对象
            nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年") + "月份出货表");        //yyyy-MM
            
            rowNo++;                                //跳过静态表格头
            
            //处理内容
            List<OutProductVO> dataList = outProductService.find(inputDate);
            for(int j=0;j<dataList.size();j++){
                colNo = 1;                //初始化
                OutProductVO op = dataList.get(j);
                
                nRow = sheet.createRow(rowNo++);
                nRow.setHeightInPoints(24);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCustomName());
                nCell.setCellStyle(customStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getContractNo());
                nCell.setCellStyle(contractNoStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getProductNo());
                nCell.setCellStyle(productNoStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getCnumber());
                nCell.setCellStyle(numStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getFactoryName());
                nCell.setCellStyle(factoryStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getDeliveryPeriod());
                nCell.setCellStyle(dateStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getShipTime());
                nCell.setCellStyle(dateStyle);
                
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(op.getTradeTerms());
                nCell.setCellStyle(tradeStyle);
            }
            
    //        OutputStream os = new FileOutputStream("c:\\outproduct.xls");
    //        wb.write(os);
    //        
    //        os.flush();
    //        os.close();
            
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            
            DownloadUtil downloadUtil = new DownloadUtil();                //直接弹出下载框，用户可以打开，可以保存
            downloadUtil.download(os, response, "出货表.xlsx");
            
            os.flush();
            os.close();
        }

## 5.小结 ##

HSSF 比较多，兼顾客户的环境，针对excel2003

XSSF 应用比较少，当数据量比较大时，才采用，针对excel2007及以上

SXSSF 只用在海量数据的导出，且不支持模板导出

转载于:https://www.cnblogs.com/SI0301/p/11204959.html

[1740941-20190719083738186-2091747930.png]: /images/20211119/09b566c3c27346cfa9a2a65db767d6c8.png