Report-split sheet and workbook processing for multiple data
/ * *
* create a local report file
* @ data to be generated by param tarinList
, /
Private void createWorkbookInLocal (List tarinList) {
/ * *
* sheetcount: returns the maximum number of sheet in the current workbook, which is a multiple of MAX_SHEET or the maximum number of sheet, and automatically divides files when the MAX_SHEET is exceeded.
, /
Int sheetcount = 0
/ * *
* currentCount: used for sheet paging processing and workbook file splitting processing
, /
Int currentCount = 0
/ * *
* after aleardSheet:workbook is divided into files, the marked data that has been paged has been paged on the first page by default
, /
Map aleardSheet = new HashMap ()
AleardSheet.put (0,0)
/ * *
* when the sheetcount is less than the total number of sheet, and the number of the last sheet records is not equal to MAX_COUNT (otherwise, create an infinite loop), create a new workBook and divide the files.
, /
Int shouldworkCount= (tarinList.size () / MAX_COUNT) + ((tarinList.size ()% MAX_COUNT) > 0)
While (sheetcount
< shouldworkCount) { currentCount = sheetcount * MAX_COUNT; SXSSFWorkbook sworkbook = getNewWorkBook(); sheetcount = createWorkbookByPage(tarinList, currentCount, sworkbook, aleardSheet); System.out.println("生成的sheet:" + sheetcount); } } /** * 创建workBook，最大sheet数量是 MAX_SHEET * * @param tarinList * @param currentCount * @param sworkbook * @param map * @return */ private int createWorkbookByPage(List tarinList, int currentCount, SXSSFWorkbook sworkbook, Map map) { /** * 标记Sheet 号 */ int sheetNum = 0; /** * 标记rowNum 行号 */ int rowNum = 0; /** * 根据当前记录数判定某个sheet的rowNum行数据 */ if (currentCount != 0) { sheetNum = currentCount / MAX_COUNT; // 取莫，获得当前sheet页面标签 rowNum = currentCount % MAX_COUNT;// 取余，获取行标记 } /** * 当sheetNum达到最大值，并且不包含已经生成workbook时,生成workbook，返回当前sheetNum,进行下一个workbook的创建 */ if (sheetNum % MAX_SHEET == 0 && !map.containsKey(sheetNum)) { /** * 创建workbook，上传workbook并且保存url */ createWorkBookFile(sworkbook); map.put(sheetNum, sheetNum); return sheetNum; } try { CellStyle cellStyleDate = getCellStyleDateTime(sworkbook); CellStyle cellStyleString = getCellStyleString(sworkbook); Sheet sheet; /** * 首次进入获取第一个sheet，需要分页时，创建新的sheet */ if (sheetNum >0 & & sheetNum% MAX_SHEET! = 0) {
String sheetName = "Sheet" + (sheetNum% MAX_SHEET + 1)
Sheet = sworkbook.getSheet (sheetName)
If (sheet = = null) {
Sheet = sworkbook.createSheet (sheetName)
}
} else {
Sheet = sworkbook.getSheetAt (0)
}
/ * *
* set the title style
, /
CellStyle style = getTitleStyle (sworkbook)
Row targetRow = sheet.createRow (0)
/ * *
* create a title column
, /
CopyRowTitle (targetRow, style)
/ * *
* rowId: traverses all the data that has been inserted according to rowNum and sheetNum records, and obtains the uninserted data according to rowId
, /
For (int rowId = rowNum + MAX_COUNT * sheetNum; rowId
< tarinList.size(); rowId++) { /** * 当已经插入的数据超过最大数据时，进行分sheet处理 */ if (rowId >= MAX_COUNT * (sheetNum + 1)) {
CurrentCount = rowId
Return createWorkbookByPage (tarinList, currentCount, sworkbook, map)
}
TrainRecordSearchVO pis = tarinList.get (rowId)
Row newRow = sheet.createRow (rowId% MAX_COUNT + 1)
InsertCrouseDataToExcel (newRow, pis, cellStyleDate, cellStyleString)
/ * *
*
, /
If (rowId==tarinList.size ()-1) {
SheetNum+=1
}
}
} catch (Exception e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
CreateWorkBookFile (sworkbook)
Return sheetNum
}
Private void createWorkBookFile (SXSSFWorkbook sworkbook) {
ByteArrayOutputStream swapStream = new ByteArrayOutputStream ()
Try {
String fileExtName = name.substring (name.lastIndexOf (".")
String filePreName = name.substring (0, name.lastIndexOf ("."))
File uplDir = new File (filePrePath)
/ / determine whether the folder exists or not, then create the folder tree.
If (! uplDir.exists ()) {
UplDir.mkdirs ()
}
String filePath = filePrePath + filePreName + System.nanoTime () + fileExtName
FileOutputStream fout = new FileOutputStream (filePath)
Sworkbook.write (fout)
WorkbookFile.add (filePath)
} catch (FileNotFoundException e) {
Log.error ("File not found:", e)
} catch (IOException e) {
Log.error ("IO error:", e)
} finally {
Try {
SwapStream.close ()
} catch (IOException e) {
Log.error ("Stream cannot be closed:", e)
}
}
}
/ / set the header style
Private CellStyle getTitleStyle (SXSSFWorkbook sworkbook) {
/ / TODO Auto-generated method stub
CellStyle style = sworkbook.createCellStyle ()
Font ztFont = sworkbook.createFont ()
ZtFont.setColor (Font.COLOR_NORMAL); / / set the font
Style.setFont (ztFont)
Style.setFillPattern (XSSFCellStyle.SOLID_FOREGROUND); / / sets the foreground fill pattern
Style.setFillForegroundColor (IndexedColors.GREY_50_PERCENT.getIndex ()); / / set the color
Return style
}
/ / set the header
Private void copyRowTitle (Row targetRow, CellStyle style) {
/ / TODO Auto-generated method stub
For (int I = 0; I < RECORD_TITLE.length; iTunes +) {
Cell targetCell = targetRow.createCell (I)
TargetCell.setCellStyle (style)
TargetCell.setCellValue (RECORD_ Tilly [I])
}
}
/ / insert data
Private void insertCrouseDataToExcel (Row newRow, TrainRecordSearchVO pis, CellStyle cellStyleDate
CellStyle cellStyleString) {
/ / Auto-generated method stub
InsetParentData (newRow, pis, cellStyleDate, cellStyleString)
/ / Learning object name
Cell cell14 = newRow.createCell (14)
Cell14.setCellValue ("")
If (pis.getObjNameCn ()! = null) {
Cell14.setCellValue (pis.getObjNameCn ())
}
}
/ / get the template file
Private SXSSFWorkbook getNewWorkBook () {
/ / TODO Auto-generated method stub
InputStream inputStrem = this.getClass () .getResourceAsStream (RPT_TMPL_DIR_PATH)
XSSFWorkbook workbook = null
Try {
Workbook = new XSSFWorkbook (inputStrem)
} catch (IOException e) {
/ / TODO Auto-generated catch block
E.printStackTrace ()
}
Return new SXSSFWorkbook (workbook)
}
