none
Export dataset to Excel (.xlsx) RRS feed

  • Question

  • How to export a dataset to excel in .xlsx format.I am getting content corrupted error.Please help me on this

    Ravi

    Monday, February 23, 2015 10:01 AM

Answers

  • Hi Shankar,

    this is an example for export your data to Excel file, in this example just you need to pass dataset as parameter to this method:>

    private void ExportDataSetToExcel(DataSet ds)
            {
                //Creae an Excel application instance
                Excel.Application excelApp = new Excel.Application();
                
                //Create an Excel workbook instance and open it from the predefined location
                Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx");
    
                foreach (DataTable table in ds.Tables)
                {
                    //Add a new worksheet to workbook with the Datatable name
                    Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                    excelWorkSheet.Name = table.TableName;
    
                    for (int i = 1; i < table.Columns.Count + 1; i++)
                    {
                        excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                    }
    
                    for (int j = 0; j < table.Rows.Count; j++)
                    {
                        for (int k = 0; k < table.Columns.Count; k++)
                        {
                            excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                        }
                    }
                }
    
                excelWorkBook.Save();
                excelWorkBook.Close();
                excelApp.Quit();
    
            }

    don't forget to add namespace to your project (Excel namespace).


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    Monday, February 23, 2015 10:19 AM

All replies

  • Hi Shankar,

    this is an example for export your data to Excel file, in this example just you need to pass dataset as parameter to this method:>

    private void ExportDataSetToExcel(DataSet ds)
            {
                //Creae an Excel application instance
                Excel.Application excelApp = new Excel.Application();
                
                //Create an Excel workbook instance and open it from the predefined location
                Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx");
    
                foreach (DataTable table in ds.Tables)
                {
                    //Add a new worksheet to workbook with the Datatable name
                    Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                    excelWorkSheet.Name = table.TableName;
    
                    for (int i = 1; i < table.Columns.Count + 1; i++)
                    {
                        excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                    }
    
                    for (int j = 0; j < table.Rows.Count; j++)
                    {
                        for (int k = 0; k < table.Columns.Count; k++)
                        {
                            excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                        }
                    }
                }
    
                excelWorkBook.Save();
                excelWorkBook.Close();
                excelApp.Quit();
    
            }

    don't forget to add namespace to your project (Excel namespace).


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    Monday, February 23, 2015 10:19 AM
  • Hello Ravi,

    Please provide your code so we can examine the code and make suggestions, otherwise it's impossible to provide advise to your current code and can only show examples which may or may not suit your needs.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, February 23, 2015 12:34 PM
    Moderator
  • As Kevininstructor said, we can only offer you example that may help you towards right direction -

    Export Datatable to Excel from Database in C#

    Wednesday, February 25, 2015 3:40 AM
  • Ammar,

    That is an awesome solution!  Excel interop is so backwards and mind numbing.  I am just hoping someday they will rewrite that whole namespace.

    Thanks!

    Thursday, February 11, 2016 9:46 PM