Articles → .NET → Export Datagridview data to Excel using OpenXML in C#

Export Datagridview data to Excel using OpenXML in C#






Code


public void ExportDataToExcel(DataGridView dgView, string fileName, string sheet_name)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheet_name };

                sheets.Append(sheet);

                Row headerRow = new Row();

                List<String> visibleColumns = new List<string>();
                foreach (DataGridViewColumn column in dgView.Columns)
                {
                    if (column.GetType() == typeof(DataGridViewTextBoxColumn))
                    {
                        if (column.Visible == true)
                        {
                            visibleColumns.Add(column.Name);

                            Cell cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(column.HeaderText);
                            headerRow.AppendChild(cell);
                        }
                    }

                }

                sheetData.AppendChild(headerRow);


                foreach (DataGridViewRow row in dgView.Rows)
                {
                    Row newRow = new Row();
                    foreach (string column in visibleColumns)
                    {
                        Cell cellRow = new Cell();
                        cellRow.DataType = CellValues.String;
                        if (dgView.Rows[row.Index].Cells[column].Value != null)
                            cellRow.CellValue = new CellValue(dgView.Rows[row.Index].Cells[column].Value.ToString());
                        newRow.AppendChild(cellRow);
                    }
                    sheetData.AppendChild(newRow);
                }

                workbookPart.Workbook.Save();
            }
        }



Posted By  -  Karan Gupta
 
Posted On  -  Monday, November 18, 2019

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250