Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Kevin Roche

Reading Excel Files With ColdFusion And POI

Posted by Ben Nadel
Tags: ColdFusion

I have been doing a lot of experimenting with writing Microsoft Excel files using ColdFusion. I was just recently turned onto POI by Rob Brooks-Bilson and found some great code resources by Dave Ross. However, when it comes to reading in an Excel file using POI and ColdFusion, I was not able to find much information. So, using Dave's POI reference page and some Java examples on OnJava.com I put this little example together.

There are probably lots of pitfalls and stuff that I am not seeing (this is my first read with ColdFusion and POI) so m code makes the following assumptions with this example:

  1. The data is in a uniform block of cells, meaning, all rows have the same number of column.
  2. The data only has one sheet (Excel tab) that I am trying to get at.
  3. There is no header row.
  4. There are no "bad cells" - cells that have no data but the files thinks they are part of the "active cell block."

While the assumptions above might seem a bit limiting, I think that they will cover about 95% of the cases that I will have to deal with. So, here is the code that I have come up with:

  • <!---
  • Create the Excel file system object. This object is
  • responsible for reading in the given Excel file.
  • --->
  • <cfset objExcelFileSystem = CreateObject(
  • "java",
  • "org.apache.poi.poifs.filesystem.POIFSFileSystem"
  • ).Init(
  •  
  • CreateObject(
  • "java",
  • "java.io.FileInputStream"
  • ).Init(
  •  
  • ExpandPath( "./jenna_jameson.xls" )
  •  
  • )
  • ) />
  •  
  •  
  • <!---
  • Get the workbook from the Excel file system object that
  • we just created. Remember, the workbook contains the
  • Excel sheets that have our data.
  • --->
  • <cfset objWorkBook = CreateObject(
  • "java",
  • "org.apache.poi.hssf.usermodel.HSSFWorkbook"
  • ).Init(
  • objExcelFileSystem
  • ) />
  •  
  • <!---
  • For this demo, we are only interested in reading in the
  • data from the first sheet. Remember, since Java is zero-
  • based, not one-based like ColdFusion, the first Excel
  • sheet is at index ZERO (not ONE).
  • --->
  • <cfset objSheet = objWorkBook.GetSheetAt(
  • JavaCast( "int", 0 )
  • ) />
  •  
  •  
  • <!---
  • We are going to build a ColdFusion query that houses the
  • Excel data, but we don't know anything about the data
  • just yet. So, just create the place holder for the query
  • and then we will add to it when we have more information.
  • --->
  • <cfset qCell = "" />
  •  
  •  
  • <!---
  • Get the Excel sheet's row iterator. This appears to be some
  • sort of implementation of the Java class java.util.TreeMap,
  • but I don't know much about that. What I do know, is that
  • this will allow us to loop over the rows in the Excel file
  • until there are no more to loop over. The interface for it
  • looks like the standard iterator interface.
  • --->
  • <cfset objRowIterator = objSheet.rowIterator() />
  •  
  •  
  • <!---
  • User the row iterator to loop over all the physical rows in
  • the Excel sheet. This condition checks to see if we have a
  • row to read in. At this point, the iterator is NOT pointing
  • at a valid Excel data row.
  • --->
  • <cfloop condition="objRowIterator.HasNext()">
  •  
  • <!---
  • We have determined that we have a valid row to read.
  • Now, move the iterator to point to this valid row.
  • --->
  • <cfset objRow = objRowIterator.Next() />
  •  
  • <!---
  • Get the number of physical cells in this row. While I
  • think that this can possibly change from row to row,
  • for the purposes of this demo, I am going to assume
  • that all rows are uniform and that this row is a model
  • of how the rest of the data will be displayed.
  • --->
  • <cfset intCellCount = objRow.GetPhysicalNumberOfCells() />
  •  
  •  
  • <!---
  • Check to see if the query variable we have it actually
  • a query. If we have not done anything to it yet, then
  • it should still just be a string value (Yahoo for
  • dynamic typing!!!). If that is the case, then let's use
  • this first data row to set up the query object.
  • --->
  • <cfif NOT IsQuery( qCell )>
  •  
  • <!---
  • Create an empty query. Doing it this way creates a
  • query with neither column nor row values.
  • --->
  • <cfset qCell = QueryNew( "" ) />
  •  
  • <!---
  • Now that we have an empty query, we are going to
  • loop over the cells COUNT for this data row and for
  • each cell, we are going to create a query column
  • of type VARCHAR. I understand that cells are going
  • to have different data types, but I am chosing to
  • store everything as a string to make it easier.
  • --->
  • <cfloop
  • index="intCell"
  • from="0"
  • to="#(intCellCount - 1)#"
  • step="1">
  •  
  • <!---
  • Add the column. Notice that the name of the
  • column is the text "column" plus the column
  • index. I am starting my column indexes at ONE
  • rather than ZERO to get it back into a more
  • ColdFusion standard notation.
  • --->
  • <cfset QueryAddColumn(
  • qCell,
  • "column#(intCell + 1)#",
  • "CF_SQL_VARCHAR",
  • ArrayNew( 1 )
  • ) />
  •  
  • </cfloop>
  •  
  • </cfif>
  •  
  •  
  • <!---
  • ASSERT: Whether we are on our first Excel data row or
  • our Nth data row, at this point, we have a ColdFusion
  • query object that has the proper columns defined.
  • --->
  •  
  • <!---
  • Add a row to the query so that we can store this row's
  • data values.
  • --->
  • <cfset QueryAddRow( qCell ) />
  •  
  •  
  • <!--- Loop over the cells in this row to find values. --->
  • <cfloop
  • index="intCell"
  • from="0"
  • to="#(intCellCount - 1)#"
  • step="1">
  •  
  •  
  • <!---
  • When getting the value of a cell, it is important
  • to know what type of cell value we are dealing
  • with. If you try to grab the wrong value type,
  • an error might be thrown. For that reason, we must
  • check to see what type of cell we are working with.
  • These are the cell types and they are constants
  • of the cell object itself:
  •  
  • 0 - CELL_TYPE_NUMERIC
  • 1 - CELL_TYPE_STRING
  • 2 - CELL_TYPE_FORMULA
  • 3 - CELL_TYPE_BLANK
  • 4 - CELL_TYPE_BOOLEAN
  • 5 - CELL_TYPE_ERROR
  • --->
  •  
  • <!--- Get the cell from the row object. --->
  • <cfset objCell = objRow.GetCell(
  • JavaCast( "int", intCell )
  • ) />
  •  
  • <!--- Get the type of data in this cell. --->
  • <cfset objCellType = objCell.GetCellType() />
  •  
  • <!---
  • Get teh value of the cell based on the data type.
  • The thing to worry about here is cell forumlas and
  • cell dates. Formulas can be strange and dates are
  • stored as numeric types. For this demo, I am not
  • going to worry about that at all. I will just grab
  • dates as floats and formulas I will try to grab as
  • numeric values.
  • --->
  • <cfif (objCellType EQ objCell.CELL_TYPE_NUMERIC)>
  •  
  • <!---
  • Get numeric cell data. This could be a
  • standard number, could also be a date value.
  • I am going to leave it up to the calling
  • program to decide.
  • --->
  • <cfset objCellValue = objCell.GetNumericCellValue() />
  •  
  • <cfelseif (objCellType EQ objCell.CELL_TYPE_STRING)>
  •  
  • <cfset objCellValue = objCell.GetStringCellValue() />
  •  
  • <cfelseif (objCellType EQ objCell.CELL_TYPE_FORMULA)>
  •  
  • <!---
  • Since most forumlas deal with numbers, I am
  • going to try to grab the value as a number. If
  • that throws an error, I will just grab it as a
  • string value.
  • --->
  • <cftry>
  • <cfset objCellValue = objCell.GetNumericCellValue() />
  •  
  • <cfcatch>
  •  
  • <!---
  • The numeric grab failed. Try to get the
  • value as a string. If this fails, just
  • force the empty string.
  • --->
  • <cftry>
  • <cfset objCellValue = objCell.GetStringCellValue() />
  •  
  • <cfcatch>
  •  
  • <!--- Force empty string. --->
  • <cfset objCellValue = "" />
  •  
  • </cfcatch>
  • </cftry>
  •  
  • </cfcatch>
  • </cftry>
  •  
  • <cfelseif (objCellType EQ objCell.CELL_TYPE_BLANK)>
  •  
  • <cfset objCellValue = "" />
  •  
  • <cfelseif (objCellType EQ objCell.CELL_TYPE_BOOLEAN)>
  •  
  • <cfset objCellValue = objCell.GetBooleanCellValue() />
  •  
  • <cfelse>
  •  
  • <!--- If all else fails, get empty string. --->
  • <cfset objCellValue = "" />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • ASSERT: At this point, we either got the cell value
  • out of the Excel data cell or we have thrown an
  • error or didn't get a matching type and just
  • have the empty string by default. No matter what,
  • the object objCellValue is defined and has some
  • sort of SIMPLE ColdFusion value in it.
  • --->
  •  
  •  
  • <!---
  • Now that we have a value, store it as a string in
  • the ColdFusion query object. Remember again that my
  • query names are ONE based for ColdFusion standards.
  • That is why I am adding 1 to the cell index.
  • --->
  • <cfset qCell[ "column#(intCell + 1)#" ][ qCell.RecordCount ] = JavaCast( "string", objCellValue ) />
  •  
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • At this point, the excel data should be in a ColdFusion
  • query object. However, if the query did not contain any
  • record, then the row iterator was never launched which
  • mean we never actually defined a query. As one final check
  • just make sure we are dealing with a query.
  • --->
  • <cfif NOT IsQuery( qCell )>
  •  
  • <!--- Just define an empty query. --->
  • <cfset qCell = QueryNew( "" ) />
  •  
  • </cfif>

Taking this Excel file:


 
 
 

 
Reading Excel Files Using ColdFusion And POI  
 
 
 

... and then CFDump'ing it out, we get:


 
 
 

 
Reading Excel Files Into A ColdFusion Query Using POI  
 
 
 

This is pretty exciting! I am really liking this POI library. Rob Brooks-Bilson tells me that the version of POI that ships with ColdFusion is a slightly older one. I can only hope that the upcoming versions of ColdFusion continue to ship with Microsoft Excel solutions.

POI aside, I am still hoping to get a JExcel sample up and running. Mark Mandel has been nice enough to help me out with that, but I just haven't had time to go back and implement his advice. As you probably notice, I have about a 1000 things going on in my head and it just pours out when ever I can get a few minutes here and there.

Tweet This Deep thoughts by @BenNadel - Reading Excel Files With ColdFusion And POI Thanks my man — you rock the party that rocks the body!



Reader Comments

I played around with this a few weeks ago, as well.
I didn't really get that far into it, because of paying work that needed to get done, but I created a small ColdFusion function that takes as an input a path to an excel file and the number of columns to pull in each worksheet.
It returns a Structure of CF Query objects containing the data from the excel spreadsheet. I didn't go into the detail you have with the different cell types, and your work is a bit more elegant, but combining your work with the structure aspect from my function would yield a powerful data import tool.
You can see my [ uncommented :( ] function here: http://ken.auenson.com/examples/func_ExcelToQueryStruct_POI_example.txt

Reply to this Comment

Ben:

Can you clarify that that POI library you are using is not the one that comes with CF? If so, I'm assuming you had to load this library into CF's class path.

For my work sites, I have access to the server. But for my personal site, I'm hosted by a web hosting company that doesn't allow us to add Java packages.

Bruce

Reply to this Comment

@Ken,

That stuff looks good. I like the way that you are looping over the sheets and returning them in an array. I have some ideas that could perhaps improve up on it (such as returning an array of sheets structures, each that would have keys for things like "Name", "Query", "Index"... or something). I will try to merge our two ideas together and make a wicked awesome love-child.

@Bruce,

As far as I know, this IS the POI package that ships with ColdFusion MX 7. Other people have been working with JExcel, which I know has to be installed. No one here (at my office) knew about POI until Rob pointed it out to me... meaning, we have not installed anything extra as far as I know. When in doubt, try to run this line:

<cfset CreateObject( "java", "java.lang.StringBuffer" ).Init() />

If that can run without error then you know two things:

1. CreateObject() works
2. You can directly create installed Java packages.

If you can do that, then try to cut-n-paste the demo above. I assume that as long as you are running on a recent enough edition of ColdFusion then you should be good to go.

But again, this is my first time ever reading via POI and ColdFusion, so I am still quite wet behind the ears.

Reply to this Comment

@Bruce:
My function was using the built-in POI library as well.

@Ben:
That sounds like a great idea.
The only piece I am not happy with right now is that I can't seem to see a nice way to determine the number of columns dynamically. Maybe what would be required would be to loop over every row in a given sheet one time before doing the real work to determine the max columns on that sheet. That would iliminate my solution of requiring a column input, and your solution of retrieving the first row's number of columns.
This could turn out to be a really useful tool! I wish I had more time to spend on it. Good luck, and keep us posted!

Reply to this Comment

A long time ago (back in CF5), I remember using COM objects to pull data out of an excel file. I was very tedious and SLOWWWW. Don't know how fast this thing is under load, but the COM objects I used sucked. I think really it had to do with a CF5 bug, but can't really remember. Anywho, the way I got around it was to use COM to basically open up the excel file and then save it to a TAB delimited file. Then I could just use go old CF to read the TAB delimited file and throw it into a query.

Reply to this Comment

Tony,

That's a pretty clever idea regarding the TAB file saving. I have just started working with POI so I cannot testify as to the speed of it... but I hear very good things. Hopefully, I will know more soon.

Reply to this Comment

Ken,

I was just trying to put some code down on paper, doing a what you suggested (going through the sheet once to find the number of columns) when it dawned on me... who care?!?

This is what occurred to me: what ever part of the application uses the query that comes out of the Excel sheet is going to know the format of the excel sheet. Meaning, they are going to assume column 1 is last name and column 2 is first name (for example). The structure of the Excel is set for the calling app, not dynamic.

Because the structure is set, no one is every going to treat the query structure as dynamic. This makes extra columns irrelevant. If there is an extra 6th column passed to a part that expects 5 column, it doesn't matter because most likely that 6th column will never be reference.

I am going to go an take out the logic I put in for checking the column count as I think it is going to add more overhead and complicate things more than is worth it.

Reply to this Comment

Ben:
Good point. I didn't actually think of the use cases before writing my last comment, but your right. anyone using this code will already know the structure of the spreadsheet.

Reply to this Comment

Ken,

I was thinking the same exact thing you were. I even played around with the excel, adding and clearing cells vs. "Delete"ing a column altogether. But then as I was writing the code it just didn't seem that useful (and was making my code more complicated than it needed to be).

I am done writing the "Reading" part of a small CFC, POIUtility.cfc. I just want to add the Write (query to excel) and then I will post up. Hopefully I can do this at lunch.

I am borrowing for you ideas and adding to them. More to come soon.

Reply to this Comment

Matt,

It is written. I am just writing up the entry and example. I will try to finish it right after work. If not then, first thing in the morning.

Reply to this Comment

If I understand this correctly, this is for apache. Is there a way to do it with IIS?

Reply to this Comment

CJ,

This is for ColdFusion. I don't think it has anything to do with the server (but I am NOT a server guy, so this may be misinformed). I am working with Windows Server and IIS and all is going swimingly.

Reply to this Comment

Ben, I'll recommend you to know about JExcel Java libraries. This is a great great piece of code. The ntegration with CF is very easy. I'm using JExcel(http://jexcelapi.sourceforge.net/) and I'm very happy with your power. Please look this simple example:
<cfset inicio=GetTickCount()>

<!--- armazenar a biblioteca jxl.jar no diretorio c:\cfusionmx7\lib e restartar o servidor --->

<cfset filepath = "C:\Manufact\200701_Fat.xls">
<cfscript>
//carrega o metodo Java que manuseia o arquivo xls
wfile = createObject("java","java.io.File").init(filepath);

//cria o workbook e carrega a planilha
workbook = createObject("java","jxl.Workbook");

//pega o workbook
wworkbook = createObject("java","jxl.write.WritableWorkbook");
wworkbook = workbook.getWorkbook(wfile);

sheet = createObject("java","jxl.write.WritableSheet");
//pega uma planilha especifica
sheet = wworkbook.getSheet("FaturamentoMaior");
</cfscript>

<cfset rowslen = sheet.getRows()>

<cfset columnslen = sheet.getColumns()>

<!--- captura o texto das colunas da planilha --->
<cfoutput>
<cfloop from="0" to="#columnslen-1#" index="c">
#sheet.getCell(javacast('int',c),0).getContents()#
<br />
</cfloop>
</cfoutput>

<cfoutput>
<cfloop from="0" to="#rowslen-1#" index="q">
#sheet.getCell(0,javacast('int',q)).getContents()# | #sheet.getCell(1,javacast('int',q)).getContents()# | #sheet.getCell(2,javacast('int',q)).getContents()#
<br />
</cfloop>
</cfoutput>
Tempo de execução: <cfdump var="#GetTickCount()-inicio#">ms

Reply to this Comment

Marco,

I have fooled around a bit with JExcel. It is very cool but once I found out that POI was already delivered with ColdFusion MX 7 I kind of took a detour. When I get a better footing on POI, I will surely come back and continue to look at JExcel. Thanks for you example above.

Keep on rockin!

Reply to this Comment

There's a rudimentary google code project called cfjexcel, that is an attempt at making using JExcelAPI easier.

You do need access to createObject, but it's got some basic row-level formula embedding, which users always love. Might be at least a good place for ideas.

Here's a demo of the formula stuff, and some basic IO:

http://coldshen.com/cfjexcel/test.cfm

Reply to this Comment

Your code is littered with magic numbers and non-descript variable names, and is absolutely overkilled with comments. Quality code shouldn't need nearly so many comments. It should be self-explanatory.

Reply to this Comment

@AJ,

I am always looking for ways to improve my code. Could you give me an example of the "magic numbers" and non-descript variables names and maybe some suggestions? Thanks!

Reply to this Comment

Ben,

How do I grab dates that are stored in non-US format in the Excel file and store them in US 'mm-dd-yyyy' format in the database?

I have an Excel file that has about 400 records which I have to parse data out of. One of the columns in the file has the dates that range from 01/26/2007 stored simply in the file as '26-Jan' in the file and go up to '02/07/2007' also stored as '07-Feb'.

How do I grab these non-US format dates and convert them to the proper US format and store them in the DB?

Thanks!!

Reply to this Comment

@Brian,

I don't know much about non-US dates. I usually just grab the date value and format it with ColdFusion. I have not had to deal with non-US dates. Sorry.

Reply to this Comment

no problemo Ben

Thanks Matthew. After checking out the DateUtil Class, I think I might find something there that will help me get this done.

Reply to this Comment

I am successfully using this code to read an Excel file, but when I wish to run a query on the returned query object, I am constantly getting errors.

Code I am using:

<cfquery name="chkFieldSize" dbtype="query">
SELECT *
FROM qCell
</cfquery>

Error message:

"The value '' cannot be converted to a number" on the above.

Any reason why I cannot run a query on the query object? And checking the fieldtypes, they are all returned as varchar.

Reply to this Comment

@Daniel,

The problem is probably that ColdFusion does some data type guessing when it runs query of queries. It will look at the column and guess what kind of data you are using; then, if a value farther down in the column doesn't comply, it throws an error.

Reply to this Comment

I am trying to use the POI, and the problem I am having is with Null values. For instance if you use your example and delete 2006 from the second cell of Jenna Depraved. The code throws and error. Because it is expecting an int or something, but nothing is there. Help! It errors on (objCellType EQ objCell.CELL_TYPE_NUMERIC) with the following error.
Variable OBJCELL is undefined.
I actually think this is the culprit.
objCellType = objCell.GetCellType()
Any help would be appreciated.

Reply to this Comment

@Dana,

After you call the GetCell() method, you probably have to check to see if the cell you got actually exists. You can do this by checking to see if the call to GetCell() destroyed the variable into which it was being stored:

<cfset objCell = objRow.GetCell( ... ) />

<!--- Check to see if cell exists. --->
<cfif structKeyExists( variables, "objCell" )>
....
</cfif>

Reply to this Comment

Is there a way to set the color of a cell if its under a certain value. If the value in column c is below 3, is there a way to set the cell color to red? Thanks for answering my previous question.

Reply to this Comment

I am trying to use your code for one of my projects and trying to dump the value of the query on page but I am not getting anything. I have tried using false file names but it still doesn't throw any error, just blank page.

Reply to this Comment

@Omer,

Is there any info in the file. Are you looking at the correct directory for the file. I've used it and it works great.

Reply to this Comment

yes, there is data in my excel file but I don't see anything on the page. I have gone through the source code and it's empty.

Reply to this Comment

@Dana,

Do set the BG color on a cell based on the value, you'd probably have to use the Custom Tags in my POI library, conditionally adding a CSS class to the given Cell.

@Omer,

Hmm, that's really odd. It sounds like there might be a CFAbort or something in the top of one of the files??

Reply to this Comment

I am trying to figure out how to get the color types correctly, I cannot seam to do any lookup from style.getFillForegroundColor() or style.getFillBackgroundColor().

Also the answer to the date question if it is a number test it using the date utility.

Created it here..
<cfset oHSSFDateUtil = createObject("java","org.apache.poi.hssf.usermodel.HSSFDateUtil") />

Then test the cell itself

<cfif oHSSFDateUtil.isCellDateFormatted(cell)>
#cell.getDateCellValue()#
<cfelse>
#cell.getNumericCellValue()#
</cfif>

--------
Also, I don't think your code has to many comments it was a good read.

Reply to this Comment

@Lucas,

I am not sure about reading type of colors. It will probably just return the sub-class for the given type of color.

Reply to this Comment

@Dana,

To do landscape you will have to get to the POI PrintSetup. After the objSheet has been created, you can do it this way. Try the code below.

<cfset pageSetup = objSheet.getPrintSetup() />
<cfset pageSetup.setLandscape(true) />

Reply to this Comment

@Matthew Abbott,

Thanks it worked. I got it to work in one line also.

<cfset objSheet.getPrintSetup().setLandscape(true)>

With objSheet being the name of my excel Sheet.

Thanks alot.

Reply to this Comment

This works great when I hard code the file name but how do I pass the file name as a variable? I keep getting an error:

An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

The error occurred in D:\Websites\cdicorp61\Recruiters\SuperAdmin\Talx\readfile.cfm: line 16

14 : ).Init(
15 :
16 : ExpandPath( "#form.filename#" )
17 :
18 : )

Reply to this Comment

Hi Ben,

When I read my excel file I got everything fine except the dates I got all dates as INT example: 38934 this should be 08/06/2010

If I do a dateformat I will get Saturday, August 5, 2006

Any help

Regrads
Jorge

Reply to this Comment

@Jorge,

Instead of using int as the dates, why dont you use date/time. Or some sort of time format. You can always get the format of 08/06/2010. By using mm/dd/yyyy.

Reply to this Comment

@Dana,

Thanks for the comment, I alrready try that but I think is the dateformat of MS Excel for MAC OSX.

The only way it will work is by converting the column as TEXT that will keep the dateformat.

Jorge

Reply to this Comment

Hi Ben:

Yet another reason your blog is near the top of my bookmarked pages. I successfully incorporated your code example into an app importing data via submitted .xls pages. I'm running into a single issue but it has nothing to do with your code.

The .xls files that are being submitted are of an older Excel type (Excel 5.0 and not the 97/2000/XP format your example manages so well) and apparently POI can't read those.

Is there anything you're aware of that can take an Excel 5.0 file and convert it to 97/2000/XP on the fly? I've consulted the mighty Google but it has spurned me.

Thanks again for the blog entry. My bacon...it has been saved many times here. :)

Reply to this Comment

@ Jorge : Regarding the 4 digit excel date format, it is Julian encoded.

Try dateadd("d",THEVARIABLEYOUNEEDTOCONVERT,createdate(1900,1,1))

or

dateformat(dateadd("d",THEVARIABLEYOUNEEDTOCONVERT,createdate(1900,1,1)), "mm/dd/yyyy") for output

or

createodbcdate(dateadd("d",THEVARIABLEYOUNEEDTOCONVERT,createdate(1900,1,1))) if you are inserting it into a database.

That should do the trick.

Reply to this Comment

I believe that it is actually Serial date encoded (Julian encoded is since January 1, 4713 BC.

The number of days is since 1900-01-00 so that 1900-01-01 = 1. Also, if you have an integer, the days may well have been rounded UP from a decimal value. Therefore, the following may work better for you:

DateAdd('d', YourDate-2, CreateDate(1900,1,1))

Dominic

Reply to this Comment

@Ben and @AJ,

I have heard about the comments before, but I personally like the code to be commented, as I do not have many, many years as a CF developer, and appreciate the explanation. Also, there may be something new you are doing that I don't really know too much about, and the comments are always nice for ME. Just adding my 2 cents in there...I am fine with some of the code not having the comments, if it really is self-explanatory, but not all code can be, and there are some things that you can't effectively write in a "self-explanatory" way, and I like to see comments in those places. Also, there are some things that may SEEM self-explanatory to a seasoned developer with many, many years writing CF code, but for someone like me, who doesn't have quite so many years of writing code, I appreciate the comments and like the code to be commented. Anyway...just adding my input there. Thanks...

Reply to this Comment

Just wanted to share this in case anyone else ran into the same issue. If you'd rather the above code return Java formatted date values under the "ReadExcelSheet" function, change this:

For the sake of brevity and avoiding posting a huge chunk of code, you'll need to include the POI DateUtil class. I did this toward the top of the function.

  • // Define the local scope.
  • var LOCAL = StructNew();
  •  
  • // JP: Load DateUtil class.
  • LOCAL.DateUtil = CreateObject("java", "org.apache.poi.ss.usermodel.DateUtil");

Then change this bit of code...

  • // Get numeric cell data. This could be a standard number,
  • // could also be a date value. I am going to leave it up to
  • // the calling program to decide.
  • LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();

to...

  • if (LOCAL.DateUtil.isCellDateFormatted(LOCAL.Cell)) {
  • LOCAL.CellValue = LOCAL.DateUtil.getJavaDate(LOCAL.Cell.GetNumericCellValue());
  • } else {
  • // Get numeric cell data. This could be a standard number,
  • // could also be a date value. I am going to leave it up to
  • // the calling program to decide.
  • LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();
  • }

Hope this helps!

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.