DataGridViewS export to excel sheetS

Question

I want to export all my DataGridViews in one Excell document. For every DataGridView there shoud be a own sheet in the Excell File.

But with my code i only recive the Error: System.Runtime.InteropServices.COMException: HRESULT: 0x800A03EC"

I think there is something wrong with my parameters.

        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();
    }

Spended allready days into it and cant get it work. Thx for your Help!

EDIT: Fixed one error to get to the new one.

Answer 1

Simply Make a method and pass 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;
        }

    }

Now Call Method

ete(datagridview1);

Answer 2

There are a few problems you may have with the posted code. Therefore, I will break them down.

For starters, it appears you are using a SaveFileDialog however I do not see where it is being used. The code sets the InitalDirectory and FileName, but it is never used. This is not that important as a dialog is not really needed, however the way the code is getting the file name is going to have some problems. The line of code…

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

is going to have problems if you try to save the file name because the string returned from DateTime.Now.ToShortDateString() is going to be in a format like “2019\11\26”… Obviously the “\” characters are going to be interpreted as a path (folder) and will most likely fail when the code tries to save the file. Creating a method that returns a string that uses some other character should be easy to fix this.

Next is the fact that Excel files are NOT zero based on their rows and columns. Therefore, setting the initial Excel row column variables (int c = 0, r = 0;) will fail on the first try. These values should be one (1).

Another problem is on the line…

book.Save();

Is most likely going to save the file to the users “Documents” folder using the file name of “Book1.xlsx.” When saving the file you need to supply the complete path and file name which as stated earlier does not appear to be used.

Lastly, anytime you use “COM” objects such as Excel apps, workbooks and worksheets, it is very important for the code to “RELEASE” the com objects your code creates before you exit the program. In the current posted code, it is highly likely that there are lingering “Excel” resources still running. Therefore, to avoid leaking resources, it is important for your code to release the com objects it creates. In my example below the code to release the resources is in the finally clause of the try/catch/finally statement.

private void button1_Click(object sender, EventArgs e) {
  //SaveFileDialog saveFileD = new SaveFileDialog();
  //string fileName = truckListBox.SelectedItem.ToString() + "__" + DateTime.Now.ToShortDateString();
  string fileName = @"C:\Users\John\Desktop\Grr\TestExcelFile" + "__" + DateTime.Now.Year + "_" + DateTime.Now.Month;

  //saveFileD.InitialDirectory = @"C:\Users\John\Desktop\Grr\";
  //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);
  }
}

Hope this helps.