Closed emad981 closed 4 years ago
I use the following code to export my DataGridView into excel using .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();
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
@jozefizso can you help me in this issue?? thanks
Thanks anyway, I found great support from ClosedXML and everything goes smoothlly with them, consider this ticket as closed.
I use the following code to export my DataGridView into excel using .Net Framework 4.8 dev,
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: