Read and writing xlsx files

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);

}

}

}