Excel和Word数据导入导出通用程序今天已经完成了。把excel的部分代码贴过来吧。
public
File export(Class dtoClass, Object[] dtos,
int
recordCountPerSheet) {
Assert.notNull(dtoClass);
Assert.isTrue(dtos instanceof Dto[]);
POIFSFileSystem fis = null ;
try {
// 获得excel模板
fis = new POIFSFileSystem(loadExcleTemplate(dtoClass));
} catch (IOException e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
throw new ExportException(e);
}
OutputStream fileOut = null ;
File excelOutput = null ;
try {
HSSFWorkbook wb = new HSSFWorkbook(fis);
excelOutput = this .generateFile();
fileOut =
new BufferedOutputStream( new FileOutputStream(excelOutput), Constants.DEFAULT_BUFFER_SIZE);
if (dtos != null ) {
ExportDto exportDto = (ExportDto) mappingCache.get(dtoClass.getName());
int dtoLength = dtos.length;
// 根据总的记录条数和每个sheet输出的条数计算出sheet个数
int sheetCount = caculateSheetCount(dtoLength, recordCountPerSheet);
// 获得第一页的模板
HSSFSheet templateSheet = wb.getSheetAt( 0 );
int dtoIndexStart = 0 ;
int dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
for ( int sheetIndex = 0 ; sheetIndex < sheetCount; sheetIndex ++ ) {
// 根据模板的sheet复制一个新的sheet
HSSFSheet newSheet = createHSSFSheet(sheetIndex, wb, templateSheet, exportDto);
// 对新建的sheet填充数据
for ( int dtoIndex = dtoIndexStart; dtoIndex < dtoIndexEnd; dtoIndex ++ ) {
processRow(exportDto.getStartRow() - 1 + dtoIndex % recordCountPerSheet, exportDto, (Dto)dtos[dtoIndex], newSheet);
}
dtoIndexStart = dtoIndexEnd;
dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
}
}
wb.removeSheetAt( 0 ); // 删除拷贝模板的第一页
wb.write(fileOut);
fileOut.flush();
return excelOutput;
} catch (Throwable e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
if (excelOutput != null && excelOutput.exists()) {
excelOutput.delete();
}
throw new ExportException(e);
} finally {
if (fileOut != null ) {
try {
fileOut.close();
} catch (IOException e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
}
}
}
}
Assert.notNull(dtoClass);
Assert.isTrue(dtos instanceof Dto[]);
POIFSFileSystem fis = null ;
try {
// 获得excel模板
fis = new POIFSFileSystem(loadExcleTemplate(dtoClass));
} catch (IOException e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
throw new ExportException(e);
}
OutputStream fileOut = null ;
File excelOutput = null ;
try {
HSSFWorkbook wb = new HSSFWorkbook(fis);
excelOutput = this .generateFile();
fileOut =
new BufferedOutputStream( new FileOutputStream(excelOutput), Constants.DEFAULT_BUFFER_SIZE);
if (dtos != null ) {
ExportDto exportDto = (ExportDto) mappingCache.get(dtoClass.getName());
int dtoLength = dtos.length;
// 根据总的记录条数和每个sheet输出的条数计算出sheet个数
int sheetCount = caculateSheetCount(dtoLength, recordCountPerSheet);
// 获得第一页的模板
HSSFSheet templateSheet = wb.getSheetAt( 0 );
int dtoIndexStart = 0 ;
int dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
for ( int sheetIndex = 0 ; sheetIndex < sheetCount; sheetIndex ++ ) {
// 根据模板的sheet复制一个新的sheet
HSSFSheet newSheet = createHSSFSheet(sheetIndex, wb, templateSheet, exportDto);
// 对新建的sheet填充数据
for ( int dtoIndex = dtoIndexStart; dtoIndex < dtoIndexEnd; dtoIndex ++ ) {
processRow(exportDto.getStartRow() - 1 + dtoIndex % recordCountPerSheet, exportDto, (Dto)dtos[dtoIndex], newSheet);
}
dtoIndexStart = dtoIndexEnd;
dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
}
}
wb.removeSheetAt( 0 ); // 删除拷贝模板的第一页
wb.write(fileOut);
fileOut.flush();
return excelOutput;
} catch (Throwable e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
if (excelOutput != null && excelOutput.exists()) {
excelOutput.delete();
}
throw new ExportException(e);
} finally {
if (fileOut != null ) {
try {
fileOut.close();
} catch (IOException e) {
if (logger.isErrorEnabled()) {
logger.error(e);
}
}
}
}
}
private
InputStream loadExcleTemplate(Class dtoClass)
throws
IOException {
Assert.notNull(templateCache);
Resource template = (Resource) templateCache.get(dtoClass.getName());
return template.getInputStream();
}
private HSSFSheet createHSSFSheet( int sheetIndex, HSSFWorkbook wb, HSSFSheet templateSheet, ExportDto exportDto) {
HSSFSheet newSheet = wb.createSheet();
int startRow = exportDto.getStartRow();
int columnCount = exportDto.getExportDtoFieldsCount() + exportDto.getStartColumn() - 1 ;
// 拷贝设置的开始行的上面所有行
for ( int i = 0 ; i < startRow; i ++ ) {
HSSFRow templateRow = templateSheet.getRow(i);
if (templateRow != null ) {
HSSFRow newRow = newSheet.createRow(i);
for ( int j = 0 ; j < columnCount; j ++ ) {
HSSFCell templateCell = templateRow.getCell(( short )j);
if (templateCell != null ) {
HSSFCell newCell = newRow.createCell(( short )j);
copyCell(templateCell, newCell);
}
}
}
}
return newSheet;
}
private void copyCell(HSSFCell srcCell, HSSFCell distCell) {
distCell.setCellStyle(srcCell.getCellStyle());
if (srcCell.getCellComment() != null ) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else {
}
}
Assert.notNull(templateCache);
Resource template = (Resource) templateCache.get(dtoClass.getName());
return template.getInputStream();
}
private HSSFSheet createHSSFSheet( int sheetIndex, HSSFWorkbook wb, HSSFSheet templateSheet, ExportDto exportDto) {
HSSFSheet newSheet = wb.createSheet();
int startRow = exportDto.getStartRow();
int columnCount = exportDto.getExportDtoFieldsCount() + exportDto.getStartColumn() - 1 ;
// 拷贝设置的开始行的上面所有行
for ( int i = 0 ; i < startRow; i ++ ) {
HSSFRow templateRow = templateSheet.getRow(i);
if (templateRow != null ) {
HSSFRow newRow = newSheet.createRow(i);
for ( int j = 0 ; j < columnCount; j ++ ) {
HSSFCell templateCell = templateRow.getCell(( short )j);
if (templateCell != null ) {
HSSFCell newCell = newRow.createCell(( short )j);
copyCell(templateCell, newCell);
}
}
}
}
return newSheet;
}
private void copyCell(HSSFCell srcCell, HSSFCell distCell) {
distCell.setCellStyle(srcCell.getCellStyle());
if (srcCell.getCellComment() != null ) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else {
}
}