Set Excel Cell value using C#

Question

I use the following code to set the cell value of an Excel file:

for (int i = 2; i < rowCount; i++)
{
    for (int j = 1; j < columnCount; j++)
    {
        worksheet.Cells[i, j].Value = enrollmentDataGrid.Columns[j].GetCellContent(enrollmentDataGrid.Items[i]);
    }
 }

However I keep getting the

Error: Exception from HRESULT: 0x800A03EC

Why do I get this error and how do I fix it?

How many rows in the file ? https://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range refer to the second answer — LearningToCode, Jul 06 '18 at 14:51
@ArchitGoyal, it depends on the datagrid. I wanted the application to export data into an Excel sheet on click of a button. So it's supposed to create a new Excel file and write to it — Yi Qin, Jul 06 '18 at 14:54
Also when I hardcode the data the code works fine, but when I use the code in the question it fails — Yi Qin, Jul 06 '18 at 14:55
I was simply experimenting so there were only 9 rows, I don't think it was the problem in your reference @ArchitGoyal — Yi Qin, Jul 06 '18 at 14:56
What are the values of `rowCount`, in particular, prior to entering this code loop? (This may be crucially important when it is applied to Excel). — gravity, Jul 06 '18 at 19:47
For debugging purposes, you should probably separate the code a little, figure out where your actual problem is. var x = enrollment...; Value = x; Which line fails, the get or the assignment. Assuming it is the get, try var y = enrollment.Items[i], and then var x = GetCellContent(y); Just straightforward debugging until you figure out exactly which part is giving you errors. You indicated that hard coding works, now figure out which variable is causing issues and research that. — Owen Ivory, Sep 11 '18 at 21:41

Dhanushka Dayawansha · Answer 1 · 2018-07-06T17:05:36.123

Update:

  try
            {

                saveFileDialog1.Title = "Save as Excel File";
                saveFileDialog1.FileName = "";
                saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xls";
                if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
                {
                    Microsoft.Office.Interop.Excel.Application worksheet = new Microsoft.Office.Interop.Excel.Application();
                    worksheet.Application.Workbooks.Add(Type.Missing);
                    worksheet.Columns.ColumnWidth = 20;
                    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                    {
                        worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;

                    }
                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }

                    }
                    worksheet.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
                    worksheet.ActiveWorkbook.Saved = true;
                    worksheet.Quit();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

the problem is I can't seem to use enrollmentDataGrid.Rows[I].Cells[j]. The "Rows" part does not exist — Yi Qin, Jul 06 '18 at 15:24
@YiQin it's Winforms or Wpf? Please add the corresponding tag — Magnetron, Jul 06 '18 at 15:42
Appreciate the help, but I'm not using datagridview, just datagrid. Is it possible to apply datagridview methods to datagrid? — Yi Qin, Jul 06 '18 at 19:17

score 0 · Answer 2 · answered Jul 06 '18 at 17:06

0

I usually use Value2 property (not Value)

So, your code would be as the following:

for (int i = 2; i < rowCount; i++)
{
    for (int j = 1; j < columnCount; j++)
    {
        worksheet.Cells[i, j].Value2 = enrollmentDataGrid.Columns[j].GetCellContent(enrollmentDataGrid.Items[i]);
    }
 }

answered Jul 06 '18 at 17:06

Sergei Zinovyev

1,238
14
14

Still get the error after using Value2. But thanks for the help! – Yi Qin Jul 06 '18 at 19:25

score 0 · Answer 3 · answered Jul 06 '18 at 19:39

0

https://www.codeproject.com/Questions/470089/Exception-from-HRESULT-x-A-EC-Error

It seems, that you are not the only one: http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range[^].

According to the post, this error is occurring when you work with an old (xls) workbook opened in Excel 2007 or 2010. If this is the case, try first to save the file as new format workbook, before you access the cells.

answered Jul 06 '18 at 19:39

Sergei Zinovyev

1,238
14
14

I checked the Excel file generated after hard coding it, and it is an xlsx file. Also, there are only 9 rows in my sample data, so I guess this is not the answer to my problem. Still, thanks for the help! – Yi Qin Jul 06 '18 at 19:45

score 0 · Answer 4 · answered Jul 06 '18 at 20:46

try this code

for (int i = 1; i < columnCount + 1; i++)
                    {
                        worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;

                    }



 for (int i = 0; i < rowCount -1; i++)
    {
        for (int j = 0; j < columnCount; j++)
        {
            worksheet.Cells[i+2, j+1].Value2 = enrollmentDataGrid.Columns[j].GetCellContent(enrollmentDataGrid.Items[i]);
        }
     }

score 0 · Answer 5 · answered Jul 30 '18 at 07:24

When I write exporting into xls using wpf framework in C#. I also encounter with this issue and I solve it with following code.Try to see from this code

 Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Workbook workb = app.Workbooks.Open("c:\\Users\\Jack\\Documents\\Document1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            worksheet = workb.Sheets[1];
           // worksheet = workbook.Sheets[1];
            //worksheet = workbook.ActiveSheet;
            //worksheet.Name = "birinchi";




            for (int i = 0; i < Table.Columns.Count; i++) 
            {
             //   worksheet.Cells[1, i+1] = Table.Columns[i].Header;

            }


                for (int i = 0; i < Table.Columns.Count; i++)
                {

                    for (int j = 0; j < Table.Items.Count; j++)
                    {
                        TextBlock b = Table.Columns[i].GetCellContent(Table.Items[j]) as TextBlock;
                        worksheet.Cells[j + 2, i + 1] = b.Text;
                        //MessageBox.Show(b.Text);

                    }
                }

                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.FileName = "Document";
                dlg.DefaultExt = ".xlsx";
                Nullable<bool> result = dlg.ShowDialog();
                if (result == true)
                {

                   workbook.SaveAs(dlg.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);



                }
                app.Quit();

Set Excel Cell value using C#

5 Answers5