I used Microsoft.Office.Interop to export my DataGridView into Excel, however I'm not willing to use MS Office anymore and shifted to LibreOffice and WPS.
I decided to use an alternative approach (NetOfficeFW), and modified the original code into the following code to export my DataGridView into excel using NetOfficeFW with .Net Framework 4.8 dev,
string time = DateTime.Now.ToString("dd-MM-yyyy");
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Add(Type.Missing);
//Make Worksheet Name
Excel.Worksheet worksheet = null;
//worksheet = workbook.ActiveSheet;
// worksheet = workbook.Sheets["Sheet1"];
worksheet.Name = "MySheetName-" + time;
worksheet.Range("A1", "M1").Interior.Color = XlRgbColor.rgbGrey;
worksheet.Range("A1", "M1").Font.Color = XlRgbColor.rgbWhite;
//Set Colum width
app.Columns.AutoFit();
app.Columns.ColumnWidth = 14;
app.Columns[1].Hidden = true;
app.Columns[2].Hidden = true;
app.Columns[3].ColumnWidth = 13;
app.Columns[4].ColumnWidth = 15;
app.Columns[5].ColumnWidth = 9;
app.Columns[6].ColumnWidth = 9;
app.Columns[7].ColumnWidth = 9;
app.Columns[8].ColumnWidth = 9;
app.Columns[9].ColumnWidth = 9;
app.Columns[10].ColumnWidth = 9;
app.Columns[11].ColumnWidth = 9;
app.Columns[12].ColumnWidth = 9;
app.Columns[13].ColumnWidth = 9;
app.Columns[14].ColumnWidth = 25;
////TextAlignment
app.Columns[3].HorizontalAlignment = XlHAlign.xlHAlignLeft;
app.Columns[4].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[5].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[6].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[7].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[8].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[9].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[10].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[11].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[12].HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Columns[13].HorizontalAlignment = XlHAlign.xlHAlignCenter;
//Set Header & Footer
ckconnection.getGeneralSetting();
worksheet.PageSetup.LeftHeader = "&\"Calibri\"&14&K8B2252" + DEVELOPER NAME;
worksheet.PageSetup.CenterHeader = "&\"Calibri\"&14&K8B2252 REPORT NAME";
worksheet.PageSetup.RightHeader = "&\"Calibri\"&14&K8B2252" + company_name;
worksheet.PageSetup.CenterFooter = "&[Page] of &[Pages]";
worksheet.PageSetup.RightFooter = time;
//Page size and layout
worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
worksheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
// Narrow Margins
worksheet.PageSetup.LeftMargin = app.InchesToPoints(0.25);
worksheet.PageSetup.RightMargin = app.InchesToPoints(0.25);
worksheet.PageSetup.TopMargin = app.InchesToPoints(0.75);
worksheet.PageSetup.BottomMargin = app.InchesToPoints(0.75);
worksheet.PageSetup.HeaderMargin = app.InchesToPoints(0.3);
worksheet.PageSetup.FooterMargin = app.InchesToPoints(0.3);
for (int i = 1; i < gridView.Columns.Count + 1; i++)
{
Excel.Range xlRange = (Excel.Range)app.Cells[1, i];
xlRange.Font.Bold = -1;
xlRange.Borders.LineStyle = XlLineStyle.xlContinuous;
xlRange.Borders.Weight = 1d;
xlRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
app.Cells[1, i] = gridView.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gridView.Rows.Count; i++)
{
for (int j = 0; j < gridView.Columns.Count; j++)
{
Excel.Range xlRange = (Excel.Range)app.Cells[i + 2, j + 1];
xlRange.Borders.LineStyle = XlLineStyle.xlContinuous;
xlRange.Borders.Weight = 1d;
app.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
}
}
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = "MySheetName-" + time + ".xlsx";
if (saveFileDialoge.ShowDialog() == DialogResult.OK)
{
app.ActiveWorkbook.SaveAs(saveFileDialoge.FileName);
}
app.Quit();
unfortunately, there is a highlighted error in the following two lines:
app.Cells[1, i] = gridView.Columns[i - 1].HeaderText;
app.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
The error text is:
can't convert string to NetOffice.Excel.Api.Range
How can I resolve this issue?
Actually i did not find any support from the developers of NetOffice, so i shifted to ClosedXML and it is really easy to use.. Here is the code that working with me without installing MS Office... using ClosedXML