Wednesday, March 19, 2008

Write Microsoft Excel File in ASP .NET

As usual , I want to share my experience in my last project. The topic is how to write excel file in ASP .NET

1. Use Response object from System.Web
First, you have to make the content in HTML table code. Here is the example:

string filename = "myexcel.xls";
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "inline;filename=" + filename);
Response.Charset = "";
this.EnableViewState = false;
Response.Write("<table border='1'><tr><th>test title</th><th>test title2</th></tr><tr><td>value1</td><td>value2</td></tr></table>");
Response.Flush();
Response.Close();

Here is the result if you execute this code


2. Another way to create excel file is using this class below( I forgot where I took this code. I will post the source URL as soon as I find it). Don't forget to add Microsoft Office 12.0 object library reference. Add this line of code to the using section

using Excel = Microsoft.Office.Interop.Excel;

Here is the class code :

class ExcelHelper
{
private Excel.Application _excelApplication;

public ExcelHelper()
{
_excelApplication = new Excel.Application();
}

public Excel.Workbook Create(string caption, string heading1)
{
try
{
_excelApplication.Caption = caption;
_excelApplication.ScreenUpdating = false;
_excelApplication.Visible = false;

Excel.Workbook book = _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;

Excel.Range r = (Excel.Range)sheet.Cells[1, "A"];
r.Value2 = heading1;
r.EntireRow.Font.Bold = true;

return book;

}
catch (Exception ex)
{
throw (ex);
}
}

public void Close()
{
_excelApplication.ScreenUpdating = true;
_excelApplication.Visible = true;
_excelApplication.DisplayAlerts = true;

if (_excelApplication != null)
{
_excelApplication.Quit();
_excelApplication = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}

I will give two examples how to use this ExcelHelper class:
- First, this example will write cell A4 and B4 with "Parent Directory" and "Testing" and also make all the cell font bold.

ExcelHelper _eh = new ExcelHelper();
object _missing = System.Reflection.Missing.Value;
Excel.Workbook _book;
Excel.Worksheet _sheet;
Excel.Range _rng;
int _row = 4;
string caption = "Test Caption";
string heading1 = "Test Heading1";
_book = _eh.Create(caption, heading1);
_sheet = ((Excel.Worksheet)_book.ActiveSheet);

_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
_rng.Font.Bold = true;
_rng.EntireRow.Font.Bold = true;

_sheet.Columns.ColumnWidth = 30;

_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = "Parent Directory";
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = "Testing";

_eh.Close();

- Second, use this ExcelFileReport class (I took this code from someone's blog, I will post the source URL as soon as I find it)

public class ExcelFileReport
{
private object _missing;
private Excel.Workbook _book;
Excel.Worksheet _sheet;
Excel.Range _rng;
int _row;
private DirectoryInfo _di;
ExcelHelper _eh = new ExcelHelper();

public ExcelFileReport(DirectoryInfo di)
{
_di = di;
_missing = System.Reflection.Missing.Value;
_row = 4;
}

public void DocumentDirectory(DirectoryInfo di)
{
foreach (DirectoryInfo d in di.GetDirectories())
{
DocumentDirectory(d);
}

foreach (FileInfo f in di.GetFiles())
{
_row++;
_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = di.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = f.FullName;
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = f.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = f.Length;
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = f.Extension;
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = f.LastWriteTime.ToLongDateString();
}
}

public void Generate()
{
string caption = "File Analysis Results";
string heading1 = "File Analysis Report for Folder " + _di.FullName;
_book = _eh.Create(caption, heading1);
_sheet = ((Excel.Worksheet)_book.ActiveSheet);
WriteTableHeader();
DocumentDirectory(_di);
SetAutoFilter();
_eh.Close();
}

private void SetAutoFilter()
{
string lastrow = "F" + _row.ToString();
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
_rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
_rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}

public void WriteTableHeader()
{
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
_rng.Font.Bold = true;
_rng.EntireRow.Font.Bold = true;

_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = "Parent Directory";
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = "Full Path";
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = "File Name";
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = "Size";
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = "Type";
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = "Last Modified";

_sheet.Columns.ColumnWidth = 30;
}
}
And use this code to invoke the Generate method :

DirectoryInfo di = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE12\1033");
ExcelFileReport efr = new ExcelFileReport(di);
efr.Generate();

Please remind that these two examples will run Microsoft Excel and "Do you want to save" dialog will pop up. Therefore you must have Microsoft Excel installed in the PC you want to execute this code. Here is the result if you execute the last code

If you have any suggestion or have better and optimized code, please put your comment. Any suggestion will be appreciated.

4 comments:

Anonymous said...

I tried both ExcelHelper and ExcelFileReport samples you posted but cant seem to make it work in asp.net application. The page generated is blank.

I would really appreciate it you could help me out on this.

agoesz said...

My example may need administrator rights (because it reads files and directories name in your computer). My guess is you should have administrator rights. Sorry I just tested this example in my own computer:)

vincent said...

Both methods have disadvantage.

The first method is using HTML format in the Excel file and won't work in version 2003 below. And it is lack of features because of the HTML format.

The second method needs us to install Excel in the server, which client does not usually want to do this. And it can make the Excel application exists in the server memory if it is not closed properly.

The best method is to use third party component :D

agoesz said...

thanks for your advice, vincent. could you give me any third party control which is free :P , if available ? or are there any good third party controls that you recommend ?