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!