Convert datatable into .xls

• • Reply

  12 years ago

  by GueAnaqSelat

  Ferryandi Chairul

  , Singapore

  Joined 19 years ago

  how do i convert datatable into .xls without using microsoft.excel library

  Report abuse
• • Reply

  12 years ago

  by pameladelafield

  Pamela Delafield

  , Mexico

  Joined 13 years ago

  Does it have to be in .xls format or could you put it into csv (comma seperated value) format, this would be easy to achieve just put a comma between the column values and start each row on a new line 

  Report abuse
• • Reply

  12 years ago

  by GueAnaqSelat

  Ferryandi Chairul

  , Singapore

  Joined 19 years ago

  yes the problem is i need the datatype for the excel. like we need to diffrentiate which one is integer, date and string.

  Report abuse
• • Reply

  12 years ago

  by pameladelafield

  Pamela Delafield

  , Mexico

  Joined 13 years ago

  I don't believe that you can achieve this without using the EXCEL libary, why dont you want to use the libary? 

  Report abuse
• • Reply

  12 years ago

  by GueAnaqSelat

  Ferryandi Chairul

  , Singapore

  Joined 19 years ago

  Its client requirement

  Report abuse
• • Reply

  12 years ago

  by Rob P

  Robert Pegg

  , Canada

  Joined 13 years ago

  You may try thirdpaty products like ComponentOne XLS for .NET (2.0)

  Report abuse
• • Reply

  12 years ago

  by GueAnaqSelat

  Ferryandi Chairul

  , Singapore

  Joined 19 years ago

  I dont want to use third parties!

   

  anyone ?

  Report abuse
• • Reply

  12 years ago

  by quangvo

  Quang Vo

  Perth, Australia

  Joined 12 years ago
  Covert Data Table To Excel With Format I have written a class library which does this with one line of code. The first parameter is the data table, the second one is to include the header or not. The library also has a method call DisplayAndRemoveFile which display the file to user for download after which if it will remove from the temp location. It also has a bunch of other hand util. This is an opensource util which you can look at the code and do further customisation. QVISLib.Utils.Utils.ConvertDTtoExcel(dt, @"c:\\temp\\dataTable.xls", true); For more information on the library and download: **http://connectionstringexamples.com/article.php?story=QVISLib-CHandyLibrary**
  Report abuse
• • Reply

  11 years ago

  by baby123

  mr baby

  Ashqelon, Israel

  Joined 11 years ago
  link for working example: **C# | Asp.Net | Convert DataTable to Excel File ** http://cf-sami.blogspot.com/2009/03/c-aspnet-convert-datatable-to-excel.html
  Report abuse
• • Reply

  11 years ago

  by Muthuvinoth

  Muthu Kumar

  chennai, India

  Joined 11 years ago
  Hi here is some sample of code of converting DataTable to Excel protected void DataTableToExcel() { Response.Clear(); Response.ClearHeaders(); DataTable dt = (DataTable)(Session["dtExport"]); string html = GenerateHTMLTable(dt); Response.AddHeader("Content-Disposition", "attachment;filename="+Request.QueryString["page"]+".xls"); Response.ContentType = "application/vnd.ms-excel"; Response.Write(html); Response.Flush(); Response.Close(); Response.End(); } private string GenerateHTMLTable(DataTable dt) { StringBuilder sb = new StringBuilder(); sb.Append(""); sb.Append(""); foreach (DataColumn dc in dt.Columns) { sb.Append(""); } sb.Append(""); foreach (DataRow dr in dt.Rows) { sb.Append(""); foreach (object obj in dr.ItemArray) { if (isDateTime(obj.ToString())) { string format = GetFormat(obj.ToString()); sb.Append(""); } sb.Append(""); } sb.Append("

  "); sb.Append(dc.ColumnName); sb.Append("
  "); sb.Append(obj.ToString()); sb.Append("\",\""); sb.Append(format); sb.Append("\")"); } else { sb.Append("	"); sb.Append(obj.ToString()); } sb.Append("

  "); return sb.ToString(); }
  Report abuse