/**

 * 创建本地报表文件

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

}