0

I've been trying to use Microsoft.Office.Interop.Excel to modify some worksheets, but I can't figure out how to get it to do some simple things. So far I've managed to load a worksheet and PrettyPrint out a range that I choose. That's fine. But how do I get the size of the sheet?

Some of the code is here.

class Excel
{
    string path = "";
    _Application excel = new _Excel.Application();
    Workbook wb;
    Worksheet ws;
     public Excel(string path, int Sheet)
    {
        this.path = path;
        wb = excel.Workbooks.Open(path);
        ws = wb.Worksheets[Sheet];

    }        
     public string MakeStringLength(string str, int Length)
    {
        if (string.IsNullOrEmpty(str))
            return str.PadRight(Length);
        return str.Substring(0, Math.Min(str.Length, Length)).PadRight(Length);
    }

     public XlCellType[,] GetCellRange (int I, int J, int w, int h)
    {

        I++;
        J++;
        if (I < 1 || J < 1 || w < 1 || h < 1)
        {
            throw new Exception($"Cell Range Error: i,j,w,h not valid {I},{J},{w},{h}");
        }

        XlCellType[,] ans = new XlCellType[w, h];

        for (int i = I, endx = I + w; i < endx; i++)
        {
            for (int j = J, endy = J + h; j < endy; j++)
            {
                var test = ws.Cells[i, j];
                ans[i, j] = ws.Cells[i, j];
            }
        }
        return ans;
    }
     public void PrintCells(int I, int J, int w, int h, int l)
    {
        I++;
        J++;
        if (I < 1 || J < 1 || w < 1 || h < 1)
        {
            throw new Exception($"Cell Range Error: i,j,w,h not valid {I},{J},{w},{h}");
        }


        for (int i = I, endx = I + w; i < endx; i++)
        {
            for (int j = J, endy = J + h; j < endy; j++)
            {
                if(ws.Cells[i, j].Value2 != null)
                {
                    Console.Write($"{MakeStringLength(ws.Cells[i, j].Value2.ToString(), l)}  \t");
                }
                else
                {
                    Console.Write($"{MakeStringLength("", l)}  \t");
                }


            }
            Console.WriteLine();
        }
    }

}

Basically I have a worksheet with some nonsense text at the top and then a few rows down there is a table of data of unknown size.

I need a way to figure out that size, so I can select it and thus iterate over it, and modify certain cell formulas

There is a Worksheet.Cells.Width/height property but its a double and appears to represent actual column/row width/height within excel rather than number of columns/rows

I'm somewhat new to this so any help is appreciated!

0 Answers0