報表-對于多數據進行 分sheet以及 分workbook處理

/**

* 創建本地報表文件

* @param tarinList 需要生成的數據

*/

private void createWorkbookInLocal(List<TrainRecordSearchVO> tarinList) {

/**

* sheetcount:返回當前workbook中最大sheet數，是MAX_SHEET的倍數或者是最大sheet數，當超過MAX_SHEET時回自動分文件處理

*/

int sheetcount = 0;

/**

* currentCount：用于sheet分頁處理以及workbook分文件處理

*/

int currentCount = 0;

/**

* aleardSheet:workbook分文件后，標記已經分頁過的數據 默認已經分頁第一頁

*/

Map<Integer, Integer> aleardSheet = new HashMap<Integer, Integer>();

aleardSheet.put(0, 0);

/**

* 當sheetcount小于總sheet數量，并且最后一個sheet記錄數不等于 MAX_COUNT 時（否則回無限循環創建），新建workBook，進行分文件

*/

int shouldworkCount=(tarinList.size() / MAX_COUNT)+((tarinList.size() % MAX_COUNT)>0?1: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<TrainRecordSearchVO> tarinList, int currentCount, SXSSFWorkbook sworkbook,

Map<Integer, Integer> 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);

}

/**

* 設置標題樣式

*/

CellStyle style = getTitleStyle(sworkbook);

Row targetRow = sheet.createRow(0);

/**

* 創建標題列

*/

copyRowTitle(targetRow, style);

/**

* rowId：根據rowNum和sheetNum記錄已經插入的數據 遍歷所有數據，根據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);

// 判斷文件夾是否存在 不存在則創建該文件夾樹

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);

}

}

}

//設置頭標題樣式

private CellStyle getTitleStyle(SXSSFWorkbook sworkbook) {

// TODO Auto-generated method stub

CellStyle style = sworkbook.createCellStyle();

Font ztFont = sworkbook.createFont();

ztFont.setColor(Font.COLOR_NORMAL); // 將字體設置

style.setFont(ztFont);

style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 設置前景填充樣式

style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());// 設置顏色

return style;

}

//設置頭標題

private void copyRowTitle(Row targetRow, CellStyle style) {

// TODO Auto-generated method stub

for (int i = 0; i < RECORD_TITLE.length; i++) {

Cell targetCell = targetRow.createCell(i);

targetCell.setCellStyle(style);

targetCell.setCellValue(RECORD_TITLE[i]);

}

}

//插入數據

private void insertCrouseDataToExcel(Row newRow, TrainRecordSearchVO pis, CellStyle cellStyleDate,

CellStyle cellStyleString) {

// Auto-generated method stub

insetParentData(newRow, pis, cellStyleDate, cellStyleString);

// 學習對象名稱

Cell cell14 = newRow.createCell(14);

cell14.setCellValue("");

if (pis.getObjNameCn() != null) {

cell14.setCellValue(pis.getObjNameCn());

}

｝

//獲取模板文件

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);

}