Copy table from Excel and paste as html??

[MrPolite]

I'm sending an email via SMTP and I want the HTML content of the email be the table from an excel document. I want to preserve the look and feel of it by converting it to HTML somehow....
how would i do that? (ie given a selection in excel, convert it to HTML?)

[wild_bill]

Here's some code that reads in excel, formats to html, and writes to a file. I've got email code too, but I wasn't sure if you wanted to paste the html into the email, or add a html file as an attachment.

private void btnConvert_Click(object sender, System.EventArgs e)
{
	try
	{
		//retrieve data from excel
		DataTable dt = GetDataFromExcelSheet("c:\\new.xls","Sheet1",false);
		//format data into html table
		string html = GetHtmlFromDataTable(dt);
		//write data to file
		System.IO.StreamWriter writer = new System.IO.StreamWriter("C:\\Temp\\MyTable.html",false);
		writer.Write(html);
		writer.Flush();
		writer.Close();
	}
	catch ( Exception ex)
	{
		Console.WriteLine(ex.ToString());
	}
	

}
private DataTable GetDataFromExcelSheet(string excelPath, string sheetName,bool hasHeaderRow)
{
	string hdr = "No";
	if (hasHeaderRow) hdr = "Yes";
	System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(
		"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + 
		";Extended Properties=\"Excel 8.0;HDR=" + hdr + ";IMEX=1\"");

		System.Data.OleDb.OleDbDataAdapter da = 
			new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", cnn);
		DataSet ds = new DataSet();
		cnn.Open();
		da.Fill(ds,"ExcelSheet");
		return ds.Tables[0];
}
private string GetHtmlFromDataTable(DataTable dt)
{
	System.Text.StringBuilder sb = new System.Text.StringBuilder("<TABLE>");
	foreach (DataRow dr in dt.Rows)
	{
		sb.Append("<TR>");
		foreach (object o in dr.ItemArray)
		{
			sb.Append("<TD>");
			sb.Append(o as string);
			sb.Append("</TD>");
		}
		sb.Append("</TR>");
	}
	sb.Append("</TABLE>");
	return sb.ToString();
}

[MrPolite]

whoa! nice
I dont know much about oleDB and those sorta database conncetions.
btw I've been inserting and modifying the excel file using the office interops.... Is that bad? I mean i could probably do updates and inserts with a database connection like you're doing here, right? which one is considered "better"

oh and also in that function, should you close the connection when done?

[MrPolite]

ooh and one more thing
sb.append (o as string) wasnt working . o.ToString() works fine
also, on some cells i have a date and when it reads it, it just reads it as an integer how would i fix that

[wild_bill]

I think you have to interop for everything but queries (which blow in my opinion). You should add the close call, I just forgot. Sorry about the o as string thing, I'm pretty new to c#. What is the format on the cells with the weird date values?

[MrPolite]

I think you have to interop for everything but queries (which blow in my opinion). You should add the close call, I just forgot. Sorry about the o as string thing, I'm pretty new to c#. What is the format on the cells with the weird date values?

hmm well in C# I just set that cell's value2 property to something like "11/3/06" and when i read the value again in C#, it's an integer like 32564
lol

oh and thanks again for the code

[wild_bill]

I'm not that familiar with the interop stuff, is there a way to set the cell's format type programatically? So instead of having excel try to auto pick the format, you would set it to Date.