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
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:
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;
}
reportDataSet.ReadXml("Customers.xml");
reportDataSet.Tables[0].TableName = "Customers";
reportDataSet.Tables[1].TableName = "Country";
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)
{
InitializeReportData();
}
Add the following method:
private void InitializeReportData()
{
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);
}
spreadsheet.DataView = reportView;
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;
spreadsheet.ExportFile = outputFile;
spreadsheet.InitializeReportColumns(reportConfigFile,reportId);
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()
{
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);
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();
spreadsheet.InitializeReportColumns(reportConfigFile,reportId);
InitializeReportData();
ReportDesigner frm = new ReportDesigner(reportConfigFile, reportId,spreadsheet);
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;
spreadsheet.ExportFile = outputFile;
spreadsheet.InitializeReportColumns(reportConfigFile,reportId);
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.