java 生成Excel，可根据模板格式生成 Excel打印专用 报表

package excelut.java.excel; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import excelut.java.excel.domain.Classes; import excelut.java.excel.domain.Column; import excelut.java.excel.domain.ExportEntity; import excelut.java.excel.domain.formula.ForEach; import excelut.java.excel.domain.formula.Formula; import excelut.java.excel.domain.formula.Status; import excelut.java.utils.LangUtils; import excelut.java.utils.OgnlParser; import excelut.java.utils.RefUtils; public class ExcelDataService { public ExcelDataService(ExportEntity exportEntity,OutputStream outputStream){ this.exportEntity=exportEntity; this.outputStream=outputStream; } private HSSFWorkbook workbook; private HSSFSheet activitySheet; private OutputStream outputStream; private ExportEntity exportEntity; private List<Classes> tempRowDatas; private Formula rootFormula; private int writeRowNum; private int sheetIndex=1; public void executeRead() throws Exception{ //File tempFile=this.exportEntity.getTempFile(); InputStream inputStream=exportEntity.getTempFileInputStream(); this.workbook = new HSSFWorkbook(inputStream); inputStream.close(); int sheetCount=workbook.getNumberOfSheets(); //sheetIndex=sheetCount; for (int i = 0; i < sheetCount; i++) { HSSFSheet sheet=workbook.getSheetAt(i); HSSFSheet tempSheet=workbook.cloneSheet(i); this.activitySheet=sheet; sheet=tempSheet; this.exportEntity.addFormula(null); int rowNum=sheet.getLastRowNum(); for (int r = 0; r <= rowNum; r++) { HSSFRow row=sheet.getRow(r); if(row!=null){ Classes classes=new Classes(); classes.setThisRow(row); this.parserFormula(classes,r); this.exportEntity.addClasses(classes); } } this.tempRowDatas=exportEntity.getClasses(); this.parserForEachs(); ///写入数据 } } public void executeWrite(Object data,String sheetName) throws Exception{ writeRowNum=0; sheetIndex++; this.activitySheet=workbook.cloneSheet(0); if(sheetName==null){ sheetName="Sheet0"+(sheetIndex-1); } workbook.setSheetName(sheetIndex, sheetName); List<ForEach> forEachs=this.exportEntity.getActivityFormula().getForEachs(); rootFormula =new Formula(); ForEach root=new ForEach(); root.setTagStartIndex(-1); root.setTagEndIndex(this.tempRowDatas.size()); root.getForEachs().addAll(forEachs); root.setValue("${rootData}"); root.setVar(this.exportEntity.getAttributeKey()); rootFormula.addForEach(root); this.addIndexFlag(rootFormula.getForEachs(),root); Map<String,Object> rootData=new HashMap<String,Object>(); Collection<Object> collection=new ArrayList<Object>(); collection.add(this.exportEntity.getDatas()); rootData.put("rootData", collection); this.writeData(rootFormula.getForEachs(),rootData); } public void flush() throws IOException{ workbook.removeSheetAt(0); workbook.removeSheetAt(0); workbook.write(outputStream); } public void writeData(List<ForEach> forEachs,Object data){ for (int f = 0; f < forEachs.size(); f++) { ForEach item=forEachs.get(f); int start=item.getTagStartIndex(); int end=item.getTagEndIndex(); //取出模板数据列 List<Classes> rows=this.getTempRowData(start, end,item); String ognl=item.getValue(); Object itemVal=null; Collection<Object> collection=new ArrayList<Object>(); if(data!=null){ itemVal=this.getValue(ognl, data); if(itemVal!=null){ if(itemVal instanceof Object[]||itemVal instanceof Collection){ collection=(Collection) itemVal; }else{ collection.add(itemVal); } } String[] ognls=this.getVaribles(ognl); } int index=0; for (Object subItemsVal:collection) { if(LangUtils.isEmpty(subItemsVal)){ continue; } Map<String, Object> mapData=new HashMap<String, Object>(); mapData.put(item.getVar(), subItemsVal); if(item.getStatus()!=null){ Status status=new Status(); status.setIndex(index); status.setCount(index+1); mapData.put(item.getStatus(), status); } for (int r = 0; r < rows.size(); r++) { Classes classesRow=rows.get(r); if(classesRow.getFormulaFlag()<1){ writeRowNum++; PoiUtils.copyRow(workbook,this.activitySheet,mapData,classesRow,writeRowNum); }else if(classesRow.getFormulaFlag()==1){ Formula formula=classesRow.getFormula(); Map<String, Object> subData=new HashMap<String, Object>(); subData.put(item.getVar(), subItemsVal); this.writeData(formula.getForEachs(),subData); } } index++; } } } public Object getValue(String ognl,Object data){ String[] ognls=this.getVaribles(ognl); for (int i = 0; i < ognls.length; i++) { OgnlParser ognlParser = new OgnlParser(data); Object rs= ognlParser.getValue(ognls[i]); return rs; } return null; } public String[] getVaribles(String str) { if (str == null || str.equals("")) return null; int i1 = 0, i2 = 0, i = 0; String[] _retStr = new String[str.length()/3]; String[] retStr; while ((i1 = str.indexOf("${", i2)) != -1) { i2 = str.indexOf("}", i1); _retStr[i++] = str.substring(i1 + 2, i2); } retStr = new String[i]; for (int j = 0; j < i; j++) { retStr[j] = _retStr[j]; } return retStr; } public List<Classes> getTempRowData(int start,int end,ForEach item){ List<Classes> rows=new ArrayList<Classes>(); List<Classes> items=this.tempRowDatas; for (int i = start+1; i<end&&i < items.size(); i++) { Classes it=items.get(i); if(it.getUnderForEach()==item){ rows.add(it); } } return rows; } public void parserFormula(Classes classes,int index){ String start="<ForEach"; String end="</ForEach"; StringBuffer sb=new StringBuffer(); HSSFRow thisRow=classes.getThisRow(); int columnNum=thisRow.getPhysicalNumberOfCells(); for (int c = 0; c < columnNum; c++) { HSSFCell cell=thisRow.getCell(c); if(cell!=null){ int[] indexs =getMergedRegionIndex(this.activitySheet,index, c); Column column=new Column(); column.setThisCell(cell); column.setCosIndex(indexs); classes.addColumn(column); String val=cell.toString(); sb.append(val); } } String str=sb.toString().trim(); if(str.startsWith(start)){ Formula formula=new Formula(); classes.setFormula(formula); ForEach forEach=formula.addForEach(null).setTagStartIndex(index); this.exportEntity.getActivityFormula().addForEach(forEach); //解析成属性值 String[] arrs=str.split(" "); for (int i = 0; i < arrs.length; i++) { String s=arrs[i]; String[] vs=s.split("="); if(vs.length==2){ String name=vs[0]; String val=vs[1]; if(val.length()>2){ val=val.substring(1,val.length()-1); } RefUtils.setFieldValue(forEach, name, val); } } if(str.endsWith(end)){ //列的遍历功能，缺省 System.out.println("列的循环"+forEach); } classes.setFormulaFlag(1); } if(str.startsWith(end)){ //寻找上一个组成闭合的函数 List<ForEach> forEachs=this.exportEntity.getActivityFormula().getForEachs(); for (int i=forEachs.size()-1; i>=0 ;i--) { ForEach forEach=forEachs.get(i); if("start".equals(forEach.getTagFlag())&&forEach.getTagEndIndex()==0){ forEach.setTagEndIndex(index); break; } } classes.setFormulaFlag(2); } }