CODEWITHSUNDEEP
c#winformsexcelexcel-interop
Muhammad Ali
Muhammad Ali

Reputation: 139

Creating excel file in C#

Its my first experience with excel file using C#.

As a demo, I wrote a single line in excel file and it worked but now I am trying to insert all the rows of DataTable but it gives the following error:

"HRESULT: 0x800A03EC"

Here is the code:

for (int i = 0; i < table.Rows.Count; i++)
{
    xlWorkSheet.Cells[i, 1] = table.Rows[i]["Ref"].ToString();
    xlWorkSheet.Cells[i, 2] = table.Rows[i]["Name"].ToString();
}

Please tell me how can I fix it?

Upvotes: 1

Views: 817

Answers (3)

phoog
phoog

Reputation: 43056

I wholeheartedly endorse Jeremy Thompson's answer to use a 2-dimensional object array. The performance will be orders of magnitude faster. I'm writing to explain why the code you've written doesn't work (and why you probably thought it should work).

In Visual Basic, there's a concept of "Default" property. When you write this in VBA, you're using that concept:

xlWorkSheet.Cells(i, 1) = someValue

The Cells property returns a Range object. In VBA, since you haven't used the Set keyword, this line is not reassigning the value of the object (and, if you added the Set keyword, it would fail to reassign the value of the object, since it's a read-only property, as Ria notes).

The absence of the Set keyword causes the assignment to go to the default prpoperty of the Range object returned by Cells(i, 1). The default property, of course, is the Value property. The above VBA sample, in other words, is equivalent to this:

Dim r As Range

Set r = xlWorkSheet.Cells(i, 1)
r.Value = someValue

Of course, you can also skip the r variable:

xlWorkSheet.Cells(i, 1).Value = someValue

Now, C# doesn't have the default property concept. You're therefore unable to write this code the shortcut way, which leads us to the code suggested in Ria's answer:

xlWorkSheet.Cells[i, 1].Value = someValue;

Or, with the real value instead of someValue:

xlWorkSheet.Cells[i, 1].Value = table.Rows[i]["Ref"].ToString();

The default property makes it easy for beginners to write code, and it helps code to be concise, but it's also confusing for people who are between the beginner stage and the advanced stage.

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65712

Instead of a DataTable, put all the values into a object[,]

Then you can assign them in one swoop:

using (var targetRangeHeader = _excelApp.Range[TargetRangeTopAddress].WithComCleanup())
 using (var targetRangeFirstDataCell = targetRangeHeader.Resource.Offset[1, 0].WithComCleanup())
  using (var targetRange = targetRangeFirstDataCell.Resource.Resize[MyObjectArrayValues.Length, 1].WithComCleanup())
{
 targetRange.Resource.Value2 = MyObjectArrayValues;

Using http://jake.ginnivan.net/vsto-com-interop

Upvotes: 1

Ria
Ria

Reputation: 10357

Use Range.Value instead. Worksheet.Cells property is readonly:

for (int i = 0; i < table.Rows.Count; i++)
{
    xlWorkSheet.Cells[i, 1].Value = table.Rows[i]["Ref"].ToString();
    xlWorkSheet.Cells[i, 2].Value = table.Rows[i]["Name"].ToString();
}

Upvotes: 1

Related Questions