POI

POI设置单元格格式问题:

package com.yht.ryg.controller;

import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
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.HSSFDataFormat;
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.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.yht.ryg.commons.dto.ProjectReportDto;

public class ProjectInfoReportViewExcel extends AbstractExcelView {

@Override
protected void buildExcelDocument(Map<String, Object> map,
HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
String excelName = "项目报表.xls";
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename="+URLEncoder.encode(excelName, "utf-8"));

List auList = (List) map.get("list");

//创建 表格,设置标题(名称,合并单元格范围)
HSSFSheet sheet = workbook.createSheet("项目报表");
HSSFCell header = sheet.createRow(0).createCell(0);
header.setCellValue("项目报表");
//通过workbook创建新的格式,仅对引用它的单元格有效,对其他单元格无效.可理解为新图层对老图层的叠加.
HSSFCellStyle style = workbook.createCellStyle();
//通过cell(单元格)获取原有的格式,会对整张表格产生影响.可理解为修改了老图层.
// HSSFCellStyle style = header.getCellStyle();
//同一类型的格式只能设置一种,不能叠加,后设置的会覆盖先设置的.
//如设置了先设置了上下居中,后设置了左右居中,最终显示左右居中.
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
header.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0,2,0,9));

//设置列宽(1个单位为字高的1/256)
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 2000);
sheet.setColumnWidth(4, 4000);
sheet.setColumnWidth(5, 8000);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 4000);
sheet.setColumnWidth(8, 4000);
sheet.setColumnWidth(9, 2000);

HSSFRow title = sheet.createRow(3);
title.createCell(0).setCellValue("项目名称");
title.createCell(1).setCellValue("监管仓地址");
title.createCell(2).setCellValue("客户经理");
title.createCell(3).setCellValue("监管员");
title.createCell(4).setCellValue("质权人");
title.createCell(5).setCellValue("出质人");
title.createCell(6).setCellValue("融资金额（元）");
title.createCell(7).setCellValue("项目开始时间");
title.createCell(8).setCellValue("项目结束时间");
title.createCell(9).setCellValue("状态");

//设置日期显示格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
//设置BigDecimal格式的数据保留两位有效小数
DecimalFormat df = new DecimalFormat("#.00");

int rowNum = 4;
for (Iterator iterator = auList.iterator(); iterator.hasNext();) {

ProjectReportDto element = (ProjectReportDto) iterator.next();
HSSFRow row = sheet.createRow(rowNum++);

//项目名称
if(element.getName() == null){//允许表格内数据为空,否则报空指针异常
row.createCell(0).setCellValue("");
}else {
row.createCell(0).setCellValue(element.getName().toString());
}

//监管仓地址
if (element.getAddressShow() == null) {
row.createCell(1).setCellValue("");
}else {
row.createCell(1).setCellValue(element.getAddressShow().toString());
}

//客户经理
if (element.getCreditManName() == null) {
row.createCell(2).setCellValue("");
}else {
row.createCell(2).setCellValue(element.getCreditManName().toString());
}

//监管员
if (element.getSupMemName() == null) {
row.createCell(3).setCellValue("");
}else {
row.createCell(3).setCellValue(element.getSupMemName().toString());
}

//质权人
if (element.getFinOrgName() == null) {
row.createCell(4).setCellValue("");
}else {
row.createCell(4).setCellValue(element.getFinOrgName().toString());
}

//出质人
if (element.getShipperName() == null) {
row.createCell(5).setCellValue("");
}else {
row.createCell(5).setCellValue(element.getShipperName().toString());
}

//融资金额（元）
if (element.getFinAmount() == null) {
row.createCell(6).setCellValue("");
}else {
row.createCell(6).setCellValue(df.format(element.getFinAmount()));
}

//项目开始时间
if (element.getLoanStartDate() == null) {
row.createCell(7).setCellValue("");
}else {
row.createCell(7).setCellValue(sdf.format(element.getLoanStartDate()));
}

//项目结束时间
if (element.getLoanEndDate() == null) {
row.createCell(8).setCellValue("");
}else {
row.createCell(8).setCellValue(sdf.format(element.getLoanEndDate()));
}

//状态
if (element.getStatus() == null) {
row.createCell(9).setCellValue("");
}else {
row.createCell(9).setCellValue(element.getStatus().toString());
}
}

HSSFRow row = sheet.createRow(rowNum);
row.createCell(10).setCellValue("总计(条):");
row.createCell(11).setCellValue(auList.size());
}


}