20 Replies Latest reply on May 9, 2007 3:08 PM by gr8white

    CFX_excel - reading excel using coldfusion

    baskark
      hi everyone,
      i got a form through with i upload the excel file.
      these excel files are parsed through cfx_excel tags.

      is there any other instead of using cfx_excel query to read the values from excel sheet?

      thanks
        • 1. Re: CFX_excel - reading excel using coldfusion
          Level 7
          baskark a écrit :
          > hi everyone,
          > i got a form through with i upload the excel file.
          > these excel files are parsed through cfx_excel tags.
          >
          > is there any other instead of using cfx_excel query to read the values from
          > excel sheet?

          >
          > thanks
          >
          >
          You can do that witdh a datasource directly
          (i don't know what cfx_excell is )

          JiBé
          • 2. Re: CFX_excel - reading excel using coldfusion
            MikerRoo Level 1
            If the filename is constant, you can set up an odbc datasource and use that in the CF administrator.
            This requires Excel to be installed in the server which is a huge security hole.

            There is Apache POI, which you have been introduced to. With POI and a little java skill, there is nothing you can't do with an excel file.

            You can also set up an Access or MS SQL database to link to the excel file.

            Finally, if the excel file is saved as CSV, it can be parsed by most DB's or you can read it in and parse it yourself.

            Anyway, cfx_excel works for many people's needs.
            Why didn't it work for you?
            • 3. Re: CFX_excel - reading excel using coldfusion
              baskark Level 1
              Hi miker roo,
              The cfx_excel tags is working fine.but iam unable to validate the header and column values.
              Is there any solution for this kind of problem.
              Is there any sample solution for this please post it in the forum..

              thanks for your valuable infofmation

              • 4. Re: CFX_excel - reading excel using coldfusion
                MikerRoo Level 1
                What do you mean you are unable to validate the header and column values?

                Do you mean that you want the first row to become the query column name?
                • 5. Re: CFX_excel - reading excel using coldfusion
                  baskark Level 1
                  hi MikerRoo,
                  my first row is my header instead of excel cell header.but i overcome this problem by skkiping the first row. this is fine .
                  Problem
                  i got 3 rows each row contains 4 columns
                  in 2nd row 2nd column is null. instead of displaying null. it takes the next column value and displays it (2nd row 3rd column value) and the last column becomes null
                  i use this command

                  <cfx_ExcelQuery action = "read" file="#file_url#" sheet="0" variable="myExcel" />
                  <cfoutput>
                  <cfdump var = "#myExcel#">
                  </cfoutput>


                  is there any possible solution .
                  thanks for you valuable information
                  • 6. Re: CFX_excel - reading excel using coldfusion
                    MikerRoo Level 1
                    Yes, I see that this is a design flaw in the cfx_ExcelQuery code.

                    If you can wait a few days, I will fix this, and some other bugs, in that CFX.

                    Can you provide me a link to the kind of files you wish to read? (You can PM me if needed.)

                    • 7. Re: CFX_excel - reading excel using coldfusion
                      baskark Level 1
                      hi mike
                      please post your email id .
                      i will send my excel files to you...


                      thanks and regards
                      baskar k
                      • 8. Re: CFX_excel - reading excel using coldfusion
                        MikerRoo Level 1
                        Ok. PM'd you with my email address.
                        • 9. Re: CFX_excel - reading excel using coldfusion
                          di_wakar
                          Hi every one,
                          to read a excel i am using cfx_ExcelQuery tag, but it doesn't support blank cell..any idea?
                          • 10. Re: CFX_excel - reading excel using coldfusion
                            MikerRoo Level 1
                            Actually, I stumbled on CFX_Excel2Query.

                            This is another tag that supposedly works much better than cfx_ExcelQuery.

                            Since (1) I don't need this functionality, (2) The solution already exists, (3) I have other projects that are just as much fun, and (4) a valued CF developer is charging (a reasonable price) for his working tag; I will not be fixing cfx_ExcelQuery, for free, in the next few days.

                            CFX_Excel2Query is at: http://www.emerle.net/programming/display.cfm/t/cfx_excel2query

                            • 11. Re: CFX_excel - reading excel using coldfusion
                              cf_dev2
                              Strange, I've used the tag and haven't had a problem with blank cells. Maybe one of our developers modified it? I'll try to get the source and post it if anyone is interested. I haven't used CFX_Excel2Query but it looks good .. seems to have richer features.
                              • 12. Re: CFX_excel - reading excel using coldfusion
                                di_wakar Level 1
                                Hi all,

                                We have many methods to read an excel sheet…
                                A. Save it as .csv & read as file (but coding is too much & if you cell have any comma separated value it cause a problem)
                                B. Create ODBC & than DSN inside of CF admin (it is also painful if you want to read different type of excel.)
                                C. There is some paid custom tag available on net (you need to pay much amount & some limitations are there)
                                D. Last & final method is use Jakarta POI (some already guess about it)

                                I use Jakarta POI…the problem is that with these POI that there is not enough material or documentation is available on net…I am lucky I did much R &D & get the solution.

                                With using these POI our most of requirement’s get fulfill…see the sample code to read an excel sheet without creating a DSN


                                <cfset fileIn = createObject("java","java.io.FileInputStream").init("#file_url#")/>

                                <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init("#fileIn#")/>

                                <cfset sheet = wb.getSheetAt(0)/> //this is sheet number you can read multiple sheet..

                                <cfset Last_Row = sheet.getLastRowNum()>// you will get number of last row to validate number of row

                                <cfloop index="i" from="1" to="#Last_Row#">
                                <cfset row = sheet.getRow(javacast("int",#i#))>
                                <cfloop index="j" from="0" to="18">
                                <cfoutput>
                                <cfset Cell_len=#len(row.getCell(javacast("int",j)))#>
                                <cfif #Cell_len# GT 0>//to validate null value(blank cell)
                                <cfset cell_Add=row.getCell(javacast("int",j))>
                                <cfset "c#j#"=cell_Add.getNumericCellValue()>// to read numenric value
                                <cfset "c#j#"=cell_Add.getStringCellValue()>// to read string value
                                <cfelse>
                                <cfset "c#j#"="NULL">
                                </cfif>
                                </cfoutput>
                                </cfloop>
                                </cfloop>

                                Now a tip: use CFdump for wb, sheet, row you will able to see all related function.

                                I hope this small description will provide you enough help…if any one have any question feel free to contact me…

                                Thanks & Regards
                                Diwakar Gupta
                                • 13. Re: CFX_excel - reading excel using coldfusion
                                  rizalfir
                                  If you're interested to get the fastest, small memory footprint and easiest way to convert CF query to Excel and vice versa, you can try my custom tag:
                                  http://www.masrizal.com/index.cfm?fuseaction=idea.download_detail&ProductID=cfx_excel

                                  Well, it's not free though :)
                                  But it's way cheaper than our hourly rate, considering to develop this kind of rich functions will take many days...
                                  • 14. Re: CFX_excel - reading excel using coldfusion
                                    MikerRoo Level 1
                                    Nice site.
                                    And the online livedemo (nice!) shows that your tag does solve the "null cell" problem that baskark was posting about.
                                    • 15. CFX_excel - reading excel using coldfusion
                                      gr8white Level 1
                                      I ran into that flaw and we fixed it in our local version.

                                      I can give you the modified .jar if you're interested.
                                      If anyone has any problem w/ me doing this please let me know. Don't want to step on any toes.

                                      Ken

                                      • 16. Re: CFX_excel - reading excel using coldfusion
                                        baskark Level 1
                                        Hi everyone,
                                        thanks for your support.
                                        We solved the problem not by changing any of those file instead
                                        we wrote a cfscript there we generated the query dynamically.
                                        we used those query for further process.


                                        thanks for your wonderfull support.

                                        • 17. Re: CFX_excel - reading excel using coldfusion
                                          baskark Level 1
                                          Hi everyone,
                                          thanks for your support.
                                          We solved the problem not by changing any of those file
                                          instead we wrote a cfscript there we generated the query dynamically.
                                          we used those query for further process.


                                          thanks for your wonderfull support.

                                          • 18. Re: CFX_excel - reading excel using coldfusion
                                            baskark Level 1
                                            hi every one .
                                            i tried to upload an excel file using apache poi.jar in my coldfusion page.
                                            i succesfully uploaded the file buf after doing more than 10 time it shows me an error like this .
                                            can any one guide me to solve this error



                                            11:42:59.059 - Application Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/fusebox4.loader.cfmx .cfm : line 97
                                            The specified Directory attribute value /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/parsed could not be created.
                                            11:42:59.059 - Application Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/fusebox4.loader.cfmx .cfm : line 101
                                            The specified Directory attribute value /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/plugins could not be created.
                                            11:42:59.059 - fusebox.LoadUnneeded Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/fusebox4.loader.cfmx .cfm : line 178
                                            The full XML load is unneeded because the in-memory copy is fully up to date
                                            11:43:00.000 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:00.000 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:01.001 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:01.001 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:01.001 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:02.002 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:02.002 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:02.002 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:02.002 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:03.003 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.
                                            11:43:03.003 - Database Exception - in /apps1/WebALC_domain/WebALCQA1/WebALCQA1/cfusion-ear/cfusion-war/fb4/model/dabek/engine_f .cfc : line 512
                                            Error Executing Database Query.


                                            thanks for valuable information


                                            • 19. Re: CFX_excel - reading excel using coldfusion
                                              baskark Level 1
                                              hi gr8white,

                                              ' can give you the modified .jar if you're interested.

                                              please provide me the link of the updated jar file.
                                              thanks
                                              • 20. Re: CFX_excel - reading excel using coldfusion
                                                gr8white Level 1
                                                baskark,

                                                You can download the file from http://www.gr8white.com/java/javacfx.exe. (it is actually a .jar file, I renamed it to .exe to facilitate downloading it)

                                                If you have already installed the custom tag, place that file in the same directory as the current jar file. Rename the existing one just in case, then rename the new one to .jar and restart CF.

                                                Ken