import java.io.File;
import java.io.FileFilter;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Vector;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
*
* @author Ranjeet Singh
*/
public class Operations {
private static Operations op = new Operations();
private Operations() {
}
public static Operations getInstance() {
return op;
}
public Vector getFilesListData(String rawFolder, String splitString, int sheetNumber) {
try {
FileFilter filter = new FileFilter() {
@Override
public boolean accept(File pathname) {
if (pathname.toString().contains(".xls")) {
return pathname.isFile();
} else {
return false;
}
}
};
File f = new File(rawFolder);
File arr1[] = f.listFiles(filter);
jxl.Workbook wb;
int totalRows;
jxl.Sheet sh;
Vector list = new Vector();
String st;
for (int i = 0; i < arr1.length; i++) {
try {
wb = jxl.Workbook.getWorkbook(arr1[i]);
sh = wb.getSheet(sheetNumber);
totalRows = sh.getRows();
for (int row = 0; row < totalRows; row++) {
if (row == 0 && i >= 1) {
continue;
}
st = "";
for (int col = 0; col < sh.getColumns(); col++) {
st += splitString + sh.getCell(col, row).getContents();
}
list.add(st);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
return list;
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
public void writaData(String fileName, String sheetName, Vector list, String splitString, int bufferRows) {
try {
String object, arr[];
SXSSFWorkbook wwb = new SXSSFWorkbook(bufferRows);
Sheet wsh = wwb.createSheet();
int row = 0;
Row excelRow;
System.out.println("List Size" + list.size());
list.trimToSize();
for (int i = 0; i < list.size(); i++) {
excelRow = wsh.createRow(row++);
object = list.get(i).toString();
arr = object.split(splitString);
for (int ii = 1; ii < arr.length; ii++) {
try {
excelRow.createCell(ii - 1).setCellValue(Long.parseLong(arr[ii]));
} catch (NumberFormatException ee) {
excelRow.createCell(ii - 1).setCellValue(arr[ii]);
}
}
}
wwb.write(new FileOutputStream(new File(fileName)));
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static void main(String args[]) {
Operations op = Operations.getInstance();
Vector l = op.getFilesListData("Raw data/dump", "#", 0);
op.writaData("aaa.xlsx", "Sheet1", l, "#", 1000);
}
}
