C# array only repeating first array item into excel fields

Question

Only the first array item is being loaded into the cells, they're all getting the same value rather than each value being sent to its own cell

I have the same code being used in a save function that instead uses this code:

var iRowCount = xlSaveWorksheet.UsedRange.Rows.Count;

var newRow = iRowCount + 1;
var timeStamp = DateTime.Now;
var rangeStart = string.Concat("A", newRow.ToString());
var rangeEnd = string.Concat("F", newRow.ToString());


string[] stringSaveArray = new string[] { textBox_Name.Text, textBox_Number.Text, textBox_Client.Text, textBox_Site.Text, richTextBox_Description.Text, timeStamp.ToString() };
Excel.Range rng = xlSaveApp.get_Range(rangeStart, rangeEnd);
rng.Value = stringSaveArray;

This option is working beautifully, so I'm confused as to why it works here and not in the other function

private void button_CopyTable_Click(object sender, EventArgs e)
{
    var fullTempPath = Directory.GetCurrentDirectory() + "\\temp.xlsx";
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlTempWorkbook = xlApp.Workbooks.Open(fullTempPath);
    Excel._Worksheet xlTempWorksheet = xlTempWorkbook.Sheets[1];

    string[] stringArray = new string[] { textBox_Name.Text, textBox_Number.Text, textBox_Client.Text, textBox_Site.Text, richTextBox_Description.Text };
    Excel.Range rng = xlApp.get_Range("B1","B5");
    rng.Value = stringArray;

    xlTempWorksheet.Range["A1:B5"].Copy();


    Marshal.ReleaseComObject(xlTempWorksheet);

    //close and release
    xlTempWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
    Marshal.ReleaseComObject(xlTempWorkbook);

    //quit and release
    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);
}

The values of the first array item are being put into every cell in the range, rather than each item going into the cell walking through the list

The first range is horizontal, and the second one is vertical. Try rng.Value = xlApp.Transpose(stringArray); — Tim Williams
– Tim Williams, Commented May 16, 2019 at 17:33
Excellent, thank you very much. It never even occurred to me that the problem was because the range in this case is vertical rather than horizontal. — Straconis
– Straconis, Commented May 16, 2019 at 17:52

Slai · Accepted Answer · 2019-05-16 18:01:26Z

1

Multi-cell ranges accept and return two dimensional rectangular arrays. For example (not tested) :

xlWorksheet.Range["A1:B1"].Value = new[,] { {1, 2} };    // for "horizontal" range

xlWorksheet.Range["A1:A2"].Value = new[,] { {1}, {2} };    // for "vertical" range

xlWorksheet.Range["A1:B2"].Value = new[,] { {1, 2}, {3, 4} };

Single dimensional arrays are accepted for horizontal ranges, but not for vertical.

As mentioned by @TimWilliams in the comments, Application.Transpose or Application.WorksheetFunction.Transpose can be used to convert single dimensional array to two-dimensional "vertical" array :

xlWorksheet.Range["A1:A2"].Value = xlApp.Transpose(new[] { 1, 2 });

answered May 16, 2019 at 18:01

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charith Lankathilake · Accepted Answer · 2019-05-16 17:38:09Z

1

instead of using rng.Value = stringArray; use rng.Value2 = stringArray;

This will work.

answered May 16, 2019 at 17:38

Charith Lankathilake

1081 silver badge6 bronze badges

Collectives™ on Stack Overflow

C# array only repeating first array item into excel fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related