java.poi

public class CoServerBackExcel extends AbstractExcelView{

/**
* 合并单元格
* @param sheet 所在表
* @param row 所在行
* @param cellStyle 样式
* @param firstRow 合并开始行
* @param lastRow 合并结束行
* @param firstCol 合并开始列
* @param lastCol 合并结束列
* @param cellRangeAddress 合并区域
* @param cell 单元格
* @param cellNum 单元格序号
* @param value 单元格值
*/
protected static void addCell(HSSFSheet sheet, HSSFRow row, HSSFCellStyle cellStyle, int firstRow, int lastRow, int firstCol, int lastCol, CellRangeAddress cellRangeAddress, HSSFCell cell, int cellNum, String value) {
cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cellRangeAddress);
cell = row.createCell(cellNum);
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
}

/**
* 创建excel表格
*/
@Override
protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
String date = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
String excelName = "返点未收表_" + date + ".xls";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName,"utf-8"));

List lists = (List)model.get("list");

//产生Excel表
HSSFSheet sheet = workbook.createSheet("返点未收表_" + date);

// 设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));

//行
HSSFRow row = null;
//单元格
HSSFCell cell = null;
//合并区域
CellRangeAddress cellRangeAddress = null;

//第1行
row = sheet.createRow(0);
addCell(sheet, row, cellStyle, 0, 1, 0, 0, cellRangeAddress, cell, 0, "业主姓名");
addCell(sheet, row, cellStyle, 0, 1, 1, 1, cellRangeAddress, cell, 1, "电话");
addCell(sheet, row, cellStyle, 0, 1, 2, 2, cellRangeAddress, cell, 2, "所在地区");
addCell(sheet, row, cellStyle, 0, 1, 3, 3, cellRangeAddress, cell, 3, "房屋类型");
addCell(sheet, row, cellStyle, 0, 0, 4, 6, cellRangeAddress, cell, 4, "客服确认签约金额");
addCell(sheet, row, cellStyle, 0, 0, 7, 9, cellRangeAddress, cell, 7, "销售确认签约金额");
addCell(sheet, row, cellStyle, 0, 0, 10, 12, cellRangeAddress, cell, 10, "财务确认签约金额");
addCell(sheet, row, cellStyle, 0, 1, 13, 13, cellRangeAddress, cell, 13, "状态");

//第2行
row = sheet.createRow(1);
addCell(sheet, row, cellStyle, 1, 1, 4, 4, cellRangeAddress, cell, 4, "设计金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 5, 5, cellRangeAddress, cell, 5, "施工金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 6, 6, cellRangeAddress, cell, 6, "合计(元)");
addCell(sheet, row, cellStyle, 1, 1, 7, 7, cellRangeAddress, cell, 7, "设计金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 8, 8, cellRangeAddress, cell, 8, "施工金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 9, 9, cellRangeAddress, cell, 9, "合计(元)");
addCell(sheet, row, cellStyle, 1, 1, 10, 10, cellRangeAddress, cell, 10, "设计金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 11, 11, cellRangeAddress, cell, 11, "设计金额(元)");
addCell(sheet, row, cellStyle, 1, 1, 12, 12, cellRangeAddress, cell, 12, "合计(元)");

//填充数据
int rownum = 2;
for(Iterator iter = lists.iterator();iter.hasNext();){
AssignBackResponse element = (AssignBackResponse)iter.next();
row = sheet.createRow(rownum++);
row.createCell(0).setCellValue(element.getUserName());
row.createCell(1).setCellValue(element.getUserMobilePhone());
row.createCell(2).setCellValue(element.getUserAddress());
row.createCell(3).setCellValue(element.getHouseType());

/*客服*/
row.createCell(4).setCellValue(element.getDesignCostCustomer().doubleValue());
row.createCell(5).setCellValue(element.getFitmentCostCustomer().doubleValue());
row.createCell(6).setCellValue(element.getTotalCostCustomer().doubleValue());

/*销售*/
row.createCell(7).setCellValue(element.getDesignCostSeller().doubleValue());
row.createCell(8).setCellValue(element.getFitmentCostSeller().doubleValue());
row.createCell(9).setCellValue(element.getTotalCostSeller().doubleValue());

/*财务*/
row.createCell(10).setCellValue(element.getDesignCostFinance().doubleValue());
row.createCell(11).setCellValue(element.getFitmentCostFinance().doubleValue());
row.createCell(12).setCellValue(element.getTotalCostFinance().doubleValue());

row.createCell(13).setCellValue(element.getBackCostState());
}
}
}