Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 956
Reply with quote  #1 

A Better Excel Export

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.

 

 

 

Attached Images
Click image for larger version - Name: ExcelExportCompare.jpg, Views: 1377, Size: 1.57 MB 

 
Attached Files
zip LeftSide.zip (1.97 KB, 87 views)
zip RightSide.zip (1.83 KB, 102 views)
doc A_Better_Excel_Export.doc (613.50 KB, 216 views)


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting

moulay

Registered:
Posts: 86
Reply with quote  #2 
you're the best. thank you for sharing.
best wishes for 2013.
TheChief

Avatar / Picture

MVP Developer
Registered:
Posts: 145
Reply with quote  #3 
Thanks Pinbot,
Great Article and documentation

__________________

Former Senior Web Engineer
CEO Simple And Smart Apps
IronSpeed MVP

pk_davidson

Registered:
Posts: 223
Reply with quote  #4 
Hi Bryan:
Can the EPPlus libs be used to open spreadsheet template files, fill in certain ranges and then output the template to a new spreadsheet ?

My brief perusing didn't find anything about opening an existing file, just creating new ones.

thanks

__________________
Paul
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 956
Reply with quote  #5 
Looks like here is some code to read an xlsx file.

http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx

__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
pk_davidson

Registered:
Posts: 223
Reply with quote  #6 
Excellent, thanks.
We are currently using the Microsoft Open XML SDK libraries with an open source wrapper on them.  EPPlus looks to be more developed and supported.  Msofts Open XML SDK is well supported but would take more work to replace the ISD stuff and the wrapper is not really supported.  EPPlus looks like a nice option.

I also found this:
http://excelpackage.codeplex.com/wikipage?title=Using%20a%20template%20to%20create%20an%20Excel%20spreadsheet&referringTitle=Home

This is done in the excel package that was the starting point for EPPlus so it certainly looks like one can EPPlus to read/write a spreadsheet starting with a template file.

Great work again Bryan. Thanks!

__________________
Paul
jimatqsi

Registered:
Posts: 62
Reply with quote  #7 
Thank you, this will be immediately helpful.

Jim
jimatqsi

Registered:
Posts: 62
Reply with quote  #8 
Has anybody done this in VB? I'm getting a compile error, "Utils is an ambiguous name,imported from thenamespaces or types 'OfficeOpenXML,BaseClasses'


miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,660
Reply with quote  #9 
That is because Utils is also in the BaseClasses.  Just fully qualify your use of Utils with the appropriate version: OfficeOpenXML.Utils.xx etc.  That should do it.

HTH,

__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 956
Reply with quote  #10 

Thanks Miles.

I'm not a VB guy.

[biggrin]



__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
jimatqsi

Registered:
Posts: 62
Reply with quote  #11 
Doesn't that also mean finding every reference to the other Utils and fully qualifying them with Baseclasses.Utils? Sounds painful. Might have to go into the code templates to do that and then do a rebuild? I'm not sure how prevalent the use of Utils is but I know it shows up in message displays.

Jim
spda

Registered:
Posts: 71
Reply with quote  #12 
Thanks a lot for this!!! Very well integrated with the iron speed code.
msigala

Registered:
Posts: 6
Reply with quote  #13 
Hello Brian-

Thank you for posting your code.  You did not provide an example field with a date value.  How would you format a date field (e.g., dd/mmm/yyyy) in the context of your example?  I tried the following for my 6th column:

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

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 956
Reply with quote  #14 
Not really.

Maybe setting the column format BEFORE loading the data?

Try this to set the numberformat for the column before setting the date value:

ws.Column(1).Style.Numberformat.Format  = "yyyy-mm-dd"; 

http://stackoverflow.com/questions/9859610/how-to-set-column-type-when-using-epplus





__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
spda

Registered:
Posts: 71
Reply with quote  #15 
ISD is already doing some formatting so I added my appropriate date format to that:

Code:

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;
...



If you don't mind having it as a string like in my case.

Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement