报表-对于多数据进行分sheet以及分workbook处理
/**
成都创新互联作为成都网站建设公司，专注网站建设公司、网站设计，有关企业网站设计方案、改版、费用等问题，行业涉及LED显示屏等多个领域，已为上千家企业服务，得到了客户的尊重与认可。
* 创建本地报表文件
* @param tarinList 需要生成的数据
*/
private void createWorkbookInLocal(List
/**
* sheetcount:返回当前workbook中最大sheet数，是MAX_SHEET的倍数或者是最大sheet数，当超过MAX_SHEET时回自动分文件处理
*/
int sheetcount = 0;
/**
* currentCount：用于sheet分页处理以及workbook分文件处理
*/
int currentCount = 0;
/**
* aleardSheet:workbook分文件后，标记已经分页过的数据 默认已经分页第一页
*/
Map
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
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);
}
网站栏目：报表-对于多数据进行分sheet以及分workbook处理
文章网址：http://pwwzsj.com/article/pdedcs.html
