我使用Microsoft.Office.Interop将DataGridView导出到Excel中，但我不愿意再使用MS Office，而是改用LibreOffice和WPS。
我决定使用另一种方法(NetOfficeFW(，并将原始代码修改为以下代码，使用带有.Net Framework 4.8 dev、的NetOfficeFW将我的DataGridView导出到excel中
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();
不幸的是，在以下两行中有一个突出显示的错误：
app.Cells[1, i] = gridView.Columns[i - 1].HeaderText;
app.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
错误文本为：
无法将字符串转换为NetOffice.Excel.Api.Range
如何解决此问题？
实际上，我没有从NetOffice的开发人员那里找到任何支持，所以我转向了ClosedXML，它真的很容易使用。。以下是在不安装MS Office的情况下与我一起工作的代码。。。使用ClosedXML
//Creating DataTable.
DataTable dt = new DataTable();
//Adding the Columns.
foreach (DataGridViewColumn column in gridView.Columns)
{
dt.Columns.Add(column.HeaderText, typeof(string));
}
//Adding the Rows.
foreach (DataGridViewRow row in gridView.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
//Exporting to Excel
using (XLWorkbook workbook = new XLWorkbook())
{
string time = DateTime.Now.ToString("dd-MM-yyyy");
// add a new Workbook and worksheet
var worksheet = workbook.Worksheets.Add(dt, "SheetName-" + time);
workbook.Worksheet(1).Cells("A1:M1").Style.Fill.BackgroundColor = XLColor.Gray;
workbook.Worksheet(1).Cells("A1:M1").Style.Font.FontColor = XLColor.White;
workbook.Worksheet(1).Cells("A1:M1").Style.Font.Bold = true;
worksheet.Column(1).Hide();
worksheet.Column(2).Hide();
worksheet.Column(3).Width = 13;
worksheet.Column(4).Width = 21;
worksheet.Column(5).Width = 6.43;
worksheet.Column(6).Width = 19;
worksheet.Column(7).Width = 9;
worksheet.Column(8).Width = 9;
worksheet.Column(9).Width = 9;
worksheet.Column(10).Width = 9;
worksheet.Column(11).Width = 9;
worksheet.Column(12).Width = 9;
worksheet.Column(13).Width = 20.60;
worksheet.Column(14).Hide();
worksheet.Column(15).Hide();
////TextAlignment
///
worksheet.Column(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Column(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Column(5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(13).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//worksheet.Column(14).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
////Set Header & Footer
ckconnection.getGeneralSetting();
worksheet.PageSetup.Header.Left.AddText("&"Tahoma"&14&K8B2252" + ckconnection.company_name);
worksheet.PageSetup.Header.Center.AddText("&"Tahoma"&14&K8B2252 GATE MOVE REPORT");
worksheet.PageSetup.Header.Right.AddText("&"Tahoma"&14&K8B2252" + ckconnection.depot_name);
worksheet.PageSetup.Footer.Center.AddText("&[Page]/&[Pages]");
worksheet.PageSetup.Footer.Right.AddText(time);
//Page size and layout
worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
worksheet.PageSetup.PaperSize = XLPaperSize.A4Paper;
//// Narrow Margins
worksheet.PageSetup.Margins.Left = (0.25);
worksheet.PageSetup.Margins.Right = (0.25);
worksheet.PageSetup.Margins.Top = (0.75);
worksheet.PageSetup.Margins.Bottom = (0.75);
worksheet.PageSetup.Margins.Header = (0.3);
worksheet.PageSetup.Margins.Footer = (0.3);
//Save Excel
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = "SheetName-" + time + ".xlsx";
if (saveFileDialoge.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialoge.FileName);
}
// close excel and dispose reference
workbook.Dispose();
}