none
Reading/getting Column Headers from Excel in C# RRS feed

  • Question

  • I have spent hours looking for an answer to this on the net but cannot find one. Surely I should just be able to Loop round the columns in an Excel file and read the Column headers or names or whatever they are called. Yet I have tried to code this and VS complains at all the different ways I have tried to code it. Code as follows. (The columns are never fixed, they are dynamic which explains why I must iterate through them.)

    No, MSDn won't accept my code and formats is horribly (why cannot one just copy and paste?) but essentially it is the Header_text  line of code that needs solving: Thanks for any help.

    for (inti = 3; i <= DummyCol_Number - 1; i++) // this will apply it from col 3 to DummyCol_Number

                {

                   Header_Text = xlWS.Columns[i].ColumnHeader;

    // .Name?

    switch(Header_Text)

               

    for(inti = 3; i <= DummyCol_Number - 1; i++) // this will apply it from col 3 to DummyCol_Number


                {

                    Header_Text = xlWS.Columns[i].ColumnHeader;

    // .Name?


                   

    switch(Header_Text)

                    {

                       

    case"LKW":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                       

    case"LIEFNR":

                            xlWS.Columns[i].ColumnWidth = 10;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"REFERENZ":

                            xlWS.Columns[i].ColumnWidth = 10;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"LAGERAUFNR":

                            xlWS.Columns[i].ColumnWidth = 10;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"VORGANG":

                            xlWS.Columns[i].ColumnWidth = 15;

                           

    break;

                       

    case"URABSNR":

                            xlWS.Columns[i].ColumnWidth = 10;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"HAUS_NAME":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                       

    case"HAUS_ORT":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                       

    case"FF_NAME":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                       

    case"FF_ORT":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                       

    case"ZEICHEN":

                            xlWS.Columns[i].ColumnWidth = 15;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"AUFNR":

                            xlWS.Columns[i].ColumnWidth = 10;

                            xlWS.Columns[i].Wraptext =

    true;

                           

    break;

                       

    case"BEMERKUNG":

                            xlWS.Columns[i].ColumnWidth = 10;

                           

    break;

                    }

                }

    • Moved by CoolDadTx Thursday, June 4, 2015 9:32 PM Office related
    Thursday, June 4, 2015 3:58 PM

Answers

  • Hummm, I'm not a C# guru, but I think this is what you're looking for.

                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }

    Here's more sample code.

            private void button7_Click(object sender, EventArgs e)
            {
    
                Excel.Application app = new Excel.Application();
                app.Visible = true;
                Excel.Workbook wb = app.Workbooks.Add(1);
                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
                // changing the name of active sheet
                ws.Name = "Exported from gridview";
    
                ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    ws.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++)
                    {
                        ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
    
                // sizing the columns
                ws.Cells.EntireColumn.AutoFit();
    
                // save the application
                wb.SaveAs("C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\output.xls", 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();
            }

    HTH!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Sansevieria Wednesday, June 10, 2015 7:04 AM
    Thursday, June 4, 2015 7:51 PM

All replies

  • Hummm, I'm not a C# guru, but I think this is what you're looking for.

                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }

    Here's more sample code.

            private void button7_Click(object sender, EventArgs e)
            {
    
                Excel.Application app = new Excel.Application();
                app.Visible = true;
                Excel.Workbook wb = app.Workbooks.Add(1);
                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
                // changing the name of active sheet
                ws.Name = "Exported from gridview";
    
                ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    ws.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++)
                    {
                        ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
    
                // sizing the columns
                ws.Cells.EntireColumn.AutoFit();
    
                // save the application
                wb.SaveAs("C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\output.xls", 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();
            }

    HTH!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Sansevieria Wednesday, June 10, 2015 7:04 AM
    Thursday, June 4, 2015 7:51 PM
  • Hi Sansevieria,

    Are you using Office object  modle to automate the Excel application? As far as I know, there is no columnHeader property under the range object.

    To get the header of the worksheet, we can get the first row in the usedRange. Here is an sample for your reference:

      Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook workbook=excelApp.Workbooks.Open(@"C:\book.xlsx");
                           for (int i = 1; i <= workbook.Worksheets["Sheet1"].UsedRange.Columns.Count; i++)
                {
                    Console.WriteLine(workbook.Worksheets["Sheet1"].Cells[1, i].Value);
                }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 5, 2015 7:12 AM
    Moderator
  • Thanks for your answer but the problem is not one of the Datagrid. if it was the Datagrid it would be no problem as they have column headers. Actually the probelm is resolved now but thanks for your assistance anyway. 
    Monday, June 8, 2015 1:47 PM
  • Thanks - that would have been useful for me but I solved it another way.
    Monday, June 8, 2015 1:48 PM
  • Thank you. I will try that next time. The problem was was that I had not initilaized the Array and also stupidly that i was not actually reading the Headers but the data I wanted was in the 3rd row down - the Header names had been copied there. 
    Wednesday, June 10, 2015 7:04 AM