0

I have an array of "transactions". My array looks like:

1, 12/15/18, 125.00, "phone bill"
2, 01/16/19, 37.25, "supplies"
3, 02/28/19, -50.00, "refund"

Write Array to Excel Range has good sample code that uses the line

[Excel]range.Value = arr

This works great if all my data types are the same. But they are not.

In Visual Basic (VB), it is easy to create an array of variants, and use the VB command

  Worksheet.Range(UpperLeftCell).Resize(NumOfRangeRows, NumOfRangeColumns).Value = myArray

But I cannot find an equivalent in C#. Also I don't see how it is possible to create an array of variants in C#.

The best solution I can come up with is to split my array of transactions into 4 arrays: 1 for an integer, 1 for the date, 1 for the amount, and 1 for the description.

I could easily write each value individually to an Excel call, one at a time, but this is actually quite slow (I have thousands of transactions).

I am hoping someone can suggest a better way.

I have tried the code

Excel.Range range = arrayOfTrans;

but I get System.NotSupportedException: 'Operation is not supported. (0x80131515)'

zerotax
  • 1
  • 2
  • 1
    Use NPOI instead, using Excel through automation is one of the stupidest anti-patterns someone can possibly do. – Blindy Apr 04 '22 at 00:15
  • If you look at your first code sample, you'll see the assignment is made to `Range.Value`. Your last one tries to assign directly to `Range`, which requires a `Range` (a group of cells). The `Value` is extremely important, but you seemed to have overlooked it entirely. – Ken White Apr 04 '22 at 00:22
  • Write a csv and import it? There are plenty of other ways to skin a cat. – Jeremy Lakeman Apr 04 '22 at 00:24
  • Try it without the dates and see what you get. Excel dates are very goofy. If it works without the dates, then pass an appropriate Excel date value in (as a floating point numbers, using the standard weird Excel date conventions – Flydog57 Apr 04 '22 at 00:57
  • EPPlus is another option to consider: https://stackoverflow.com/a/69092221/1410664 - converting from excel automation code to EPP is usually quite simple because they adopt similar conventions, though dumping data to an xlsx is largely unrelated to stuff like setting cell formats etc. EPP makes exporting a datatable trivial – Caius Jard Apr 04 '22 at 05:44
  • (I'd generate the data straight into a datatable but it's also easy to convert your 2D array to a datatable) – Caius Jard Apr 04 '22 at 05:52
  • Thanks, all. I will definitely check out NPOI.
    This is the code I was looking for:
    var data = new object[rows, columns]; // Each object can contain integers, strings, decimals, etc.
    var startCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
    var endCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rows, columns];
    var writeRange = worksheet.Range[startCell, endCell];
    I was able to write 5 columns and 1,000 rows of data in 0.05 secs.     – zerotax Apr 09 '22 at 16:42

0 Answers0