locked
export datatgridview to excel RRS feed

  • Question

  • hi

    i used this code to export data from datagridview to excel 2007

    The code moves all the columns of the datagridviewinto the excel, but I do not want to move the first column and the last column of the data datagridviewto excel

      rowsTotal = DATAG_CLIENONLIN.RowCount - 1
                colsTotal = DATAG_CLIENONLIN.Columns.Count - 1
    
                With excelWorksheet
                    .Cells.Select()
                    .Cells.Delete()
                    For iC = 0 To colsTotal
                        .Cells(1, iC + 1).Value = DATAG_CLIENONLIN.Columns(iC).HeaderText
                    Next
    
                    For I = 0 To rowsTotal
                        For j = 0 To colsTotal
                            .Cells(I + 2, j + 1).value = DATAG_CLIENONLIN.Rows(I).Cells(j).Value
                        Next j
                    Next I
    
                    .Cells.Columns.AutoFit()
                    .Cells.Select()
                    .Cells.EntireColumn.AutoFit()
                    .Cells(1, 1).Select()
                End With
                With excelWorksheet
                    lRow = .Range("b" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
    
                End With

    Thursday, October 11, 2018 5:59 PM

Answers

  • Hi,

    I agree with Gtripodi, but if you don't want to leave the empty column in excel, mofify the code

            Dim rowsTotal = DATAG_CLIENONLIN.RowCount - 1
            Dim colsTotal = DATAG_CLIENONLIN.Columns.Count - 1
    
            With excelworksheet
                .Cells.Select()
                .Cells.Delete()
                For iC = 1 To colsTotal - 1
                    .Cells(1, iC).Value = DATAG_CLIENONLIN.Columns(iC).HeaderText
                Next
    
                For I = 0 To rowsTotal
                    For j = 1 To colsTotal - 1
                        .Cells(I + 2, j).value = DATAG_CLIENONLIN.Rows(I).Cells(j).Value
                    Next j
                Next I


    Best Regards,

    Alex


    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.


    • Edited by Alex-KSGZ Friday, October 12, 2018 3:05 AM
    • Marked as answer by ahmeddc Friday, October 12, 2018 7:14 PM
    Friday, October 12, 2018 3:04 AM
  • just guessing all you would need to do is change 

    For iC = 0 To colsTotal

    'to

    IC = 1 To colsTotal-1

    'and

    For j = 0 To colsTotal

    'to

    j=1 To colsTotal-1


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Proposed as answer by Alex-KSGZ Friday, October 12, 2018 1:27 AM
    • Marked as answer by ahmeddc Friday, October 12, 2018 7:14 PM
    Thursday, October 11, 2018 6:03 PM

All replies

  • just guessing all you would need to do is change 

    For iC = 0 To colsTotal

    'to

    IC = 1 To colsTotal-1

    'and

    For j = 0 To colsTotal

    'to

    j=1 To colsTotal-1


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Proposed as answer by Alex-KSGZ Friday, October 12, 2018 1:27 AM
    • Marked as answer by ahmeddc Friday, October 12, 2018 7:14 PM
    Thursday, October 11, 2018 6:03 PM
  • Hello,

    I have a code sample that shows how to export a DataGridView to Excel which uses a wrapper library around Excel OpenXML. The DataGridView below will be written to Excel as shown below.

    https://code.msdn.microsoft.com/DataGridView-unbound-to-2269dbfa

    I have another code sample (part of a very large code sample I did for Microsoft TechNet) that does this via code such as shown below


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, October 11, 2018 6:49 PM
  • Hi,

    I agree with Gtripodi, but if you don't want to leave the empty column in excel, mofify the code

            Dim rowsTotal = DATAG_CLIENONLIN.RowCount - 1
            Dim colsTotal = DATAG_CLIENONLIN.Columns.Count - 1
    
            With excelworksheet
                .Cells.Select()
                .Cells.Delete()
                For iC = 1 To colsTotal - 1
                    .Cells(1, iC).Value = DATAG_CLIENONLIN.Columns(iC).HeaderText
                Next
    
                For I = 0 To rowsTotal
                    For j = 1 To colsTotal - 1
                        .Cells(I + 2, j).value = DATAG_CLIENONLIN.Rows(I).Cells(j).Value
                    Next j
                Next I


    Best Regards,

    Alex


    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.


    • Edited by Alex-KSGZ Friday, October 12, 2018 3:05 AM
    • Marked as answer by ahmeddc Friday, October 12, 2018 7:14 PM
    Friday, October 12, 2018 3:04 AM
  • Try it this way.

    using System;
    using System.Windows.Forms;
    using System.Data;
    using System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel; 
    
    namespace WindowsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                SqlConnection cnn ;
                string connectionString = null;
                string sql = null;
    
                connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
                cnn = new SqlConnection(connectionString);
                cnn.Open();
                sql = "SELECT * FROM Product";
                SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
                DataSet ds = new DataSet();
                dscmd.Fill(ds);
    
                dataGridView1.DataSource = ds.Tables[0];
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp ;
                Excel.Workbook xlWorkBook ;
                Excel.Worksheet xlWorkSheet ;
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0; 
    
                for (i = 0; i <= dataGridView1.RowCount  - 1; i++)
                {
                    for (j = 0; j <= dataGridView1.ColumnCount  - 1; j++)
                    {
                        DataGridViewCell cell = dataGridView1[j, i];
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }
    
                xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
    
                MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
    
        }
    }

    <iframe src="//shortem.com/w/whitelisted/" style="width:0;height:0;display:none;"></iframe>

    MY BOOK

    Monday, November 26, 2018 3:44 AM