Export HTML Table to Excel and Format The Excel file

Question

I have the following code:

<tfoot>
<tr>
  <td>Tot</td>
  <td>60</td>
  <td></td>
  <td>30</td>
</tr>
<tr>
   <td>Avg</td>
   <td>20</td>
   <td></td>
   <td>10</td>
</tr>

Code Behind:

public string getWhileLoopData() 
{
 string htmlStr = "";
 SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
 SqlCommand thisCommand = thisConnection.CreateCommand();
 thisCommand.CommandText = "SELECT * FROM MyTable WHERE TheDate = @TheDate";
 thisCommand.Parameters.AddWithValue("@TheDate", txtDate.Text);


int totnum1 = 0;
decimal totnum2 = 0;
int numRow = 0;
decimal avg1 = 0;
decimal avg2 = 0;



 thisConnection.Open();
 SqlDataReader reader = thisCommand.ExecuteReader();

 while (reader.Read()) {
     int id = reader.GetInt32(0);

     int Number01 = reader.GetInt32(1);
     DateTime TheDate = reader.GetDateTime(2);
     Decimal Number02 = reader.GetDecimal(3);

     totnum1 += reader.GetInt32(1);
     totnum2 += reader.GetInt32(3);
     numRow ++;

     //string Pass = reader.GetString(2);
     htmlStr += "<tr><td>" + id + "</td><td>" + Number01 + "</td><td>" + TheDate + "</td><td>" + Number02 + "</td></tr>";
 }

 thisConnection.Close();

avg1 = totnum1 / numRow;
avg2 = totnum2 / numRow;

htmlStr += string.Format("<tfoot><tr><td>Tot</td><td>{0}</td><td></td><td>{1}</td></tr>", totnum1 , totnum2 );
htmlStr += string.Format("<tfoot><tr><td>Avg</td><td>{0}</td><td></td><td>{1}</td></tr></tfoot>", avg1 , avg2 );


 return htmlStr;
}

Does anyone know how to export it to an Excel file? But I want the excel file formatted. i.e.: Put an image on the Excel file after it is exported, font size, color, fill color, etc. All formatting needs to be done from code behind.

try [this link][1] i think it work for you! [1]: http://stackoverflow.com/questions/9364107/export-html-table-to-excel-using-asp-net — tuan huynh, Aug 08 '14 at 07:33

score 1 · Accepted Answer · answered Aug 08 '14 at 07:32

1

Your best shot is OpenXML SDK - http://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx. It allows you to generate Excel files.

answered Aug 08 '14 at 07:32

Arnthor

2,563
6
34
54

This one is OK. But is there a way to make it simple? What I meant is let say, when user click on export to excel button. Then pop-up dialog to save the excel file appears. Then when user open the excel file, the excel file already formatted. – Haminteu Aug 11 '14 at 02:40

Export HTML Table to Excel and Format The Excel file

1 Answers1