poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Beardsley <markbrd...@tiscali.co.uk>
Subject Re: read xls cell value if it is greater than 24 hours (HH:MM:SS = 55:34:34)
Date Tue, 25 Jan 2011 19:31:08 GMT

This evening, I had the chance to play around further with some code and put
together a small class that I hope will help you out.

Look first at the code in the main() method as that shows how I intended the
class would ba called. All you need to do is create an array of Strings
where each element is the path to and name of a Excel workbook. Notice that
the class is able to process both types of workbook file and so you can pass
the names of both binary (.xls) and SpreadsheetML (.xlsx) files to it. The
code will then simply open the workbook and iterate through the sheets it
contains. The rows will be recovered from the sheet and the cells from the
row. Next, a swicth construct is used to determine the actual type of the
cell and to print out a message detailing what this is along with the cells
contents. At the end of the switch statement, you will see that a
DataFormatter object is used to obtain a String that encapsulates the
contents of the cell formatted in accordance with the data format applied to
the cell. This call to the formatCellValue() method of the DataFormatter
will return a value formatted in the HH:MM:SS style you requested whether
the original cell of of type String or is a date cell with the [h]:mm:ss
format applied to it.

 * To change this template, choose Tools | Templates
 * and open the template in the editor.

import java.util.*;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

 * @author win user
public class TimeCellTest {

    public TimeCellTest(String[] filenames) throws IOException,
InvalidFormatException {
        Workbook workbook = null;
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        File file = null;
        FileInputStream fis = null;
        Iterator<Row> rowIter = null;
        Iterator<Cell> cellIter = null;
        DataFormatter formatter = null;
        int numSheets = 0;
        try {
            formatter = new DataFormatter();
            for(String filename : filenames) {
                file = new File(filename);
                fis = new FileInputStream(file);
                System.out.println("Opening workbook " + file.getName());
                workbook = WorkbookFactory.create(fis);
                numSheets = workbook.getNumberOfSheets();
                for(int i = 0 ; i < numSheets ; i++) {
                    sheet = workbook.getSheetAt(i);
                    System.out.println("\tProcessing sheet: " +
                    rowIter = sheet.rowIterator();
                    while(rowIter.hasNext()) {
                        row = rowIter.next();
                        System.out.println("\t\tProcessing row number: " +
                        cellIter = row.cellIterator();
                        while(cellIter.hasNext()) {
                            cell = cellIter.next();
                            System.out.print("\t\t\tProcessing cell number:
" + cell.getColumnIndex());
                            switch(cell.getCellType()) {
                                case Cell.CELL_TYPE_BLANK:
                                    System.out.println(" and the cell is
                                case Cell.CELL_TYPE_BOOLEAN:
                                    System.out.println(" and it is a boolean
" +
                                            "cell with the following value "
                                case Cell.CELL_TYPE_ERROR:
                                    System.out.println(" and it indicates an
" +
                                            "error whose code is " +
                                case Cell.CELL_TYPE_FORMULA:
                                    System.out.println(" and it contains a "
                                            "formula which follows " +
                                case Cell.CELL_TYPE_NUMERIC:
                                    if(DateUtil.isCellDateFormatted(cell)) {
                                        System.out.println(" and it contains
a " +
                                                "date " +
                                    else {
                                        System.out.println(" and it contains
a " +
                                                "number " +
                                case Cell.CELL_TYPE_STRING:
                                    System.out.println(" and it contains a "
                                            "String \"" +
cell.getRichStringCellValue().getString() +
                            System.out.println("\t\t\tThen, using the " +
                                    "DataFormatter, the cells value is " + 
        finally {
            try {
                if(fis != null) {
                    fis = null;
            catch(IOException ioEx) {
                // I G N O R E

    public static void main(String[] args) {
        try {
            new TimeCellTest(new String[]{"C:/temp/Test Book.xls", 
            "C:/temp/Test Book.xls"});
        catch(Exception ex) {
            System.out.println("Caught a: " + ex.getClass().getName());
            System.out.println("Message: " + ex.getMessage());
            System.out.println("Stacktrace follows:.....");

Hope this helps a little, and now the only question is how you need to store
the value into the database.


Mark B
View this message in context: http://apache-poi.1045710.n5.nabble.com/read-xls-cell-value-if-it-is-greater-than-24-hours-HH-MM-SS-55-34-34-tp3340264p3356823.html
Sent from the POI - User mailing list archive at Nabble.com.

To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

View raw message