PureBasic

XLSX Convert Sheet to CSV

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

IncludeFile "CkStringTable.pb"
IncludeFile "CkCsv.pb"
IncludeFile "CkZip.pb"

Procedure ChilkatExample()

    success.i = 0

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

    ; .xlsx files are Zip archives
    zip.i = CkZip::ckCreate()
    If zip.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success = CkZip::ckOpenZip(zip,"qa_data/excel/fakeCompanies.xlsx")
    If success = 0
        Debug CkZip::ckLastErrorText(zip)
        CkZip::ckDispose(zip)
        ProcedureReturn
    EndIf

    csv.i = CkCsv::ckCreate()
    If csv.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    sheetNames.i = CkStringTable::ckCreate()
    If sheetNames.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success = CkCsv::ckXlsxGetSheets(csv,zip,sheetNames)
    If success = 0
        Debug CkCsv::ckLastErrorText(csv)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

    If CkStringTable::ckCount(sheetNames) = 0
        Debug "There are no sheets in the .xlsx"
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

    ; Get the name of the 1st sheet.
    sheetName.s = CkStringTable::ckStringAt(sheetNames,0)

    ; Load the 1st sheet into the CSV.
    ; We could've also loaded the 1st sheet by passing an empty string for the sheet name.
    success = CkCsv::ckXlsxLoadSheet(csv,zip,sheetName)
    If success = 0
        Debug CkZip::ckLastErrorText(zip)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

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

    csvStr.s = CkCsv::ckSaveToString(csv)
    Debug csvStr

    ; 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.
    success = CkCsv::ckSaveFile2(csv,"c:/temp/qa_output/fakeCompanies.csv","utf-8")
    If success = 0
        Debug CkCsv::ckLastErrorText(csv)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf



    CkZip::ckDispose(zip)
    CkCsv::ckDispose(csv)
    CkStringTable::ckDispose(sheetNames)


    ProcedureReturn
EndProcedure