summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--errors.go6
-rw-r--r--excelize_test.go10
-rw-r--r--rows_test.go63
-rw-r--r--stream.go7
-rw-r--r--stream_test.go7
-rw-r--r--styles.go79
6 files changed, 158 insertions, 14 deletions
diff --git a/errors.go b/errors.go
index fd896a6..6a23a2e 100644
--- a/errors.go
+++ b/errors.go
@@ -87,6 +87,12 @@ func newDecodeXMLError(err error) error {
return fmt.Errorf("xml decode error: %s", err)
}
+// newStreamSetRowError defined the error message on the stream writer
+// receiving the non-ascending row number.
+func newStreamSetRowError(row int) error {
+ return fmt.Errorf("row %d has already been written", row)
+}
+
var (
// ErrStreamSetColWidth defined the error message on set column width in
// stream writing mode.
diff --git a/excelize_test.go b/excelize_test.go
index 12d155d..4c86d56 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -721,10 +721,10 @@ func TestSetCellStyleNumberFormat(t *testing.T) {
data := []int{0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49}
value := []string{"37947.7500001", "-37947.7500001", "0.007", "2.1", "String"}
expected := [][]string{
- {"37947.7500001", "37948", "37947.75", "37948", "37947.75", "3794775%", "3794775.00%", "3.79E+04", "37947.7500001", "37947.7500001", "11-22-03", "22-Nov-03", "22-Nov", "Nov-03", "6:00 pm", "6:00:00 pm", "18:00", "18:00:00", "11/22/03 18:00", "37947", "37947", "37947.75", "37947.75", "37947.7500001", "37947.7500001", "37947.7500001", "37947.7500001", "00:00", "910746:00:00", "37947.7500001", "3.79E+04", "37947.7500001"},
- {"-37947.7500001", "-37948", "-37947.75", "-37948", "-37947.75", "-3794775%", "-3794775.00%", "-3.79E+04", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "(37947)", "(37947)", "(-37947.75)", "(-37947.75)", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-3.79E+04", "-37947.7500001"},
- {"0.007", "0", "0.01", "0", "0.01", "1%", "0.70%", "7.00E-03", "0.007", "0.007", "12-30-99", "30-Dec-99", "30-Dec", "Dec-99", "0:10 am", "0:10:04 am", "00:10", "00:10:04", "12/30/99 00:10", "0", "0", "0.01", "0.01", "0.007", "0.007", "0.007", "0.007", "10:04", "0:10:04", "0.007", "7.00E-03", "0.007"},
- {"2.1", "2", "2.10", "2", "2.10", "210%", "210.00%", "2.10E+00", "2.1", "2.1", "01-01-00", "1-Jan-00", "1-Jan", "Jan-00", "2:24 am", "2:24:00 am", "02:24", "02:24:00", "1/1/00 02:24", "2", "2", "2.10", "2.10", "2.1", "2.1", "2.1", "2.1", "24:00", "50:24:00", "2.1", "2.10E+00", "2.1"},
+ {"37947.7500001", "37948", "37947.75", "37,948", "37947.75", "3794775%", "3794775.00%", "3.79E+04", "37947.7500001", "37947.7500001", "11-22-03", "22-Nov-03", "22-Nov", "Nov-03", "6:00 pm", "6:00:00 pm", "18:00", "18:00:00", "11/22/03 18:00", "37,948 ", "37,948 ", "37,947.75 ", "37,947.75 ", "37947.7500001", "37947.7500001", "37947.7500001", "37947.7500001", "00:00", "910746:00:00", "37947.7500001", "3.79E+04", "37947.7500001"},
+ {"-37947.7500001", "-37948", "-37947.75", "-37,948", "-37947.75", "-3794775%", "-3794775.00%", "-3.79E+04", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "(37,948)", "(37,948)", "(37,947.75)", "(37,947.75)", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-37947.7500001", "-3.79E+04", "-37947.7500001"},
+ {"0.007", "0", "0.01", "0", "0.01", "1%", "0.70%", "7.00E-03", "0.007", "0.007", "12-30-99", "30-Dec-99", "30-Dec", "Dec-99", "0:10 am", "0:10:04 am", "00:10", "00:10:04", "12/30/99 00:10", "0 ", "0 ", "0.01 ", "0.01 ", "0.007", "0.007", "0.007", "0.007", "10:04", "0:10:04", "0.007", "7.00E-03", "0.007"},
+ {"2.1", "2", "2.10", "2", "2.10", "210%", "210.00%", "2.10E+00", "2.1", "2.1", "01-01-00", "1-Jan-00", "1-Jan", "Jan-00", "2:24 am", "2:24:00 am", "02:24", "02:24:00", "1/1/00 02:24", "2 ", "2 ", "2.10 ", "2.10 ", "2.1", "2.1", "2.1", "2.1", "24:00", "50:24:00", "2.1", "2.10E+00", "2.1"},
{"String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String"},
}
@@ -744,7 +744,7 @@ func TestSetCellStyleNumberFormat(t *testing.T) {
}
assert.NoError(t, f.SetCellStyle("Sheet2", c, c, style))
cellValue, err := f.GetCellValue("Sheet2", c)
- assert.Equal(t, expected[i][k], cellValue)
+ assert.Equal(t, expected[i][k], cellValue, "Sheet2!"+c, i, k)
assert.NoError(t, err)
}
}
diff --git a/rows_test.go b/rows_test.go
index 74c4d25..76823ba 100644
--- a/rows_test.go
+++ b/rows_test.go
@@ -993,6 +993,68 @@ func TestNumberFormats(t *testing.T) {
}
assert.Equal(t, []string{"", "200", "450", "200", "510", "315", "127", "89", "348", "53", "37"}, cells[3])
assert.NoError(t, f.Close())
+
+ f = NewFile()
+ numFmt1, err := f.NewStyle(&Style{NumFmt: 1})
+ assert.NoError(t, err)
+ numFmt2, err := f.NewStyle(&Style{NumFmt: 2})
+ assert.NoError(t, err)
+ numFmt3, err := f.NewStyle(&Style{NumFmt: 3})
+ assert.NoError(t, err)
+ numFmt9, err := f.NewStyle(&Style{NumFmt: 9})
+ assert.NoError(t, err)
+ numFmt10, err := f.NewStyle(&Style{NumFmt: 10})
+ assert.NoError(t, err)
+ numFmt37, err := f.NewStyle(&Style{NumFmt: 37})
+ assert.NoError(t, err)
+ numFmt38, err := f.NewStyle(&Style{NumFmt: 38})
+ assert.NoError(t, err)
+ numFmt39, err := f.NewStyle(&Style{NumFmt: 39})
+ assert.NoError(t, err)
+ numFmt40, err := f.NewStyle(&Style{NumFmt: 40})
+ assert.NoError(t, err)
+ for _, cases := range [][]interface{}{
+ {"A1", numFmt1, 8.8888666665555493e+19, "88888666665555500000"},
+ {"A2", numFmt1, 8.8888666665555487, "9"},
+ {"A3", numFmt2, 8.8888666665555493e+19, "88888666665555500000.00"},
+ {"A4", numFmt2, 8.8888666665555487, "8.89"},
+ {"A5", numFmt3, 8.8888666665555493e+19, "88,888,666,665,555,500,000"},
+ {"A6", numFmt3, 8.8888666665555487, "9"},
+ {"A7", numFmt3, 123, "123"},
+ {"A8", numFmt3, -1234, "-1,234"},
+ {"A9", numFmt9, 8.8888666665555493e+19, "8888866666555550000000%"},
+ {"A10", numFmt9, -8.8888666665555493e+19, "-8888866666555550000000%"},
+ {"A11", numFmt9, 8.8888666665555487, "889%"},
+ {"A12", numFmt9, -8.8888666665555487, "-889%"},
+ {"A13", numFmt10, 8.8888666665555493e+19, "8888866666555550000000.00%"},
+ {"A14", numFmt10, -8.8888666665555493e+19, "-8888866666555550000000.00%"},
+ {"A15", numFmt10, 8.8888666665555487, "888.89%"},
+ {"A16", numFmt10, -8.8888666665555487, "-888.89%"},
+ {"A17", numFmt37, 8.8888666665555493e+19, "88,888,666,665,555,500,000 "},
+ {"A18", numFmt37, -8.8888666665555493e+19, "(88,888,666,665,555,500,000)"},
+ {"A19", numFmt37, 8.8888666665555487, "9 "},
+ {"A20", numFmt37, -8.8888666665555487, "(9)"},
+ {"A21", numFmt38, 8.8888666665555493e+19, "88,888,666,665,555,500,000 "},
+ {"A22", numFmt38, -8.8888666665555493e+19, "(88,888,666,665,555,500,000)"},
+ {"A23", numFmt38, 8.8888666665555487, "9 "},
+ {"A24", numFmt38, -8.8888666665555487, "(9)"},
+ {"A25", numFmt39, 8.8888666665555493e+19, "88,888,666,665,555,500,000.00 "},
+ {"A26", numFmt39, -8.8888666665555493e+19, "(88,888,666,665,555,500,000.00)"},
+ {"A27", numFmt39, 8.8888666665555487, "8.89 "},
+ {"A28", numFmt39, -8.8888666665555487, "(8.89)"},
+ {"A29", numFmt40, 8.8888666665555493e+19, "88,888,666,665,555,500,000.00 "},
+ {"A30", numFmt40, -8.8888666665555493e+19, "(88,888,666,665,555,500,000.00)"},
+ {"A31", numFmt40, 8.8888666665555487, "8.89 "},
+ {"A32", numFmt40, -8.8888666665555487, "(8.89)"},
+ } {
+ cell, styleID, value, expected := cases[0].(string), cases[1].(int), cases[2], cases[3].(string)
+ f.SetCellStyle("Sheet1", cell, cell, styleID)
+ assert.NoError(t, f.SetCellValue("Sheet1", cell, value))
+ result, err := f.GetCellValue("Sheet1", cell)
+ assert.NoError(t, err)
+ assert.Equal(t, expected, result)
+ }
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestNumberFormats.xlsx")))
}
func BenchmarkRows(b *testing.B) {
@@ -1016,6 +1078,7 @@ func BenchmarkRows(b *testing.B) {
}
}
+// trimSliceSpace trim continually blank element in the tail of slice.
func trimSliceSpace(s []string) []string {
for {
if len(s) > 0 && s[len(s)-1] == "" {
diff --git a/stream.go b/stream.go
index 62470b5..44d8eb7 100644
--- a/stream.go
+++ b/stream.go
@@ -32,6 +32,7 @@ type StreamWriter struct {
cols strings.Builder
worksheet *xlsxWorksheet
rawData bufferedWriter
+ rows int
mergeCellsCount int
mergeCells strings.Builder
tableParts string
@@ -40,7 +41,7 @@ type StreamWriter struct {
// NewStreamWriter return stream writer struct by given worksheet name for
// generate new worksheet with large amounts of data. Note that after set
// rows, you must call the 'Flush' method to end the streaming writing process
-// and ensure that the order of line numbers is ascending, the normal mode
+// and ensure that the order of row numbers is ascending, the normal mode
// functions and stream mode functions can't be work mixed to writing data on
// the worksheets, you can't get cell value when in-memory chunks data over
// 16MB. For example, set data for worksheet of size 102400 rows x 50 columns
@@ -358,6 +359,10 @@ func (sw *StreamWriter) SetRow(cell string, values []interface{}, opts ...RowOpt
if err != nil {
return err
}
+ if row <= sw.rows {
+ return newStreamSetRowError(row)
+ }
+ sw.rows = row
sw.writeSheetData()
options := parseRowOpts(opts...)
attrs, err := options.marshalAttrs()
diff --git a/stream_test.go b/stream_test.go
index c399d63..a4a0590 100644
--- a/stream_test.go
+++ b/stream_test.go
@@ -61,7 +61,7 @@ func TestStreamWriter(t *testing.T) {
}}))
assert.NoError(t, streamWriter.SetRow("A6", []interface{}{time.Now()}))
assert.NoError(t, streamWriter.SetRow("A7", nil, RowOpts{Height: 20, Hidden: true, StyleID: styleID}))
- assert.EqualError(t, streamWriter.SetRow("A7", nil, RowOpts{Height: MaxRowHeight + 1}), ErrMaxRowHeight.Error())
+ assert.EqualError(t, streamWriter.SetRow("A8", nil, RowOpts{Height: MaxRowHeight + 1}), ErrMaxRowHeight.Error())
for rowID := 10; rowID <= 51200; rowID++ {
row := make([]interface{}, 50)
@@ -77,7 +77,7 @@ func TestStreamWriter(t *testing.T) {
assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx")))
// Test set cell column overflow.
- assert.ErrorIs(t, streamWriter.SetRow("XFD1", []interface{}{"A", "B", "C"}), ErrColumnNumber)
+ assert.ErrorIs(t, streamWriter.SetRow("XFD51201", []interface{}{"A", "B", "C"}), ErrColumnNumber)
// Test close temporary file error.
file = NewFile()
@@ -226,6 +226,9 @@ func TestStreamSetRow(t *testing.T) {
streamWriter, err := file.NewStreamWriter("Sheet1")
assert.NoError(t, err)
assert.EqualError(t, streamWriter.SetRow("A", []interface{}{}), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
+ // Test set row with non-ascending row number
+ assert.NoError(t, streamWriter.SetRow("A1", []interface{}{}))
+ assert.EqualError(t, streamWriter.SetRow("A1", []interface{}{}), newStreamSetRowError(1).Error())
}
func TestStreamSetRowNilValues(t *testing.T) {
diff --git a/styles.go b/styles.go
index b4d0a53..bbb21dc 100644
--- a/styles.go
+++ b/styles.go
@@ -758,7 +758,7 @@ var builtInNumFmtFunc = map[int]func(v, format string, date1904 bool) string{
0: format,
1: formatToInt,
2: formatToFloat,
- 3: formatToInt,
+ 3: formatToIntSeparator,
4: formatToFloat,
9: formatToC,
10: formatToD,
@@ -869,6 +869,26 @@ var operatorType = map[string]string{
"greaterThanOrEqual": "greater than or equal to",
}
+// printCommaSep format number with thousands separator.
+func printCommaSep(text string) string {
+ var (
+ target strings.Builder
+ subStr = strings.Split(text, ".")
+ length = len(subStr[0])
+ )
+ for i := 0; i < length; i++ {
+ if i > 0 && (length-i)%3 == 0 {
+ target.WriteString(",")
+ }
+ target.WriteString(string(text[i]))
+ }
+ if len(subStr) == 2 {
+ target.WriteString(".")
+ target.WriteString(subStr[1])
+ }
+ return target.String()
+}
+
// formatToInt provides a function to convert original string to integer
// format as string type by given built-in number formats code and cell
// string.
@@ -880,7 +900,7 @@ func formatToInt(v, format string, date1904 bool) string {
if err != nil {
return v
}
- return fmt.Sprintf("%d", int64(math.Round(f)))
+ return strconv.FormatFloat(math.Round(f), 'f', -1, 64)
}
// formatToFloat provides a function to convert original string to float
@@ -894,9 +914,27 @@ func formatToFloat(v, format string, date1904 bool) string {
if err != nil {
return v
}
+ source := strconv.FormatFloat(f, 'f', -1, 64)
+ if !strings.Contains(source, ".") {
+ return source + ".00"
+ }
return fmt.Sprintf("%.2f", f)
}
+// formatToIntSeparator provides a function to convert original string to
+// integer format as string type by given built-in number formats code and cell
+// string.
+func formatToIntSeparator(v, format string, date1904 bool) string {
+ if strings.Contains(v, "_") {
+ return v
+ }
+ f, err := strconv.ParseFloat(v, 64)
+ if err != nil {
+ return v
+ }
+ return printCommaSep(strconv.FormatFloat(math.Round(f), 'f', -1, 64))
+}
+
// 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(v, format string, date1904 bool) string {
@@ -907,10 +945,17 @@ func formatToA(v, format string, date1904 bool) string {
if err != nil {
return v
}
+ var target strings.Builder
if f < 0 {
- return fmt.Sprintf("(%d)", int(math.Abs(f)))
+ target.WriteString("(")
}
- return fmt.Sprintf("%d", int(f))
+ target.WriteString(printCommaSep(strconv.FormatFloat(math.Abs(math.Round(f)), 'f', -1, 64)))
+ if f < 0 {
+ target.WriteString(")")
+ } else {
+ target.WriteString(" ")
+ }
+ return target.String()
}
// formatToB provides a function to convert original string to special format
@@ -923,10 +968,24 @@ func formatToB(v, format string, date1904 bool) string {
if err != nil {
return v
}
+ var target strings.Builder
if f < 0 {
- return fmt.Sprintf("(%.2f)", f)
+ target.WriteString("(")
}
- return fmt.Sprintf("%.2f", f)
+ source := strconv.FormatFloat(math.Abs(f), 'f', -1, 64)
+ var text string
+ if !strings.Contains(source, ".") {
+ text = printCommaSep(source + ".00")
+ } else {
+ text = printCommaSep(fmt.Sprintf("%.2f", math.Abs(f)))
+ }
+ target.WriteString(text)
+ if f < 0 {
+ target.WriteString(")")
+ } else {
+ target.WriteString(" ")
+ }
+ return target.String()
}
// formatToC provides a function to convert original string to special format
@@ -939,6 +998,10 @@ func formatToC(v, format string, date1904 bool) string {
if err != nil {
return v
}
+ source := strconv.FormatFloat(f, 'f', -1, 64)
+ if !strings.Contains(source, ".") {
+ return source + "00%"
+ }
return fmt.Sprintf("%.f%%", f*100)
}
@@ -952,6 +1015,10 @@ func formatToD(v, format string, date1904 bool) string {
if err != nil {
return v
}
+ source := strconv.FormatFloat(f, 'f', -1, 64)
+ if !strings.Contains(source, ".") {
+ return source + "00.00%"
+ }
return fmt.Sprintf("%.2f%%", f*100)
}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 08:58:25 +0000