JAVA WEB ------ 文件下载及导出数据到office Execl表格
文件下载需要五步：
1.设置文件ContentType类型
// 设置文件ContentType类型，这样设置，会自动判断下载文件类型 response.setContentType("multipart/form-data");
2.设置文件头
// 设置文件头：最后一个参数是设置下载文件名 response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes(),"ISO8859-1") + ".xls");
3.获取输出流(out)
// 获取输出流 out = res.getOutputStream();
4.写到输出流(out)中
5.关闭资源
--------华丽的分割线-------web项目导出Excel文档
POM ：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15-beta1</version> </dependency>
Class ： RestController
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); } }
Class ： @Service
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); } } } }
啦啦啦
啦啦啦
啦啦啦
啦啦啦
