Click here to Skip to main content
14,424,412 members
Rate this:
Please Sign up or sign in to vote.
See more:
I managed to export datagridview1 to excel worksheet. The problem is i want the header text to start in row 5 and not row 1.

What I have tried:

<blockquote class="quote"><div class="op">Quote:</div> int cr = 1;
            int cc = 1;
           
            //Loop through each row and read value from each column. 
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {

                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. 
                    if (cr == 1)
                    {
                        xlWorkSheet.Cells[cr, cc] = dataGridView1.Columns[j].HeaderText;
                    }

                    cc++;
                }
                cc = 1;
                cr++;


            } 
            //get data:
            //you can erase this if you dont need data from rows:
            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                for (int j = 0; j <dataGridView1.Columns.Count; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
                    
                }
            }</blockquote>
Posted
Updated 18-Aug-17 10:49am
Comments
Richard Deeming 18-Aug-17 14:26pm
   
So change the export to start at row 5. What's the problem?
int cr = 5; // <-- The starting row number
Member 12686587 18-Aug-17 15:48pm
   
Hi. I tried that. The headers disappear. Here is my complete code on button click that exports from form2 to excel. Is it possible to have it go to a template with Logos and Company name instead.
Karthik_Mahalingam 21-Aug-17 7:10am
   
use  Reply  button, to post Comments/query to the user, so that the user gets notified and responds to your text.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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 = 1;
            //int j = 1;
            
            string datestr = DateTime.Now.ToShortDateString();
            //string colName = dataGridView1.Columns[j].HeaderText;
            string filename = "Quote_" + txtName.Text.Replace(" ", "") + "_" + datestr + ".xls";

            int cr = 5;
            int cc = 1;
            
            //Loop through each row and read value from each column. 
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {

                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. 
                    if (cr == 5)
                    {
                        xlWorkSheet.Cells[cr, cc] = dataGridView1.Columns[j].HeaderText;
                    }

                    cc++;
                }
                cc = 1;
                cr++;


            } 
            //get data:
            //you can erase this if you dont need data from rows:
            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                for (int j = 0; j <dataGridView1.Columns.Count; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
                    xlWorkSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    
                }
            }
  

            xlWorkBook.SaveAs(filename, 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:\\quote.xls");
        }
   
v2
Comments
Member 12686587 19-Aug-17 9:28am
   
Thank you. I had to change if (cr==5) as well. It works perfectly on a new excel worksheet. If I try to send it to a template the qty and price is blank
CHill60 19-Aug-17 11:27am
   
Why are you thanking yourself for a solution that you posted to your own question?
Richard MacCutchan 19-Aug-17 11:49am
   
One can never receive too much praise :)
RickZeeland 19-Aug-17 15:02pm
   
schizophrenic ?
Karthik_Mahalingam 21-Aug-17 7:09am
   
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100