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