因为前台Extjs导出excel有缺点,所以需要在后台导出excel,主要是用apache的poi
下载地址http://poi.apache.org/
package com.shs.crm.excel;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
public class Excel {
public HSSFWorkbook exportExcel(List<Contact> contacts){
//创建一个新的excel
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = workbook.createSheet();
//设置每列的宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 4000);
sheet.setColumnWidth(4, 5000);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
//设置headStyle样式
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setFont(font);
//普通cellStyle样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置sheet名称
workbook.setSheetName(0, "通讯录");
HSSFRow row = sheet.createRow(0);
//设置行高
row.setHeight((short)500);
//row.setRowStyle(style);
HSSFCell cell0 = row.createCell(0);
HSSFCell cell1 = row.createCell(1);
HSSFCell cell2 = row.createCell(2);
HSSFCell cell3 = row.createCell(3);
HSSFCell cell4 = row.createCell(4);
//设置字符集
cell0.setCellType(HSSFCell.ENCODING_UTF_16);
cell1.setCellType(HSSFCell.ENCODING_UTF_16);
cell2.setCellType(HSSFCell.ENCODING_UTF_16);
cell3.setCellType(HSSFCell.ENCODING_UTF_16);
cell4.setCellType(HSSFCell.ENCODING_UTF_16);
//设置cell样式
cell0.setCellStyle(headStyle);
cell1.setCellStyle(headStyle);
cell2.setCellStyle(headStyle);
cell3.setCellStyle(headStyle);
cell4.setCellStyle(headStyle);
//设置内容
cell0.setCellValue("姓名");
cell1.setCellValue("性别");
cell2.setCellValue("手机");
cell3.setCellValue("生日");
cell4.setCellValue("地址");
for (int i = 0; i < contacts.size(); i++) {
row = sheet.createRow(i+1);
row.setHeight((short)350);
cell0 = row.createCell(0);
cell1 = row.createCell(1);
cell2 = row.createCell(2);
cell3 = row.createCell(3);
cell4 = row.createCell(4);
//设置cell样式
cell0.setCellStyle(cellStyle);
cell1.setCellStyle(cellStyle);
cell2.setCellStyle(cellStyle);
cell3.setCellStyle(cellStyle);
cell4.setCellStyle(cellStyle);
//设置字符集
cell0.setCellType(HSSFCell.ENCODING_UTF_16);
cell1.setCellType(HSSFCell.ENCODING_UTF_16);
cell2.setCellType(HSSFCell.ENCODING_UTF_16);
cell3.setCellType(HSSFCell.ENCODING_UTF_16);
cell4.setCellType(HSSFCell.ENCODING_UTF_16);
//设置内容
cell0.setCellValue(contacts.get(i).getKhmc());
cell1.setCellValue(contacts.get(i).getKhxb());
cell2.setCellValue(contacts.get(i).getKhsj());
cell3.setCellValue(contacts.get(i).getKhsr());
cell4.setCellValue(contacts.get(i).getKhdz());
}
return workbook;
}
}
到处excel:
Excel excel = new Excel();
HSSFWorkbook workbook = excel.exportExcel(contacts);
resp.reset();
resp.setContentType("application/msexcel;charset=UTF-8");
try {
resp.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode("通讯录.xls", "UTF-8"));
OutputStream out = resp.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}