java 导出excel
首先进入控制器
@RequestMapping(value="account/exportWithPopularizeDetail")
public void exportWithPopularizeDetail(HttpServletRequest request, HttpServletResponse response,String fnickName,String fpuserNickName){
FileInputStream in = null;
OutputStream out = null;
File file = null;
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-excel;charset=utf-8");
String dateTime = DateUtil.DateToStr(new Date(), "yyyyMMddHHmmss");
String fileName = "推广明细统计记录" + dateTime + ".xls";
response.setHeader("Content-Disposition","attachment;fileName="+URLEncoder.encode(fileName,"UTF-8"));
String proPath = request.getSession().getServletContext().getRealPath("/");
String path = proPath+File.separator+"tempFile";
file = new File(path);
if(!file.isDirectory()){
file.mkdir();
}
file = new File(path+File.separator+String.valueOf(System.currentTimeMillis())+".xls");
if(!file.exists()){
file.createNewFile();
}
userBo.exportPopularizeDetailExcel(fnickName,fpuserNickName,new FileOutputStream(file));
out = response.getOutputStream();
in = new FileInputStream(file);
byte[] b = new byte[1024];
int i=0;
while((i=in.read(b))>0){
out.write(b, 0, i);
}
out.flush();
} catch (IOException e) {
e.printStackTrace();
}finally{
try{
if(in!=null){
in.close();
}
if(out!=null){
out.close();
}
if(file!=null){
file.delete();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
服务层 条件查询出所需要打印的list
public void exportPopularizeDetailExcel(String fnickName,String fpuserNickName,OutputStream out){
String title = "推广明细统计";
//excel 头
String[] headers = {"昵称","推广","推广日期"};
String pattern = "yyyy-MM-dd HH:mm:ss";
UserInfoDTO userInfoDTO = new UserInfoDTO();
userInfoDTO.setFnickName(fnickName);
userInfoDTO.setFrealName(fpuserNickName);
List<PopularizedtlDTO> list = this.getpopularizeDetail(userInfoDTO);
ExcelExportUtils<PopularizedtlDTO> util = new ExcelExportUtils<PopularizedtlDTO>();
util.exportExcel(title, headers, list, out, pattern);
}
工具类导出excel到临时文件的 文件输出流里
public class ExcelExportUtils<T> { @SuppressWarnings({ "resource"}) public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 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("leno"); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); Field field = fields[i]; field.setAccessible(true); try { if(field.getName().equals("stop")){ break; } Object value = field.get(t); // 判断值的类型后进行强制类型转换 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(new HSSFRichTextString( String.valueOf(fValue))); } else if (value instanceof Double) { double dValue = (Double) value; cell.setCellValue(new HSSFRichTextString( String.valueOf(dValue))); } else if (value instanceof Long) { long longValue = (Long) value; cell.setCellValue(longValue); } 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{ // 其它数据类型都当作字符串简单处理 textValue = value==null?"":value.toString(); cell.setCellValue(textValue); } // 如果不是图片数据，就利用正则表达式判断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); // } // } } catch (SecurityException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); }finally{ try { out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } } } }
