package com.eastriver.comm.util;
import java.io.ByteArrayOutputStream;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
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.log4j.Logger;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
/**
* 下载Excel通用类.
* @Company hnxtsoft Corporation
* @author admin
* @version 1.0
*/
public class DownLoadByExcel {
private final static String CONTENT_TYPE = "application/vnd.ms-excel;charset=gb2312";
private final static String HEADER = "CONTENT-DISPOSITION";
private final static String FILENAME = "attachment; filename=downloadexcelfile.xls";
private final static Logger logger =Logger.getLogger(DownLoadByExcel.class);
/**
* 通用下载Excel方法.
* @param response
* @param columns 下载的Excel文件头标题
* @param fields 下载List 中包含的Bean中要显示的字段
* @param list 下载内容集合
* @param title 报表名称
*/
public static void download(final HttpServletResponse response,
final String[] columns, final String[] fields, final List<?> list,
String title) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
response.setContentType(CONTENT_TYPE);
try{
WritableWorkbook book = Workbook.createWorkbook(baos);
WritableSheet sheet = book.createSheet(title+"导出", 0);
//设置logo文件样式
WritableFont font_logo = new WritableFont(WritableFont.createFont("黑体"), 14, WritableFont.BOLD);
WritableCellFormat format_logo = new WritableCellFormat(font_logo);
//format_logo.setBackground(Colour.WHITE, Pattern.NONE);
format_logo.setAlignment(Alignment.CENTRE);
format_logo.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置TITLE文件样式
WritableFont font_title = new WritableFont(WritableFont.createFont("黑体"), 10, WritableFont.BOLD);
WritableCellFormat format_title = new WritableCellFormat(font_title);
format_title.setBackground(Colour.GRAY_50);
format_title.setBorder(Border.ALL, BorderLineStyle.THIN);
format_title.setAlignment(Alignment.CENTRE);
format_title.setVerticalAlignment(VerticalAlignment.CENTRE);
//添加Excel头标记
//File file = new File(path + "/images/logo.png");
//WritableImage image = new WritableImage(0, 0, 1, 1, file);
//image.setHeight(0.82);
//image.setWidth(0.85);
//sheet.setRowView(0,1200);
//sheet.addImage(image);
//添加标题
Label logotitle = new Label(0,0, title, format_logo);
sheet.addCell(logotitle);
sheet.mergeCells(0,0,columns.length - 1,0);
//添加列标题
for (int i = 0; i < columns.length; i++) {
Label label = new Label(i, 1, columns[i],format_title);
sheet.setColumnView(i, 13);
sheet.addCell(label);
}
int row = 2;
if(list!=null && list.size()>0){
for(int i = 0;i < list.size();i ++){
Object object = list.get(i);
Label lbl = null;
for(int j = 0;j < fields.length;j ++){
BeanWrapper wrapper = new BeanWrapperImpl(object);
Object content = wrapper.getPropertyValue(fields[j]);
if("id".equals(fields[j])) content = String.valueOf(i+1);
//String content = String.valueOf(wrapper.getPropertyValue(fields[j]));
if(null == content)content = "";
lbl = new Label(j, row, String.valueOf(content));
sheet.addCell(lbl);
}
row ++;
}
}
book.write();
book.close();
ServletOutputStream out = response.getOutputStream();
response.setHeader(HEADER,FILENAME);
response.setContentLength(baos.size());
baos.writeTo(out);
out.flush();
baos.close();
}catch(Exception ex){
logger.error("download list with excel was error:",ex);
}
}
}