JAVA POI将数据导出保存到EXCEL 的一些方式

来源：互联网发布：陈伟霆家境知乎编辑：程序博客网时间：2024/06/16 06:59

第一种:导入后保存的格式为.xls格式,针对EXCEL2003 版本

采用的是SSM框架从后台获取数据
前台点击后得到.xls格式的文件
打开文件后的内容的具体格式
这里写图片描述

具体实现代码如下,注释写的很详细,不在阐述

    @RequestMapping("/cargo/outproduct/print.action")    public void print(String inputDate,HttpServletResponse response) throws IOException{        List<OutProductVO> dataList=outProductService.find(inputDate);        Workbook wb=new HSSFWorkbook(); //创建一个工作簿        Sheet sheet=wb.createSheet();   //创建一个工作表        Row nRow=null;        Cell nCell=null;        int rowNo=0;   //行号        int colNo=1;  //列号        sheet.setColumnWidth(0, 2*200);    //列宽        sheet.setColumnWidth(1, 26*300);        sheet.setColumnWidth(2, 12*300);        sheet.setColumnWidth(3, 29*300);        sheet.setColumnWidth(4, 10*300);        sheet.setColumnWidth(5, 12*300);        sheet.setColumnWidth(6, 16*300);        sheet.setColumnWidth(7, 15*300);        sheet.setColumnWidth(8, 15*300);        sheet.setColumnWidth(9, 12*300);        //声明样式对象和字体对象        CellStyle nStyle=wb.createCellStyle();        Font nFont=wb.createFont();        //大标题,合并单元格        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 9));        //合并单元格的内容是写在合并单元格之前的第一个单元格中        nRow=sheet.createRow(rowNo++);        nRow.setHeightInPoints(36);  //行高        //初始化        nStyle=wb.createCellStyle();        nFont=wb.createFont();        nCell=nRow.createCell(1);        nCell.setCellStyle(this.bigTitle(wb, nStyle, nFont));        nCell.setCellValue(inputDate.replaceFirst("-0", "-").replaceFirst("-", "年")+"月份出货表");        String [] title=new String[]{"客户","订单号","货号","数量","工厂","附件","工厂交期","船期","贸易条款"};        nRow=sheet.createRow(rowNo++);        //初始化        nStyle=wb.createCellStyle();        nFont=wb.createFont();        for (int i = 0; i < title.length; i++) {            nCell=nRow.createCell(i+1);            nCell.setCellValue(title[i]);            nCell.setCellStyle(this.title(wb, nStyle, nFont));        }        //初始化        nStyle=wb.createCellStyle();        nFont=wb.createFont();        //处理数据        for (int i = 0; i < dataList.size(); i++) {            colNo=1;            OutProductVO op=dataList.get(i);            nRow = sheet.createRow(rowNo++);            nRow.setHeightInPoints(24);            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getCustomName());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getContractNo());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getProductNo());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getCnumber());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getFactoryName());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getExts());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getDeliveryPeriod());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getShipTime());            nCell.setCellStyle(this.text(wb, nStyle, nFont));            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getTradeTerms());            nCell.setCellStyle(this.text(wb, nStyle, nFont));        }        /*//直接保存文件到桌面的方法OutputStream os=new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\outproduct.xls"));        wb.write(os);        os.flush();        os.close();*/        //下载,自己封装的一个工具类,点击后弹出下载保存框        DownloadUtil du=new DownloadUtil();        ByteArrayOutputStream os=new ByteArrayOutputStream();        wb.write(os);        du.download(os, response, "出货表.xls");    }    //大标题样式    public CellStyle bigTitle(Workbook wb,CellStyle nStyle,Font nFont){        nFont.setFontName("宋体");        nFont.setFontHeightInPoints((short)16);        nFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗        nStyle.setAlignment(CellStyle.ALIGN_CENTER);  //横向居中        nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中        nStyle.setFont(nFont);        return nStyle;    }    //标题样式    public CellStyle title(Workbook wb,CellStyle nStyle,Font nFont){        nFont.setFontName("黑体");        nFont.setFontHeightInPoints((short)12);        nStyle.setAlignment(CellStyle.ALIGN_CENTER);  //横向居中        nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中        //表格线        nStyle.setBorderBottom(CellStyle.BORDER_THIN);        nStyle.setBorderLeft(CellStyle.BORDER_THIN);        nStyle.setBorderRight(CellStyle.BORDER_THIN);        nStyle.setBorderTop(CellStyle.BORDER_THIN);        nStyle.setFont(nFont);        return nStyle;    }    //文字样式    public CellStyle text(Workbook wb,CellStyle nStyle,Font nFont){        nFont.setFontName("Times New Roman");        nFont.setFontHeightInPoints((short)12);        nStyle.setAlignment(CellStyle.ALIGN_CENTER);  //横向居中        nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中        //表格线        nStyle.setBorderBottom(CellStyle.BORDER_THIN);        nStyle.setBorderLeft(CellStyle.BORDER_THIN);        nStyle.setBorderRight(CellStyle.BORDER_THIN);        nStyle.setBorderTop(CellStyle.BORDER_THIN);        nStyle.setFont(nFont);        return nStyle;    }

第二种: 这种方法是对上一种方法的优化,可以直接利用现成的模板直接获取模板里面的样式,以后修改样式只需对模板进行修改,而不用修改代码

模板样式的.XLS文件,在EXCEL弄好所要的样式文件,在代码中直接get行,列的样式等属性,同样是EXCEL2003版本

模板的样式

我的模板文件放置的目录
这里写图片描述
具体效果不贴图了,下面是代码的实现

//模板开发    @RequestMapping("/cargo/outproduct/printHSSF.action")    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 extStyle = nCell.getCellStyle();              //工厂交期        nCell = nRow.getCell(7);        CellStyle fdateStyle = nCell.getCellStyle();            //船期交期        nCell = nRow.getCell(7);        CellStyle sdateStyle = 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.getExts());            nCell.setCellStyle(extStyle);            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getDeliveryPeriod());            nCell.setCellStyle(fdateStyle);            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getShipTime());            nCell.setCellStyle(sdateStyle);            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();    }

第三种: 对于EXCEL 2007版本的保存方式,.xlsx文件,同样采用的是模板保存的方式进行保存,跟第二种方法差不多,主要将HSSF改成XSSF,模板文件同样要改成xlsx格式的,具体实现如下

//模板开发XSSF    @RequestMapping("/cargo/outproduct/printXSSF.action")    public void printXSSF(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 extStyle = nCell.getCellStyle();              //工厂交期        nCell = nRow.getCell(7);        CellStyle fdateStyle = nCell.getCellStyle();            //船期交期        nCell = nRow.getCell(7);        CellStyle sdateStyle = 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.getExts());            nCell.setCellStyle(extStyle);            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getDeliveryPeriod());            nCell.setCellStyle(fdateStyle);            nCell = nRow.createCell(colNo++);            nCell.setCellValue(op.getShipTime());            nCell.setCellStyle(sdateStyle);            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();    }

转载请注明出处,谢谢!

阅读全文

1 0