GSheet can't use Tables when xlsx file generated by Excelize

[simulot]

Description I'm using excelize to produce read and write workbook around an entreprise application. I'm using Table feature to get a convient sheet with banded rows. This works on Excel and GSheet (with limitations). GSheet is mandatory because many of users don't have Excel.

Generated files are opened by GSheet, by all Table definitions are removed. In another hand, the same file is correctly handled by Excel (2016). Tables are rendered. Saving the same file from excel makes GSheet accepting Tables and render them as "Alternating colors".

Steps to reproduce the issue:

1. Create the table f.AddTable(sheet, "A1","D6", { "table_name": "Report", "table_style": "TableStyleMedium1", "show_first_column": 0, "show_last_column": 0, "show_row_stripes": 1, "show_column_stripes": 0 })
2. Save the file into a google drive

Describe the results you received:

Describe the results you expected:

Color schemas isn't good (GSheet issue), but headers are present.

Output of go version:

go1.19 windows/amd64

Excelize version or commit ID:

github.com/xuri/excelize/v2 v2.6.0

Environment details (OS, Microsoft Excel™ version, physical, etc.): Windows 10 Google Sheet current Excel 2016

[xuri]

Thanks for your feedback. Please receive and check the error returned by the AddTable function, you need to use the right data type for each field in the table properties JSON like this:

{
    "table_name": "Report",
    "table_style": "TableStyleMedium1",
    "show_first_column": false,
    "show_last_column": false,
    "show_row_stripes": true,
    "show_column_stripes": false
}

and it will be working as your expected in the Google Sheet.

[simulot]

You're right... I have compared table file inside the XLSX and found that booleans are stored as true/false in excelize, but as 0 and 1 in Excel.

Using 0 and 1 with standard release gives errors. So I have forked your code to do test if it was the cause problem. But's it doesn't change the problem. I forget to switch back to the official release before copying the code. Sorry.

Anyway, opening the file using excel, save it is enough to get it working in GSheet. This is easy, but its ruins the possibility to have a fully automatized process.

[xuri]

The booleans value storage in the XML is not the reason for this problem, I mean that you need to use the true or false in the show_first_column, show_last_column fields when you call the AddTable function of the Excelize. If you are using the 0 or 1, there is an error that would be returned, the table has not been added to the worksheet, so you can't see it when your upload and open the generated workbook on Google Sheet.

[simulot]

Agreed, but this isn't the problem.

Here is the test code:

package main

import (
	"fmt"
	"os"

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

var (
	sample = [][]any{
		{"User", "Age", "Country", "Color"},
		{"John", 25, "Green"},
		{"Tom", 10, "Yellow"},
		{"Mary", 25, "Green"},
		{"Lin", 20},
		{"Ted", 20, "Red"},
	}
	sampleAbsTopLeft     = checkError(excelize.CoordinatesToCellName(1, 1, true))
	sampleAbsBottomRight = checkError(excelize.CoordinatesToCellName(len(sample[0]), len(sample), true))
)

func checkError[T any](v T, err error) T {
	if err != nil {
		exitOnError(err)
	}
	return v
}

func exitOnError(err error) {
	if err != nil {
		fmt.Println("Error:", err.Error())
		os.Exit(1)
	}
}

func main() {

	sheet := "Test1"
	f := excelize.NewFile()

	f.NewSheet(sheet)
	for r, row := range sample {
		for c, cell := range row {
			exitOnError(f.SetCellValue(sheet, checkError(excelize.CoordinatesToCellName(c+1, r+1)), cell))
		}
	}
	exitOnError(f.AddTable(sheet, sampleAbsTopLeft, sampleAbsBottomRight, fmt.Sprintf(`{
		"table_name": "_%s",
		"table_style": "TableStyleMedium12",
		"show_first_column": false,
		"show_last_column": false,
		"show_row_stripes": true,
		"show_column_stripes": false
	}`, "_"+sheet)))
	f.DeleteSheet("Sheet1")
	exitOnError(f.SaveAs(sheet + ".xlsx"))
}

Here the generated file.

Test1.xlsx

Place it into google drive and open it Now, open the original with excel, save it (just move the active cell), place it into google drive and open it again in google sheet.

[xuri]

After investigation, the reason for this issue was the same as #1244.

[xuri]

This issue has been fixed, please upgrade to the master branch code, and this patch will be released in the next version.

[simulot]

Thank you for this quick answer

I have tested the new version: The table loads with named ranges and data validation based on list or named range.

But I have found following:

• The style of the table (ex: TableStyleMedium12) is lost, the table is rendered without colors
• All conditional formatting is lost. (use vlookup over named ranges)

The loading and saving files in excel makes conditional formatting working.

[xuri]

Please upgrade to the master branch. I've tested with your provides code (which using the TableStyleMedium12 theme color for the table), and the table was in yellow theme when open the generated workbook in Google Sheet. There are no conditional formatting in this workbook. Could you show us a complete, standalone example program or reproducible demo?

[simulot]

With the following version;

require github.com/xuri/excelize/v2 v2.6.1-0.20220813032159-551fb8a9e4b0

I get following:

Simple sheet with conditional formatting:

Excel:

GSheet:

Same data with conditional formatting and a Table:

Excel:

GSheet:

The test code:

package main

import (
	"fmt"
	"os"

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

var (
	sample = [][]any{
		{"User", "Age", "Color"},
		{"John", 25, "Green"},
		{"Tom", "", "Yellow"},
		{"Mary", 25, "Green"},
		{"Lin", 20},
		{"Ted", 20, "Red"},
	}
	sampleAbsTopLeft     = checkError(excelize.CoordinatesToCellName(1, 1, true))
	sampleAbsBottomRight = checkError(excelize.CoordinatesToCellName(len(sample[0]), len(sample), true))
)

func checkError[T any](v T, err error) T {
	if err != nil {
		exitOnError(err)
	}
	return v
}

func exitOnError(err error) {
	if err != nil {
		fmt.Println("Error:", err.Error())
		os.Exit(1)
	}
}

func main() {
	test1()
	test2()
}

func test1() {
	sheet := "Test1"
	f := excelize.NewFile()

	f.NewSheet(sheet)
	for r, row := range sample {
		for c, cell := range row {
			exitOnError(f.SetCellValue(sheet, checkError(excelize.CoordinatesToCellName(c+1, r+1)), cell))
		}
	}
	format := checkError(f.NewConditionalStyle(`{
		"font":
		{
			"color": "#9A0511"
		},
		"fill":
		{
			"type": "pattern",
			"color": ["#FEC7CE"],
			"pattern": 1
		}
	}`))

	exitOnError(f.SetConditionalFormat(sheet, sampleAbsTopLeft+":"+sampleAbsBottomRight, fmt.Sprintf(`[
		{
			"type": "formula",
			"criteria": "=A1=\"\"",
			"format": %d
		}]`, format)))

	f.DeleteSheet("Sheet1")
	exitOnError(f.SaveAs(sheet + ".xlsx"))
}

func test2() {
	sheet := "Test2"
	f := excelize.NewFile()

	f.NewSheet(sheet)
	for r, row := range sample {
		for c, cell := range row {
			exitOnError(f.SetCellValue(sheet, checkError(excelize.CoordinatesToCellName(c+1, r+1)), cell))
		}
	}
	format := checkError(f.NewConditionalStyle(`{
		"font":
		{
			"color": "#9A0511"
		},
		"fill":
		{
			"type": "pattern",
			"color": ["#FEC7CE"],
			"pattern": 1
		}
	}`))

	exitOnError(f.SetConditionalFormat(sheet, sampleAbsTopLeft+":"+sampleAbsBottomRight, fmt.Sprintf(`[
		{
			"type": "formula",
			"criteria": "=A1=\"\"",
			"format": %d
		}]`, format)))

	exitOnError(f.AddTable(sheet, sampleAbsTopLeft, sampleAbsBottomRight, fmt.Sprintf(`{
		"table_name": "_%s",
		"table_style": "TableStyleMedium12",
		"show_first_column": false,
		"show_last_column": false,
		"show_row_stripes": true,
		"show_column_stripes": false
	}`, "_"+sheet)))

	f.DeleteSheet("Sheet1")
	exitOnError(f.SaveAs(sheet + ".xlsx"))
}

[xuri]

Please remove the equal symbol in the conditional formatting formula, using the "A1=\"\"" instead of "=A1=\"\"".

[simulot]

Correct. Sorry for the noise. Thanks for your help.