您现在的位置： 主页 > 嵌入式相关 > POI生成EXCEL文件
本文所属标签:
为本文创立个标签吧:

POI生成EXCEL文件

来源：网络整理 网络用户发布，如有版权联系网管删除　2018-07-04　
进入嵌入式论坛与网友讨论>>

一、背景

根据指定格式的JSON文件生成对应的excel文件，需求如下

  • 支持多sheet
  • 支持单元格合并
  • 支持插入图片
  • 支持单元格样式可定制
  • 需要 标题(title)，表头(head)，数据(data) ，表尾(foot) 明确区分

二、效果预览

Alt text

三、数据格式

由于是生成Excel文件，这里值考虑生成xlsx格式的Excel文件，数据多表头默认考虑使用 | 表示，不在使用colspan rowspan作为。如需要表示两列两行，第一列合并表头格式为: A|B,A|C生成的表格为

A
B C

前端通过post的方式将需要生成的数据构造成符合要求的JSON文件提交跟后台。根据以上需求定义JSON格式如下

{"saveName": "生成Excel的文件名.xlsx","userStyles": [{"id": "1", //不能出现重复，在需要设置单元样式的地方，可以直接将style赋值为此值"style": {"font": { //设置字体基本格式"blod": true,//是否加粗"italic": true, //是否倾斜"color": "#FF0000",//字体颜色"name": "微软雅黑", //字体名称"height": 20 //大小},"fmtStr": "", //单元格格式，#,##0.00_);#,##0.00;0 千分位"align": "",//水平对齐方式 left right center"valign": "",//垂直对齐方式 top center bottom"borderColor": "", //设置边框颜色 如 #FF0000"bgColor": "" //设置单元格填充颜色}}],"sheets": [{"sheetName": "", //sheet名称"title": [], // 对应Sheet标题区域数据"titleMerge": [], //对应Sheet标题区域合并信息"head": [{}], //表头信息"data": [], //数据信息"dataMerge": [], //数据合并信息"foot": [], //表尾信息"footMerge": [], //表尾合并信息"img": [] //图片信息，需要将图片转换base64}]}

简要说明

  • head 数组中为JSON对象格式为
{"name": "A|B", //表头名称，多表头用|分割"type": "str", //此列数据类型 str num ,在excel中日期也是数字类型，通过fmtStr,显示为日期格式"field": "F_FIELD1", //备用字段，可不用"style": { //此列数据为列默认样式，可以是Style对象，也可以是在userStyles中定义的id值"align": "center"}}
  • 在数组 title data foot 中，列表中的数据，可以是一个单独的值如 1,”a”，也可以是一个对象，当为对象时，格式为
{"value": "", //单元格具体的值"type": "", //单元格类型，默认str"style": {} //单元格样式 可以是Style对象，也可以是在userStyles中定义的id值，如果没设置，默认取head总此列对应的style}
  • titleMerge、dataMerge、footMerge数组值为逗号分隔的字符串，其含义为"开始行，结束行，开始列，结束列"，索引从0开始。如在title中有两行三列数据，现在需要合并一行两列数据对应的值为"0，0，0，1"
  • img数组中值为对象，格式
{"col": 1, //图片开始列"row": 0, //开始行"colSpan": 1,//列跨度，最小值1"rowSpan": 2, //行跨度，最小值1"data": "" //base64图片数据如: "data:image/png;base64,iVBO...ggg=="}

四、关键实现

07以后的Excle文件，其实是一个压缩包，里边是一个个的xml文件，其中每一个sheet是一个xml文件，样式是一个xml文件，图片是对应的图片文件，放在media文件夹中，所以，代码思路依次为

  • 构建 XSSFWorkbook 对象
  • 生成样式
  • 依次生成，title head data foot 行数据
  • 依次处理合并信息 titlemerge datamerge footmerge
  • 添加图片信息
  • 输出文件流

功能入口如下

 1 @Override 2 public void buildOutputStream() throws FileProducerException { 3 // 处理传入的JSON数据 4 sheets = this.jsonData.getJSONArray(this.SHEETS); 5 Iterator sheetIter = sheets.iterator(); 6 if (sheets.isEmpty()) { 7 this.responseData.setErrcode(1001); 8 this.responseData.setSuccess(false); 9 this.responseData.setErrmsg("无数据可生成");10 throw new FileProducerException();11 }12 wb = new XSSFWorkbook();13 // 建立全局格式14 JSONArray userStyles = this.jsonData.getJSONArray(this.USERSTYLES);15 this.initUserStyles(userStyles);16 this.initDefaultHeadStyle();17  18 XSSFSheet ws;19 JSONObject sheet;20 JSONArray sheetData;21 JSONArray sheetTitle;22 JSONArray sheetHead;23 JSONArray sheetFoot;24 JSONArray sheetImgs;25  26 String sheetName;27 int sheetIndex = 0;28 while (sheetIter.hasNext()) {29 sheet = (JSONObject) sheetIter.next();30 // 获取sheet名称31 sheetName = sheet.getString(this.SHEET_NAME);32 ws = wb.createSheet();33 if (StringUtils.isNotBlank(sheetName)) {34 wb.setSheetName(sheetIndex, sheetName);35 }36 int sheetRowIndex = 0;37 sheetTitle = sheet.getJSONArray(this.SHEET_TITLE);38 this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_TITLE_MERGE),39 sheetRowIndex);40 sheetRowIndex = this.createRandom(ws, sheetTitle, sheetRowIndex);41  42 sheetHead = sheet.getJSONArray(this.SHEET_HEAD);43 sheetRowIndex = this.createHeadColumn(ws, sheetHead, sheetRowIndex);44  45 this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_DATA_MERGE),46 sheetRowIndex);47 sheetData = sheet.getJSONArray(this.SHEET_DATA);48 sheetRowIndex = this.createData(ws, sheetData, sheetRowIndex);49  50 sheetFoot = sheet.getJSONArray(this.SHEET_FOOT);51 this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_FOOT_MERGE),52 sheetRowIndex);53 sheetRowIndex = this.createRandom(ws, sheetFoot, sheetRowIndex);54  55 sheetImgs = sheet.getJSONArray(this.SHEET_IMG);56  57 this.setSheetImages(ws, sheetImgs);58 }59  60 // 返回输出流61 try {62 ByteArrayOutputStream os = new ByteArrayOutputStream();63 wb.write(os);64 this.outStreams.add(os);65 } catch (IOException e) {66 throw new FileProducerException(e.getMessage(), e.getCause());67 }68 }
View Code




生成单元格样式对象，包括字体 边框 背景 对齐方式




private XSSFCellStyle createCellStyle(JSONObject style) { XSSFCellStyle cellStyle = wb.createCellStyle();// 设置字体JSONObject font = style.getJSONObject(this.STYLE_FONT);Font excelFont = this.createFont(font);if (excelFont != null) {cellStyle.setFont(excelFont);}// border统一黑色cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN); String borderColor = style.getString(this.BORDER_COLOR);if (StringUtils.isNotBlank(borderColor)) {XSSFColor xfBorderColor = new XSSFColor(new Color(Integer.parseInt(borderColor.substring(1), 16)));cellStyle.setBorderColor(BorderSide.BOTTOM, xfBorderColor);cellStyle.setBorderColor(BorderSide.TOP, xfBorderColor);cellStyle.setBorderColor(BorderSide.LEFT, xfBorderColor);cellStyle.setBorderColor(BorderSide.RIGHT, xfBorderColor);}// 背景色String bgColor = style.getString(this.BACKGROUND_COLOR);if (StringUtils.isNotBlank(bgColor)) {XSSFColor cellBgColor = new XSSFColor(new Color(Integer.parseInt(bgColor.substring(1), 16)));cellStyle.setFillForegroundColor(cellBgColor);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);}// 对齐方式String hAlignment = style.getString(this.HALIGNMENT);if (StringUtils.isNotBlank(hAlignment))cellStyle.setAlignment(HorizontalAlignment.valueOf(hAlignment.toUpperCase()));String vAlignment = style.getString(this.VALIGNMENT);if (StringUtils.isNotBlank(vAlignment))cellStyle.setVerticalAlignment(VerticalAlignment.valueOf(vAlignment.toUpperCase()));// 自动换行TRUEcellStyle.setWrapText(true); // 格式String fmt = style.getString(this.FMTSTRING);if (StringUtils.isNotBlank(fmt))cellStyle.setDataFormat(wb.createDataFormat().getFormat(fmt));return cellStyle;}

View Code







创建字体样式




 1 private Font createFont(JSONObject fontCfg) { 2 if (fontCfg == null) 3 return null; 4 XSSFFont font = wb.createFont(); 5 font.setFontName(fontCfg.getString(this.FONT_NAME)); 6 Boolean fontBoole = fontCfg.getBoolean(FONT_BLOD); 7 if (fontBoole != null) 8 font.setBold(fontBoole.booleanValue()); 9 fontBoole = fontCfg.getBoolean(this.FONT_ITALIC);10 if (fontBoole != null)11 font.setItalic(fontBoole.booleanValue());12 fontBoole = fontCfg.getBoolean(this.FONT_UNDERLINE);13 if (fontBoole != null && fontBoole.booleanValue() == true)14 font.setUnderline(FontUnderline.SINGLE.getByteValue());15 Short fontHeight = fontCfg.getShort(this.FONT_HEIGHT);16 if (fontHeight != null)17 font.setFontHeightInPoints(fontHeight);18 String colorStr = fontCfg.getString(this.FONT_COLOR);19 if (colorStr != null) {20 font.setColor(new XSSFColor(new Color(Integer.parseInt(21 colorStr.substring(1), 16))));22 }23 return font;24 }

View Code





处理表头,表过多表头处理，采用 | 分割的方式，传入head长度为列数据，name中有几个 | 就知道表头有几行。所以针对表头处理有以下几个步骤


    

  • 生成默认列样式
    • 

  • 填充所有列数据，求出最大行数
    • 

  • 横向合并内容相同的单元
    • 

  • 纵向合并空白的单元格
    • 





  1 private int createHeadColumn(XSSFSheet ws, JSONArray sheetHead,  2 int sheetRowIndex) {  3 if (sheetHead == null)  4 return sheetRowIndex;  5 Iterator headIter = sheetHead.iterator();  6 JSONObject curHead = null;  7 int colIndex = 0;  8 Object colStyle = null;  9 int colSize = sheetHead.size(); 10 headTypes = new String[colSize]; 11 headCellStyleKeys = new String[colSize]; 12 int[] headColLevel = new int[colSize]; 13 String colName = null; 14 String[] colNameAry = null; 15 int maxLevel = 0; 16 int colLevel = 0; 17 XSSFCell headCell = null; 18 ArrayList> headValueList = new ArrayList>(); 19 while (headIter.hasNext()) { 20 curHead = (JSONObject) headIter.next(); 21 // 处理默认样式 22 if (curHead.containsKey(this.COLUMN_STYLE)) { 23 colStyle = curHead.get(this.COLUMN_STYLE); 24 if (colStyle instanceof JSONObject) { 25 headCellStyleKeys[colIndex] = this.COLUMNSTYLE_PREV 26 + colIndex; 27 this.userStyles.put(headCellStyleKeys[colIndex], 28 this.createCellStyle((JSONObject) colStyle)); 29 } else if (this.userStyles.containsKey(colStyle)) { 30 headCellStyleKeys[colIndex] = (String) colStyle; 31 } 32 } 33 // 处理默认列宽 34 if (curHead.containsKey(this.COLUMN_WIDTH)) { 35 ws.setDefaultColumnWidth(pixToExcelWdith(curHead 36 .getIntValue(this.COLUMN_WIDTH))); 37 } 38 // 保存列样式 39 if (curHead.containsKey(this.COLUMN_TYPE)) { 40 headTypes[colIndex] = curHead.getString(this.COLUMN_TYPE); 41 } else { 42 headTypes[colIndex] = this.CELLTYPESTRING; 43 } 44 // 处理多表头 45 colName = curHead.getString(this.COLUMN_NAME); 46 colNameAry = colName.split("\|"); 47 colLevel = colNameAry.length; 48 headColLevel[colIndex] = colLevel; 49 if (colLevel > maxLevel) { 50 maxLevel = colLevel; 51 } 52 for (int i = 0; i < colLevel; i++) { 53 if (headValueList.size() <= i) { 54 headValueList.add(new ArrayList()); 55 } 56 headValueList.get(i).add(colIndex, colNameAry[i]); 57 XSSFRow row = ws.getRow(sheetRowIndex + i); 58 if (row == null) { 59 row = ws.createRow(sheetRowIndex + i); 60 } 61 headCell = row.createCell(colIndex); 62 headCell.setCellValue(colNameAry[i]); 63 headCell.setCellStyle(this.userStyles.get(this.HEADSTYLE_KEY)); 64 } 65 colIndex++; 66 } 67   68 // 横向合并 69 Iterator> a = headValueList.iterator(); 70 JSONArray headMerge = new JSONArray(); 71 String prev = ""; 72 String curent = null; 73 int lRowIndex = 0; 74 int startCol = 0; 75 int mergeCol = 0; 76 ArrayList columnInfo = null; 77 while (a.hasNext()) { 78 startCol = 0; 79 mergeCol = 0; 80 prev = ""; 81 columnInfo = a.next(); 82 // 第三列才能知道，第一列和第二列是否合并 83 columnInfo.add(""); 84 Iterator b = columnInfo.iterator(); 85 XSSFCell lastRowCell = null; 86 while (b.hasNext()) { 87 curent = b.next(); 88 if (lRowIndex > 0) { 89 lastRowCell = ws.getRow(sheetRowIndex + lRowIndex - 1) 90 .getCell(startCol); 91 } 92 if (prev.equalsIgnoreCase(curent) && lRowIndex == 0) { 93 ws.getRow(sheetRowIndex + lRowIndex).getCell(startCol) 94 .setCellType(Cell.CELL_TYPE_BLANK); 95 mergeCol++; 96 } else if (prev.equalsIgnoreCase(curent) 97 && lRowIndex > 0 98 && StringUtils 99 .isBlank(lastRowCell.getStringCellValue())) {100 ws.getRow(sheetRowIndex + lRowIndex).getCell(startCol)101 .setCellType(Cell.CELL_TYPE_BLANK);102 mergeCol++;103 } else {104 if (mergeCol > 0 && startCol > 0) {105 headMerge.add(String.format("%d,%d,%d,%d", lRowIndex,106 lRowIndex, startCol - mergeCol - 1,107 startCol - 1));108 mergeCol = 0;109 }110 }111 startCol++;112 prev = curent;113 }114 lRowIndex++;115 }116 for (int i = 0; i < colSize; i++) {117 if (headColLevel[i] < maxLevel) { // 存在列合并118 headMerge.add(String.format("%d,%d,%d,%d", headColLevel[i] - 1,119 maxLevel - 1, i, i));120 for (int r = headColLevel[i]; r < maxLevel; r++) {121 ws.getRow(sheetRowIndex + r)122 .createCell(i)123 .setCellStyle(124 this.userStyles.get(this.HEADSTYLE_KEY));125 }126 }127 }128  129 this.setMergeCells(ws, headMerge, sheetRowIndex);130 return sheetRowIndex + maxLevel;131 }
View Code




添加图片,默认采用单元格描点方式，将图片固定指定的单元格区域内




 1 private void addImg(XSSFSheet ws, JSONObject img, XSSFCreationHelper cHelper) { 2 String imgBase64 = img.getString(this.SHEET_IMG_DATA); 3 if (StringUtils.isBlank(imgBase64)) 4 return; 5 String[] imgary = imgBase64.split(","); 6 System.out.println(imgary[0]); 7 byte[] imgByte = Base64.decodeBase64(imgary[1]); 8 int imgIdx = wb.addPicture(imgByte, Workbook.PICTURE_TYPE_JPEG); 9 XSSFDrawing drawImg = ws.createDrawingPatriarch();10 XSSFClientAnchor anchor = cHelper.createClientAnchor();11 int col = img.getIntValue(this.SHEET_IMG_COL);12 int row = img.getIntValue(this.SHEET_IMG_ROW);13 anchor.setCol1(col);14 anchor.setRow1(row);15 XSSFPicture pict = drawImg.createPicture(anchor, imgIdx);16 Integer colSpan = img.getInteger(this.SHEET_IMG_COLSPAN);17 if (colSpan == null)18 colSpan = 1;19 Integer rowSpan = img.getInteger(this.SHEET_IMG_ROWSPAN);20 if (rowSpan == null)21 rowSpan = 1;22 pict.resize(colSpan, rowSpan);23 }

View Code







五、总结


这次通过传入JSON对象生成样式丰富的excel文件，对于POI操作office文档又更加熟悉一些。相对于解析excel文档，生成就不用考虑文件格式，如：兼容2003格式，考虑大文件sax方式解析。相对于js前端生成excel文件，增加了对生成后文件二次加工的可能性，所以在功能入口中，采用了生成二进制流的方式。文件生成好后，可以继续发送邮件，上传ftp等操作。 
重点说明


    

  • 对于各数据区域数据，保持区域数据独立性（数据索引值）
    • 

  • 对于图片开始行和开始列，索引值是针对一个完整的sheet
    • 

  • 对于表头区域，多表头采用 | 分割，减少部分传输数据
    • 

  • excel中style为所有sheet共享样式。
    • 




			

			

			
			　　　　　　　　　　　　　
									  查看评论  回复		
						  	
			

			


						  
			
			
			  
			  
			 
			  	

				
			  

			
		 
		 

		 
嵌入式交流网主页 > 嵌入式相关 > POI生成EXCEL文件
        

		 

			 数据  文件  生成  
		
         
         

           

				
		   

         

         

		   
"POI生成EXCEL文件"的相关文章

		   
         

         

          
		 

        
        

        
        

            
热门文章

			

			
♦
最强Wi-Fi漏洞影响所有设备，安卓补丁


♦
收缩毛孔有效的方法


♦
苹果为啥要自研GPU？彻底真相了


♦
借苹果自研GPU说说GPU硬件构成及CUDA架


♦
14nm骁龙660跑分曝光：GPU比653增40%OP


♦
iPhone 6s GPU性能为何能够有大幅提升

 
            

        
 
          

            
相关文章

            

			  
            

          
        

        

        

	  
	 
	
    
	

	  

		
	  

	  

	    
热门文章

		
	  


	  

		
	  


	  

	    
论坛热帖

		

			 
		

	  


	  

		
	  

	  
	  

	    
相关图文

		  
	  
  
	  
			

		

	  	
  
  
 

	
网站地图

    

  
  
 
 



 

围观()