package com.thinkgem.jeesite.common.utils.excel; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.*; import javax.servlet.http.HttpServletResponse; import com.thinkgem.jeesite.common.utils.DateUtils; import com.thinkgem.jeesite.modules.faw.entity.question.FawQuestionReportsAvgzb; import com.thinkgem.jeesite.modules.faw.entity.question.FawQuestionReportsOrder; import com.thinkgem.jeesite.modules.faw.entity.question.FawQuestionReportsSales; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.collect.Lists; import com.thinkgem.jeesite.common.utils.Encodes; import com.thinkgem.jeesite.common.utils.Reflections; import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField; import com.thinkgem.jeesite.modules.sys.utils.DictUtils;//根据指定的excel模板导出数据 public String exportSales(Map<Integer,List<FawQuestionReportsSales>> salesMap, Map<Integer,List<FawQuestionReportsOrder>> orderMap, Map<String,Object> sumSalesMap) throws Exception { //获取模板文件 String srcFilePath = this.getClass().getClassLoader().getResource("/").getPath()+"/templates/excels/销售满意度统计.xlsx"; //设置文件名称 String fileName = "销售满意度统计" + System.currentTimeMillis() + ".xlsx"; String desFilePath = "d:/" + fileName; //创建Excel文件的输入流对象 FileInputStream fis = new FileInputStream(srcFilePath); //根据模板创建excel工作簿 XSSFWorkbook workBook = new XSSFWorkbook(fis); //创建Excel文件输出流对象 FileOutputStream fos = new FileOutputStream(desFilePath); try { //设置一网环节及推荐度得分,一网指标得分,二网经销商得分workBook //动态改变表头数据star StringBuffer sb = new StringBuffer(); sb.append(DateUtils.getYear()); sb.append("年第"); sb.append(DateUtils.chineseSeason(sumSalesMap.get("SEASON").toString())); sumSalesMap.remove("SEASON"); sb.append("季度销售满意度成绩"); //动态改变表头数据end /** * 第一层循环设置sheet(j) * 0:一网环节及推荐度得分 * 1:一网指标得分 * 2:二网经销商得分 */ for(int j=0;j<salesMap.size();j++) { //获取创建的工作簿第J页 XSSFSheet sheet = workBook.getSheetAt(j); XSSFRow row = null; XSSFCell cell = null; String sheetName = ""; switch (j){ case 0: sheetName = "一网环节及推荐度得分"; row = sheet.getRow(1); cell = row.getCell(0); //改变第二行第一列的值 cell.setCellValue(sb.toString()); row = sheet.getRow(5); cell = row.getCell(4); cell.setCellValue(sumSalesMap.get("ROSTER_NUM").toString()); sumSalesMap.remove("ROSTER_NUM"); cell = row.getCell(5); cell.setCellValue(sumSalesMap.get("SAMPLE_NUM").toString()); sumSalesMap.remove("SAMPLE_NUM"); cell = row.getCell(6); cell.setCellValue(sumSalesMap.get("TOTAL_POINTS").toString()); sumSalesMap.remove("TOTAL_POINTS"); cell = row.getCell(7); if((Double)sumSalesMap.get("SORT0") > 0){ cell.setCellValue("否"); }else{ cell.setCellValue("是"); } for(int l = 1; l < sumSalesMap.size(); l++){ cell = row.getCell(7+l); cell.setCellValue((Double)sumSalesMap.get("SORT"+l)); } break; case 1: sheetName = "一网指标得分"; row = sheet.getRow(1); cell = row.getCell(0); //改变第二行第一列的值 cell.setCellValue(sb.toString()); break; case 2: sheetName = "二网经销商得分"; break; } workBook.setSheetName(j, sheetName); //给sheet命名 int lastRow = sheet.getLastRowNum()+1; List<FawQuestionReportsSales> sales = salesMap.get(j); //第二层循环设置表头cell for (int i = 0; i < sales.size(); i++) { int count = 0; row = sheet.createRow(lastRow+i); FawQuestionReportsSales reportsSales = sales.get(i); if(null == reportsSales){ continue; } cell = row.createCell(count++); cell.setCellValue(reportsSales.getAreaName()); cell = row.createCell(count++); cell.setCellValue(reportsSales.getDistrictName()); //判断是否为全国数据 if(null == reportsSales.getDistrictCode()){ continue; } cell = row.createCell(count++); cell.setCellValue(reportsSales.getBranchCode()); cell = row.createCell(count++); cell.setCellValue(reportsSales.getBranchName()); //判断是否为全国大区数据 if(null==reportsSales.getDealerCode()){ continue; } cell = row.createCell(count++); cell.setCellValue(reportsSales.getRosterNum()); //判断是否是经销商得分 if(j!=2){ cell = row.createCell(count++); cell.setCellValue(reportsSales.getSampleNum()); } cell = row.createCell(count++); cell.setCellValue(reportsSales.getTotalPoints()); //第三层循环设置得分信息cell List<FawQuestionReportsAvgzb> avgzbs = reportsSales.getReportsAvgtzb(); //判断是否为一网环节及推荐度得分 if(j == 0){ cell = row.createCell(count++); if(avgzbs.get(0).getAvgPoints() > 0){ cell.setCellValue("否"); }else{ cell.setCellValue("是"); } for(int k=1;k<avgzbs.size();k++){ cell = row.createCell(count++); cell.setCellValue(avgzbs.get(k).getAvgPoints()); } }else{ for(int k=0;k<avgzbs.size();k++){ cell = row.createCell(count++); cell.setCellValue(avgzbs.get(k).getAvgPoints()); } } } } //经销商综合得分与排名workBook设置 //获取创建的工作簿第3页 XSSFSheet sheet = workBook.getSheetAt(3); //给sheet命名 workBook.setSheetName(3, "经销商综合得分与排名"); //复制第二行 XSSFRow fromRow = sheet.getRow(1); int lastRow = sheet.getLastRowNum()+1; XSSFRow row = null; XSSFCell cell = null; //第一层循环设置经销商综合得分与排名按条件排名 for(int i = 0; i < orderMap.size(); i++){ String titleName = ""; switch (i+1){ case 1: titleName = "20≤合计样本量<30个"; break; case 2: titleName = "10≤合计样本量<20个"; break; case 3: titleName = "合计样本量<10个"; break; case 4: titleName = "只有二网成功样本量"; break; } //第二层循环设置经销商综合得分与排名cell List<FawQuestionReportsOrder> orders = orderMap.get(i); for (int j = 0; j < orders.size(); j++) { int count = 0; row = sheet.createRow(lastRow++); FawQuestionReportsOrder order = orders.get(j); if (null == order) { break; } cell = row.createCell(count++); cell.setCellValue(order.getAreaName()); cell = row.createCell(count++); cell.setCellValue(order.getDistrictName()); cell = row.createCell(count++); cell.setCellValue(order.getDealerCode()); cell = row.createCell(count++); cell.setCellValue(order.getDealerName()); cell = row.createCell(count++); cell.setCellValue(order.getDealerSampleNum()); cell = row.createCell(count++); cell.setCellValue(order.getDealerTotalPoints()); cell = row.createCell(count++); cell.setCellValue(order.getBranchSampleNum()); cell = row.createCell(count++); cell.setCellValue(order.getBranchTotalPoints()); cell = row.createCell(count++); cell.setCellValue(order.getTotalSampleNum()); cell = row.createCell(count++); cell.setCellValue(order.getTotalPoints()); cell = row.createCell(count++); cell.setCellValue(order.getOrders()); } lastRow += 1; //设置标题 if(i!=4){ row = sheet.createRow(lastRow++); cell = row.createCell(0); cell.setCellValue(titleName); //黏贴到指定行 row = sheet.createRow(lastRow++); copyRow(workBook,fromRow,row,true); } } workBook.write(fos); } catch (Exception e){ e.printStackTrace(); } finally{ //关闭流 fis.close(); fos.flush(); fos.close(); } return desFilePath; } /** * 复制行 * @param wb * @param fromRow * @param toRow * @param copyValueFlag */ public static void copyRow(XSSFWorkbook wb, XSSFRow fromRow, XSSFRow toRow, boolean copyValueFlag) { for(Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) { XSSFCell tmpCell = (XSSFCell) cellIt.next(); XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex()); copyCell(wb, tmpCell, newCell, copyValueFlag); } XSSFSheet worksheet = fromRow.getSheet(); for(int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if(cellRangeAddress.getFirstRow() == fromRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress .getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } /** * 复制单元格 * @param wb * @param srcCell * @param distCell * @param copyValueFlag */ public static void copyCell(XSSFWorkbook wb, XSSFCell srcCell, XSSFCell distCell, boolean copyValueFlag) { XSSFCellStyle newStyle = wb.createCellStyle(); XSSFCellStyle srcStyle = srcCell.getCellStyle(); newStyle.cloneStyleFrom(srcStyle); newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex())); //样式 distCell.setCellStyle(newStyle); //评论 if (srcCell.getCellComment() != null) { distCell.setCellComment(srcCell.getCellComment()); } distCell.setCellType(srcCell.getCellType()); if (copyValueFlag) { distCell.setCellValue(srcCell.getRichStringCellValue()); } }
XSSF根据模板导出多个sheet
最新推荐文章于 2024-08-21 22:46:12 发布