I need java code to read data for specific column from excel sheet. – (lo number, line, voucher no, stloc , quantity ,activity.) These set of values for a particular column will be used for sql query (jdbc-odbc connection done). The output for the query will be matched with a column in this sheet (this part ll be done later) Kindly help. sample excel sheet
2 Answers
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package excelfilereading;
/**
*
* @author vkantiya
*/
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 java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
public class Main {
@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception {
//
// An excel file name. You can create a file name with a full
// path information.
//
String filename = "FirstExcel.xls";
// Create an ArrayList to store the data read from excel sheet.
//
List sheetData = new ArrayList();
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.
//
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
HSSFSheet 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 rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
showExelData(sheetData);
}
private static void showExelData(List sheetData) {
//
// Iterates the data and print it out to the console.
//
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
HSSFCell cell = (HSSFCell) list.get(j);
System.out.print(
cell.getRichStringCellValue().getString());
if (j < list.size() - 1) {
System.out.print(", ");
}
}
System.out.println("");
}
}
}
Comments
Have a look at Apache POI - the Java API for Microsoft Documents.
It covers
- Excel (SS=HSSF+XSSF)
- Word (HWPF+XWPF)
- PowerPoint (HSLF+XSLF)
- OpenXML4J (OOXML)
- OLE2 Filesystem (POIFS)
- OLE2 Document Props (HPSF)
- Outlook (HSMF)
- Visio (HDGF) TNEF (HMEF)
- Publisher (HPBF)
1 Comment
heikkim
There's also jxl. Discussion of the two: stackoverflow.com/questions/4763624/…