下载Excel工具类(JXL)

package com.agx.base.commons.util;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang3.StringUtils;
/**
 * 
 * @author Peter
 * @date 2016-11-23
 *
 */
public class DownloadExcel {
	
	private DownloadExcel(){
		throw new Error("you can't instantiate this class: DownloadExcel ");
	}
	
	/**
	 * @param response
	 * @param fileName Excel文件名
	 * @param titles 表头
	 * @param fieldMap
 
   键为字段类型，值为对应Excel表的列索引，下标从0开始 
	 * @param list 数据
	 * @return
	 * @author Peter
	 */
	public static 
  
    boolean exportExcel(HttpServletResponse response,String fileName,String[] titles,Map
   
     fieldMap, List
    
      list){ /**创建工作簿*/ WritableWorkbook workbook = null; // 取得输出流 OutputStream os = null; try { /**定义输出流，以便打开保存对话框 */ os = response.getOutputStream();// 取得输出流 response.reset();// 清空输出流 if(StringUtils.lastIndexOfIgnoreCase(fileName, ".xls")<=0) fileName="sheet1.xls"; response.setHeader("Content-disposition", "attachment; filename="+ new String(fileName.getBytes("GB2312"),"ISO8859-1")); // 设定输出文件头 response.setContentType("application/msexcel");// 定义输出类型 /**创建工作簿*/ workbook = Workbook.createWorkbook(os); /**创建工作表*/ //Sheet1表示Excel表左下角的名字，后面的0表示第几个 WritableSheet sheet = workbook.createSheet("Sheet1", 0); /** 设置纵横打印（默认为纵打）、打印纸 */ jxl.SheetSettings sheetset = sheet.getSettings(); sheetset.setProtected(false); /** 设置单元格字体*/ WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); //用于正文 WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);//用于标题 /** 以下设置单元格样式 */ // 用于标题居中 WritableCellFormat wcf_center = new WritableCellFormat(BoldFont); wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直居中 wcf_center.setAlignment(Alignment.CENTRE); // 文字水平居中 wcf_center.setWrap(false); // 文字不换行 // 用于正文格式 WritableCellFormat wcf_left = new WritableCellFormat(NormalFont); //wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条 wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直居中 wcf_left.setAlignment(Alignment.CENTRE); // 文字水平居中 wcf_left.setWrap(true); // 超过列宽自动换行 /** EXCEL开头大标题，暂时省略*/ //sheet.mergeCells(0, 0, colWidth, 0); //sheet.addCell(new Label(0, 0, "XX报表", wcf_center)); //EXCEL第一行列标题 if(titles!=null){ //设置行高 sheet.setRowView(0, 500); //设置标题的每一列 for (int i = 0; i < titles.length; i++) { sheet.addCell(new Label(i, 0,titles[i],wcf_center)); } } /** 向Excel添加数据 */ Field[] fields=null; int i=1; //表示第几行 if(list!=null){ for(Object obj:list){ Class
      c = obj.getClass(); fields = c.getDeclaredFields(); for(Field v:fields){ v.setAccessible(true); String filedName = v.getName(); if(fieldMap!=null){ if(fieldMap.containsKey(filedName)){ Method m = c.getMethod("get"+filedName.substring(0,1).toUpperCase()+filedName.substring(1)); Object va = m.invoke(obj); if(va==null) va=""; int columnIndex = fieldMap.get(filedName); //创建一行新数据 sheet.addCell(new Label(columnIndex, i,va.toString(),wcf_left)); //设置行高 sheet.setRowView(i, 400); //设置列宽:因该项目中第一条数据的内容长度将会是最长的， //所以我们只用第一条数据的内容长度与标题名作比较,谁长就谁作为列宽 if(i==1){ if(titles[fieldMap.get(filedName)]!=null){ if(va.toString().getBytes().length>titles[columnIndex].getBytes().length){ //第一个参数是列索引，第二个参数是宽度 sheet.setColumnView(columnIndex, va.toString().getBytes().length+2); }else{ //根据标题宽度自适应 sheet.setColumnView(columnIndex, titles[columnIndex].getBytes().length+2); } } } } } } i++; } } /** 将以上缓存中的内容写到EXCEL文件中*/ workbook.write(); } catch (Exception e) { e.printStackTrace(); return false; } finally{ // 关闭 try { workbook.close(); os.close(); } catch (Exception e) { e.printStackTrace(); return false; } } return true; } }
    
   
  
 

/**

	 * 专辑概况下载接口

	 */
	@RequestMapping(value="/exportAlbumSummary",method=RequestMethod.GET)
	public BaseResult exportExcel(HttpServletRequest request,HttpServletResponse response,
            @RequestParam(required=false)String startTime,@RequestParam(required=false)String endTime){		
		BaseResult result = new BaseResult();		
		if(endTime==null)
			endTime=DateFormatUtils.format(DateTime.now().getMillis(), "yyyy-MM-dd");			
		//Excel表文件名

		String fileName = null;
		if(startTime==null){
			fileName = "截止到"+endTime+"专辑概况统计表.xls";
		}else{
			fileName = startTime+"~"+endTime+"专辑概况统计表.xls";
		}
		//表头

		String[] titles = {"日期","总专辑量","总时长","通过专辑量","通过时长","未通过专辑量",
				"未通过时长","待审核专辑量","待审核时长","上线专辑量","上线时长","下线专辑量","下线时长"};
		//概况数据 

	    List
 
   list = statAlbumService.getAlbumStatImageList(1, startTime, endTime);
	    //字段及其对应列索引

	    Map
  
    fieldMap = new HashMap
   
    (); fieldMap.put("time", 0); fieldMap.put("totalNums", 1); fieldMap.put("totalDuration", 2); fieldMap.put("adoptNums", 3); fieldMap.put("adoptDuration", 4); fieldMap.put("discardNums", 5); fieldMap.put("discardDuration",6); fieldMap.put("uncheckedNums", 7); fieldMap.put("uncheckedDuration", 8); fieldMap.put("onlineNums", 9); fieldMap.put("onlineDuration", 10); fieldMap.put("soldNums", 11); fieldMap.put("soldDuration", 12); //导出 if(! DownloadExcel.exportExcel(response,fileName, titles, fieldMap, list)) result.failure("系统提示，导出Excel失败！"); return result; }