工具类
package com.banxue.kmsservice.helper;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import rx.internal.util.LinkedArrayList;
import javax.servlet.http.HttpServletResponse;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
public class ExcelExportHelper {
public static void writeExcelHeader(HttpServletResponse response,String fileName) throws Exception{
if(!(fileName.endsWith(".xls") || fileName.endsWith("xlsx"))){
fileName+=".xlsx";
}
response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("gb2312"),"ISO8859-1")); //设置文件头编码格式
response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型
response.setHeader("Cache-Control","no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头
}
public static void writeExcelData(HttpServletResponse response, JSONArray dataAraay, Map<String,String> map, String sheetName) throws Exception{
XSSFWorkbook book=new XSSFWorkbook();
XSSFSheet sheet=book.createSheet(sheetName);
//创建表头
Iterator<String> iterator = map.keySet().iterator();
Row row = sheet.createRow(0);
int i = 0;
LinkedList list = new LinkedList();
while (iterator.hasNext()){
String key = iterator.next();
Cell cell = row.createCell(i);
cell.setCellValue(map.get(key));
list.add(key);
i++;
}
//写入数据
for(int j = 0; j < dataAraay.size(); j++){
JSONObject jsonObject = JSONObject.fromObject(dataAraay.get(j));
Row dataRow = sheet.createRow(j+1);
for(int k = 0; k < list.size(); k++){
dataRow.createCell(k).setCellValue(String.valueOf(jsonObject.get(list.get(k))));
}
}
book.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
}
引用:
//获取活动下可以发货的数据 List<Map> recordDaos = laXinActivityExchangeRecordMapperUser.selectWaitDeliverByActivityId(activityId); //生成excel ExcelExportHelper.writeExcelHeader(response,DateHelper.getDateTimeString(new Date(),"yyyy-MM-dd")); //设置字段对应的含义值 Map map = new HashMap(); map.put("id","id"); map.put("wx_name","用户微信名"); map.put("receive_user","收货人"); map.put("receive_phone","收货电话"); map.put("receive_address","收货地址"); map.put("receive_memo","备注"); map.put("quantity","申请数量"); map.put("id","id"); //写入数据并下载excel ExcelExportHelper.writeExcelData(response,JSONArray.fromObject(recordDaos),map,DateHelper.getDateTimeString(new Date(),"yyyy-MM-dd"));