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();
}
}