java 合并汇总多个excel数据到单个excel中

需求：家人工作需要统计公司全体员工个人信息及各个资料，每个统计指标一个excel，每个人19张excel，最终要把所有员工的各个统计指标分别都合并到单独的excel中，即最终汇总到19张excel中。由于员工基数大，单独靠一个人合并起来就很费时费力，工作量十分庞大，且是枯燥的重复苦力活，故开发此程序，减少工作量，提升工作效率。

设计：本程序是一次输出为excel，所以当文件过大，就会有内存溢出，而导致合拼失败，开始使用poi读取，但是报一个block[0] removed的异常，查阅官方文档，说是excel07以下的版本和07版本格式不一样所致，结果使用了jxl。

优化建议：本程序纯属后端Java程序，灵活性不高，只适合表头行数固定的excel，后续优化建议做成B/S架构模型，可配置化(源路径、目标路径、表头行数等等)，增加程序灵活性。

package myTest;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
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;// 开始合并的行标(例如第一行是表头第二行开始是数据，则此时从第二行有数据的开始合并即为1，也意味着表头就一行)
private int pageCount = 0;
//private List<String> columns = null;
private String[][] headColumns = 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("合并_" + 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) {
e.printStackTrace();
} catch (IOException e) {
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 {
// TODO 创建？ 
srcFile.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}*/
System.out.println("要合并的文件路径不存在！");
} else {
File[] file = srcFile.listFiles();
for (int i = 0; i < file.length; i++) {
String path = file[i].getAbsolutePath();
if (path.indexOf(".xls") > 0) {
if (headColumns == null) {
this.createHeader(excelSheet, path); //如果此处不需要，不能直接注释，里面涉及到一些endMerGerColumnIndex、headColumns的赋值逻辑
}
System.out.println("正在读入第" + i + "个文件:" + path + "----请稍等");
try {
this.readSingleExcel(excelSheet, path);
} catch (Exception e) {
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(); 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));
}*/
for (int i = 0; i < beginMergerRow; i++) {
for (int j = beginMergerColumnIndex; j < endMerGerColumnIndex; j++) {
this.addLabel(excelSheet, column++, i, headColumns[i][j]);
}
column = 0;
}
//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;
}*/
endMerGerColumnIndex = sheet.getColumns();
headColumns = new String[beginMergerRow][endMerGerColumnIndex];
for (int i = 0; i < beginMergerRow; i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
String cell_value = cell.getContents();
headColumns[i][j] = cell_value;
}
}
} catch (Exception e) {
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 {
System.out.println("*********merger begin*********");
//文件夹从1开始，19代表有多个文件夹
for (int i = 1; i <= 19; i++) {
JXLMergerWriteExcel mergerExcel = new JXLMergerWriteExcel("d:/excel/合并后文件/merger_" + i + ".xls", "d:/excel/待合并文件/" + i,
3);
String inf = mergerExcel.mergerExcel();
System.out.println("------------第" + i + "批次文件" + inf + "------------");
}
System.out.println("*********merger end*********");
}
}