java使用opi导出excel

来源：互联网发布：速卖通数据分析网站编辑：程序博客网时间：2024/06/15 20:49

1.导入jar包

        <!-- xls格式 -->        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version></dependency><!-- xlsx格式 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14</version></dependency>

2.工具类

package com.hr.basic.utils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/**** @author gw* @version 2017年3月10日 上午8:07:52*/public class ExcelUtil {/** *  * @methodName:getHSSFWorkbook * * @param sheetName * * @param title 表头 * * @param values 表格内容 * * @param wb * * @return * @return HSSFWorkbook * @author gw  * @date 2017年3月10日上午11:19:20 */public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){//第一步，创建一个webbook,对应一个Excel文件if(wb == null){wb = new HSSFWorkbook();}//第二步，在webbook中添加一个sheet,对应excel文件中的sheetHSSFSheet sheet = wb.createSheet(sheetName);//第三步，在sheet中添加表头第0行HSSFRow row = sheet.createRow(0);//第四步，创建单元格，并设置表头，设置表头居中HSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//创建一个居中格式HSSFCell cell = null;//创建标题for(int i=0;i<title.length;i++){cell = row.createCell(i);cell.setCellValue(title[i]);cell.setCellStyle(style);}//创建内容for(int i=0;i<values.length;i++){row = sheet.createRow(i+1);for(int j=0;j<values[i].length;j++){row.createCell(j).setCellValue(values[i][j]);}}return wb;}}

3.导入表格信息

      @RequestMapping(value = "/exportExcel", method = RequestMethod.POST)      @ResponseBody      public String exportExcel(Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) {        List<Bz_Pay> list = payDao.getList(params);String fileName = "Excel"+System.currentTimeMillis()+".xls"; //文件名         String sheetName = "收款管理";//sheet名                String []title = new String[]{"付款人","付款金额","上传日期","付款时间","确认人"};//标题                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                String [][]values = new String[list.size()][];        for(int i=0;i<list.size();i++){            values[i] = new String[title.length];            //将对象内容转换成string            Bz_Pay obj = list.get(i);            values[i][0] = obj.getPayer();//付款人            values[i][1] = String.valueOf(obj.getPayment());//付款金额            try {            values[i][2] = sdf.format(obj.getCreateTime());//上传日期values[i][5] = sdf.format(obj.getVerifyTime());//确认时间    } catch (NullPointerException e) {            }            values[i][3] = obj.getPayTime();//付款时间            values[i][4] = (String) obj.get("salerName");//确认人        }                HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);                String  excelPath = request.getSession().getServletContext().getRealPath("res/upload/excel/export/");        //request.getSession().getServletContext() 获取的是Servlet容器对象，     //相当于tomcat容器了。getRealPath("/") 获取实际路径，“/”指代项目根目录，     //所以代码返回的是项目在容器中的实际发布运行的根路径       File path = new File(excelPath);        //将文件存到指定位置          String exPath = "res/upload/excel/"+"export"+fileName;        try {               this.setResponseHeader(response, fileName);              FileOutputStream os = new FileOutputStream(path+fileName);             //OutputStream os = response.getOutputStream();               wb.write(os);               os.flush();               os.close();          } catch (Exception e) {               e.printStackTrace();          }          return exPath;    }         public void setResponseHeader(HttpServletResponse response, String fileName) {           try {                try {                     fileName = new String(fileName.getBytes(),"ISO8859-1");                } catch (UnsupportedEncodingException e) {                     // TODO Auto-generated catch block                     e.printStackTrace();                }                response.setContentType("application/x-msdownload;charset=ISO8859-1");               response.setHeader("Content-Disposition", "attachment;filename="+ fileName);                response.addHeader("Pargam", "no-cache");                response.addHeader("Cache-Control", "no-cache");           } catch (Exception ex) {                ex.printStackTrace();           }      }

其中

            try {            values[i][2] = sdf.format(obj.getCreateTime());//上传日期values[i][5] = sdf.format(obj.getVerifyTime());//确认时间    } catch (NullPointerException e) {            }

是因为我的数据库中有数据为空，需要捕获一下

我是用的是，先在服务器上保存文件，然后使用浏览器的下载功能，下载到本地

调用方法返回url字符串，将字符串给浏览器，window.open(data.responseText);

exportAllExcel:function(){        Ext.Msg.alert("信息提示","正在导出，请稍后！");Ext.Ajax.request({          url:'business/pay/exportExcel',          method:'POST',          //params:{path:record.data.payId},          success:function(data){        Ext.Msg.alert("信息提示","导出成功！");        window.open(data.responseText);          },  failure : function(){Ext.Msg.alert("信息提示", "操作失败，请检查网络是否正常！");  }});  }

0 0