报表-对于多数据进行 分sheet以及 分workbook处理
/**
* 創(chuàng)建本地報(bào)表文件
* @param tarinList?需要生成的數(shù)據(jù)
*/
private void createWorkbookInLocal(List<TrainRecordSearchVO> tarinList) {
/**
* sheetcount:返回當(dāng)前workbook中最大sheet數(shù)，是MAX_SHEET的倍數(shù)或者是最大sheet數(shù)，當(dāng)超過MAX_SHEET時(shí)回自動(dòng)分文件處理
*/
int sheetcount = 0;
/**
* currentCount：用于sheet分頁處理以及workbook分文件處理
*/
int currentCount = 0;
/**
* aleardSheet:workbook分文件后，標(biāo)記已經(jīng)分頁過的數(shù)據(jù) 默認(rèn)已經(jīng)分頁第一頁
*/
Map<Integer, Integer> aleardSheet = new HashMap<Integer, Integer>();
aleardSheet.put(0, 0);
/**
* 當(dāng)sheetcount小于總sheet數(shù)量，并且最后一個(gè)sheet記錄數(shù)不等于 MAX_COUNT 時(shí)（否則回?zé)o限循環(huán)創(chuàng)建），新建workBook，進(jìn)行分文件
*/
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);
}
}
/**
* 創(chuàng)建workBook，最大sheet數(shù)量是 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) {
/**
* 標(biāo)記Sheet 號(hào)
*/
int sheetNum = 0;
/**
* 標(biāo)記rowNum 行號(hào)
*/
int rowNum = 0;
/**
* 根據(jù)當(dāng)前記錄數(shù)判定某個(gè)sheet的rowNum行數(shù)據(jù)
*/
if (currentCount != 0) {
sheetNum = currentCount / MAX_COUNT; // 取莫，獲得當(dāng)前sheet頁面標(biāo)簽
rowNum = currentCount % MAX_COUNT;// 取余，獲取行標(biāo)記
}
/**
* 當(dāng)sheetNum達(dá)到最大值，并且不包含已經(jīng)生成workbook時(shí),生成workbook，返回當(dāng)前sheetNum,進(jìn)行下一個(gè)workbook的創(chuàng)建
*/
if (sheetNum % MAX_SHEET == 0 && !map.containsKey(sheetNum)) {
/**
* 創(chuàng)建workbook，上傳workbook并且保存url
*/
createWorkBookFile(sworkbook);
map.put(sheetNum, sheetNum);
return sheetNum;
}
try {
CellStyle cellStyleDate = getCellStyleDateTime(sworkbook);
CellStyle cellStyleString = getCellStyleString(sworkbook);
Sheet sheet;
/**
* 首次進(jìn)入獲取第一個(gè)sheet，需要分頁時(shí)，創(chuàng)建新的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);
}
/**
* 設(shè)置標(biāo)題樣式
*/
CellStyle style = getTitleStyle(sworkbook);
Row targetRow = sheet.createRow(0);
/**
* 創(chuàng)建標(biāo)題列
*/
copyRowTitle(targetRow, style);
/**
* rowId：根據(jù)rowNum和sheetNum記錄已經(jīng)插入的數(shù)據(jù) 遍歷所有數(shù)據(jù)，根據(jù)rowId獲取未插入的數(shù)據(jù)
*/
for (int rowId = rowNum + MAX_COUNT * sheetNum; rowId < tarinList.size(); rowId++) {
/**
* 當(dāng)已經(jīng)插入的數(shù)據(jù)超過最大數(shù)據(jù)時(shí)，進(jìn)行分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);
// 判斷文件夾是否存在 不存在則創(chuàng)建該文件夾樹
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);
}
}
}
//設(shè)置頭標(biāo)題樣式
private CellStyle getTitleStyle(SXSSFWorkbook sworkbook) {
// TODO Auto-generated method stub
CellStyle style = sworkbook.createCellStyle();
Font ztFont = sworkbook.createFont();
ztFont.setColor(Font.COLOR_NORMAL); // 將字體設(shè)置
style.setFont(ztFont);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 設(shè)置前景填充樣式
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());// 設(shè)置顏色
return style;
}
//設(shè)置頭標(biāo)題
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]);
}
}
//插入數(shù)據(jù)
private void insertCrouseDataToExcel(Row newRow, TrainRecordSearchVO pis, CellStyle cellStyleDate,
CellStyle cellStyleString) {
// Auto-generated method stub
insetParentData(newRow, pis, cellStyleDate, cellStyleString);
// 學(xué)習(xí)對(duì)象名稱
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);
}
轉(zhuǎn)載于:https://blog.51cto.com/tianjian/2047942
總結(jié)
以上是生活随笔為你收集整理的报表-对于多数据进行 分sheet以及 分workbook处理的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Android Studio Emula
- 下一篇： redis4.0之基于LFU的热点key