locked
DataTable To Excel RRS feed

  • Question

  • Dear Experts 

    using following code i get excel sheets at a very fast speed,

    but some cell formatting gets messed up,

    how can i make sure that the cells are properly rendered as per table row cell data type 

                System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                grid.HeaderStyle.Font.Bold = true;
                grid.DataSource = ds;
                grid.DataMember = ds.Tables["billWithItem"].ToString();
    
    
    
                grid.DataBind();
                // render the DataGrid control to a file
                Cursor.Current = Cursors.WaitCursor;
                using (StreamWriter sw = new StreamWriter(bindNavigator1.save2xlfile)) 
                {
                    using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                    {
                        grid.RenderControl(hw);
                    }
                }
    


    • Moved by Stanly Fan Wednesday, August 16, 2017 8:14 AM from windows forms
    Friday, August 4, 2017 4:53 PM

All replies

  • Hi Sushil,

    Based on your description and your code, I guess that your project is asp.net. If not, you can refer to the following sample that importing data from DataGridView to Excel.

    private void button1_Click(object sender, EventArgs e)
            {
                // creating Excel Application  
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application  
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook  
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program  
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.  
                // store its reference to worksheet  
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                // changing the name of active sheet  
                worksheet.Name = "Exported from gridview";
                // storing header part in Excel  
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }
                // storing Each row and column value to excel sheet  
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
                // save the application  
                workbook.SaveAs("D:\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                // Exit from the application  
                app.Quit();
                MessageBox.Show("Import successfully!");
            }
    

    If your project is asp.net, please refer to the asp.net forum. Here is the link:

    https://forums.asp.net/

     

    Best Regards,

    Cherry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Monday, August 14, 2017 6:26 AM
    Monday, August 7, 2017 3:15 AM
  • Thanks Cherry,

    i m using the above code in c#.net in Winform

    the method of using Excel id dam slow.

    and the one i am using is dazzling fast. only i want to know with in a winform how can i set cell styles or formatting it as the table column datatype 

    Monday, August 7, 2017 7:07 PM
  • You say this is a WinForm application?  Why are you using System.Web.UI in the code you posted?  That is an ASP.Net control.  You will not be able to format cells using the simple text writer method you are using.  You will need to use an Excel library such as NPOI or Office Interop as the other posted did his example in.  It will be slower which is unavoidable when comparing to a text writer that isn't doing anything Excel specific; it's just writing plain text.  Chances are when opening any Excel file created with your existing code you get a message stating the format of the file doesn't match the extension.  That is because you are really writing plain text to a file and expecting to open it in Excel.

    NOTE: If you using Office Interop the user must have Office installed on their local PC, and that method would not work reliably if this was an ASP.Net website.

    • Proposed as answer by Stanly Fan Monday, August 14, 2017 6:26 AM
    Monday, August 7, 2017 7:20 PM
  • Hi Sushil, if you don't mind using a 3rd party library for this task, you could try Spire.DataExport, which supports to export data from datatable to popular file formats with high speed. And it does not require MS Office to be installed on machines. 

    Here is a link for your kind reference: How to Export Datatable to Excel Through DataGridview


    • Edited by Ezreal93 Tuesday, August 8, 2017 7:34 AM
    Tuesday, August 8, 2017 7:33 AM
  • Hi Sushil,

    The following code is a ASP.NET control(maybe a WebForm Application):

    System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();

    If you want to use a WinForm control, you can use:

    System.Windows.Forms.DataGridView Dgv = new System.Windows.Forms.DataGridView();

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 14, 2017 6:26 AM