From f2b8798a34aab4411a50861a4cdf47203edc3a19 Mon Sep 17 00:00:00 2001
From: Artem Kustikov <artem.kustikov@gmail.com>
Date: Sun, 4 Oct 2020 16:07:39 +0300
Subject: extend cell value load to support custom datetime format (#703)
* extend cell value load to support custom datetime format
* cleanup incorrect imports
* fix numeric values conversion as done in legacy Excel
* fix tests coverage
* revert temporary package name fix
* remove personal info from test XLSX files
* remove unused dependencies
* update format conversion in parseTime
* new UT to increase code coverage
* Resolve code review issue for PR #703
* Rename broken file name generated by unit test
Co-authored-by: xuri <xuri.me@gmail.com>
---
.gitignore | 1 +
cell.go | 18 +++-
cell_test.go | 36 ++++++++
crypt_test.go | 2 +-
excelize.go | 2 +-
excelize_test.go | 5 +-
file.go | 2 +-
rows.go | 19 +++++
rows_test.go | 37 +++++++-
sheet_test.go | 86 +++++++++----------
sheetpr_test.go | 250 +++++++++++++++++++++++++++---------------------------
sheetview_test.go | 92 ++++++++++----------
styles.go | 104 +++++++++++++++++------
styles_test.go | 46 ++++++++++
14 files changed, 452 insertions(+), 248 deletions(-)
diff --git a/.gitignore b/.gitignore
index a3fcff2..685d2bf 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,5 +1,6 @@
~$*.xlsx
test/Test*.xlsx
+test/Test*.xlsm
*.out
*.test
.idea
diff --git a/cell.go b/cell.go
index 5fe2157..11c6836 100644
--- a/cell.go
+++ b/cell.go
@@ -762,9 +762,23 @@ func (f *File) formattedValue(s int, v string) string {
return v
}
styleSheet := f.stylesReader()
- ok := builtInNumFmtFunc[*styleSheet.CellXfs.Xf[s].NumFmtID]
+ if s >= len(styleSheet.CellXfs.Xf) {
+ return v
+ }
+ numFmtId := *styleSheet.CellXfs.Xf[s].NumFmtID
+ ok := builtInNumFmtFunc[numFmtId]
if ok != nil {
- return ok(*styleSheet.CellXfs.Xf[s].NumFmtID, v)
+ return ok(v, builtInNumFmt[numFmtId])
+ }
+ for _, xlsxFmt := range styleSheet.NumFmts.NumFmt {
+ if xlsxFmt.NumFmtID == numFmtId {
+ format := strings.ToLower(xlsxFmt.FormatCode)
+ if strings.Contains(format, "y") || strings.Contains(format, "m") || strings.Contains(format, "d") || strings.Contains(format, "h") {
+ return parseTime(v, format)
+ }
+
+ return v
+ }
}
return v
}
diff --git a/cell_test.go b/cell_test.go
index 441a694..a855344 100644
--- a/cell_test.go
+++ b/cell_test.go
@@ -111,6 +111,23 @@ func TestSetCellValue(t *testing.T) {
assert.EqualError(t, f.SetCellValue("Sheet1", "A", time.Duration(1e13)), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
}
+func TestSetCellValues(t *testing.T) {
+ f := NewFile()
+ err := f.SetCellValue("Sheet1", "A1", time.Date(2010, time.December, 31, 0, 0, 0, 0, time.UTC))
+ assert.NoError(t, err)
+
+ v, err := f.GetCellValue("Sheet1", "A1")
+ assert.NoError(t, err)
+ assert.Equal(t, v, "12/31/10 12:00")
+
+ // test date value lower than min date supported by Excel
+ err = f.SetCellValue("Sheet1", "A1", time.Date(1600, time.December, 31, 0, 0, 0, 0, time.UTC))
+ assert.NoError(t, err)
+
+ _, err = f.GetCellValue("Sheet1", "A1")
+ assert.EqualError(t, err, `strconv.ParseFloat: parsing "1600-12-31T00:00:00Z": invalid syntax`)
+}
+
func TestSetCellBool(t *testing.T) {
f := NewFile()
assert.EqualError(t, f.SetCellBool("Sheet1", "A", true), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
@@ -264,3 +281,22 @@ func TestSetCellRichText(t *testing.T) {
// Test set cell rich text with illegal cell coordinates
assert.EqualError(t, f.SetCellRichText("Sheet1", "A", richTextRun), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
}
+
+func TestFormattedValue(t *testing.T) {
+ f := NewFile()
+ v := f.formattedValue(0, "43528")
+ assert.Equal(t, "43528", v)
+
+ v = f.formattedValue(15, "43528")
+ assert.Equal(t, "43528", v)
+
+ v = f.formattedValue(1, "43528")
+ assert.Equal(t, "43528", v)
+ customNumFmt := "[$-409]MM/DD/YYYY"
+ _, err := f.NewStyle(&Style{
+ CustomNumFmt: &customNumFmt,
+ })
+ assert.NoError(t, err)
+ v = f.formattedValue(1, "43528")
+ assert.Equal(t, "03/04/2019", v)
+}
diff --git a/crypt_test.go b/crypt_test.go
index c6acb38..f9a3fb7 100644
--- a/crypt_test.go
+++ b/crypt_test.go
@@ -19,5 +19,5 @@ import (
func TestEncrypt(t *testing.T) {
f, err := OpenFile(filepath.Join("test", "encryptSHA1.xlsx"), Options{Password: "password"})
assert.NoError(t, err)
- assert.EqualError(t, f.SaveAs(filepath.Join("test", "TestEncrypt.xlsx"), Options{Password: "password"}), "not support encryption currently")
+ assert.EqualError(t, f.SaveAs(filepath.Join("test", "BadEncrypt.xlsx"), Options{Password: "password"}), "not support encryption currently")
}
diff --git a/excelize.go b/excelize.go
index a90b765..cca6616 100644
--- a/excelize.go
+++ b/excelize.go
@@ -158,7 +158,7 @@ func (f *File) setDefaultTimeStyle(sheet, axis string, format int) error {
}
if s == 0 {
style, _ := f.NewStyle(&Style{NumFmt: format})
- _ = f.SetCellStyle(sheet, axis, axis, style)
+ err = f.SetCellStyle(sheet, axis, axis, style)
}
return err
}
diff --git a/excelize_test.go b/excelize_test.go
index f1cd652..890bcf6 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -257,7 +257,7 @@ func TestBrokenFile(t *testing.T) {
t.Run("SaveAsEmptyStruct", func(t *testing.T) {
// Test write file with broken file struct with given path.
- assert.NoError(t, f.SaveAs(filepath.Join("test", "BrokenFile.SaveAsEmptyStruct.xlsx")))
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "BadWorkbook.SaveAsEmptyStruct.xlsx")))
})
t.Run("OpenBadWorkbook", func(t *testing.T) {
@@ -1175,6 +1175,9 @@ func TestSetDefaultTimeStyle(t *testing.T) {
f := NewFile()
// Test set default time style on not exists worksheet.
assert.EqualError(t, f.setDefaultTimeStyle("SheetN", "", 0), "sheet SheetN is not exist")
+
+ // Test set default time style on invalid cell
+ assert.EqualError(t, f.setDefaultTimeStyle("Sheet1", "", 42), "cannot convert cell \"\" to coordinates: invalid cell name \"\"")
}
func TestAddVBAProject(t *testing.T) {
diff --git a/file.go b/file.go
index 83ed271..6a48c0c 100644
--- a/file.go
+++ b/file.go
@@ -123,7 +123,7 @@ func (f *File) WriteToBuffer() (*bytes.Buffer, error) {
}
}
- if f.options != nil {
+ if f.options != nil && f.options.Password != "" {
if err := zw.Close(); err != nil {
return buf, err
}
diff --git a/rows.go b/rows.go
index eb4b1df..50e7308 100644
--- a/rows.go
+++ b/rows.go
@@ -345,6 +345,25 @@ func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) {
}
return f.formattedValue(xlsx.S, xlsx.V), nil
default:
+ // correct numeric values as legacy Excel app
+ // https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
+ // In the top figure the fraction 1/9000 in Excel is displayed.
+ // Although this number has a decimal representation that is an infinite string of ones,
+ // Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures.
+ const precision = 1000000000000000
+ if len(xlsx.V) > 16 {
+ num, err := strconv.ParseFloat(xlsx.V, 64)
+ if err != nil {
+ return "", err
+ }
+
+ num = math.Round(num*precision) / precision
+ val := fmt.Sprintf("%g", num)
+ if val != xlsx.V {
+ return f.formattedValue(xlsx.S, val), nil
+ }
+ }
+
return f.formattedValue(xlsx.S, xlsx.V), nil
}
}
diff --git a/rows_test.go b/rows_test.go
index c469c01..246233f 100644
--- a/rows_test.go
+++ b/rows_test.go
@@ -817,7 +817,7 @@ func TestDuplicateMergeCells(t *testing.T) {
assert.EqualError(t, f.duplicateMergeCells("SheetN", xlsx, 1, 2), "sheet SheetN is not exist")
}
-func TestGetValueFrom(t *testing.T) {
+func TestGetValueFromInlineStr(t *testing.T) {
c := &xlsxC{T: "inlineStr"}
f := NewFile()
d := &xlsxSST{}
@@ -826,6 +826,20 @@ func TestGetValueFrom(t *testing.T) {
assert.Equal(t, "", val)
}
+func TestGetValueFromNumber(t *testing.T) {
+ c := &xlsxC{T: "n", V: "2.2200000000000002"}
+ f := NewFile()
+ d := &xlsxSST{}
+ val, err := c.getValueFrom(f, d)
+ assert.NoError(t, err)
+ assert.Equal(t, "2.22", val)
+
+ c = &xlsxC{T: "n", V: "2.220000ddsf0000000002-r"}
+ val, err = c.getValueFrom(f, d)
+ assert.NotNil(t, err)
+ assert.Equal(t, "strconv.ParseFloat: parsing \"2.220000ddsf0000000002-r\": invalid syntax", err.Error())
+}
+
func TestErrSheetNotExistError(t *testing.T) {
err := ErrSheetNotExist{SheetName: "Sheet1"}
assert.EqualValues(t, err.Error(), "sheet Sheet1 is not exist")
@@ -842,6 +856,27 @@ func TestCheckRow(t *testing.T) {
assert.EqualError(t, f.SetCellValue("Sheet1", "A1", false), `cannot convert cell "-" to coordinates: invalid cell name "-"`)
}
+func TestNumberFormats(t *testing.T) {
+ f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ cells := make([][]string, 0)
+ cols, err := f.Cols("Sheet2")
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ for cols.Next() {
+ col, err := cols.Rows()
+ assert.NoError(t, err)
+ if err != nil {
+ break
+ }
+ cells = append(cells, col)
+ }
+ assert.Equal(t, []string{"", "200", "450", "200", "510", "315", "127", "89", "348", "53", "37"}, cells[3])
+}
+
func BenchmarkRows(b *testing.B) {
f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
for i := 0; i < b.N; i++ {
diff --git a/sheet_test.go b/sheet_test.go
index 0014220..890a4e5 100644
--- a/sheet_test.go
+++ b/sheet_test.go
@@ -1,4 +1,4 @@
-package excelize_test
+package excelize
import (
"fmt"
@@ -6,26 +6,24 @@ import (
"strings"
"testing"
- "github.com/360EntSecGroup-Skylar/excelize/v2"
-
"github.com/mohae/deepcopy"
"github.com/stretchr/testify/assert"
)
func ExampleFile_SetPageLayout() {
- f := excelize.NewFile()
+ f := NewFile()
if err := f.SetPageLayout(
"Sheet1",
- excelize.PageLayoutOrientation(excelize.OrientationLandscape),
+ PageLayoutOrientation(OrientationLandscape),
); err != nil {
fmt.Println(err)
}
if err := f.SetPageLayout(
"Sheet1",
- excelize.PageLayoutPaperSize(10),
- excelize.FitToHeight(2),
- excelize.FitToWidth(2),
+ PageLayoutPaperSize(10),
+ FitToHeight(2),
+ FitToWidth(2),
); err != nil {
fmt.Println(err)
}
@@ -33,12 +31,12 @@ func ExampleFile_SetPageLayout() {
}
func ExampleFile_GetPageLayout() {
- f := excelize.NewFile()
+ f := NewFile()
var (
- orientation excelize.PageLayoutOrientation
- paperSize excelize.PageLayoutPaperSize
- fitToHeight excelize.FitToHeight
- fitToWidth excelize.FitToWidth
+ orientation PageLayoutOrientation
+ paperSize PageLayoutPaperSize
+ fitToHeight FitToHeight
+ fitToWidth FitToWidth
)
if err := f.GetPageLayout("Sheet1", &orientation); err != nil {
fmt.Println(err)
@@ -67,7 +65,7 @@ func ExampleFile_GetPageLayout() {
}
func TestNewSheet(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
sheetID := f.NewSheet("Sheet2")
f.SetActiveSheet(sheetID)
// delete original sheet
@@ -76,7 +74,7 @@ func TestNewSheet(t *testing.T) {
}
func TestSetPane(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.SetPanes("Sheet1", `{"freeze":false,"split":false}`))
f.NewSheet("Panes 2")
assert.NoError(t, f.SetPanes("Panes 2", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"top_left_cell":"B1","active_pane":"topRight","panes":[{"sqref":"K16","active_cell":"K16","pane":"topRight"}]}`))
@@ -93,13 +91,13 @@ func TestPageLayoutOption(t *testing.T) {
const sheet = "Sheet1"
testData := []struct {
- container excelize.PageLayoutOptionPtr
- nonDefault excelize.PageLayoutOption
+ container PageLayoutOptionPtr
+ nonDefault PageLayoutOption
}{
- {new(excelize.PageLayoutOrientation), excelize.PageLayoutOrientation(excelize.OrientationLandscape)},
- {new(excelize.PageLayoutPaperSize), excelize.PageLayoutPaperSize(10)},
- {new(excelize.FitToHeight), excelize.FitToHeight(2)},
- {new(excelize.FitToWidth), excelize.FitToWidth(2)},
+ {new(PageLayoutOrientation), PageLayoutOrientation(OrientationLandscape)},
+ {new(PageLayoutPaperSize), PageLayoutPaperSize(10)},
+ {new(FitToHeight), FitToHeight(2)},
+ {new(FitToWidth), FitToWidth(2)},
}
for i, test := range testData {
@@ -108,11 +106,11 @@ func TestPageLayoutOption(t *testing.T) {
opt := test.nonDefault
t.Logf("option %T", opt)
- def := deepcopy.Copy(test.container).(excelize.PageLayoutOptionPtr)
- val1 := deepcopy.Copy(def).(excelize.PageLayoutOptionPtr)
- val2 := deepcopy.Copy(def).(excelize.PageLayoutOptionPtr)
+ def := deepcopy.Copy(test.container).(PageLayoutOptionPtr)
+ val1 := deepcopy.Copy(def).(PageLayoutOptionPtr)
+ val2 := deepcopy.Copy(def).(PageLayoutOptionPtr)
- f := excelize.NewFile()
+ f := NewFile()
// Get the default value
assert.NoError(t, f.GetPageLayout(sheet, def), opt)
// Get again and check
@@ -150,7 +148,7 @@ func TestPageLayoutOption(t *testing.T) {
}
func TestSearchSheet(t *testing.T) {
- f, err := excelize.OpenFile(filepath.Join("test", "SharedStrings.xlsx"))
+ f, err := OpenFile(filepath.Join("test", "SharedStrings.xlsx"))
if !assert.NoError(t, err) {
t.FailNow()
}
@@ -172,36 +170,36 @@ func TestSearchSheet(t *testing.T) {
assert.EqualValues(t, expected, result)
// Test search worksheet data after set cell value
- f = excelize.NewFile()
+ f = NewFile()
assert.NoError(t, f.SetCellValue("Sheet1", "A1", true))
_, err = f.SearchSheet("Sheet1", "")
assert.NoError(t, err)
}
func TestSetPageLayout(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test set page layout on not exists worksheet.
assert.EqualError(t, f.SetPageLayout("SheetN"), "sheet SheetN is not exist")
}
func TestGetPageLayout(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test get page layout on not exists worksheet.
assert.EqualError(t, f.GetPageLayout("SheetN"), "sheet SheetN is not exist")
}
func TestSetHeaderFooter(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.SetCellStr("Sheet1", "A1", "Test SetHeaderFooter"))
// Test set header and footer on not exists worksheet.
assert.EqualError(t, f.SetHeaderFooter("SheetN", nil), "sheet SheetN is not exist")
// Test set header and footer with illegal setting.
- assert.EqualError(t, f.SetHeaderFooter("Sheet1", &excelize.FormatHeaderFooter{
+ assert.EqualError(t, f.SetHeaderFooter("Sheet1", &FormatHeaderFooter{
OddHeader: strings.Repeat("c", 256),
}), "field OddHeader must be less than 255 characters")
assert.NoError(t, f.SetHeaderFooter("Sheet1", nil))
- assert.NoError(t, f.SetHeaderFooter("Sheet1", &excelize.FormatHeaderFooter{
+ assert.NoError(t, f.SetHeaderFooter("Sheet1", &FormatHeaderFooter{
DifferentFirst: true,
DifferentOddEven: true,
OddHeader: "&R&P",
@@ -214,28 +212,28 @@ func TestSetHeaderFooter(t *testing.T) {
}
func TestDefinedName(t *testing.T) {
- f := excelize.NewFile()
- assert.NoError(t, f.SetDefinedName(&excelize.DefinedName{
+ f := NewFile()
+ assert.NoError(t, f.SetDefinedName(&DefinedName{
Name: "Amount",
RefersTo: "Sheet1!$A$2:$D$5",
Comment: "defined name comment",
Scope: "Sheet1",
}))
- assert.NoError(t, f.SetDefinedName(&excelize.DefinedName{
+ assert.NoError(t, f.SetDefinedName(&DefinedName{
Name: "Amount",
RefersTo: "Sheet1!$A$2:$D$5",
Comment: "defined name comment",
}))
- assert.EqualError(t, f.SetDefinedName(&excelize.DefinedName{
+ assert.EqualError(t, f.SetDefinedName(&DefinedName{
Name: "Amount",
RefersTo: "Sheet1!$A$2:$D$5",
Comment: "defined name comment",
}), "the same name already exists on the scope")
- assert.EqualError(t, f.DeleteDefinedName(&excelize.DefinedName{
+ assert.EqualError(t, f.DeleteDefinedName(&DefinedName{
Name: "No Exist Defined Name",
}), "no defined name on the scope")
assert.Exactly(t, "Sheet1!$A$2:$D$5", f.GetDefinedName()[1].RefersTo)
- assert.NoError(t, f.DeleteDefinedName(&excelize.DefinedName{
+ assert.NoError(t, f.DeleteDefinedName(&DefinedName{
Name: "Amount",
}))
assert.Exactly(t, "Sheet1!$A$2:$D$5", f.GetDefinedName()[0].RefersTo)
@@ -244,7 +242,7 @@ func TestDefinedName(t *testing.T) {
}
func TestGroupSheets(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
sheets := []string{"Sheet2", "Sheet3"}
for _, sheet := range sheets {
f.NewSheet(sheet)
@@ -256,7 +254,7 @@ func TestGroupSheets(t *testing.T) {
}
func TestUngroupSheets(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
sheets := []string{"Sheet2", "Sheet3", "Sheet4", "Sheet5"}
for _, sheet := range sheets {
f.NewSheet(sheet)
@@ -265,7 +263,7 @@ func TestUngroupSheets(t *testing.T) {
}
func TestInsertPageBreak(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.InsertPageBreak("Sheet1", "A1"))
assert.NoError(t, f.InsertPageBreak("Sheet1", "B2"))
assert.NoError(t, f.InsertPageBreak("Sheet1", "C3"))
@@ -276,7 +274,7 @@ func TestInsertPageBreak(t *testing.T) {
}
func TestRemovePageBreak(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.RemovePageBreak("Sheet1", "A2"))
assert.NoError(t, f.InsertPageBreak("Sheet1", "A2"))
@@ -302,7 +300,7 @@ func TestRemovePageBreak(t *testing.T) {
}
func TestGetSheetName(t *testing.T) {
- f, _ := excelize.OpenFile(filepath.Join("test", "Book1.xlsx"))
+ f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
assert.Equal(t, "Sheet1", f.GetSheetName(0))
assert.Equal(t, "Sheet2", f.GetSheetName(1))
assert.Equal(t, "", f.GetSheetName(-1))
@@ -314,7 +312,7 @@ func TestGetSheetMap(t *testing.T) {
1: "Sheet1",
2: "Sheet2",
}
- f, _ := excelize.OpenFile(filepath.Join("test", "Book1.xlsx"))
+ f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
sheetMap := f.GetSheetMap()
for idx, name := range sheetMap {
assert.Equal(t, expectedMap[idx], name)
diff --git a/sheetpr_test.go b/sheetpr_test.go
index 6e03151..29bd99e 100644
--- a/sheetpr_test.go
+++ b/sheetpr_test.go
@@ -1,4 +1,4 @@
-package excelize_test
+package excelize
import (
"fmt"
@@ -6,39 +6,37 @@ import (
"github.com/mohae/deepcopy"
"github.com/stretchr/testify/assert"
-
- "github.com/360EntSecGroup-Skylar/excelize/v2"
)
-var _ = []excelize.SheetPrOption{
- excelize.CodeName("hello"),
- excelize.EnableFormatConditionsCalculation(false),
- excelize.Published(false),
- excelize.FitToPage(true),
- excelize.AutoPageBreaks(true),
- excelize.OutlineSummaryBelow(true),
+var _ = []SheetPrOption{
+ CodeName("hello"),
+ EnableFormatConditionsCalculation(false),
+ Published(false),
+ FitToPage(true),
+ AutoPageBreaks(true),
+ OutlineSummaryBelow(true),
}
-var _ = []excelize.SheetPrOptionPtr{
- (*excelize.CodeName)(nil),
- (*excelize.EnableFormatConditionsCalculation)(nil),
- (*excelize.Published)(nil),
- (*excelize.FitToPage)(nil),
- (*excelize.AutoPageBreaks)(nil),
- (*excelize.OutlineSummaryBelow)(nil),
+var _ = []SheetPrOptionPtr{
+ (*CodeName)(nil),
+ (*EnableFormatConditionsCalculation)(nil),
+ (*Published)(nil),
+ (*FitToPage)(nil),
+ (*AutoPageBreaks)(nil),
+ (*OutlineSummaryBelow)(nil),
}
func ExampleFile_SetSheetPrOptions() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
if err := f.SetSheetPrOptions(sheet,
- excelize.CodeName("code"),
- excelize.EnableFormatConditionsCalculation(false),
- excelize.Published(false),
- excelize.FitToPage(true),
- excelize.AutoPageBreaks(true),
- excelize.OutlineSummaryBelow(false),
+ CodeName("code"),
+ EnableFormatConditionsCalculation(false),
+ Published(false),
+ FitToPage(true),
+ AutoPageBreaks(true),
+ OutlineSummaryBelow(false),
); err != nil {
fmt.Println(err)
}
@@ -46,16 +44,16 @@ func ExampleFile_SetSheetPrOptions() {
}
func ExampleFile_GetSheetPrOptions() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
var (
- codeName excelize.CodeName
- enableFormatConditionsCalculation excelize.EnableFormatConditionsCalculation
- published excelize.Published
- fitToPage excelize.FitToPage
- autoPageBreaks excelize.AutoPageBreaks
- outlineSummaryBelow excelize.OutlineSummaryBelow
+ codeName CodeName
+ enableFormatConditionsCalculation EnableFormatConditionsCalculation
+ published Published
+ fitToPage FitToPage
+ autoPageBreaks AutoPageBreaks
+ outlineSummaryBelow OutlineSummaryBelow
)
if err := f.GetSheetPrOptions(sheet,
@@ -89,15 +87,15 @@ func TestSheetPrOptions(t *testing.T) {
const sheet = "Sheet1"
testData := []struct {
- container excelize.SheetPrOptionPtr
- nonDefault excelize.SheetPrOption
+ container SheetPrOptionPtr
+ nonDefault SheetPrOption
}{
- {new(excelize.CodeName), excelize.CodeName("xx")},
- {new(excelize.EnableFormatConditionsCalculation), excelize.EnableFormatConditionsCalculation(false)},
- {new(excelize.Published), excelize.Published(false)},
- {new(excelize.FitToPage), excelize.FitToPage(true)},
- {new(excelize.AutoPageBreaks), excelize.AutoPageBreaks(true)},
- {new(excelize.OutlineSummaryBelow), excelize.OutlineSummaryBelow(false)},
+ {new(CodeName), CodeName("xx")},
+ {new(EnableFormatConditionsCalculation), EnableFormatConditionsCalculation(false)},
+ {new(Published), Published(false)},
+ {new(FitToPage), FitToPage(true)},
+ {new(AutoPageBreaks), AutoPageBreaks(true)},
+ {new(OutlineSummaryBelow), OutlineSummaryBelow(false)},
}
for i, test := range testData {
@@ -106,11 +104,11 @@ func TestSheetPrOptions(t *testing.T) {
opt := test.nonDefault
t.Logf("option %T", opt)
- def := deepcopy.Copy(test.container).(excelize.SheetPrOptionPtr)
- val1 := deepcopy.Copy(def).(excelize.SheetPrOptionPtr)
- val2 := deepcopy.Copy(def).(excelize.SheetPrOptionPtr)
+ def := deepcopy.Copy(test.container).(SheetPrOptionPtr)
+ val1 := deepcopy.Copy(def).(SheetPrOptionPtr)
+ val2 := deepcopy.Copy(def).(SheetPrOptionPtr)
- f := excelize.NewFile()
+ f := NewFile()
// Get the default value
assert.NoError(t, f.GetSheetPrOptions(sheet, def), opt)
// Get again and check
@@ -148,46 +146,46 @@ func TestSheetPrOptions(t *testing.T) {
}
func TestSetSheetrOptions(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test SetSheetrOptions on not exists worksheet.
assert.EqualError(t, f.SetSheetPrOptions("SheetN"), "sheet SheetN is not exist")
}
func TestGetSheetPrOptions(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test GetSheetPrOptions on not exists worksheet.
assert.EqualError(t, f.GetSheetPrOptions("SheetN"), "sheet SheetN is not exist")
}
-var _ = []excelize.PageMarginsOptions{
- excelize.PageMarginBottom(1.0),
- excelize.PageMarginFooter(1.0),
- excelize.PageMarginHeader(1.0),
- excelize.PageMarginLeft(1.0),
- excelize.PageMarginRight(1.0),
- excelize.PageMarginTop(1.0),
+var _ = []PageMarginsOptions{
+ PageMarginBottom(1.0),
+ PageMarginFooter(1.0),
+ PageMarginHeader(1.0),
+ PageMarginLeft(1.0),
+ PageMarginRight(1.0),
+ PageMarginTop(1.0),
}
-var _ = []excelize.PageMarginsOptionsPtr{
- (*excelize.PageMarginBottom)(nil),
- (*excelize.PageMarginFooter)(nil),
- (*excelize.PageMarginHeader)(nil),
- (*excelize.PageMarginLeft)(nil),
- (*excelize.PageMarginRight)(nil),
- (*excelize.PageMarginTop)(nil),
+var _ = []PageMarginsOptionsPtr{
+ (*PageMarginBottom)(nil),
+ (*PageMarginFooter)(nil),
+ (*PageMarginHeader)(nil),
+ (*PageMarginLeft)(nil),
+ (*PageMarginRight)(nil),
+ (*PageMarginTop)(nil),
}
func ExampleFile_SetPageMargins() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
if err := f.SetPageMargins(sheet,
- excelize.PageMarginBottom(1.0),
- excelize.PageMarginFooter(1.0),
- excelize.PageMarginHeader(1.0),
- excelize.PageMarginLeft(1.0),
- excelize.PageMarginRight(1.0),
- excelize.PageMarginTop(1.0),
+ PageMarginBottom(1.0),
+ PageMarginFooter(1.0),
+ PageMarginHeader(1.0),
+ PageMarginLeft(1.0),
+ PageMarginRight(1.0),
+ PageMarginTop(1.0),
); err != nil {
fmt.Println(err)
}
@@ -195,16 +193,16 @@ func ExampleFile_SetPageMargins() {
}
func ExampleFile_GetPageMargins() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
var (
- marginBottom excelize.PageMarginBottom
- marginFooter excelize.PageMarginFooter
- marginHeader excelize.PageMarginHeader
- marginLeft excelize.PageMarginLeft
- marginRight excelize.PageMarginRight
- marginTop excelize.PageMarginTop
+ marginBottom PageMarginBottom
+ marginFooter PageMarginFooter
+ marginHeader PageMarginHeader
+ marginLeft PageMarginLeft
+ marginRight PageMarginRight
+ marginTop PageMarginTop
)
if err := f.GetPageMargins(sheet,
@@ -238,15 +236,15 @@ func TestPageMarginsOption(t *testing.T) {
const sheet = "Sheet1"
testData := []struct {
- container excelize.PageMarginsOptionsPtr
- nonDefault excelize.PageMarginsOptions
+ container PageMarginsOptionsPtr
+ nonDefault PageMarginsOptions
}{
- {new(excelize.PageMarginTop), excelize.PageMarginTop(1.0)},
- {new(excelize.PageMarginBottom), excelize.PageMarginBottom(1.0)},
- {new(excelize.PageMarginLeft), excelize.PageMarginLeft(1.0)},
- {new(excelize.PageMarginRight), excelize.PageMarginRight(1.0)},
- {new(excelize.PageMarginHeader), excelize.PageMarginHeader(1.0)},
- {new(excelize.PageMarginFooter), excelize.PageMarginFooter(1.0)},
+ {new(PageMarginTop), PageMarginTop(1.0)},
+ {new(PageMarginBottom), PageMarginBottom(1.0)},
+ {new(PageMarginLeft), PageMarginLeft(1.0)},
+ {new(PageMarginRight), PageMarginRight(1.0)},
+ {new(PageMarginHeader), PageMarginHeader(1.0)},
+ {new(PageMarginFooter), PageMarginFooter(1.0)},
}
for i, test := range testData {
@@ -255,11 +253,11 @@ func TestPageMarginsOption(t *testing.T) {
opt := test.nonDefault
t.Logf("option %T", opt)
- def := deepcopy.Copy(test.container).(excelize.PageMarginsOptionsPtr)
- val1 := deepcopy.Copy(def).(excelize.PageMarginsOptionsPtr)
- val2 := deepcopy.Copy(def).(excelize.PageMarginsOptionsPtr)
+ def := deepcopy.Copy(test.container).(PageMarginsOptionsPtr)
+ val1 := deepcopy.Copy(def).(PageMarginsOptionsPtr)
+ val2 := deepcopy.Copy(def).(PageMarginsOptionsPtr)
- f := excelize.NewFile()
+ f := NewFile()
// Get the default value
assert.NoError(t, f.GetPageMargins(sheet, def), opt)
// Get again and check
@@ -297,29 +295,29 @@ func TestPageMarginsOption(t *testing.T) {
}
func TestSetPageMargins(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test set page margins on not exists worksheet.
assert.EqualError(t, f.SetPageMargins("SheetN"), "sheet SheetN is not exist")
}
func TestGetPageMargins(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
// Test get page margins on not exists worksheet.
assert.EqualError(t, f.GetPageMargins("SheetN"), "sheet SheetN is not exist")
}
func ExampleFile_SetSheetFormatPr() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
if err := f.SetSheetFormatPr(sheet,
- excelize.BaseColWidth(1.0),
- excelize.DefaultColWidth(1.0),
- excelize.DefaultRowHeight(1.0),
- excelize.CustomHeight(true),
- excelize.ZeroHeight(true),
- excelize.ThickTop(true),
- excelize.ThickBottom(true),
+ BaseColWidth(1.0),
+ DefaultColWidth(1.0),
+ DefaultRowHeight(1.0),
+ CustomHeight(true),
+ ZeroHeight(true),
+ ThickTop(true),
+ ThickBottom(true),
); err != nil {
fmt.Println(err)
}
@@ -327,17 +325,17 @@ func ExampleFile_SetSheetFormatPr() {
}
func ExampleFile_GetSheetFormatPr() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
var (
- baseColWidth excelize.BaseColWidth
- defaultColWidth excelize.DefaultColWidth
- defaultRowHeight excelize.DefaultRowHeight
- customHeight excelize.CustomHeight
- zeroHeight excelize.ZeroHeight
- thickTop excelize.ThickTop
- thickBottom excelize.ThickBottom
+ baseColWidth BaseColWidth
+ defaultColWidth DefaultColWidth
+ defaultRowHeight DefaultRowHeight
+ customHeight CustomHeight
+ zeroHeight ZeroHeight
+ thickTop ThickTop
+ thickBottom ThickBottom
)
if err := f.GetSheetFormatPr(sheet,
@@ -374,16 +372,16 @@ func TestSheetFormatPrOptions(t *testing.T) {
const sheet = "Sheet1"
testData := []struct {
- container excelize.SheetFormatPrOptionsPtr
- nonDefault excelize.SheetFormatPrOptions
+ container SheetFormatPrOptionsPtr
+ nonDefault SheetFormatPrOptions
}{
- {new(excelize.BaseColWidth), excelize.BaseColWidth(1.0)},
- {new(excelize.DefaultColWidth), excelize.DefaultColWidth(1.0)},
- {new(excelize.DefaultRowHeight), excelize.DefaultRowHeight(1.0)},
- {new(excelize.CustomHeight), excelize.CustomHeight(true)},
- {new(excelize.ZeroHeight), excelize.ZeroHeight(true)},
- {new(excelize.ThickTop), excelize.ThickTop(true)},
- {new(excelize.ThickBottom), excelize.ThickBottom(true)},
+ {new(BaseColWidth), BaseColWidth(1.0)},
+ {new(DefaultColWidth), DefaultColWidth(1.0)},
+ {new(DefaultRowHeight), DefaultRowHeight(1.0)},
+ {new(CustomHeight), CustomHeight(true)},
+ {new(ZeroHeight), ZeroHeight(true)},
+ {new(ThickTop), ThickTop(true)},
+ {new(ThickBottom), ThickBottom(true)},
}
for i, test := range testData {
@@ -392,11 +390,11 @@ func TestSheetFormatPrOptions(t *testing.T) {
opt := test.nonDefault
t.Logf("option %T", opt)
- def := deepcopy.Copy(test.container).(excelize.SheetFormatPrOptionsPtr)
- val1 := deepcopy.Copy(def).(excelize.SheetFormatPrOptionsPtr)
- val2 := deepcopy.Copy(def).(excelize.SheetFormatPrOptionsPtr)
+ def := deepcopy.Copy(test.container).(SheetFormatPrOptionsPtr)
+ val1 := deepcopy.Copy(def).(SheetFormatPrOptionsPtr)
+ val2 := deepcopy.Copy(def).(SheetFormatPrOptionsPtr)
- f := excelize.NewFile()
+ f := NewFile()
// Get the default value
assert.NoError(t, f.GetSheetFormatPr(sheet, def), opt)
// Get again and check
@@ -434,26 +432,26 @@ func TestSheetFormatPrOptions(t *testing.T) {
}
func TestSetSheetFormatPr(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.GetSheetFormatPr("Sheet1"))
f.Sheet["xl/worksheets/sheet1.xml"].SheetFormatPr = nil
- assert.NoError(t, f.SetSheetFormatPr("Sheet1", excelize.BaseColWidth(1.0)))
+ assert.NoError(t, f.SetSheetFormatPr("Sheet1", BaseColWidth(1.0)))
// Test set formatting properties on not exists worksheet.
assert.EqualError(t, f.SetSheetFormatPr("SheetN"), "sheet SheetN is not exist")
}
func TestGetSheetFormatPr(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
assert.NoError(t, f.GetSheetFormatPr("Sheet1"))
f.Sheet["xl/worksheets/sheet1.xml"].SheetFormatPr = nil
var (
- baseColWidth excelize.BaseColWidth
- defaultColWidth excelize.DefaultColWidth
- defaultRowHeight excelize.DefaultRowHeight
- customHeight excelize.CustomHeight
- zeroHeight excelize.ZeroHeight
- thickTop excelize.ThickTop
- thickBottom excelize.ThickBottom
+ baseColWidth BaseColWidth
+ defaultColWidth DefaultColWidth
+ defaultRowHeight DefaultRowHeight
+ customHeight CustomHeight
+ zeroHeight ZeroHeight
+ thickTop ThickTop
+ thickBottom ThickBottom
)
assert.NoError(t, f.GetSheetFormatPr("Sheet1",
&baseColWidth,
diff --git a/sheetview_test.go b/sheetview_test.go
index d999875..e323e23 100644
--- a/sheetview_test.go
+++ b/sheetview_test.go
@@ -1,60 +1,58 @@
-package excelize_test
+package excelize
import (
"fmt"
"testing"
"github.com/stretchr/testify/assert"
-
- "github.com/360EntSecGroup-Skylar/excelize/v2"
)
-var _ = []excelize.SheetViewOption{
- excelize.DefaultGridColor(true),
- excelize.RightToLeft(false),
- excelize.ShowFormulas(false),
- excelize.ShowGridLines(true),
- excelize.ShowRowColHeaders(true),
- excelize.TopLeftCell("B2"),
+var _ = []SheetViewOption{
+ DefaultGridColor(true),
+ RightToLeft(false),
+ ShowFormulas(false),
+ ShowGridLines(true),
+ ShowRowColHeaders(true),
+ TopLeftCell("B2"),
// SheetViewOptionPtr are also SheetViewOption
- new(excelize.DefaultGridColor),
- new(excelize.RightToLeft),
- new(excelize.ShowFormulas),
- new(excelize.ShowGridLines),
- new(excelize.ShowRowColHeaders),
- new(excelize.TopLeftCell),
+ new(DefaultGridColor),
+ new(RightToLeft),
+ new(ShowFormulas),
+ new(ShowGridLines),
+ new(ShowRowColHeaders),
+ new(TopLeftCell),
}
-var _ = []excelize.SheetViewOptionPtr{
- (*excelize.DefaultGridColor)(nil),
- (*excelize.RightToLeft)(nil),
- (*excelize.ShowFormulas)(nil),
- (*excelize.ShowGridLines)(nil),
- (*excelize.ShowRowColHeaders)(nil),
- (*excelize.TopLeftCell)(nil),
+var _ = []SheetViewOptionPtr{
+ (*DefaultGridColor)(nil),
+ (*RightToLeft)(nil),
+ (*ShowFormulas)(nil),
+ (*ShowGridLines)(nil),
+ (*ShowRowColHeaders)(nil),
+ (*TopLeftCell)(nil),
}
func ExampleFile_SetSheetViewOptions() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
if err := f.SetSheetViewOptions(sheet, 0,
- excelize.DefaultGridColor(false),
- excelize.RightToLeft(false),
- excelize.ShowFormulas(true),
- excelize.ShowGridLines(true),
- excelize.ShowRowColHeaders(true),
- excelize.ZoomScale(80),
- excelize.TopLeftCell("C3"),
+ DefaultGridColor(false),
+ RightToLeft(false),
+ ShowFormulas(true),
+ ShowGridLines(true),
+ ShowRowColHeaders(true),
+ ZoomScale(80),
+ TopLeftCell("C3"),
); err != nil {
fmt.Println(err)
}
- var zoomScale excelize.ZoomScale
+ var zoomScale ZoomScale
fmt.Println("Default:")
fmt.Println("- zoomScale: 80")
- if err := f.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(500)); err != nil {
+ if err := f.SetSheetViewOptions(sheet, 0, ZoomScale(500)); err != nil {
fmt.Println(err)
}
@@ -65,7 +63,7 @@ func ExampleFile_SetSheetViewOptions() {
fmt.Println("Used out of range value:")
fmt.Println("- zoomScale:", zoomScale)
- if err := f.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(123)); err != nil {
+ if err := f.SetSheetViewOptions(sheet, 0, ZoomScale(123)); err != nil {
fmt.Println(err)
}
@@ -87,18 +85,18 @@ func ExampleFile_SetSheetViewOptions() {
}
func ExampleFile_GetSheetViewOptions() {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
var (
- defaultGridColor excelize.DefaultGridColor
- rightToLeft excelize.RightToLeft
- showFormulas excelize.ShowFormulas
- showGridLines excelize.ShowGridLines
- showZeros excelize.ShowZeros
- showRowColHeaders excelize.ShowRowColHeaders
- zoomScale excelize.ZoomScale
- topLeftCell excelize.TopLeftCell
+ defaultGridColor DefaultGridColor
+ rightToLeft RightToLeft
+ showFormulas ShowFormulas
+ showGridLines ShowGridLines
+ showZeros ShowZeros
+ showRowColHeaders ShowRowColHeaders
+ zoomScale ZoomScale
+ topLeftCell TopLeftCell
)
if err := f.GetSheetViewOptions(sheet, 0,
@@ -124,7 +122,7 @@ func ExampleFile_GetSheetViewOptions() {
fmt.Println("- zoomScale:", zoomScale)
fmt.Println("- topLeftCell:", `"`+topLeftCell+`"`)
- if err := f.SetSheetViewOptions(sheet, 0, excelize.TopLeftCell("B2")); err != nil {
+ if err := f.SetSheetViewOptions(sheet, 0, TopLeftCell("B2")); err != nil {
fmt.Println(err)
}
@@ -132,7 +130,7 @@ func ExampleFile_GetSheetViewOptions() {
fmt.Println(err)
}
- if err := f.SetSheetViewOptions(sheet, 0, excelize.ShowGridLines(false)); err != nil {
+ if err := f.SetSheetViewOptions(sheet, 0, ShowGridLines(false)); err != nil {
fmt.Println(err)
}
@@ -140,7 +138,7 @@ func ExampleFile_GetSheetViewOptions() {
fmt.Println(err)
}
- if err := f.SetSheetViewOptions(sheet, 0, excelize.ShowZeros(false)); err != nil {
+ if err := f.SetSheetViewOptions(sheet, 0, ShowZeros(false)); err != nil {
fmt.Println(err)
}
@@ -170,7 +168,7 @@ func ExampleFile_GetSheetViewOptions() {
}
func TestSheetViewOptionsErrors(t *testing.T) {
- f := excelize.NewFile()
+ f := NewFile()
const sheet = "Sheet1"
assert.NoError(t, f.GetSheetViewOptions(sheet, 0))
diff --git a/styles.go b/styles.go
index 14bcecc..d4d0468 100644
--- a/styles.go
+++ b/styles.go
@@ -21,6 +21,7 @@ import (
"log"
"math"
"reflect"
+ "regexp"
"strconv"
"strings"
)
@@ -755,7 +756,7 @@ var currencyNumFmt = map[int]string{
// builtInNumFmtFunc defined the format conversion functions map. Partial format
// code doesn't support currently and will return original string.
-var builtInNumFmtFunc = map[int]func(i int, v string) string{
+var builtInNumFmtFunc = map[int]func(v string, format string) string{
0: formatToString,
1: formatToInt,
2: formatToFloat,
@@ -847,14 +848,14 @@ var criteriaType = map[string]string{
// formatToString provides a function to return original string by given
// built-in number formats code and cell string.
-func formatToString(i int, v string) string {
+func formatToString(v string, format string) string {
return v
}
// formatToInt provides a function to convert original string to integer
// format as string type by given built-in number formats code and cell
// string.
-func formatToInt(i int, v string) string {
+func formatToInt(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -865,7 +866,7 @@ func formatToInt(i int, v string) string {
// formatToFloat provides a function to convert original string to float
// format as string type by given built-in number formats code and cell
// string.
-func formatToFloat(i int, v string) string {
+func formatToFloat(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -875,7 +876,7 @@ func formatToFloat(i int, v string) string {
// formatToA provides a function to convert original string to special format
// as string type by given built-in number formats code and cell string.
-func formatToA(i int, v string) string {
+func formatToA(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -890,7 +891,7 @@ func formatToA(i int, v string) string {
// formatToB provides a function to convert original string to special format
// as string type by given built-in number formats code and cell string.
-func formatToB(i int, v string) string {
+func formatToB(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -903,7 +904,7 @@ func formatToB(i int, v string) string {
// formatToC provides a function to convert original string to special format
// as string type by given built-in number formats code and cell string.
-func formatToC(i int, v string) string {
+func formatToC(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -914,7 +915,7 @@ func formatToC(i int, v string) string {
// formatToD provides a function to convert original string to special format
// as string type by given built-in number formats code and cell string.
-func formatToD(i int, v string) string {
+func formatToD(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -925,7 +926,7 @@ func formatToD(i int, v string) string {
// formatToE provides a function to convert original string to special format
// as string type by given built-in number formats code and cell string.
-func formatToE(i int, v string) string {
+func formatToE(v string, format string) string {
f, err := strconv.ParseFloat(v, 64)
if err != nil {
return v
@@ -933,6 +934,8 @@ func formatToE(i int, v string) string {
return fmt.Sprintf("%.e", f)
}
+var dateTimeFormatsCache = map[string]string{}
+
// parseTime provides a function to returns a string parsed using time.Time.
// Replace Excel placeholders with Go time placeholders. For example, replace
// yyyy with 2006. These are in a specific order, due to the fact that m is
@@ -944,15 +947,46 @@ func formatToE(i int, v string) string {
// arbitrary characters unused in Excel Date formats, and then at the end,
// turn them to what they should actually be. Based off:
// http://www.ozgrid.com/Excel/CustomFormats.htm
-func parseTime(i int, v string) string {
- f, err := strconv.ParseFloat(v, 64)
+func parseTime(v string, format string) string {
+ var (
+ f float64
+ err error
+ goFmt string
+ )
+ f, err = strconv.ParseFloat(v, 64)
if err != nil {
return v
}
val := timeFromExcelTime(f, false)
- format := builtInNumFmt[i]
+
+ if format == "" {
+ return v
+ }
+
+ goFmt, found := dateTimeFormatsCache[format]
+ if found {
+ return val.Format(goFmt)
+ }
+
+ goFmt = format
+
+ if strings.Contains(goFmt, "[") {
+ var re = regexp.MustCompile(`\[.+\]`)
+ goFmt = re.ReplaceAllLiteralString(goFmt, "")
+ }
+
+ // use only first variant
+ if strings.Contains(goFmt, ";") {
+ goFmt = goFmt[:strings.IndexByte(goFmt, ';')]
+ }
replacements := []struct{ xltime, gotime string }{
+ {"YYYY", "2006"},
+ {"YY", "06"},
+ {"MM", "01"},
+ {"M", "1"},
+ {"DD", "02"},
+ {"D", "2"},
{"yyyy", "2006"},
{"yy", "06"},
{"mmmm", "%%%%"},
@@ -962,38 +996,59 @@ func parseTime(i int, v string) string {
{"mmm", "Jan"},
{"mmss", "0405"},
{"ss", "05"},
+ {"s", "5"},
{"mm:", "04:"},
{":mm", ":04"},
+ {"m:", "4:"},
+ {":m", ":4"},
{"mm", "01"},
{"am/pm", "pm"},
{"m/", "1/"},
{"%%%%", "January"},
{"&&&&", "Monday"},
}
+
+ replacementsGlobal := []struct{ xltime, gotime string }{
+ {"\\-", "-"},
+ {"\\ ", " "},
+ {"\\.", "."},
+ {"\\", ""},
+ }
// It is the presence of the "am/pm" indicator that determines if this is
// a 12 hour or 24 hours time format, not the number of 'h' characters.
if is12HourTime(format) {
- format = strings.Replace(format, "hh", "03", 1)
- format = strings.Replace(format, "h", "3", 1)
+ goFmt = strings.Replace(goFmt, "hh", "3", 1)
+ goFmt = strings.Replace(goFmt, "h", "3", 1)
+ goFmt = strings.Replace(goFmt, "HH", "3", 1)
+ goFmt = strings.Replace(goFmt, "H", "3", 1)
} else {
- format = strings.Replace(format, "hh", "15", 1)
- format = strings.Replace(format, "h", "15", 1)
+ goFmt = strings.Replace(goFmt, "hh", "15", 1)
+ goFmt = strings.Replace(goFmt, "h", "3", 1)
+ goFmt = strings.Replace(goFmt, "HH", "15", 1)
+ goFmt = strings.Replace(goFmt, "H", "3", 1)
}
+
for _, repl := range replacements {
- format = strings.Replace(format, repl.xltime, repl.gotime, 1)
+ goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, 1)
+ }
+ for _, repl := range replacementsGlobal {
+ goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, -1)
}
// If the hour is optional, strip it out, along with the possible dangling
// colon that would remain.
if val.Hour() < 1 {
- format = strings.Replace(format, "]:", "]", 1)
- format = strings.Replace(format, "[03]", "", 1)
- format = strings.Replace(format, "[3]", "", 1)
- format = strings.Replace(format, "[15]", "", 1)
+ goFmt = strings.Replace(goFmt, "]:", "]", 1)
+ goFmt = strings.Replace(goFmt, "[03]", "", 1)
+ goFmt = strings.Replace(goFmt, "[3]", "", 1)
+ goFmt = strings.Replace(goFmt, "[15]", "", 1)
} else {
- format = strings.Replace(format, "[3]", "3", 1)
- format = strings.Replace(format, "[15]", "15", 1)
+ goFmt = strings.Replace(goFmt, "[3]", "3", 1)
+ goFmt = strings.Replace(goFmt, "[15]", "15", 1)
}
- return val.Format(format)
+
+ dateTimeFormatsCache[format] = goFmt
+
+ return val.Format(goFmt)
}
// is12HourTime checks whether an Excel time format string is a 12 hours form.
@@ -2226,6 +2281,7 @@ func newNumFmt(styleSheet *xlsxStyleSheet, style *Style) int {
// setCustomNumFmt provides a function to set custom number format code.
func setCustomNumFmt(styleSheet *xlsxStyleSheet, style *Style) int {
nf := xlsxNumFmt{FormatCode: *style.CustomNumFmt}
+
if styleSheet.NumFmts != nil {
nf.NumFmtID = styleSheet.NumFmts.NumFmt[len(styleSheet.NumFmts.NumFmt)-1].NumFmtID + 1
styleSheet.NumFmts.NumFmt = append(styleSheet.NumFmts.NumFmt, &nf)
diff --git a/styles_test.go b/styles_test.go
index b68365b..8ce26a4 100644
--- a/styles_test.go
+++ b/styles_test.go
@@ -201,10 +201,44 @@ func TestNewStyle(t *testing.T) {
assert.NoError(t, err)
_, err = f.NewStyle(Style{})
assert.EqualError(t, err, "invalid parameter type")
+
_, err = f.NewStyle(&Style{Font: &Font{Family: strings.Repeat("s", MaxFontFamilyLength+1)}})
assert.EqualError(t, err, "the length of the font family name must be smaller than or equal to 31")
_, err = f.NewStyle(&Style{Font: &Font{Size: MaxFontSize + 1}})
assert.EqualError(t, err, "font size must be between 1 and 409 points")
+
+ // new numeric custom style
+ fmt := "####;####"
+ f.Styles.NumFmts = nil
+ styleID, err = f.NewStyle(&Style{
+ CustomNumFmt: &fmt,
+ })
+ assert.NoError(t, err)
+ assert.Equal(t, 2, styleID)
+
+ assert.NotNil(t, f.Styles)
+ assert.NotNil(t, f.Styles.CellXfs)
+ assert.NotNil(t, f.Styles.CellXfs.Xf)
+
+ nf := f.Styles.CellXfs.Xf[styleID]
+ assert.Equal(t, 164, *nf.NumFmtID)
+
+ // new currency custom style
+ f.Styles.NumFmts = nil
+ styleID, err = f.NewStyle(&Style{
+ Lang: "ko-kr",
+ NumFmt: 32, // must not be in currencyNumFmt
+
+ })
+ assert.NoError(t, err)
+ assert.Equal(t, 3, styleID)
+
+ assert.NotNil(t, f.Styles)
+ assert.NotNil(t, f.Styles.CellXfs)
+ assert.NotNil(t, f.Styles.CellXfs.Xf)
+
+ nf = f.Styles.CellXfs.Xf[styleID]
+ assert.Equal(t, 32, *nf.NumFmtID)
}
func TestGetDefaultFont(t *testing.T) {
@@ -250,3 +284,15 @@ func TestGetStyleID(t *testing.T) {
func TestGetFillID(t *testing.T) {
assert.Equal(t, -1, getFillID(NewFile().stylesReader(), &Style{Fill: Fill{Type: "unknown"}}))
}
+
+func TestParseTime(t *testing.T) {
+ assert.Equal(t, "2019", parseTime("43528", "YYYY"))
+ assert.Equal(t, "43528", parseTime("43528", ""))
+
+ assert.Equal(t, "2019-03-04 05:05:42", parseTime("43528.2123", "YYYY-MM-DD hh:mm:ss"))
+ assert.Equal(t, "2019-03-04 05:05:42", parseTime("43528.2123", "YYYY-MM-DD hh:mm:ss;YYYY-MM-DD hh:mm:ss"))
+ assert.Equal(t, "3/4/2019 5:5:42", parseTime("43528.2123", "M/D/YYYY h:m:s"))
+ assert.Equal(t, "March", parseTime("43528", "mmmm"))
+ assert.Equal(t, "Monday", parseTime("43528", "dddd"))
+
+}
--
cgit v1.2.1