Last active
April 4, 2016 19:39
-
-
Save jsianes/5874866 to your computer and use it in GitHub Desktop.
A Java application using Apache POI library for merging Excel files
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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."); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.