|
The ExportData.cs / .vb file contains two classes required to export information: the ExcelColumn
class and the ExportData class. The ExcelColumn class defines the default behavior of the ExcelColumn
object that is being used when export data in an XLS format.
This class has two default properties, DisplayColumn and DisplayFormat.
First, add the property in the ExcelColumn class to hold the column summary of values.
Visual Basic .NET:
Private _ColSumValue As Double
Public Property SumValue() As Double
Get
Return _ColSumValue
End Get
Set(ByVal value As Double)
Me_ColSumValue = Me_ColSumValue + value
End Set
End Property
|
Next, add a new property in the properties section to mark the specific column you want to summarize.
Visual Basic .NET:
Private _SumTotal As Boolean
Public Property SumTotal() As Boolean
Get
Return _SumTotal
End Get
Set(ByVal value As Boolean)
Me._SumTotal = value
Me._ColSumValue = 0
End Set
End Property
|
To use these properties, create another version of the class constructor. This new constructor will
accept an additional variable indicating whether the column is to be summed.
Visual Basic .NET:
Public Sub New(ByVal col As BaseColumn, ByVal format As String, _
ByVal SumColumn As Boolean)
DisplayColumn = col
DisplayFormat = format
SumTotal = SumColumn
End Sub
|
Finally, modify the export infrastructure by changing the method that exports the data to Excel.
The example below shows the complete modified method and highlights the changes.
Visual Basic .NET:
Public Sub ExportToExcel(ByVal response As System.Web.HttpResponse)
Dim done As Boolean = False
Dim rec As BaseRecord
Dim elem As ExcelColumn
Dim val As String
Dim skip As Boolean
Dim skipHeader As Boolean
If Not IsNothing(response) Then
Dim excelBook As New CarlosAg.ExcelXmlWriter.Workbook()
excelBook.Properties.Title = DBTable.TableDefinition.Name
excelBook.Properties.Created = DateTime.Now
excelBook.Properties.Author = ""
Dim excelSheet As CarlosAg.ExcelXmlWriter.Worksheet = _
excelBook.Worksheets.Add("Sheet1")
Dim excelRow As CarlosAg.ExcelXmlWriter.WorksheetRow= _
excelSheet.Table.Rows.Add()
Dim excelDataType As CarlosAg.ExcelXmlWriter.DataType
excelSheet.Options.SplitHorizontal = 1
excelSheet.Options.FreezePanes = True
excelSheet.Options.TopRowBottomPane = 1
Dim excelHeaderStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle = _
excelBook.Styles.Add("HeaderRowStyle")
excelHeaderStyle.Font.Bold = True
excelHeaderStyle.Font.FontName = "Verdana"
excelHeaderStyle.Font.Size = 8
excelHeaderStyle.Font.Color = "#FFFFFF"
excelHeaderStyle.Interior.Color = "#000000"
excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid
Dim width As Integer = 0
' First write out the Column Headers
For Each elem In ColumnList
Dim exCol As ExcelColumn = CType(elem, ExcelColumn)
Dim col As BaseColumn = exCol.DisplayColumn
If Not (IsNothing(col)) Then
skipHeader = False
If col.TableDefinition.IsExpandableNonCompositeForeignKey(col) Then
'Set width if field is a foreign key field
width = 100
Else
Select Case col.ColumnType
Case BaseColumn.ColumnTypes.Binary, _
BaseColumn.ColumnTypes.Image
' Skip - do nothing for these columns
skipHeader = True
Case BaseColumn.ColumnTypes.Currency, _
BaseColumn.ColumnTypes.Number, _
BaseColumn.ColumnTypes.Percentage
width = 60
Case BaseColumn.ColumnTypes.String, _
BaseColumn.ColumnTypes.Very_Large_String
width = 110
Case Else
width = 50
End Select
End If
If Not skipHeader Then
'excelHeaderStyle.NumberFormat = exCol.DisplayFormat
excelRow.Cells.Add(New WorksheetCell(col.Name, "HeaderRowStyle"))
Dim worksheetColumn As WorksheetColumn = _
excelSheet.Table.Columns.Add(width)
End If
End If
Next
' Read 100 records at a time and write out the Excel file.
Dim pageIndex As Integer = 0
Dim pageSize As Integer = 100
Dim totalRowsReturned As Integer = 0
Dim rowCounter As Integer = 0
While (Not done)
Dim recList As ArrayList = _
DBTable.GetRecordList(SelectWhereClause.GetFilter(), _
SelectOrderBy, pageIndex, pageSize, totalRowsReturned)
totalRowsReturned = recList.Count
If totalRowsReturned <= 0 Then
done = True
Else
For Each rec In recList
excelRow = excelSheet.Table.Rows.Add()
Dim columnCounter As Integer = 0
For Each elem In ColumnList
Dim exCol As ExcelColumn = CType(elem, ExcelColumn)
Dim col As BaseColumn = exCol.DisplayColumn
If Not IsNothing(col) Then
skip = False
val = ""
If _
col.TableDefinition.IsExpandableNonCompositeForeignKey(col) _
Then
' Foreign Key column, so we will use DFKA and String type.
excelDataType = DataType.String
val = rec.Format(col)
Else
Select Case col.ColumnType
Case BaseColumn.ColumnTypes.Binary, _
BaseColumn.ColumnTypes.Image
' Skip - do nothing for these columns
skip = True
Case BaseColumn.ColumnTypes.Number, _
BaseColumn.ColumnTypes.Currency, _
BaseColumn.ColumnTypes.Percentage
excelDataType = DataType.Number
Dim numVal As ColumnValue = rec.GetValue(col)
' If the value of the column to be exported is
' nothing, add an empty cell to the Excel file
If (numVal.IsNull) Then
excelRow.Cells.Add(New WorksheetCell())
skip = True
Else
val = rec.GetValue(col).ToString()
' If this column is being summed –
' we should add this value to the
' cumulative sum of the column
If elem.SumTotal Then
elem.SumValue = numVal.ToDouble
End If
End If
Case BaseColumn.ColumnTypes.Date
excelDataType = DataType.DateTime
Dim dateVal As ColumnValue = rec.GetValue(col)
If (dateVal.IsNull) Then
excelRow.Cells.Add(New WorksheetCell())
skip = True
Else
' Specify the default Excel format for the
' date field
val = rec.Format(col, "s")
val += ".000"
End If
Case BaseColumn.ColumnTypes.Very_Large_String
excelDataType = DataType.String
val = rec.GetValue(col).ToString
Case BaseColumn.ColumnTypes.Boolean
excelDataType = DataType.String
val = rec.Format(col)
Case Else
excelDataType = DataType.String
val = rec.Format(col)
End Select
End If
' Define a unique column style for each column in the table
' to be exported
If (rowCounter = 0) Then
Dim excelColumnStyle As _
CarlosAg.ExcelXmlWriter.WorksheetStyle = _
excelBook.Styles.Add(exCol.DisplayColumn.ToString() _
& columnCounter)
excelColumnStyle.Font.FontName = "Verdana"
excelColumnStyle.Font.Size = 8
If Not (exCol.DisplayFormat.Equals("Default")) Then
excelColumnStyle.NumberFormat = exCol.DisplayFormat
End If
End If
If Not skip Then
excelRow.Cells.Add(New WorksheetCell(val,excelDataType, _
exCol.DisplayColumn.ToString() & columnCounter))
End If
End If
columnCounter += 1
Next
rowCounter += 1
Next
pageIndex += 1
' If we already are below the pageSize, then we are done.
If totalRowsReturned < pageSize Then
done = True
End If
End If
End While
' Define the header style
Dim excelInfoStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle = _
excelBook.Styles.Add("HeaderInfoStyle")
excelInfoStyle.Font.Bold = True
excelInfoStyle.Font.FontName = "Verdana"
excelInfoStyle.Font.Size = 8
excelInfoStyle.Font.Color = "#000000"
excelInfoStyle.Interior.Color = "#00FF99"
excelInfoStyle.Interior.Pattern = StyleInteriorPattern.Solid
'
' Display Summaries if Needed
'
excelRow = excelSheet.Table.Rows.Add()
excelRow = excelSheet.Table.Rows.Add()
excelRow.Cells.Add(New WorksheetCell("Totals:", "HeaderInfoStyle"))
excelRow.Cells(0).MergeAcross = ColumnList.Count - 1
excelRow = excelSheet.Table.Rows.Add()
For Each elem In ColumnList
If elem.SumTotal Then
excelRow.Cells.Add(New WorksheetCell(elem.SumValue.ToString))
Else
excelRow.Cells.Add(New WorksheetCell(""))
End If
Next
Dim fileName As String
If (Not IsNothing(DBTable.TableDefinition.Name)) Then
fileName = DBTable.TableDefinition.Name & ".xls"
Else
fileName = "Untitled.xls"
End If
response.ClearHeaders()
response.Clear()
response.Cache.SetCacheability(HttpCacheability.Private)
response.Cache.SetMaxAge(New TimeSpan(0))
response.Cache.SetExpires(New DateTime(0))
response.Cache.SetNoServerCaching()
response.ContentType = "application/vnd.ms-excel"
response.AppendHeader("Content-Disposition", "attachment; filename=""" & _
fileName & """")
excelBook.Save(response.OutputStream)
response.End()
End If
End Sub
|
C#:
public class ExcelColumn
{
#region "Properties"
private BaseColumn _column;
private string _format;
private double _ColSumValue;
private Boolean _SumTotal;
#endregion
#region "Constructor"
public ExcelColumn(BaseColumn col, string format)
{
DisplayColumn = col;
DisplayFormat = format;
}
public ExcelColumn(BaseColumn col, string format, Boolean sumColumn)
{
DisplayColumn = col;
DisplayFormat = format;
SumTotal = sumColumn;
}
#endregion
#region "Public Methods"
public BaseColumn DisplayColumn
{
get {
return _column;
}
set {
this._column = value;
}
}
public double SumValue
{
get
{
return _ColSumValue;
}
set
{
this._ColSumValue = this._ColSumValue + value;
}
}
public Boolean SumTotal
{
get
{
return _SumTotal;
}
set
{
this._SumTotal = value;
this._ColSumValue = 0;
}
}
public string DisplayFormat
{
get {
return _format;
}
set {
this._format = value;
}
}
#endregion
}
|
First, if the column is to be summed when adding the values to the spreadsheet, we add its value
to the cumulative column sum. This information is stored in the SumValue property. Next, we add
an additional line of summaries to display the columns totals prior to sending the spreadsheet to
the application user.
The C# Export-To-Excel method will look like this:
public void ExportToExcel(System.Web.HttpResponse response)
{
bool done = false;
string val;
bool skip;
bool skipHeader;
if (!(response == null))
{
CarlosAg.ExcelXmlWriter.Workbook excelBook = new _
CarlosAg.ExcelXmlWriter.Workbook();
excelBook.Properties.Title = DBTable.TableDefinition.Name;
excelBook.Properties.Created = DateTime.Now;
excelBook.Properties.Author = "";
CarlosAg.ExcelXmlWriter.Worksheet excelSheet = _
excelBook.Worksheets.Add("Sheet1");
excelSheet.Options.SplitHorizontal = 1;
excelSheet.Options.FreezePanes = true;
excelSheet.Options.TopRowBottomPane = 1;
CarlosAg.ExcelXmlWriter.WorksheetRow excelRow = _
excelSheet.Table.Rows.Add();
CarlosAg.ExcelXmlWriter.WorksheetStyle excelHeaderStyle = _
excelBook.Styles.Add("HeaderRowStyle");
excelHeaderStyle.Font.Bold = true;
excelHeaderStyle.Font.FontName = "Verdana";
excelHeaderStyle.Font.Size = 8;
excelHeaderStyle.Font.Color = "#FFFFFF";
excelHeaderStyle.Interior.Color = "#000000";
excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid;
int width = 0;
// First write out the Column Headers
foreach (ExcelColumn elem in ColumnList){
ExcelColumn exCol = ((ExcelColumn)(elem));
BaseColumn col = exCol.DisplayColumn;
if (!(col == null)) {
skipHeader = false;
if _
(col.TableDefinition.IsExpandableNonCompositeForeignKey(col)) {
// Set width if field is a foreign key field
width = 100;
}
else {
switch (col.ColumnType) {
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
// Skip - do nothing for these columns
skipHeader = true;
break;
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
width = 60;
break;
case BaseColumn.ColumnTypes.String:
case BaseColumn.ColumnTypes.Very_Large_String:
width = 110;
break;
default:
width = 50;
break;
}
}
if (!skipHeader) {
// excelHeaderStyle.NumberFormat = xCol.DisplayFormat
excelRow.Cells.Add(new WorksheetCell(col.Name, _
"HeaderRowStyle"));
WorksheetColumn worksheetColumn = _
excelSheet.Table.Columns.Add(width);
}
}
}
// Read 100 records at a time and write out the Excel file.
int pageIndex = 0;
int pageSize = 100;
int totalRowsReturned = 0;
int rowCounter = 0;
while (!done)
{
ArrayList recList = _
DBTable.GetRecordList(SelectWhereClause.GetFilter(), _
SelectOrderBy, pageIndex, pageSize, ref totalRowsReturned);
totalRowsReturned = recList.Count;
if ((totalRowsReturned <= 0))
{
done = true;
}
else
{
foreach (BaseRecord rec in recList)
{
excelRow = excelSheet.Table.Rows.Add();
int columnCounter = 0;
foreach (ExcelColumn elem in ColumnList)
{
ExcelColumn exCol = ((ExcelColumn)(elem));
BaseColumn col = exCol.DisplayColumn;
if (col != null)
{
skip = false;
val = "";
if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col))
{
// Foreign Key column, so we will use DFKA and String type.
excelDataType = DataType.String;
val = rec.Format(col);
}
else
{
switch (col.ColumnType)
{
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
// Skip - do nothing for these columns
skip = true;
break;
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Percentage:
excelDataType = DataType.Number;
ColumnValue numVal = rec.GetValue(col);
//If the value of the column to be exported is nothing, add an
//empty cell to the Excel file
if (numVal.IsNull)
{
excelRow.Cells.Add(new WorksheetCell());
skip = true;
}
else
{
val = rec.GetValue(col).ToString();
// If this column is being summed –
// we should add this value to the
// cumulative sum of the column
if (elem.SumTotal)
{
elem.SumValue = numVal.ToDouble();
}
}
break;
case BaseColumn.ColumnTypes.Date:
excelDataType = DataType.DateTime;
ColumnValue dateVal = rec.GetValue(col);
if (dateVal.IsNull)
{
excelRow.Cells.Add(new WorksheetCell());
skip = true;
}
else
{ // Specify the default Excel format for the date field
val = rec.Format(col, "s");
val += ".000";
}
break;
case BaseColumn.ColumnTypes.Very_Large_String:
excelDataType = DataType.String;
val = rec.GetValue(col).ToString();
break;
case BaseColumn.ColumnTypes.Boolean:
excelDataType = DataType.String;
val = rec.Format(col);
break;
default:
excelDataType = DataType.String;
val = rec.Format(col);
break;
}
}
// Define a unique column style for each column in the table to be exported
if ((rowCounter == 0))
{
CarlosAg.ExcelXmlWriter.WorksheetStyle excelColumnStyle = excelBook.Styles.Add((exCol.DisplayColumn.ToString() + columnCounter));
excelColumnStyle.Font.FontName = "Verdana";
excelColumnStyle.Font.Size = 8;
if (!exCol.DisplayFormat.Equals("Default"))
{
excelColumnStyle.NumberFormat = exCol.DisplayFormat;
}
}
if (!skip)
{
excelRow.Cells.Add(new WorksheetCell(val, excelDataType, (exCol.DisplayColumn.ToString() + columnCounter)));
}
}
columnCounter++;
}
rowCounter++;
}
pageIndex++;
// If we already are below the pageSize, then we are done.
if ((totalRowsReturned < pageSize))
{
done = true;
}
}
}
// define the header style
CarlosAg.ExcelXmlWriter.WorksheetStyle excelInfoStyle = excelBook.Styles.Add("HeaderInfoStyle");
excelInfoStyle.Font.Bold = true;
excelInfoStyle.Font.FontName = "Verdana";
excelInfoStyle.Font.Size = 8;
excelInfoStyle.Font.Color = "#000000";
excelInfoStyle.Interior.Color = "#00FF99";
excelInfoStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//
// Display Summaries if Needed
//
excelRow = excelSheet.Table.Rows.Add();
excelRow = excelSheet.Table.Rows.Add();
excelRow.Cells.Add(new WorksheetCell("Totals:", "HeaderInfoStyle"));
excelRow = excelSheet.Table.Rows.Add();
Int32 ElemCounter;
ElemCounter = 0;
foreach (ExcelColumn elem in ColumnList)
{
ExcelColumn exCol = ((ExcelColumn)(elem));
BaseColumn col = exCol.DisplayColumn;
CarlosAg.ExcelXmlWriter.WorksheetStyle excelColumnStyle;
if (elem.SumTotal)
{
excelRow.Cells.Add(new WorksheetCell(elem.SumValue.ToString(), DataType.Number, exCol.DisplayColumn.ToString() + ElemCounter.ToString()));
}
else {
excelRow.Cells.Add(new WorksheetCell(""));
}
ElemCounter += 1;
}
string fileName;
if (DBTable.TableDefinition.Name != null)
{
fileName = (DBTable.TableDefinition.Name + ".xls");
}
else
{
fileName = "Untitled.xls";
}
response.ClearHeaders();
response.Clear();
response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
response.Cache.SetMaxAge(new TimeSpan(0));
response.Cache.SetExpires(new DateTime(0));
response.Cache.SetNoServerCaching();
response.ContentType = "application/vnd.ms-excel";
response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (fileName + "\"")));
excelBook.Save(response.OutputStream);
response.End();
}
}
|
The infrastructure required to add summaries of columns to the Excel spreadsheet is complete. Now we can
use this technique in any Excel report.
Please review the sample case below which utilizes information from the Microsoft Southwind database ("Product
Sales for 1997").
In this example, we need to modify the way numeric columns are handled by the ExportData class. First, override
the method used to export the table contents into Excel. In our case, it is called _Sales_For_1997ExportExcelButton_Click.
Below is the complete code using the new method. The only change is marked in red and flags the product sales
column as being summed.
Visual Basic .NET:
Public Overrides Sub Product_Sales_For_1997ExportExcelButton_Click(ByVal _
sender As Object, ByVal args As ImageClickEventArgs)
Try
DbUtils.StartTransaction()
' To customize the columns or the format, override this function in Section 1
' of the page
' and modify it to your liking.
' Build the where clause based on the current filter and search criteria
' Create the Order By clause based on the user's current sorting preference.
Dim wc As WhereClause = CreateWhereClause()
Dim orderBy As OrderBy = CreateOrderBy()
' Create an instance of the Excel report class with the table lass, where
' clause and order by.
Dim excelReport As ExportData = New _
ExportData(Product_Sales_For_1997View.Instance, wc, orderBy)
' Add each of the columns in order of export.
' To customize the data type, change the second parameter of the new
' ExcelColumn to be
' a format string from Excel's Format Cell menu. For example "dddd, mmmm dd,
' yyyy h:mm AM/PM;@", "#,##0.00"
excelReport.AddColumn(New _
ExcelColumn(Product_Sales_For_1997View.CategoryName, "Default"))
excelReport.AddColumn(New _
ExcelColumn(Product_Sales_For_1997View.ProductName, "Default"))
excelReport.AddColumn(New _
ExcelColumn(Product_Sales_For_1997View.ProductSales, _
"$#,##0.00;($#,##0.00)", True))
excelReport.AddColumn(New _
ExcelColumn(Product_Sales_For_1997View.ShippedQuarter, "Default"))
excelReport.ExportToExcel(Me.Page.Response)
Me.Page.CommitTransaction(sender)
Catch ex As Exception
Me.Page.RollBackTransaction(sender)
Me.Page.ErrorOnPage = True
ScriptManager.RegisterStartupScript(Me.Page, Page.GetType(), _
"ErrorMsg", "alert(" & _
BaseClasses.Web.AspxTextWriter.CreateJScriptStringLiteral(ex.Message) & _
");", True)
Finally
DbUtils.EndTransaction()
End Try
End Sub
Public Sub New(ByVal col As BaseColumn, ByVal format As String, _
ByVal SumColumn As Boolean)
DisplayColumn = col
DisplayFormat = format
SumTotal = SumColumn
End Sub
|
C#:
public override void Product_Sales_For_1997ExportExcelButton_Click(object sender, ImageClickEventArgs args)
{
try
{
DbUtils.StartTransaction();
// To customize the columns or the format, override this function in Section 1 of the page
// and modify it to your liking.
// Build the where clause based on the current filter and search criteria
// Create the Order By clause based on the user's current sorting preference.
WhereClause wc = this.CreateWhereClause();
OrderBy orderBy = CreateOrderBy();
// Create an instance of the Excel report class with the table class, where clause and order by.
ExportData excelReport = new ExportData(Product_Sales_For_1997View.Instance, wc, orderBy);
// Add each of the columns in order of export.
// To customize the data type, change the second parameter of the new ExcelColumn to be
// a format string from Excel's Format Cell menu. For example "dddd, mmmm dd, yyyy h:mm AM/PM;@", "#,##0.00"
excelReport.AddColumn(new ExcelColumn(Product_Sales_For_1997View.CategoryName, "Default"));
excelReport.AddColumn(new ExcelColumn(Product_Sales_For_1997View.ProductName, "Default"));
excelReport.AddColumn(new ExcelColumn(Product_Sales_For_1997View.ProductSales, "$#,##0.00;($#,##0.00)", true));
excelReport.AddColumn(new ExcelColumn(Product_Sales_For_1997View.ShippedQuarter, "Default"));
excelReport.ExportToExcel(this.Page.Response);
this.Page.CommitTransaction(sender);
}
catch (Exception ex)
{
this.Page.RollBackTransaction(sender);
this.Page.ErrorOnPage = true;
BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(this, "BUTTON_CLICK_MESSAGE", ex.Message);
}
finally
{
DbUtils.EndTransaction();
}
}
|
Now a summary line will appear at the bottom of the report displaying a total for each designated
column.
In this example, the spread sheet displays the total number of ‘Product Sales’ in US Dollars.
|
