OSDN -- Develop and Download Open Source Software
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

excelize: Commit

Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets


Commit MetaInfo

Revisionf20bbd1f1dcb44eeaf60ed2f5bdc1ac77000a930 (tree)
Time2024-03-04 22:40:27
Authorxuri <xuri.me@gmai...>
Commiterxuri

Log Message

This closes #1830, closes #1831, and closes #1833

- Fix a v2.8.1 regression bug, auto filter does not work in the LibreOffice
- Fix a v2.8.1 regression bug, support to adjust data validation with multiple cell range
- Fix incorrect result data type of the DATE formula function
- Update the unit tests

Change Summary

Incremental Difference

--- a/adjust.go
+++ b/adjust.go
@@ -237,38 +237,43 @@ func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, of
237237 }
238238
239239 // adjustCellRef provides a function to adjust cell reference.
240-func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, bool, error) {
241- if !strings.Contains(ref, ":") {
242- ref += ":" + ref
243- }
244- var delete bool
245- coordinates, err := rangeRefToCoordinates(ref)
246- if err != nil {
247- return ref, delete, err
248- }
249- if dir == columns {
250- if offset < 0 && coordinates[0] == coordinates[2] {
251- delete = true
252- }
253- if coordinates[0] >= num {
254- coordinates[0] += offset
255- }
256- if coordinates[2] >= num {
257- coordinates[2] += offset
240+func (f *File) adjustCellRef(cellRef string, dir adjustDirection, num, offset int) (string, error) {
241+ var SQRef []string
242+ for _, ref := range strings.Split(cellRef, " ") {
243+ if !strings.Contains(ref, ":") {
244+ ref += ":" + ref
258245 }
259- } else {
260- if offset < 0 && coordinates[1] == coordinates[3] {
261- delete = true
246+ coordinates, err := rangeRefToCoordinates(ref)
247+ if err != nil {
248+ return "", err
262249 }
263- if coordinates[1] >= num {
264- coordinates[1] += offset
250+ if dir == columns {
251+ if offset < 0 && coordinates[0] == coordinates[2] {
252+ continue
253+ }
254+ if coordinates[0] >= num {
255+ coordinates[0] += offset
256+ }
257+ if coordinates[2] >= num {
258+ coordinates[2] += offset
259+ }
260+ } else {
261+ if offset < 0 && coordinates[1] == coordinates[3] {
262+ continue
263+ }
264+ if coordinates[1] >= num {
265+ coordinates[1] += offset
266+ }
267+ if coordinates[3] >= num {
268+ coordinates[3] += offset
269+ }
265270 }
266- if coordinates[3] >= num {
267- coordinates[3] += offset
271+ if ref, err = coordinatesToRangeRef(coordinates); err != nil {
272+ return "", err
268273 }
274+ SQRef = append(SQRef, ref)
269275 }
270- ref, err = coordinatesToRangeRef(coordinates)
271- return ref, delete, err
276+ return strings.Join(SQRef, " "), nil
272277 }
273278
274279 // adjustFormula provides a function to adjust formula reference and shared
@@ -284,7 +289,7 @@ func (f *File) adjustFormula(sheet, sheetN string, cell *xlsxC, dir adjustDirect
284289 return nil
285290 }
286291 if cell.F.Ref != "" && sheet == sheetN {
287- if cell.F.Ref, _, err = f.adjustCellRef(cell.F.Ref, dir, num, offset); err != nil {
292+ if cell.F.Ref, err = f.adjustCellRef(cell.F.Ref, dir, num, offset); err != nil {
288293 return err
289294 }
290295 if si && cell.F.Si != nil {
@@ -932,11 +937,11 @@ func (f *File) adjustConditionalFormats(ws *xlsxWorksheet, sheet string, dir adj
932937 if cf == nil {
933938 continue
934939 }
935- ref, del, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
940+ ref, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
936941 if err != nil {
937942 return err
938943 }
939- if del {
944+ if ref == "" {
940945 ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i],
941946 ws.ConditionalFormatting[i+1:]...)
942947 i--
@@ -967,11 +972,11 @@ func (f *File) adjustDataValidations(ws *xlsxWorksheet, sheet string, dir adjust
967972 continue
968973 }
969974 if sheet == sheetN {
970- ref, del, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
975+ ref, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
971976 if err != nil {
972977 return err
973978 }
974- if del {
979+ if ref == "" {
975980 worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation[:i],
976981 worksheet.DataValidations.DataValidation[i+1:]...)
977982 i--
--- a/adjust_test.go
+++ b/adjust_test.go
@@ -1059,6 +1059,16 @@ func TestAdjustDataValidations(t *testing.T) {
10591059 assert.NoError(t, err)
10601060 assert.Equal(t, "\"A<,B>,C\",D\t,E',F\"", dvs[2].Formula1)
10611061
1062+ // Test adjust data validation with multiple cell range
1063+ dv = NewDataValidation(true)
1064+ dv.Sqref = "G1:G3 H1:H3"
1065+ assert.NoError(t, dv.SetDropList([]string{"1", "2", "3"}))
1066+ assert.NoError(t, f.AddDataValidation("Sheet1", dv))
1067+ assert.NoError(t, f.InsertRows("Sheet1", 2, 1))
1068+ dvs, err = f.GetDataValidations("Sheet1")
1069+ assert.NoError(t, err)
1070+ assert.Equal(t, "G1:G4 H1:H4", dvs[3].Sqref)
1071+
10621072 dv = NewDataValidation(true)
10631073 dv.Sqref = "C5:D6"
10641074 assert.NoError(t, dv.SetRange("Sheet1!A1048576", "Sheet1!XFD1", DataValidationTypeWhole, DataValidationOperatorBetween))
--- a/calc.go
+++ b/calc.go
@@ -12081,7 +12081,7 @@ func (fn *formulaFuncs) DATE(argsList *list.List) formulaArg {
1208112081 return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments")
1208212082 }
1208312083 d := makeDate(int(year.Number), time.Month(month.Number), int(day.Number))
12084- return newStringFormulaArg(timeFromExcelTime(daysBetween(excelMinTime1900.Unix(), d)+1, false).String())
12084+ return newNumberFormulaArg(daysBetween(excelMinTime1900.Unix(), d) + 1)
1208512085 }
1208612086
1208712087 // calcDateDif is an implementation of the formula function DATEDIF,
--- a/calc_test.go
+++ b/calc_test.go
@@ -1540,8 +1540,9 @@ func TestCalcCellValue(t *testing.T) {
15401540 "=XOR(1>0,0>1,INT(0),INT(1),A1:A4,2)": "FALSE",
15411541 // Date and Time Functions
15421542 // DATE
1543- "=DATE(2020,10,21)": "2020-10-21 00:00:00 +0000 UTC",
1544- "=DATE(1900,1,1)": "1899-12-31 00:00:00 +0000 UTC",
1543+ "=DATE(2020,10,21)": "44125",
1544+ "=DATE(2020,10,21)+1": "44126",
1545+ "=DATE(1900,1,1)": "1",
15451546 // DATEDIF
15461547 "=DATEDIF(43101,43101,\"D\")": "0",
15471548 "=DATEDIF(43101,43891,\"d\")": "790",
--- a/table.go
+++ b/table.go
@@ -474,7 +474,7 @@ func (f *File) AutoFilter(sheet, rangeRef string, opts []AutoFilterOptions) erro
474474 }
475475 filterRange := fmt.Sprintf("'%s'!%s", sheet, ref)
476476 d := xlsxDefinedName{
477- Name: builtInDefinedNames[2],
477+ Name: builtInDefinedNames[3],
478478 Hidden: true,
479479 LocalSheetID: intPtr(sheetID),
480480 Data: filterRange,
@@ -490,7 +490,7 @@ func (f *File) AutoFilter(sheet, rangeRef string, opts []AutoFilterOptions) erro
490490 if definedName.LocalSheetID != nil {
491491 localSheetID = *definedName.LocalSheetID
492492 }
493- if definedName.Name == builtInDefinedNames[2] && localSheetID == sheetID && definedName.Hidden {
493+ if definedName.Name == builtInDefinedNames[3] && localSheetID == sheetID && definedName.Hidden {
494494 wb.DefinedNames.DefinedName[idx].Data = filterRange
495495 definedNameExists = true
496496 }
--- a/table_test.go
+++ b/table_test.go
@@ -174,7 +174,7 @@ func TestAutoFilter(t *testing.T) {
174174 assert.EqualError(t, f.AutoFilter("Sheet1", "D4:B1", nil), "XML syntax error on line 1: invalid UTF-8")
175175 // Test add auto filter with empty local sheet ID
176176 f = NewFile()
177- f.WorkBook = &xlsxWorkbook{DefinedNames: &xlsxDefinedNames{DefinedName: []xlsxDefinedName{{Name: builtInDefinedNames[2], Hidden: true}}}}
177+ f.WorkBook = &xlsxWorkbook{DefinedNames: &xlsxDefinedNames{DefinedName: []xlsxDefinedName{{Name: builtInDefinedNames[3], Hidden: true}}}}
178178 assert.NoError(t, f.AutoFilter("Sheet1", "A1:B1", nil))
179179 }
180180
Show on old repository browser