java常用技巧
java常用技巧
阅读(2.9k) 书签 赞(0) 我要纠错

给定模板头部单元格合并设置

2018-09-08 16:36 更新
您使用了广告拦截器。请将 www.w3cschool.cn 加入白名单，解除广告屏蔽后，刷新页面。谢谢。 
  1. public class ExcelUtil{
  2.     /**
  3.      * 表导出
  4.      * @param userId
  5.      * @param templeCode
  6.      * @param reqParamter
  7.      * @return
  8.      * @throws Exception
  9.      */
  10.     public Attach exportBargainPk(String userId, String templeCode, String reqParamter) throws Exception {
  11.         Attach attach = new Attach();
  12.         attach.setAttachementType("xlsx");
  13.         Map<String, Object> templet = platformConsumer.selectExpTemplet(userId, templeCode);
  14.         templet = CommonUtil.sourceTofomater(templet);
  15.         String realTemplePath = templetPath + templet.get("templePath");
  16.         int startIndex = Integer.parseInt(templet.get("templeIndex").toString());
  17.         String serviceId = templet.get("serviceId").toString();
  18.         String methodId = templet.get("methodId").toString();
  19.         Map<String, Object> data = exportConsumer.getExportExcelData(serviceId, methodId, reqParamter, userId);
  20.         String[] key = templet.get("templeKey").toString().split(CommonConstant.SPLIT);
  21.         String templeName = templet.get("templeName").toString();
  22.         attach.setAttachementName(templeName);
  23.         byte[] attachementData = exportBargainPk(realTemplePath, startIndex, key, data);
  24.         attach.setAttachementData(attachementData);
  25.         return attach;
  26.     }
  27.     /**
  28.      * 表excel读取及格式数据组装
  29.      * @param realTemplePath
  30.      * @param startIndex
  31.      * @param key
  32.      * @param data
  33.      * @return
  34.      * @throws Exception
  35.      */
  36.     private byte[]  exportBargainPk(String realTemplePath, int startIndex, String[] key, Map<String, Object> data) throws Exception{
  37.         byte[] bytes = null;
  38.         ByteArrayOutputStream os = new ByteArrayOutputStream();
  39.         //数据获取分析
  40.         @SuppressWarnings("unchecked")
  41.         List<Map<String, Object>> list = (List<Map<String, Object>>) data.get("list");
  42.         @SuppressWarnings("unchecked")
  43.         Map<String, Object> header = (Map<String, Object>) data.get("header");
  44.         @SuppressWarnings("unchecked")
  45.         List<Map<String, Object>> bj = (List<Map<String, Object>>) header.get("bj");
  46.         String reqCode = data.get("reqCode")+"";
  47.         //供应商个数+一列
  48.         int vendorSize = bj.size()+1;
  49.         String[] abc = {"A","B","C","D","E","F","G","H","I","J","......"};
  50.         String[] sheetHeader = {"序号","询价单单号","物料编码","物料名称","直送地","基本单位","供应商报价","目标价","议价结果","价格单位","最终供应商/最终报价","选中理由"};
  51.         String[] vendorCode = new String[vendorSize];
  52.         String[] vendorName = new String[vendorSize];
  53.         for(int i=0; i<vendorSize-1; i++){
  54.             Map<String, Object> map = bj.get(i);
  55.             vendorCode[i] = map.get("field").toString();
  56.             vendorName[i] = map.get("displayName").toString();
  57.         }
  58.         File excel = PropertiesUtil.getFile(realTemplePath);
  59.         FileInputStream is = new FileInputStream(excel);
  60.         Workbook workBook = WorkbookFactory.create(is);
  61.         Sheet sheet = workBook.getSheetAt(0);
  62.         //设置头信息格式
  63.         CellStyle style = getCellStyle(workBook);
  64.         sheet.autoSizeColumn(1, true);
  65.         //设置列宽
  66.         sheet.setColumnWidth(1, 6000);
  67.         sheet.setColumnWidth(2, 6000);
  68.         sheet.setColumnWidth(3, 10000);
  69.         for(int i=0; i<vendorSize-1; i++){
  70.             sheet.setColumnWidth(6+i, 10000);
  71.             sheet.setColumnWidth(7+vendorSize+i, 10000);
  72.             sheet.setColumnWidth(8+2*vendorSize+i, 10000);
  73.         }
  74.         sheet.setDefaultColumnWidth(3000);
  75.         //总列数
  76.         int cellCount = 9 + 3*vendorSize;
  77.         //前四列设置
  78.         for(int i=0; i<4; i++){
  79.             Row row = sheet.createRow(i);
  80.             if(i==0){
  81.                 Cell cell = row.createCell(0);
  82.                 cell.setCellValue("价格PK表明细");
  83.                 cell.setCellStyle(style);
  84.                 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount-1));
  85.             }else if(i==1){
  86.                 for(int j=0; j<cellCount; j++){
  87.                     if(j<6){
  88.                         createCell(workBook,row,j).setCellValue(sheetHeader[j]);
  89.                     }else if(j == 6+vendorSize){
  90.                         createCell(workBook,row,j).setCellValue(sheetHeader[7]);
  91.                     }else if(j == 7+2*vendorSize){
  92.                         createCell(workBook,row,j).setCellValue(sheetHeader[9]);
  93.                     }else if(j == cellCount-1){
  94.                         createCell(workBook,row,j).setCellValue(sheetHeader[11]);
  95.                     }
  96.                 }
  97.                 sheet.addMergedRegion(new CellRangeAddress(1,1, 6, 5+vendorSize));
  98.                 createCell(workBook,row,6).setCellValue(sheetHeader[6]);
  99.                 sheet.addMergedRegion(new CellRangeAddress(1,1,7+vendorSize,6+2*vendorSize));
  100.                 createCell(workBook,row,7+vendorSize).setCellValue(sheetHeader[8]);
  101.                 sheet.addMergedRegion(new CellRangeAddress(1,1,8+2*vendorSize,7+3*vendorSize));
  102.                 createCell(workBook,row,8+2*vendorSize).setCellValue(sheetHeader[10]);
  103.             }else if(i==2){
  104.                 for(int j=0; j<cellCount; j++){
  105.                     if(j<6){
  106.                         createCell(workBook,row,j);
  107.                     }else if(j==5+vendorSize||j==6+2*vendorSize||j==7+3*vendorSize){
  108.                         createCell(workBook,row,j).setCellValue(abc[10]);
  109.                     }else if(5<j&&j<5+vendorSize){
  110.                         createCell(workBook,row,j).setCellValue(abc[j-6]);
  111.                     }else if(6+vendorSize<j&&j<6+2*vendorSize){
  112.                         createCell(workBook,row,j).setCellValue(abc[j-7-vendorSize]);
  113.                     }else if(7+2*vendorSize<j&&j<7+3*vendorSize){
  114.                         createCell(workBook,row,j).setCellValue(abc[j-8-2*vendorSize]);
  115.                     }
  116.                 }
  117.             }else if(i==3){
  118.                 for(int j=0; j<cellCount; j++){
  119.                     if(j<6){
  120.                         createCell(workBook,row,j);
  121.                     }else if(j==5+vendorSize||j==6+2*vendorSize||j==7+3*vendorSize){
  122.                         createCell(workBook,row,j);
  123.                     }else if(5<j&&j<5+vendorSize){
  124.                         createCell(workBook,row,j).setCellValue(vendorName[j-6]);
  125.                     }else if(6+vendorSize<j&&j<6+2*vendorSize){
  126.                         createCell(workBook,row,j).setCellValue(vendorName[j-7-vendorSize]);
  127.                     }else if(7+2*vendorSize<j&&j<7+3*vendorSize){
  128.                         createCell(workBook,row,j).setCellValue(vendorName[j-8-2*vendorSize]);
  129.                     }
  130.             }
  131.         }
  132.         }
  133.         //数据
  134.          for(int i=0; i<list.size(); i++){
  135.              Map<String, Object> map = list.get(i);
  136.              Row row = sheet.createRow(i+4);
  137.              createCell(workBook,row,0).setCellValue(i+1);
  138.              createCell(workBook,row,1).setCellValue(reqCode);
  139.              createCell(workBook,row,2).setCellValue(map.get(key[0])+"");
  140.              createCell(workBook,row,3).setCellValue(map.get(key[1])+"");
  141.              createCell(workBook,row,4).setCellValue(map.get(key[2])+"");
  142.              createCell(workBook,row,5).setCellValue(map.get(key[3])+"");
  143.              createCell(workBook,row,6+vendorSize).setCellValue(StringUtil.isEmpty(map.get(key[4])+"")?"":map.get(key[4])+"");
  144.              createCell(workBook,row,7+2*vendorSize).setCellValue(map.get(key[5])+"");
  145.             for (int j = 6; j < cellCount; j++) {
  146.                 if (5 < j && j < 5 + vendorSize) {
  147.                     createCell(workBook,row,j).setCellValue(map.get(vendorCode[j - 6])+"");
  148.                 } else if (6 + vendorSize < j && j < 6 + 2 * vendorSize) {
  149.                     createCell(workBook,row,j).setCellValue(map.get(vendorCode[j-7-vendorSize].replace("bj", "yj"))+"");
  150.                 } else if (7 + 2 * vendorSize < j && j < 7 + 3 * vendorSize) {
  151.                     createCell(workBook,row,j).setCellValue(map.get(vendorCode[j-8-2*vendorSize].replace("bj", "zj"))+"");
  152.                 }
  153.             }
  154.         }
  155.         os = new ByteArrayOutputStream();
  156.         try {
  157.             workBook.write(os);
  158.             bytes = os.toByteArray();
  159.         } catch (IOException e) {
  160.             e.printStackTrace();
  161.         }finally {
  162.             try {
  163.                 os.flush();
  164.                 os.close();
  165.             } catch (Exception e) {
  166.                 logger.error(e.getMessage(),e);
  167.                 e.printStackTrace();
  168.             }
  169.         }
  170.         return bytes;
  171.     }
  172.     /**
  173.      * 创建单元格
  174.      * @param wb
  175.      * @param row
  176.      * @param i
  177.      * @return
  178.      */
  179.     private static Cell createCell(Workbook wb, Row row, int i){
  180.         CellStyle style = wb.createCellStyle();
  181.         style.setAlignment(CellStyle.ALIGN_CENTER);
  182.         if(row.getRowNum()==1){
  183.             style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
  184.             style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  185.         }
  186.         style.setBorderBottom((short) 1);
  187.         style.setBorderLeft((short) 1);
  188.         style.setBorderRight((short) 1);
  189.         style.setBorderTop((short) 1);
  190.         Cell cell = row.createCell(i);
  191.         cell.setCellStyle(style);
  192.         return cell;
  193.     }
  194.     /**
  195.      * 单元格格式设置
  196.      * @param wb
  197.      * @return
  198.      */
  199.     private static CellStyle getCellStyle(Workbook wb) {
  200.         CellStyle cellStyle = wb.createCellStyle();
  201.         cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
  202.         Font font = wb.createFont();  
  203.         font.setFontName("黑体");  
  204.         font.setFontHeightInPoints((short) 24);//设置字体大小
  205.         font.setColor((short) 16);
  206.         cellStyle.setFont(font);
  207.         return cellStyle;
  208.     }
  211. }

导出格式： 导出excel样式图

以上内容是否对您有帮助：
← 简单实现邮件发送
JAVA 基本类型处理 →
在线笔记
App下载
App下载

扫描二维码

下载编程狮App

公众号
微信公众号

编程狮公众号