Using Excel in C# to transfer Cell Values to a 2D string array

Question

I need someone to create a method that will get me all the cell values in an excel spreadsheet into a 2D array.

I'm making it using ribbons in C# to work with Excel but i just can't get it to work.

  private string[,] GetSpreadsheetData () 
  {
        try
        {
            Excel.Application exApp =
            Globals.TSExcelAddIn.Application as Excel.Application;
            Excel.Worksheet ExWorksheet = exApp.ActiveSheet as Excel.Worksheet;
            Excel.Range xlRange = ExWorksheet.get_Range("A1","F188000");

            object[,] values = (object[,])xlRange.Value2;
            string[,] tsReqs = new string[xlRange.Rows.Count, 7];
            char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();

            for (int i = 0; i < tsReqs.GetLength(0); i++)
            {
                for (int z = 0; z < tsReqs.GetLength(1); z++)
                {
                    if(values[i+1,z+1] != null)
                        tsReqs[i, z] = values[i + 1, z + 1].ToString();
                }
            }
            return tsReqs;
        }

        catch
        {
            MessageBox.Show
                ("Excel has encountered an error. \nSaving work and exitting");
            return null;
        }
    }

Also if anyone has a more efficient way of doing this I would greatly appreciate it.

            Excel.Range xlRange = ExWorksheet.get_Range("A1","F188000");

Reads all the way till F188000 cell from A1, I just want it to keep reading until it reaches a row with absolutely no data.

   -        Caught: "Index was outside the bounds of the array." (System.IndexOutOfRangeException)  Exception Message = "Index was outside the bounds of the array.", Exception Type = "System.IndexOutOfRangeException"

How is it not working? Do you get an exception or the wrong results? — D Stanley
– D Stanley, Commented Jan 16, 2013 at 20:21
Please update your post with the Exception.Message or the full stack trace for the Exception. — Brian
– Brian, Commented Jan 16, 2013 at 20:25
People will generally not write methods for you :)) I'll see what I can find though. — Christian Stewart
– Christian Stewart, Commented Jan 16, 2013 at 21:44

Porkbutts · Accepted Answer · 2013-01-16 22:20:26Z

4

You could consider using ExWorksheet.UsedRange instead of ExWorksheet.get_Range("A1","F188000")

EDIT: Also, I think if you use the .Text field of a Range, the value is automatically casted to a string, so no need to use .Value2 here

Excel.Range rng = ExWorksheet.UsedRange;
int rowCount = rng.Rows.Count;
int colCount = rng.Columns.Count;

string[,] tsReqs = new string[rowCount, colCount];

for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        string str = rng.Cells[i, j].Text;
        tsReqs[i - 1, j - 1] = str;
    }
}

edited Jan 16, 2013 at 22:20

answered Jan 16, 2013 at 22:13

Porkbutts

9747 silver badges13 bronze badges

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

3 Comments

Ramie Over a year ago

what if the value is null? or theres nothing in the field? also, the rng value doesn't have a text property, it has tostring.

Bobby Tables Over a year ago

I don't thing there will be a null value, or nothing in the field, as u can see he is using the UsedRange property.

Porkbutts Over a year ago

Hmm, the range should have a text property. msdn.microsoft.com/en-us/library/…

D Stanley · Accepted Answer · 2013-01-16 22:02:31Z

0

Your arrays are different sizes. Your values come from A1:F188000 which is 6 columns, but you're looping through the columns of tsReqs which is 7 columns. It looks like you're trying to account for the fact that values will be a 1-based array but aren't accounting for that properly.

Change the declaration of tsReqs to:

string[,] tsReqs = new string[xlRange.Rows.Count, 6];

and you should be fine.

answered Jan 16, 2013 at 22:02

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Comments

Bobby Tables · Accepted Answer · 2013-01-17 14:15:36Z

0

Try this

    private static void printExcelValues(Worksheet xSheet)
    {
        Range xRng =xSheet.Cells.SpecialCells(
            XlCellType.xlCellTypeConstants);
        var arr = new string[xRng.Rows.Count,xRng.Columns.Count];
        foreach (Range item in xRng)
        {
            arr[item.Row-1,item.Column-1]=item.Value.ToString();
        }
    }

You can use the | to specify more cell types like XlCellType.xlCellTypeFormulas or any other type you'd like.

edited Jan 17, 2013 at 14:15

answered Jan 16, 2013 at 21:57

Bobby Tables

3,0237 gold badges32 silver badges55 bronze badges

3 Comments

D Stanley Over a year ago

This will use COM-Interop to call item.Value for every cell in the worksheet and will perform HORRIBLY.

Ramie Over a year ago

i need it in a 2d string array

Bobby Tables Over a year ago

Updated the code, hope it help. But as Stanley said it might be terribly slow

Herman Vercuiel · Accepted Answer · 2013-03-06 14:14:18Z

I had to do something very similar. Reading an excel file takes a while... I took a different route but I got the job done with instant results.

Firstly I gave each column its own array. I saved each column from the spreadsheet as a CSV(Comma delimited) file. After opening that file with notePad I was able to copy the 942 values separated with a comma to the initialization of the array

int[] Column1 = {300, 305, 310, ..., 5000};
                 [0]  [1]  [2]  ...  [941]

Now... If you do this for each Column, the position will resemble the 'row' of each 'column' of your 'spreadsheet' .

This odd method worked PERFECT for me, as I needed to compare user input sizes to the values in the "Width" column in the spreadsheet to get information regarding each respective size found in the spreadsheet.

NOTE: If your cells contain strings and not integers like mine, you can use enumeration method:

enum column1 { thing_1, thing_2, ..., thing_n,}
                 [0]      [1]           [n]

Here's my code If you want to have a look: (This is NOT the code converting a spreadsheet into some arrays --that is only a few lines of code, depending on your amount of columns-- this is the whole LookUp method I wrote)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PalisadeWorld
{   
//struct to store all the positions of each matched panel size in an array 'Identities'
struct ID
    {
        public int[] Identities;
        public ID(int[] widths, int rows)
        {   
            //Column1 from spreadsheet
            int[] allWidths = { 300, 305, 310, 315, 320, 325, 330, ..., 5000 };
            int i,j;
            int[] Ids = new int[rows];

            for (i = 0; i < rows; i++)
            {
                for (j = 0; j < 941; j++)
                {
                    if (widths[i] == allWidths[j])
                    {
                        Ids[i] = j;
                        break;
                    }
                }
            }
            this.Identities = Ids;
        }
        public override string ToString()
        {
            string data = String.Format("{0}", this.Identities);
            return data;
        }
    }

class LookUpSheet
{   
    //retrieve user input from another class
    public int[] lookUp_Widths {get; set;}
    public int lookUp_Rows { get; set; }

    //Method returning desired values from Column2
    public int[] GetNumPales1()
    {
        //column2
        int[] all_numPales = { 2, 2, 2, 2, 2, 2, 2, 2, 2, ..."goes on till [941]"...};
        int[] numPales = new int[lookUp_Rows];

        ID select = new ID(lookUp_Widths, lookUp_Rows);

        for (int i = 0; i < lookUp_Rows; i++)
        {
            numPales[i] = all_numPales[select.Identities[i]];
        }

        return numPales;
    }
    //Method returning desired values from Column3
    public int[] GetBlocks1()
    {
        //column3
        int[] all_blocks = { 56, 59, 61, 64, 66, 69, 71, 74, "goes on till [941]"...};
        int[] blocks = new int[lookUp_Rows];

        ID select = new ID(lookUp_Widths, lookUp_Rows);

        for (int i = 0; i < lookUp_Rows; i++)
        {
            blocks[i] = all_blocks[select.Identities[i]];
        }
        return blocks;
    }

...

Goes on through each column of my spreadsheet

Really hope this helps someone. Cheers

This obviously wont work if your spreadsheet undergoes any change.

Collectives™ on Stack Overflow

Using Excel in C# to transfer Cell Values to a 2D string array

4 Answers 4

3 Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related