DataFlex

XLSX Convert Sheet to CSV

Open an Excel spreadsheet (.xlsx) and loads a sheet into a CSV object. DataFlex

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Variant vZip
    Handle hoZip
    Handle hoCsv
    Variant vSheetNames
    Handle hoSheetNames
    String sSheetName
    String sCsvStr
    String sTemp1
    Integer iTemp1

    Move False To iSuccess

    // This example assumes the Chilkat API to have been previously unlocked.
    // See Global Unlock Sample for sample code.

    // .xlsx files are Zip archives
    Get Create (RefClass(cComChilkatZip)) To hoZip
    If (Not(IsComObjectCreated(hoZip))) Begin
        Send CreateComObject of hoZip
    End
    Get ComOpenZip Of hoZip "qa_data/excel/fakeCompanies.xlsx" To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoZip To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get Create (RefClass(cComChilkatCsv)) To hoCsv
    If (Not(IsComObjectCreated(hoCsv))) Begin
        Send CreateComObject of hoCsv
    End
    Get Create (RefClass(cComChilkatStringTable)) To hoSheetNames
    If (Not(IsComObjectCreated(hoSheetNames))) Begin
        Send CreateComObject of hoSheetNames
    End
    Get pvComObject of hoZip to vZip
    Get pvComObject of hoSheetNames to vSheetNames
    Get ComXlsxGetSheets Of hoCsv vZip vSheetNames To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoCsv To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get ComCount Of hoSheetNames To iTemp1
    If (iTemp1 = 0) Begin
        Showln "There are no sheets in the .xlsx"
        Procedure_Return
    End

    // Get the name of the 1st sheet.
    Get ComStringAt Of hoSheetNames 0 To sSheetName

    // Load the 1st sheet into the CSV.
    // We could've also loaded the 1st sheet by passing an empty string for the sheet name.
    Get pvComObject of hoZip to vZip
    Get ComXlsxLoadSheet Of hoCsv vZip sSheetName To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoZip To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    // The Chilkat CSV API can now be used to read, modify, or insert cells.
    // ...
    // ...

    Get ComSaveToString Of hoCsv To sCsvStr
    Showln sCsvStr

    // Sample output:
    // CompanyName,Address,City,State,Zip,Phone
    // Acme Innovations LLC,7428 Westbrook Blvd,Oak Bend,IL,60514,630-555-2398
    // BlueHorizon Data Systems,1850 Crestline Park Suite 210,Rivergate,CA,92814,714-555-8821
    // Pinnacle Manufacturing Corp,3912 Industrial Loop,Maple Ridge,TX,77301,281-555-4477
    // Evergreen Analytics Inc,1250 Highland Terrace,Stonefield,WA,98290,425-555-9914
    // NovaTech Solutions Group,578 Marketview Drive,Clearwater,FL,33760,727-555-6032

    // Save to a .csv file.
    Get ComSaveFile2 Of hoCsv "c:/temp/qa_output/fakeCompanies.csv" "utf-8" To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoCsv To sTemp1
        Showln sTemp1
        Procedure_Return
    End



End_Procedure