0 Replies Latest reply on Dec 13, 2006 3:41 AM by di_wakar

    Reading an excel

      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">
      <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
      <cfset "c#j#"="NULL">

      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