Excel Generator with Column Designer

• Download source code - 187 KB

Introduction

This is a fully customizable and extensible C# library using .NET Framework 3.5 that makes it easy to create Excel files by passing an ADO.NET DataView object. There may be situations when you need to generate a report in MS Excel format without using MS Office components. This scenario is common in production environments in many projects.

Additionally, this library will give you freedom to design your own column layout for a given DataSet. The column layout designer is a WinForms application. The core library with export functionality can be used in WinForms and in ASP.NET web applications. At runtime, if it can’t find any layout information for a given report, it will export the data for all columns.

Class diagram

Using the code

• Open the Visual Studio 2008 IDE and create a C# Windows Application Project named “SpreadsheetDemo”.
• Rename Form1 to TestReport.
• Add a reference to the library.
• Paste the following controls into the TestReport form:

Type	Name	Caption
Label	lable1	Country
ComboBox	comboCountry
Button	btnGo	GO
DataGridView	DataGridView1
Label	lblStatus	Status
Button	btnDesign	Design
Button	btnGenerateExcel	Export to Excel
Button	btnCancel	Cancel

The form should look like the following screen in the Visual Studio Design view:



• Add the following lines in the declaration section of the form:

// variables
private Spreadsheet spreadsheet  = null;
private string reportId = string.Empty;
private string reportTitle = string.Empty;
private string reportConfigFile =  string.Empty;
private string outputFile = string.Empty;

• Add the following lines in the TestReport_Load event:

// load the country data into combo box
private void TestReport_Load(object sender, System.EventArgs e)
{
    PrepareDataset();

    spreadsheet = new Spreadsheet();
    spreadsheet.OnProgress+= 
      new sommon.Spreadsheet.Spreadsheet.StatusHandler(xmlSheet_OnProgress);
    spreadsheet.OnError+=
      new sommon.Spreadsheet.Spreadsheet.StatusHandler(xmlSheet_OnError);
    spreadsheet.OnFinish+=
      new sommon.Spreadsheet.Spreadsheet.StatusHandler(xmlSheet_OnFinish);
}

• Add the following method:

private void  PrepareDataset()
{
    if(!File.Exists("Customers.xml"))
    {
        MessageBox.Show("Cannot find the Customers.xml file.", 
                        this.Text, MessageBoxButtons.OK, 
                        MessageBoxIcon.Exclamation );
        this.Close();
        return;
    }

    // prepare the dataset 
    reportDataSet.ReadXml("Customers.xml");
    reportDataSet.Tables[0].TableName = "Customers";
    reportDataSet.Tables[1].TableName = "Country";

    // set the combo box for country selection
    comboCountry.DataSource = reportDataSet.Tables["Country"].DefaultView;
    comboCountry.DisplayMember = "Country";
    comboCountry.ValueMember = "Country";
}

• Add the following lines in the Click event of the Go button:

private void btnGo_Click(object sender, EventArgs e)
{
   // Prepare dataset and bind the datasource to grid
    InitializeReportData();
}

• Add the following method:

private void InitializeReportData()
{
    // here you can build the dataset as per your requirement.
    // In this example we will simply filter the existing dataset
    // based on the country selection.
    DataView reportView = null;
    string country = comboCountry.Text;
    lblStatus.Text = "";
    if (country == "All")
         reportView = reportDataSet.Tables["Customers"].DefaultView;
    else
    {
        string rowFilter = "Country = '" + country + "'";
                           lblStatus.Text = "Filter: " +  rowFilter; 
        reportView = new DataView(reportDataSet.Tables["Customers"], 
             rowFilter,"CompanyName", DataViewRowState.CurrentRows);
    }

    // assign reportView object to spreadsheet class
    spreadsheet.DataView = reportView;

    // bind the datasource into data grid
    dataGridView1.DataSource = reportView;

}

• Add the following lines in the Click event of the Export to Excel button:

private void btnGenerateExcel_Click(object sender, System.EventArgs e)
{
    if(!ValidateAll())
    return;
    EnableButton(false);
    GenerateReport(OutputType.Excel);
    EnableButton(true);
}

• Add the following methods:

private void EnableButton(bool enable)
{
    btnDesign.Enabled = enable;
    btnGenerateExcel.Enabled = enable;
    btnGenerateHtml.Enabled = enable;
}

private void GenerateReport(OutputType outputType)
{
    reportId = "CustomerList";
    reportTitle = "List of Customer";
    reportConfigFile =  "Report.config";
    outputFile = Application.StartupPath + @"\"+ reportTitle;
    try
    {
        this.Cursor = Cursors.WaitCursor;
        // setting output file
        spreadsheet.ExportFile = outputFile;

        // Initialize the ColumnStyles item
        spreadsheet.InitializeReportColumns(reportConfigFile,reportId);

        // initialize the report data based on specified search criteria 
        InitializeReportData();

        if(spreadsheet.DataView.Count==0)
        {
            this.Cursor = Cursors.Default;
            MessageBox.Show("No Records Found.", 
              this.Text,MessageBoxButtons.OK, 
              MessageBoxIcon.Information,MessageBoxDefaultButton.Button1); 
            return;
        }
        spreadsheet.GenerateWorkSheet(outputType);
        this.Cursor = Cursors.Default;
    }
    catch(Exception ex)
    {
        this.Cursor = Cursors.Default;
        MessageBox.Show("Unable to generate report.\n"+ 
          ex.Message,this.Text,MessageBoxButtons.OK, 
          MessageBoxIcon.Error,MessageBoxDefaultButton.Button1); 
    }
}

private bool ValidateAll()
{
    // for a demo purpose I have hard coded the return value 
    // as true but you can implement your own logic here.
    return true;
}

• Add the following lines to the Click event of the Design button:

private void btnDesign_Click(object sender, System.EventArgs e)
{
    reportId = "CustomerList";
    reportTitle = "List of Customer";
    reportConfigFile =  "Report.config";
    spreadsheet.InitializeReportColumns(reportConfigFile,reportId);
    // Prepare dataset
    InitializeReportData();
    ReportDesigner frm = 
       new ReportDesigner(reportConfigFile, reportId,spreadsheet);
    frm.ShowDialog();
}

• Press F5 to run the form.

Sample customer report input screen

How to open the column layout designer window

string reportId = "CustomerList";
string reportTitle = "List of Customer";
string reportConfigFile =  "Report.config";
Spreadsheet spreadsheet  = new Spreadsheet();

// Initialize the ColumnStyles item
spreadsheet.InitializeReportColumns(reportConfigFile,reportId);

// initialize the report data based on specified search criteria 
InitializeReportData();

// create instance of ReportDesigner class
ReportDesigner frm = new ReportDesigner(reportConfigFile, reportId,spreadsheet);

// show the designer window
frm.ShowDialog();

Column designer screen for the customer report

How to call the Export to Excel task

reportId = "CustomerList";
reportTitle = "List of Customer";
reportConfigFile =  "Report.config";
outputFile = Application.StartupPath + @"\"+ reportTitle;
try
{
    this.Cursor = Cursors.WaitCursor;
    // setting output file
    spreadsheet.ExportFile = outputFile;
    // Initialize the ColumnStyles item
    spreadsheet.InitializeReportColumns(reportConfigFile,reportId);
    // initialize the report data based on specified search criteria 
    InitializeReportData();
    if(spreadsheet.DataView.Count==0)
    {
        this.Cursor = Cursors.Default;
        MessageBox.Show("No Records Found.", 
          this.Text,MessageBoxButtons.OK,MessageBoxIcon.Information,
          MessageBoxDefaultButton.Button1); 
        return;
    }
    spreadsheet.GenerateWorkSheet(outputType);
    this.Cursor = Cursors.Default;
}
catch(Exception ex)
{
    this.Cursor = Cursors.Default;
    MessageBox.Show("Unable to generate report.\n"+ ex.Message, 
      this.Text,MessageBoxButtons.OK,MessageBoxIcon.Error, 
      MessageBoxDefaultButton.Button1);
}

Customer report (Excel)

How the Excel Generator Works

Basically, it creates an XML Spreadsheet file as output. The GenerateWorkSheet method is responsible for creating the output file. The steps below are followed while generating the output file:

• Declare a variable of type StringBuilder and add the following result into it:
• Create an Excel Header string
• Create all the Style strings
• Create a Worksheet options string (required only one time)
• Create the First Worksheet tag string
• Create the Table tag
• Create the Table Header Style tag
• Loop through the DataView and create an Excel compatible tag for each row, column
• Close the Workbook tag
• Save the string in the output file.
• Notify the caller about the status.

Conclusion

Using the above library, you can generate Excel files easily in a few minutes and hence improve your efficiency and productivity. This library gives you freedom from the MS Office Primary Interop Assemblies (PIA) which are heavy, memory hungry, and system dependent.