package ;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.struts2.ServletActionContext;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
/**
* excel 导出工具
* @author
*
*/
public class ClassifyExcelModel {
/**
* todo
* @param list xx
* @param response xx
*/
public void export(List list, HttpServletResponse response) {
try {
HSSFWorkbook workbook = exportExcel(list);
if (workbook != null) {
//this.printExcel(workbook, response, java.net.URLEncoder.encode("我的客户.xls", "UTF-8"));
this.printExcel(workbook, response, "分类客户模板.xls");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* to do
* @param list xx
* @return xx
* @throws Exception e
*/
public HSSFWorkbook exportExcel(List list) throws Exception {
HSSFWorkbook workbook = null;
try {
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 这里的数据即时你要从后台取得的数据
this.fileData(list, workbook, "模板", 0);
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* todo
* @param workbook xx
* @param response xx
* @param filename xx
* @throws IOException xx
*/
private void printExcel(HSSFWorkbook workbook, HttpServletResponse response, String filename) throws IOException {
OutputStream out = response.getOutputStream();
try {
response.reset();
response.resetBuffer();
response.setContentType("application/x-download");
String userAgent = ServletActionContext.getRequest().getHeader("User-Agent");
if (userAgent.indexOf("MSIE") != -1) {
response.addHeader("Content-Disposition", "attachment;filename=" + new String( filename.getBytes("GB2312"), "ISO8859-1" ));
} else if (userAgent.indexOf("Mozilla") != 1) {
response.addHeader("Content-Disposition", "attachment;filename=" + new String( filename.getBytes("UTF-8"), "ISO8859-1" ));
}
//filename = new String(filename.getBytes("iso-8859-1"),"");
//filename = new String(filename.getBytes("utf-8"),"iso-8859-1");
//filename = java.net.URLDecoder.decode(filename, "utf-8");
//response.setContentType("application/msexcel;charset=UTF-8");
//response.setHeader("Content-disposition", "attachment; filename=" + filename);
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
response.flushBuffer();
}
}
/**
* sdf
* @param dataList xx
* @param workbook xx
* @param sheetName xx
* @param count xx
*/
private void fileData(List dataList, HSSFWorkbook workbook, String sheetName, int count) {
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet(sheetName);
workbook.setSheetName(count, sheetName);
// 设置列宽
this.setSheetColumnWidth(sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
HSSFCellStyle style1 = this.createTitleStyle1(workbook);
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行
//this.createCell(row, 0, style1, HSSFCell.CELL_TYPE_STRING, "公司ID");
this.createCell(row, 0, style1, HSSFCell.CELL_TYPE_STRING, "客户名称");
this.createCell(row, 1, style1, HSSFCell.CELL_TYPE_STRING,"隶属分类");
this.createCell(row, 2, style1, HSSFCell.CELL_TYPE_STRING,"城市");
this.createCell(row, 3, style1, HSSFCell.CELL_TYPE_STRING,"数据状态");
this.createCell(row, 4, style1, HSSFCell.CELL_TYPE_STRING,"数据来源");
this.createCell(row, 5, style1, HSSFCell.CELL_TYPE_STRING,"创建时间");
//自定义属性
for (int i = 0; i < dataList.size(); i++){
ClassifyHoldProperty cp = (ClassifyHoldProperty)dataList.get(i);
Propertymate pt = cp.getPropertymate();
if (pt != null) {
String propName = cp.getPropertymate().getPropertyName();
this.createCell(row, (6+i), style1, HSSFCell.CELL_TYPE_STRING, propName);
}
}
}
/**
* to do
* @param sheet xx
*/
private void setSheetColumnWidth(HSSFSheet sheet) {
// 根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth((short) 0, (short) 5000);
sheet.setColumnWidth((short) 1, (short) 8000);
sheet.setColumnWidth((short) 2, (short) 5000);
sheet.setColumnWidth((short) 3, (short) 10000);
sheet.setColumnWidth((short) 4, (short) 8000);
}
/**
* to do
* @param wb xx
* @return xx
*/
//设置第一行标题的样式
private HSSFCellStyle createTitleStyle1(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 240);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
// style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("G/通用格式"));
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置字体居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return style;
}
// 设置excel的title样式
/**
* xx
* @param wb xx
* @return xx
*/
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 240);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
return style;
}
/**
* xx
* @param row xx
* @param column xx
* @param style xx
* @param cellType xx
* @param value xx
*/
// 创建Excel单元格
private void createCell(HSSFRow row, int column, HSSFCellStyle style,
int cellType, Object value) {
HSSFCell cell = row.createCell((short) column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_STRING:
if (value != null) {
cell.setCellValue(new HSSFRichTextString(value.toString()));
} else {
cell.setCellValue(new HSSFRichTextString(""));
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// DecimalFormat format = new DecimalFormat("###,##0.00");
// cell.setCellValue(Float.parseFloat(value.toString()));
cell.setCellValue(Double.parseDouble(value.toString()));
break;
default:
break;
}
}
}
window.open('/xxAction?method=exportExcel&index='+uuid);
HttpServletResponse response = ServletActionContext.getResponse();
ExportExcel exportExcel = new ExportExcel();
exportExcel.export(list,response);