From dc01264562e6e88d77a28042408029770ea32df4 Mon Sep 17 00:00:00 2001 From: Veniamin Albaev Date: Tue, 19 Mar 2019 19:14:41 +0300 Subject: Huge refactorig for consistent col/row numbering (#356) * Huge refactorig for consistent col/row numbering Started from simply changing ToALphaString()/TitleToNumber() logic and related fixes. But have to go deeper, do fixes, after do related fixes and again and again. Major improvements: 1. Tests made stronger again (But still be weak). 2. "Empty" returns for incorrect input replaces with panic. 3. Check for correct col/row/cell naming & addressing by default. 4. Removed huge amount of duplicated code. 5. Removed ToALphaString(), TitleToNumber() and it helpers functions at all, and replaced with SplitCellName(), JoinCellName(), ColumnNameToNumber(), ColumnNumberToName(), CellNameToCoordinates(), CoordinatesToCellName(). 6. Minor fixes for internal variable naming for code readability (ex. col, row for input params, colIdx, rowIdx for slice indexes etc). * Formatting fixes --- lib.go | 234 +++++++++++++++++++++++++++++++++++++++++++---------------------- 1 file changed, 154 insertions(+), 80 deletions(-) (limited to 'lib.go') diff --git a/lib.go b/lib.go index 30a20e0..c33c934 100644 --- a/lib.go +++ b/lib.go @@ -12,11 +12,11 @@ package excelize import ( "archive/zip" "bytes" + "fmt" "io" "log" "strconv" "strings" - "unicode" ) // ReadZipReader can be used to read an XLSX in memory without touching the @@ -64,116 +64,188 @@ func readFile(file *zip.File) []byte { return buff.Bytes() } -// ToAlphaString provides a function to convert integer to Excel sheet column -// title. For example convert 36 to column title AK: +// SplitCellName splits cell name to column name and row number. // -// excelize.ToAlphaString(36) +// Example: // -func ToAlphaString(value int) string { - if value < 0 { - return "" +// excelize.SplitCellName("AK74") // return "AK", 74, nil +// +func SplitCellName(cell string) (string, int, error) { + alpha := func(r rune) bool { + return ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') + } + + if strings.IndexFunc(cell, alpha) == 0 { + i := strings.LastIndexFunc(cell, alpha) + if i >= 0 && i < len(cell)-1 { + col, rowstr := cell[:i+1], cell[i+1:] + if row, err := strconv.Atoi(rowstr); err == nil && row > 0 { + return col, row, nil + } + } + } + return "", -1, newInvalidCellNameError(cell) +} + +// JoinCellName joins cell name from column name and row number +func JoinCellName(col string, row int) (string, error) { + normCol := strings.Map(func(rune rune) rune { + switch { + case 'A' <= rune && rune <= 'Z': + return rune + case 'a' <= rune && rune <= 'z': + return rune - 32 + } + return -1 + }, col) + if len(col) == 0 || len(col) != len(normCol) { + return "", newInvalidColumnNameError(col) } - var ans string - i := value + 1 - for i > 0 { - ans = string((i-1)%26+65) + ans - i = (i - 1) / 26 + if row < 1 { + return "", newInvalidRowNumberError(row) } - return ans + return fmt.Sprintf("%s%d", normCol, row), nil } -// TitleToNumber provides a function to convert Excel sheet column title to -// int (this function doesn't do value check currently). For example convert -// AK and ak to column title 36: +// ColumnNameToNumber provides a function to convert Excel sheet +// column name to int. Column name case insencitive +// Function returns error if column name incorrect. // -// excelize.TitleToNumber("AK") -// excelize.TitleToNumber("ak") +// Example: // -func TitleToNumber(s string) int { - weight := 1 - sum := 0 - for i := len(s) - 1; i >= 0; i-- { - ch := s[i] - if ch >= 'a' && ch <= 'z' { - ch -= 32 +// excelize.ColumnNameToNumber("AK") // returns 37, nil +// +func ColumnNameToNumber(name string) (int, error) { + if len(name) == 0 { + return -1, newInvalidColumnNameError(name) + } + col := 0 + multi := 1 + for i := len(name) - 1; i >= 0; i-- { + r := name[i] + if r >= 'A' && r <= 'Z' { + col += int(r-'A'+1) * multi + } else if r >= 'a' && r <= 'z' { + col += int(r-'a'+1) * multi + } else { + return -1, newInvalidColumnNameError(name) } - sum += int(ch-'A'+1) * weight - weight *= 26 + multi *= 26 } - return sum - 1 + return col, nil } -// letterOnlyMapF is used in conjunction with strings.Map to return only the -// characters A-Z and a-z in a string. -func letterOnlyMapF(rune rune) rune { - switch { - case 'A' <= rune && rune <= 'Z': - return rune - case 'a' <= rune && rune <= 'z': - return rune - 32 +// MustColumnNameToNumber provides a function to convert Excel sheet column +// name to int. Column name case insencitive. +// Function returns error if column name incorrect. +// +// Example: +// +// excelize.MustColumnNameToNumber("AK") // returns 37 +// +func MustColumnNameToNumber(name string) int { + n, err := ColumnNameToNumber(name) + if err != nil { + panic(err) } - return -1 + return n } -// intOnlyMapF is used in conjunction with strings.Map to return only the -// numeric portions of a string. -func intOnlyMapF(rune rune) rune { - if rune >= 48 && rune < 58 { - return rune +// ColumnNumberToName provides a function to convert integer +// to Excel sheet column title. +// +// Example: +// +// excelize.ToAlphaString(37) // returns "AK", nil +// +func ColumnNumberToName(num int) (string, error) { + if num < 1 { + return "", fmt.Errorf("incorrect column number %d", num) } - return -1 + var col string + for num > 0 { + col = string((num-1)%26+65) + col + num = (num - 1) / 26 + } + return col, nil } -// boolPtr returns a pointer to a bool with the given value. -func boolPtr(b bool) *bool { return &b } +// CellNameToCoordinates converts alpha-numeric cell name +// to [X, Y] coordinates or retrusn an error. +// +// Example: +// CellCoordinates("A1") // returns 1, 1, nil +// CellCoordinates("Z3") // returns 26, 3, nil +// +func CellNameToCoordinates(cell string) (int, int, error) { + const msg = "cannot convert cell %q to coordinates: %v" -// defaultTrue returns true if b is nil, or the pointed value. -func defaultTrue(b *bool) bool { - if b == nil { - return true + colname, row, err := SplitCellName(cell) + if err != nil { + return -1, -1, fmt.Errorf(msg, cell, err) } - return *b + + col, err := ColumnNameToNumber(colname) + if err != nil { + return -1, -1, fmt.Errorf(msg, cell, err) + } + + return col, row, nil } -// axisLowerOrEqualThan returns true if axis1 <= axis2 axis1/axis2 can be -// either a column or a row axis, e.g. "A", "AAE", "42", "1", etc. +// MustCellNameToCoordinates converts alpha-numeric cell name +// to [X, Y] coordinates or panics. // -// For instance, the following comparisons are all true: +// Example: +// MustCellNameToCoordinates("A1") // returns 1, 1 +// MustCellNameToCoordinates("Z3") // returns 26, 3 // -// "A" <= "B" -// "A" <= "AA" -// "B" <= "AA" -// "BC" <= "ABCD" (in a XLSX sheet, the BC col comes before the ABCD col) -// "1" <= "2" -// "2" <= "11" (in a XLSX sheet, the row 2 comes before the row 11) -// and so on -func axisLowerOrEqualThan(axis1, axis2 string) bool { - if len(axis1) < len(axis2) { - return true - } else if len(axis1) > len(axis2) { - return false - } else { - return axis1 <= axis2 +func MustCellNameToCoordinates(cell string) (int, int) { + c, r, err := CellNameToCoordinates(cell) + if err != nil { + panic(err) } + return c, r } -// getCellColRow returns the two parts of a cell identifier (its col and row) -// as strings +// CoordinatesToCellName converts [X, Y] coordinates to alpha-numeric cell name or returns an error. // -// For instance: +// Example: +// CoordinatesToCellName(1, 1) // returns "A1", nil // -// "C220" => "C", "220" -// "aaef42" => "aaef", "42" -// "" => "", "" -func getCellColRow(cell string) (col, row string) { - for index, rune := range cell { - if unicode.IsDigit(rune) { - return cell[:index], cell[index:] - } +func CoordinatesToCellName(col, row int) (string, error) { + if col < 1 || row < 1 { + return "", fmt.Errorf("invalid cell coordinates [%d, %d]", col, row) + } + colname, err := ColumnNumberToName(col) + if err != nil { + return "", fmt.Errorf("invalid cell coordinates [%d, %d]: %v", col, row, err) + } + return fmt.Sprintf("%s%d", colname, row), nil +} +// MustCoordinatesToCellName converts [X, Y] coordinates to alpha-numeric cell name or panics. +// +// Example: +// MustCoordinatesToCellName(1, 1) // returns "A1" +// +func MustCoordinatesToCellName(col, row int) string { + n, err := CoordinatesToCellName(col, row) + if err != nil { + panic(err) } + return n +} + +// boolPtr returns a pointer to a bool with the given value. +func boolPtr(b bool) *bool { return &b } - return cell, "" +// defaultTrue returns true if b is nil, or the pointed value. +func defaultTrue(b *bool) bool { + if b == nil { + return true + } + return *b } // parseFormatSet provides a method to convert format string to []byte and @@ -208,7 +280,9 @@ func namespaceStrictToTransitional(content []byte) []byte { // is great, numerous passwords will match the same hash. Here is the // algorithm to create the hash value: // -// take the ASCII values of all characters shift left the first character 1 bit, the second 2 bits and so on (use only the lower 15 bits and rotate all higher bits, the highest bit of the 16-bit value is always 0 [signed short]) +// take the ASCII values of all characters shift left the first character 1 bit, +// the second 2 bits and so on (use only the lower 15 bits and rotate all higher bits, +// the highest bit of the 16-bit value is always 0 [signed short]) // XOR all these values // XOR the count of characters // XOR the constant 0xCE4B -- cgit v1.2.1