Monday, October 10, 2011

Read Excel file using poi-3.0.1-FINAL-20070705.jar

Read Excel file using poi-3.0.1-FINAL-20070705.jar code as below

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
*
* @author user
*/
public class Demo {

public static void main(String arg[]) {
Demo demo = new Demo();
String[][] strCom = {{"A", "String"}, {"B", "String"}, {"E", "String"}};
ArrayList alRTags = demo.readExcelValue("C:\\Documents and Settings\\user\\Desktop\\def.xls", 5, strCom);
for (int i = 0; i < alRTags.size(); i++) {
//int uid = vIAObj.getOPCTagMasterUniqueID();
System.out.println(alRTags.get(i));
}
}

public ArrayList readExcelValue(String strFileName, int len, String[][] strCom) {
ArrayList alRTags = null;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(strFileName));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
int rows = sheet.getPhysicalNumberOfRows();
row = sheet.getRow(0);
String strTagName = "";

HashMap hmRC = new HashMap();
HashMap hmRCN = new HashMap();
for (int i = 0; i < strCom.length; i++) {
hmRC.put(strCom[i][0], strCom[i][1]);
}

String heading = "";

for (int j = 0; j <= len; j++) //for (int j = 0; j <= 5; j++) //
{
if (row != null) {
cell = row.getCell((short) j);
if (cell != null) {
String strH = String.valueOf(cell.getStringCellValue());
if (hmRC.containsKey(strH)) {
hmRCN.put(j, hmRC.get(strH));
}
heading += strH + ",";
}
}
}

if (heading == null) {
heading = "NoData";
} else if (heading.trim().equals("")) {
heading = "NoData";
}
System.out.println("heading " + heading);

alRTags = new ArrayList();
for (int r = 1; r <= rows; r++) {
row = sheet.getRow(r);
if (row != null) {
for (int i = 0; i < len; i++) {
strTagName = "NA";

if (hmRCN.containsKey(i)) {
cell = row.getCell((short) i);
try {
if (hmRCN.get(i).equals("String")) {
strTagName = String.valueOf(cell.getStringCellValue());
} else {
strTagName = String.valueOf(cell.getNumericCellValue());
}
} catch (Exception e) {
}
alRTags.add(strTagName);
}
}
}
}

} catch (Exception e) {
e.printStackTrace();
}
return alRTags;
}
}

No comments:

Post a Comment