C#: OpenXML cannot get columns name print into excel file

Question

This is a console application trying to print data from datatable to excel file using open XML lib. I have also provided datatable code for reference.

class ProgramCheck
        {
            private static void Main()
            {
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\Users\nilam\Desktop\Work\project\GeneratedExcel.xlsx", SpreadsheetDocumentType.Workbook);
    
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
    
    
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
    
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                
                Sheet sheet = new Sheet()
                {
                    Id = workbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "TestSheet2"
                };
    
                sheets.Append(sheet);
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                DataTable dt = Data.SampleData();
    
                foreach (DataRow item in dt.Rows)
                {
    
                    Row row = new Row();
    
                    for (int i = 0; i < item.ItemArray.Length; i++)
                    {
                        foreach (DataColumn itemCol in dt.Columns)
                        {
    
                            Columns col = new Columns();
                            Cell cell1 = new Cell()
                            {
                                CellValue = new CellValue(item[i].ToString()),
                                DataType = CellValues.String
                            };
                            col.Append(cell1);
                            Cell cell = new Cell()
                            {
                                CellValue = new CellValue(item[i].ToString()),
                                DataType = CellValues.String
                            };
                            row.Append(cell);
                        }
                    }
                    sheetData.Append(row);
                }
                workbookPart.Workbook.Save();
                spreadsheetDocument.Close();   
            }
        }

Code for row works fine, but when I try to loop through columns for heading, it does not append anything. I am using openXML to get data from the data table and output it into an excel file.

    This is datatable (data.cs)
    
        class Data
        {
            public static DataTable SampleData()
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("ID");
                dt.Columns.Add("Name");
                dt.Columns.Add("Salary");
    
                dt.Rows.Add("01", "Anis", "1000");
                dt.Rows.Add("02", "AK", "2000");
                dt.Rows.Add("03", "Mak", "3000");
                return dt;
            }
        }
    
    I have looked at the documentation of openXML but could not find anything so far.
    
    [![enter image description here][1]][1]
    
        enter code here
    
      [1]: https://i.sstatic.net/EaPLh.png

Answer 1

I solved this...this is for someone who needs help. This code can be more refined you are welcome to do so and paste it.

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\Users\nilam\Desktop\Work\project\GeneratedExcel.xlsx", SpreadsheetDocumentType.Workbook);
    
            WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();
    
    
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());
    
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    
            Sheet sheet = new Sheet()
            {
                Id = workbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "TestSheet2"
            };
    
            sheets.Append(sheet);
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            DataTable dt = Data.SampleData();
    
            //this is for Columns heading
            Row rowHeading = new Row();
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Cell cell1 = new Cell()
                {
                    CellValue = new CellValue(dt.Columns[j].ColumnName.ToString()),
                    DataType = CellValues.String
                };
                rowHeading.Append(cell1);
            }
            sheetData.Append(rowHeading);
            //this is for row content
            foreach (DataRow item in dt.Rows)
            {
                Row row = new Row();
                for (int i = 0; i < item.ItemArray.Length; i++)
                {
                    Cell cell = new Cell()
                    {
                        CellValue = new CellValue(item[i].ToString()),
                        DataType = CellValues.String
                    };
                    row.Append(cell);
                }
                sheetData.Append(row);
            }
    
            workbookPart.Workbook.Save();
            spreadsheetDocument.Close();
        }