这是我的后端代码:
/**
* 导出年度项目预算
* @param proProject
* @param response
*/
public static void exportYearProBud(ProProject proProject,List<ProProject> regularList,List<ProProject> disposableList,List<ProProject> informatizationList, HttpServletResponse response) {
//map的key对应Excel单元格里的值
Map<String, Object> map = new HashMap<String, Object>();
//获取当前日期并格式化
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
map.put("${fillDate}", "填报日期:"+sdf.format(date)); //填报日期
map.put("${title}",proProject.getThisYear()+"~"+(proProject.getAfterYear())+"年市本级财政项目预算(按项目)");//标题
map.put("${thisYear}",proProject.getThisYear()+"年申报预算"); //选择的年份
map.put("${LastYear}",(proProject.getThisYear()-1)+"年批复数"); //选择的年份的前年
map.put("${nextYear}",(proProject.getNextYear())+"年申报预算"); //选择的年份的明年
map.put("${afterYear}",(proProject.getAfterYear())+"年申报预算"); //选择的年份的后年
//遍历经常性项目
Map<String, Object> regularMap = proProjectList(regularList,proProject.getThisYear(),"regular");
map.putAll(regularMap);
//遍历一次性项目
Map<String, Object> disposableMap = proProjectList(disposableList,proProject.getThisYear(),"disposable");
map.putAll(disposableMap);
//遍历信息化项目
Map<String, Object> informatizationMap = proProjectList(informatizationList,proProject.getThisYear(),"informatization");
map.putAll(informatizationMap);
int regularSize = regularList.size();
int disposableSize = disposableList.size();
int informatizationSize = informatizationList.size();
//导出Excel文件
exportYearProBud(proProject,regularSize,disposableSize,informatizationSize,map,response);
}
/**
* 遍历项目信息
* @param list
* @param flag
* @return
*/
public static Map<String, Object> proProjectList(List<ProProject> list, int year, String flag){
Map<String, Object> map = new HashMap<String, Object>();
//遍历项目信息
for(int i=0; i<list.size(); i++){
map.put("${"+flag+"["+i+"].kind}", list.get(i).getBudSystem().getKind());
map.put("${"+flag+"["+i+"].paragraph}", list.get(i).getBudSystem().getParagraph());
map.put("${"+flag+"["+i+"].item}", list.get(i).getBudSystem().getItem());
map.put("${"+flag+"["+i+"].name}", list.get(i).getName());
map.put("${"+flag+"["+i+"].budProClass.name}", list.get(i).getBudProClass().getName());
map.put("${"+flag+"["+i+"].budSystem.name}", list.get(i).getBudSystem().getName());
map.put("${"+flag+"["+i+"].content}", list.get(i).getContent());
if(list.get(i).getTotalMoney1()!=null && !"".equals(list.get(i).getTotalMoney1())){
map.put("${"+flag+"["+i+"].thisYear}", list.get(i).getTotalMoney1());
}
if(list.get(i).getTotalMoney2()!=null && !"".equals(list.get(i).getTotalMoney2())){
map.put("${"+flag+"["+i+"].nextYear}", list.get(i).getTotalMoney2());
}
if(list.get(i).getTotalMoney3()!=null && !"".equals(list.get(i).getTotalMoney3())){
map.put("${"+flag+"["+i+"].afterYear}", list.get(i).getTotalMoney3());
}
map.put("${"+flag+"["+i+"].number}", ++i+"");
--i;
}
return map;
}
/**
* 根据map导出Excel文件
* @param proProject
* @param map
* @param response
*/
public static void exportYearProBud(ProProject proProject,int regularSize, int disposableSize, int informatizationSize,
Map<String, Object> map, HttpServletResponse response){
//读取的模板文件的位置(在resources下的resources文件夹里面)
String srcPath = GetResource.class.getClassLoader().getResource("/resources/某某年支出项目预算.xls").getPath();
String filrName = proProject.getThisYear()+"年项目支出项目预算.xls";
OutputStream os = null;
try {
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(srcPath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
int j = 0;
while(rows.hasNext()){
HSSFRow row = (HSSFRow) rows.next();
if(row!=null) {
int num = row.getLastCellNum();
j++;
for(int i=0; i<num; i++) {
if(j==4 || j==5 || j==6 || j==28 || j==50){
break;
}
HSSFCell cell = row.getCell(i);
if(cell!=null){
String value= cell.toString();
if(value==null || "".equals(value)) {
continue;
}
if(!"".equals(value)) {
Set<String> keySet = map.keySet();
Iterator<String> it = keySet.iterator();
while (it.hasNext()) {
String text = it.next();
if(value.equalsIgnoreCase(text)) {
if(i>=9 && j!=3){
BigDecimal totalMoney = new BigDecimal(map.get(text).toString());
cell.setCellValue(totalMoney.doubleValue());
}else{
cell.setCellValue((String)map.get(text));
}
break;
}
}
} else {
cell.setCellValue("");
}
}
}
}
}
//删掉多余的行
int delRow = 7; //删除行
sheet = deleteCell(sheet,sheet.getLastRowNum(),regularSize,delRow);
int delRow2 = 29-(20-regularSize); //删除行(初始),要减掉上面删除的行
sheet = deleteCell(sheet,sheet.getLastRowNum(),disposableSize,delRow2);
int delRow3 = 51-(40-regularSize-disposableSize); //删除行(初始),要减掉上面删除的行
sheet = deleteCell(sheet,sheet.getLastRowNum(),informatizationSize,delRow3);
HSSFRow row = sheet.getRow(sheet.getLastRowNum());
sheet.removeRow(row);
//检索替换
retrieve(rows,sheet,regularSize,disposableSize,informatizationSize);
//刷新公式
sheet.setForceFormulaRecalculation(true);
response.reset();
os = response.getOutputStream(); //获取响应的输出流
response.setContentType("text/html; charset=UTF-8"); //设置编码字符
response.setContentType("application/octet-stream"); //设置内容类型为下载类型
response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(filrName, "utf-8"));
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
if(os != null){
try {
os.flush();//释放缓存
os.close();//关闭输出流
} catch (Exception e) {
logger.info("关闭输出流失败:"+e.getMessage() );
e.printStackTrace();
}
}
}
}
/**
* 删除预留多余的行
* @param sheet
* @param lastRow
* @param size
* @param delRow
* @return
*/
public static HSSFSheet deleteCell(HSSFSheet sheet, int lastRow, int size, int delRow){
int totalDelRow = 20; //预留的行
delRow += size;
totalDelRow -= size; //要删除的行数量
for(int i=0;i<totalDelRow;i++){
sheet.shiftRows(delRow,lastRow-1,-1);
}
return sheet;
}
/**
* 检索替换
* @param rows
* @param sheet
* @param regularSize
* @param disposableSize
*/
public static void retrieve(Iterator<Row> rows, HSSFSheet sheet, int regularSize, int disposableSize, int informatizationSize){
rows = sheet.rowIterator();
int j = 0;
while(rows.hasNext()){
HSSFRow row1 = (HSSFRow) rows.next();
j++;
if(row1!=null) {
int num = row1.getLastCellNum();
for(int i=0; i<num; i++) {
if(j<=6 || j==(28-20)+regularSize || j==(50-40)+regularSize+disposableSize){
if(j==6 || j==(28-20)+regularSize || j==(50-40)+regularSize+disposableSize){
row1.setHeight((short) 600);//目的是想把行高设置成600px(30磅)
}
break;
}else if(j>=14+regularSize+disposableSize+informatizationSize){
row1.setHeight((short) 400);//目的是想把行高设置成400px
}
HSSFCell cell = row1.getCell(i);
if(cell!=null){
String value= cell.toString();
if(value==null || "".equals(value)) {
continue;
}
if(value.contains("${")){//检索Excel表中包含有"${"字符的单元格替换为""
cell.setCellValue("");
}
}
}
}
}
}