java后台导出excel（笔记记录）

54 阅读 0 评论 36 点赞

我是靠谱客的博主迷人八宝粥，最近开发中收集的这篇文章主要介绍java后台导出excel（笔记记录），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

//调用方法，可以用请求的方式，用postman可以模拟页面的下载文件场景

public static void main (String args[]){

excelHelper.setXls(导出的数据);

//编辑好报表的属性名，这个就是导出后顺序哦

String queryConText = "序号,发电户号,授权协议编号,授权协议版本号,签订渠道,签约完成状态,用户签订时间,授权协议类型,维护时间 _#,powerGenerationNo,dealId,dealNum,dealDitch,signType,signDate,dealType,timeStamp";

excelHelper.dto2Excel(fileName, 报表的唯一标识, HttpServletResponse, queryConText);

}

public class ExcelHelper {

//存入需要导出的数据

private List<?> xls;

   public void setXls(List<?> xls) {
       this.xls = xls;
   }
   //private static final Logger logger = Logger.getLogger(ExcelHelper.class);

   /**
   * wangyf
   *
   * 导出Excel方法
   *
   */

   @SuppressWarnings("deprecation")
   public void dto2Excel(String outfileName, String sheetName, HttpServletResponse response, String queryConText) {

       HSSFWorkbook hwb = null;
       OutputStream out = null;
       String excelHead = null;
       String excelValue = null;
       String[] excelName = null;
       String[] excelValueText = null;
       String[] conText = null;

       try {
           if (!queryConText.isEmpty()&&queryConText!=null) {
               conText = queryConText.split("_");
               excelHead = conText[0];
               excelValue = conText[1];
           }

           excelName = excelHead.split(",");
           excelValueText = excelValue.split(",");
           // 获取总列数
           int CountColumnNum = excelName.length;
           // 创建Excel文档
           hwb = new HSSFWorkbook();
           Object xlsDto = null;
           // sheet 对应一个工作页
           HSSFSheet sheet = hwb.createSheet(sheetName);//导出文件
           HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
           HSSFCellStyle headstyle = hwb.createCellStyle();//设置样式
           HSSFFont headfont = hwb.createFont();//设置字体
           HSSFCell[] firstcell = new HSSFCell[CountColumnNum];

           HSSFRichTextString ts = null;
           HSSFFont font = null;
           for (int i = 0, len = CountColumnNum; i < len; i++) {
               font = (HSSFFont) hwb.createFont();
               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度
               firstcell[i] = firstrow.createCell(i);
               ts = new HSSFRichTextString(excelName[i]);
               ts.applyFont(font);
               firstcell[i].setCellValue(ts);
           }
           //设置列的长度
           setColumnWidth(excelName, sheet);
           //设置字体
           headfont.setFontName("黑体");
           headfont.setFontHeightInPoints((short) 22);// 字体大小
           headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
           //设置样式
           headstyle.setFont(headfont);
           headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
           headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
           headstyle.setLocked(true);
           headstyle.setWrapText(true);// 自动换行
           HSSFRow row = null;


           String value = "";
           for (int i = 0, len = xls.size(); i < len; i++) {
               // 创建一行
               row = sheet.createRow(i + 1);
               // 得到要插入的每一条记录
               xlsDto = xls.get(i);
               for (int colu = 0, _len = CountColumnNum; colu < _len; colu++) {
                   // 在一行内循环
                   HSSFCell cell = row.createCell(colu);
                   String filedName = excelValueText[colu];
                   if (filedName.equals("#")) {
                       value = (i + 1) + "";
                   } else {
                       value = findMethodByFieldName(filedName, xlsDto.getClass().getName(), xlsDto);
                       if ("null".equals(value)) {
                           value = "";
                       }
                   }
                   cell.setCellValue(value);
               }
           }
           // 创建文件输出流，准备输出电子表格
           // 清空response
           response.reset();
           // 设置response的Header
           //response.addHeader("Content-Disposition", "attachment;filename=" + new String(outfileName.getBytes("UTF-8"),"ISO-8859-1"));
           // response.addHeader("Content-Length", "" + file.length());
           //response.setCharacterEncoding("UTF-8");
           //response.setContentType("application/octet-stream;charset=utf-8");
//设置请求头，防止输出文件内容乱码
           response.setContentType("application/vnd.ms-excel;charset=UTF-8");
   response.setCharacterEncoding("UTF-8");
   response.addHeader("Content-Disposition", "attachment;filename=" + new String(outfileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");

           out = new BufferedOutputStream(response.getOutputStream());
           hwb.write(out);
       //   logger.info("数据库导出成功");
       } catch (Exception e) {
       //   logger.error("报表生成xls异常！"+e.getMessage());
       } finally {

           if (out != null) {
               try {
                   out.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
/*           if (hwb != null) {
               try {
                   hwb.cl
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }*/
       }

}

   public String findMethodByFieldName(String name, String className, Object obj) {
       String result = "";
       try {
           Method[] ms = Class.forName(className).getMethods();
           for (Method method : ms) {

               if (method.getName().equals(findKey(name))) {
                   // 如果是时间类型
                   if (method.getReturnType().equals(Date.class)) {

                       String timeFormat = AnnotationUtil.findExcelFormartByfileName(name, obj);
                       java.text.DateFormat format1 = new java.text.SimpleDateFormat(timeFormat);
                       Date date = (Date) method.invoke(obj);
                       if (date != null) {
                           result = format1.format(date);
                       }
                   } else {
                       result = String.valueOf(method.invoke(obj));
                   }

}
}

       } catch (Exception e) {
           e.printStackTrace();
       }
       return result;
   }

   public String findKey(String fieldName) {
       String method = "";
       method += "get";
       //把第一个字符转为大写
       method += fieldName.substring(0, 1).toUpperCase();
       //取第一行字符后的
       method += fieldName.substring(1);
       return method;
   }

   //设置xls字段在文件中占多大区间，根据中文属性名的长度来设置区间长度，我这里写的比较low，大家可以参考下！
   public void setColumnWidth(String[] excelName,HSSFSheet sheet) {
       for (int i = 0; i < excelName.length; i++) {
           if(excelName[i].length()<=3) {
               sheet.setColumnWidth(i, 1600);
           }else if(excelName[i].length()==4) {
               boolean status = excelName[i].contains("日期");
               if(status) {
                   sheet.setColumnWidth(i, 5200);
               }else{
                   sheet.setColumnWidth(i, 3000);
               }
           }
           if(excelName[i].length()>=6) {
               sheet.setColumnWidth(i, 4000);
           }

       }
       //
   }
}