Export Data to Excel File not in HTML Table Format using C#.NET

If you would like to see how to export data to Excel file not in HTML table using VB.NET, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.ApplicationClass.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}