Related Tags:
C# C# (pronounced "C-sharp") is an object-oriented programming language from Microsoft that aims to combine the computing power of C++ with the programming ease of Visual Basic. C# is based on C++ and contains features similar to those of Java. Learn More, Visual Studio Tools for Office (VSTO) Visual Studio Tools for Office is a set of development tools available in the form of a Visual Studio add-in and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime to expose their functionality via .NET. Learn More, Microsoft Excel Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Learn More, Add-in An add-in is a software program that expands the capabilities of bigger programs. It is a term commonly used by Microsoft and other platforms which have additional functions that can be added to primary programs. An add-in has specific but limited features that require only minimal memory resources. Learn More,

how to get the last column used/last column that has some data in a excel file programmatically by using C#?

Description:I need to iterate through a single row where I don’t know the end range as I have a dynamically generated excel file which might have different number of columns used every time. I am using following code, taken from reference code


List<string> myValue = new List<string>();
//unknown end Range
//need to get the Last data filled column or Last Column used in the following line
string endRange="D1";
foreach (Range row in sheet.Cells.Rows.Range["A1", endRange])
{
Range cell = (Range)row.Cells[1, 1];
if (cell.Value2 != null)
{
myValue.Add(Convert.ToString(cell.Value2));
}
}

Posted by: | Posted on: Jan 22, 2020

1 answers

Replies

2

You can easily achieve this by just adding the three lines to your code as shown and a method to get your column name. So your final code will be similar to the following code.

List<string> myValue = new List<string>();
Range usedRange = sheet.UsedRange;
int lastUsedcolumn = usedRange.Column + usedRange.Columns.Count - 1;
string Column = GetExcelColumnName(lastUsedcolumn);
//In the above line you have got the last column name
//In the following line you will be appending your row number
string endRange= Column +1;
foreach (Range row in sheet.Cells.Rows.Range["A1", endRange])
{
Range cell = (Range)row.Cells[1, 1];
if (cell.Value2 != null)
{
myValue.Add(Convert.ToString(cell.Value2));
}
}


public string GetExcelColumnName(int columnNumber)
{
int num1 = columnNumber;
string str = string.Empty;
int num2;
for (; num1 > 0; num1 = (num1 - num2) / 26)
{
num2 = (num1 - 1) % 26;
str = Convert.ToChar(65 + num2).ToString() + str;
}
return str;
}

Replied by: | Replied on: Jan 23, 2020



Reply