用POI 生成Excel 并下载

public String exportExcel() {
/**
* 1: 生成标题 2：读取数据 3：设置数据 4：生成响应
*/
Company company = null;
List<Department> departments = null;
Department department = null;
BudgetSplit split = budgetSplitService.getSplitById(splitId);

// 表标题
if (split.getMainBodyType() == 1) { // 单位
company = organizationService.getCompanyById(split.getMainBody());
departments = organizationService.listDepartmentByCompany(split
.getMainBody());
} else if (split.getMainBodyType() == 2) { // 部门
department = organizationService.getDepartmentById(split
.getMainBody());
departments = organizationService.getChildrenDepartment(split
.getMainBody());
}

String fatherName = (company == null) ? ((department == null) ? ""
: department.getName()) : company.getName();

HSSFWorkbook workbook = new HSSFWorkbook();

String title = split.getCycleYear() + "_" + fatherName + "_预算分解";

HSSFSheet sheet = workbook.createSheet(title);
String header[] = { "年", "指标编码", "指标名称" };
HSSFRow row = sheet.createRow(0);

for (int i = 0; i < header.length; i++) {
CellRangeAddress region = new CellRangeAddress(0, 1, i, i);
sheet.addMergedRegion(region);
HSSFCell cell = row.createCell(i);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(header[i]);
}

// 生成分解表里的父部门 标题

CellRangeAddress region = new CellRangeAddress(0, 0, 3, 5);
sheet.addMergedRegion(region);
HSSFCell cell = row.createCell(3);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(fatherName);

// 生成子部门标题
for (int j = 0; j < departments.size(); j++) {
Department dep = departments.get(j);
region = new CellRangeAddress(0, 0, j * 3 + 6, j * 3 + 8);
sheet.addMergedRegion(region);
cell = row.createCell(j * 3 + 6);
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(dep.getName());
}

row = sheet.createRow(1);
cell = row.createCell(3);
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("预算金额");

cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("分解合计");

cell = row.createCell(5);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("结余");

// 生成子部门
for (int k = 0; k < departments.size(); k++) {
region = new CellRangeAddress(0, 0, k * 3 + 6, k * 3 + 8);
sheet.addMergedRegion(region);
cell = row.createCell(k * 3 + 6);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("本部门上报金额");

region = new CellRangeAddress(0, 0, k * 3 + 6, k * 3 + 8);
sheet.addMergedRegion(region);
cell = row.createCell(k * 3 + 7);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("调整金额");

region = new CellRangeAddress(0, 0, k * 3 + 6, k * 3 + 8);
sheet.addMergedRegion(region);
cell = row.createCell(k * 3 + 8);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("预算金额");
}

// 设置值
List<Map<String, Object>> listMap = budgetSplitService
.loadBatchSplitIndexes(split);
for (int m = 0; m < listMap.size(); m++) {
Map dataRow = listMap.get(m);
row = sheet.createRow(m + 2);
cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(String.valueOf(dataRow.get("year")));

cell = row.createCell(1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(String.valueOf(dataRow.get("indexCode")));

cell = row.createCell(2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(String.valueOf(dataRow.get("indexName")));

// 设置总公司的值
cell = row.createCell(3);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("0.0");

cell = row.createCell(4);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("0.0");

cell = row.createCell(5);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("0.0");

for (int n = 6; n < departments.size() * 3 + 6; n += 3) {
Department dept = departments.get((n - 6) / 3);
cell = row.createCell(n);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(getValue(dataRow, dept, "forwardValue_"));
cell = row.createCell(n + 1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(getValue(dataRow, dept, "adjustValue_"));
cell = row.createCell(n + 2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(getValue(dataRow, dept, "splitValue_"));
}
}

try {
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/msexcel;charset=UTF-8");
response.setHeader("content-disposition", "attachment;filename=\""
+ URLEncoder.encode(title, "UTF-8") + ".xls\"");
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}

private String getValue(Map dataRow, Department dept, String str) {
String ret = String.valueOf(dataRow.get(str + dept.getId()));
return ret.equals("") ? "0.0" : ret;
}