Generate Excel sheet from DataSet

Question

Hi,

I want to create an excel sheet from html table, so i first get the records in List and then convert this list
into Dataset and now i want to create excel sheet from this dataset but the main problem is that i dont want to
Include objects in excel sheet..like in my query i applied inner join and other models are also getting bind
so i am getting their name in Excel sheet..like system.collection.generic or "Student" ,the whole column display values
like this. How to remove these columns. Or is there any way to hide the columns after generating excel.

Any  help is highly appropriated.

Regards,

Mithran.

---

Best Regards, Mithran.

Answer 1

We can create a so called "comma delimited" excel file, which can have a *.csv extension.

Check here for a solution:

StringBuilder sb = new StringBuilder(); 
var columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in ds.Tables[0].Rows) //specify from which dataset and datatable you wanna read datqa
{
    var fields = row.ItemArray.Select(field => field.ToString()).ToArray();
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("myFile.csv", sb.ToString()); //cpecify the path (can be even a full one, like: @"C:\Myfolder\myfile.csv";)

Hope it helps, 

bye

---

Mitja

Answer 2

Use the Columns property to get the columns you want 

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.columns.aspx

Then use Delete to delete the range you selected

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.delete.aspx

Regards

Answer 3

Hi,

Serguey thanks for the links. But I want Oledb for the connection to excel and want to save it.

Answer 4

Hi MithranM,

  I strongly recommend you that you need to read this document:

  DataSet to Excel File Conversion using ExcelLibrary

  http://www.codeproject.com/Tips/313731/How-to-convert-DataSet-to-Excel-workbook-xls-using

  Sincerely,

  Jason Wang

---

Jason Wang [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Check following link, it may be helpful to you.

1. http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/
2. http://snippets.dzone.com/posts/show/7762

DataSet ds = new DataSet();
                //load dataset "ds"
                ds = (DataSet)ViewState["CurrentDS"];
                DataView dv = new DataView(ds.Tables[0]);
                dv.Sort = "PercentClosed DESC";


                System.Text.StringBuilder builder = new System.Text.StringBuilder();
                DataTable dt = dv.Table;

                string headerToExport = string.Empty;

                foreach (DataColumn col in dt.Columns)
                    headerToExport += (char)34 + col.ColumnName + (char)34 + (char)44;

                headerToExport.Remove(headerToExport.Length - 1, 1);
                headerToExport = headerToExport + Environment.NewLine + Environment.NewLine;
                builder.Append(headerToExport);

                string body = string.Empty;

                foreach (DataRow row in dt.Rows)
                {
                    foreach (object obj in row.ItemArray)
                        body = body + obj.ToString() + (char)44;

                    body.Remove(body.Length - 1, 1);

                    body = body + Environment.NewLine;
                }

                builder.Append(body);
                builder.Append(Environment.NewLine);
                builder.Append(Environment.NewLine);


                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("content-disposition", "attachment;filename=myfile.csv");
                Response.Charset = "";
                Response.Write(builder.ToString());
                Response.Flush();
                Response.End();

Answer 6

Hi, MithranM.

With Free Spire.DataExport:

this.cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
   this.cellExport1.AutoFitColWidth = true;
            this.cellExport1.DataFormats.CultureName = "en-us";
   this.cellExport1.DataFormats.Currency = "?#,###,##0.00";
   this.cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
   this.cellExport1.DataFormats.Float = "#,###,##0.00";
   this.cellExport1.DataFormats.Integer = "#,###,##0";
   this.cellExport1.DataFormats.Time = "H:mm";
   this.cellExport1.FileName = "sample.xls";
   this.cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial";
            this.cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial";
   this.cellExport1.SheetOptions.DefaultFont.Name = "Arial";
   this.cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial";
   this.cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial";
   this.cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
   this.cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
   this.cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
   this.cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
   this.cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
   this.cellExport1.SheetOptions.NoteFormat.Font.Bold = true;
   this.cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma";
   this.cellExport1.SheetOptions.NoteFormat.Font.Size = 8F;
   this.cellExport1.SheetOptions.TitlesFormat.Font.Bold = true;
   this.cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial";
            this.cellExport1.DataSource = ExportSource.ListView;
            this.cellExport1.ListView = this.ListView1;
              cellExport1.SaveToFile();

And If you try to use GridView, Try to see this blog: http://c-sharp-paradise.blogspot.com/2012/04/best-solution-to-export-datatable-to.html