download jar files:
1.dom4j-1.6.1,jar
2.poi-3.6.jar
3.poi-ooxml-3.5-FINAL-20090928.jar
4.poi-ooxml-schemas-3.6.jar
5.xmlbeans-2.3.0.jar
Code:
import java.io.File;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author sachanta
*/
public class ReadFiles {
public void getFiles() throws IOException {
try {
File pathName = new File("D:/excel");
String filename[] = pathName.list();
if (filename == null) {
System.out.println("No files exists");
} else {
for (int i = 0; i
File f = new File(pathName.getAbsoluteFile(), filename[i]);
String fname = f.getCanonicalPath();
System.out.print(fname);
readWriteExcel(fname, i);
System.out.println("File created successfully");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void readWriteExcel(String fname, int f) throws IOException {
String filename = fname;//"D:/Test case Teplete.xlsx";
FileInputStream fis = null;
FileOutputStream fout = null;
try {
fis = new FileInputStream(filename);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
int numSheets = workbook.getNumberOfSheets();
//writing the excel file
if (f == 0) {
(new File("D:/excel/excel")).mkdir();
}
fout = new FileOutputStream("D:/excel/excel/Consolidated-" + f + ".xlsx");
XSSFWorkbook cworkbook = new XSSFWorkbook();
XSSFSheet csheet = cworkbook.createSheet("output");
int createrownumber = 0;
for (int i = 0; i <>
{
XSSFSheet sheet = workbook.getSheetAt(i);
//String name = workbook.getSheetName(i);
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
XSSFRow row = ((XSSFRow) rows.next());
float oldheight = row.getHeightInPoints();
int remove_row = row.getRowNum();
if ((i != 0) && (remove_row == 0))
{
continue;
}
XSSFRow crow = csheet.createRow(createrownumber);
crow.setHeightInPoints(oldheight);
Iterator cells = row.cellIterator();
int cellSize = row.getFirstCellNum();
while (cells.hasNext())
{
XSSFCell cell = (XSSFCell) cells.next();
//creating a cell in target file
XSSFCell cell1 = crow.createCell((short) cellSize);
csheet.autoSizeColumn((short) 0);
csheet.autoSizeColumn((short) 1);
csheet.autoSizeColumn((short) 4);
int columnWidth = sheet.getColumnWidth(cellSize);
csheet.setColumnWidth(cellSize, columnWidth);
String Value = cell.getStringCellValue();
//setting the cell value in the target cell
cell1.setCellValue(Value);
XSSFCellStyle oldCellStyle = cell.getCellStyle();
XSSFCellStyle cellStyle = cworkbook.createCellStyle();
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
//cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setWrapText(true);
cellStyle.setFillForegroundColor(oldCellStyle.getFillForegroundXSSFColor());
cellStyle.setFillPattern(oldCellStyle.getFillPattern());
cell1.setCellStyle(cellStyle);
cellSize++;
}//end of cell iterator
createrownumber++;
}//end of row iterator
createrownumber = createrownumber +3;
}//end of sheet iterator
cworkbook.write(fout);
} catch (IOException e) {
e.printStackTrace();
} finally
{
if (fout != null)
{
fout.close();
}
if (fis != null)
{
fis.close();
}
}
}
public static void main(String args[]) {
ReadFiles r = new ReadFiles();
try {
r.getFiles();
} catch (IOException ex) {
Logger.getLogger(ReadFiles.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
