Detecting if Excel file has header or not

May 26, 2009 at 7:00 PM

Hi,

Is there a way provided by this class to detect if an excel file has a header or not?
A header is nothing more than a column's name followed by the column's data below, that's why it is pretty odd to consider it a header or not. Usually it takes first row of the file.
Also, it there an API to detect the cell value type, e.g. 123 is number, abc is text and so on...?

Thanks

Developer
May 26, 2009 at 7:35 PM

> Is there a way provided by this class to detect if an excel file has a header or not?
Not really sure what you mean. Do you have some example? Are you trying to read some CSV-like Excel file?

> Also, it there an API to detect the cell value type, e.g. 123 is number, abc is text and so on...?
You can look at this. If you only need to check whether e.g. a cell is a number or text, you can reduce the code.

$coordinate = 'A1';

if ($objWorksheet->cellExists($coordinate)) {
    echo 'Cell exists. ';
    $cell = $sheet->getCell($coordinate);
   
    switch ($cell->getDataType()) {
   
    case PHPExcel_Cell_DataType::TYPE_BOOL:
        echo 'Boolean value. (TRUE or FALSE)';
        break;

    case PHPExcel_Cell_DataType::TYPE_FORMULA:
        echo 'Formula.';
        break;

    case PHPExcel_Cell_DataType::TYPE_NUMERIC:
        echo 'Number. (This includes dates and times.)';
        break;

    case PHPExcel_Cell_DataType::TYPE_STRING:
        echo 'Cell contains (shared) string or is blank.';

        if ($cell->getValue() instanceof PHPExcel_RichText) {
            echo ' Actually, it is a Rich-Text string.';
        }
       
        break;

    case PHPExcel_Cell_DataType::TYPE_INLINE:
        echo 'Inline string. Rarely used by Excel as far as I know.';
        break;

    case PHPExcel_Cell_DataType::TYPE_ERROR:
        echo 'Error code.';
        break;

    }
} else {
    echo 'Cell does not exist';
}

May 26, 2009 at 8:58 PM

Hi,

Thanks for the fast reply.
Here is my situation, maybe you can understand me better.
I have a pretty large excel file (xls or xlsx, 3-5Mb) containg columns like 'firs_name', 'last_name', 'phone_no', etc...
On the other hand I have a template (a text which contains some variables  like [first_name], [last_name],[phone_no], etc..)
I have to parse the excel file  and replace the variables [] with the excel values.
Doing this just by reading the (large) file could get me some 'max_execution_time' errors, so I decided to export the excel data into a (MySql) database so I can use it later also..
My ideea to export the excel into Mysql is to parse it and add the data into database, here comes my questions:

1. Would like to create the database table dynamically with the table columns having the same names as excel column name (that's why I asked for an excel header, maybe header is not the right term for this, is the first row of the file which is used as a data deader, e.g. 'name','lastname'....
2. Is there a better way to do this (than parsing the excel row by row and column by column)?
3. I can relay on Excel's data type in order to use it to generate the database data types? e.g. case PHPExcel_Cell_DataType::TYPE_STRING: => Mysql Varchar?

So my script which creates the mysql table should look like this:

CREATE TABLE 'XXX' (Excel_Column1_Name Excel_Column1_DATA_TYPE, Excel_Column2_Name Excel_Column2_DATA_TYPE,......Excel_ColumnN_Name Excel_ColumnN_DATA_TYPE)
Is this OK or can I solve my problem easier?

Thanks for the help!
Cristian

Developer
May 26, 2009 at 9:34 PM

Your explanation helps giving a better idea what you are trying. My first thought was whether the dynamic creation of the table is necessary. If your Excel file has fixed columns, would it not be possible to design the database table in advance and just insert rows as you read the Excel file?

I guess you have a reason for trying a more generalized approach creating database table on the fly. We have not seen examples of this before, but I don't see why it should not be possible. It sounds like you are on the right track.

> Is there a better way to do this (than parsing the excel row by row and column by column)?
This is the simplest, and I would just stick with that. There are methods $objWorksheet->getHighestColumn(), $objWorksheet->getHighestRow() to make things easier.

> I can relay on Excel's data type in order to use it to generate the database data types?

In simple cases, yes. There will be problems for advanced Excel data, but if it is just a list of business contacts, it should be fine.

To start with, you could perhaps also just store everything as text (including numbers) in the database, and keep one column for the datatype.

May 27, 2009 at 9:39 AM

Hi,

Thanks for the reply. Unfortunately the excel file may differ from user to user (these files are uploaded by users) and I don't know their format (how many columns or rows) before creating the database, that's why I have to create it 'on the fly'.

I will follow your toughts (which are mostly like mine).

Thanks for the help!