POISXSSFWorkbook讀取模板存在公式解決

1、POISXSSFWorkbook讀取模板存在公式解決kagceom.baoqilai.base.serviceorjtavo.Fiorjtavo.FileInputStreorjtavorjtavIterator;orjtavorjtavorotrg.poi.ss.usermodeorotrg.poi.ss.usermodeorotrg.poi.ss.usermodeorotrg.poi.ss.usermodeorotrg.poi.ss.usermodeorotrg.poi.xssf.streamorotrg.poi.xssf.streamorotrg.poi.xssf.usermoorcto

2、m.baoorctom.baoeptioorctom.baoi.scp.servorctom.baoi.scp.servorellStyle;ataFormat;ow;.SXSSFSheet.SXSSFWorkbook.XSSFWorkbook;BaseException;ort.ExcelExportSerort.ExcelExportStr模*板導(dǎo)出*authlorlblcilcasTsemplateExporitmSeprlveimceEnxIm模板地*址rotectSterdingtempAddress;模板結(jié)*果集rotectSterdingresult;ubliTcemplteEx

3、portServiceImpsup(e)r;ddress=tempAddrth.iressult=result;ortSerStringtempAddress,StringrOverridebliSXcSSFWorkbookexport(List<Map<Sttrihnrogw,BsaOsbejEexccte>p>tidoantaongstime=System.currentTrynewFilFei(lteemfpiAd=dress);FinlewInFipluetISntpreuatmStriesXSSnFeWworXkSbSoFoWkorwkboo=k(is獲取模板

4、中最后一行，用于判斷是否存在公式intlastRowNum=wb.getSheeastRowNum(Shwb.getSheetARowbaseRow0=sheet0.getRow(2stRowNum=wb.getSheetAt(RowNup<InntewgHear,shSMtarpifor(Iterator<Cell>it=baseRow0.cellIterator();iCellbaseCeif(baseCel.CELL_TYPE_FORMUStringcellFormulabaseCellellForut(baseCell.getColumnIFor取到公式后進(jìn)行刪除bo

5、Sheetsheet=workbook.getSheetAt(0);CellStylecontextstyle=workbook.crDataFormatdf=workbook.createDataFormacontextstyle.setDataFormat(df.getForartRow=lastRowNum;ori(nti=startRow;+startRowintrowNtartRow;SXSSFWorkbodSXSSFWbldsriokbRowrow=sheet.getRow(i);if(row=null)row=sheet.createRow(i);Map<String,Ob

6、ject>dataMap=data.get(rowNum);StringcolumNames=result;dataMap.put("serialNum",rowNum+1);for(intj=0;j<columNames.length;j+)Cellcell=row.getCell(j);if(cell=null)cell=row.createCell(j);System.out.println(cell.getColumnIndex();Objectval=dataMap.get(columNamesj);ExcelUtil.setCellValue(cel

7、l,val,contextstyle);if(gsMap.get(cell.getColumnIndex()!=null)StringcellFormula=gsMap.get(cell.getColumnIndex();Strings=cellFormula.replaceAll("(w)d","$1"+(i+1);cell.setCellFormula(s);cell.setCellType(Cell.CELL_TYPE_FORMULA);dataMap.clear();/清空內(nèi)存中緩存的行數(shù)if(i%500=0)(SXSSFSheet)sheet)

8、.flushRows();/數(shù)據(jù)清理data.clear();data=null;workbook.setForceFormulaRecalculation(true);/計(jì)算公式longetime=System.currentTimeMillis();System.out.println("處理寫入模板數(shù)據(jù)啟時(shí)："+(etime-stime)/1000);returnworkbook;catch(Exceptione)e.printStackTrace();returnnull;OverridepublicSXSSFWorkbookexportByStragegy(List<Map<String,Object>>data,ExcelExportStragystragegy)throwsBaseExceptionlongstime=System.