Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / desktop / WinForms

Excel Generator with Column Designer

4.56/5 (27 votes)
8 Oct 2009CPOL2 min read 63.8K   2.2K  
A fully customizable and extensible C# library that makes it easy to generate Excel files for a given DataSet, with column layout design support.

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

Image 1

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:
  • TypeNameCaption
    Labellable1Country
    ComboBoxcomboCountry
    ButtonbtnGoGO
    DataGridViewDataGridView1
    LabellblStatusStatus
    ButtonbtnDesignDesign
    ButtonbtnGenerateExcelExport to Excel
    ButtonbtnCancelCancel

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

    Image 2

  • Add the following lines in the declaration section of the form:
  • C#
    // 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:
  • C#
    // 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:
  • C#
    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:
  • C#
    private void btnGo_Click(object sender, EventArgs e)
    {
       // Prepare dataset and bind the datasource to grid
        InitializeReportData();
    }
  • Add the following method:
  • C#
    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:
  • C#
    private void btnGenerateExcel_Click(object sender, System.EventArgs e)
    {
        if(!ValidateAll())
        return;
        EnableButton(false);
        GenerateReport(OutputType.Excel);
        EnableButton(true);
    }
  • Add the following methods:
  • C#
    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:
  • C#
    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

Image 3

How to open the column layout designer window

C#
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

Image 4

How to call the Export to Excel task

C#
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)

Image 5

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)