Copy table from Excel and paste as html??

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?)

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.

Code:

---

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();
}

---

whoa! nice:D:D:D
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?:D 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?

ooh and one more thing :D
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 :confused: how would i fix that

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?

Quote:

---

Originally Posted by 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?

---

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 :confused:
lol

oh and thanks again for the code :)

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.