JAVA WEB ------ 文件下载及导出数据到office Execl表格

// 设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("multipart/form-data");

// 设置文件头：最后一个参数是设置下载文件名
response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes(),"ISO8859-1") + ".xls");

// 获取输出流
out = res.getOutputStream();

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15-beta1</version>
</dependency>

package com.xindatai.ibs.device.act;import java.io.IOException;
import java.io.OutputStream;
import java.util.List;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;import com.xindatai.common.web.BaseAct;
import com.xindatai.common.web.resp.ModelMapWriter;
import com.xindatai.common.web.resp.PageRespWriter;
import com.xindatai.ibs.device.act.bean.PerfReqParam;
import com.xindatai.ibs.device.act.validate.PerfActValidate;
import com.xindatai.ibs.device.bean.PerfPM25;
import com.xindatai.ibs.device.service.PerfService;
import com.xindatai.ibs.util.ExportExcel;@RestController
public class PerfAct extends BaseAct{ @Resource
private PerfService service;
@Resource
private PerfActValidate validate;
@Resource
private ExportExcel<PerfPM25> exportExcel; @RequestMapping(value = "/perf" , method = RequestMethod.GET)
public String perf(HttpServletRequest req,HttpServletResponse res , PerfReqParam param){
res.setHeader("Access-Control-Allow-Origin", "*");
ModelMapWriter writer = new ModelMapWriter();
validate.perf(param);
if(param.hasErrors()){
writer = ModelMapWriter.createErrWriter(param);
}else{
if(param.getExport()){
OutputStream out = null;
try {
String title = "来福士PM2.5浓度监测值统计报表";
List<PerfPM25> list = service.getPerfsExport(param);
String[] headers = {"监测点","统计时间","PM2.5浓度平均值","温度平均值","湿度平均值"};
String[] headersName = {"name","perfTime","pm25","temp","humi"};
// 设置文件ContentType类型，这样设置，会自动判断下载文件类型
res.setContentType("multipart/form-data");
// 设置文件头：最后一个参数是设置下载文件名
res.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes(),"ISO8859-1") + ".xls");
// 获取输出流
out = res.getOutputStream();
exportExcel.exportExcel(title, headers, headersName, list, out);
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
if(null != null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}else{
PageRespWriter<PerfPM25> query = service.getPerfs(param);
return toJsonFormatDate(query);
}
}
return toJsonFormatDate(writer);
}
}

package com.xindatai.ibs.util;import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Service;@Service
public class ExportExcel<T> { public void exportExcel(String title, String[] headers,String[] headersName,Collection<T> dataset, OutputStream out) {
exportExcel(title, headers, headersName, dataset, out, "yyyy-MM-dd HH:mm:ss");
} /*
* 这是一个通用的方法，利用JAVA的反射机制，可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @Param headersName 字段数组，用来获取反射方法。
* @param dataset 需要显示的数据集合，集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String，Date，byte[]（图片数据)
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中。
* @param pattern 如果有时间数据，设定输出格式。默认为“yyyy-MM-dd”
*/
public void exportExcel(String title,String[] headers,String[] headersName, Collection<T> dataset,OutputStream out,String pattern){
// 声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 声明一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short)15);
// 生成一个单元格样式：表头单元格样式
HSSFCellStyle styleCaptaion = workbook.createCellStyle();
// 设置表头单元格样式：设置单元格背景色
// styleCaptaion.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
// 设置表头单元格样式：指定单元格的填充信息模式和纯色填充单元。
// styleCaptaion.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置表头单元格样式：设置边框的类型为单元格的右边界
styleCaptaion.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCaptaion.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCaptaion.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCaptaion.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置表头单元格样式：设置单元格为水平对齐的类型
styleCaptaion.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体：表头字体
HSSFFont fontCaptaion = workbook.createFont();
// 生成一个字体：设置字体颜色
fontCaptaion.setColor(HSSFColor.VIOLET.index);
// 生成一个字体：设置字体大小
fontCaptaion.setFontHeightInPoints((short)12);
// 生成一个字体：字体加粗
fontCaptaion.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前单元格样式中
styleCaptaion.setFont(fontCaptaion);// 生成内容单元格样式
HSSFCellStyle styleContent = workbook.createCellStyle();
styleContent.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
styleContent.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成内容字体
HSSFFont fontContent = workbook.createFont();
fontContent.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到内容单元格样式中
styleContent.setFont(fontContent);// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释"));
// 设置注释作者，当鼠标移动到单元格上时可以在状态栏中看到该内容
comment.setAuthor("lime");// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for(short i = 0;i < headers.length;i++){
HSSFCell cell = row.createCell(i);
cell.setCellStyle(styleCaptaion);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while(it.hasNext()){
row = sheet.createRow(++index);
T t = (T)it.next();
Class<? extends Object> tCls = t.getClass();
for(short i = 0;i < headers.length;i++){
HSSFCell cell = row.createCell(i);
String getterName = "get" + headersName[i].substring(0, 1).toUpperCase() + headersName[i].substring(1);
Method method = null;
Object value = null;
try {
method = tCls.getDeclaredMethod(getterName);
value = method.invoke(t);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
verify(value, cell, row, pattern, sheet, index, patriarch, workbook, i);
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}finally{
if(null != out){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} public void verify(Object value,HSSFCell cell,HSSFRow row, String pattern, HSSFSheet sheet, int index, HSSFPatriarch patriarch, HSSFWorkbook workbook, int i){
// 判断值的类型后进行强制类型转换
String textValue = null;
if(value instanceof Integer){
int intValue = (Integer)value;
cell.setCellValue(intValue);
}else if(value instanceof Float){
float fValue = (Float)value;
cell.setCellValue(fValue);
}else if(value instanceof Double){
double dValue = (Double)value;
cell.setCellValue(dValue);
}else if(value instanceof Long){
long longValue = (Long)value;
cell.setCellValue(longValue);
}else if(value instanceof Boolean){
boolean bValue = (Boolean)value;
textValue ="男";
if(!bValue){
textValue = "女";
}
cell.setCellValue(textValue);
}else if(value instanceof Date){
Date date = (Date)value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
}else if(value instanceof byte[]){
// 有图片时，设置行高为60px；
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px，注意这里单位的一个换算
sheet.setColumnWidth(i,(short)35.7*80);
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[])value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short)6,index,(short)6,index);
// anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}else{
// 其他数据类型都当作字符串简单处理
if(null == value){
value = "未知区域";
}
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部有数字组成
if(textValue != null){
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if(matcher.matches()){
// 是数字当double处理
cell.setCellValue(Double.parseDouble(textValue));
}else{
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}}