DataGridViewS export to excel sheetS

Question

我想将所有 DataGridView 导出到一个 Excell 文档中。 对于每个 DataGridView，Excell 文件中都应该有一个自己的工作表。

但是对于我的代码，我只收到错误：System.Runtime.InteropServices.COMException：HRESULT：0x800A03EC">

我认为我的参数有问题。

private void exportToExcellButton_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileD = new SaveFileDialog();
string fileName = truckListBox.SelectedItem.ToString() + "__" + DateTime.Now.ToShortDateString();
saveFileD.InitialDirectory = @"C:/TML/";
saveFileD.FileName = fileName;
if (!Directory.Exists(@"C:/TML/"))
Directory.CreateDirectory(@"C:/TML/");
List<DataGridView> dataGridViews = getAllDataGridViews();
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Workbook book;
Microsoft.Office.Interop.Excel.Worksheet sheet;
app = new Excel.Application();
app.Visible = true;
book = app.Workbooks.Add(System.Reflection.Missing.Value);
foreach (var grid in dataGridViews)
{
int count = book.Worksheets.Count;
sheet = (Worksheet)book.Sheets.Add(Type.Missing, book.Worksheets[count], Type.Missing, Type.Missing);
sheet.Name = grid.Name.ToString().Remove(0, 13);
int cMin = 0, rMin = 0;
int c = cMin, r = rMin;
// Set Headers
foreach (DataGridViewColumn column in grid.Columns)
{
//Here appears the Error: System.Runtime.InteropServices.COMException: HRESULT: 0x800A03EC"
sheet.Cells[r, c] = column.HeaderText;
c++;
}
sheet.Range[sheet.Cells[r, cMin], sheet.Cells[r, c]].Font.Bold = true;
sheet.Range[sheet.Cells[r, cMin], sheet.Cells[r, c]].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Set Rows
foreach (DataGridViewRow row in grid.Rows)
{
r++;
c = cMin;
// Set Cells
foreach (DataGridViewCell item in row.Cells)
{
sheet.Cells[r, c++] = item.Value;
}
}
}
book.Save();
book.Close();
app.Quit();
}

把所有准备好的日子都投入其中，无法让它工作。 感谢您的帮助！

编辑：修复了一个错误以进入新错误。

Answer 1

发布的代码可能会遇到一些问题。因此，我将分解它们。

对于初学者来说，您似乎正在使用SaveFileDialog但是我没有看到它在哪里使用。该代码设置了InitalDirectory和FileName，但从未使用过。这并不那么重要，因为实际上不需要对话框，但是代码获取文件名的方式会遇到一些问题。代码行...

string fileName = truckListBox.SelectedItem.ToString() + "__" + DateTime.Now.ToShortDateString();

如果您尝试保存文件名，将出现问题，因为从DateTime.Now.ToShortDateString()返回的字符串将采用类似"2019\11\26"的格式...显然，"\"字符将被解释为路径(文件夹(，并且很可能在代码尝试保存文件时失败。创建一个返回使用其他字符的字符串的方法应该很容易解决此问题。

接下来是 Excel 文件根据其行和列不为零的事实。因此，设置初始 Excel 行列变量 (int c = 0, r = 0;( 将在第一次尝试时失败。这些值应为一 (1(。

另一个问题就上线了...

book.Save();

最有可能使用"Book1.xlsx"的文件名将文件保存到用户的"文档"文件夹中。保存文件时，您需要提供完整的路径和文件名，如前所述，这些路径和文件名似乎没有被使用。

最后，无论何时使用"COM"对象(如 Excel 应用程序、工作簿和工作表(，在退出程序之前，代码"释放"代码创建的 COM 对象非常重要。在当前发布的代码中，很可能仍有挥之不去的"Excel"资源仍在运行。因此，为了避免泄漏资源，代码必须释放它创建的 com 对象。 在下面的示例中，释放资源的代码位于try/catch/finally语句的finally子句中。

private void button1_Click(object sender, EventArgs e) {
//SaveFileDialog saveFileD = new SaveFileDialog();
//string fileName = truckListBox.SelectedItem.ToString() + "__" + DateTime.Now.ToShortDateString();
string fileName = @"C:UsersJohnDesktopGrrTestExcelFile" + "__" + DateTime.Now.Year + "_" + DateTime.Now.Month;
//saveFileD.InitialDirectory = @"C:UsersJohnDesktopGrr";
//saveFileD.FileName = fileName;
//if (!Directory.Exists(@"C:/TML/"))
//    Directory.CreateDirectory(@"C:/TML/");
//List<DataGridView> dataGridViews = getAllDataGridViews();
List<DataGridView> dataGridViews = getGrids();
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbook book = null;
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
book = app.Workbooks.Add(System.Reflection.Missing.Value);
try {
foreach (var grid in dataGridViews) {
int count = book.Worksheets.Count;
//sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.Add(Type.Missing, book.Worksheets[count], Type.Missing, Type.Missing);
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.Add();
//sheet.Name = grid.Name.ToString().Remove(0, 13);
sheet.Name = grid.Name.ToString();
int cMin = 1, rMin = 1;
int c = cMin, r = rMin;
// Set Headers
foreach (DataGridViewColumn column in grid.Columns) {
//Here appears the Error: System.Runtime.InteropServices.COMException: HRESULT: 0x800A03EC"
sheet.Cells[r, c] = column.HeaderText;
c++;
}
sheet.Range[sheet.Cells[r, cMin], sheet.Cells[r, c]].Font.Bold = true;
sheet.Range[sheet.Cells[r, cMin], sheet.Cells[r, c]].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Set Rows
foreach (DataGridViewRow row in grid.Rows) {
r++;
c = cMin;
// Set Cells
foreach (DataGridViewCell item in row.Cells) {
sheet.Cells[r, c++] = item.Value;
}
}
}
book.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing);
book.Close();
app.Quit();
}
catch (Exception ex) {
MessageBox.Show("Error writing to excel: " + ex.Message);
}
finally {
if (sheet != null)
Marshal.ReleaseComObject(sheet);
if (book != null)
Marshal.ReleaseComObject(book);
if (app != null)
Marshal.ReleaseComObject(app);
}
}

希望这有帮助。

Answer 2

只需创建一个方法并传递 DataGridView

using Excel = Microsoft.Office.Interop.Excel;
public void ete(DataGridView dgv)//ExportToExcel
{
// Creating a Excel object.
Excel._Application excel = new Excel.Application();
Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
excel.Columns.ColumnWidth = 20;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = -1; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName.ToString());
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}

现在调用方法

ete(datagridview1);