Java中实时导出

一、创建工具类
package com.yibaomd.utils.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;

/**
 * Created by luoxin on 2017/7/25.
 */
public class ExcelUtil {

    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
        // 第一步，创建一个webbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        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;
    }


}

 

二、实现导出逻辑

@RequestMapping(value="/DownLoad/exportChannelUser" ,method = RequestMethod.GET)
@ResponseBody
public ResponseEntity<byte[]> exportChannelUser(String channelId, HttpServletResponse response) throws ServiceException, ParseException, ParseException {
   log.info("-----------------莱康渠道用户批量导出---------------------");

   JSONObject param = new JSONObject();
   param.put("channelId",channelId);
   String patientStr = patientCenterService.selectAll(JSON.toJSONString(param));

   JSONArray patients = null;
   if(null != patientStr && !"".equals(patientStr)) {
      JSONObject resultObj = JSONObject.parseObject(patientStr);
      patients = resultObj.getJSONArray("patients");
   }

   String dateString = DateUtil.getFormatTime(new Date(),DateUtil.FORMAT_YMD_1);
   String fileName = "渠道客户管理"+dateString+".xls"; //文件名
   String sheetName = "渠道客户管理";//sheet名

   String []title = new String[]{"客户姓名","联系方式","签约状态","专家咨询次数","专家姓名","陪诊次数","签约日期"
         ,"注册时间"};//标题

   String [][]values = new String[patients.size()][];
   for(int i=0;i<patients.size();i++){
      values[i] = new String[title.length];
      //将对象内容转换成string
      JSONObject obj = (JSONObject) patients.get(i);
      values[i][0] = Convert.stringConvert(obj.getString("patName"));
      values[i][1] =  Convert.stringConvert(obj.getString("mobile"));
      int isSignResult = doctorPatientRelationService.selectIsSign(obj.getLong("id"));
      String isSignName;
      if(isSignResult > 0){
         isSignName = "已签约";
      }else{
         isSignName = "未签约";
      }
      values[i][2] =   isSignName;
      values[i][3] =   followUpService.findConsuNum(obj.getLong("id"))+"";//专家咨询次数
      List<TDoctorPatientRelation> list = doctorPatientRelationService.findByPatIdAndType(obj.getLong("id"),0);
      StringBuffer docName = new StringBuffer();
      StringBuffer startTime = new StringBuffer();
      for(TDoctorPatientRelation relation:list){
         //TDoctorPatientRelation relation = list.size() > 0? list.get(0):null;
         if(relation != null){
            Long doctorId =relation .getDoctorid();//莱康的签约医生只有一个
            if(doctorId != null){
               docName.append( userProfileService.findUserByUid(doctorId).getUserName());
               docName.append("，");
            }
            String startTimeStr = DateUtil.getDateStr(relation.getStartTime(),DateUtil.FORMAT_DATE_TIME_3);
            startTime.append(DateUtil.formatDate(startTimeStr ,DateUtil.FORMAT_DATE_TIME_3, DateUtil.FORMAT_DATE_TIME_1));
            startTime.append("，");
         }
      }

      String docNameList = "";
      String startTimeList = "";
      if(docName.length()>0){
         docNameList = docName.toString();
         docNameList = docNameList.substring(0,docNameList.length()-1);
      }

      if(startTime.length() > 0){
         startTimeList = startTime.toString();
         startTimeList = startTimeList.substring(0,startTimeList.length()-1);
      }

      values[i][4] =   docNameList;//专家姓名
      values[i][5] =   patientBookService.selectCountByPatientId(obj.getLong("id")) + "";//陪诊次数
      values[i][6] =   startTimeList;//签约日期
      String registerTime= DateUtil.formatDate( obj.getString("registerTime"),DateUtil.FORMAT_DATE_TIME_3, DateUtil.FORMAT_DATE_TIME_1);
      values[i][7] =   registerTime;//注册时间
   }
   //logger.info("values list is [ {} ]", JSON.toJSONString(values));
   HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);

   downloadFile(fileName, wb, response);
   return null;
}

三、转成文件流

private void downloadFile(String fileName,HSSFWorkbook wb,HttpServletResponse response){
   //HSSFWorkbook对象转成File
   File file = new File(fileName);
   try {
      byte[] bytes= wb.getBytes();
      OutputStream output = new FileOutputStream(file);
      wb.write(output);
   } catch (IOException e) {
      e.printStackTrace();
   }
   download(file,fileName, response);
}

 

四、输出文件流

public void download(File file , String fileName, HttpServletResponse response){
      log.info("-----------------下载开始---------------------");
      try {
         byte[] bytes ;

         bytes = FileUtils.readFileToByteArray(file);
              fileName = URLEncoder.encode(fileName, "UTF-8"); 
         response.reset();
         response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
         response.addHeader("Content-Length", "" + bytes.length);
         response.setContentType(fileName);
         
//       response.setContentType("application/octet-stream;charset=utf-8");
//
//       response.setHeader("Content-Disposition", "attachment;filename="
//             + new String(fileName.getBytes(),"iso-8859-1") + ".xls");


         InputStream in = new ByteArrayInputStream(bytes);
         OutputStream outputSream = response.getOutputStream();
         int len = 0;
         byte[] buf = new byte[1024];
         while ((len = in.read(buf, 0, 1024)) != -1) {
            outputSream.write(buf, 0, len);
         }

         outputSream.write(buf);
         outputSream.flush();
         outputSream.close();
         in.close();
         log.info("-----------------下载结束---------------------");
      } catch (IOException e) {
         log.error("error:{}",e);
         throw new ServiceException("01", "文件下载失败!",fileName);
      }
   }

 

记得在pom文件中引入

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.5-FINAL</version>
   <exclusions>
      <exclusion>
         <groupId>log4j</groupId>
         <artifactId>log4j</artifactId>
      </exclusion>
   </exclusions>
</dependency>