golang excelize how to copy the cell and it's format

Issue

I use github.com/xuri/excelize/v2 to process the excel file.

I append many sheets in many excel files into one sheet in one excel.

Below is the sample code.

    var mergedRows [][]string
    for _, f := range files {
        excelPath := folder + "/" + f.Name()
        rows := loadXlsx(excelPath, sheetName)
        for _, row := range rows[rowOffset:] {
            mergedRows = append(mergedRows, row)
        }
    }

    saveXlsx(aggregatedFilePath, sheetName, mergedRows, rowOffset)


...

func loadXlsx(xlsxPath string, sheetName string) [][]string {

    f, err := excelize.OpenFile(xlsxPath)
    if err != nil {
        log.Fatal(err)
    }

    defer func() {
        if err := f.Close(); err != nil{
            fmt.Println(err)
        }
    }()

    rows, err := f.GetRows(sheetName)
    if err != nil {
        log.Fatal(err)
    }

    return rows
}

func saveXlsx(path string, sheetName string, rows [][]string, rowOffset int) {

    f, err := excelize.OpenFile(path)
    if err != nil {
        log.Fatal(err)
    }

    defer func() {
        if err := f.Close(); err != nil{
            fmt.Println(err)
        }
    }()


    index := f.GetSheetIndex(sheetName)
    offset := 1
    sequence := 1
    for _, row := range rows{
        row[0] = strconv.Itoa(sequence)
        sequence = sequence + 1
        offset = offset + 1
        axis := "A" + strconv.Itoa(offset)
        f.SetSheetRow(sheetName, axis, &row)
    }

    for index, _ := range rows[0] {
        axis, _ := excelize.CoordinatesToCellName(index, 2)
        column, _ := excelize.ColumnNumberToName(index)
        styleId, _ := f.GetCellStyle(sheetName, axis)
        cellType, _ := f.GetCellType(sheetName, axis)
        fmt.Println(styleId)
        fmt.Println(cellType)
        f.SetColStyle(sheetName, column, styleId)
    }


    f.SetActiveSheet(index)
    if err := f.Save(); err != nil {
        fmt.Println(err)
    }

}

This works, except some data format issues. the number’s style is copyed, but not works; the date is copyed, but with wrong value.

  1. In the source file, there has some number with 2 decimal format and shows like 70.12, while in the output file the format is the same but shows like 70.119.

  2. In the source file, there has some date with Y/m/d format and shows like 2022/1/12, while in the output file the format is the same but shows like 01-12-22.

Solution

From the manual

func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error)

If the cell format can be applied to the value of the cell, the
applied value will be used, otherwise the original value will be used.

So in my question, rows, err := f.GetRows(sheetName) will copy the date and number value with format, not the original number. The formated value may be convert to non equal value.

The solution is just read the raw value with RawCellValue option true,

rows, err := f.GetRows(sheetName, excelize.Options{RawCellValue:true})

If the format is changed, just apply the style from the original file to the new file.

Answered By – LF00

Answer Checked By – Senaida (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.