Click here to Skip to main content
Click here to Skip to main content

Excel Generator with Column Designer

, 8 Oct 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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.

License

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

Share

About the Author

Somnath Mondal
Technical Lead
India India
No Biography provided

Comments and Discussions

 
GeneralQuestion PinmemberMember 42774809-Oct-09 23:59 
GeneralRe: Question PinmemberSomnath Mondal10-Oct-09 6:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411028.1 | Last Updated 8 Oct 2009
Article Copyright 2009 by Somnath Mondal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid