Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / Windows Forms

Excel Generator with Column Designer

Rate me:
Please Sign up or sign in to vote.
4.56/5 (27 votes)
8 Oct 2009CPOL2 min read 62.4K   2.2K   114   9
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)


Written By
Technical Lead
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Filip D'haene26-May-11 0:28
Filip D'haene26-May-11 0:28 
Generalcontact info Pin
aaron shah13-Apr-10 8:36
aaron shah13-Apr-10 8:36 
General2 questions Pin
Yifeng Ding12-Oct-09 15:36
Yifeng Ding12-Oct-09 15:36 
GeneralRe: 2 questions [modified] Pin
Somnath Mondal12-Oct-09 20:02
Somnath Mondal12-Oct-09 20:02 
QuestionSo how do I use the library? Pin
John Whitmire12-Oct-09 11:47
professionalJohn Whitmire12-Oct-09 11:47 
AnswerRe: So how do I use the library? Pin
Somnath Mondal12-Oct-09 18:50
Somnath Mondal12-Oct-09 18:50 
GeneralRe: So how do I use the library? Pin
John Whitmire27-Oct-09 5:37
professionalJohn Whitmire27-Oct-09 5:37 
GeneralQuestion Pin
42774809-Oct-09 22:59
42774809-Oct-09 22:59 
GeneralRe: Question Pin
Somnath Mondal10-Oct-09 5:26
Somnath Mondal10-Oct-09 5:26 

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

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