Excel range to string array

[Suzie999]

I'm getting an excel range to an array.

From my search it was clear I need to use object array.

object[,] saRet;
oRng = oSheet.UsedRange;
saRet = (System.Object[,])oRng.get_Value(Type.Missing);

This works fine, but what I really need is for that array to be string.
So how might I cast object[,] to string[,]?

I read it's a bad idea to get the range directly into string[,] you see.

If there is a way other than creating a new array and looping through it to copy, I'd love to hear it.

Thank you for taking the time to read.

[yesi_1]

I guess you need to get excel range to string finally, if right ,recommend u
a .NET API-Spire.xls,after downloading and add dll to project,using the following code.

Workbook workbook=new Workbook();
            workbook.LoadFromFile("test.xlsx");
            Worksheet worksheet=workbook.Worksheets[0];
            string str = worksheet.Range["A1"].Value.ToString();
            Console.WriteLine(str);
            Console.ReadKey();

[ddanbe]

Anothr approach could be this If you still want your string array, you could get it out of the DataGridView.

[Suzie999]

Thanks for the input guys, I appreciate it.

@yesi_1 free version is severly limited, and I don't have the kind of money ($700) to buy pro version.

[Suzie999]

I found this article, which basically says 2 for loops is the only way, as Array.ConvertAll does only 1d array and uses for loops anyway.

So I guess I'm looking for confirmation about the dangers of using string[,] initially, or someone who has automated Excel in this manner who might have other solution.

[ddanbe]

I perhaps posted the wrong link. I guess this is more what you want.

[Suzie999]

Thank you far taking the time to look ddanbe, I really appreciate it.

But I have done most of the coding, my only issue is string array, here is my class.

Please excuse my shabby coding, I'm still new to OOP.

class oExcel : Form1
    {
        public Excel.Application oXL;
        public Excel.Workbook oWB;
        public Excel._Worksheet oSheet;
        public Excel.Range oRng;

        public string[,] var1 = new string[10,10];
        public int error = 0;

        public oExcel()
        {
            try
            {
                oXL = new Excel.Application();
                oXL.Visible = true;
            }
            catch (Exception ex)
            {
                error = 1;
                Debug.WriteLine(ex.Message.ToString());
            }

        }

        public Excel._Worksheet OpenWorkSheet(string path)
        {
            try
            {
                oWB = oXL.Workbooks.Open(path);

            }
            catch (Exception ex)
            {
                oXL.Quit();
                error = 2;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());
                return null;
            }

            try
            {
                //Get a reference to the active sheet of the workbook.
                //objSheets = oWB.Worksheets;
                //oSheet = (Excel.Worksheet)objSheets.get_Item(1);
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            }
            catch(Exception ex)
            {
                error = 3;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());
                return null;
            }

            object[,] obj = GetRange();

            return oSheet;
        }

        public object[,] GetRange()
        {
            object[,] saRet;
            try
            {
                oRng = oSheet.UsedRange;
                saRet = (System.Object[,])oRng.get_Value(Type.Missing);
            }
            catch (Exception ex)
            {
                error = 4;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());
                return null;
            }


            //Determine the dimensions of the array.
            long iRows;
            long iCols;
            iRows = saRet.GetUpperBound(0);
            iCols = saRet.GetUpperBound(1);

            //Build a string that contains the data of the array.
            String valueString;
            valueString = "Array Data\n";

            for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= iCols; colCounter++)
                {

                    //Write the next value into the string.
                    valueString = String.Concat(valueString,
                       saRet[rowCounter, colCounter].ToString() + ", ");
                }

                //Write in a new line.
                valueString = String.Concat(valueString, "\n");
            }

            //Report the value of the array.
            Debug.Write(valueString);

            return saRet;
        }

        ~oExcel()
        {
            //oWB.Close(false);
            //oXL.Quit();
        }
    }

I will need to be sorting the 2d array by various columns, which is why I need string array.

[Suzie999]

If I do this...

string[,] saRet;
            //try
            //{
                oRng = oSheet.UsedRange;
                saRet = (string[,])oRng.get_Value(Type.Missing);

I get error "Unable to cast object of type 'System.Object[,]' to type 'System.String[,]'."

If I do this...

string[,] saRet;
            //try
            //{
                oRng = oSheet.UsedRange;
                saRet = (object[,])oRng.get_Value(Type.Missing);

I get error "Cannot implicitly convert type 'object[,]' to 'string[,]'. An explicit conversion exists (are you missing a cast?)"

I think it may have something to do with "Type.Missing" passed in...

saRet = (object[,])oRng.get_Value(Type.Missing);

But I cannot figure out what I might put there.

(edit)

I tried...

saRet = (string[,])oRng.get_Value(Type.GetType("String"));

Without succes, same error.

[ddanbe]

I should forget about that object thing you have. This is the code I use to get a string out of an excel cell.

// Get data as string out of an excel cell
        public string GetData(int row, int col)
        {
            string Val = string.Empty;
            if (worksheet.Cells[row, col].Value2 != null)
            {
                Val = worksheet.Cells[row, col].Value2.ToString();
            }
            return Val;
        }

worksheet is my active worksheet here.
Use this in two for loops to stuff your 2D string array with whatever range you want.

[Suzie999]

Thanks, I'm happily confident now that I have to use a couple of loops somewhere, so I just copied the object array.

sRet = new string[iRows, iCols];

for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
    {
        for (long colCounter = 1; colCounter <= iCols; colCounter++)
        {
            sRet[rowCounter - 1, colCounter - 1] = saRet[rowCounter, colCounter].ToString();
        }
    }

rowCounter - 1 because object array turns out to be 1 based.

Thanks all for your support.