ExcelExportUtil.calss
package com.common.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class ExcelExportUtil {
/**
*
* @Title: exportManySheetExcel
* @Description: 可生成单个、多个sheet
* @param @param file 导出文件路径
* @param @param mysheets
* @return void
* @throws
*/
public static Workbook exportManySheetExcel(List<ExcelExp> mysheets){
HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
List<ExcelExp> sheets = mysheets;
//表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short)12);
//fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(fontStyle);
for(ExcelExp excel: sheets){
//新建一个sheet
HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称
String[] handers = excel.getHanders();//获取sheet的标题名
HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题
//写标题
for(int i=0;i<handers.length;i++){
//获取第一行的每个单元格
HSSFCell cell = rowFirst.createCell(i);
//往单元格里写数据
cell.setCellValue(handers[i]);
cell.setCellStyle(style); //加样式
sheet.setColumnWidth(i, 4000); //设置每列的列宽
}
//写数据集
List<String[]> dataset = excel.getDataset();
for(int i=0;i<dataset.size();i++){
String[] data = dataset.get(i);//获取该对象
//创建数据行
HSSFRow row = sheet.createRow(i+1);
for(int j=0;j<data.length;j++){
//设置对应单元格的值
row.createCell(j).setCellValue(data[j]);
}
}
}
return wb;
}
public static void outputXls(Workbook workbook, String fileName, HttpServletResponse response, HttpServletRequest request) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+encodeFileName(fileName + ".xls",request));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
public static String encodeFileName(String fileName,HttpServletRequest request)
throws UnsupportedEncodingException {
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE")) {
return URLEncoder.encode(fileName, "UTF-8");
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
return "=?UTF-8?B?"
+ (new String(
org.apache.commons.codec.binary.Base64
.encodeBase64(fileName.getBytes("UTF-8"))))
+ "?=";
} else {
return fileName;
}
}
/**
* 把list<map>封装成list<String[]>
* 由于我的结果集是List<Map<String,Object>>,所以我写了这个个方法,把它转换成List<String[]>
* @param list 要封装的list
* @param strNumber String[]的长度
* @return
*/
public static List<String []> listUtils(List<Map<String,Object>> list,String [] strKey){
if(list!=null && list.size()>0) {//如果list有值
List<String []> strList=new ArrayList<String []>();//实例化一个list<string[]>
for(Map<String,Object> map:list) {//遍历数组
String [] str= new String[strKey.length];//实力一个string[]
Integer count=0;//作为str的下标,每次从0开始
for(String s:strKey) {//遍历map中的key
str[count]=map.get(s).toString();//把map的value赋值到str数组中
count++;//str的下标+1
}
if(str!=null) {//如果str有值,添加到strList
strList.add(str);
}
}
if(strList!=null&&strList.size()>0) {//如果strList有值,返回strList
return strList;
}
}
return null;
}
}
ExcelExp
package com.common.excel;
import java.util.List;
public class ExcelExp {
private String fileName;// sheet的名称
private String[] handers;// sheet里的标题
private List<String[]> dataset;// sheet里的数据集
public ExcelExp(String fileName, String[] handers, List<String[]> dataset) {
this.fileName = fileName;
this.handers = handers;
this.dataset = dataset;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getHanders() {
return handers;
}
public void setHanders(String[] handers) {
this.handers = handers;
}
public List<String[]> getDataset() {
return dataset;
}
public void setDataset(List<String[]> dataset) {
this.dataset = dataset;
}
}
controller
package com.core.controller;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
public class controller {
/**
* 业务逻辑实现层
*/
@Autowired
private Service service;
/**
* 导出
* @return
*/
@ResponseBody
@RequestMapping(value = "/chartExcel", method = RequestMethod.GET)
public Object retailUserCompriseChartExcel() {
try {
String fileName = "平台交易分析-交易构成-用户构成-趋势图";//导出的文件名
//第一页sheet的数据
List<Map<String,Object>> list1=service.findByDataInfo();//调用service得到数据
//第二页sheet的数据
List<Map<String,Object>> list2=service.findByDataInfo();//调用service得到数据
//第三页sheet的数据
List<Map<String,Object>> list3=service.findByDataInfo();//调用service得到数据
//导出头(第一行标题头)
String[] nameList = { "编号","姓名","年龄","性别"};
//map的key
String[] str = { "id","name","age","sex"};
//对象1-->第一页sheet的数据
ExcelExp e1 =null;
if(list1!=null&&list1.size()>0) {
List<String[]> list=ExcelExportUtil.listUtils(list1, str);
if(list!=null&&list.size()>0) {
e1 = new ExcelExp("第一页sheet", nameList,list);//这里的第一页指的是第一页的名字
}
}
//对象2-->第二页sheet的数据
ExcelExp e2 =null;
if(list2!=null&&list2.size()>0) {
List<String[]> list=ExcelExportUtil.listUtils(list2, str);
if(list!=null&&list.size()>0) {
e2 = new ExcelExp("第二页sheet", nameList,list);//这里的第一页指的是第一页的名字
}
}
//对象3-->第三页sheet的数据
ExcelExp e3 =null;
if(list3!=null&&list3.size()>0) {
List<String[]> list=ExcelExportUtil.listUtils(list3, str);
if(list!=null&&list.size()>0) {
e3 = new ExcelExp("第三页sheet", nameList,list);//这里的第一页指的是第一页的名字
}
}
List<ExcelExp> mysheet = new ArrayList<ExcelExp>();
if(e1!=null) {//如果e1有值,就导出
mysheet.add(e1);
}
if(e2!=null) {//如果e2有值,就导出
mysheet.add(e2);
}
if(e3!=null) {//如果e3有值,就导出
mysheet.add(e3);
}
Workbook workbook= ExcelExportUtil.exportManySheetExcel(mysheet); //生成sheet
ExcelExportUtil.outputXls(workbook, fileName, response, request);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
pom.xml
<!-- 导出excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
完美!!!