Below Jars are Required for implemnetation:
dom4j-1.6.1.jar
poi-3.10-FINAL-20140208.jar
poi-ooxml-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
commons-lang-2.6.jar
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ExcelMainProgram {
public static void main(String[] args) throws Exception {
//
// An excel file name. You can create a file name with a full path
// information.
//
System.out.println(new Date());
String filename = "Template.xls";//xls or xlsx
//
// Create an ArrayList to store the data read from excel sheet.
//
ArrayList<ArrayList<Cell>> sheetData = new ArrayList<ArrayList<Cell>>();
FileInputStream fis = null;
try {
//
// Create a FileInputStream that will be use to read the excel file.
//
fis = new FileInputStream(filename);
//
// Create an excel workbook from the file system.
//
Workbook workbook = WorkbookFactory.create(fis);;
//
// Get the first sheet on the workbook.
//
Sheet sheet = workbook.getSheetAt(0);
//
// When we have a sheet object in hand we can iterator on each
// sheet's rows and on each row's cells. We store the data read
// on an ArrayList so that we can printed the content of the excel
// to the console.
//
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row =rows.next();
if(row.getRowNum()==2)
{continue;}
}
while (rows.hasNext()) {
Row row =rows.next();
Iterator<Cell> cells = row.cellIterator();
System.out.println(row.getRowNum());
ArrayList<Cell> data = new ArrayList<Cell>();
while (cells.hasNext()) {
Cell cell = cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
showExelData(sheetData);
System.out.println(new Date());
}
private static void showExelData(List<ArrayList<Cell>> sheetData) {
//
// Iterates the data and print it out to the console.
//
for (int i = 0; i < sheetData.size(); i++) {
List<Cell> list = (List<Cell>) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell =list.get(j);
cell.setCellType(Cell.CELL_TYPE_STRING);//To get the cell value as string set the cell type as String
System.out.print(cell.getStringCellValue() + "\t\t");
if (j < list.size() - 1) {
//System.out.print("SPACE");
}
}
System.out.println("");
}
}
}
dom4j-1.6.1.jar
poi-3.10-FINAL-20140208.jar
poi-ooxml-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
commons-lang-2.6.jar
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ExcelMainProgram {
public static void main(String[] args) throws Exception {
//
// An excel file name. You can create a file name with a full path
// information.
//
System.out.println(new Date());
String filename = "Template.xls";//xls or xlsx
//
// Create an ArrayList to store the data read from excel sheet.
//
ArrayList<ArrayList<Cell>> sheetData = new ArrayList<ArrayList<Cell>>();
FileInputStream fis = null;
try {
//
// Create a FileInputStream that will be use to read the excel file.
//
fis = new FileInputStream(filename);
//
// Create an excel workbook from the file system.
//
Workbook workbook = WorkbookFactory.create(fis);;
//
// Get the first sheet on the workbook.
//
Sheet sheet = workbook.getSheetAt(0);
//
// When we have a sheet object in hand we can iterator on each
// sheet's rows and on each row's cells. We store the data read
// on an ArrayList so that we can printed the content of the excel
// to the console.
//
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row =rows.next();
if(row.getRowNum()==2)
{continue;}
}
while (rows.hasNext()) {
Row row =rows.next();
Iterator<Cell> cells = row.cellIterator();
System.out.println(row.getRowNum());
ArrayList<Cell> data = new ArrayList<Cell>();
while (cells.hasNext()) {
Cell cell = cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
showExelData(sheetData);
System.out.println(new Date());
}
private static void showExelData(List<ArrayList<Cell>> sheetData) {
//
// Iterates the data and print it out to the console.
//
for (int i = 0; i < sheetData.size(); i++) {
List<Cell> list = (List<Cell>) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell =list.get(j);
cell.setCellType(Cell.CELL_TYPE_STRING);//To get the cell value as string set the cell type as String
System.out.print(cell.getStringCellValue() + "\t\t");
if (j < list.size() - 1) {
//System.out.print("SPACE");
}
}
System.out.println("");
}
}
}
No comments:
Post a Comment