Migration Solutions for ColdFusion Applications to ASP.NET
      
New Atlanta Product Forums Profile | Search | Login | RSS
New Topic Reply   Previous Page  Page: 1   Next Page

Thread: Converting an excel doc to a csv
Created on: 12/08/14 03:01 PM Replies: 1
CavlinClick59


Joined: 12/08/14
Posts: 1
Converting an excel doc to a csv
12/08/14 3:01 PM

Anyone have any suggestions? I would greatly appreciate it. I know there is a CFSpreadsheet tab but its not available in the latest version of BD.
Link | Top | Bottom
Peter


Joined: 03/31/08
Posts: 22
RE: Converting an excel doc to a csv
01/13/16 4:44 AM

Calvin,

A bit late to reply but you can use the Java libraries from https://poi.apache.org/ You can use the JavaLoader or place the JAR files in the C:\BlueDragon_Server_JX_71\lib folder or equivalent. Depending on version, the JAR files will have names similar to
poi-dom4j-16.jar
poi-ooxml-schemas-39.jar
poi-xls-39.jar
poi-xlsx-39.jar
poi-xmlbeans-23.jar

Then the CF code to read the XLSX or XLS file is like this example

<cfscript>

   function ReadExcel(FilePath, HasHeaderRow, SheetIndex) {

      var LOCAL = StructNew();

      LOCAL.FileInputStream = CreateObject("java", "java.io.FileInputStream" ).Init(ARGUMENTS.FilePath);

      LOCAL.WorkBookFactory = CreateObject("java","org.apache.poi.ss.usermodel.WorkbookFactory");

      LOCAL.WorkBook = LOCAL.WorkBookFactory.create(LOCAL.FileInputStream);

      if (ARGUMENTS.SheetIndex GTE 0) {

         LOCAL.Sheets = ReadExcelSheet(LOCAL.WorkBook, ARGUMENTS.SheetIndex, ARGUMENTS.HasHeaderRow);

      } else {

         LOCAL.Sheets = ArrayNew(1);

         for (LOCAL.SheetIndex = 0 ; LOCAL.SheetIndex LT LOCAL.WorkBook.GetNumberOfSheets() ; LOCAL.SheetIndex = (LOCAL.SheetIndex + 1)) {

            ArrayAppend(LOCAL.Sheets, ReadExcelSheet(LOCAL.WorkBook, LOCAL.SheetIndex, ARGUMENTS.HasHeaderRow));

         }

      }

      LOCAL.FileInputStream.Close();

      return(LOCAL.Sheets);

   }



   function ReadExcelSheet(WorkBook, SheetIndex, HasHeaderRow) {

      var LOCAL = StructNew();

      LOCAL.SheetData = StructNew();

      LOCAL.SheetData.Index = ARGUMENTS.SheetIndex;

      LOCAL.SheetData.Name = ARGUMENTS.WorkBook.GetSheetName(JavaCast("int", ARGUMENTS.SheetIndex));

      LOCAL.SheetData.Query = QueryNew("");

      LOCAL.SheetData.HasHeaderRow = ARGUMENTS.HasHeaderRow;

      LOCAL.SheetData.ColumnNames = ArrayNew(1);

      LOCAL.SheetData.MinColumnCount = 0;

      LOCAL.SheetData.MaxColumnCount = 0;

      LOCAL.Sheet = ARGUMENTS.WorkBook.GetSheetAt(JavaCast("int", ARGUMENTS.SheetIndex));

      for (LOCAL.RowIndex = 0 ; LOCAL.RowIndex LTE LOCAL.Sheet.GetLastRowNum() ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)) {

         LOCAL.Row = LOCAL.Sheet.GetRow(JavaCast("int", LOCAL.RowIndex));

         if (StructKeyExists(LOCAL, "Row")) {

            LOCAL.ColumnCount = LOCAL.Row.GetLastCellNum();

            LOCAL.SheetData.MinColumnCount = Min(LOCAL.SheetData.MinColumnCount, LOCAL.ColumnCount);

            LOCAL.SheetData.MaxColumnCount = Max(LOCAL.SheetData.MaxColumnCount, LOCAL.ColumnCount);

         }

      }   

      for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE LOCAL.SheetData.MaxColumnCount ;   LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)) {

         QueryAddColumn(LOCAL.SheetData.Query, "column#LOCAL.ColumnIndex#", "CF_SQL_VARCHAR", ArrayNew(1));

      }

      for (LOCAL.RowIndex = 0 ; LOCAL.RowIndex LTE LOCAL.Sheet.GetLastRowNum() ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)) {

         LOCAL.Row = LOCAL.Sheet.GetRow(JavaCast("int", LOCAL.RowIndex));

         if (LOCAL.RowIndex OR ((NOT ARGUMENTS.HasHeaderRow) AND StructKeyExists(LOCAL, "Row"))) {

            QueryAddRow(LOCAL.SheetData.Query);

         }            

         if (StructKeyExists(LOCAL, "Row")) {

            LOCAL.ColumnCount = LOCAL.Row.GetLastCellNum();

            for (LOCAL.ColumnIndex = 0 ; LOCAL.ColumnIndex LT LOCAL.ColumnCount ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)) {

               if (ARGUMENTS.HasHeaderRow AND (NOT LOCAL.RowIndex)) {

                  try {            

                     ArrayAppend(LOCAL.SheetData.ColumnNames, LOCAL.Row.GetCell(JavaCast("int", LOCAL.ColumnIndex)).GetStringCellValue());

                  } catch (any ErrorHeader) {

                     ArrayAppend(LOCAL.SheetData.ColumnNames,"");

                  }

               } else {

                  LOCAL.Cell = LOCAL.Row.GetCell(JavaCast("int", LOCAL.ColumnIndex));

                  if (StructKeyExists( LOCAL, "Cell" )) {

                     LOCAL.CellType = LOCAL.Cell.GetCellType();

                     if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_NUMERIC) {

                        LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();

                     } else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_STRING) {

                        LOCAL.CellValue = LOCAL.Cell.GetStringCellValue();

                     } else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_FORMULA) {

                        try {

                           LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();

                        } catch (any Error1) {

                           try {

                              LOCAL.CellValue = LOCAL.Cell.GetStringCellValue();

                           } catch (any Error2) {

                              LOCAL.CellValue = "";

                           }

                        }

                     } else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_BLANK){

                        LOCAL.CellValue = "";

                     } else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_BOOLEAN){

                        LOCAL.CellValue = LOCAL.Cell.GetBooleanCellValue();

                     } else {

                        LOCAL.CellValue = "";

                     }

                     LOCAL.SheetData.Query["column#(LOCAL.ColumnIndex + 1)#"][ LOCAL.SheetData.Query.RecordCount ] = JavaCast("string", LOCAL.CellValue);

                  }

               }

            }            

         }

      }

      return(LOCAL.SheetData);

   }

</cfscript>      

<cfset strFilePath = "C:\downloads\report.xls">

<cfset SpreadSheet = ReadExcel(FilePath = strFilePath, HasHeaderRow = true, SheetIndex = 0)>

<cfset qExcel = SpreadSheet.query>

<cfoutput>

<cfdump var="#SpreadSheet.ColumnNames#">

<cfdump var="#qExcel#">

</cfoutput>


In the above example, qExcel is the name of the query that contains the first worksheet of the spreadsheet.

While this is not yet a CSV you can then loop through the query writing it line by line as a comma delimited text file. However if there are cells containing multiple lines of text or containing commas then you will have problems as each record in the CSV is delimited by a CRFL (CHR(13) & CHR(10)) and each column or cell by a comma. To overcome this and preserve any mult-line cells or embedded commas if they exist, then use the following function to parse each cell when writing to the CSV file.

<cfscript>

   

   function csvFormat(string) {

      string = Replace(Replace(Replace(Replace(string, "’", "'", "all"), "`", "'", "all"), chr(13), "", "all"), "”", "''", "all") ;

      if (Find(",", string) gt 0 or Find(chr(10), string)) {

         string = chr(34) & string & chr(34) ;

      } else if (IsNumeric(string) and left(string, 1) eq "0" and Compare(string, "0") neq 0) {

         string = chr(9) & string ;

      }

      return string;

   }

</cfscript>


This will also preserve leading zeros in text data containing only numeric characters.

If you need more explanation then just ask.
Link | Top | Bottom

New Post
Please login to post a response.


company media information terms of use privacy policy contact us
This page was dynamically built on the BlueDragon CFML Engine