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?
