报表-对于多数据进行 分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);

}