1、Util
package com.quancheng.util;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import javax.servlet.http.HttpServletResponse;
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;
/**
* 导出excel工具类
*
* @author Administrator
*/
public class ExcelUtilPro {
/**
* 根据数据返回excel表格
*
* @param mbUserVo:数据
* @return
*/
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);
}
if (values.length > 0) {
//创建内容
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;
}
public static Integer outPutExcel(HttpServletResponse response, String fileName, String[] title, String sheetName, String[][] content) {
HSSFWorkbook wb = ExcelUtilPro.getHSSFWorkbook(sheetName, title, content, null);
//将文件存到指定位置
try {
setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
return -3;
}
return 1;
}
/**
* 将表格转码
*
* @param response
* @param fileName
*/
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;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();
}
}
}
2、使用
/**
* 导出活动订单
*
* @param request
* @param source
*/
@RequestMapping("/excel")
public void excel(HttpServletRequest request, HttpServletResponse response, String source) {
List<ActivityOrder> activityOrders = new ArrayList<>();
try {
activityOrders = activityService.selectActivityOrderBySource(source);
//excel标题
String[] title = {"平台", "商品名字", "商家名字", "销售数量", "金额"};
//excel文件名
String fileName = "活动订单表" + System.currentTimeMillis() + ".xls";
//sheet名
String sheetName = "活动订单信息表";
String[][] content = new String[activityOrders.size()][];
for (int i = 0; i < activityOrders.size(); i++) {
content[i] = new String[title.length];
content[i][0] = activityOrders.get(i).getSource();
content[i][1] = activityOrders.get(i).getShopName();
content[i][2] = activityOrders.get(i).getBusinessName();
content[i][3] = activityOrders.get(i).getSaleNum().toString();
content[i][4] = activityOrders.get(i).getTotalMoney().toString();
}
ExcelUtilPro.outPutExcel(response, fileName, title, sheetName, content);
} catch (Exception e) {
logger.info("查询活动订单列表失败" + e.getMessage());
e.printStackTrace();
}
}