使用poi将数据导出到excel

深藏阁楼爱情的钟 2022-07-17 01:28 319阅读 0赞

第一种方式：代码中自定义样式（不推荐）

//获取数据
            List<OutProductVO> outProductList = this.outProductService.query(inputDate);
    
            Workbook wb = new HSSFWorkbook();//创建一个工作簿
            Sheet sheet = wb.createSheet("出货表");//创建一个工作表
            CellStyle cellStyle= wb.createCellStyle();
            Font nFont = wb.createFont();
            int rowNo = 0;//行号
            int colNo = 1;//列号
            Row nRow = null;//sheet.createRow(2);
            Cell nCell = null;//nRow.createCell(1);
            //设置大标题
            sheet.addMergedRegion(new CellRangeAddress(0,0,1,9));
            nRow = sheet.createRow(rowNo++);
            nCell = nRow.createCell(colNo++);
            nRow.setHeightInPoints(36); 
            nCell.setCellValue(inputDate.replace("-0", "年").replace("-","年")+"月份出货表");
            nCell.setCellStyle(bigTitleStyle(cellStyle, nFont));
    
            //设置列宽
            sheet.setColumnWidth(0, 2*272); //列宽
            sheet.setColumnWidth(1, 26*272); //列宽 BUG，API底层设置不够精确 256；272近似
            sheet.setColumnWidth(2, 12*272);
            sheet.setColumnWidth(3, 29*272);
            sheet.setColumnWidth(4, 10*272);
            sheet.setColumnWidth(5, 12*272);
            sheet.setColumnWidth(6, 8*272);
            sheet.setColumnWidth(7, 10*272);
            sheet.setColumnWidth(8, 10*272);
            sheet.setColumnWidth(9, 10*272);
    
            //设置列标题
            String[] title = new String[]{
       "客户","订单号","货号","数量","工厂","附件","工厂交期","船期","贸易条款"};
            nRow = sheet.createRow(rowNo++);
    
            nCell=nRow.createCell(colNo++);
    
            nRow.setHeightInPoints(26.25f);//设置行高
            colNo=1;//列号置一
    
            cellStyle= wb.createCellStyle();
            nFont = wb.createFont();
            for(int i=0;i<title.length;i++){ 
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(title[i]);
                nCell.setCellStyle(titleStyle(cellStyle,nFont));
            }
    
             cellStyle= wb.createCellStyle();
             nFont = wb.createFont();
    
            for(int i=0;i<outProductList.size();i++){ 
                colNo=1;
                nRow = sheet.createRow(rowNo++);
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getCustomName());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getContractNo());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getContractNo());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getCnumber());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getFactoryName());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getExts());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getDeliveryPeriod());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getShipTime());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getTradeTerms());
                nCell.setCellStyle(textStyle(cellStyle, nFont));
    
            }
    
    //      FileOutputStream fileOut = new FileOutputStream("D://出货表.xls");
    //      
    //      wb.write(fileOut);
    //      
    //      fileOut.close();
            //下载
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            DownloadUtil.download(bos, response, "出货表.xls");;
            bos.close();

第二种方式：采用模板方式，样式在模板文件中通过手动方式设置好

//获取打印数据
            List<OutProductVO> outProductList = this.outProductService.query(inputDate);
            //获取打印模板路径
            String path =request.getSession().getServletContext().getRealPath("/");//服务器的根路径
            String filePath = path+"/make/xlsprint/tOUTPRODUCT.xls";
            //获取打印模板
            Workbook wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
            //获取工作表
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell =null; 
            row= sheet.getRow(0);
            cell= row.getCell(1);
            //设置大标题
            cell.setCellValue(inputDate.replace("-0","年").replace("-","年")+"月份出货表");
            //获取列样式
            row = sheet.getRow(2);
            cell=row.getCell(1);
            CellStyle customStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(2);
            CellStyle contactNOStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(3);
            CellStyle productNoStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(4);
            CellStyle cnumberStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(5);
            CellStyle factoryStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(6);
            CellStyle extsStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(7);
            CellStyle deliveryStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(8);
            CellStyle shiptimeStyle = cell.getCellStyle();
    
            row = sheet.getRow(2);
            cell=row.getCell(9);
            CellStyle tradetermsStyle = cell.getCellStyle();
            //设置主题内容
            int rowNo=2;
            int colNo=1;
            Row nRow=null;
            Cell nCell=null;
            for(int i=0;i<outProductList.size();i++){ 
                colNo=1;
                nRow = sheet.createRow(rowNo++);
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getCustomName());
                nCell.setCellStyle(customStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getContractNo());
                nCell.setCellStyle(contactNOStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getContractNo());
                nCell.setCellStyle(productNoStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getCnumber());
                nCell.setCellStyle(cnumberStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getFactoryName());
                nCell.setCellStyle(factoryStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getExts());
                nCell.setCellStyle(extsStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getDeliveryPeriod());
                nCell.setCellStyle(deliveryStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getShipTime());
                nCell.setCellStyle(shiptimeStyle);
    
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(outProductList.get(i).getTradeTerms());
                nCell.setCellStyle(tradetermsStyle);
            }
            //下载文件
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
    
            wb.write(bos);
    
            DownloadUtil.download(bos, response, "出货表.xls");
    
            bos.close();