根据xml模板导出excel

依赖

<poi.version>5.2.3</poi.version>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>${poi.version}</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>${poi.version}</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-lite</artifactId><version>${poi.version}</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>${poi.version}</version>
</dependency>
<velocity.version>2.3</velocity.version>
<dependency><groupId>org.apache.velocity</groupId><artifactId>velocity-engine-core</artifactId><version>${velocity.version}</version>
</dependency>

StyledXmlToWorkbook(.xls)

public class StyledXmlToWorkbook  extends DefaultHandler  {private InputStream excelTemplate;private StringBuffer content = new StringBuffer();private boolean hasChars = false;private String xml;private OutputStream output;/*** excel*/private HSSFWorkbook wb = null;private HSSFSheet sheet = null;//当前处理的行private HSSFRow row = null;/*** 当前处理的Excel*/private HSSFCell cell = null;private Map<Integer,Map<String,CellType>> typeMap = new HashMap<>();//定义的样式表private Map<String, HSSFCellStyle> defineStyleMap = new HashMap<>();/*** excel模板的sheet索引*/private int sheetIdx = -1;/*** excel模板的sheet的最大索引号* 在处理完毕一个table后， 需要将剩余的模板行删除掉*/private int sheetMaxTmplRowIdx = -1;private int rowIdx = -1;private int colIdx = -1;private boolean contentTr = false;private List<Integer> columns = new ArrayList<>();private Map<Integer,List<Merge>> mergeMap = new HashMap<>();private Map<String,String> cellAttributes = new HashMap<>();private Map<String, Short> colorIndexMap = new HashMap<>();//颜色的索引只能是8-64private short beginColorIndex = 50;//用于标志tr结束后，columns所有的元素是否都减去1了private Map<Integer,Boolean> columnsSubtracFlags = new HashMap<Integer, Boolean>();private Map<String,CellStyle> xmlStyles = new HashMap<String, CellStyle>();private CellStyle currStyle = null;private Map<String,HSSFFont> fonts = new HashMap<>();//用于解决通过自动换行自适应行高但同时又需要设置最小行高的情况private Map<HSSFRow, Short> rowMinHeights = new LinkedHashMap<>();private List<RichFontText> richFontTexts = null;private RichFontText richFontText = null;private StringBuffer contentOfRichFontTexts = null;/*** * @param excelTemplate Excel模板的Inputstram* @param tableXml table的xml内容* @param output 输出的output*/public StyledXmlToWorkbook(InputStream excelTemplate,String tableXml,OutputStream output){this.xml=tableXml;this.output = output;this.excelTemplate=excelTemplate;}public void parse() {try {SAXParserFactory spFactory = SAXParserFactory.newInstance();spFactory.setFeature("http://xml.org/sax/features/validation",false);spFactory.setValidating(false);SAXParser sParser = spFactory.newSAXParser();XMLReader xr = sParser.getXMLReader();xr.setErrorHandler(this);xr.setContentHandler(this);xr.parse(new InputSource(new StringReader(this.xml)));} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}}/*** 初始化Excel*/private void initWorkbook(){try {this.wb = new HSSFWorkbook(this.excelTemplate);} catch (IOException e) {throw new RuntimeException(e);}}/*** 将excel写入到excel输出文件中*/private void writeWorkbook(){try {this.wb.write(this.output);} catch (IOException e) {throw new RuntimeException(e);}}public void startDocument() throws SAXException {this.initWorkbook();}public void endDocument() throws SAXException {for(HSSFRow row:this.rowMinHeights.keySet()){Short minHeight = this.rowMinHeights.get(row);if(row.getHeight()<minHeight){row.setHeight(minHeight);}}this.cleanTmplSheetIfNecessary();this.writeWorkbook();}private void initSheet(){this.sheetIdx++;if(sheetIdx>=this.wb.getNumberOfSheets()){this.wb.createSheet();this.row = this.wb.getSheetAt(this.sheetIdx).createRow(0);}this.sheet = this.wb.getSheetAt(this.sheetIdx);this.colIdx=0;this.rowIdx=0;this.sheetMaxTmplRowIdx =this.sheet.getLastRowNum();this.columns.clear();this.mergeMap.clear();this.contentTr=false;this.row=null;this.cell=null;}/*** 清除剩余的模板的sheet*/private void cleanTmplSheetIfNecessary(){List<Integer> removeSheetIdxs = new ArrayList<Integer>();for(int i=this.sheetIdx+1;i>=0;i++){try{HSSFSheet sheet = this.wb.getSheetAt(i);if(sheet!=null){removeSheetIdxs.add(i);}else{break;}}catch(Exception e){break;}}int removed = 0;int maxSheetIdx = this.wb.getNumberOfSheets();boolean emptyExcel = false;if(removeSheetIdxs.size()==maxSheetIdx){removeSheetIdxs.remove(0);emptyExcel=true;}for(Integer i:removeSheetIdxs){wb.removeSheetAt(i-removed);removed++;}if(emptyExcel){this.cleanAllRowsAtSheet(0);}}public void startElement(String namespaceURI, String localName,String qName, Attributes attr) throws SAXException {this.content = new StringBuffer();if ("style".equalsIgnoreCase(qName)) {this.currStyle = new CellStyle();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);if("id".equalsIgnoreCase(n)){this.currStyle.setId(v.trim());}this.currStyle.getStyles().put(n, v);if(StringUtils.isNotBlank(this.currStyle.getId())){this.xmlStyles.put(this.currStyle.getId(), this.currStyle);}}return;}if ("table".equalsIgnoreCase(qName)) {this.initSheet();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);if("name".equalsIgnoreCase(n)){this.wb.setSheetName(this.sheetIdx, v);}}return;}if ("tr".equalsIgnoreCase(qName)) {if(contentTr){this.row = this.sheet.createRow(this.rowIdx);}else{this.row = this.sheet.getRow(this.rowIdx);}return;}if("font".equalsIgnoreCase(qName)){if(this.row!=null && this.cell!=null){if(this.richFontTexts == null){this.richFontTexts = new ArrayList<>();}this.richFontText = new RichFontText();this.richFontTexts.add(this.richFontText);this.contentOfRichFontTexts = new StringBuffer();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);this.richFontText.styles.put(n.toLowerCase(), v);}}}if ("td".equalsIgnoreCase(qName) ||"th".equalsIgnoreCase(qName) ||"tmpd".equalsIgnoreCase(qName)) {int colspan=1;int rowspan=1;cellAttributes.clear();this.richFontTexts = null;int actualColIdx = this.colIdx;for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);cellAttributes.put(n.toLowerCase(), v);if ("colspan".equalsIgnoreCase(n)) {colspan = Integer.valueOf(v.trim());}if ("rowspan".equalsIgnoreCase(n)) {rowspan = Integer.valueOf(v.trim());}if("selectList".equalsIgnoreCase(n)){cellAttributes.put("selectList", v);}}if("td".equalsIgnoreCase(qName)){if(!contentTr){this.createContentTrStyleMap();}contentTr=true;}if(this.columns.size()<=this.colIdx){//这是第一行,生成columns元数据for(int i=0;i<colspan;i++){this.columns.add(rowspan);//endTr函数中统一从rowspan-1HSSFCell _cell = this.createCell();if(i==0){this.cell = _cell;}}}else{int currTdColIdx = -1;for(int i=this.colIdx;i<columns.size();i++){if(columns.get(i)>0 || "tmpd".equalsIgnoreCase(qName)){//当前单元格全面有被覆盖的单元格columns.set(i, columns.get(i)-1);columnsSubtracFlags.put(i, Boolean.TRUE);//创建一个dummy单元格,用于进行合并this.createCell();}else{currTdColIdx = i;actualColIdx = currTdColIdx;break;}}for(int i=0;i<colspan;i++){if(!"tmpd".equalsIgnoreCase(qName)){this.colIdx = i+currTdColIdx;this.columns.set(colIdx,rowspan-1);columnsSubtracFlags.put(colIdx, Boolean.TRUE);HSSFCell _cell = this.createCell();if(i==0){this.cell = _cell;}}}}//计算需合并区域if(colspan>1 || rowspan>1){Merge m = new Merge();m.beginCol=actualColIdx;m.beginRow=this.rowIdx;m.endRow=m.beginRow+rowspan-1;m.endCol=m.beginCol+colspan-1;List<Merge> sameEndRowMerges = this.mergeMap.get(m.endRow);if(sameEndRowMerges==null){sameEndRowMerges = new ArrayList<Merge>();mergeMap.put(m.endRow, sameEndRowMerges);}sameEndRowMerges.add(m);}if(StringUtils.isNotBlank(cellAttributes.get("selectList"))){// 创建下拉列表数据// 绑定HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);DataValidationConstraint constraint = (DataValidationConstraint) dvHelper.createExplicitListConstraint(cellAttributes.get("selectList").split(","));CellRangeAddressList regions = new CellRangeAddressList(this.rowIdx, this.rowIdx, actualColIdx, actualColIdx);XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(constraint, regions);this.sheet.addValidationData(validation);}}}/*** 删除剩余的模板行数*/private void cleanSheetTemplRowIfNecessary(){if(this.rowIdx<=this.sheetMaxTmplRowIdx){for(int i=rowIdx;i<=this.sheetMaxTmplRowIdx;i++){if(this.sheet.getRow(i)!=null){//加非空判断（成刊信息管理导出时此处报空指针异常）this.sheet.removeRow(this.sheet.getRow(i));}}}}/*** 情况所有的行* @param sheetIdx*/private void cleanAllRowsAtSheet(int sheetIdx){HSSFSheet _sheet = this.wb.getSheetAt(sheetIdx);int maxRow = _sheet.getLastRowNum();if(maxRow>0){for(int i=0;i<=maxRow;i++){_sheet.removeRow(_sheet.getRow(i));}}}public boolean greater(int beginIndex,List<Integer> columns){boolean greater= false;if(columns==null || columns.size()+1<beginIndex){return greater;}for(int i=beginIndex;i<columns.size();i++){if(columns.get(i)>0){greater = true;break;}}return greater;}private void cloneFont(HSSFFont clone,HSSFFont font){clone.setBold(font.getBold());clone.setCharSet(font.getCharSet());clone.setColor(font.getColor());clone.setFontHeight(font.getFontHeight());clone.setFontHeightInPoints(font.getFontHeightInPoints());clone.setFontName(font.getFontName());clone.setItalic(font.getItalic());clone.setStrikeout(font.getStrikeout());clone.setTypeOffset(font.getTypeOffset());clone.setUnderline(font.getUnderline());}/*** 设定特定的样式* @param _cell*/private void createSpecifiedStylesIfNecessary(HSSFCell _cell){for(String k:cellAttributes.keySet()){String v = cellAttributes.get(k);if("width".equals(k)){int w = calcCellWidth(v);if(this.rowIdx>0){}sheet.setColumnWidth(_cell.getColumnIndex(),w);}if("height".equals(k)){short h = Double.valueOf( ( Double.valueOf(v)*20)+"").shortValue();this.row.setHeight(h);}if("minHeight".equals(k)){short h = Double.valueOf( ( Double.valueOf(v)*20)+"").shortValue();// this.row.setHeight(h);this.rowMinHeights.put(this.row, h);}if("styleId".equalsIgnoreCase(k)){HSSFCellStyle style = this.getDefinedCellStyle2(_cell,v.trim());if(style!=null){_cell.setCellStyle(style);}}}}/*** 创建样式* @param _cell* @return*/private HSSFCellStyle getDefinedCellStyle2(HSSFCell _cell,String styleId){HSSFCellStyle style = this.defineStyleMap.get(styleId);if(style == null){style = this.wb.createCellStyle();/*if(_cell.getCellStyle()!=null){style.cloneStyleFrom(_cell.getCellStyle());   }*/CellStyle xmlStyle = this.xmlStyles.get(styleId);if(xmlStyle!=null&&xmlStyle.getStyles()!=null){for(String k:xmlStyle.getStyles().keySet()){String v = xmlStyle.getStyles().get(k);if("alignment".equals(k)){style.setAlignment((HorizontalAlignment)getStatifFieldValue(HorizontalAlignment.class,v.trim().toUpperCase()));}if("vertical".equals(k)){style.setVerticalAlignment((VerticalAlignment)getStatifFieldValue(VerticalAlignment.class,v.trim().toUpperCase()));}if("wrapText".equalsIgnoreCase(k)){style.setWrapText("true".equalsIgnoreCase(v));}if("backgroundColor".equalsIgnoreCase(k)){style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillForegroundColor(this.getColorIndex(v.trim()));}if("border".equals(k)){List<String> borders=BaseUtils.splitString(v,",");if(borders.size()==1){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));}else if(borders.size()==2){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));}else if(borders.size()==4){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(2))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(4))*1)));}}if("dataFormat".equals(k)){style.setDataFormat(HSSFDataFormat.getBuiltinFormat(v));}}CellFont cellFont = CellFont.toFont(styleId,xmlStyle.getStyles());if(cellFont.isCustomFont()){HSSFFont font = this.fonts.get(cellFont.getFontKey());if(font==null){font = this.wb.createFont();if(_cell.getCellStyle()!=null && this.wb.getFontAt(_cell.getCellStyle().getFontIndex())!=null){this.cloneFont(font, this.wb.getFontAt(_cell.getCellStyle().getFontIndex())) ;}if(StringUtils.isNotBlank(cellFont.bold)){font.setBold("true".equalsIgnoreCase(cellFont.bold));}if(StringUtils.isNotBlank(cellFont.color)){font.setColor(this.getColorIndex(cellFont.color));}if(StringUtils.isNotBlank(cellFont.fontheightinpoints)){font.setFontHeightInPoints(Short.valueOf(cellFont.fontheightinpoints.trim()));}if(StringUtils.isNotBlank(cellFont.fontname)){font.setFontName(cellFont.fontname.trim());}this.fonts.put(cellFont.getFontKey(), font);}style.setFont(font);}}defineStyleMap.put(styleId, style);}else{return style;}return style;}private int calcCellWidth(String v) {double excelWidth = Double.valueOf(v);double charWidth = 8;Double dw = (excelWidth * (charWidth - 1) + 5) / (charWidth - 1) * 256;int w = dw.intValue();return w;}/*** 创建自定义的颜色索引* @param rgbcolor* @return*/private short getColorIndex(String rgbcolor){rgbcolor = rgbcolor.replaceFirst("#", "").trim().toUpperCase();Short idx = this.colorIndexMap.get(rgbcolor);if(idx!=null){return idx;}idx = ++this.beginColorIndex;int[] color=new int[3];color[0]=Integer.parseInt(rgbcolor.substring(0, 2), 16);color[1]=Integer.parseInt(rgbcolor.substring(2, 4), 16);color[2]=Integer.parseInt(rgbcolor.substring(4, 6), 16);//自定义颜色HSSFPalette palette = wb.getCustomPalette();palette.setColorAtIndex(idx, (byte)color[0], (byte)color[1], (byte)color[2]);this.colorIndexMap.put(rgbcolor, idx);return idx;}private Object getStatifFieldValue(Class<?> clazz,String field){Field[] fs = clazz.getFields();// 取得当前类的所以属性for (Field f : fs) {// 把FS的值付给f进行循繁if (f.getName().equals(field.trim())) {try {Object v = f.get(clazz);return v;} catch (Exception e) {throw new RuntimeException(e);}}}throw new RuntimeException("no field："+field+" in class:"+clazz);}private HSSFCell createCell(){HSSFCell _cell = null;if(this.contentTr){_cell = this.row.createCell(this.colIdx);Map<String,CellType> types=this.typeMap.get(this.sheetIdx);if(types!=null && types.get(this.colIdx+"")!=null){_cell.setCellType(types.get(this.colIdx+""));}else{//设置类型为前一个单元格的类型if(this.colIdx-1 >= 0 ){_cell.setCellType(this.row.getCell(colIdx-1).getCellType());}}}else{_cell = this.row.getCell(this.colIdx);if(_cell==null){//表头的列超出模板的表头列，生成这个cell，并且设置样式为前一个cell样式_cell = this.row.createCell(this.colIdx);}}this.colIdx++;return _cell;}private void createContentTrStyleMap(){int lastCellNum = this.row.getLastCellNum();Map<String,HSSFCellStyle> styles = new HashMap<>();Map<String,CellType> types = new HashMap<String,CellType>();for(int i=0;i<lastCellNum;i++){if(this.row.getCell(i)!=null){styles.put(i+"", this.row.getCell(i).getCellStyle());types.put(i+"", this.row.getCell(i).getCellType());}}typeMap.put(this.sheetIdx, types);}private void endRow(){List<Merge> sameEndRowMerges = this.mergeMap.get(this.rowIdx);if(sameEndRowMerges!=null){for(Merge m:sameEndRowMerges){System.out.println("----merge region from ("+m.beginRow+","+m.beginCol+") to ("+m.endRow+","+m.endCol+")");/**处理合并行因为没有列而产生的合并后没有边框bug**/HSSFCellStyle mergeBeferCellStyle=this.sheet.getRow(m.beginRow).getCell(m.beginCol).getCellStyle();for (int i=m.beginRow;i<=m.endRow;i++) {for (int j=m.beginCol;j<=m.endCol;j++) {if(m.beginRow==i&&m.beginCol==j) continue;HSSFCell cell=this.sheet.getRow(i).getCell(j);if(cell==null){cell=this.sheet.getRow(i).createCell(m.endCol);}cell.setCellStyle(mergeBeferCellStyle);}}/**处理合并行因为没有列而产生的合并后没有边框bug**/this.sheet.addMergedRegion(new CellRangeAddress(m.beginRow, m.endRow, m.beginCol, m.endCol));}}//处理某行最后一个td后面又被覆盖的单元格的情况for(int i=0;i<this.columns.size();i++){int rowspan = columns.get(i);if(rowspan>0 && !this.columnsSubtracFlags.containsKey(i)){columns.set(i, rowspan-1);}}this.rowIdx++;this.colIdx=0;this.row=null;this.cell=null;this.columnsSubtracFlags.clear();}public void endElement(String namespaceURI, String localName, String qName)throws SAXException {String contentStr = null;if (hasChars)contentStr = content.toString().trim();try {if("tr".equalsIgnoreCase(qName)){this.endRow();}if("td".equalsIgnoreCase(qName) ||"th".equalsIgnoreCase(qName) ||"tmpd".equalsIgnoreCase(qName)){CellType cellType=null;if(this.cellAttributes.containsKey("celltype")){String celltypeStr = this.cellAttributes.get("celltype");cellType=(CellType)getStatifFieldValue(CellType.class,celltypeStr.trim().toUpperCase());}if(this.richFontTexts!=null && this.richFontTexts.size()>0){if(cellType!=null) this.cell.setCellType(cellType);//单元格内的内容为 <font bold="true">加粗字体</font>this.processRichFontTexts();if(cellType!=null) this.cell.setCellType(cellType);}else if(StringUtils.isNotBlank(contentStr)){//内容长度超多32768是excel会报错，此时截取其长度if(StringUtils.isNotBlank(contentStr) && contentStr.length()>32000){contentStr = contentStr.substring(0, 32000);}try{if(CellType.NUMERIC == cellType && contentStr.trim().length()>0){this.cell.setCellType(CellType.NUMERIC);this.cell.setCellValue(Double.valueOf(contentStr.trim()));this.cell.setCellType(CellType.NUMERIC);}else{if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr.trim());if(cellType!=null) this.cell.setCellType(cellType);}}catch(NumberFormatException e){//e.printStackTrace();if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr.trim());if(cellType!=null) this.cell.setCellType(cellType);}}else{if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr);if(cellType!=null) this.cell.setCellType(cellType);}this.createSpecifiedStylesIfNecessary(this.cell);}if("table".equalsIgnoreCase(qName)){this.cleanSheetTemplRowIfNecessary();}if("font".equalsIgnoreCase(qName)){if(this.row!=null && this.cell!=null && this.richFontText!= null){if(this.contentOfRichFontTexts!=null){this.richFontText.content = this.contentOfRichFontTexts.toString();}this.contentOfRichFontTexts = null; this.richFontText = null;}}} finally {this.content = new StringBuffer();hasChars = false;}}private void processRichFontTexts() {StringBuffer allContents = new StringBuffer();for(RichFontText rt : this.richFontTexts){rt.startIndex = allContents.length();allContents.append(rt.content);rt.endIndex = allContents.length();}XSSFRichTextString textString = new XSSFRichTextString(allContents.toString());for(RichFontText rt : this.richFontTexts){HSSFFont font = this.getFontOfRichText(rt);if(font != null){textString.applyFont(rt.startIndex, rt.endIndex, font);}}cell.setCellValue(textString); this.richFontTexts = null;}private Map<String, HSSFFont> fontsOfRichText = new HashMap<>();private HSSFFont getFontOfRichText(RichFontText rt){if(rt.styles!=null && rt.styles.size()>0){HSSFFont font = fontsOfRichText.get(rt.styles.toString());if(font == null){font = this.wb.createFont();for(String k : rt.styles.keySet()){String v = rt.styles.get(k);if("bold".equalsIgnoreCase(k)){font.setBold("true".equalsIgnoreCase(v));}if("fontheightinpoints".equalsIgnoreCase(k)){font.setFontHeightInPoints(Short.valueOf(v.trim()));}if("fontname".equalsIgnoreCase(k)){font.setFontName(v.trim());}}}return font;}else{return null;}}public void characters(char[] ch, int start, int length)throws SAXException {if(this.contentOfRichFontTexts!=null && this.richFontText!=null){this.contentOfRichFontTexts.append(ch, start, length);}else{content.append(ch, start, length);hasChars = true;}}public void fatalError(SAXParseException exception) throws SAXException {throw (exception);}static class Merge{int beginRow;int endRow;int beginCol;int endCol;}static class CellStyle {private String id;private Map<String,String> styles = new HashMap<String, String>();public String getId() {return id;}public void setId(String id) {this.id = id;}public Map<String, String> getStyles() {return styles;}public void setStyles(Map<String, String> styles) {this.styles = styles;}}static class CellFont{String color;String bold;String fontname;String fontheightinpoints;//BUG:WYX 字体不能各样式件共用String styleId;CellFont(String styleId){this.styleId = styleId;}String getFontKey(){return this.styleId+"_FONT:"+this.color+"_"+"_"+this.bold+"_"+this.fontname+"_"+this.fontheightinpoints;}boolean isCustomFont(){return StringUtils.isNotBlank(this.color) || StringUtils.isNotBlank(this.bold) || StringUtils.isNotBlank(this.fontname) || StringUtils.isNotBlank(this.fontheightinpoints);}static CellFont toFont(String styleId,Map<String,String> styles){CellFont font = new CellFont(styleId);if(styles!=null){for(String k : styles.keySet()){String v = styles.get(k);if("font.color".equalsIgnoreCase(k)){font.color = v;}if("font.bold".equalsIgnoreCase(k)){font.bold = v;}if("font.fontname".equalsIgnoreCase(k)){font.fontname = v;}if("font.fontheightinpoints".equalsIgnoreCase(k)){font.fontheightinpoints = v;}}}return font;}}static class RichFontText{Map<String,String> styles = new LinkedHashMap<>();String content;int startIndex;int endIndex;}}

StyledXmlToXlsxbook(.xlsx)

public class StyledXmlToXlsxbook  extends DefaultHandler  {private InputStream excelTemplate;private StringBuffer content = new StringBuffer();private boolean hasChars = false;private String xml;private OutputStream output;/*** excel*/private XSSFWorkbook wb = null;private XSSFSheet sheet = null;//当前处理的行private XSSFRow row = null;/*** 当前处理的Excel*/private XSSFCell cell = null;private Map<Integer,Map<String,CellType>> typeMap = new HashMap<>();//定义的样式表private Map<String, XSSFCellStyle> defineStyleMap = new HashMap<>();/*** excel模板的sheet索引*/private int sheetIdx = -1;/*** excel模板的sheet的最大索引号* 在处理完毕一个table后， 需要将剩余的模板行删除掉*/private int sheetMaxTmplRowIdx = -1;private int rowIdx = -1;private int colIdx = -1;private boolean contentTr = false;private List<Integer> columns = new ArrayList<>();private Map<Integer,List<Merge>> mergeMap = new HashMap<>();private Map<String,String> cellAttributes = new HashMap<>();private Map<String, XSSFColor> colorIndexMap = new HashMap<>();//用于标志tr结束后，columns所有的元素是否都减去1了private Map<Integer,Boolean> columnsSubtracFlags = new HashMap<Integer, Boolean>();private Map<String,CellStyle> xmlStyles = new HashMap<String, CellStyle>();private CellStyle currStyle = null;private Map<String,XSSFFont> fonts = new HashMap<>();//用于解决通过自动换行自适应行高但同时又需要设置最小行高的情况private Map<XSSFRow, Short> rowMinHeights = new LinkedHashMap<>();private List<RichFontText> richFontTexts = null;private RichFontText richFontText = null;private StringBuffer contentOfRichFontTexts = null;/*** * @param excelTemplate Excel模板的Inputstram* @param tableXml table的xml内容* @param output 输出的output*/public StyledXmlToXlsxbook(InputStream excelTemplate,String tableXml,OutputStream output){this.xml=tableXml;this.output = output;this.excelTemplate=excelTemplate;}public void parse() {try {SAXParserFactory spFactory = SAXParserFactory.newInstance();spFactory.setFeature("http://xml.org/sax/features/validation",false);spFactory.setValidating(false);SAXParser sParser = spFactory.newSAXParser();XMLReader xr = sParser.getXMLReader();xr.setErrorHandler(this);xr.setContentHandler(this);xr.parse(new InputSource(new StringReader(this.xml)));} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}}/*** 初始化Excel*/private void initWorkbook(){try {this.wb = new XSSFWorkbook(this.excelTemplate);} catch (IOException e) {throw new RuntimeException(e);}}/*** 将excel写入到excel输出文件中*/private void writeWorkbook(){try {this.wb.write(this.output);} catch (IOException e) {throw new RuntimeException(e);}}public void startDocument() throws SAXException {this.initWorkbook();}public void endDocument() throws SAXException {for(XSSFRow row:this.rowMinHeights.keySet()){Short minHeight = this.rowMinHeights.get(row);if(row.getHeight()<minHeight){row.setHeight(minHeight);}}this.cleanTmplSheetIfNecessary();this.writeWorkbook();}private void initSheet(){this.sheetIdx++;if(sheetIdx>=this.wb.getNumberOfSheets()){this.wb.createSheet();this.row = this.wb.getSheetAt(this.sheetIdx).createRow(0);}this.sheet = this.wb.getSheetAt(this.sheetIdx);this.colIdx=0;this.rowIdx=0;this.sheetMaxTmplRowIdx =this.sheet.getLastRowNum();this.columns.clear();this.mergeMap.clear();this.contentTr=false;this.row=null;this.cell=null;}/*** 清除剩余的模板的sheet*/private void cleanTmplSheetIfNecessary(){List<Integer> removeSheetIdxs = new ArrayList<Integer>();for(int i=this.sheetIdx+1;i>=0;i++){try{XSSFSheet sheet = this.wb.getSheetAt(i);if(sheet!=null){removeSheetIdxs.add(i);}else{break;}}catch(Exception e){break;}}int removed = 0;int maxSheetIdx = this.wb.getNumberOfSheets();boolean emptyExcel = false;if(removeSheetIdxs.size()==maxSheetIdx){removeSheetIdxs.remove(0);emptyExcel=true;}for(Integer i:removeSheetIdxs){wb.removeSheetAt(i-removed);removed++;}if(emptyExcel){this.cleanAllRowsAtSheet(0);}}public void startElement(String namespaceURI, String localName,String qName, Attributes attr) throws SAXException {this.content = new StringBuffer();if ("style".equalsIgnoreCase(qName)) {this.currStyle = new CellStyle();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);if("id".equalsIgnoreCase(n)){this.currStyle.setId(v.trim());}this.currStyle.getStyles().put(n, v);if(StringUtils.isNotBlank(this.currStyle.getId())){this.xmlStyles.put(this.currStyle.getId(), this.currStyle);}}return;}if ("table".equalsIgnoreCase(qName)) {this.initSheet();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);if("name".equalsIgnoreCase(n)){this.wb.setSheetName(this.sheetIdx, v);}}return;}if ("tr".equalsIgnoreCase(qName)) {if(contentTr){this.row = this.sheet.createRow(this.rowIdx);}else{this.row = this.sheet.getRow(this.rowIdx);}return;}if("font".equalsIgnoreCase(qName)){if(this.row!=null && this.cell!=null){if(this.richFontTexts == null){this.richFontTexts = new ArrayList<>();}this.richFontText = new RichFontText();this.richFontTexts.add(this.richFontText);this.contentOfRichFontTexts = new StringBuffer();for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);this.richFontText.styles.put(n.toLowerCase(), v);}}}if ("td".equalsIgnoreCase(qName) ||"th".equalsIgnoreCase(qName) ||"tmpd".equalsIgnoreCase(qName)) {int colspan=1;int rowspan=1;cellAttributes.clear();this.richFontTexts = null;int actualColIdx = this.colIdx;for (int i = 0; i < attr.getLength(); i++) {String n = attr.getQName(i);String v = attr.getValue(n);cellAttributes.put(n.toLowerCase(), v);if ("colspan".equalsIgnoreCase(n)) {colspan = Integer.valueOf(v.trim());}if ("rowspan".equalsIgnoreCase(n)) {rowspan = Integer.valueOf(v.trim());}if("selectList".equalsIgnoreCase(n)){cellAttributes.put("selectList", v);}}if("td".equalsIgnoreCase(qName)){if(!contentTr){this.createContentTrStyleMap();}contentTr=true;}if(this.columns.size()<=this.colIdx){//这是第一行,生成columns元数据for(int i=0;i<colspan;i++){this.columns.add(rowspan);//endTr函数中统一从rowspan-1XSSFCell _cell = this.createCell();if(i==0){this.cell = _cell;}}}else{int currTdColIdx = -1;for(int i=this.colIdx;i<columns.size();i++){if(columns.get(i)>0 || "tmpd".equalsIgnoreCase(qName)){//当前单元格全面有被覆盖的单元格columns.set(i, columns.get(i)-1);columnsSubtracFlags.put(i, Boolean.TRUE);//创建一个dummy单元格,用于进行合并this.createCell();}else{currTdColIdx = i;actualColIdx = currTdColIdx;break;}}for(int i=0;i<colspan;i++){if(!"tmpd".equalsIgnoreCase(qName)){this.colIdx = i+currTdColIdx;this.columns.set(colIdx,rowspan-1);columnsSubtracFlags.put(colIdx, Boolean.TRUE);XSSFCell _cell = this.createCell();if(i==0){this.cell = _cell;}}}}//计算需合并区域if(colspan>1 || rowspan>1){Merge m = new Merge();m.beginCol=actualColIdx;m.beginRow=this.rowIdx;m.endRow=m.beginRow+rowspan-1;m.endCol=m.beginCol+colspan-1;List<Merge> sameEndRowMerges = this.mergeMap.get(m.endRow);if(sameEndRowMerges==null){sameEndRowMerges = new ArrayList<Merge>();mergeMap.put(m.endRow, sameEndRowMerges);}sameEndRowMerges.add(m);}if(StringUtils.isNotBlank(cellAttributes.get("selectList"))){CellRangeAddressList regions = new CellRangeAddressList(this.rowIdx,this.rowIdx,actualColIdx,actualColIdx);// 创建下拉列表数据XSSFDataValidationConstraint constraint=new XSSFDataValidationConstraint(cellAttributes.get("selectList").split(","));CTDataValidation validation = CTDataValidation.Factory.newInstance();// 绑定XSSFDataValidation dataValidation = new XSSFDataValidation(constraint, regions, validation);this.sheet.addValidationData(dataValidation);}}}/*** 删除剩余的模板行数*/private void cleanSheetTemplRowIfNecessary(){if(this.rowIdx<=this.sheetMaxTmplRowIdx){for(int i=rowIdx;i<=this.sheetMaxTmplRowIdx;i++){if(this.sheet.getRow(i)!=null){//加非空判断（成刊信息管理导出时此处报空指针异常）this.sheet.removeRow(this.sheet.getRow(i));}}}}/*** 情况所有的行* @param sheetIdx*/private void cleanAllRowsAtSheet(int sheetIdx){XSSFSheet _sheet = this.wb.getSheetAt(sheetIdx);int maxRow = _sheet.getLastRowNum();if(maxRow>0){for(int i=0;i<=maxRow;i++){_sheet.removeRow(_sheet.getRow(i));}}}public boolean greater(int beginIndex,List<Integer> columns){boolean greater= false;if(columns==null || columns.size()+1<beginIndex){return greater;}for(int i=beginIndex;i<columns.size();i++){if(columns.get(i)>0){greater = true;break;}}return greater;}private void cloneFont(XSSFFont clone,XSSFFont font){clone.setBold(font.getBold());clone.setCharSet(font.getCharSet());clone.setColor(font.getColor());clone.setFontHeight(font.getFontHeight());clone.setFontHeightInPoints(font.getFontHeightInPoints());clone.setFontName(font.getFontName());clone.setItalic(font.getItalic());clone.setStrikeout(font.getStrikeout());clone.setTypeOffset(font.getTypeOffset());clone.setUnderline(font.getUnderline());}/*** 设定特定的样式* @param _cell*/private void createSpecifiedStylesIfNecessary(XSSFCell _cell){for(String k:cellAttributes.keySet()){String v = cellAttributes.get(k);if("width".equals(k)){int w = calcCellWidth(v);if(this.rowIdx>0){}sheet.setColumnWidth(_cell.getColumnIndex(),w);}if("height".equals(k)){short h = Double.valueOf( ( Double.valueOf(v)*20)+"").shortValue();this.row.setHeight(h);}if("minHeight".equals(k)){short h = Double.valueOf( ( Double.valueOf(v)*20)+"").shortValue();// this.row.setHeight(h);this.rowMinHeights.put(this.row, h);}if("styleId".equalsIgnoreCase(k)){XSSFCellStyle style = this.getDefinedCellStyle2(_cell,v.trim());if(style!=null){_cell.setCellStyle(style);}}}}/*** 创建样式* @param _cell* @return*/private XSSFCellStyle getDefinedCellStyle2(XSSFCell _cell,String styleId){XSSFCellStyle style = this.defineStyleMap.get(styleId);if(style == null){style = this.wb.createCellStyle();/*if(_cell.getCellStyle()!=null){style.cloneStyleFrom(_cell.getCellStyle());   }*/CellStyle xmlStyle = this.xmlStyles.get(styleId);if(xmlStyle!=null&&xmlStyle.getStyles()!=null){for(String k:xmlStyle.getStyles().keySet()){String v = xmlStyle.getStyles().get(k);if("alignment".equals(k)){style.setAlignment((HorizontalAlignment)getStatifFieldValue(HorizontalAlignment.class,v.trim().toUpperCase()));}if("vertical".equals(k)){style.setVerticalAlignment((VerticalAlignment)getStatifFieldValue(VerticalAlignment.class,v.trim().toUpperCase()));}if("wrapText".equalsIgnoreCase(k)){style.setWrapText("true".equalsIgnoreCase(v));}if("backgroundColor".equalsIgnoreCase(k)){style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillForegroundColor(this.getColorIndex(v.trim()));}if("border".equals(k)){List<String> borders=BaseUtils.splitString(v,",");if(borders.size()==1){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));}else if(borders.size()==2){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));}else if(borders.size()==4){style.setBorderTop(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(0))*1)));style.setBorderBottom(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(1))*1)));style.setBorderLeft(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(2))*1)));style.setBorderRight(BorderStyle.valueOf((short)(Integer.valueOf(borders.get(4))*1)));}}if("dataFormat".equals(k)){style.setDataFormat(HSSFDataFormat.getBuiltinFormat(v));}}CellFont cellFont = CellFont.toFont(styleId,xmlStyle.getStyles());if(cellFont.isCustomFont()){XSSFFont font = this.fonts.get(cellFont.getFontKey());if(font==null){font = this.wb.createFont();if(_cell.getCellStyle()!=null && this.wb.getFontAt(_cell.getCellStyle().getFontIndex())!=null){this.cloneFont(font, this.wb.getFontAt(_cell.getCellStyle().getFontIndex())) ;}if(StringUtils.isNotBlank(cellFont.bold)){font.setBold("true".equalsIgnoreCase(cellFont.bold));}if(StringUtils.isNotBlank(cellFont.color)){font.setColor(this.getColorIndex(cellFont.color));}if(StringUtils.isNotBlank(cellFont.fontheightinpoints)){font.setFontHeightInPoints(Short.valueOf(cellFont.fontheightinpoints.trim()));}if(StringUtils.isNotBlank(cellFont.fontname)){font.setFontName(cellFont.fontname.trim());}this.fonts.put(cellFont.getFontKey(), font);}style.setFont(font);}}defineStyleMap.put(styleId, style);}else{return style;}return style;}private int calcCellWidth(String v) {double excelWidth = Double.valueOf(v);double charWidth = 8;Double dw = (excelWidth * (charWidth - 1) + 5) / (charWidth - 1) * 256;int w = dw.intValue();return w;}/*** 创建自定义的颜色索引* @param rgbcolor* @return*/private XSSFColor getColorIndex(String rgbcolor){rgbcolor = rgbcolor.replaceFirst("#", "").trim().toUpperCase();XSSFColor cl = this.colorIndexMap.get(rgbcolor);if(cl!=null){return cl;}int[] rgbColor=new int[3];rgbColor[0]=Integer.parseInt(rgbcolor.substring(0, 2), 16);rgbColor[1]=Integer.parseInt(rgbcolor.substring(2, 4), 16);rgbColor[2]=Integer.parseInt(rgbcolor.substring(4, 6), 16);cl = new XSSFColor(new java.awt.Color(rgbColor[0], rgbColor[1], rgbColor[2]), null);//自定义颜色this.colorIndexMap.put(rgbcolor, cl);return cl;}private Object getStatifFieldValue(Class<?> clazz,String field){Field[] fs = clazz.getFields();// 取得当前类的所以属性for (Field f : fs) {// 把FS的值付给f进行循繁if (f.getName().equals(field.trim())) {try {Object v = f.get(clazz);return v;} catch (Exception e) {throw new RuntimeException(e);}}}throw new RuntimeException("no field："+field+" in class:"+clazz);}private XSSFCell createCell(){XSSFCell _cell = null;if(this.contentTr){_cell = this.row.createCell(this.colIdx);Map<String,CellType> types=this.typeMap.get(this.sheetIdx);if(types!=null && types.get(this.colIdx+"")!=null){_cell.setCellType(types.get(this.colIdx+""));}else{//设置类型为前一个单元格的类型if(this.colIdx-1 >= 0 ){_cell.setCellType(this.row.getCell(colIdx-1).getCellType());}}}else{_cell = this.row.getCell(this.colIdx);if(_cell==null){//表头的列超出模板的表头列，生成这个cell，并且设置样式为前一个cell样式_cell = this.row.createCell(this.colIdx);}}this.colIdx++;return _cell;}private void createContentTrStyleMap(){int lastCellNum = this.row.getLastCellNum();Map<String,XSSFCellStyle> styles = new HashMap<>();Map<String,CellType> types = new HashMap<String,CellType>();for(int i=0;i<lastCellNum;i++){if(this.row.getCell(i)!=null){styles.put(i+"", this.row.getCell(i).getCellStyle());types.put(i+"", this.row.getCell(i).getCellType());}}typeMap.put(this.sheetIdx, types);}private void endRow(){List<Merge> sameEndRowMerges = this.mergeMap.get(this.rowIdx);if(sameEndRowMerges!=null){for(Merge m:sameEndRowMerges){System.out.println("----merge region from ("+m.beginRow+","+m.beginCol+") to ("+m.endRow+","+m.endCol+")");/**处理合并行因为没有列而产生的合并后没有边框bug**/XSSFCellStyle mergeBeferCellStyle=this.sheet.getRow(m.beginRow).getCell(m.beginCol).getCellStyle();for (int i=m.beginRow;i<=m.endRow;i++) {for (int j=m.beginCol;j<=m.endCol;j++) {if(m.beginRow==i&&m.beginCol==j) continue;XSSFCell cell=this.sheet.getRow(i).getCell(j);if(cell==null){cell=this.sheet.getRow(i).createCell(m.endCol);}cell.setCellStyle(mergeBeferCellStyle);}}/**处理合并行因为没有列而产生的合并后没有边框bug**/this.sheet.addMergedRegion(new CellRangeAddress(m.beginRow, m.endRow, m.beginCol, m.endCol));}}//处理某行最后一个td后面又被覆盖的单元格的情况for(int i=0;i<this.columns.size();i++){int rowspan = columns.get(i);if(rowspan>0 && !this.columnsSubtracFlags.containsKey(i)){columns.set(i, rowspan-1);}}this.rowIdx++;this.colIdx=0;this.row=null;this.cell=null;this.columnsSubtracFlags.clear();}public void endElement(String namespaceURI, String localName, String qName)throws SAXException {String contentStr = null;if (hasChars)contentStr = content.toString().trim();try {if("tr".equalsIgnoreCase(qName)){this.endRow();}if("td".equalsIgnoreCase(qName) ||"th".equalsIgnoreCase(qName) ||"tmpd".equalsIgnoreCase(qName)){CellType cellType=null;if(this.cellAttributes.containsKey("celltype")){String celltypeStr = this.cellAttributes.get("celltype");cellType=(CellType)getStatifFieldValue(CellType.class,celltypeStr.trim().toUpperCase());}if(this.richFontTexts!=null && this.richFontTexts.size()>0){if(cellType!=null) this.cell.setCellType(cellType);//单元格内的内容为 <font bold="true">加粗字体</font>this.processRichFontTexts();if(cellType!=null) this.cell.setCellType(cellType);}else if(StringUtils.isNotBlank(contentStr)){//内容长度超多32768是excel会报错，此时截取其长度if(StringUtils.isNotBlank(contentStr) && contentStr.length()>32000){contentStr = contentStr.substring(0, 32000);}try{if(CellType.NUMERIC == cellType && contentStr.trim().length()>0){this.cell.setCellType(CellType.NUMERIC);this.cell.setCellValue(Double.valueOf(contentStr.trim()));this.cell.setCellType(CellType.NUMERIC);}else{if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr.trim());if(cellType!=null) this.cell.setCellType(cellType);}}catch(NumberFormatException e){//e.printStackTrace();if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr.trim());if(cellType!=null) this.cell.setCellType(cellType);}}else{if(cellType!=null) this.cell.setCellType(cellType);this.cell.setCellValue(contentStr);if(cellType!=null) this.cell.setCellType(cellType);}this.createSpecifiedStylesIfNecessary(this.cell);}if("table".equalsIgnoreCase(qName)){this.cleanSheetTemplRowIfNecessary();}if("font".equalsIgnoreCase(qName)){if(this.row!=null && this.cell!=null && this.richFontText!= null){if(this.contentOfRichFontTexts!=null){this.richFontText.content = this.contentOfRichFontTexts.toString();}this.contentOfRichFontTexts = null; this.richFontText = null;}}} finally {this.content = new StringBuffer();hasChars = false;}}private void processRichFontTexts() {StringBuffer allContents = new StringBuffer();for(RichFontText rt : this.richFontTexts){rt.startIndex = allContents.length();allContents.append(rt.content);rt.endIndex = allContents.length();}XSSFRichTextString textString = new XSSFRichTextString(allContents.toString());for(RichFontText rt : this.richFontTexts){XSSFFont font = this.getFontOfRichText(rt);if(font != null){textString.applyFont(rt.startIndex, rt.endIndex, font);}}this.cell.setCellValue(textString); this.richFontTexts = null;}private Map<String, XSSFFont> fontsOfRichText = new HashMap<>();private XSSFFont getFontOfRichText(RichFontText rt){if(rt.styles!=null && rt.styles.size()>0){XSSFFont font = fontsOfRichText.get(rt.styles.toString());if(font == null){font = this.wb.createFont();for(String k : rt.styles.keySet()){String v = rt.styles.get(k);if("bold".equalsIgnoreCase(k)){font.setBold("true".equalsIgnoreCase(v));}if("fontheightinpoints".equalsIgnoreCase(k)){font.setFontHeightInPoints(Short.valueOf(v.trim()));}if("fontname".equalsIgnoreCase(k)){font.setFontName(v.trim());}}}return font;}else{return null;}}public void characters(char[] ch, int start, int length)throws SAXException {if(this.contentOfRichFontTexts!=null && this.richFontText!=null){this.contentOfRichFontTexts.append(ch, start, length);}else{content.append(ch, start, length);hasChars = true;}}public void fatalError(SAXParseException exception) throws SAXException {throw (exception);}static class Merge{int beginRow;int endRow;int beginCol;int endCol;}static class CellStyle {private String id;private Map<String,String> styles = new HashMap<String, String>();public String getId() {return id;}public void setId(String id) {this.id = id;}public Map<String, String> getStyles() {return styles;}public void setStyles(Map<String, String> styles) {this.styles = styles;}}static class CellFont{String color;String bold;String fontname;String fontheightinpoints;//BUG:WYX 字体不能各样式件共用String styleId;CellFont(String styleId){this.styleId = styleId;}String getFontKey(){return this.styleId+"_FONT:"+this.color+"_"+"_"+this.bold+"_"+this.fontname+"_"+this.fontheightinpoints;}boolean isCustomFont(){return StringUtils.isNotBlank(this.color) || StringUtils.isNotBlank(this.bold) || StringUtils.isNotBlank(this.fontname) || StringUtils.isNotBlank(this.fontheightinpoints);}static CellFont toFont(String styleId,Map<String,String> styles){CellFont font = new CellFont(styleId);if(styles!=null){for(String k : styles.keySet()){String v = styles.get(k);if("font.color".equalsIgnoreCase(k)){font.color = v;}if("font.bold".equalsIgnoreCase(k)){font.bold = v;}if("font.fontname".equalsIgnoreCase(k)){font.fontname = v;}if("font.fontheightinpoints".equalsIgnoreCase(k)){font.fontheightinpoints = v;}}}return font;}}static class RichFontText{Map<String,String> styles = new LinkedHashMap<>();String content;int startIndex;int endIndex;}}

VelocityUtils(模板解析)

public class VelocityUtils {private static VelocityEngine ve=null;private static VelocityEngine getVelocityEngine() {if(ve==null) {Properties p = new Properties();// 设置输入输出编码类型。p.put(RuntimeConstants.INPUT_ENCODING, StandardCharsets.UTF_8.name());p.put(RuntimeConstants.ENCODING_DEFAULT, StandardCharsets.UTF_8.name());//设置velocity资源加载方式为类路径p.put(RuntimeConstants.RESOURCE_LOADERS, "classpath");//设置velocity资源加载方式为类路径时的 处理类p.put(RuntimeConstants.RESOURCE_LOADER+".classpath."+RuntimeConstants.RESOURCE_LOADER_CLASS,ClasspathResourceLoader.class.getName());//p.put(RuntimeConstants.RESOURCE_LOADER+".classpath."+RuntimeConstants.RESOURCE_LOADER_INSTANCE, new ClasspathResourceLoader());p.put(RuntimeConstants.FILE_RESOURCE_LOADER_CACHE, Boolean.FALSE);//禁用反射处理p.put(RuntimeConstants.UBERSPECT_CLASSNAME, SecureUberspector.class.getName());ve = new VelocityEngine(p);ve.init();}return ve;}/*********************************************************开始根据模板内容渲染*********************************************************//*** 使用Velocity 根据模板内容 渲染到StringWriter* @param tmplContent* @param out* @param datas* @*/public static final void renderByTmplContent(String tmplContent, StringWriter sw,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}try {//替换模板内容ve.evaluate(context, sw, "", tmplContent);} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");}}/*** 使用Velocity 根据模板内容 渲染到OutputStream* @param tmplContent* @param out* @param datas* @*/public static final void renderByTmplContent(String tmplContent, OutputStream out,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}try {//替换模板内容StringWriter sw = new StringWriter();ve.evaluate(context, sw, "", tmplContent);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");}}/*** 使用Velocity 根据模板内容 渲染到File* @param tmplContent* @param out* @param datas* @*/public static final void renderByTmplContent(String tmplContent, File file,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}OutputStream out=null;try {out=new FileOutputStream(file);//替换模板内容StringWriter sw = new StringWriter();ve.evaluate(context, sw, "", tmplContent);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");} finally{BaseUtils.clean(out);}}/*********************************************************开始根据模板文件渲染*********************************************************//*** 使用Velocity 根据模板文件 渲染到StringWriter* @param tmplFile* @param out* @param datas* @*/public static final void renderByTmplFile(File tmplFile, StringWriter sw,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}Reader r=null;try {r=new FileReader(tmplFile);//替换模板内容ve.evaluate(context, sw, "", r);} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");} finally{BaseUtils.clean(r);}}/*** 使用Velocity 根据模板文件 渲染到OutputStream* @param tmplFile* @param out* @param datas* @*/public static final void renderByTmplFile(File tmplFile, OutputStream out,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}Reader r=null;try {r=new FileReader(tmplFile);//替换模板内容StringWriter sw = new StringWriter();ve.evaluate(context, sw, "", r);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");} finally{BaseUtils.clean(r);}}/*** 使用Velocity 根据模板文件 渲染到File* @param tmplFile* @param out* @param datas* @*/public static final void renderByTmplFile(File tmplFile, File file,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}Reader r=null;OutputStream out=null;try {r=new FileReader(tmplFile);out=new FileOutputStream(file);//替换模板内容StringWriter sw = new StringWriter();ve.evaluate(context, sw, "", r);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");} finally{BaseUtils.clean(r, out);}}/*********************************************************开始根据模板相对类路径渲染*********************************************************//*** 使用Velocity 根据模板相对类路径 渲染到StringWriter* @param tmplRelativePath* @param out* @param datas* @*/public static final void renderByTmplRelativePath(String tmplRelativePath, StringWriter sw,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}Template t = ve.getTemplate(tmplRelativePath);//"xmlTemplate/performancetask_export.xml"类路径下的模板try {//替换模板内容t.merge(context, sw);} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");}}/*** 使用Velocity 根据模板相对类路径 渲染到OutputStream* @param tmplRelativePath* @param out* @param datas* @*/public static final void renderByTmplRelativePath(String tmplRelativePath, OutputStream out,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}Template t = ve.getTemplate(tmplRelativePath);//"xmlTemplate/performancetask_export.xml"类路径下的模板try {//替换模板内容StringWriter sw = new StringWriter();t.merge(context, sw);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");}}/*** 使用Velocity 根据模板相对类路径 渲染到File* @param tmplRelativePath* @param out* @param datas* @*/public static final void renderByTmplRelativePath(String tmplRelativePath, File file,Map<String, Object> datas) {//获取 VelocityEngineVelocityEngine ve = getVelocityEngine();VelocityContext context = new VelocityContext();if(datas!=null&&datas.size()>0){for (Map.Entry<String,Object> item : datas.entrySet()) {context.put(item.getKey(),item.getValue());}}OutputStream out=null;Template t = ve.getTemplate(tmplRelativePath);//"xmlTemplate/performancetask_export.xml"类路径下的模板try {out=new FileOutputStream(file);//替换模板内容StringWriter sw = new StringWriter();t.merge(context, sw);String renderContent = sw.toString();out.write(renderContent.getBytes("UTF-8"));out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("渲染模板出错");} finally{BaseUtils.clean(out);}}}

ExportExcelUtils

public class ExportExcelUtils {private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);/*** 根据模板相对路径导出excel** @param tmplRelativePath* @param excleTmplRelativePath* @param exportFile* @param datas*/public static final void exportExcelByTmplRelativePath(String tmplRelativePath, String excleTmplRelativePath,File exportFile, Map<String, Object> datas) {StringWriter sw = new StringWriter();VelocityUtils.renderByTmplRelativePath(tmplRelativePath, sw, datas);
//        String osName = System.getProperty("os.name");InputStream is = null;OutputStream out = null;try {if (!exportFile.getParentFile().exists()) {exportFile.getParentFile().mkdirs();}if (!exportFile.exists()) {exportFile.createNewFile();}is = ExportExcelUtils.class.getClassLoader().getResourceAsStream(excleTmplRelativePath);out = new FileOutputStream(exportFile);if (exportFile.getName().endsWith(".xls")) {StyledXmlToWorkbook xwb = new StyledXmlToWorkbook(is, sw.toString(), out);xwb.parse();} else {StyledXmlToXlsxbook xwb = new StyledXmlToXlsxbook(is, sw.toString(), out);xwb.parse();}out.flush();} catch (Exception e) {logger.warn("exportExcelByTmplRelativePath 出现异常:", e);throw new RuntimeException("生成excel文档出错" + e.getMessage());} finally {BaseUtils.clean(out, is);}}public static final void exportExcelByTmplRelativePath(String tmplRelativePath, String excleTmplRelativePath,File exportFile, Object... datas) {exportExcelByTmplRelativePath(tmplRelativePath, excleTmplRelativePath, exportFile, BaseUtils.buildMap(datas));}/*** 根据模板相对路径导出excel** @param tmplRelativePath* @param excleTmplRelativePath* @param out* @param outFileName* @param datas*/public static final void exportExcelByTmplRelativePath(String tmplRelativePath, String excleTmplRelativePath,OutputStream out, String outFileName, Map<String, Object> datas) {StringWriter sw = new StringWriter();VelocityUtils.renderByTmplRelativePath(tmplRelativePath, sw, datas);InputStream is = null;try {is = ExportExcelUtils.class.getClassLoader().getResourceAsStream(excleTmplRelativePath);if (StringUtils.isNotBlank(outFileName) && outFileName.endsWith(".xls")) {StyledXmlToWorkbook xwb = new StyledXmlToWorkbook(is, sw.toString(), out);xwb.parse();} else {StyledXmlToXlsxbook xwb = new StyledXmlToXlsxbook(is, sw.toString(), out);xwb.parse();}out.flush();} catch (Exception e) {e.printStackTrace();throw new RuntimeException("生成excel文档出错" + e.getMessage());} finally {BaseUtils.clean(is);}}public static final void exportExcelByTmplRelativePath(String tmplRelativePath, String excleTmplRelativePath,OutputStream out, String outFileName, Object... datas) {exportExcelByTmplRelativePath(tmplRelativePath, excleTmplRelativePath, out, outFileName, BaseUtils.buildMap(datas));}//检查 并导出 excel或者压缩包 public static final File checkExportExcelOrZipByTmplRelativePath(String tmplRelativePath, String excleTmplRelativePath,String fileDir, String fileName, List<Map<String, Object>> batchDatas) {File exportFile = new File(fileDir, fileName);if (CollectionUtils.isEmpty(batchDatas)) {exportExcelByTmplRelativePath(tmplRelativePath, excleTmplRelativePath, exportFile, (Map<String, Object>) null);return exportFile;}if (batchDatas.size() == 1) {exportExcelByTmplRelativePath(tmplRelativePath, excleTmplRelativePath, exportFile, batchDatas.get(0));return exportFile;}String fileNotExtName = fileName;//文件无后缀名称String fileExt = "";//文件后缀 待.if (fileName != null) {int idx = fileName.lastIndexOf(".");if (idx > -1) {fileNotExtName = fileName.substring(0, idx);fileExt = fileName.substring(idx);}}exportFile = new File(fileDir, fileNotExtName + ".zip");File zipDir = new File(fileDir, BaseUtils.getUUID(""));try {if (!zipDir.exists()) {zipDir.mkdirs();}//分批导出 并压缩int i = 0;for (Map<String, Object> datas : batchDatas) {File excelFile = new File(zipDir, fileNotExtName + "(" + i + ")" + fileExt);try {exportExcelByTmplRelativePath(tmplRelativePath, excleTmplRelativePath, excelFile, datas);} catch (Exception e) {e.printStackTrace();throw e;}i++;}ZipUtils.zipFile(zipDir.getAbsolutePath(), exportFile.getAbsolutePath(), false);} catch (Exception e) {e.printStackTrace();throw new RuntimeException("生成zip文档出错," + e.getMessage());} finally {BaseUtils.deleteFile(zipDir);}return exportFile;}}

实战

xml模板

<?xml version="1.0" encoding="UTF-8"?>
<tables><styles><style id="h1" font.fontheightinpoints="10" font.fontname="Arial Unicode MS" alignment="center" font.bold="true" border="1" wrapText="true"/><style id="header_cell_center_bold" font.fontheightinpoints="18" font.fontname="宋体"  alignment="center" vertical="center" font.bold="true"  border="1" wrapText="true"/><style id="content_cell_center" font.fontheightinpoints="15" font.fontname="Arial Unicode MS"  alignment="center" font.size="50" vertical="center" border="1" wrapText="true"/><style id="content_cell_left" font.fontheightinpoints="15" font.fontname="宋体"  alignment="left" vertical="center" font.size="50" border="1" wrapText="true"/></styles><table name="问题点位统计"><tr><td styleId="header_cell_center_bold" height="40" width="20" rowspan="3" colspan="2">问题点位</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="3">阶段</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">总量</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">个体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">群体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="6">初次信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="6">重复信访</td></tr><tr><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">人次</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">人次</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20" rowspan="2">人次</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">个体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">群体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">小计</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">个体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">群体信访</td><td styleId="header_cell_center_bold" height="40" width="20" colspan="2">小计</td></tr><tr><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td><td styleId="header_cell_center_bold" height="40" width="20">批(件)次</td><td styleId="header_cell_center_bold" height="40" width="20">人次</td></tr>#foreach($list in $result.entrySet())#foreach($data in $list.value)<tr>#if($foreach.count==1)#if($data.sf == 'biotope')<td styleId="content_cell_center" height="40" width="20" rowspan="$!{rowspan}">$!{data.orderNo}</td><td styleId="content_cell_center" height="40" width="20" rowspan="$!{rowspan}">$!{data.cbdw}</td>#else<td styleId="content_cell_center" height="40" width="20" colspan="2"  rowspan="$!{rowspan}">$!{data.cbdw}</td>#end#end<td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('jd').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('zl_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('zl_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('gtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('gtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('qtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('qtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_gtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_gtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_qtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_qtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_xj_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('ccxf_xj_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_gtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_gtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_qtxf_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_qtxf_rc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_xj_pjc').df}</td><td styleId="content_cell_center" height="40" width="20">$!{data.totalDataMap.get('cfxf_xj_rc').df}</td></tr>#end#end</table>
</tables>

数据封装DTO

表头

@ApiModel(value="TableHeaderDTO ",description="统计表头")
@JsonIgnoreProperties(ignoreUnknown=true)
public class TableHeaderDTO {@ApiModelProperty(value = "表头名称")private String title;@ApiModelProperty(value = "对应数据值")private String code;@ApiModelProperty(value = "对应考核指标id-总表使用")private String key;@ApiModelProperty(value = "下级表头")private List<TableHeaderDTO> childHeader = new ArrayList<>();@ApiModelProperty(value = "占用表头个数/无下级本级也需要占用一个")private int tabSize;@ApiModelProperty(value = "下级个数:指的是所有下级")private int childSize;@ApiModelProperty(value = "是否展示该表头-生成数据查询时用到")private boolean isDisplay = true;@ApiModelProperty(value = "该考核指标对应的统计指标")private SysDataSimpleDTO index;public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getCode() {return code;}public void setCode(String code) {this.code = code;}public String getKey() {return key;}public void setKey(String key) {this.key = key;}public List<TableHeaderDTO> getChildHeader() {return childHeader;}public void setChildHeader(List<TableHeaderDTO> childHeader) {this.childHeader = childHeader;}public int getChildSize() {return childSize;}public void setChildSize(int childSize) {this.childSize = childSize;}public int getTabSize() {return tabSize;}public void setTabSize(int tabSize) {this.tabSize = tabSize;}public boolean isDisplay() {return isDisplay;}public void setDisplay(boolean display) {isDisplay = display;}public SysDataSimpleDTO getIndex() {return index;}public void setIndex(SysDataSimpleDTO index) {this.index = index;}public TableHeaderDTO() {}public TableHeaderDTO(String title) {this.title = title;this.tabSize = 1;this.childSize = 0;}public TableHeaderDTO(String title, String code) {this.title = title;this.code = code;this.tabSize = 1;this.childSize = 0;}public TableHeaderDTO(String title, String code, String key) {this.title = title;this.code = code;this.key = key;this.tabSize = 1;this.childSize = 0;}public TableHeaderDTO(String title, List<TableHeaderDTO> childHeader) {this.title = title;this.childHeader = childHeader;this.childSize = childHeader.size();this.defaultTabSize(childHeader);}public TableHeaderDTO(String title, String key, List<TableHeaderDTO> childHeader) {this.title = title;this.key = key;this.childHeader = childHeader;this.childSize = childHeader.size();this.defaultTabSize(childHeader);}public TableHeaderDTO(String title, String code, String key, List<TableHeaderDTO> childHeader) {this.title = title;this.key = key;this.code = code;this.childHeader = childHeader;this.childSize = childHeader.size();this.defaultTabSize(childHeader);}public TableHeaderDTO(String title, String key, List<TableHeaderDTO> childHeader, boolean isDisplay, SysDataSimpleDTO index) {this.title = title;this.key = key;this.childHeader = childHeader;this.childSize = childHeader.size();this.defaultTabSize(childHeader);this.isDisplay = isDisplay;this.index = index;}private void defaultTabSize(List<TableHeaderDTO> childHeader) {int size = 0;if (CollectionUtils.isEmpty(childHeader)) {this.tabSize = 1;return;}for (TableHeaderDTO table : childHeader) {if (CollectionUtils.isNotEmpty(table.getChildHeader())) {size = size + this.getChidlSize(table.getChildHeader());} else {size++;}}this.tabSize = size;}private int getChidlSize(List<TableHeaderDTO> childHeader){int size = 0;for (TableHeaderDTO table : childHeader) {if (CollectionUtils.isNotEmpty(table.getChildHeader())) {size = size + this.getChidlSize(table.getChildHeader());} else {size++;}}return size;}//获取信访工作情况二级表头待合并的行public int getTwoHeadMergeTr(){if(CollectionUtils.isEmpty(this.childHeader))return 0;if(this.childHeader.stream().anyMatch(c-> StringUtils.isBlank(c.getTitle()))){return 2;}return 0;}public void resetSize() {this.childSize = childHeader.size();this.defaultTabSize(childHeader);}public TableHeaderDTO updateChildHeader(List<TableHeaderDTO> childHeader) {this.childHeader = childHeader;return this;}public void addChildHeader(TableHeaderDTO childHeader){if (this.childHeader == null) this.childHeader = new ArrayList<>();this.childHeader.add(childHeader);}
}

数据格式

@ApiModel(value="TableDataDTO ",description="统计数据行DTO")
@JsonIgnoreProperties(ignoreUnknown=true)
public class TableDataDTO {@ApiModelProperty(value = "承办单位id")private String cbdwId;@ApiModelProperty(value = "承办单位")private String cbdw;@ApiModelProperty(value = "是否有数据")private boolean hasData=true;@ApiModelProperty(value = "考核指标对应的数据 key：考核指标id value：对应数据")private Map<String,TotalData> totalDataMap = new LinkedHashMap<>();@ApiModelProperty(value = "组织归类排序号")private int orderNo;@ApiModelProperty(value = "失分")private String sf;public String getSf() {return sf;}public void setSf(String sf) {this.sf = sf;}public String getCbdwId() {return cbdwId;}public void setCbdwId(String cbdwId) {this.cbdwId = cbdwId;}public String getCbdw() {return cbdw;}public void setCbdw(String cbdw) {this.cbdw = cbdw;}public boolean isHasData() {return hasData;}public void setHasData(boolean hasData) {this.hasData = hasData;}public Map<String, TotalData> getTotalDataMap() {return totalDataMap;}public void setTotalDataMap(Map<String, TotalData> totalDataMap) {this.totalDataMap = totalDataMap;}public void addTotalData(String key, TotalData totalData) {this.totalDataMap.put(key, totalData);}public int getOrderNo() {return orderNo;}public void setOrderNo(int orderNo) {this.orderNo = orderNo;}public static class TotalData{private String zb;//指标private String df;//得分private String khzs;//纳入考核总数public String getZb() {return zb;}public void setZb(String zb) {this.zb = zb;}public String getDf() {return df;}public void setDf(String df) {this.df = df;}public String getKhzs() {return khzs;}public void setKhzs(String khzs) {this.khzs = khzs;}public TotalData() {}public TotalData(String zb, String df) {this.zb = zb;this.df = df;}public TotalData(String zb, String df, String khzs) {this.zb = zb;this.df = df;this.khzs = khzs;}}public TableDataDTO() {}public TableDataDTO(int orderNo) {this.orderNo = orderNo;}public TableDataDTO(String cbdw, Map<String, TotalData> totalDataMap) {this.cbdw = cbdw;this.totalDataMap = totalDataMap;}/*public double getZf() {//总分为所有考核指标里的得分相加BigDecimal num = this.getBigDecimalZf();return num.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();}*//*private BigDecimal getBigDecimalZf() {//总分为所有考核指标里的得分相加BigDecimal num = new BigDecimal("0");for (Map.Entry<String, TotalData> entry : totalDataMap.entrySet()) {if (StringUtils.isBlank(entry.getValue().getDf()) || "--".equals(entry.getValue().getDf())|| Arrays.asList("是","否","解锁","已解锁").contains(entry.getValue().getDf())) {continue;}num = num.add(new BigDecimal(entry.getValue().getDf()));}return num.setScale(3, BigDecimal.ROUND_HALF_UP);}*//*public String getZfStr() {//总分为所有考核指标里的得分相加BigDecimal num = this.getBigDecimalZf();return num.setScale(3, BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString();}*/// 返回所有考核指标idpublic Set<String> getTargetIdKey() {if (totalDataMap == null) {return new LinkedHashSet<>();}return new LinkedHashSet<>(totalDataMap.keySet());}}

返回前端格式

@ApiModel(value = "TableTotalDTO",description = "统计DTO")
@JsonIgnoreProperties(ignoreUnknown = true)
public class TableTotalDTO {@ApiModelProperty(value = "对应数据类型 总表：total 详表：accept/finish/eval/leapfrogVisit/repeated")//XysdBusinessConstants.ASSESS_EFFICIENCY_TYPE_private String type;@ApiModelProperty(value = "详表中考核指标类型id")private String indexId;@ApiModelProperty(value = "表头数据")private List<TableHeaderDTO> headers = new ArrayList<>();@ApiModelProperty(value = "考核总表")private List<TableDataDTO> total = new ArrayList<>();@ApiModelProperty(value = "考核总表 - 根据组织归类分组")private Map<String,List<TableDataDTO>> ptvNameToTotal = new LinkedHashMap<>();@ApiModelProperty(value = "考核详表")private List<Map<String,String>> data = new ArrayList<>();@ApiModelProperty(value = "考核详表 - 根据组织归类分组")private Map<String,List<Map<String,String>>> ptvNameToData = new LinkedHashMap<>();@ApiModelProperty(value = "表头标题")private String title;@ApiModelProperty(value = "其他数据")private OtherData otherData = new OtherData();public String getType() {return type;}public void setType(String type) {this.type = type;}public String getIndexId() {return indexId;}public void setIndexId(String indexId) {this.indexId = indexId;}public List<TableHeaderDTO> getHeaders() {return headers;}public void setHeaders(List<TableHeaderDTO> headers) {this.headers = headers;}public List<TableDataDTO> getTotal() {return total;}public void setTotal(List<TableDataDTO> total) {this.total = total;}public List<Map<String, String>> getData() {return data;}public void setData(List<Map<String, String>> data) {this.data = data;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public Map<String, List<TableDataDTO>> getPtvNameToTotal() {return ptvNameToTotal;}public void setPtvNameToTotal(Map<String, List<TableDataDTO>> ptvNameToTotal) {this.ptvNameToTotal = ptvNameToTotal;}public Map<String, List<Map<String, String>>> getPtvNameToData() {return ptvNameToData;}public void setPtvNameToData(Map<String, List<Map<String, String>>> ptvNameToData) {this.ptvNameToData = ptvNameToData;}public OtherData getOtherData() {return otherData;}public void setOtherData(OtherData otherData) {this.otherData = otherData;}public TableTotalDTO() {}public TableTotalDTO(String type, String indexId, List<TableHeaderDTO> headers, List<Map<String,String>> data,Map<String,List<Map<String,String>>> ptvNameToData) {this.type = type;this.indexId = indexId;this.headers = headers;this.data = data;this.ptvNameToData = ptvNameToData;}public TableTotalDTO(String type, List<TableHeaderDTO> headers, List<TableDataDTO> total,Map<String, List<TableDataDTO>> ptvNameToTotal) {this.type = type;this.headers = headers;this.total = total;this.ptvNameToTotal = ptvNameToTotal;}// 生成总表时，如果数据里没有对应的考核指标，则删除该表头private void removeExtraHeader(List<TableHeaderDTO> headers) {List<String> targetIds = this.total.stream().flatMap(x -> x.getTargetIdKey().stream()).distinct().collect(Collectors.toList());//        BigDecimal zfScore = new BigDecimal(0);//总分BigDecimal scScore = new BigDecimal(0);//要删除的考核项目分数for (TableHeaderDTO header : headers) {if ("zf".equals(header.getCode())) {/*List<String> content = CalcValueService.getBracketContent(header.getTitle());if (CollectionUtils.isNotEmpty(content)) {String score = content.get(content.size() - 1).replace("分","").replace("共", "");zfScore = new BigDecimal(score);}*/continue;}for (TableHeaderDTO childHeader : header.getChildHeader()) {if (!targetIds.contains(childHeader.getKey())) {for (TableHeaderDTO subHeader : childHeader.getChildHeader()) {/*List<String> content = CalcValueService.getBracketContent(childHeader.getTitle());if (CollectionUtils.isNotEmpty(content)) {String score = content.get(content.size() - 1).replace("分","");scScore = scScore.add(new BigDecimal(score));}*/}}}//header.getChildHeader().removeIf(childHeader -> !targetIds.contains(childHeader.getKey()));//重新设置下级数量//header.resetSize();}// 将总分表头重新赋值/*zfScore = zfScore.subtract(scScore);TableHeaderDTO tableHeader = headers.get(headers.size() - 1);tableHeader.setTitle("总分(共" + zfScore + "分)");*/// 删除没有子表头的数据headers.removeIf(header -> CollectionUtils.isEmpty(header.getChildHeader()) && StringUtils.isBlank(header.getCode()));}// 获取实际拥有数据的表头public List<TableHeaderDTO> getRealHeaders() {if (!"total".equals(this.type) || CollectionUtils.isEmpty(this.headers) || CollectionUtils.isEmpty(this.total)) {return new ArrayList<>();}List<TableHeaderDTO> realHeaders = new ArrayList<>(this.headers);if (CollectionUtils.isEmpty(realHeaders)) {return new ArrayList<>();}this.removeExtraHeader(realHeaders);return realHeaders;}// 获取当前表格总共列数 导出时需要用到public int getAllTdSize() {if ("total".equals(this.type)) {return this.getRealHeaders().stream().mapToInt(TableHeaderDTO::getTabSize).sum();} else {return this.headers.stream().mapToInt(TableHeaderDTO::getTabSize).sum();}}// 获取当前表格总共列数 导出时需要用到public int getCmpAllTdSize() {if(CollectionUtils.isEmpty(this.headers))return 0;List<TableHeaderDTO> childs = this.headers.stream().flatMap(s->s.getChildHeader().stream()).collect(Collectors.toList());int tdSize = 0;int i = 0;while (true){if(CollectionUtils.isEmpty(childs)){break;}tdSize = tdSize+childs.stream().filter(s->CollectionUtils.isEmpty(s.getChildHeader())).mapToInt(TableHeaderDTO::getTabSize).sum();childs = childs.stream().flatMap(s->s.getChildHeader().stream()).collect(Collectors.toList());i++;if(i>100)break;}return tdSize;}   
}

Controller接口

@ApiOperation(value = "统计 效能、统计的数据", httpMethod = "POST")@RequestMapping(value = "/table/export", method = RequestMethod.POST)public void exportTableInfo(@RequestBody Map<String, Object> parameters,HttpServletRequest req,HttpServletResponse resp) {ApiResultDTO<String> result = RestAPITemplate.restapi(new IMyLogic<String>() {@Overridepublic String logic()throws IOException {OperateInfo operateInfo = new AccessTokenUserAssembler().getOperateInfoFromReq(req);parameters.put("statOrgId",operateInfo.obtainOperateOrgId());parameters.put("transparent_不显示总量为0统计行",parameters.get("totalIsZero")+"");parameters.put("transparent_不显示统计值0",parameters.get("valueIsZero")+"");TableTotalDTO totalDTO = statService.statsTableInfo(parameters, operateInfo.getOperator());Map<String, List<TableDataDTO>> ptvNameToTotal = totalDTO.getPtvNameToTotal();String fileName= Utils.getUUID("");try {boolean proportion = Boolean.TRUE.equals(parameters.get("proportion"));String period = (String) parameters.get("period");int rowspan = 1;if ("bqtqtb".equals(period)) rowspan = 3;if ("bqsqhb".equals(period)) rowspan = 3;if ("bqtqtbsqhb".equals(period)) rowspan = 5;if (proportion) rowspan = rowspan + 1;String totalC = null;String belongProjType = null;String templRelativePath = null;String statName = (String) parameters.get("statName");templRelativePath = "xmlTemplate/export_belongPoint_query.xml";fileName = "问题点位统计导出";fileName = fileName + ".xlsx";resp.setHeader("content-disposition", "attachment;filename=\"" + new String(Utils.toUtf8String(fileName).getBytes("gb2312"),"ISO8859-1") + "\"");ExportExcelUtils.exportExcelByTmplRelativePath(templRelativePath,"excelTemplate/default.xlsx", resp.getOutputStream(), fileName,"result",ptvNameToTotal,"totalC",totalC,"rowspan",rowspan);if(fileName.endsWith(".xls")) {resp.setCharacterEncoding("UTF-8");resp.setContentType("application/vnd.ms-excel");}else {resp.setCharacterEncoding("UTF-8");resp.setContentType("application/octet-stream");}} catch (Exception e) {e.printStackTrace();throw new RuntimeException("导出文件出错");} finally {}return null;}});if (!ApiResultDTO.STATUS_SUCCESS.equals(result.getStatus())) {resp.setCharacterEncoding("UTF-8");resp.setContentType("application/vnd.ms-excel;charset=UTF-8");resp.setHeader("Content-Transfer-Encoding", "binary");resp.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");resp.setHeader("Pragma", "public");resp.setHeader("Content-Disposition", "attachment;");}}

public TableTotalDTO statsTableInfo(Map<String, Object> parameters, AccessTokenUser user) {Map<String, Object> datas = baseStatService.query(parameters, user);StatAssembler assembler = new StatAssembler(beansFactoryService);TableTotalDTO statDatas = assembler.buildStatData(datas,parameters);return statDatas;}

List<Map<String,Object>> data = (List<Map<String, Object>>) datas.get("首开集团问题点位分组统计数据集");
//处理问题点位统计private TableTotalDTO handleBelongPoint(List<Map<String,Object>> rows,Map<String,Object> parameters){List<TableHeaderDTO> headers = this.buildBelongPointHeaders();List<TableDataDTO> datas = this.buildBelongPointDatas(rows,parameters);Map<String,List<TableDataDTO>> belongPointToDatas = new LinkedHashMap<>();for (TableDataDTO data : datas) {if (!belongPointToDatas.containsKey(data.getCbdw())){belongPointToDatas.put(data.getCbdw(),new ArrayList<>());}belongPointToDatas.get(data.getCbdw()).add(data);}TableTotalDTO dto = new TableTotalDTO("问题点位统计",headers,datas,belongPointToDatas);return dto;}//处理问题点位表头private List<TableHeaderDTO> buildBelongPointHeaders(){List<TableHeaderDTO> headers = new ArrayList<>();TableHeaderDTO paim = new TableHeaderDTO("排名", "df", "totalDataMap.paim", new ArrayList<>());headers.add(paim);TableHeaderDTO wtdw = new TableHeaderDTO("问题点位", "df", "totalDataMap.wtdw", new ArrayList<>());headers.add(wtdw);TableHeaderDTO jd = new TableHeaderDTO("阶段", "df", "totalDataMap.jd", new ArrayList<>());headers.add(jd);List<TableHeaderDTO> zl_child = new ArrayList<>();zl_child.add(new TableHeaderDTO("批(件)次", "df", "totalDataMap.zl_pjc", new ArrayList<>()));zl_child.add(new TableHeaderDTO("人次", "df", "totalDataMap.zl_rc", new ArrayList<>()));TableHeaderDTO zl = new TableHeaderDTO("总量", "df", "totalDataMap.zl", zl_child);headers.add(zl);List<TableHeaderDTO> gtxf_child = new ArrayList<>();gtxf_child.add(new TableHeaderDTO("批(件)次", "df", "totalDataMap.gtxf_pjc", new ArrayList<>()));gtxf_child.add(new TableHeaderDTO("人次", "df", "totalDataMap.gtxf_rc", new ArrayList<>()));TableHeaderDTO gtxf = new TableHeaderDTO("个体信访", "df", "totalDataMap.gtxf", gtxf_child);headers.add(gtxf);List<TableHeaderDTO> qtxf_child = new ArrayList<>();qtxf_child.add(new TableHeaderDTO("批(件)次", "df", "totalDataMap.qtxf_pjc", new ArrayList<>()));qtxf_child.add(new TableHeaderDTO("人次", "df", "totalDataMap.qtxf_rc", new ArrayList<>()));TableHeaderDTO qtxf = new TableHeaderDTO("群体信访", "df", "totalDataMap.qtxf", qtxf_child);headers.add(qtxf);List<TableHeaderDTO> ccxf_gtxf_child = new ArrayList<>();ccxf_gtxf_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.ccxf_gtxf_pjc",new ArrayList<>()));ccxf_gtxf_child.add(new TableHeaderDTO("人次","df","totalDataMap.ccxf_gtxf_rc",new ArrayList<>()));List<TableHeaderDTO> ccxf_qtxf_child = new ArrayList<>();ccxf_qtxf_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.ccxf_qtxf_pjc",new ArrayList<>()));ccxf_qtxf_child.add(new TableHeaderDTO("人次","df","totalDataMap.ccxf_qtxf_rc",new ArrayList<>()));List<TableHeaderDTO> ccxf_xj_child = new ArrayList<>();ccxf_xj_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.ccxf_xj_pjc",new ArrayList<>()));ccxf_xj_child.add(new TableHeaderDTO("人次","df","totalDataMap.ccxf_xj_rc",new ArrayList<>()));List<TableHeaderDTO> ccxf_child = new ArrayList<>();ccxf_child.add(new TableHeaderDTO("个体信访","df","totalDataMap.ccxf_gtxf",ccxf_gtxf_child));ccxf_child.add(new TableHeaderDTO("群体信访","df","totalDataMap.ccxf_qtxf",ccxf_qtxf_child));ccxf_child.add(new TableHeaderDTO("小计","df","totalDataMap.ccxf_xj",ccxf_xj_child));TableHeaderDTO ccxf = new TableHeaderDTO("初次信访","df","totalDataMap.ccxf",ccxf_child);headers.add(ccxf);List<TableHeaderDTO> cfxf_gtxf_child = new ArrayList<>();cfxf_gtxf_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.cfxf_gtxf_pjc",new ArrayList<>()));cfxf_gtxf_child.add(new TableHeaderDTO("人次","df","totalDataMap.cfxf_gtxf_rc",new ArrayList<>()));List<TableHeaderDTO> cfxf_qtxf_child = new ArrayList<>();cfxf_qtxf_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.cfxf_qtxf_pjc",new ArrayList<>()));cfxf_qtxf_child.add(new TableHeaderDTO("人次","df","totalDataMap.cfxf_qtxf_rc",new ArrayList<>()));List<TableHeaderDTO> cfxf_xj_child = new ArrayList<>();cfxf_xj_child.add(new TableHeaderDTO("批(件)次","df","totalDataMap.cfxf_xj_pjc",new ArrayList<>()));cfxf_xj_child.add(new TableHeaderDTO("人次","df","totalDataMap.cfxf_xj_rc",new ArrayList<>()));List<TableHeaderDTO> cfxf_child = new ArrayList<>();cfxf_child.add(new TableHeaderDTO("个体信访","df","totalDataMap.cfxf_gtxf",cfxf_gtxf_child));cfxf_child.add(new TableHeaderDTO("群体信访","df","totalDataMap.cfxf_qtxf",cfxf_qtxf_child));cfxf_child.add(new TableHeaderDTO("小计","df","totalDataMap.cfxf_xj",cfxf_xj_child));TableHeaderDTO cfxf = new TableHeaderDTO("重复信访","df","totalDataMap.cfxf",cfxf_child);headers.add(cfxf);return headers;}//处理问题点位数据private List<TableDataDTO> buildBelongPointDatas(List<Map<String,Object>> rows,Map<String,Object> parameters){if (CollectionUtils.isEmpty(rows)) return new ArrayList<>();Map<String,Boolean> handled = new HashMap<>();List<TableDataDTO> datas = new ArrayList<>();/*int total = 0;if (CollectionUtils.isNotEmpty(rows) && rows.get(0).get("总数") != null){total = (int)rows.get(0).get("总数");}int pageNo = parameters.get("pageNo") == null ?  1 : (Integer) parameters.get("pageNo");int pageSize = parameters.get("pageSize") == null ?  20 : (Integer) parameters.get("pageSize");int i = (pageNo * pageSize) - pageSize + 1;*/String url = "/xf/efficiencyDetail?";List<String> params = new ArrayList<>();for (Map.Entry<String, Object> entry : parameters.entrySet()) {params.add(entry.getKey() + "=" + entry.getValue());}url = url + Utils.collectionToString(params, "&");int orderNo = 1;for (Map<String, Object> row : rows) {String wtdwbm = (String) row.get("问题点位编码");String wtdw = (String) row.get("问题点位");String wtdwlx = (String) row.get("问题点位类型");String wtdwq = (String) row.get("问题点位区");String wtdwjz = (String) row.get("问题点位街道");System.out.println("wtdwq="+wtdwq+",wtdwjz="+wtdwjz+",wtdw="+wtdw+",wtdwlx="+wtdwlx);if ("观音寺街道".equals(wtdwjz)){logger.info("===");}if ("兴政西里-北区".equals(wtdw)){logger.info("=====");}if (!handled.containsKey(wtdwq+wtdwjz) && "street".equals(wtdwlx)){handled.put(wtdwq+wtdwjz,true);orderNo = 1;}TableDataDTO data = new TableDataDTO(orderNo);data.setCbdwId(wtdwbm);data.setCbdw(wtdw);data.setSf(wtdwlx);data.addTotalData("paim",new TableDataDTO.TotalData("paim",orderNo+""));data.addTotalData("wtdw",new TableDataDTO.TotalData("wtdw",wtdw));data.addTotalData("jd",new TableDataDTO.TotalData("jd",(String)row.get("阶段")));data.addTotalData("zl_pjc",new TableDataDTO.TotalData("zl_pjc",(String) row.get("总量_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=总量&statNameY2=批(件)次"));data.addTotalData("zl_rc",new TableDataDTO.TotalData("zl_rc",(String) row.get("总量_人次")));data.addTotalData("gtxf_pjc",new TableDataDTO.TotalData("gtxf_pjc",(String) row.get("个体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=个体信访&statNameY2=批(件)次"));data.addTotalData("gtxf_rc",new TableDataDTO.TotalData("gtxf_rc",(String) row.get("群体信访_人次")));data.addTotalData("qtxf_pjc",new TableDataDTO.TotalData("qtxf_pjc",(String) row.get("群体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=群体信访&statNameY2=批(件)次"));data.addTotalData("qtxf_rc",new TableDataDTO.TotalData("qtxf_rc",(String) row.get("群体信访_人次")));data.addTotalData("ccxf_gtxf_pjc",new TableDataDTO.TotalData("ccxf_gtxf_pjc",(String) row.get("初次信访_个体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=初次信访&statNameY2=个体信访"));data.addTotalData("ccxf_gtxf_rc",new TableDataDTO.TotalData("ccxf_gtxf_rc",(String) row.get("初次信访_个体信访_人次")));data.addTotalData("ccxf_qtxf_pjc",new TableDataDTO.TotalData("ccxf_qtxf_pjc",(String) row.get("初次信访_群体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=初次信访&statNameY2=群体信访"));data.addTotalData("ccxf_qtxf_rc",new TableDataDTO.TotalData("ccxf_qtxf_rc",(String) row.get("初次信访_群体信访_人次")));data.addTotalData("ccxf_xj_pjc",new TableDataDTO.TotalData("ccxf_xj_pjc",(String) row.get("初次信访_小计_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=初次信访&statNameY2=小计"));data.addTotalData("ccxf_xj_rc",new TableDataDTO.TotalData("ccxf_xj_rc",(String) row.get("初次信访_小计_人次")));data.addTotalData("cfxf_gtxf_pjc",new TableDataDTO.TotalData("cfxf_gtxf_pjc",(String) row.get("重复信访_个体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=重复信访&statNameY2=个体信访"));data.addTotalData("cfxf_gtxf_rc",new TableDataDTO.TotalData("cfxf_gtxf_rc",(String) row.get("重复信访_个体信访_人次")));data.addTotalData("cfxf_qtxf_pjc",new TableDataDTO.TotalData("cfxf_qtxf_pjc",(String) row.get("重复信访_群体信访_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=重复信访&statNameY2=群体信访"));data.addTotalData("cfxf_qtxf_rc",new TableDataDTO.TotalData("cfxf_qtxf_rc",(String) row.get("重复信访_群体信访_人次")));data.addTotalData("cfxf_xj_pjc",new TableDataDTO.TotalData("cfxf_xj_pjc",(String) row.get("重复信访_小计_批(件)次"),url + "&statNameX="+wtdw+"&statNameY3="+wtdwq+"&statNameY=重复信访&statNameY2=小计"));data.addTotalData("cfxf_xj_rc",new TableDataDTO.TotalData("cfxf_xj_rc",(String) row.get("重复信访_小计_人次")));datas.add(data);if ("biotope".equals(wtdwlx)){orderNo++;}}return datas;}