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.