Export a multidimensional string array to a new Excel spreadsheet

Question

Using Interop I managed to import an excel document (that contains a population) to a multidimensional string array of this format:

public string[,] DataArray;

I used this method to populate the array:

try
{
  Excel.Application xlApp = new Excel.Application();
  xlWorkBook = xlApp.Workbooks.Open(FilePath);
  Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1];
  Excel.Range xlRange = xlWorksheet.UsedRange;
  rowCount = xlRange.Rows.Count;
  colCount = xlRange.Columns.Count;
  InitialiserTableauPopulation(rowCount, colCount);
  for (int x = 1; x <= colCount; x++)
  {
    for (int y = 1; y <= rowCount; y++)
    {
      DataArray[x - 1, y - 1] = xlRange.Cells[y, x].Value2.ToString();
    }
  }
  xlApp.Workbooks.Close();
}
catch (Exception ex)
{
  MessageBox.Show(ex.message);
}

While debugging, I can see that the format is correct, for every X (column name), I have multiple Y (row data).

Example : DataArray[0,0] = NAME, DataArray[0,1] = JAMES, DataArray[0,2] = ERIC, etc.

Now what I'm trying to do is take a sample of this population, make a new multidimensional string array then export this "sample" to a new excel document : but I'm lost.

How should I proceed to export an existing two dimensional string array to a new excel sheet, keeping the same Array[column][row] format?

XSLX would be preferred, it's actually the initial file type that I import into my program and to my array. Even a procedure to save in CSV would help me right now, but my teacher wants me to be able to make multiple sheets in one excel document, so XSLX would be the way to go. — Beerbossa
So is this a programming course that you need help with? If yes, I would urge you to check the course material. As a teacher of C# courses myself I would be in error to just give you the answer. — CrazyDart
Sadly we don't have any material, it's a statistic class and our teacher doesn't know anything about programming, we have to prove her that we know how to "sample a population". This assignment makes us google around and learn by ourselves : it's a good learning method, but kind of annoying that she can't help us at all / answer any question if we hit a wall. — Beerbossa

M.E. M.E. · Accepted Answer · 2016-02-03T19:28:25

You just need to create a new Excel.Application, add a new Excel.Workbook and get the Excel.Worksheet from the Excel.Workbook.

Then you can iterate over your DataArray with a nested for-loop and add your values as needed using the Cell-Property of the Excel.Worksheet.

Then you save your new workbook and close it appropriately. This could look something like this

private void SaveDataArray(string excelFileName, string[,] dataArray)
{
  var xlApp = new Application();
  var xlWorkBook = xlApp.Workbooks.Add();
  var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Item[1];

  for (int i = 0; i < dataArray.GetLength(0); i++)
  {
    for (int j = 0; j < dataArray.GetLength(1); j++)
    {
      xlWorkSheet.Cells[j + 1, i + 1] = dataArray[i, j];
    }
  }

  xlWorkBook.SaveAs(excelFileName);
  xlWorkBook.Close(true);
  xlApp.Quit();
}

And then call the method like this

  // create some sample data ...
  string[,] dataArray = new string[1, 3];

  dataArray[0, 0] = "NAME";
  dataArray[0, 1] = "JAMES";
  dataArray[0, 2] = "ERIC";

  SaveDataArray("c:\\temp\\exceltest.xlsx", dataArray);

Export a multidimensional string array to a new Excel spreadsheet

2 Answers