I tried sending this once before but it appears it didn't make it past my
company's security checks.
Here'some code that checks for dates in the spreadsheet. There are several
formats you can pick to represent the date in. I probably picked one that
isn't what you want but you should be able to find out what you need in that
respect from the internet. That's where I got the info regarding date
handling.
Note that the excel test file name is hard coded but is easily changed.
Mike
//**************************************************************************
************
//XcelReader.java
//(requires Apache Jakarta POI jar files)
//
//Michael Page
//23 May 2005
//
//**************************************************************************
***********
import java.util.Iterator;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.poifs.filesystem.*;
import java.io.FileInputStream;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.Toolkit;
public class XcelReader
{
public String ExcelFileName;
public static void main(String[] args)
{
new XcelReader();
}
public XcelReader()
{
new ExcelFileLocation();
System.out.println("Reading the HSSF.xls data.");
new Tools();
}
class Tools extends JFrame
{
// variable, object declaration
int MaxColSize = 100;
int MaxRowSize = 500;
int MaxColCount;
int MaxRowCount;
int RowCount;
int ColCount;
String ColumnNames[];
String[][] tools;
JFrame ToolsFrame;
// create the default table
DefaultTableModel defaulttablemodel = new
DefaultTableModel();
public Tools()
{
MaxColCount = 0;
MaxRowCount = 0;
tools = new String[MaxRowSize][MaxColSize];
//[rows][cols]
ColumnNames = new String[MaxColSize];
// file location test
try
{
HSSFWorkbook wb1 = new HSSFWorkbook(new
FileInputStream(ExcelFileName));
}
catch (Exception ex) // check if the file is located
in the same directory as the class file
{
int strLength = ExcelFileName.length();
String temp =
ExcelFileName.substring(3,strLength);
ExcelFileName = temp;
}
// read the excel file
try
{
FileInputStream iStream = new
FileInputStream(ExcelFileName);
POIFSFileSystem fs = new
POIFSFileSystem(iStream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0); // get
the first sheet data
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
while( rows.hasNext() )
{
HSSFRow row = (HSSFRow) rows.next();
// Iterate over each cell in the row
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
HSSFCell cell = (HSSFCell)
cells.next();
if (cell.getCellNum() <
MaxColSize)
{
switch (
cell.getCellType() )
{
case
HSSFCell.CELL_TYPE_NUMERIC:
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//
cell.getNumericCellValue() );
// check if
the cell has a date format first
if
(HSSFDateUtil.isCellDateFormatted(cell))
{
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//
cell.getDateCellValue() );
//tools [row.getRowNum()][cell.getCellNum()] =
cell.getDateCellValue().toString();
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yy");
tools
[row.getRowNum()][cell.getCellNum()] =
formatter.format(cell.getDateCellValue());
}
else //
probably not a date
{
tools [row.getRowNum()][cell.getCellNum()] =
Double.toString(cell.getNumericCellValue());
}
if
(cell.getCellNum() >= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount = cell.getCellNum();
}
if
(row.getRowNum() >= MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount = row.getRowNum();
}
break;
case
HSSFCell.CELL_TYPE_STRING:
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//
cell.getStringCellValue() );
tools
[row.getRowNum()][cell.getCellNum()] = cell.getStringCellValue();
if
(cell.getCellNum() >= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount = cell.getCellNum();
}
if
(row.getRowNum() >= MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount = row.getRowNum();
}
break;
case
HSSFCell.CELL_TYPE_FORMULA:
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//
cell.getStringCellValue() );
if
(isString(cell.getStringCellValue()))
{
tools
[row.getRowNum()][cell.getCellNum()] = cell.getStringCellValue();
}
else
{
tools
[row.getRowNum()][cell.getCellNum()] =
Double.toString(cell.getNumericCellValue());
}
if
(cell.getCellNum() >= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount = cell.getCellNum();
}
if (row.getRowNum() >=
MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount =
row.getRowNum();
}
break;
case
HSSFCell.CELL_TYPE_BLANK:
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//
cell.getStringCellValue() );
tools
[row.getRowNum()][cell.getCellNum()] = cell.getStringCellValue();
if (cell.getCellNum()
>= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount
= cell.getCellNum();
}
if (row.getRowNum() >=
MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount
= row.getRowNum();
}
break;
case
HSSFCell.CELL_TYPE_ERROR:
//System.out.println("Row #: " + row.getRowNum() + " Cell #: " +
cell.getCellNum() + " " +
//cell.getStringCellValue() );
tools
[row.getRowNum()][cell.getCellNum()] = cell.getStringCellValue();
if
(cell.getCellNum() >= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount = cell.getCellNum();
}
if
(row.getRowNum() >= MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount = row.getRowNum();
}
break;
default:
//System.out.println("Row #: "
+ row.getRowNum() + " Cell #: " + cell.getCellNum() + " " +
//
cell.getStringCellValue() );
tools
[row.getRowNum()][cell.getCellNum()] = cell.getStringCellValue();
if
(cell.getCellNum() >= MaxColCount && cell.getCellNum() < MaxColSize)
{
MaxColCount = cell.getCellNum();
}
if
(row.getRowNum() >= MaxRowCount && row.getRowNum() < MaxRowSize)
{
MaxRowCount = row.getRowNum();
}
break;
} // end switch
block
}
} // end column while loop
} // end row while loop
iStream.close(); // close the read stream
}
catch (Exception ex)
{
System.out.println("Excel Reader: " + ex);
} // end try block
System.out.println("max spreadsheet col count is " +
MaxColCount+1);
System.out.println("max spreadsheet row count is " +
MaxRowCount+1);
// get the column headers
try
{
for (int i = 0;i <= MaxColCount; i++)
{
if (tools[0][i] != null)
{
ColumnNames[i] = tools[0][i];
//System.out.println("col " + i + "
header = " + ColumnNames[i]);
}
}
}
catch(Exception e1)
{
System.out.println("Tools Reader: " + e1);
}
// instantiate the tools frame
ToolsFrame = new JFrame();
// instantiate the dose model data object
Object [] data = new Object[MaxRowSize];
// create default model JTable
JTable toolstable = new JTable(defaulttablemodel);
// create panel for add row/col buttons
JPanel jpanel = new JPanel();
// put the data into the JTable
for (int column = 0; column <= MaxColCount;
column++)
{
defaulttablemodel.addColumn(ColumnNames[column]);
}
for (int row = 0; row <= MaxRowCount; row++)
{
for (int column = 0; column <= MaxColCount;
column++)
{
data[column] = tools[row][column];
}
defaulttablemodel.addRow(data);
}
// add the JTable, panel and panel buttons to the
JFrame
ToolsFrame.getContentPane().setLayout(new
BorderLayout());
// add the JTable
ToolsFrame.getContentPane().add(new
JScrollPane(toolstable),BorderLayout.CENTER);
// add the panel containing the add row/column
buttons
ToolsFrame.getContentPane().add(jpanel,BorderLayout.SOUTH);
// JFrame properties
ToolsFrame.setTitle("Michael Page");
Dimension screensize =
Toolkit.getDefaultToolkit().getScreenSize();
//System.out.println("screen resolution is " +
screensize.width);
//System.out.println("screen resolution is " +
screensize.height);
ToolsFrame.setSize(screensize.width,screensize.height-30);
// maximize the frame
ToolsFrame.setExtendedState(JFrame.MAXIMIZED_BOTH);
// set the table auto resize feature to off
toolstable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
// now show the frame
ToolsFrame.setVisible(true);
} // end Tools constructor
// checks if data type is string
public boolean isString(String checkStr)
{
try
{
String test = checkStr.substring(0,1); //
did not throw, must be a non-empty string
return true;
}
catch(Exception e8)
{
return false; // threw, so not a string
}
} // end method isString
}// end class Tools
class ExcelFileLocation
{
ExcelFileLocation() // constructor
{
ExcelFileName = "c:/HSSF.xls";
} // end constructor
} // end class ExcelFileLocation
} // end class XcelReader
-----Original Message-----
From: news [mailto:[EMAIL PROTECTED] Behalf Of jaypee
Sent: Friday, July 15, 2005 2:20 AM
To: [email protected]
Subject: How to read data as it appears in the excel sheet.........
Hi Everyone,
I am trying to read(just read) the data from an excel sheet column.
The problem is, that particular column is formatted as "d/m/yy;@". When
i
read a cell in that column, it returns the alias value(eg., 38473) but not
the
actual value(eg., 5/1/05).
How can i read the actual value(ie., 5/1/05) which is appears in that
cell?
I am using Version : POI api 2.5.1.
Thanks in advance,
Jaypee
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
