【项目实战】Java POI之Excel导出经典案例一
来源：互联网 发布：灵智精实广告公司 知乎 编辑：程序博客网 时间：2024/06/11 01:45
最近项目中需要用到数据的导出， 于是乎就做几个，其实说白了还是利用apache的poi，在项目中直接导入poi包就可以。多的就不用说了，我直接上代码。
1、首先准备一些poi的jar包，如下图：
2、在项目工程都创建一张Excel表，存放某个目录下。图图如下：
3、在JSP页面添加一个导出Excel按钮。如下：
4、Java后台Controller导出方法我就不过多解释，我直接 如下：
/** * 导出Excel功能 * @return */@RequestMapping(value = "queryAuditGroupExcelList.htm")public void queryAuditGroupExcelList(HttpServletRequest request,HttpServletResponse response, HttpServletResponse reponse, ModelMap model, Integer groupId) {Integer bizId = WebUtils.getCurBizId(request);//获取信息1Map map= financeService.queryAuditViewInfo(groupId, bizId);Map groupMap = (Map) map.get("group");//获取到团信息//获取信息2Map<String, Object> pm = WebUtils.getQueryParamters(request);List<Map<String, Object>> orderList = getCommonService(null).queryList("fin.selectOrderList", pm);//获取信息3//List<InfoBean>del_list = (List) map.get("del");List<Map<String, Object>> deliveryList = getCommonService(null).queryList("fin.selectDeliveryList", pm);//获取信息4List<InfoBean>list = (List) map.get("sup");Map<String, Object> order_pm = WebUtils.getQueryParamters(request);List<Map<String, Object>> supplierList = getCommonService(null).queryList("fin.selectSupplierList", pm);//获取信息5List<Map<String, Object>> otherIncomeList = getCommonService(null).queryList("fin.selectOtherIncomeList", pm);SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");System.out.println("supplierList="+supplierList);String path = "";try {String url = request.getSession().getServletContext().getRealPath("/template/excel/financeAuditGroupList.xlsx");FileInputStream input = new FileInputStream(new File(url)); // 读取的文件路径XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));XSSFFont createFont = wb.createFont();createFont.setFontName("微软雅黑");createFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示createFont.setFontHeightInPoints((short) 12);XSSFFont tableIndex = wb.createFont();tableIndex.setFontName("宋体");tableIndex.setFontHeightInPoints((short) 11);CellStyle cellStyleFont = wb.createCellStyle();cellStyleFont.setBorderBottom(CellStyle.BORDER_THIN); // 下边框cellStyleFont.setBorderLeft(CellStyle.BORDER_THIN);// 左边框cellStyleFont.setBorderTop(CellStyle.BORDER_THIN);// 上边框cellStyleFont.setBorderRight(CellStyle.BORDER_THIN);// 右边框cellStyleFont.setAlignment(CellStyle.ALIGN_LEFT); // 居中XSSFFont font = wb.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示cellStyleFont.setFont(font);CellStyle cellStyle = wb.createCellStyle();cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中CellStyle styleFontCenter = wb.createCellStyle();styleFontCenter.setBorderBottom(CellStyle.BORDER_THIN); // 下边框styleFontCenter.setBorderLeft(CellStyle.BORDER_THIN);// 左边框styleFontCenter.setBorderTop(CellStyle.BORDER_THIN);// 上边框styleFontCenter.setBorderRight(CellStyle.BORDER_THIN);// 右边框styleFontCenter.setAlignment(CellStyle.ALIGN_CENTER); // 居中styleFontCenter.setFont(createFont);CellStyle styleFontTable = wb.createCellStyle();styleFontTable.setBorderBottom(CellStyle.BORDER_THIN); // 下边框styleFontTable.setBorderLeft(CellStyle.BORDER_THIN);// 左边框styleFontTable.setBorderTop(CellStyle.BORDER_THIN);// 上边框styleFontTable.setBorderRight(CellStyle.BORDER_THIN);// 右边框styleFontTable.setAlignment(CellStyle.ALIGN_CENTER); // 居中styleFontTable.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());styleFontTable.setFillPattern(CellStyle.SOLID_FOREGROUND);CellStyle styleLeft = wb.createCellStyle();styleLeft.setBorderBottom(CellStyle.BORDER_THIN); // 下边框styleLeft.setBorderLeft(CellStyle.BORDER_THIN);// 左边框styleLeft.setBorderTop(CellStyle.BORDER_THIN);// 上边框styleLeft.setBorderRight(CellStyle.BORDER_THIN);// 右边框styleLeft.setAlignment(CellStyle.ALIGN_LEFT); // 居左CellStyle styleRight = wb.createCellStyle();styleRight.setBorderBottom(CellStyle.BORDER_THIN); // 下边框styleRight.setBorderLeft(CellStyle.BORDER_THIN);// 左边框styleRight.setBorderTop(CellStyle.BORDER_THIN);// 上边框styleRight.setBorderRight(CellStyle.BORDER_THIN);// 右边框styleRight.setAlignment(CellStyle.ALIGN_RIGHT); // 居右Sheet sheet = wb.getSheetAt(0); // 获取到第一个sheetRow row = null;Cell cc = null;// 遍历集合数据，产生数据行int index = 0;//1、信息1if(groupMap.size()>0){row = sheet.createRow(index + 3);cc = row.createCell(0);cc.setCellValue("XX");//cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue((String)groupMap.get("group_code"));//cc.setCellStyle(styleLeft);cc = row.createCell(2);cc.setCellValue("计调");//cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue((String)groupMap.get("operator_name"));//计调cc.setCellStyle(cellStyle);cc = row.createCell(4);cc.setCellValue("人数");//cc.setCellStyle(cellStyle);cc = row.createCell(5);cc.setCellValue(groupMap.get("total_adult")+"+"+groupMap.get("total_child")+"+"+groupMap.get("total_guide"));//人数cc.setCellStyle(cellStyle);cc = row.createCell(6);cc.setCellValue("状态");//cc.setCellStyle(cellStyle);Integer num = (Integer)groupMap.get("group_state");if(num==0){cc = row.createCell(7);cc.setCellValue("未确认");//未确认cc.setCellStyle(cellStyle);}else if(num==1){cc = row.createCell(7);cc.setCellValue("已确认");//已确认cc.setCellStyle(cellStyle);}else if(num==2){cc = row.createCell(7);cc.setCellValue("作废");//作废cc.setCellStyle(cellStyle);}else if(num==3){cc = row.createCell(7);cc.setCellValue("已审核");//已审核cc.setCellStyle(cellStyle);}else {cc = row.createCell(7);cc.setCellValue("已封存");//已封存cc.setCellStyle(cellStyle);}cc = row.createCell(8);cc.setCellValue("产品名称");//cc.setCellStyle(cellStyle);cc = row.createCell(9);cc.setCellValue("【"+groupMap.get("product_brand_name")+"】"+groupMap.get("product_name"));//产品名称cc.setCellStyle(styleLeft);row = sheet.createRow(index + 4);cc = row.createCell(0);cc.setCellValue("收入");//cc.setCellStyle(cellStyle);BigDecimal total_income = (BigDecimal) groupMap.get("total_income");BigDecimal total_income_shop = (BigDecimal) groupMap.get("total_income_shop");cc = row.createCell(1);cc.setCellValue(df.format(total_income.add(total_income_shop)));//收入cc.setCellStyle(cellStyle);cc = row.createCell(2);cc.setCellValue("支出");//cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue(df.format(groupMap.get("total_cost")));//支出cc.setCellStyle(cellStyle);cc = row.createCell(4);cc.setCellValue("利润");//cc.setCellStyle(cellStyle);BigDecimal total_profit = (BigDecimal) groupMap.get("total_profit");cc = row.createCell(5);cc.setCellValue(df.format(total_profit.add(total_income_shop)));//利润cc.setCellStyle(cellStyle);cc = row.createCell(6);cc.setCellValue("人均利润");//cc.setCellStyle(cellStyle);Integer person_num = (Integer) groupMap.get("person_num");BigDecimal sum = total_profit.add(total_income_shop);if(person_num.intValue()==0){cc = row.createCell(7);cc.setCellValue(df.format(sum));//人均利润 = 单团利润cc.setCellStyle(cellStyle);}else {cc = row.createCell(7);cc.setCellValue(df.format(sum.divide(new BigDecimal(person_num),2, RoundingMode.HALF_UP)));//人均利润cc.setCellStyle(cellStyle);}cc = row.createCell(8);cc.setCellValue("起始日期");//cc.setCellStyle(cellStyle);cc = row.createCell(9);cc.setCellValue(groupMap.get("date_start")+"~"+groupMap.get("date_end"));//起始日期cc.setCellStyle(styleLeft);}//2、信息2index=0;if(orderList != null && orderList.size() > 0 ){for(Map<String, Object> item : orderList){row = sheet.createRow(index +9);cc = row.createCell(0);cc.setCellValue(index + 1);cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue((String)item.get("supplier_name"));//cc.setCellStyle(styleLeft);cc = row.createCell(2);cc.setCellValue((String)item.get("sale_operator_name"));//销售cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue((String)item.get("receive_mode"));//cc.setCellStyle(styleLeft);cc = row.createCell(4);cc.setCellValue(item.get("num_adult")+"+"+item.get("num_child")+"+"+item.get("num_guide"));//人数cc.setCellStyle(cellStyle);String total = df.format(item.get("total"));if(!"".equals(total)){cc = row.createCell(5);cc.setCellValue(total);//金额cc.setCellStyle(cellStyle);}else {cc = row.createCell(5);cc.setCellValue(0);//金额cc.setCellStyle(cellStyle);}String total_cash = df.format(item.get("total_cash"));if(!"".equals(total_cash)){cc = row.createCell(6);cc.setCellValue(total_cash);//已收cc.setCellStyle(cellStyle);}else {cc = row.createCell(6);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}String balance = df.format(item.get("balance"));if(!"".equals(total_cash)){cc = row.createCell(7);cc.setCellValue(balance);//未收cc.setCellStyle(cellStyle);}else {cc = row.createCell(7);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}index ++;}//加合计行index=0;BigDecimal sumTotal = new BigDecimal(0); BigDecimal sumTotalCost = new BigDecimal(0); BigDecimal sumBalance = new BigDecimal(0); for(int i=0; i<orderList.size(); i++) { Map<String,Object> sumMap = orderList.get(i); String total = df.format(sumMap.get("total"));if(!"".equals(total)){sumTotal = sumTotal.add((BigDecimal)sumMap.get("total"));}else {sumTotal = new BigDecimal(0);}String totalCash = df.format(sumMap.get("total_cash"));if(!"".equals(totalCash)){sumTotalCost = sumTotalCost.add((BigDecimal)sumMap.get("total_cash"));}else {sumTotalCost = new BigDecimal(0);}String balance = df.format(sumMap.get("balance"));if(!"".equals(balance)){sumBalance = sumBalance.add((BigDecimal)sumMap.get("balance"));}else {sumBalance = new BigDecimal(0);} } row = sheet.createRow(index + orderList.size()+9); // 加合计行 cc = row.createCell(0); cc.setCellStyle(styleRight); cc = row.createCell(1); cc.setCellStyle(styleRight); cc = row.createCell(2); cc.setCellStyle(styleRight); cc = row.createCell(3); cc.setCellStyle(styleRight); cc = row.createCell(4); cc.setCellValue("合计"); cc.setCellStyle(styleRight); cc = row.createCell(5); cc.setCellValue(df.format(sumTotal)); cc.setCellStyle(cellStyle); cc = row.createCell(6); cc.setCellValue(df.format(sumTotalCost)); cc.setCellStyle(cellStyle); cc = row.createCell(7); cc.setCellValue(df.format(sumBalance)); cc.setCellStyle(cellStyle); }//3、信息3index=0;if(deliveryList != null && deliveryList.size()>0){CellRangeAddress region = new CellRangeAddress(index +orderList.size()+11,index +orderList.size()+11, 0, 5);sheet.addMergedRegion(region);row = sheet.createRow(index +orderList.size()+11);cc = row.createCell(0);cc.setCellValue("XXX");cc.setCellStyle(cellStyleFont);row = sheet.createRow(index +orderList.size()+12);cc = row.createCell(0);cc.setCellValue("序号");cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue("商家");//商家cc.setCellStyle(cellStyle);cc = row.createCell(2);cc.setCellValue("日期");//日期cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue("金额");//成本/金额cc.setCellStyle(cellStyle);cc = row.createCell(4);cc.setCellValue("已收");//已收cc.setCellStyle(cellStyle);cc = row.createCell(5);cc.setCellValue("未收");//未收cc.setCellStyle(cellStyle);for(Map<String, Object> delItem : deliveryList){row = sheet.createRow(index +orderList.size()+13);cc = row.createCell(0);cc.setCellValue(index + 1);cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue((String)delItem.get("supplier_name"));//商家cc.setCellStyle(styleLeft);cc = row.createCell(2);cc.setCellValue((String)delItem.get("create_date"));//日期cc.setCellStyle(styleLeft);if(delItem.get("total") == null){cc = row.createCell(3);cc.setCellValue(0);//成本/金额cc.setCellStyle(styleLeft);}else{String t_totalCash = df.format(delItem.get("total"));if("".equals(t_totalCash)){cc = row.createCell(3);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else {cc = row.createCell(3);cc.setCellValue(t_totalCash);//已收cc.setCellStyle(cellStyle);}}if(delItem.get("total_cash") == null){cc = row.createCell(4);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else{String t_totalCash = df.format(delItem.get("total_cash"));if("".equals(t_totalCash)){cc = row.createCell(4);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else {cc = row.createCell(4);cc.setCellValue(t_totalCash);//已收cc.setCellStyle(cellStyle);}}String b_balance = df.format(delItem.get("balance"));if("".equals(b_balance)){cc = row.createCell(5);cc.setCellValue(0);//未收cc.setCellStyle(cellStyle);}else {cc = row.createCell(5);cc.setCellValue(b_balance);//未收cc.setCellStyle(cellStyle);}index ++;}//加合计行index=0;BigDecimal sumDelTotal = new BigDecimal(0); BigDecimal sumDelTotalCost = new BigDecimal(0); BigDecimal sumDelBalance = new BigDecimal(0); for(int i=0; i<deliveryList.size(); i++) { Map<String,Object> sumDel = deliveryList.get(i); if(sumDel.get("total") == null){ sumDelTotal = new BigDecimal(0);}else{String total = df.format(sumDel.get("total"));if(!"".equals(total)){sumDelTotal = sumDelTotal.add((BigDecimal)sumDel.get("total"));}else {sumDelTotal = new BigDecimal(0);}} if(sumDel.get("total_cash") == null){ sumDelTotalCost = new BigDecimal(0);}else{String totalCash = df.format(sumDel.get("total_cash"));if(!"".equals(totalCash)){sumDelTotalCost = sumDelTotalCost.add((BigDecimal)sumDel.get("total_cash"));}else {sumDelTotalCost = new BigDecimal(0);}} if(sumDel.get("balance") == null){ sumDelBalance = new BigDecimal(0);}else{String balance = df.format(sumDel.get("balance"));if(!"".equals(balance)){sumDelBalance = sumDelBalance.add((BigDecimal)sumDel.get("balance"));}else {sumDelBalance = new BigDecimal(0);}} } if(orderList.size()==1){ row = sheet.createRow(index +orderList.size()+14); } if(orderList.size()>1){ row = sheet.createRow(index +orderList.size()+15); } cc = row.createCell(0); cc.setCellStyle(styleRight); cc = row.createCell(1); cc.setCellStyle(styleRight); cc = row.createCell(2); cc.setCellValue("合计"); cc.setCellStyle(styleRight); cc = row.createCell(3); cc.setCellValue(df.format(sumDelTotal)); cc.setCellStyle(cellStyle); cc = row.createCell(4); cc.setCellValue(df.format(sumDelTotalCost)); cc.setCellStyle(cellStyle); cc = row.createCell(5); cc.setCellValue(df.format(sumDelBalance)); cc.setCellStyle(cellStyle);}//4、信息4 index=0;if(otherIncomeList.size() > 0 && otherIncomeList.size()>0){if(deliveryList.size() == 0){CellRangeAddress region = new CellRangeAddress(index +orderList.size()+11,index +orderList.size()+11, 0, 5);sheet.addMergedRegion(region);row = sheet.createRow(index +orderList.size()+11);}else {CellRangeAddress region = new CellRangeAddress(index +orderList.size()+deliveryList.size()+15,index +orderList.size()+deliveryList.size()+15, 0, 5);sheet.addMergedRegion(region);row = sheet.createRow(index +orderList.size()+deliveryList.size()+15);}cc = row.createCell(0);cc.setCellValue("其他收入");cc.setCellStyle(cellStyleFont);if(deliveryList .size() == 0){row = sheet.createRow(index +orderList.size()+12);}else {row = sheet.createRow(index +orderList.size()+deliveryList.size()+16);}cc = row.createCell(0);cc.setCellValue("序号");cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue("商家");//商家cc.setCellStyle(cellStyle);cc = row.createCell(2);cc.setCellValue("日期");//日期cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue("金额");//成本/金额cc.setCellStyle(cellStyle);cc = row.createCell(4);cc.setCellValue("已收");//已收cc.setCellStyle(cellStyle);cc = row.createCell(5);cc.setCellValue("未收");//未收cc.setCellStyle(cellStyle);for(Map<String, Object> otherMap : otherIncomeList){if(deliveryList .size() == 0){row = sheet.createRow(index +orderList.size()+13);}else {row = sheet.createRow(index +orderList.size()+deliveryList.size()+17);}cc = row.createCell(0);cc.setCellValue(index + 1);cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue((String)otherMap.get("supplier_name"));//商家cc.setCellStyle(styleLeft);cc = row.createCell(2);cc.setCellValue(sdf.format(otherMap.get("booking_date")));//日期cc.setCellStyle(styleLeft);String _total = df.format(otherMap.get("total"));if("".equals(_total)){cc = row.createCell(3);cc.setCellValue(0);//成本/金额cc.setCellStyle(styleLeft);}else {cc = row.createCell(3);cc.setCellValue(_total);//成本/金额cc.setCellStyle(styleLeft);}if(otherMap.get("total_cash") == null){cc = row.createCell(4);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else{String t_totalCash = df.format(otherMap.get("total_cash"));if("".equals(t_totalCash)){cc = row.createCell(4);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else {cc = row.createCell(4);cc.setCellValue(t_totalCash);//已收cc.setCellStyle(cellStyle);}}String b_balance = df.format(otherMap.get("balance"));if("".equals(b_balance)){cc = row.createCell(5);cc.setCellValue(0);//未收cc.setCellStyle(cellStyle);}else {cc = row.createCell(5);cc.setCellValue(b_balance);//未收cc.setCellStyle(cellStyle);}index ++;}//加合计行index=0;BigDecimal sumOtherTotal = new BigDecimal(0); BigDecimal sumOtherTotalCost = new BigDecimal(0); BigDecimal sumOtherBalance = new BigDecimal(0); for(int i=0; i<otherIncomeList.size(); i++) { Map<String,Object> sumOther = otherIncomeList.get(i); if(sumOther.get("total") == null){ sumOtherTotal = new BigDecimal(0);}else{String total = df.format(sumOther.get("total"));if(!"".equals(total)){sumOtherTotal = sumOtherTotal.add((BigDecimal)sumOther.get("total"));}else {sumOtherTotal = new BigDecimal(0);}} if(sumOther.get("total_cash") == null){ sumOtherTotalCost = new BigDecimal(0);}else{String totalCash = df.format(sumOther.get("total_cash"));if(!"".equals(totalCash)){sumOtherTotalCost = sumOtherTotalCost.add((BigDecimal)sumOther.get("total_cash"));}else {sumOtherTotalCost = new BigDecimal(0);}} if(sumOther.get("balance") == null){ sumOtherBalance = new BigDecimal(0);}else{String balance = df.format(sumOther.get("balance"));if(!"".equals(balance)){sumOtherBalance = sumOtherBalance.add((BigDecimal)sumOther.get("balance"));}else {sumOtherBalance = new BigDecimal(0);}} } if(deliveryList .size() == 0){row = sheet.createRow(index +orderList.size()+14);}else {row = sheet.createRow(index +orderList.size()+deliveryList.size()+18);} cc = row.createCell(0); cc.setCellStyle(styleRight); cc = row.createCell(1); cc.setCellStyle(styleRight); cc = row.createCell(2); cc.setCellValue("合计"); cc.setCellStyle(styleRight); cc = row.createCell(3); cc.setCellValue(df.format(sumOtherTotal)); cc.setCellStyle(cellStyle); cc = row.createCell(4); cc.setCellValue(df.format(sumOtherTotalCost)); cc.setCellStyle(cellStyle); cc = row.createCell(5); cc.setCellValue(df.format(sumOtherBalance)); cc.setCellStyle(cellStyle);}//5、信息5index=0;if (supplierList != null && supplierList.size() > 0) {if(otherIncomeList.size() == 0 && deliveryList.size() >0){row = sheet.createRow(index +orderList.size()+deliveryList.size()+16);}else if (otherIncomeList .size() > 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+otherIncomeList.size()+16);}else if (otherIncomeList.size() == 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+12);}else{row = sheet.createRow(index +orderList.size()+deliveryList.size()+otherIncomeList.size()+20);}cc = row.createCell(0);cc.setCellValue("序号");cc.setCellStyle(cellStyle);cc = row.createCell(1);cc.setCellValue("类别");//类别cc.setCellStyle(cellStyle);cc = row.createCell(2);cc.setCellValue("商家");//商家cc.setCellStyle(cellStyle);cc = row.createCell(3);cc.setCellValue("日期");//日期cc.setCellStyle(cellStyle);cc = row.createCell(4);cc.setCellValue("成本");//成本/金额cc.setCellStyle(cellStyle);cc = row.createCell(5);cc.setCellValue("已收");//已收cc.setCellStyle(cellStyle);cc = row.createCell(6);cc.setCellValue("未收");//未收cc.setCellStyle(cellStyle);for (Map<String, Object> items : supplierList) {if(otherIncomeList.size() == 0 && deliveryList.size() >0){row = sheet.createRow(index +orderList.size()+deliveryList.size()+17);}else if (otherIncomeList .size() > 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+otherIncomeList.size()+17);}else if (otherIncomeList.size() == 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+12);}else{row = sheet.createRow(index +orderList.size()+deliveryList.size()+otherIncomeList.size()+21);}cc = row.createCell(0);cc.setCellValue(index + 1);cc.setCellStyle(cellStyle);Integer type = (Integer)items.get("supplier_type");if(type == Constants.TRAVELAGENCY){// cc = row.createCell(1);cc.setCellValue("XXX");cc.setCellStyle(styleLeft);}else if(type == Constants.RESTAURANT){// 餐厅cc = row.createCell(1);cc.setCellValue("餐厅");cc.setCellStyle(styleLeft);}else if(type == Constants.HOTEL){ // 酒店cc = row.createCell(1);cc.setCellValue("酒店");cc.setCellStyle(styleLeft);}else if(type == Constants.FLEET){// 车队cc = row.createCell(1);cc.setCellValue("车队");cc.setCellStyle(styleLeft);}else if(type == Constants.SCENICSPOT){// 景区cc = row.createCell(1);cc.setCellValue("景区");cc.setCellStyle(styleLeft);}else if(type == Constants.SHOPPING){// 购物cc = row.createCell(1);cc.setCellValue("购物");cc.setCellStyle(styleLeft);}else if(type == Constants.ENTERTAINMENT){// 娱乐cc = row.createCell(1);cc.setCellValue("娱乐");cc.setCellStyle(styleLeft);}else if(type == Constants.GUIDE){// 导游cc = row.createCell(1);cc.setCellValue("导游");cc.setCellStyle(styleLeft);}else if(type == Constants.AIRTICKETAGENT){// cc = row.createCell(1);cc.setCellValue("代理1");cc.setCellStyle(styleLeft);}else if(type == Constants.TRAINTICKETAGENT){// cc = row.createCell(1);cc.setCellValue("火车票代理");cc.setCellStyle(styleLeft);}else if(type == Constants.GOLF){// 高尔夫cc = row.createCell(1);cc.setCellValue("高尔夫");cc.setCellStyle(styleLeft);}else if(type == Constants.OTHER){// 其他cc = row.createCell(1);cc.setCellValue("其他");cc.setCellStyle(styleLeft);}else if(type == Constants.CONTRACTAGREEMENT){// 合同协议cc = row.createCell(1);cc.setCellValue("合同协议");cc.setCellStyle(styleLeft);}else if(type == Constants.SUPPLIERCOMMENT){// 商家评论cc = row.createCell(1);cc.setCellValue("商家评论");cc.setCellStyle(styleLeft);}else if(type == Constants.INSURANCE){// 保险cc = row.createCell(1);cc.setCellValue("保险");cc.setCellStyle(styleLeft);}else if(type == Constants.LOCALTRAVEL){// 地接社cc = row.createCell(1);cc.setCellValue("地接社");cc.setCellStyle(styleLeft);}else if(type == Constants.OTHERINCOME){// 其他收入cc = row.createCell(1);cc.setCellValue("其他收入");cc.setCellStyle(styleLeft);}else {// 其他支出cc = row.createCell(1);cc.setCellValue("其他支出");cc.setCellStyle(styleLeft);}cc = row.createCell(2);cc.setCellValue((String)items.get("supplier_name"));//商家cc.setCellStyle(styleLeft);cc = row.createCell(3);cc.setCellValue(sdf.format(items.get("booking_date")));//日期cc.setCellStyle(styleLeft);String _total = df.format(items.get("total"));if("".equals(_total)){cc = row.createCell(4);cc.setCellValue(0);//成本/金额cc.setCellStyle(styleLeft);}else {cc = row.createCell(4);cc.setCellValue(_total);//成本/金额cc.setCellStyle(styleLeft);}if(items.get("total_cash") == null){cc = row.createCell(5);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else{String t_totalCash = df.format(items.get("total_cash"));if("".equals(t_totalCash)){cc = row.createCell(5);cc.setCellValue(0);//已收cc.setCellStyle(cellStyle);}else {cc = row.createCell(5);cc.setCellValue(t_totalCash);//已收cc.setCellStyle(cellStyle);}}String b_balance = df.format(items.get("balance"));if("".equals(b_balance)){cc = row.createCell(6);cc.setCellValue(0);//未收cc.setCellStyle(cellStyle);}else {cc = row.createCell(6);cc.setCellValue(b_balance);//未收cc.setCellStyle(cellStyle);}index ++;}//加合计行index=0;BigDecimal sumSupplierTotal = new BigDecimal(0); BigDecimal sumSupplierTotalCost = new BigDecimal(0); BigDecimal sumSupplierBalance = new BigDecimal(0); for(int i=0; i<supplierList.size(); i++) { Map<String,Object> sumSupplier = supplierList.get(i); if(sumSupplier.get("total") == null){ sumSupplierTotal = new BigDecimal(0);}else{String total = df.format(sumSupplier.get("total"));if(!"".equals(total)){sumSupplierTotal = sumSupplierTotal.add((BigDecimal)sumSupplier.get("total"));}else {sumSupplierTotal = new BigDecimal(0);}} if(sumSupplier.get("total_cash") == null){ sumSupplierTotalCost = new BigDecimal(0);}else{String totalCash = df.format(sumSupplier.get("total_cash"));if(!"".equals(totalCash)){sumSupplierTotalCost = sumSupplierTotalCost.add((BigDecimal)sumSupplier.get("total_cash"));}else {sumSupplierTotalCost = new BigDecimal(0);}} if(sumSupplier.get("balance") == null){ sumSupplierBalance = new BigDecimal(0);}else{String balance = df.format(sumSupplier.get("balance"));if(!"".equals(balance)){sumSupplierBalance = sumSupplierBalance.add((BigDecimal)sumSupplier.get("balance"));}else {sumSupplierBalance = new BigDecimal(0);}} } if(otherIncomeList.size() == 0 && deliveryList.size() >0){row = sheet.createRow(index +orderList.size()+deliveryList.size()+supplierList.size()+17);}else if (otherIncomeList .size() > 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+otherIncomeList.size()+supplierList.size()+17);}else if (otherIncomeList.size() == 0 && deliveryList.size() ==0) {row = sheet.createRow(index +orderList.size()+supplierList.size()+12);}else{row = sheet.createRow(index +orderList.size()+deliveryList.size()+otherIncomeList.size()+supplierList.size()+21);} cc = row.createCell(0); cc.setCellStyle(styleRight); cc = row.createCell(1); cc.setCellStyle(styleRight); cc = row.createCell(2); cc.setCellStyle(styleRight); cc = row.createCell(3); cc.setCellValue("合计"); cc.setCellStyle(styleRight); cc = row.createCell(4); cc.setCellValue(df.format(sumSupplierTotal)); cc.setCellStyle(cellStyle); cc = row.createCell(5); cc.setCellValue(df.format(sumSupplierTotalCost)); cc.setCellStyle(cellStyle); cc = row.createCell(6); cc.setCellValue(df.format(sumSupplierBalance)); cc.setCellStyle(cellStyle);}/*CellRangeAddress region = new CellRangeAddress(supplierList.size() + 5,supplierList.size() + 5, 0, 10);sheet.addMergedRegion(region);row = sheet.createRow(supplierList.size() + 5);cc = row.createCell(0);cc.setCellValue("打印人：" + WebUtils.getCurUser(request).getName()+ " 打印时间："+ DateUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));*/path = request.getSession().getServletContext().getRealPath("/") + "/download/" + System.currentTimeMillis()+ ".xlsx";FileOutputStream out = new FileOutputStream(path);wb.write(out);out.close();wb.close();} catch (Exception e) {e.printStackTrace();}String fileName = "";try {fileName = new String("结算单详情.xlsx".getBytes("UTF-8"), "iso-8859-1");} catch (UnsupportedEncodingException e) {e.printStackTrace();}download(path,fileName,request,response);//下载方法} //下载方法private void download(String path, String fileName, HttpServletRequest request, HttpServletResponse response) {try {// path是指欲下载的文件的路径。File file = new File(path);// 以流的形式下载文件。InputStream fis = new BufferedInputStream(new FileInputStream(path));byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();// 清空responseresponse.reset();// 设置response的Headerresponse.addHeader("Content-Length", "" + file.length());response.addHeader("Content-Disposition", "attachment;filename=" + fileName);OutputStream toClient = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/vnd.ms-excel;charset=gb2312");toClient.write(buffer);toClient.flush();toClient.close();file.delete();} catch (IOException ex) {ex.printStackTrace();}}5、点击按钮【导出Excel】按钮，效果如下：
Excel导出结果如图所示：
到此，功能就算完了。希望有所启发吧。
0 0
- 【项目实战】Java POI之Excel导出经典案例一
- 【项目实战】Java POI之Excel导出经典案例二
- 【项目实战】Java POI之Excel导出经典案例三
- 【项目实战】Java POI之Excel导出经典案例四
- 【项目实战】Java POI之Word导出经典案例一
- POI之经典案例--按列名导出excel
- Java中使用POI导出Excel 之 项目实战详细教程
- poi导出经典案例
- 【JAVA】POI导出EXCEL经典实现
- Java POI导出EXCEL经典实现
- java poi 导出excel经典demo
- Java POI导出EXCEL经典实现
- Java POI 导出EXCEL经典实现
- java POI导出EXCEL经典实现
- Java POI 导出EXCEL经典实现 Java导出Excel
- JAVA 之 使用 POI 导出 Excel （一）
- Java web项目利用POI导出excel
- java 项目利用POI导出excel总结
- AndroidStudio关联SVN
- Android笔记：Android 锁屏
- 日常生活小技巧 -- CSDN自定义博客栏目
- 使用iOS10的UserNotification报错
- 前端学习
- 【项目实战】Java POI之Excel导出经典案例一
- C语言程序设计实践（OJ）-字符串
- 命令执行顺序控制与管道
- 使用Nginx+Lua(OpenResty)开发高性能Web应用
- GreenDAO框架（二）基本使用篇
- csdn中如何快速转载别人的文章
- SQL Server中日期格式化
- 1.cocos2d-x的初步认识
- MVC模式职责分工及学习路上的一些感想
