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
– Beerbossa, Commented Feb 3, 2016 at 18:04
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
– CrazyDart, Commented Feb 3, 2016 at 18:09
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
– Beerbossa, Commented Feb 3, 2016 at 21:13

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

1

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);

answered Feb 3, 2016 at 19:28

M.E.

2,9293 gold badges27 silver badges36 bronze badges

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

1 Comment

Beerbossa Over a year ago

Thanks dude, I love you, this just helped me a LOT to understand the procedures needed to export an excel document, it's exactly what I was looking for. <3 Time to code a "sample generator" now ! Thanks again.

CrazyDart · Accepted Answer · 2016-02-03 18:05:50Z

0

While I dont have a great answer for you, you didnt exactly give us the interop you are using. First I would ask why you are trying to do this the hard way? Can you not use a 3rd part lib like http://epplus.codeplex.com/

answered Feb 3, 2016 at 18:05

CrazyDart

3,8112 gold badges25 silver badges29 bronze badges

1 Comment

Beerbossa Over a year ago

Added a part of my code, I guess I'm going the hard way because I don't really know how to do otherwise. The goal is to be able to load an excel document that contains statistic datas on a population (some people's name, age, address, phone number, etc.), take a sample of this population then export it to another excel document right after.

Collectives™ on Stack Overflow

Export a multidimensional string array to a new Excel spreadsheet

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related