JAVAWEB文件下载及导出数据到officeExecl表格

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

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

// 获取输出流out &＃61; res.getOutputStream();

　　<dependency>
　　　　<groupId>org.apache.poigroupId>
　　　　<artifactId>poiartifactId><version>3.15-beta1version>
　　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;&＃64;RestController
public class PerfAct extends BaseAct{&＃64;Resourceprivate PerfService service;&＃64;Resourceprivate PerfActValidate validate;&＃64;Resourceprivate ExportExcel exportExcel;&＃64;RequestMapping(value &＃61; "/perf" , method &＃61; RequestMethod.GET)public String perf(HttpServletRequest req,HttpServletResponse res , PerfReqParam param){res.setHeader("Access-Control-Allow-Origin", "*");ModelMapWriter writer &＃61; new ModelMapWriter();validate.perf(param);if(param.hasErrors()){writer &＃61; ModelMapWriter.createErrWriter(param);}else{if(param.getExport()){OutputStream out &＃61; null;try {String title &＃61; "来福士PM2.5浓度监测值统计报表";List list &＃61; service.getPerfsExport(param);String[] headers &＃61; {"监测点","统计时间","PM2.5浓度平均值","温度平均值","湿度平均值"};String[] headersName &＃61; {"name","perfTime","pm25","temp","humi"};
// 设置文件ContentType类型&＃xff0c;这样设置&＃xff0c;会自动判断下载文件类型 res.setContentType("multipart/form-data");
// 设置文件头&＃xff1a;最后一个参数是设置下载文件名 res.addHeader("Content-Disposition", "attachment;filename&＃61;" &＃43; new String(title.getBytes(),"ISO8859-1") &＃43; ".xls");
// 获取输出流out &＃61; res.getOutputStream();exportExcel.exportExcel(title, headers, headersName, list, out);} catch (IOException e) {e.printStackTrace();}finally{try {if(null !&＃61; null){out.close();}} catch (IOException e) {e.printStackTrace();}}}else{PageRespWriter query &＃61; 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;&＃64;Service
public class ExportExcel {public void exportExcel(String title, String[] headers,String[] headersName,Collection dataset, OutputStream out) {exportExcel(title, headers, headersName, dataset, out, "yyyy-MM-dd HH:mm:ss");}/** 这是一个通用的方法&＃xff0c;利用JAVA的反射机制&＃xff0c;可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上* &＃64;param title 表格标题名* &＃64;param headers 表格属性列名数组* &＃64;Param headersName 字段数组&＃xff0c;用来获取反射方法。* &＃64;param dataset 需要显示的数据集合&＃xff0c;集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String&＃xff0c;Date&＃xff0c;byte[]&＃xff08;图片数据)* &＃64;param out 与输出设备关联的流对象&＃xff0c;可以将EXCEL文档导出到本地文件或者网络中。* &＃64;param pattern 如果有时间数据&＃xff0c;设定输出格式。默认为“yyyy-MM-dd”*/public void exportExcel(String title,String[] headers,String[] headersName, Collection dataset,OutputStream out,String pattern){
// 声明一个工作簿HSSFWorkbook workbook &＃61; new HSSFWorkbook();
// 声明一个表格HSSFSheet sheet &＃61; workbook.createSheet(title);
// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth((short)15);
// 生成一个单元格样式&＃xff1a;表头单元格样式HSSFCellStyle styleCaptaion &＃61; workbook.createCellStyle();
// 设置表头单元格样式&＃xff1a;设置单元格背景色
// styleCaptaion.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
// 设置表头单元格样式&＃xff1a;指定单元格的填充信息模式和纯色填充单元。
// styleCaptaion.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置表头单元格样式&＃xff1a;设置边框的类型为单元格的右边界
styleCaptaion.setBorderBottom(HSSFCellStyle.BORDER_THIN);styleCaptaion.setBorderTop(HSSFCellStyle.BORDER_THIN);styleCaptaion.setBorderLeft(HSSFCellStyle.BORDER_THIN);styleCaptaion.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置表头单元格样式&＃xff1a;设置单元格为水平对齐的类型
styleCaptaion.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体&＃xff1a;表头字体HSSFFont fontCaptaion &＃61; workbook.createFont();
// 生成一个字体&＃xff1a;设置字体颜色
fontCaptaion.setColor(HSSFColor.VIOLET.index);
// 生成一个字体&＃xff1a;设置字体大小fontCaptaion.setFontHeightInPoints((short)12);
// 生成一个字体&＃xff1a;字体加粗
fontCaptaion.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前单元格样式中
styleCaptaion.setFont(fontCaptaion);// 生成内容单元格样式HSSFCellStyle styleContent &＃61; 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 &＃61; workbook.createFont();fontContent.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到内容单元格样式中
styleContent.setFont(fontContent);// 声明一个画图的顶级管理器HSSFPatriarch patriarch &＃61; sheet.createDrawingPatriarch();
// 定义注释的大小和位置HSSFComment comment &＃61; patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));
// 设置注释内容comment.setString(new HSSFRichTextString("可以在POI中添加注释"));
// 设置注释作者&＃xff0c;当鼠标移动到单元格上时可以在状态栏中看到该内容comment.setAuthor("lime");// 产生表格标题行HSSFRow row &＃61; sheet.createRow(0);for(short i &＃61; 0;i ){HSSFCell cell &＃61; row.createCell(i);cell.setCellStyle(styleCaptaion);HSSFRichTextString text &＃61; new HSSFRichTextString(headers[i]);cell.setCellValue(text);}// 遍历集合数据&＃xff0c;产生数据行Iterator it &＃61; dataset.iterator();int index &＃61; 0;while(it.hasNext()){row &＃61; sheet.createRow(&＃43;&＃43;index);T t &＃61; (T)it.next();Classextends Object> tCls &＃61; t.getClass();for(short i &＃61; 0;i ){HSSFCell cell &＃61; row.createCell(i);String getterName &＃61; "get" &＃43; headersName[i].substring(0, 1).toUpperCase() &＃43; headersName[i].substring(1);Method method &＃61; null;Object value &＃61; null;try {method &＃61; tCls.getDeclaredMethod(getterName);value &＃61; 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 !&＃61; 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 &＃61; null;if(value instanceof Integer){int intValue &＃61; (Integer)value;cell.setCellValue(intValue);}else if(value instanceof Float){float fValue &＃61; (Float)value;cell.setCellValue(fValue);}else if(value instanceof Double){double dValue &＃61; (Double)value;cell.setCellValue(dValue);}else if(value instanceof Long){long longValue &＃61; (Long)value;cell.setCellValue(longValue);}else if(value instanceof Boolean){boolean bValue &＃61; (Boolean)value;textValue &＃61;"男";if(!bValue){textValue &＃61; "女";}cell.setCellValue(textValue);}else if(value instanceof Date){Date date &＃61; (Date)value;SimpleDateFormat sdf &＃61; new SimpleDateFormat(pattern);textValue &＃61; sdf.format(date);cell.setCellValue(textValue);}else if(value instanceof byte[]){
// 有图片时&＃xff0c;设置行高为60px&＃xff1b;row.setHeightInPoints(60);
// 设置图片所在列宽度为80px&＃xff0c;注意这里单位的一个换算sheet.setColumnWidth(i,(short)35.7*80);
// sheet.autoSizeColumn(i);byte[] bsValue &＃61; (byte[])value;HSSFClientAnchor anchor &＃61; 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 &＃61;&＃61; value){value &＃61; "未知区域";}textValue &＃61; value.toString();}
// 如果不是图片数据&＃xff0c;就利用正则表达式判断textValue是否全部有数字组成if(textValue !&＃61; null){Pattern p &＃61; Pattern.compile("^//d&＃43;(//.//d&＃43;)?$");Matcher matcher &＃61; p.matcher(textValue);if(matcher.matches()){
// 是数字当double处理
cell.setCellValue(Double.parseDouble(textValue));}else{HSSFRichTextString richString &＃61; new HSSFRichTextString(textValue);HSSFFont font3 &＃61; workbook.createFont();font3.setColor(HSSFColor.BLUE.index);richString.applyFont(font3);cell.setCellValue(richString);}}}}