execl多sheet导出
来源:互联网 发布:安卓手机运行java游戏 编辑:程序博客网 时间:2024/06/08 17:28
/**
* 按款级导出
* @param id
* @param redirectAttributes
* @return
*/
@SuppressWarnings("deprecation")
@RequestMapping(value = "exploreExecl")
public String exploreExecl(BudgetOutgoDirectCost budgetOutgoDirectCost, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
String BudgetOutgoBillId=request.getParameter("bibId");
BudgetOutgoBill budgetOutgoBill=new BudgetOutgoBill();
budgetOutgoBill.setId(BudgetOutgoBillId);
budgetOutgoBill.setStatus(null);
budgetOutgoBill.setSqlMap(sqlMap);
budgetOutgoBill.setHalfYear(budgetOutgoDirectCost.getHalfYear());
budgetOutgoBill=budgetOutgoBillService.get(budgetOutgoBill);
budgetOutgoDirectCost.setBudgetOutgoBill(budgetOutgoBill);
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//第二步,通过预算单id在本级直接支出中找到该预算单的所有的款科目
//List<BudgetOutgoDirectCost> listKuanji=budgetOutgoDirectCostService.findkuanjiAccount(budgetOutgoDirectCost);
//首先找到根节点
List<BudgetOutgoDirectCost> listKuanji=new ArrayList<BudgetOutgoDirectCost>();
List<BudgetOutgoDirectCost> listGenjeidian=budgetOutgoDirectCostService.findBudget(budgetOutgoDirectCost);
if(listGenjeidian.size()>0){
for(BudgetOutgoDirectCost gen:listGenjeidian){
//查找子节点
gen.setSqlMap(sqlMap);
gen.setHalfYear(budgetOutgoDirectCost.getHalfYear());
gen.setBudgetOutgoBill(budgetOutgoBill);
List<BudgetOutgoDirectCost> listZijeidian=budgetOutgoDirectCostService.findListByParent(gen);
for(BudgetOutgoDirectCost kuanji:listZijeidian){
kuanji.setSqlMap(sqlMap);
kuanji.setHalfYear(budgetOutgoDirectCost.getHalfYear());
kuanji.setBudgetOutgoBill(budgetOutgoBill);
List<BudgetOutgoDirectCost> listsanji=budgetOutgoDirectCostService.findListByParent(kuanji);
listKuanji.addAll(listsanji);
}
}
}
//第三步,遍历list,每次遍历成成sheet和对应的内容(在webbook中添加一个sheet,对应Excel文件中的sheet )
if(listKuanji.size()>0){
for(int k=0;k<listKuanji.size();k++){
HSSFSheet sheeta = wb.createSheet("预"+k+"表");
HSSFCellStyle style = wb.createCellStyle();
Font Font = wb.createFont();
Font.setFontName("仿宋_GB2312");
Font.setFontHeightInPoints((short) 10);//字号
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
style.setFont(Font);
style.setWrapText(true);
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
// 第四步之树形式,创建单元格,设置单元格样式
HSSFCellStyle treeStyle = wb.createCellStyle();
//treeStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
treeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
treeStyle.setFont(Font);
treeStyle.setWrapText(true);
// 第五步,设置边框样式
HSSFCellStyle borderStyle = wb.createCellStyle();
//borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
borderStyle.setFont(Font);
borderStyle.setWrapText(true);
borderStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
borderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
borderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
borderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// 第六步,设置表头样式
HSSFCellStyle heardStyle = wb.createCellStyle();
heardStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
heardStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
heardStyle.setWrapText(true);
Font FirstFont = wb.createFont();
FirstFont.setFontName("黑体");
FirstFont.setFontHeightInPoints((short) 24);//字号
FirstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
// 第六步,设置边框样式
HSSFCellStyle centerStyle = wb.createCellStyle();
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
centerStyle.setFont(Font);
centerStyle.setWrapText(true);
centerStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
centerStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
centerStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
centerStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
//设置左对齐样式
HSSFCellStyle leftstyle = wb.createCellStyle();
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setFont(Font);
//设置右对齐样式
HSSFCellStyle rightstyle = wb.createCellStyle();
rightstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightstyle.setFont(Font);
HSSFRow row1 = sheeta.createRow((int) 0);
HSSFCell cell11 = row1.createCell((short) 0);
cell11.setCellValue("直接支出经费预算明细表");
heardStyle.setFont(FirstFont);
cell11.setCellStyle(heardStyle);
sheeta.setColumnWidth(0, (int)((30+ 0.72) * 256));//设置行宽
row1.setHeightInPoints(50);//设置列高
listKuanji.get(k).setSqlMap(sqlMap);
listKuanji.get(k).setHalfYear(budgetOutgoDirectCost.getHalfYear());
List<BudgetOutgoDirectCost> BudgetList=new ArrayList<BudgetOutgoDirectCost>();
List<BudgetOutgoDirectCost> cellList=new ArrayList<BudgetOutgoDirectCost>();
BudgetList=budgetOutgoDirectCostService.findBudgetsByParent(listKuanji.get(k));
for(BudgetOutgoDirectCost outgo2:BudgetList){
cellList.add(outgo2);
}
cellList=budgetOutgoDirectCostService.jisuan(cellList);
for(int i=7;i<cellList.size()+7;i++){
HSSFRow rowi= sheeta.createRow(i-1);
if(i==7){
if(cellList.get(i-7).getStandardCost()!=null){
cell64.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardCost()));
cell64.setCellStyle(borderStyle);
}else{
cell64.setCellValue("");
cell64.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getApplication()!=null){
cell65.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
cell65.setCellStyle(borderStyle);
}else{
cell65.setCellValue("");
cell65.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getAudit()!=null){
cell66.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
cell66.setCellStyle(borderStyle);
}else{
cell66.setCellValue("");
cell66.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getCompareResults()!=null){
cell67.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
cell67.setCellStyle(borderStyle);
}else{
cell67.setCellValue("");
cell67.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getJzApplication()!=null){
cell68.setCellValue(Double.parseDouble(cellList.get(i-7).getJzApplication()));
cell68.setCellStyle(borderStyle);
}else{
cell68.setCellValue("");
cell68.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getJzAudit()!=null){
cell69.setCellValue(Double.parseDouble(cellList.get(i-7).getJzAudit()));
cell69.setCellStyle(borderStyle);
}else{
cell69.setCellValue("");
cell69.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getLxApplication()!=null){
cell610.setCellValue(Double.parseDouble(cellList.get(i-7).getLxApplication()));
cell610.setCellStyle(borderStyle);
}else{
cell610.setCellValue("");
cell610.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getLxAudit()!=null){
cell611.setCellValue(Double.parseDouble(cellList.get(i-7).getLxAudit()));
cell611.setCellStyle(borderStyle);
}else{
cell611.setCellValue("");
cell611.setCellStyle(borderStyle);
}
}
for(int j=1;j<13;j++){
HSSFCell cellij = rowi.createCell((short) j-1);
if(j==1){
cellij.setCellValue(cellList.get(i-7).getAccount().getName());
cellij.setCellStyle(borderStyle);
}
else if(j==2){
cellij.setCellValue(cellList.get(i-7).getMeasurementUnit());
cellij.setCellStyle(borderStyle);
}
else if(j==3){
cellij.setCellValue(cellList.get(i-7).getNumber());
cellij.setCellStyle(borderStyle);
}
else if(j==4){
if(cellList.get(i-7).getStandardCost()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardCost()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==5){
if(cellList.get(i-7).getApplication()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==6){
if(cellList.get(i-7).getAudit()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==7){
if(cellList.get(i-7).getCompareResults()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==8){
if(cellList.get(i-7).getJzApplication()!=null){
cellij.setCellValue(cellList.get(i-7).getJzApplication());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==9){
if(cellList.get(i-7).getJzAudit()!=null){
cellij.setCellValue(cellList.get(i-7).getJzAudit());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==10){
if(cellList.get(i-7).getLxApplication()!=null){
cellij.setCellValue(cellList.get(i-7).getLxApplication());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==11){
if(cellList.get(i-7).getLxAudit()!=null){
cellij.setCellValue(cellList.get(i-7).getLxAudit());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==12){
cellij.setCellValue(cellList.get(i-7).getRemarks());
cellij.setCellStyle(borderStyle);
}
}
}
}
}else{
HSSFSheet sheeta = wb.createSheet("预表");
HSSFCellStyle style = wb.createCellStyle();
Font Font = wb.createFont();
Font.setFontName("仿宋_GB2312");
Font.setFontHeightInPoints((short) 10);//字号
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
style.setFont(Font);
style.setWrapText(true);
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
// 第四步之树形式,创建单元格,设置单元格样式
HSSFCellStyle treeStyle = wb.createCellStyle();
//treeStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
treeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
treeStyle.setFont(Font);
treeStyle.setWrapText(true);
// 第五步,设置边框样式
HSSFCellStyle borderStyle = wb.createCellStyle();
//borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
borderStyle.setFont(Font);
borderStyle.setWrapText(true);
borderStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
borderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
borderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
borderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// 第六步,设置表头样式
HSSFCellStyle heardStyle = wb.createCellStyle();
heardStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
heardStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
heardStyle.setWrapText(true);
Font FirstFont = wb.createFont();
FirstFont.setFontName("黑体");
FirstFont.setFontHeightInPoints((short) 24);//字号
FirstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
// 第六步,设置边框样式
HSSFCellStyle centerStyle = wb.createCellStyle();
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
centerStyle.setFont(Font);
centerStyle.setWrapText(true);
centerStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
centerStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
centerStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
centerStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
//设置左对齐样式
HSSFCellStyle leftstyle = wb.createCellStyle();
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setFont(Font);
//设置右对齐样式
HSSFCellStyle rightstyle = wb.createCellStyle();
rightstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightstyle.setFont(Font);
HSSFRow row1 = sheeta.createRow((int) 0);
HSSFCell cell11 = row1.createCell((short) 0);
cell11.setCellValue("直接支出经费预算明细表");
heardStyle.setFont(FirstFont);
cell11.setCellStyle(heardStyle);
row1.setHeightInPoints(50);//设置列高
}
try {
String fileName = "数据"+DateUtils.getDate("yyyyMMddHHmmss")+".xls";
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
wb.write(response.getOutputStream());
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导出失败!失败信息:"+e.getMessage());
logger.error("导出失败!失败信息:",e);
}
return "redirect:"+Global.getAdminPath()+"/directcost/budgetOutgoDirectCost/list2?id="+budgetOutgoDirectCost.getBudgetOutgoBill().getId();
}
* 按款级导出
* @param id
* @param redirectAttributes
* @return
*/
@SuppressWarnings("deprecation")
@RequestMapping(value = "exploreExecl")
public String exploreExecl(BudgetOutgoDirectCost budgetOutgoDirectCost, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
String BudgetOutgoBillId=request.getParameter("bibId");
BudgetOutgoBill budgetOutgoBill=new BudgetOutgoBill();
budgetOutgoBill.setId(BudgetOutgoBillId);
budgetOutgoBill.setStatus(null);
budgetOutgoBill.setSqlMap(sqlMap);
budgetOutgoBill.setHalfYear(budgetOutgoDirectCost.getHalfYear());
budgetOutgoBill=budgetOutgoBillService.get(budgetOutgoBill);
budgetOutgoDirectCost.setBudgetOutgoBill(budgetOutgoBill);
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//第二步,通过预算单id在本级直接支出中找到该预算单的所有的款科目
//List<BudgetOutgoDirectCost> listKuanji=budgetOutgoDirectCostService.findkuanjiAccount(budgetOutgoDirectCost);
//首先找到根节点
List<BudgetOutgoDirectCost> listKuanji=new ArrayList<BudgetOutgoDirectCost>();
List<BudgetOutgoDirectCost> listGenjeidian=budgetOutgoDirectCostService.findBudget(budgetOutgoDirectCost);
if(listGenjeidian.size()>0){
for(BudgetOutgoDirectCost gen:listGenjeidian){
//查找子节点
gen.setSqlMap(sqlMap);
gen.setHalfYear(budgetOutgoDirectCost.getHalfYear());
gen.setBudgetOutgoBill(budgetOutgoBill);
List<BudgetOutgoDirectCost> listZijeidian=budgetOutgoDirectCostService.findListByParent(gen);
for(BudgetOutgoDirectCost kuanji:listZijeidian){
kuanji.setSqlMap(sqlMap);
kuanji.setHalfYear(budgetOutgoDirectCost.getHalfYear());
kuanji.setBudgetOutgoBill(budgetOutgoBill);
List<BudgetOutgoDirectCost> listsanji=budgetOutgoDirectCostService.findListByParent(kuanji);
listKuanji.addAll(listsanji);
}
}
}
//第三步,遍历list,每次遍历成成sheet和对应的内容(在webbook中添加一个sheet,对应Excel文件中的sheet )
if(listKuanji.size()>0){
for(int k=0;k<listKuanji.size();k++){
HSSFSheet sheeta = wb.createSheet("预"+k+"表");
HSSFCellStyle style = wb.createCellStyle();
Font Font = wb.createFont();
Font.setFontName("仿宋_GB2312");
Font.setFontHeightInPoints((short) 10);//字号
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
style.setFont(Font);
style.setWrapText(true);
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
// 第四步之树形式,创建单元格,设置单元格样式
HSSFCellStyle treeStyle = wb.createCellStyle();
//treeStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
treeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
treeStyle.setFont(Font);
treeStyle.setWrapText(true);
// 第五步,设置边框样式
HSSFCellStyle borderStyle = wb.createCellStyle();
//borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
borderStyle.setFont(Font);
borderStyle.setWrapText(true);
borderStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
borderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
borderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
borderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// 第六步,设置表头样式
HSSFCellStyle heardStyle = wb.createCellStyle();
heardStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
heardStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
heardStyle.setWrapText(true);
Font FirstFont = wb.createFont();
FirstFont.setFontName("黑体");
FirstFont.setFontHeightInPoints((short) 24);//字号
FirstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
// 第六步,设置边框样式
HSSFCellStyle centerStyle = wb.createCellStyle();
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
centerStyle.setFont(Font);
centerStyle.setWrapText(true);
centerStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
centerStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
centerStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
centerStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
//设置左对齐样式
HSSFCellStyle leftstyle = wb.createCellStyle();
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setFont(Font);
//设置右对齐样式
HSSFCellStyle rightstyle = wb.createCellStyle();
rightstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightstyle.setFont(Font);
HSSFRow row1 = sheeta.createRow((int) 0);
HSSFCell cell11 = row1.createCell((short) 0);
cell11.setCellValue("直接支出经费预算明细表");
heardStyle.setFont(FirstFont);
cell11.setCellStyle(heardStyle);
sheeta.setColumnWidth(0, (int)((30+ 0.72) * 256));//设置行宽
row1.setHeightInPoints(50);//设置列高
listKuanji.get(k).setSqlMap(sqlMap);
listKuanji.get(k).setHalfYear(budgetOutgoDirectCost.getHalfYear());
List<BudgetOutgoDirectCost> BudgetList=new ArrayList<BudgetOutgoDirectCost>();
List<BudgetOutgoDirectCost> cellList=new ArrayList<BudgetOutgoDirectCost>();
BudgetList=budgetOutgoDirectCostService.findBudgetsByParent(listKuanji.get(k));
for(BudgetOutgoDirectCost outgo2:BudgetList){
cellList.add(outgo2);
}
cellList=budgetOutgoDirectCostService.jisuan(cellList);
for(int i=7;i<cellList.size()+7;i++){
HSSFRow rowi= sheeta.createRow(i-1);
if(i==7){
if(cellList.get(i-7).getStandardCost()!=null){
cell64.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardCost()));
cell64.setCellStyle(borderStyle);
}else{
cell64.setCellValue("");
cell64.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getApplication()!=null){
cell65.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
cell65.setCellStyle(borderStyle);
}else{
cell65.setCellValue("");
cell65.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getAudit()!=null){
cell66.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
cell66.setCellStyle(borderStyle);
}else{
cell66.setCellValue("");
cell66.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getCompareResults()!=null){
cell67.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
cell67.setCellStyle(borderStyle);
}else{
cell67.setCellValue("");
cell67.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getJzApplication()!=null){
cell68.setCellValue(Double.parseDouble(cellList.get(i-7).getJzApplication()));
cell68.setCellStyle(borderStyle);
}else{
cell68.setCellValue("");
cell68.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getJzAudit()!=null){
cell69.setCellValue(Double.parseDouble(cellList.get(i-7).getJzAudit()));
cell69.setCellStyle(borderStyle);
}else{
cell69.setCellValue("");
cell69.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getLxApplication()!=null){
cell610.setCellValue(Double.parseDouble(cellList.get(i-7).getLxApplication()));
cell610.setCellStyle(borderStyle);
}else{
cell610.setCellValue("");
cell610.setCellStyle(borderStyle);
}
if(cellList.get(i-7).getLxAudit()!=null){
cell611.setCellValue(Double.parseDouble(cellList.get(i-7).getLxAudit()));
cell611.setCellStyle(borderStyle);
}else{
cell611.setCellValue("");
cell611.setCellStyle(borderStyle);
}
}
for(int j=1;j<13;j++){
HSSFCell cellij = rowi.createCell((short) j-1);
if(j==1){
cellij.setCellValue(cellList.get(i-7).getAccount().getName());
cellij.setCellStyle(borderStyle);
}
else if(j==2){
cellij.setCellValue(cellList.get(i-7).getMeasurementUnit());
cellij.setCellStyle(borderStyle);
}
else if(j==3){
cellij.setCellValue(cellList.get(i-7).getNumber());
cellij.setCellStyle(borderStyle);
}
else if(j==4){
if(cellList.get(i-7).getStandardCost()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getStandardCost()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==5){
if(cellList.get(i-7).getApplication()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getApplication()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==6){
if(cellList.get(i-7).getAudit()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getAudit()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==7){
if(cellList.get(i-7).getCompareResults()!=null){
cellij.setCellValue(Double.parseDouble(cellList.get(i-7).getCompareResults()));
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==8){
if(cellList.get(i-7).getJzApplication()!=null){
cellij.setCellValue(cellList.get(i-7).getJzApplication());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==9){
if(cellList.get(i-7).getJzAudit()!=null){
cellij.setCellValue(cellList.get(i-7).getJzAudit());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==10){
if(cellList.get(i-7).getLxApplication()!=null){
cellij.setCellValue(cellList.get(i-7).getLxApplication());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==11){
if(cellList.get(i-7).getLxAudit()!=null){
cellij.setCellValue(cellList.get(i-7).getLxAudit());
}else{
cellij.setCellValue("");
}
cellij.setCellStyle(borderStyle);
}
else if(j==12){
cellij.setCellValue(cellList.get(i-7).getRemarks());
cellij.setCellStyle(borderStyle);
}
}
}
}
}else{
HSSFSheet sheeta = wb.createSheet("预表");
HSSFCellStyle style = wb.createCellStyle();
Font Font = wb.createFont();
Font.setFontName("仿宋_GB2312");
Font.setFontHeightInPoints((short) 10);//字号
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
style.setFont(Font);
style.setWrapText(true);
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
// 第四步之树形式,创建单元格,设置单元格样式
HSSFCellStyle treeStyle = wb.createCellStyle();
//treeStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
treeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
treeStyle.setFont(Font);
treeStyle.setWrapText(true);
// 第五步,设置边框样式
HSSFCellStyle borderStyle = wb.createCellStyle();
//borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
borderStyle.setFont(Font);
borderStyle.setWrapText(true);
borderStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
borderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
borderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
borderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// 第六步,设置表头样式
HSSFCellStyle heardStyle = wb.createCellStyle();
heardStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
heardStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
heardStyle.setWrapText(true);
Font FirstFont = wb.createFont();
FirstFont.setFontName("黑体");
FirstFont.setFontHeightInPoints((short) 24);//字号
FirstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
// 第六步,设置边框样式
HSSFCellStyle centerStyle = wb.createCellStyle();
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
centerStyle.setFont(Font);
centerStyle.setWrapText(true);
centerStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
centerStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
centerStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
centerStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
//设置左对齐样式
HSSFCellStyle leftstyle = wb.createCellStyle();
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setFont(Font);
//设置右对齐样式
HSSFCellStyle rightstyle = wb.createCellStyle();
rightstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightstyle.setFont(Font);
HSSFRow row1 = sheeta.createRow((int) 0);
HSSFCell cell11 = row1.createCell((short) 0);
cell11.setCellValue("直接支出经费预算明细表");
heardStyle.setFont(FirstFont);
cell11.setCellStyle(heardStyle);
row1.setHeightInPoints(50);//设置列高
}
try {
String fileName = "数据"+DateUtils.getDate("yyyyMMddHHmmss")+".xls";
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
wb.write(response.getOutputStream());
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导出失败!失败信息:"+e.getMessage());
logger.error("导出失败!失败信息:",e);
}
return "redirect:"+Global.getAdminPath()+"/directcost/budgetOutgoDirectCost/list2?id="+budgetOutgoDirectCost.getBudgetOutgoBill().getId();
}
阅读全文
0 0
- execl多sheet导出
- 导出execl
- execl导出
- EXECL导出
- execl导出
- 用GridView导出Execl
- 导出Execl(电子表格)总结
- GridView导出Execl
- C#导出Execl汇总
- 导出Execl方法汇总
- php导出execl
- 数据导出到execl
- Gridcontrol导出Execl
- datatable 导出到Execl
- Gridview导出Execl样式
- winform 导出Execl
- jsp导出word,execl
- jxl导出execl实例
- MySQL事务隔离级别与锁
- 1038. 统计同成绩学生(20) Hash散列
- springboot【26】异步调用@Async
- Java Web 学习笔记之十二:JBoss RestEasy处理跨域OPTIONS请求方式
- 中国黑客六道:生成交易截图工具
- execl多sheet导出
- 常用的导航栏下划线滚动特效
- spark rdd scala相关使用
- bzoj 1036 树链剖分(剖点)模板题
- 求递推数列通项的特征根法
- 史上最全的机器学习资料(下)
- CodeForces 595C Warrior and Archer(贪心博弈)
- 为什么360浏览器,打开几个页面,然后不对电脑进行任何操作,等过很长时间之后,为什么内存占用会逐渐上升直到崩溃??表现为操作系统卡的不行,做什么都卡
- 神经网络