CODEWITHSUNDEEP
c#arraysstringexcelwinforms
Beerbossa
Beerbossa

Reputation: 110

Export a multidimensional string array to a new Excel spreadsheet

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?

Upvotes: 0

Views: 1111

Answers (2)

M.E.
M.E.

Reputation: 2929

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

Upvotes: 1

CrazyDart
CrazyDart

Reputation: 3801

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/

Upvotes: 0

Related Questions