Import from XLSB Files

Import from XLSB Files

   Concept:

ActiveXLS^TM enables the user to load an XLSB file (Office 2007 binary file format), or to import data from an XLSB document and export data as dataset/resultset/XML/custom list.

The following methods can be used to load an XLSB document or to import data from an XLSB document:

   * ExcelDocument.esd_LoadXLSBFile
   This method allows loading an XLSB document. Once the XLSB was loaded in memory, the user can process data, add more features to the file (like formulas, cell formatting, comments, hyperlinks, images, data validations, page setup, charts, etc), and save the file back in XLSB binary format or other format (XLS, XML Spreadsheet, XML, TXT, CSV or HTML, see Export).
It can be used to load an already formatted XLSB file as a template, add data and save back the file in XLSB binary format. In this way, a lot of written code that formats the file can be skipped.
   Available for: Professional, Reader

   * ExcelDocument.esd_ReadXLSBSheet_AsDataSet
   This method allows reading cell values from a specified sheet and exporting data as dataset/resultset.
   Available for: Professional, Reader

   * ExcelDocument.esd_ReadXLSBActiveSheet_AsDataSet
   This method allows reading cell values from the selected and active sheet and exporting data as dataset/resultset.
   Available for: Professional, Reader

   * ExcelDocument.esd_ReadXLSBSheet_AsList
   This method allows reading cell values from a specified sheet and exporting data in a custom list.
   Available for: Professional, Reader

   * ExcelDocument.esd_ReadXLSBActiveSheet_AsList
   This method allows reading cell values from the selected and active sheet and exporting data in a custom list.
   Available for: Professional, Reader

   * ExcelDocument.esd_ReadXLSBSheet_AsXML
   This method allows reading cell values from a specified sheet and export data as XML file. The generated XML document structure is described in the ActiveXLS.xsd schema file.
   Available for: Professional, Reader

   Concept in action:

To load an existing XLSB file, consider the code sample and the screen shot below:

CODE

     //Read the file
     Console.WriteLine("Reading file C:\\Samples\\Tutorial25.xlsb");

     if (xls.esd_LoadXLSBFile("C:\\Samples\\Tutorial25.xlsb"))
     {
        //Get the table of the second worksheet
        ExcelTable xlsSecondTable = ((ExcelWorksheet)xls.esd_getSheet("Second tab")).esd_getExcelTable();

        //Write some data
        xlsSecondTable.esd_getCell("A1").setValue("Data added by Tutorial34");

        for (int column=0; column<5; column++)
        {
           xlsSecondTable.esd_getCell(1, column).setValue("Data " + (column + 1));
        }

        //Generate the file
        Console.WriteLine("\nWriting file C:\\Samples\\Tutorial34.xlsb.");
        xls.esd_WriteXLSBFile("C:\\Samples\\Tutorial34.xlsb");
     }
     else
     {
        Console.WriteLine("Error reading file C:\\Samples\\Tutorial25.xlsb \n" + xls.esd_getError());
     }

Click here to see Continuous Code Listing

     'Read the file
     response.write("Reading file: C:\Samples\Tutorial25.xlsb")
     if (xls.esd_LoadXLSBFile("C:\Samples\Tutorial25.xlsb")) then
        'Get the table of the second worksheet
        set xlsSecondTable = xls.esd_getSheet("Second tab").esd_getExcelTable()

        'Write some data
        xlsSecondTable.esd_getCell_2("A1").setValue("Data added by Tutorial34")

        for column=0 to 4
           xlsSecondTable.esd_getCell(1, column).setValue("Data " & (column + 1))
        next

        'Generate the file
        response.write("Writing file: C:\Samples\Tutorial34.xlsb")
        xls.esd_WriteXLSBFile ("C:\Samples\Tutorial34.xlsb")

     else
        response.write("Error reading file C:\Samples\Tutorial25.xlsb ")
        response.write(xls.esd_getError())
     end if

Click here to see Continuous Code Listing

     //Read the file
     printf("\nReading file: C:\\Samples\\Tutorial25.xlsb\n");
     if (xls->esd_LoadXLSBFile("C:\\Samples\\Tutorial25.xlsb"))
     {
        //Get the table of the second worksheet
        ActiveXLS::IExcelWorksheetPtr xlsSecondTab = (ActiveXLS::IExcelWorksheetPtr)xls->esd_getSheetAt(1);
        ActiveXLS::IExcelTablePtr xlsTable = xlsSecondTab->esd_getExcelTable();

        //Write some data
        xlsSecondTable->esd_getCell_2("A1")->setValue("Data added by Tutorial34");
        char* cellValue = (char*)malloc(11*sizeof(char));
        char* columnNumber = (char*)malloc(sizeof(char));
        for (int column=0; column<5; column++)
        {
           strcpy(cellValue, "Data ");
           _itoa(column+ 1, columnNumber , 10);

           xlsTable->esd_getCell(1, column)->setValue(strcat(cellValue, columnNumber));
        }


        //Generate the file
        printf("Writing file C:\\Samples\\Tutorial34.xlsb.");
        xls->esd_WriteXLSBFile("C:\\Samples\\Tutorial34.xlsb");
     }
     else
     {
        printf("\nError reading file C:\Samples\Tutorial25.xlsb %s\n", (LPCSTR)((_bstr_t)xls->esd_getError()));
     }

Click here to see Continuous Code Listing

     //Read the file
     Console::WriteLine("Reading file C:\\Samples\\Tutorial25.xlsb.\n");
     if (xls->esd_LoadXLSBFile("C:\\Samples\\Tutorial25.xlsb"))
     {
        //Get the table of the second worksheet
        ExcelWorksheet *xlsSecondTab = __try_cast<ExcelWorksheet*>(xls->esd_getSheet("Second tab"));
        ExcelTable *xlsSecondTable = xlsSecondTab->esd_getExcelTable();

        //Write some data
   xlsSecondTable->esd_getCell("A1")->setValue("Data added by Tutorial34");
        for (int column=0; column<5; column++)
        {
           xlsSecondTable->esd_getCell(1, column)->setValue(String::Concat("Data ", (column + 1).ToString()));
        }


        //Generate the file
        Console::WriteLine("Writing file C:\\Samples\\Tutorial34.xlsb.");
        xls->esd_WriteXLSBFile("C:\\Samples\\Tutorial34.xlsb");
     }
     else
     {
        Console::WriteLine(String::Concat("\nError reading file C:\\Samples\\Tutorial25.xlsb \n", xls->esd_getError(), "\nPress Enter to Exit..."));
     }

Click here to see Continuous Code Listing

Reading file C:\Samples\Tutorial25.xlsb

  
  <CFIF (xls.esd_LoadXLSBFile("C:\Samples\Tutorial25.xlsb") IS True)>
     
     <cfset xlsTable = xls.esd_getSheetAt(1).esd_getExcelTable()>

     
     <cfset xlsTable.esd_getCell("A1").setValue("Data added by Tutorial34")>
     <cfloop from="0" to="4" index="column">
           <cfset xlsTable.esd_getCell(1,evaluate(column)).setValue("Data " & evaluate(column + 1))>
     </cfloop>

     
     Writing file C:\Samples\Tutorial34.xlsb
     <cfset ret = xls.esd_WriteXLSBFile("C:\Samples\Tutorial34.xlsb")>
  <CFELSE>
   <cfoutput>
     Error encountered: #xls.esd_getError()#
   </cfoutput>
  </CFIF>

Click here to see Continuous Code Listing

     //Read the file
     Console.WriteLine("Reading file C:\\Samples\\Tutorial25.xlsb");
     if (xls.esd_LoadXLSBFile("C:\\Samples\\Tutorial25.xlsb"))
     {
        //Get the table of the second worksheet
        ExcelTable xlsSecondTable = ((ExcelWorksheet)xls.esd_getSheet("Second tab")).esd_getExcelTable();

        //Write some data
        xlsSecondTable.esd_getCell("A1").setValue("Data added by Tutorial34");

        for (int column=0; column<5; column++)
        {
           xlsSecondTable.esd_getCell(1, column).setValue("Data " + (column + 1));
        }

        //Generate the file
        Console.WriteLine("\nWriting file C:\\Samples\\Tutorial34.xlsb.");
        xls.esd_WriteXLSBFile("C:\\Samples\\Tutorial34.xlsb");
     }
     else
     {
        Console.WriteLine("\nError reading file C:\\Samples\\Tutorial25.xlsb \n" + xls.esd_getError());
     }

Click here to see Continuous Code Listing

     //Read the file
     System.out.println("Reading file C:\\Samples\\Tutorial25.xlsb.");
     FileInputStream file = new FileInputStream("C:\\Samples\\Tutorial25.xlsb");
     if (xls.esd_LoadXLSBFile(file))
     {
        //Get the table of the second worksheet
        ExcelTable xlsSecondTable = ((ExcelWorksheet)xls.esd_getSheetAt(1)).esd_getExcelTable();

        //Write some data
        xlsSecondTable.esd_getCell("A1").setValue("Data added by Tutorial34");

        for (int column=0; column<5; column++)
        {
           xlsSecondTable.esd_getCell(1, column).setValue("Data " + (column + 1));
        }

        //Generate the file
        System.out.println("Writing file C:\\Samples\\Tutorial34.xlsb.");
        xls.esd_WriteXLSBFile("C:\\Samples\\Tutorial34.xlsb");
     }

Click here to see Continuous Code Listing

     //Read the file
     echo "Reading file: C:\\Samples\\Tutorial25.xlsb";
     if ($xls->esd_LoadXLSBFile("C:\\Samples\\Tutorial25.xlsb"))
     {
        //Get the table of the second worksheet
        $xlsSecondTable = $xls->esd_getSheet("Second tab")->esd_getExcelTable();

        //Write some data
        $xlsSecondTable->esd_getCell_2("A1")->setValue("Data added by Tutorial34");

        for ($column=0; $column<5; $column++)
        {
           $xlsSecondTable->esd_getCell(1, $column)->setValue("Data " . ($column + 1));
        }

        //Generate the file
        echo "Writing file: C:\Samples\Tutorial34.xlsb";
        $xls->esd_WriteXLSBFile("C:\Samples\Tutorial34.xlsb");
     }
     else
     {
        echo "Error reading file C:\Samples\Tutorial25.xlsb";
        echo $xls->esd_getError();
     }

Click here to see Continuous Code Listing

     'Read the file
     Console.WriteLine("Reading file C:\Samples\Tutorial25.xlsb." & vbCrLf)
     If (xls.esd_LoadXLSBFile("C:\Samples\Tutorial25.xlsb")) Then

        'Get the table of the second worksheet
        Dim xlsSecondTab As ExcelWorksheet = xls.esd_getSheet("Second tab")
        Dim xlsSecondTable = xlsSecondTab.esd_getExcelTable

        'Write some data
        xlsSecondTable.esd_getCell("A1").setValue("Data added by Tutorial34")

        For column As Integer = 0 To 4
           xlsSecondTable.esd_getCell(1, column).setValue("Data " & (column + 1))
        Next


        'Generate the file
        Console.WriteLine(vbCrLf & "Writing file C:\Samples\Tutorial34.xlsb.")
        xls.esd_WriteXLSBFile("C:\Samples\Tutorial34.xlsb")
     Else
        Console.WriteLine(vbCrLf & "Error reading file C:\Samples\Tutorial25.xlsb " & vbCrLf & xls.esd_getError())
     End If

Click here to see Continuous Code Listing

     'Read the file
     Me.Label1.Caption = Me.Label1.Caption & "Reading file: C:\Samples\Tutorial25.xlsb" & vbCrLf
     If (xls.esd_LoadXLSBFile("C:\Samples\Tutorial25.xlsb")) Then
        'Get the table of the second worksheet
        Set xlsSecondTable = xls.esd_getSheetAt(1).esd_getExcelTable()

        'Write some data
        xlsSecondTable.esd_getCell_2("A1").setValue ("Data added by Tutorial34")

        For Column = 0 To 4
           xlsSecondTable.esd_getCell(1, Column).setValue ("Data " & (Column + 1))
        Next

        'Generate the file
        Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Writing file C:\Samples\Tutorial34.xlsb"
        xls.esd_WriteXLSBFile ("C:\Samples\Tutorial34.xlsb")
     Else
        Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Error reading file C:\Samples\Tutorial25.xlsb" & vbCrLf & xls.esd_getError()
     End If

Click here to see Continuous Code Listing

     'Read the file
     WScript.StdOut.WriteLine("Reading file: C:\Samples\Tutorial25.xlsb" & vbcrlf)
     If (xls.esd_LoadXLSBFile("C:\Samples\Tutorial25.xlsb")) Then
        'Get the table of the second worksheet
        Set xlsSecondTable = xls.esd_getSheet("Second tab").esd_getExcelTable()

        'Write some data
        xlsSecondTable.esd_getCell_2("A1").setValue("Data added by Tutorial34")

        for column=0 to 4
           xlsSecondTable.esd_getCell(1, column).setValue("Data " & (column + 1))
        next

        'Generate the file
        Wscript.StdOut.WriteLine(vbcrlf & "Writing file: C:\Samples\Tutorial34.xlsb")
        xls.esd_WriteXLSBFile ("C:\Samples\Tutorial34.xlsb")
     Else
        Wscript.StdOut.WriteLine("Error reading file C:\Samples\Tutorial25.xlsb" & vbcrlf & xls.esd_getError())
     End If

Click here to see Continuous Code Listing

The screen shots below provides an example of a loaded Excel file, modified and saved back to another Excel file.

and:

Top