StreamWriter to support writing ints, floats, etc. to the cells

[Orionsg]

The SetRow function of the StreamWriter struct has the row values to be written as interface{}, i.e.:
func (sw *StreamWriter) SetRow(axis string, values []interface{}, opts ...RowOpts) error

Thus one can write any datatypes to the row as long as they are converted to interface{}. I would have expected that when writing an int, float or time value that the SetRow function would write to the cell similarly to writing a cell using the SetCellInt and SetCellFloat functions since the underlying type of the interface would be available. Instead, the values are all written as text in the general format. Thus, numbers will appear as "Number formatted as text" rather than actual numbers for which a number format can be set using the SetColStyle function.

Is there a way to write numbers properly using the StreamWriter or alternatively a fast way to get the texts converted to numbers, or does one instead of the StreamWriter function have to use the SetCell.. functions to get the values stored as proper numbers?

The idea of the StreamWriter is to load data fast to a sheet. However, the need for having proper numbers stored in the sheets is not less for larger datasets -- probably rather the other way around.

[xuri]

Thanks for your issue, as you says, the value paramter in the SetRow function of the StreamWriter is []interface{}, so it can be accept mutipal hybrid data type values, and the value will be write in the cell as is original data type. Reference the example of the stream writer.

[Orionsg]

I am working with a use case where the number and type of columns is not static but decided at runtime. Thus, I pack the values in an interface{} slice and call SetRow with that slice. However, the values written seemed to be text in the "General" format rather than integers, decimals, etc. The code is dealing with a lot of other aspects like formatting, so I may be wrong.

Once I have my project completed, I will work out a simple example to check whether there actually is an issue or not and let you know.

In the meanwhile, I have just used SetCellValue to write the cells and that works excellently. But I guess for large datasets the stream writer may still be preferable.

[Orionsg]

Below is some code which demonstrates the problem that I encountered. However, while I originally thought that the data from the interface slice was not written correctly to the cells according to the underlying datatype, I now see that it is formatting using SetColStyle which does not achieve the result that I expected, i.e. it seems that the styles are not applied to the cells in the columns.

The code below creates a new excel file test.xlsx which contains a sheet with five columns each of a different datatype, with three rows. Even though the col styles are applied it does not seem to affect the cells in the columns, i.e. they still have the "General" format. However, I do notice that numbers are right-adjusted, which would indicate that numbers are written correctly as such to Excel.

Go version: 1.17.1 windows/amd64
Excelize version: 2.4.1

Code:

package main

import (
	"fmt"
	"log"
	"os"
	"time"

	"github.com/xuri/excelize/v2"
)

const (
	s_fi = "test.xlsx"
	s_sh = "Sheet1"
)

type data_tp struct {
	fld_str   string
	fld_int   int
	fld_float float64
	fld_bool  bool
	fld_time  time.Time
}

func main() {

	sl_data := []data_tp{
		{"str_val1", 1000, 11000.0, true, time.Now().AddDate(-10, -1, 0)},
		{"str_val2", 2000, 22000.0, true, time.Now().AddDate(-5, -2, 3)},
		{"str_val3", 3000, 33000.0, true, time.Now().AddDate(0, -4, 5)},
	}

	wb := excelize.NewFile()

	// populate the sheet with the stream writer
	sw, err := wb.NewStreamWriter(s_sh)
	if err != nil {
		fmt.Println("Error:", err)
		return
	}
	sl_intf := make([]interface{}, 5)

	for ix, data := range sl_data {
		sl_intf[0] = data.fld_str
		sl_intf[1] = data.fld_int
		sl_intf[2] = data.fld_float
		sl_intf[3] = data.fld_bool
		sl_intf[4] = data.fld_time
		cell, _ := excelize.CoordinatesToCellName(1, ix+1)
		if err := sw.SetRow(cell, sl_intf); err != nil {
			fmt.Printf("Error: %s, row num: %d\n", err, ix+1)
			return
		}
	}
	if err := sw.Flush(); err != nil {
		fmt.Printf("Error flush: %v", err)
		return
	}

	// create styles
	s_sty := ""
	s_sty = "{\"number_format\": 3}"
	i_sty_int, err := wb.NewStyle(s_sty)
	if err != nil {
		fmt.Println("Error:", err)
		return
	}
	s_sty = "{\"number_format\": 4}"
	i_sty_fl, err := wb.NewStyle(s_sty)
	if err != nil {
		fmt.Println("Error:", err)
		return
	}
	s_sty = "{\"custom_number_format\": \"yyyy-mm-dd;@\"}"
	i_sty_dt, err := wb.NewStyle(s_sty)
	if err != nil {
		fmt.Println("Error:", err)
		return
	}
	s_sty = "{\"custom_number_format\": \"@\"}"
	i_sty_gen, err := wb.NewStyle(s_sty)
	if err != nil {
		fmt.Println("Error:", err)
		return
	}

	// apply styles to the columns
	if err = wb.SetColStyle(s_sh, "A", i_sty_gen); err != nil {
		fmt.Println("Error:", err)
		return
	}
	if err = wb.SetColStyle(s_sh, "B", i_sty_int); err != nil {
		fmt.Println("Error:", err)
		return
	}
	if err = wb.SetColStyle(s_sh, "C", i_sty_fl); err != nil {
		fmt.Println("Error:", err)
		return
	}
	if err = wb.SetColStyle(s_sh, "D", i_sty_gen); err != nil {
		fmt.Println("Error:", err)
		return
	}
	if err = wb.SetColStyle(s_sh, "E", i_sty_dt); err != nil {
		fmt.Println("Error:", err)
		return
	}

	if fileExists(s_fi) {
		err := os.Remove(s_fi)
		if err != nil {
			log.Fatal("Failed to remove file:", s_fi, err)
		}
	}
	if err := wb.SaveAs("text.xlsx"); err != nil {
		fmt.Println("Error:", err)
		return
	}
}

func fileExists(s_file string) bool {
	info, err := os.Stat(s_file)
	if os.IsNotExist(err) {
		return false
	}
	if info.IsDir() {
		log.Fatal("Error:", s_file, "is a directory")
	}
	return true
}

[xuri]

Sorry for my late reply. This issue is similar to issues #725, #777, and #826. SetColStyle is not a streaming API. So it won't work with a streaming API. Please don't use common API and stream API mixed, using the wb.SetSheetRow(s_sh, cell, &sl_intf) instead of stream writer. I'll close this issue. If you have any questions, please let me know.