本程序是一次输出为excel,所以当文件过大,就会有内存溢出,而导致合拼失败,开始使用poi读取,但是报一个block[0] removed的异常,查阅官方文档,说是excel07以下的版本和07版本格式不一样所致,结果使用了jxl
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JXLMergerWriteExcel {
private WritableCellFormat times;
private String createFilePath;//合成文件存放路径
private int beginMergerColumnIndex=0;//开始合并的列
private int endMerGerColumnIndex;//结束的合并的列
//private long headRowIndex;//列头所在位置
private int beginMergerRow=1;//开始合并的行标
private int pageCount=0;
private List<String> columns=null;
private String dirPath;
private int mergerRowBeginIndex=0;
public JXLMergerWriteExcel(String createFilePath,String path){
this.createFilePath = createFilePath;
this.dirPath=path;
}
public JXLMergerWriteExcel(String createFilePath,String path,int beginMergerRow){
this.createFilePath = createFilePath;
this.dirPath=path;
this.beginMergerRow=beginMergerRow;
}
public JXLMergerWriteExcel(String createFilePath,String path,int beginMergerColumnIndex,int endMerGerColumnIndex,int beginMergerRow){
this.createFilePath = createFilePath;
this.dirPath=path;
this.beginMergerColumnIndex=beginMergerColumnIndex;
this.endMerGerColumnIndex=endMerGerColumnIndex;
this.beginMergerRow=beginMergerRow;
}
/*******
* 写文件
* @throws IOException
* @throws WriteException
* @throws Exception
*/
public String mergerExcel() {
WritableWorkbook workbook=null;
try{
if(createFilePath==null){
return "请输入创建文件路径";
}
if(dirPath==null){
return "请输入被合并文件夹的路径";
}
File file = new File(createFilePath);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setLocale(new Locale("en", "EN"));
workbook = Workbook.createWorkbook(file, wbSettings);
workbook.createSheet("Report_"+pageCount, pageCount);
WritableSheet excelSheet = workbook.getSheet(pageCount);
this.beginMergerColumn(excelSheet);
workbook.write();
return "合并成功";
}catch(Exception e){
e.printStackTrace();
}finally{
if(workbook!=null){
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return null;
}
/*****
* 开始合并文件
* @param excelSheet
* @param dirPath
*/
private void beginMergerColumn(WritableSheet excelSheet ){
File srcFile = new File(dirPath);
boolean bFile = srcFile.exists();
if (!bFile || !srcFile.isDirectory() || !srcFile.canRead()) {
try {
srcFile.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
File[] file = srcFile.listFiles();
for(int i=0;i<file.length;i++){
String path= file[i].getAbsolutePath();
if(path.indexOf(".xls")>0){
if(columns==null){
this.createHeader(excelSheet, path);
}
System.out.println("正在读入第"+i+"个文件:"+path+"----请稍等");
try {
this.readSingleExcel(excelSheet,path);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
/*****
* 读取单个文件
* @param excelSheet
* @param singFilePath
*/
public void readSingleExcel(WritableSheet excelSheet,String singFilePath){
File inputWorkbook = new File(singFilePath);
Workbook w = null;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines
mergerRowBeginIndex=excelSheet.getRows();
for (int i = beginMergerRow; i < sheet.getRows()-1; i++) {
for (int j = beginMergerColumnIndex; j < endMerGerColumnIndex; j++) {
Cell cell = sheet.getCell(j, i);
String cell_value=cell.getContents();
this.addLabel(excelSheet, j, mergerRowBeginIndex, cell_value);
}
this.addLabel(excelSheet, excelSheet.getColumns()-1, mergerRowBeginIndex, inputWorkbook.getName());
mergerRowBeginIndex++;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(w!=null){
w.close();
}
}
}
/*****
* 创建表头
* @param excelSheet
* @param firstFilePath
*/
private void createHeader(WritableSheet excelSheet,String firstFilePath){
int column=0;
try{
this.readFirstFileGetHeaders(firstFilePath);
for(int i=beginMergerColumnIndex;i<endMerGerColumnIndex;i++){
this.addLabel(excelSheet, column++, 0, columns.get(i));
}
this.addLabel(excelSheet, excelSheet.getColumns()-1, 0, "来源文件名称");
}catch(Exception e){
e.printStackTrace();
}
}
/*****
* 读取单个文件获取文件的表头信息
* @param filePath
*/
private void readFirstFileGetHeaders(String filePath){
File inputWorkbook = new File(filePath);
Workbook w = null;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines
columns=new ArrayList<String>();
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
String cell_value=cell.getContents();
columns.add(cell_value);
}
endMerGerColumnIndex=sheet.getColumns();
break;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(w!=null){
w.close();
}
}
}
/*****
* 添加信息到excel中
* @param sheet
* @param column
* @param row
* @param s
* @throws WriteException
* @throws RowsExceededException
*/
private void addLabel(WritableSheet sheet, int column, int row, String s)
throws WriteException, RowsExceededException {
Label label;
// Lets create a times font
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
// Define the cell format
times = new WritableCellFormat(times10pt);
label = new Label(column, row, s, times);
sheet.addCell(label);
}
public static void main(String[] args) throws Exception {
// for(int i=17;i<29;i++){
// JXLWriteExcel mergerExcel = new JXLWriteExcel("d:/合并文件/merger_"+i+".xls","D:/scopus20111124/"+i,2);
// String inf=mergerExcel.mergerExcel();
// System.out.println("第"+i+"批次文件"+inf);
// }
for(int i=4;i<15;i++){
JXLMergerWriteExcel mergerExcel = new JXLMergerWriteExcel("d:/合并文件/merger_2_"+i+".xls","d:/合并文件/"+i,2);
String inf=mergerExcel.mergerExcel();
System.out.println("第"+i+"批次文件"+inf);
}
}
}