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 +
;
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 +
;
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 +
;
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 +
;
sheet.addMergedRegion(region);
cell = row.createCell(k * 3 +
;
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;
}
分享到:
相关推荐
JAVA使用POI生成简单的Excel文件并下载,并设置了字体大小。
1.java使用poi生成excel插入图表曲线 2.demo包含完成jar包 3.主要用途是过滤数据自动生成曲线图
POI生成Excel POI读取Excel java操作Excel Servlet生成Excel web项目,包含含读取Excel与生成Excel方法
使用poi生成Excel所需要的jar包,poi-3.9.jar
Java用POI实现根据Excel表格模板生成新的Excel并实现数据输出...
Java POI 生成Excel时显示货币格式
apache软件基金 poi组件 轻松实现,生成excel报表
Demo1 把数据写到excel表中, Demo2 在Demo1生成的excel中添加新的数据 Demo3 和Demo1一样生成一个excel表格 剩下的那个是从数据库取数据放到excel表格
jsp利用POI生成Excel并在页面中导出的示例.docx
POI操作Excel生成水印,该水印其实就是一个图片,该代码是将文字生成图片,再将图片变成水印的。
java代码调用poi架包, 把数据生成excel文件, 导出到文件
poi生成excel及合并单元格示例,详见文章http://blog.csdn.net/geloin/article/details/17219885
java poi 根据excel模板生成excel文件,利用poijar包根据已有excel模板,生成基于模板的新文件,jar包也在压缩包内,导入即可使用。
poi 生成excel模板,下拉选项,批注。现在有模板中插入下拉信息
平时工作中用到的一些技术点,拿出来和大家共享一下。 有些功能比较粗糙,如果大家有更加完善的示例或建议,欢迎交流。 1、jpg图片缩略图:ImageUtil ...2、poi创建excel:CDSHdlReport 3、计算表达式求值:Cal
使用apach poi 导出Excel,可直接在项目中使用使用apach poi 导出Excel,可直接在项目中使用使用apach poi 导出Excel,可直接在项目中使用
将assets中.xml中的doc文件中,就会生成word文档和excel文档
POI导入Excel并返回校验后的错误文件(原样数据文件,并添加批注,注:由于批注只能加1000条,会在Excel后面添加一栏错误信息)下载以及页面展示校验错误信息,同时添加导入进度条,提供页面js和css代码,后端...
poi 导出EXCEL 例子 poi 导出EXCEL 例子poi 导出EXCEL 例子poi 导出EXCEL 例子
poi生成excel报表合并列和列,按规范构造数据集就可以,mergeRow 合并行,genMergeCellByContent 合并头