Skip to content

Instantly share code, notes, and snippets.

@jsianes
Last active April 4, 2016 19:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save jsianes/5874866 to your computer and use it in GitHub Desktop.
Save jsianes/5874866 to your computer and use it in GitHub Desktop.
A Java application using Apache POI library for merging Excel files
import java.io.*;
import java.util.Map;
import java.util.Vector;
import java.util.HashMap;
import java.util.StringTokenizer;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
public class mergeExcel {
public static void main(String[] args) {
int i=0,j=0,k=0;
boolean entrada=false,salida=false,copyStyle=false;
Vector<String> f_entrada=new Vector<String>(20);
String arg;
String outputfile = "";
String inputfile = "";
String nombreHoja ="";
InputStream fileIn=null,fileIn2=null;
OutputStream fileOut = null;
while (i < args.length && args[i].startsWith("-")) {
arg = args[i++];
if (arg.equals("-output"))
{
if (i < args.length) {
outputfile=args[i++];
salida=true;
}
else System.err.println("'-output' an output file name is required.");
}
if (arg.equals("-input"))
{
if (i < args.length) {
inputfile = args[i++];
StringTokenizer st = new StringTokenizer(inputfile, ";");
while (st.hasMoreTokens()) {
f_entrada.addElement(st.nextToken());
}
entrada=true;
}
else System.err.println("'-intput' an input file name is required.");
}
if (arg.equals("-style")) { copyStyle=true; }
}
if (entrada&&salida) {
try {
HSSFWorkbook wb_salida;
File fichero_salida = new File(outputfile);
if (fichero_salida.exists()) {
fileIn2 = new FileInputStream(outputfile);
wb_salida = new HSSFWorkbook(fileIn2);
} else {
wb_salida = new HSSFWorkbook();
}
for(i=0; i<f_entrada.size(); i++){
inputfile=(String)f_entrada.elementAt(i);
Vector<String> elementos=new Vector<String>(2);
StringTokenizer lt = new StringTokenizer(inputfile, ":");
while (lt.hasMoreTokens()) {
elementos.addElement(lt.nextToken());
}
HSSFSheet sheet_entrada;
if (elementos.size()==1) {
fileIn = new FileInputStream(inputfile);
HSSFWorkbook wb_entrada = new HSSFWorkbook(fileIn);
sheet_entrada = wb_entrada.getSheetAt(0);
} else {
fileIn = new FileInputStream(elementos.get(0));
HSSFWorkbook wb_entrada = new HSSFWorkbook(fileIn);
sheet_entrada = wb_entrada.getSheet(elementos.get(elementos.size()-1));
}
nombreHoja = sheet_entrada.getSheetName();
HSSFSheet sheet_salida = wb_salida.createSheet(nombreHoja);
System.out.printf("Processing Workbook:"+elementos.get(0)+" / Sheet:"+nombreHoja+" ");
Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
for (j=sheet_entrada.getFirstRowNum();j<=sheet_entrada.getLastRowNum();j++){
HSSFRow row_entrada = sheet_entrada.getRow(j);
HSSFRow row_salida = sheet_salida.createRow(j);
for (k=row_entrada.getFirstCellNum();k<row_entrada.getLastCellNum();k++) {
HSSFCell celda_entrada = row_entrada.getCell(k);
HSSFCell celda_salida = row_salida.createCell(k);
if(styleMap != null) {
if(celda_entrada.getSheet().getWorkbook() == celda_salida.getSheet().getWorkbook()){
celda_salida.setCellStyle(celda_entrada.getCellStyle());
} else {
int stHashCode = celda_entrada.getCellStyle().hashCode();
HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if(newCellStyle == null) {
newCellStyle = celda_salida.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(celda_entrada.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
celda_salida.setCellStyle(newCellStyle);
}
}
switch(celda_entrada.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
celda_salida.setCellFormula(celda_entrada.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
celda_salida.setCellValue(celda_entrada.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
celda_salida.setCellValue(celda_entrada.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
celda_salida.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
celda_salida.setCellValue(celda_entrada.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
celda_salida.setCellErrorValue(celda_entrada.getErrorCellValue());
break;
default:
celda_salida.setCellValue(celda_entrada.getStringCellValue());
break;
}
}
}
System.out.println(" OK");
}
fileOut = new FileOutputStream(outputfile);
wb_salida.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
System.err.println("Error, file not found:");
e.printStackTrace();
} catch (IOException e) {
System.err.println("I/O error:");
e.printStackTrace();
}
} else {
if (args.length==0) {
System.err.println("Program for merging several Excel (97-2003) files into one:");
System.err.println("");
System.err.println("Usage: java -jar mergeExcel.jar [-style] -output output.xls -input input1.xls[:sheet_name1][;input2.xls[:sheet_name2];...]");
System.err.println("");
System.err.println(" -style: If present, cell style is copied. This parameter is optional (and beta).");
System.err.println(" -output: Output Excel file. If output file exists, it adds sheets to existing ones. This parameter is mandatory.");
System.err.println(" -input: List of input files for merging. Excel files must be separated by ';'. Optionally, sheet label can be specified adding ':' at the end of Excel file name. If no sheet name is specified, first one will be taken. No spaces or special characters are allowed. All final merge sheets (including those in output file, if exists) must have different labels. Otherwise, it could not be merged. This parameter is mandatory.");
System.err.println("");
System.err.println("Copyleft - Javier Sianes <jsianes@gmail.com>");
System.exit(1);
}
if (outputfile.equals("")) {
System.err.println("Required '-output' parameter is not specified.");
}
if (inputfile.equals("")) {
System.err.println("Required '-input' parameter is not specified.");
}
}
}
}
@MrWooJ
Copy link

MrWooJ commented Oct 26, 2015

Does this merge tabels based on common key?
e.g. Suppose this:
FirstTable.xls which contains columns as: StudentID, FirstName, LastName
SecondTable.xls which contains columns as: StudentID, PassedCourse
Now my question is that does your program merge these 2 tables based on StudentID or not?
Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment