Export XLSB Files

Export XLSB Files

   Concept:

ActiveXLS allows the user to export data in XLSB format (Office 2007 binary file format), using the following methods:

    * ExcelDocument.esd_WriteXLSBFile
   This method allows saving an XLSB document in binary file format.
   Available for: Professional, Writer

    * ExcelDocument.esd_WriteXLSBFile_FromDataSet
   This method allows importing data from a dataset/resultset and save data in XLSB binary format.
   Available for: Professional, Writer, Express Writer

    * ExcelDocument.esd_WriteXLSBFile_FromList
   This method allows importing data from a list of values and save data in XLSB binary format.
   Available for: Professional, Writer, Express Writer

   Concept in action:

If you want to generate an Excel 2007 document, consider the code sample and the screen shot below:

CODE

     //Create an instance of the object that generates Excel files, having 2 sheets
        ExcelDocument xls = new ExcelDocument(2);

        //Set the sheet names
        xls.esd_getSheetAt(0).setSheetName("First tab");
        xls.esd_getSheetAt(1).setSheetName("Second tab");

        //Get the table of the first worksheet
        ExcelTable xlsFirstTable = ((ExcelWorksheet)xls.esd_getSheetAt(0)).esd_getExcelTable();

        //Add the cells for header
        for (int column=0; column<5; column++)
        {
           xlsFirstTable.esd_getCell(0,column).setValue("Column " + (column + 1));
           xlsFirstTable.esd_getCell(0,column).setDataType(DataType.STRING);
        }

        //Add the cells for data
        for (int row=0; row<100; row++)
        {
           for (int column=0; column<5; column++)
           {
              xlsFirstTable.esd_getCell(row+1,column).setValue("Data " + (row + 1) + ", " + (column + 1));
              xlsFirstTable.esd_getCell(row+1,column).setDataType(DataType.STRING);
           }
        }

        //Generate the file
        Console.WriteLine("Writing file C:\\Samples\\Tutorial25.xlsb.");
        xls.esd_WriteXLSBFile("C:\\Samples\\Tutorial25.xlsb");

        //Confirm generation
        String sError = xls.esd_getError();
        if (sError.Equals(""))
           Console.Write("\nFile successfully created. Press Enter to Exit...");
        else
           Console.Write("\nError encountered: " + sError + "\nPress Enter to Exit...");

Click here to see Continuous Code Listing

     'Create an instance of the object that generates Excel files
     set xls = Server.CreateObject("ActiveXLS.ExcelDocument")

     'Create the worksheets
     xls.esd_addWorksheet_2("First tab")
     xls.esd_addWorksheet_2("Second tab")

     'Get the table of the first worksheet
     Set xlsFirstTable = xls.esd_getSheetAt(0).esd_getExcelTable()

     'Add the cells for header
     for column = 0 to 4
        xlsFirstTable.esd_getCell(0,column).setValue("Column " & (column + 1))
        xlsFirstTable.esd_getCell(0,column).setDataType(DATATYPE_STRING)
     next

     'Add the cells for data
     for row = 0 to 99
        for column = 0 to 4
           xlsFirstTable.esd_getCell(row+1,column).setValue("Data " & (row + 1) & ", " & (column + 1))
           xlsFirstTable.esd_getCell(row+1,column).setDataType(DATATYPE_STRING)
        next
     next

     'Generate the file
     response.write("Writing file: C:\Samples\Tutorial25.xlsb <br>")
     xls.esd_WriteXLSBFile "C:\Samples\Tutorial25.xlsb"

     'Confirm generation
     if xls.esd_getError() = "" then
        response.write("File successfully created.")
     else
        response.write("Error encountered: " + xls.esd_getError())
     end if

Click here to see Continuous Code Listing

      //Create a pointer to the interface that generates Excel files
      ActiveXLS::IExcelDocumentPtr xls;
      hr = CoCreateInstance(__uuidof(ActiveXLS::ExcelDocument),
        NULL,
      CLSCTX_ALL,
      __uuidof(ActiveXLS::IExcelDocument),
      (void**) &xls) ;

      if(SUCCEEDED(hr)){

         //Create the worksheet
         xls->esd_addWorksheet_2("First tab");
         xls->esd_addWorksheet_2("Second tab");

         //Get the table of the first worksheet
         ActiveXLS::IExcelWorksheetPtr xlsFirstTab= (ActiveXLS::IExcelWorksheetPtr)xls->esd_getSheetAt(0);
         ActiveXLS::IExcelTablePtr xlsFirstTable = xlsFirstTab->esd_getExcelTable();

         //Add the cells for header
         char* cellValue = (char*)malloc(11*sizeof(char));
         char* columnNumber = (char*)malloc(sizeof(char));
         for (int column=0; column<5; column++)
         {
            strcpy(cellValue, "Column ");
            _itoa(column+ 1, columnNumber , 10);

            xlsFirstTable->esd_getCell(0,column)->setValue( strcat(cellValue, columnNumber));
            xlsFirstTable->esd_getCell(0,column)->setDataType(DATATYPE_STRING);
         }

         //Add the cells for data
         char* rowNumber = (char*)malloc(sizeof(char));
         for (int row=0; row<100; row++)
         {
            for (int column=0; column<5; column++)
            {
               strcpy(cellValue, "Data ");
               _itoa(column+ 1, columnNumber , 10);
               _itoa(row + 1, rowNumber , 10);

               strcat(cellValue, rowNumber);
               strcat(cellValue, ", ");
               strcat(cellValue, columnNumber);

               xlsFirstTable->esd_getCell(row+1,column)->setValue(cellValue);
               xlsFirstTable->esd_getCell(row+1,column)->setDataType(DATATYPE_STRING);
            }
         }


         //Generate the file
         printf("Writing file C:\\Samples\\Tutorial25.xlsb.");
         xls->esd_WriteXLSBFile("C:\\Samples\\Tutorial25.xlsb");

         //Confirm generation
         _bstr_t sError = xls->esd_getError();
         if (strcmp(sError, "") == 0){
            printf("\nFile successfully created. Press Enter to Exit...");
         }
         else{
            printf("\nError encountered: %s", (LPCSTR)sError);
         }

         //Dispose memory
         xls->Dispose();
      }
      else{
         printf("Object is not available!");
      }

Click here to see Continuous Code Listing

     //Create an instance of the object that generates Excel files, having 2 sheets
     ExcelDocument *xls = new ExcelDocument(2);

     //Set the sheet names
     xls->esd_getSheetAt(0)->setSheetName("First tab");
     xls->esd_getSheetAt(1)->setSheetName("Second tab");

     //Get the table of the first worksheet
     ExcelWorksheet *xlsFirstTab = __try_cast<ExcelWorksheet*>(xls->esd_getSheetAt(0));
     ExcelTable *xlsFirstTable = xlsFirstTab->esd_getExcelTable();

     //Add the cells for header
     for (int column=0; column<5; column++)
     {
        xlsFirstTable->esd_getCell(0,column)->setValue(String::Concat("Column ",(column + 1).ToString()));
        xlsFirstTable->esd_getCell(0,column)->setDataType(DataType::STRING);
     }

     //Add the cells for data
     for (int row=0; row<100; row++)
     {
        for (int column=0; column<5; column++)
        {
           xlsFirstTable->esd_getCell(row+1,column)->setValue(String::Concat("Data ", (row + 1).ToString(), ", ", (column + 1).ToString()));
           xlsFirstTable->esd_getCell(row+1,column)->setDataType(DataType::STRING);
        }
     }

     //Generate the file
     Console::WriteLine("Writing file C:\\Samples\\Tutorial25.xlsb.");
     xls->esd_WriteXLSBFile("C:\\Samples\\Tutorial25.xlsb");

Click here to see Continuous Code Listing

<cfobject type="java" class="ActiveXLS.ExcelDocument" name="xls" action="CREATE">


  
  <cfset ret = xls.esd_addWorksheet("First tab")>
  <cfset ret = xls.esd_addWorksheet("Second tab")>

  
  <cfset xlsFirstTable = xls.esd_getSheetAt(0).esd_getExcelTable()>

  
  <cfloop from="0" to="4" index="column">
        <cfset xlsFirstTable.esd_getCell(0,evaluate(column)).setValue("Column " & evaluate(column + 1))>
        <cfset xlsFirstTable.esd_getCell(0,evaluate(column)).setDataType(DataType.STRING)>
  </cfloop>

  
  <cfloop from="0" to="99" index="row">
     <cfloop from="0" to="4" index="column">
        <cfset xlsFirstTable.esd_getCell(evaluate(row + 1),evaluate(column)).setValue("Data " & evaluate(row + 1) & ", " & evaluate(column + 1))>
        <cfset xlsFirstTable.esd_getCell(evaluate(row + 1),evaluate(column)).setDataType(DataType.STRING)>
     </cfloop>
  </cfloop>


  
  Writing file C:\Samples\Tutorial25.xlsb<br>
  <cfset ret = xls.esd_WriteXLSBFile("C:\Samples\Tutorial25.xlsb")>

  
  <cfset sError = xls.esd_getError()>
  <CFIF (sError IS "")>
   <cfoutput>
     File successfully created.
   </cfoutput>
  <CFELSE>
   <cfoutput>
     Error encountered: #sError#
   </cfoutput>
  </CFIF>

Click here to see Continuous Code Listing

     //Create an instance of the object that generates Excel files, having 2 sheets
        ExcelDocument xls = new ExcelDocument(2);

        //Set the sheet name
        xls.esd_getSheetAt(0).setSheetName("First tab");
        xls.esd_getSheetAt(1).setSheetName("Second tab");

        //Get the table of the first worksheet
        ExcelTable xlsFirstTable = ((ExcelWorksheet)xls.esd_getSheetAt(0)).esd_getExcelTable();

        //Add the cells for header
        for (int column=0; column<5; column++)
        {
           xlsFirstTable.esd_getCell(0,column).setValue("Column " + (column + 1));
           xlsFirstTable.esd_getCell(0,column).setDataType(DataType.STRING);
        }

        //Add the cells for data
        for (int row=0; row<100; row++)
        {
           for (int column=0; column<5; column++)
           {
              xlsFirstTable.esd_getCell(row+1,column).setValue("Data " + (row + 1) + ", " + (column + 1));
              xlsFirstTable.esd_getCell(row+1,column).setDataType(DataType.STRING);
           }
        }


        //Generate the file
        Console.WriteLine("Writing file C:\\Samples\\Tutorial25.xlsb.");
        xls.esd_WriteXLSBFile("C:\\Samples\\Tutorial25.xlsb");

        //Confirm generation
        String sError = xls.esd_getError();
        if (sError.Equals(""))
           Console.Write("\nFile successfully created. Press Enter to Exit...");
        else
           Console.Write("\nError encountered: " + sError + "\nPress Enter to Exit...");

Click here to see Continuous Code Listing

   //Create an instance of the object that generates Excel files, having 2 sheets
   ExcelDocument xls = new ExcelDocument(2);

   //Set the sheet name
   xls.esd_getSheetAt(0).setSheetName("First tab");
   xls.esd_getSheetAt(1).setSheetName("Second tab");

   //Get the table of the first worksheet
   ExcelTable xlsFirstTable = ((ExcelWorksheet)xls.esd_getSheetAt(0)).esd_getExcelTable();

   //Add the cells for header
   for (int column=0; column<5; column++)
   {
     xlsFirstTable.esd_getCell(0,column).setValue("Column " + (column + 1));
     xlsFirstTable.esd_getCell(0,column).setDataType(DataType.STRING);
   }

   //Add the cells for data
   for (int row=0; row<100; row++)
   {
     for (int column=0; column<5; column++)
     {
     xlsFirstTable.esd_getCell(row+1,column).setValue("Data " + (row + 1) + ", " + (column + 1));
     xlsFirstTable.esd_getCell(row+1,column).setDataType(DataType.STRING);
     }
   }


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

   //Confirm generation
   if (xls.esd_getError().equals(""))
   System.out.println("File successfully created.");
   else
   System.out.println("Error encountered: " + xls.esd_getError());

Click here to see Continuous Code Listing

     //Create an instance of the object that generates Excel files
     $xls = new COM("ActiveXLS.ExcelDocument");

     //Create the worksheet
     $xls->esd_addWorksheet_2("First tab");
     $xls->esd_addWorksheet_2("Second tab");

     //Get the table of the first worksheet
     $xlsFirstTable = $xls->esd_getSheetAt(0)->esd_getExcelTable();

     //Add the cells for header
     for ($column=0; $column<5; $column++)
     {
        $xlsFirstTable->esd_getCell(0,$column)->setValue("Column " . ($column + 1));
        $xlsFirstTable->esd_getCell(0,$column)->setDataType($DATATYPE_STRING);
     }

     //Add the cells for data
     for ($row=0; $row<100; $row++)
     {
        for ($column=0; $column<5; $column++)
        {
           $xlsFirstTable->esd_getCell($row+1,$column)->setValue("Data ".($row + 1).", ".($column + 1));
           $xlsFirstTable->esd_getCell($row+1,$column)->setDataType($DATATYPE_STRING);
        }
     }


     //Generate the file
     echo "Writing file: C:\Samples\Tutorial25.xlsb<br>";
     $xls->esd_WriteXLSBFile("C:\Samples\Tutorial25.xlsb");

     //Confirm generation
     if ($xls->esd_getError() == "")
        echo "File successfully created.";
     else
        echo "Error encountered: " . $xls->esd_getError();

Click here to see Continuous Code Listing

   'Create an instance of the object that generates Excel files, having 2 sheets
   Dim xls As New ExcelDocument(2)

   'Set the sheet names
   xls.esd_getSheetAt(0).setSheetName("First tab")
   xls.esd_getSheetAt(1).setSheetName("Second tab")

   'Get the table of the first worksheet
   Dim xlsFirstTab As ExcelWorksheet = xls.esd_getSheetAt(0)
   Dim xlsFirstTable = xlsFirstTab.esd_getExcelTable()

   'Add the cells for header
   For column As Integer = 0 To 4
     xlsFirstTable.esd_getCell(0, column).setValue("Column " & (column + 1))
     xlsFirstTable.esd_getCell(0, column).setDataType(DataType.STRING)
   Next

   'Add the cells for data
   For row As Integer = 0 To 99
     For column As Integer = 0 To 4
       xlsFirstTable.esd_getCell(row + 1, column).setValue("Data " & (row + 1) & ", " & (column + 1))
       xlsFirstTable.esd_getCell(row + 1, column).setDataType(DataType.STRING)
     Next
   Next

   'Generate the file
   Console.WriteLine("Writing file C:\Samples\Tutorial25.xlsb.")
   xls.esd_WriteXLSBFile("C:\Samples\Tutorial25.xlsb")

Click here to see Continuous Code Listing

   'Create an instance of the object that generates Excel files
   Set xls = CreateObject("ActiveXLS.ExcelDocument")

   'Create the worksheet
   xls.esd_addWorksheet_2 ("First tab")
   xls.esd_addWorksheet_2 ("Second tab")

   'Get the table of the first worksheet
   Set xlsFirstTable = xls.esd_getSheetAt(0).esd_getExcelTable()

   'Add the cells for header
   For Column = 0 To 4
     xlsFirstTable.esd_getCell(0, Column).setValue ("Column " & (Column + 1))
     xlsFirstTable.esd_getCell(0, Column).setDataType (DataType.DATATYPE_STRING)
   Next

   'Add the cells for data
   For row = 0 To 99
     For Column = 0 To 4
       xlsFirstTable.esd_getCell(row + 1, Column).setValue ("Data " & (row + 1) & ", " & (Column + 1))
       xlsFirstTable.esd_getCell(row + 1, Column).setDataType (DataType.DATATYPE_STRING)
     Next
   Next


   'Generate the file
   Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Writing file C:\Samples\Tutorial25.xlsb"
   xls.esd_WriteXLSBFile "C:\Samples\Tutorial25.xlsb"

   'Confirm generation
   If xls.esd_getError() = "" Then
   Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "File successfully created."
   Else
   Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Error: " & xls.esd_getError()
   End If

Click here to see Continuous Code Listing

   'Create an instance of the object that generates Excel files
   Set xls = CreateObject("ActiveXLS.ExcelDocument")

     'Create the worksheet
     xls.esd_addWorksheet_2("First tab")
     xls.esd_addWorksheet_2("Second tab")

     'Get the table of the first worksheet
     Set xlsFirstTable = xls.esd_getSheetAt(0).esd_getExcelTable()

     'Add the cells for header
     For Column = 0 To 4
        xlsFirstTable.esd_getCell(0,column).setValue("Column " & (Column + 1))
        xlsFirstTable.esd_getCell(0,column).setDataType(DATATYPE_STRING)
     Next

     'Add the cells for data
     For row = 0 To 99
        For column = 0 To 4
           xlsFirstTable.esd_getCell(row+1,column).setValue("Data " & (row + 1) & ", " & (column + 1))
           xlsFirstTable.esd_getCell(row+1,column).setDataType(DATATYPE_STRING)
        Next
     Next


   'Generate the file
   WScript.StdOut.WriteLine("Writing file C:\Samples\Tutorial25.xlsb")
   xls.esd_WriteXLSBFile "C:\Samples\Tutorial25.xlsb"

   'Confirm generation
   dim sError
   sError = xls.esd_getError()
   if sError = "" then
        WScript.StdOut.Write(vbcrlf & "File successfully created. Press Enter to exit...")
   else
        WScript.StdOut.Write(vbcrlf & "Error: " & sError)
   end if

Click here to see Continuous Code Listing

The screen shot below represents the Excel document generated by the code sample above. The file has two worksheets (First Tab and Second Tab). The user set the values and the data type of the cells for the first worksheet.

Top