Is there a way to check whether an Excel file has a header or not?

[Guest]

Hi!

I'm currently using POI Utility to read and write Excel files.

You normally use the HasHeaderRow = true / false to specify whether the file has a header or not.

Now, let's say I have a program that needs to read Excel files from a directory, some have headers, some don't.

Is there a way to know dynamically whether a header has been defined or not?

Then, for those having headers, the flag will be set as true, while for those which don't have any header, the flag will be set as false.

E.g.

<!--- Create an instance of the POIUtility.cfc. --->
    <cfset objPOI = CreateObject(
        "component",
        "POIUtility"
        ).Init()
        />

<!--- Set directory --->

<cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & "newDir">

<!--- Check whether the directory exists. --->
<cfif DirectoryExists(variables.currentDirectory)>
    
     <!--- Read files from the specified directory --->
     <cfdirectory action="list" directory="#variables.currentDirectory#" type="file" filter="*.xls" name="qDirectory">

     <!--- Check if the directory has any Excel file --->
     <cfif variables.qDirectory.recordcount gt 0>

          <!--- Loop query --->
     <cfloop query="variables.qDirectory">

               <!--- Check if header is present in each of the file --->
                    IF headerExists THEN
                         headerFlag = true
                    ELSE
                         headerFlag = false
                    END IF

         
               <!--- Read Excel File --->
            <cfset objSheet = objPOI.ReadExcel(FilePath = #variables.qDirectory.name#, HasHeaderRow = #variables.headerFlag#, SheetIndex = 0) />

          </cfloop>

     </cfif>

</cfif>

Any help would be most welcome.

Thanks and regards,

Yogesh Mahadnac  

[-__cfSearching__-]

I highly doubt it.  A header is just another row.  Usually the only difference being formatting and content.  If you were dealing with files all having a specific format, you might be able to write some rules.  Examine the content of the first row and try and determine programatically if it is a header.  For example checking the values against the expected value data types.  Other than that, I do not think so.

[Guest]

Hi cfSearching,

Many thanks for your reply! I really do appreciate!

However, I've still got 1 more question for you.

At the moment, I'm using POI and sometimes cfx_Excel2Query to read Excel files.

In both cases, you have to specify whether the first row is a header.

How would you read the Excel file to check whether the 1st row is the header?

I've tried using cffile, but I get all sorts of "garbage"

        <cffile action="read" file="#variables.filename#" variable="xlsResult">
        <cfloop index="i" list="#variables.xlsResult#" delimiters="#chr(13)#&#chr(10)#">
            <cfif variables.i eq 1>
                <cfoutput>
                    Test 1st Row: #trim(replacenocase(listgetat(variables.i,1,","),'"',"","All"))#
                </cfoutput>
            </cfif>
        </cfloop>

I get the following output:

ÐÏ à¡± á����.... etc etc

I'd be very much grateful if you could please advise on the latter at your earliest convenience.

Thanks and best regards,

Yogesh Mahadnac

[-__cfSearching__-]

There is no special flag identifying header rows. So you may as well specify false for that setting.  Then examine the first row of the returned query.  Apply some rules and make a guess as to whether or not it is a header row. If it is, delete that row from the query (or ignore it).

In both cases, you have to specify whether the first row is a header.

How would you read the Excel file to check whether the 1st row is the header?

[Guest]

Hi cfSearching,

Many thanks for your answer once again!

I'll give it a try right away, was planning to do something similar though

Best regards,

Yogesh Mahadnac