I am soaking up your work with Excel spreadsheets. I am stuck on this question. I have followed your instructions for creating the multi sheet spreadsheet on meals and I can read the spreadsheet using your POIutility and I can dump out the array. I want to be able to use the data from the spreadsheet and write it into a database. You wrote "all values from the Excel are stored in the resultant ColdFusion queries as CF_SQL_VARCHAR values" - how do I access these?
Getting the data from an Excel using my POI Utility ColdFusion component is just a matter of understanding how the data gets read in from the Excel and how to access it once it is read in. The POI Utility reads each sheet of an Excel workbook into a "Sheet" object. All of these sheet objects are returned in a single array that looks like this (using my previous Meals example):
As you can see each index of the above array contains a single sheet object that looks like this:
Within that sheet object, there is the Query object (struct key: Query). This is the query object that contains the actual grid data from the Excel sheet. Now, taking that query object and using it to insert into a database could be done as follows:
<!--- Create a new instance of the POI utility. ---> <cfset objPOIUtility = CreateObject( "component", "POIUtility" ).Init() /> <!--- Get the path to our Excel document. Our Excel document workbook contains three sheets with information regarding three meals (Breakfast, lunch, and dinner). ---> <cfset strFilePath = ExpandPath( "./meals.xls" ) /> <!--- Read the Excel document into an array of Sheet objects. Each sheet object will contain the data in the Excel sheet as well as some other property-type information. We are telling the POI Utility to expect the first row of the Excel document to function as a header row. ---> <cfset arrSheets = objPOIUtility.ReadExcel( FilePath = strFilePath, HasHeaderRow = true ) /> <!--- We want to get the data from the Excel document into our local database. The first thing we are going to want to do is loop over each sheet and attach each set of data individually. ---> <cfloop index="intSheet" from="1" to="#ArrayLen( arrSheets )#" step="1"> <!--- Let's get a pointer to the current sheet object. We could continue to refer to the sheet as an index of the sheets array, but this is more convenient and breaks it up into easier to read code. ---> <cfset objSheet = arrSheets[ intSheet ] /> <!--- The data from the excel object is stored in a query within this "sheet" object and can be accessed at the key "query." For ease of use and short hand, let's get a pointer to that query. ---> <cfset qSheetData = objSheet.Query /> <!--- We can treat this query just like any old ColdFusion query because it is just a plain old ColdFusion query. Let's loop over it to get at each row. NOTE: Since we told the POI Utility to use the first row as a header row, the first row has already been stripped off and returned as part of the sheet object. We will not encounter it in THIS query. ---> <cfloop query="qSheetData"> <!--- For ease of demonstration, let's get the values out of the query that we want to use for our database insert. This is step that is not required, but helps clarify how things are working. NOTE: Remember that the POI Utility auto names the columns as it encounters them as COLUMN1, COLUMN2, COLUMN3, .... etc. We are throwing the second column (quantity) into a Val() method call as we need to get it as a number. The POI Utility reads in everything as a string. ---> <cfset strFood = qSheetData.column1 /> <cfset flQuantity = Val( qSheetData.column2 ) /> <cfset strTastiness = qSheetData.column3 /> <!--- Now that we have the values we are going after, we can insert them into our database. ---> <cfquery name="qInsert" datasource="#App.DSN.Source#"> INSERT INTO food_diary ( meal, food, quantity, tastiness, date_created ) VALUES ( <cfqueryparam value="#objSheet.Name#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#strFood#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#flQuantity#" cfsqltype="CF_SQL_FLOAT" />, <cfqueryparam value="#strTastiness#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" /> ); </cfquery> </cfloop> </cfloop>
At this point, each record of each query of each sheet has been inserted into the local database. I hope that that helps. Please let me know if you have any further questions.
Want to use code from this post? Check out the license.