POI 导出Excel

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

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.HSSFRichTextString;
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;

/**
* 导出Excel公共类
* <p>Title:ExportExcel </p>
* <p>Description: </p>
* <p>Company: </p>
* @author David
* @date May 31, 2016 9:49:50 AM
*/
public class ExcelExport {

//显示的导出表的标题
private String title;

//导出表的列名
private String[] rowName ;

private List<Object[]> dataList = new ArrayList<Object[]>();

private HttpServletResponse response;

//构造方法，传入要导出的数据
public ExcelExport(String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.response=response;
}

/**
* 导出数据
* @throws Exception
*/
public void export() throws Exception{
//创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = workbook.createSheet(title);
//获取列头样式对象
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
//单元格样式对象
HSSFCellStyle style = this.getStyle(workbook);
//合并单元格
//sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
// 定义所需列数
int columnNum = rowName.length;
// 创建行
HSSFRow rowRowName = sheet.createRow(0);
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
//设置列头单元格的值
cellRowName.setCellValue(text);
//设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
//遍历每个对象
Object[] obj = dataList.get(i);
//创建所需的行数
HSSFRow row = sheet.createRow(i+1);
for(int j=0; j<obj.length; j++){
//设置单元格的数据类型
HSSFCell cell = null;
if(j == 0){
cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i+1);
}else{
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(null!=obj[j]&&!"".equals(obj[j])){
//设置单元格的值
cell.setCellValue(obj[j].toString());
}
}
//设置单元格样式
cell.setCellStyle(style);
}
}
for (int colNum = 0; colNum < columnNum; colNum++) {
sheet.setColumnWidth(colNum, 5000);
}
if(workbook !=null){
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream out = response.getOutputStream();
workbook.write(out);
}
}

/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}

/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)10);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}

}

//调用的地方
/**
* 导出
* @param request
* @param response
*/
@RequestMapping(value = "/exportcustomerExcel")
public void exportcustomerExcel(HttpServletRequest request,HttpServletResponse response){
String title = "用户信息";
String[] rowsName = new String[]{"序号","用户账号","昵称","手机","注册日期","来源","角色"};
List<Object[]> dataList = new ArrayList<Object[]>();
try{
UserCondition condition=UserCondition.convertCondition(request,"c.CreateTime");
List<Customer> customerList=customerService.findAllCustomer(condition);
int i=0;
for (Customer customer:customerList) {
String[] obj = new String[rowsName.length];
obj[0] = i+"";
obj[1] = customer.getLoginName();
obj[2] = customer.getNikeName();
obj[3] = customer.getMobile();
obj[4] = customer.getRegTimeStr();
obj[5] = customer.getRegFrom();
obj[6] = customer.getGroupName();
dataList.add(obj);
i++;
}
ExcelExport viewExcel = new ExcelExport(title,rowsName,dataList,response);
viewExcel.export();
}catch(Exception ce){
ce.printStackTrace();
log.error("export customer to Excel error",ce);
}
}