qax-os
Address cells directly by row and column.
This is related to issue 193, which I see is marked as closed, but to me it doesn't solve the issue.
To process things in loops there is still the steps needed of first building a string that has both the row and column in it (e.g. constructing 'B6' etc), for things like the set cell functions to then split those back out again into separate row and column values.
If, for example, building up an xls file that contains thousands of rows such as some form of report/data extract, you end up doing things like:
func makeAxis(col, row int) string {
return fmt.Sprintf("%s%d",excelize.ToAlphaString(col),row)
}
Then calling this inside what could be big loops:
SetCell(... , makeAxis(col,row), ...)
instead of just being able to directly call
SetCellXY(..., col, row, ...)
As it stands for every cell the following actions are taken:
Starting with col and row int values (as part of a loop) Convert col int to a alpha string (convert 1 to A, 2 to B etc) Convert row int to a numeric string Join col string and row string .. inside SetColl .. Extract alpha from col/row string. Convert alpha string back to int (A to 1, B to 2 etc) Extract digits from col/row string. Convert digit string back to int Now have the col and row values we started with...
Being able to pass in row and column as separate params would be a lot more efficient than having to join the values only for the SetCell functions to parse and split them back out again every time.
Basically a set of functions that mirror the existing SetCell functions with a SetCellXY variant (replacing the axis param with separate row and column params)
I know there is the also checks for merged cells done on the axis, It would be up to whoever is calling to ensure the col/row values are the correct one to reference the visible cell in the event of a merged cells (if they are using merged cells, which in this sort of bulk data report scenario would seem unlikely, at least in the area of the sheet covered by the bulk data)
This is a major issue in the current API with which I agree as this API constraint of using only string cell reference is a performance bottleneck when you iterate on columns or rows and apply multiple changes to the same cell.
In my code I had to write this utility function (testsuite available on request) to workaround this API constraint:
func XY(x, y int) string {
var r []byte
if x < 26 {
r = make([]byte, 1, 5)
r[0] = 'A' + byte(x)
} else if x < 27*26 {
r = make([]byte, 2, 5)
r[0] = 'A' - 1 + byte(x/26)
r[1] = 'A' + byte(x%26)
} else if x < 16384 {
r = make([]byte, 3, 5)
r[2] = 'A' + byte(x%26)
x /= 26
r[0] = 'A' - 1 + byte(x/26)
r[1] = 'A' - 1 + byte(x%26)
} else {
panic(fmt.Errorf("more than 16384 columns: %d", x))
}
return string(strconv.AppendUint(r, uint64(y+1), 10))
}
Couldn't you just memoize the results of ToAlphaString if you're worried about that being a bottleneck?
Or keep an array of generated column strings from ToAlphaString and access them by index, only adding new ones as necessary?
The library does now have CoordinatesToCellName which would improve the convenience side of things.
Have you benchmarked to see if this was actually a performance bottleneck though? Bad performance writing many rows and columns might be related to #382 instead.
From a new user perspective, the API of https://github.com/tealeg/xlsx is much more friendly. Maybe excelize has more features I don' t need.
