Automatically Export a Section Report to Excel Using SpreadBuilder API

ActiveReports provides a lot of export options which include PDF, RTF, TIFF and XLS to name a few. Excel is one format which is very commonly used by programmers. ActiveReports does allow you to export to Excel; however there are some limitations and the output is not 100% WYSIWYG. Included with the ActiveReports Excel export filter is the SpreadBuilder API. With this utility, you can create Excel spreadsheets cell by cell and provide control over what is placed where in the resulting export. However when creating an excel file using SpreadBuilder API, you will need to add all the headings and the data manually, in order to mimic the output you see on the viewer. This obviously is pretty time consuming. In this blog post, I'll explain how we can use the SpreadBuilder API to export a Section Based report to excel, by using the fields present within the report itself. Let us find out how this can be done. The first thing I would like you to check is the Classes which are exposed by the SpreadBuilder namespace. Now the basic thing we need to know here is that when working with SpreadBuilder we populate one cell at a time, so we cannot use it once the report document has been created. This means that we need to find a way to populate the excel file cells during report creation. In other words the report generation and the excel export should go hand in hand. To accomplish this we need to use the _Detail_Format_ event as it fires for each record present in the detail section and will provide us the value which we can write into the cell using SpreadBuilder. The choice of exporting the report in a single sheet or exporting each report page to a separate sheet is up to you. In this example, I am exporting each report page to a separate sheet. So the first thing we need to keep track of is, the current row, current column and the current sheet. We need to define three variables to store this information which would be later used to write data in the excel file. A sample code implementation is provided below:

   System.Windows.Forms.Label lbl = new System.Windows.Forms.Label();  
  public GrapeCity.SpreadBuilder.Workbook SpreadBuilderExcelWorkBook { get; set; }  
  private Graphics g;  
  public NewActiveReport1()  
     {  
          //  
          // Required for Windows Form Designer support  
          //  
          InitializeComponent();  
          SpreadBuilderExcelWorkBook = new Workbook();  
          g = lbl.CreateGraphics();  
          g.PageUnit = GraphicsUnit.Pixel;  
     }  

 private int _rowindex = 0;  
 private int _colindex = 0;  
 private int _sheetindex = -1;  

 private void detail_Format(object sender, EventArgs e)  
 {  
          foreach (ARControl ctrl in detail.Controls)  
             {  
                if (ctrl is TextBox)  
                {  
                   int oldwidth = SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Columns((short)\_colindex).Width;  
                   TextBox txt = (TextBox)ctrl;  
                   //Multiply by 14.40 to set the width of the column as per text width  
                   int newwidth = (int)(g.MeasureString(txt.Text, txt.Font).Width * (14.40));  
                   oldwidth = (oldwidth > newwidth) ? oldwidth : newwidth;  
                   SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Columns((short)\_colindex).Width = oldwidth;  
                   SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Cell(\_rowindex, _colindex).SetValue(txt.Text);  
                  _colindex++;  
                }  
             }  
             _rowindex++;  
             _colindex = 0;  
}

You will notice that in the code above I've created a property of type SpreadBuilder.Workbook and the reason for doing this is that I want to save the excel workbook only when the report has been displayed on the viewer. As I spoke earlier, we are exporting each report page to a separate sheet and to do this we would force a new sheet once a new page starts. The best way of doing this would be to use the PageStart event of ActiveReports, which fires when a new page starts. We may also want to place some headings on top of the page, which can be done in the same event. Typically the headings describe the fields displayed in the detail section and we'll use the DataField property of the TextBoxes for the heading text. Let's see how the actual code looks like:

private void NewActiveReport1_PageStart(object sender, EventArgs e)  
 {  
    SpreadBuilderExcelWorkBook.Sheets.AddNew();  
    _sheetindex++;  
    _rowindex = 0;  
    foreach (ARControl ctrl in detail.Controls)  
    {  
       if (ctrl is TextBox)  
          {  
             int oldwidth = SpreadBuilderExcelWorkBook.Sheets[0].Columns((short)_colindex).Width;  
             TextBox txt = (TextBox)ctrl;  
             SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Cell(\_rowindex, _colindex).FontBold = true;  
             SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Cell(\_rowindex, _colindex).FontSize = 12;  
             Font font = new Font(SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Cell(\_rowindex, _colindex).FontName, 12, FontStyle.Bold);  
             float t = g.MeasureString(txt.DataField, font).Width;  
             int newwidth = (int)(t * (14.40));  
             oldwidth = (oldwidth > newwidth) ? oldwidth : newwidth;  
             SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Columns((short)\_colindex).Width = oldwidth;  
             SpreadBuilderExcelWorkBook.Sheets[\_sheetindex].Cell(\_rowindex, _colindex).SetValue(txt.DataField);  
             _colindex++;  
          }  
       }  
       _rowindex++;  
       _colindex = 0;  
 }

A sample application in both C# and VB.NET showcasing this implementation can be downloaded from the links below: Download Sample C# Download Sample VB