JExcelApi使用记录

import java.io.IOException;import java.io.UnsupportedEncodingException;import java.net.URLEncoder;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Random;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import jxl.Workbook;import jxl.format.Colour;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import org.apache.log4j.Logger;/** * jexcelApi工具类 *  * @author Yaozb 2015/7/20 */public class JExcelApiUtil {final static Logger log = Logger.getLogger(JExcelApiUtil.class);/** * 生成文件存储路径 *  * @Author Yaozb 2015/7/21 * @return */public String createFilName(String reportName) {return reportName+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + (new Random().nextInt(900) + 100);}/** * 生成excel表格 *  * @author Yaozb 2015/7/21 * @param response HttpServletResponse   * @param reportName 报表名称 * @param titles 表头 * @param keys 数据key值 * @param list 数据列表 * @throws WriteException * @throws IOException */public ServletOutputStream createExcel(HttpServletRequest request,HttpServletResponse response,String reportName,String[] titles,List<HashMap<String,Object>> list,int columnWidth)throws WriteException, IOException {//String fileName = createFilName(reportName);String fileName = reportName;setFileDownloadHeader(request,response,fileName);//创建工作薄ServletOutputStream os = response.getOutputStream();WritableWorkbook workbook = Workbook.createWorkbook(os);//创建新的一页WritableSheet sheet = workbook.createSheet(reportName, 0);//创建报表名称//用于标题sheet.mergeCells(0, 0, titles.length-1, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行        WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示        WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象        titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中        titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中        titleFormate.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);        Label title = new Label(0,0,reportName,titleFormate);        sheet.setRowView(0, 600, false);//设置第一行的高度        sheet.addCell(title);            //创建titleWritableFont bold2 = new WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD);        WritableCellFormat titleFormate2 = new WritableCellFormat(bold2);        titleFormate2.setAlignment(jxl.format.Alignment.CENTRE);        titleFormate2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);        titleFormate2.setBackground(Colour.GRAY_25);        titleFormate2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);for(int i=0;i<titles.length;i++){Label cell = new Label(i, 1, titles[i],titleFormate2);sheet.addCell(cell);sheet.setColumnView(i, columnWidth);}sheet.setRowView(1, 400, false);//设置第二行的高度        //创建内容        WritableCellFormat titleFormate3 = new WritableCellFormat();        titleFormate3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);for(int i=0;i<list.size();i++){HashMap<String, Object> map = list.get(i);for(int j=0;j<titles.length;j++){Label label = new Label(j,i+2,map.get(titles[j]).toString(),titleFormate3);//第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容sheet.addCell(label);}}//把创建的内容写入到输出流中，并关闭输出流workbook.write();workbook.close();return os;}public void setFileDownloadHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {response.setCharacterEncoding("UTF-8");response.setContentType("application/vnd.ms-excel;charset=utf-8");        final String userAgent = request.getHeader("USER-AGENT");        try {            String finalFileName = null;            if(userAgent.indexOf("MSIE")!=-1){                finalFileName = URLEncoder.encode(fileName,"UTF8");            }else if(userAgent.indexOf("Mozilla")!=-1){//google,火狐浏览器                finalFileName = new String(fileName.getBytes(), "ISO8859-1");            }else{                finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器            }            response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + ".xls\"");//这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开        } catch (UnsupportedEncodingException e) {        }    }}