Skip to main content linkedinfacebooktwittermenuarrow-up
1

Range Cell Address Reference Completed

Please implement a new method or constructor on CellRange class or a new GetSubrange method, so it can use cell range address reference of A1 notation, as stated in the following MSDN documentation:

https://msdn.microsoft.com/en-us/library/bb211395%28v=office.12%29.aspx

Official response

Mario at GemBox

Hi,

This feature request has been implemented and is available in the latest versions of GemBox.Spreadsheet.

Regards,
Mario

Comment (1)

Rob Sherratt
Perhaps these procedures especially Get_CellCollection() will help in the meantime? There is a simple Forms application for testing. I think I tested most of the combinations of cell range references ... seems OK. Imports System Imports System.Collections.Generic Imports System.Text Imports GemBox.Spreadsheet Imports GemBox.Spreadsheet.ConditionalFormatting Imports GemBox.Spreadsheet.PivotTables Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load ' If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY") Dim ef As ExcelFile = New ExcelFile Dim ws As ExcelWorksheet = ef.Worksheets.Add("Range Formatting") Dim range As CellRange = Nothing Dim rowCount As Integer = 20 ' Specify data formatting. ws.Columns(0).SetWidth(3, LengthUnit.Centimeter) ws.Columns(1).SetWidth(3, LengthUnit.Centimeter) ws.Columns(2).SetWidth(3, LengthUnit.Centimeter) ws.Columns(3).SetWidth(3, LengthUnit.Centimeter) ws.Columns(3).Style.NumberFormat = "[$$-409]#,##0.00" ws.Columns(4).SetWidth(3, LengthUnit.Centimeter) ws.Columns(4).Style.NumberFormat = "yyyy-mm-dd" ws.Columns(5).SetWidth(3, LengthUnit.Centimeter) Dim cells = ws.Cells ' Specify header row. cells(0, 0).Value = "Departments" cells(0, 1).Value = "Names" cells(0, 2).Value = "Years of Service" cells(0, 3).Value = "Salaries" cells(0, 4).Value = "Deadlines" cells(0, 5).Value = "Comments" ' Insert random data to sheet. Dim random = New Random() Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"} Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"} Dim comments = New String() {"", "", "Overdue", ""} For i As Integer = 0 To rowCount - 1 cells(i + 1, 0).Value = departments(random.Next(departments.Length)) cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString() cells(i + 1, 2).Value = random.Next(1, 31) cells(i + 1, 3).Value = random.Next(10, 101) * 100 cells(i + 1, 4).Value = DateTime.Now.AddDays(random.Next(-1, 2)) cells(i + 1, 5).Value = comments(random.Next(comments.Length)) Next Get_CellCollection(ws, "D16:F21", range) range.Style.FillPattern.SetSolid(Color.Yellow) Get_CellCollection(ws, "7:8", range) range.Style.Font.Weight = ExcelFont.BoldWeight range.Style.FillPattern.SetSolid(Color.LightGreen) Get_CellCollection(ws, "C4", range) range.Style.FillPattern.SetSolid(Color.LightBlue) Get_CellCollection(ws, "E", range) range.Style.FillPattern.SetSolid(Color.LightSlateGray) Get_CellCollection(ws, "12", range) range.Style.FillPattern.SetSolid(Color.MistyRose) Get_CellCollection(ws, "A17:B", range) range.Style.FillPattern.SetSolid(Color.OrangeRed) System.IO.File.Delete(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Range Formatting.xlsx") ef.Save(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Range Formatting.xlsx") Me.Close() End Sub Private Function Get_CellCollection(ws As GemBox.Spreadsheet.ExcelWorksheet, CellRef As String, _ ByRef MyCellRange As GemBox.Spreadsheet.CellRange) As Boolean ' Fetches a CellRange from the Worksheet using Excel-type CellRef string. ' Any "$" absolute references are allowed and stripped out. Dim CellRef1 As String = "" Dim CellRef2 As String = "" Dim DelimIdx As Integer Dim Row1 As Integer = 0 Dim Row2 As Integer = 0 Dim Col1 As Integer = 0 Dim Col2 As Integer = 0 Dim Syntax1 As Integer = 0 Dim Syntax2 As Integer = 0 ' 0 = invalid syntax ' 1 = whole column selection syntax like "AD" - col is valid, row is not modified ' 2 = whole row selection syntax like "3" - row is valid, col is not modified ' 3 = cell reference like "BC3" - both row and col are valid CellRef = Replace(CellRef, "$", "") ' remove any Excel "$" absolute references since these are unsupported in GB DelimIdx = InStr(CellRef, ":") If DelimIdx > 0 Then ' cellrange with two tokens CellRef1 and CellRef2 ' Process the first token, CellRef1 CellRef1 = Mid(CellRef, 1, DelimIdx - 1) If Not Parse_ExcelRefToken(CellRef1, Syntax1, Row1, Col1) Then Return False ' Process the second token, CellRef2 CellRef2 = Mid(CellRef, DelimIdx + 1, Len(CellRef) - DelimIdx) If Not Parse_ExcelRefToken(CellRef2, Syntax2, Row2, Col2) Then Return False Else ' single row or column specified by one token CellRef If Not Parse_ExcelRefToken(CellRef, Syntax1, Row1, Col1) Then Return False End If Select Case Syntax1 Case 1 Select Case Syntax2 Case 0 MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, ws.Rows.Count - 1, Col1) Case 1 MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, ws.Rows.Count - 1, Col2) Case 2 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row2, Col1, Row2, Col1) Case 3 MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, Row2, Col2) End Select Case 2 Select Case Syntax2 Case 0 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row1, ws.Columns.Count - 1) Case 1 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col2, Row1, Col2) Case 2 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row2, ws.Columns.Count - 1) Case 3 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row2, Col2) End Select Case 3 Select Case Syntax2 Case 0 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row1, Col1) Case 1 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, ws.Rows.Count - 1, Col2) Case 2 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row2, ws.CalculateMaxUsedColumns - 1) Case 3 MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row2, Col2) End Select End Select Return True End Function Private Function Parse_ExcelRefToken(CellRef As String, ByRef Syntax As Integer, ByRef Row As Integer, ByRef Col As Integer) As Boolean ' Parses a single Excel Reference token Dim Char1 As String = Mid(CellRef, 1, 1) Dim Char2 As String = Mid(CellRef, Len(CellRef), 1) If Char1 >= "A" And Char1 <= "Z" Then If IsNumeric(Char2) Then CellRange.PositionToRowColumn(CellRef, Row, Col) Syntax = 3 ' CellRef specifies a cell reference like "BC3" Return True Else If Char2 >= "A" And Char2 <= "Z" And Len(CellRef) <= 2 Then Col = ExcelColumnCollection.ColumnNameToIndex(CellRef) Syntax = 1 ' CellRef specifies a whole column selection syntax like "BD" Return True Else Syntax = 0 ' Illegal syntax Return False End If End If Else ' CellRef is either numeric and specifies a single row, or else is invalid If IsNumeric(CellRef) Then Row = CInt(CellRef) - 1 Syntax = 2 ' whole row selection syntax like "321" Return True Else Syntax = 0 ' illegal Syntax Return False End If End If End Function Private Function Get_Excel_Col_By_Index(Col_Index As Integer) As String ' Converts a numeric column index numbered from 0 to 702 into an Excel column index ' with letter references from A ... Z, AA ... AZ, BA ... BZ, , ZA to ZZ If Col_Index < 0 Or Col_Index > 702 Then Return "" Return ExcelColumnCollection.ColumnIndexToName(Col_Index) End Function Private Function Get_Excel_Col_By_Name(ws As ExcelWorksheet, Colname As String, Optional Header_row As Integer = 0) As String ' Converts the first Colname found in the header row number Header_Row into an Excel column index ' with letter references from A ... Z, AA ... AZ, BA ... BZ, , ZA to ZZ Dim headerCells = ws.Rows(Header_row).AllocatedCells Dim colNumber As Integer If String.IsNullOrWhiteSpace(Colname) Then Return "" For colNumber = 0 To headerCells.Count - 1 If (headerCells(colNumber).Value).Equals(Colname) Then Return ExcelColumnCollection.ColumnIndexToName(colNumber) End If Next Return "" End Function End Class