Bryan Patrick –Pseudo Consulting

December 31, 2012

Introduction

I recently ran into some problems with the Excel Export feature of Iron Speed in one of my Iron Speed 8.0.2 applications.  I went looking for a replacement export feature.  I tried using the Excel Export feature of my report writer and while that worked well, it would time out on large data sets. 

(The Excel Export in 9.2 is improved over 8.0.2 but using this export will give you even greater control.)

I tried to find something that would require very little change to the code generated by Iron Speed and would leverage the power already present (using table filtering/sorting, database “Label Text”  for column headings, etc).

The solution I found was an open source project on CodePlex called “EPPlus”.

http://epplus.codeplex.com/

This is an nice article (with code samples) that shows some of the features in EPPlus...
http://zeeshanumardotnet.blogspot.com/2011/06/creating-reports-in-excel-2007-using.html

One of the best features I like is the “AutoFitColumns” function which automatically sets the column widths to fit all the data in the columns!

Implementation

Modifing your Iron Speed application to use EPP is quite simple:

1.       Download the binaries from the link above and copy to your \bin folder.

2.       Add a reference to the EPP dll in Visual Studio.

3.       Add these lines at the top of your “controls” file:

using OfficeOpenXml;

using OfficeOpenXml.Style;

using System.Drawing;

4.       Modify the Iron Speed generated function as outlined below.  I used WinMerge to show the differences before and after of a standard Excel Export button. 

I’ve attached both files (LeftSide.cs and RightSide.cs) and annoted the changes below.  LeftSide.cs is the unmodified code generated by Iron Speed.  RightSide.cs is the code after I’ve modified it to use EPPlus.

(click the image to view larger)

 Conclusion

With a simple modification to the generated Excel Export code, you can improve the quality of your exported Excel files from your Iron Speed application.

About the Author

C. Bryan Patrick II has been a professional database developer since the age of 16.  He has a Bachelor of Arts in Computer Science from The University of Texas at Austin.  At 26 Bryan received a US Patent (# 5,142,624) for work done during his college years.  He is a member of the Microsoft Partner Program and has been self-employed for the last 10 years as a small business IT consultant and web application developer.

using (ExcelRange col = ws.Cells[2, 6, 2 + Row - 1, 6])
{
col.Style.Numberformat.Format = "dd/mmm/yyyy";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}

The data is being shown as '4/1/2015 12:00:00 AM' (for example) in the output until I click on each cell and hit Enter when it will show '01/Apr/2015'.  Any thoughts?

Thank You,
Marco

switch (col.ColumnType)
 {
...
case BaseColumn.ColumnTypes.Date:
if (rec.GetValue(col).ToString() != null)
 {
 worksheet.Cells[row, column++].Value = rec.GetValue(col).ToDateTime().ToString("yyyy-MM-dd");
 }
 else
 worksheet.Cells[row, column++].Value = rec.GetValue(col).ToString();
 break;
...