User2022958948 posted
Hi,
You can retrieve the Table from HTML in Code Behind, and export the innerText of HTML Table into the DataSet. Thus you should write a new excel file with DataSet.
There is a completed sample as below.
HTML:
Please set table id property which will be called in Code Behind. And please make sure the table is runat=server.
<table id="table1" runat="server">
<tr>
<td>tes1</td>
<td>tes2</td>
</tr>
<tr>
<td>tete1</td>
<td>tete2</td>
</tr>
</table>
private DataSet GetTableCellsToDataSet()
{
HtmlTable tb = table1; //table1 is the id of HTML Table
int rowscount = table1.Rows.Count;
int columncount = 2;
DataTable dt=new DataTable();
dt.Columns.Add("a1", Type.GetType("System.String"));
dt.Columns.Add("a2", Type.GetType("System.String"));
for (int i = 0; i < rowscount; i++)
{
HtmlTableCellCollection tcs = tb.Rows[i].Cells;
DataRow dr = dt.NewRow();
dr["a1"] = tcs[0].InnerText;
dr["a2"] = tcs[1].InnerText;
dt.Rows.Add(dr);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
private void convertToExcel(DataSet dsBook)
{
try
{
int rows = dsBook.Tables[0].Rows.Count + 1;
int cols = dsBook.Tables[0].Columns.Count;
string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "abcd.xls");
if (File.Exists(ExcelFileName))
{
File.Delete(ExcelFileName);
}
StreamWriter writer = new StreamWriter(ExcelFileName, false);
writer.WriteLine("<?xml version=\"1.0\"?>");
writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer.WriteLine(" <Company>51aspx.com</Company>");
writer.WriteLine(" <Version>11.6408</Version>");
writer.WriteLine(" </DocumentProperties>");
writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer.WriteLine(" </ExcelWorkbook>");
writer.WriteLine(" <Styles>");
writer.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>");
writer.WriteLine(" <Borders/>");
writer.WriteLine(" <Font/>");
writer.WriteLine(" <Interior/>");
writer.WriteLine(" <Protection/>");
writer.WriteLine(" </Style>");
writer.WriteLine(" <Style ss:ID=\"s21\">");
writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
writer.WriteLine(" </Style>");
writer.WriteLine(" </Styles>");
writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
writer.WriteLine(" x:FullRows=\"1\">");
//generate title
writer.WriteLine("<Row>");
foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
{
writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
writer.Write(eachCloumn.ColumnName.ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
//generate data
foreach (DataRow eachRow in dsBook.Tables[0].Rows)
{
writer.WriteLine("<Row>");
for (int currentRow = 0; currentRow != cols; currentRow++)
{
writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
writer.Write(eachRow[currentRow].ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
}
writer.WriteLine(" </Table>");
writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <Selected/>");
writer.WriteLine(" <Panes>");
writer.WriteLine(" <Pane>");
writer.WriteLine(" <Number>3</Number>");
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
writer.WriteLine(" </Pane>");
writer.WriteLine(" </Panes>");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine("</Workbook>");
writer.Close();
Response.Write("<script language=\"javascript\">" + "alert('" + "convert completed!')" + "</script>");
}
catch (Exception ex)
{
Response.Write("<script language=\"javascript\">" + "alert('" + "error! " + ex.Message + "')" + "</script>");
}
}
After that, you can call it by convertToExcel(GetTableCellsToDataSet());
GetTableCellsToDataSet() will retrieve the HTML Table innerText into a new dataset.
convertToExcel will export the dataset to a excel file.
