Using ExcelPackage to create Excel sheets on server
In one of my community projects I needed to output some listings as Excel file. As installing Excel to server is non-sense that I was easily able to avoid I found simple solution for Excel 2007 files – open-source project called ExcelPackage. In this posting I will show you hot to create simple event attendees report in Excel 2007 format using ExcelPackage.
Cautions
Although ExcelPackage works well for me here are some things you should be aware of.
- ExcelPackage needs file system access because compression library it uses is designed so.
- There is only very old source code available and it is published under GPL. So if you are writing application to your customers then you cannot use this library unless you make your whole application open-source.
- ExcelPackage has also some technical problems and it is not very easy to use in simple cases. Authors have not provided any new releases since the beginning of 2007 so I have good reason to consider this project as abandoned.
- You may find the extensive package EPPlus also useful as there are new versions coming over time. EPPlus is also published under GPL (because ExcelPackage is under GPL), so you can use it only on very limited manner.
If you don’t afraid some s*itfight with technology and GPL is okay for your system then let’s go on.
Exporting event attendees list to Excel
Suppose we have list with event attendees and we want to export it to Excel. We are behaving normally and we don’t install Excel desktop software to our web server. Here is the code.
void ExportToExcel(Event evt)
{
var fileInfo = new FileInfo(Path.GetTempPath() + "\\" +
DateTime.Now.Ticks + ".xlsx");
using (var xls = new ExcelPackage(fileInfo))
{
var sheet = xls.Workbook.Worksheets.Add(evt.Title);
sheet.Cell(1, 1).Value = "First name";
sheet.Cell(1, 2).Value = "Last name";
sheet.Cell(1, 3).Value = "E-mail";
sheet.Cell(1, 4).Value = "Phone";
sheet.Cell(1, 5).Value = "Registered";
sheet.Cell(1, 6).Value = "Live Meeting";
var i = 1;
foreach(var attendee in evt.Attendees)
{
i++;
var profile = attendee.Profile;
sheet.Cell(i, 1).Value = profile.FirstName;
sheet.Cell(i, 2).Value = profile.LastName;
sheet.Cell(i, 3).Value = profile.Email;
sheet.Cell(i, 4).Value = profile.Phone;
sheet.Cell(i, 5).Value = att.Created.ToString();
sheet.Cell(i, 6).Value = att.LiveMeeting.ToString();
}
xls.Save();
}
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats";
Response.AddHeader("Content-Disposition",
"attachment; filename=" + fileInfo.Name);
Response.WriteFile(fileInfo.FullName);
Response.Flush();
if (fileInfo.Exists)
fileInfo.Delete();
}
And here is the result.
Although it is possible to make this list more effective and nice it works and users can start using it until all the nice bells and whistles are coming.
Conclusion
After some fighting with technology it was not very hard to get nice Excel 2007 sheets coming out from our server. We used ExcelPackage library to create list of event attendees and our event organizers can now simply download data to Excel if they need to contact with attendees or manage their data using Excel tools.
Why not use the Microsoft Open XML SDK directly?
Thanks for question, Robert! I am still discovering OpenXML SDK. This code was written actually some time ago and it was good solution that worked for us well.
We need to install MS Excel 2007 on server
How to set excel column and row to a text format,because for date time its converting into integer values.
Its a great POC for creating an excel file in 2007
but I am getting a message after creatin the excel
“Excel found Unreadable Content in filename.xlsx.Do you want to recover the contents of the workbook? if u trust the source of this workBook, click Yes”
Please resolve this issue
Thanks
Tanmay
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
namespace WindowsFormsApplication5
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection(“Data Source=SHOEB-PC;Initial Catalog=SHOEB;Integrated Security=True”);
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = “LocationCode”;
xlWorkSheet.Cells[1, 2] = “LocationName”;
xlWorkSheet.Cells[1, 3] = “StateCode”;
xlWorkSheet.Cells[1, 4] = “StateName”;
xlWorkSheet.Cells[1, 5] = “Zonecode”;
xlWorkSheet.Cells[1, 6] = “ZoneName”;
xlWorkSheet.Name = “Locationmaster”;
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
//xlWorkSheet.Cells[1, 1] = “Location”;
//xlWorkSheet.Cells[1, 2] = “Location”;
//xlWorkSheet.Cells[1, 3] = “State”;
//xlWorkSheet.Cells[1, 4] = “State”;
//xlWorkSheet.Cells[1, 5] = “Zone “;
//xlWorkSheet.Cells[1, 6] = “Zone”;
//xlWorkSheet.Name = “prince”;
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
//xlWorkSheet.Cells[1, 1] = “Lo”;
//xlWorkSheet.Cells[1, 2] = “Lo”;
//xlWorkSheet.Cells[1, 3] = “St”;
//xlWorkSheet.Cells[1, 4] = “St”;
//xlWorkSheet.Cells[1, 5] = “Zo”;
//xlWorkSheet.Cells[1, 6] = “Zo”;
//xlWorkSheet.Name = “prince1”;
string date = DateTime.Now.ToString(“ddMMyyyy”);
xlWorkBook.SaveAs(“C:\\Users\\SHOEB\\Desktop\\revlonupload “+date+”.csv”, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
con.Open();
SqlCommand cmd = new SqlCommand(“prince1”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@filename”,SqlDbType.VarChar).Value=”C:\\Users\\SHOEB\\Desktop\\revlonupload “+date+”.csv”;
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(“Excel file created , you can find the file C:\\Users\\SHOEB\\Desktop\\shprince.xls”);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show(“Exception Occured while releasing object ” + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
You may need to check ZetExcel
A friend directed me to try ZetExcel and I find it very helpful
You can try ZetExcel.com If you need Excel generation functionality for your .net application