From aaced358f135ea871428bba87bb945cc170eee0f Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 26 May 2018 16:23:15 +0800 Subject: - Initialize shared formula types support, relate issue #227; - go test and godoc has been updated --- README.md | 2 +- cell.go | 42 ++++++++++++++++++++++++++++++++++++++++++ excelize.png | Bin 43470 -> 54188 bytes excelize_test.go | 4 ++++ file.go | 2 +- templates.go | 2 +- test/Book1.xlsx | Bin 23005 -> 23099 bytes 7 files changed, 49 insertions(+), 3 deletions(-) mode change 100644 => 100755 test/Book1.xlsx diff --git a/README.md b/README.md index d9c70df..88a67f3 100644 --- a/README.md +++ b/README.md @@ -11,7 +11,7 @@ ## Introduction -Excelize is a library written in pure Golang and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) and [Chinese translation](https://xuri.me/excelize/zh_cn). +Excelize is a library written in pure Go and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) and [Chinese translation](https://xuri.me/excelize/zh_cn). ## Basic Usage diff --git a/cell.go b/cell.go index c5cd838..eb265cc 100644 --- a/cell.go +++ b/cell.go @@ -9,6 +9,17 @@ import ( "time" ) +const ( + // STCellFormulaTypeArray defined the formula is an array formula. + STCellFormulaTypeArray = "array" + // STCellFormulaTypeDataTable defined the formula is a data table formula. + STCellFormulaTypeDataTable = "dataTable" + // STCellFormulaTypeNormal defined the formula is a regular cell formula. + STCellFormulaTypeNormal = "normal" + // STCellFormulaTypeShared defined the formula is part of a shared formula. + STCellFormulaTypeShared = "shared" +) + // mergeCellsParser provides function to check merged cells in worksheet by // given axis. func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { @@ -224,6 +235,9 @@ func (f *File) GetCellFormula(sheet, axis string) string { if xlsx.SheetData.Row[k].R == row { for i := range xlsx.SheetData.Row[k].C { if axis == xlsx.SheetData.Row[k].C[i].R { + if xlsx.SheetData.Row[k].C[i].F.T == STCellFormulaTypeShared { + return getSharedForumula(xlsx, xlsx.SheetData.Row[k].C[i].F.Si) + } if xlsx.SheetData.Row[k].C[i].F != nil { return xlsx.SheetData.Row[k].C[i].F.Content } @@ -234,6 +248,34 @@ func (f *File) GetCellFormula(sheet, axis string) string { return "" } +// getSharedForumula find a cell contains the same formula as another cell, +// the "shared" value can be used for the t attribute and the si attribute can +// be used to refer to the cell containing the formula. Two formulas are +// considered to be the same when their respective representations in +// R1C1-reference notation, are the same. +// +// Note that this function not validate ref tag to check the cell if or not in +// allow area, and always return origin shared formula. +func getSharedForumula(xlsx *xlsxWorksheet, si string) string { + for k := range xlsx.SheetData.Row { + for i := range xlsx.SheetData.Row[k].C { + if xlsx.SheetData.Row[k].C[i].F == nil { + continue + } + if xlsx.SheetData.Row[k].C[i].F.T != STCellFormulaTypeShared { + continue + } + if xlsx.SheetData.Row[k].C[i].F.Si != si { + continue + } + if xlsx.SheetData.Row[k].C[i].F.Ref != "" { + return xlsx.SheetData.Row[k].C[i].F.Content + } + } + } + return "" +} + // SetCellFormula provides function to set cell formula by given string and // worksheet name. func (f *File) SetCellFormula(sheet, axis, formula string) { diff --git a/excelize.png b/excelize.png index b4b4529..9f220b5 100644 Binary files a/excelize.png and b/excelize.png differ diff --git a/excelize_test.go b/excelize_test.go index f1f23b4..bf6ff4c 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -56,6 +56,10 @@ func TestOpenFile(t *testing.T) { // Test get cell formula with illegal rows number. xlsx.GetCellFormula("Sheet1", "B20") xlsx.GetCellFormula("Sheet1", "B") + // Test get shared cell formula + xlsx.GetCellFormula("Sheet2", "H11") + xlsx.GetCellFormula("Sheet2", "I11") + getSharedForumula(&xlsxWorksheet{}, "") // Test read cell value with given illegal rows number. xlsx.GetCellValue("Sheet2", "a-1") xlsx.GetCellValue("Sheet2", "A") diff --git a/file.go b/file.go index 36af6f8..39798e1 100644 --- a/file.go +++ b/file.go @@ -72,7 +72,7 @@ func (f *File) Write(w io.Writer) error { if err != nil { return err } - _, err = fi.Write([]byte(content)) + _, err = fi.Write(content) if err != nil { return err } diff --git a/templates.go b/templates.go index 708368e..ef6058c 100644 --- a/templates.go +++ b/templates.go @@ -12,7 +12,7 @@ var ( XMLHeaderByte = []byte(XMLHeader) ) -const templateDocpropsApp = `0Golang Excelize` +const templateDocpropsApp = `0Go Excelize` const templateContentTypes = `` diff --git a/test/Book1.xlsx b/test/Book1.xlsx old mode 100644 new mode 100755 index f94dfe9..84c43d1 Binary files a/test/Book1.xlsx and b/test/Book1.xlsx differ -- cgit v1.2.1