SetColStyle not honoring given style

[SteveTalt]

Description
SetColStyle seems to be corrupting spreadsheets. It doesn't seem to matter what data is in the column or which column or sheet you use.

I created this simple code to re-produce it:

package main

import (
	"github.com/excelize"
)

// define globals

var xlfile = "testlocal.xlsx"
var xlsheetname = "test"

// main
func main() {
	var (
		file *excelize.File
		err  error
	)
	// open the file
	file, err = excelize.OpenFile(xlfile)
	errorhandler(err)

	err = file.SetColStyle(xlsheetname, "C", 7)
	errorhandler(err)
	file.Save()

}

func errorhandler(e error) {
	if e != nil {
		panic(e)
	}
}

Steps to reproduce the issue:

1. Create a spreadsheet named localtest.xlsx and a sheet named test
2. run this test code against it
3. open the spreadsheet and see it needs repair

Describe the results you received:

Repair Log:
`

Repair Result to testlocal5.xml

Errors were detected in file '/Users/stalt/Documents/go/src/setcoltest/testlocal.xlsx'

Repaired Records: Column information from /xl/worksheets/sheet1.xml part

`

Describe the results you expected:
I expect the column to be formated with

Output of go version:

$ go version
go version go1.10 darwin/amd64

Excelize version or commit ID:

commit id: 448f552

Environment details (OS, Microsoft Excel™ version, physical, etc.):
macOS Mojave 10.14.5
Excel for Mac 16.27 (19071500)

[SteveTalt]

Weirdly, if i do this:

style, err = file.NewStyle(`{"number_format": 7}`)
err = file.SetColStyle(xlsheetname, "C", style)

It doesn't corrupt it, but also doesn't format to number_format 7. It may be that format 7 doesn't exist.

[SteveTalt]

I'm expecting 7 to be:

based on this

[SteveTalt]

Created a custom style....doesn't break it...but also doesn't change the style:

style, err = f.NewStyle(`{"custom_number_format": "($#,##0.00_);($#,##0.00)"}`)
err = file.SetColStyle(xlsheetname, "C", style)

[SteveTalt]

For reference, it works fine using SetCellStyle with the same custom format. Just not SetColStyle.

And number format 7 doesn't work either way.

[mlh758]

You're getting the error with SetColStyle because the function expects the ID of a custom style already added to the sheet. If you just put a number in before adding a style with that ID then the generated relationship is incorrect. The way you have it in your later comments with NewStyle is the correct way to use it.

[SteveTalt]

Understood. BUT:

1. using new style with SetColStyle still does not work....it doesn't corrupt the file...but also doesn't format the column...it basically does nothing. It works with SetCellStyle...but not SetColStyle
2. I wasn't trying to use a custom style....i was trying to use an number_format that already existed. If it requires a custom_number_format it should say that in the documentation...it doesn't. There is a whole section in the documentation that is a table of number_format and styleIDs which thus makes it very unclear that you'd have to create your own style to use this function.
3. even if the function can't work without newstyle....it shouldn't corrupt the file as a result and should error out instead....no error is thrown when this happens.

I'll update my sample code with new style and setcolstyle and repost in a bit.

[mlh758]

I wasn't trying to use a custom style....i was trying to use an number_format that already existed.

Even so, you would have to use NewStyle to create a style in the document to apply to your cell. Anything that sets a style in this library takes the style ID for a style that already exists in the workbook. 7 isn't an existing style ID, it's one of the built in number formats that you can apply to a style you create before calling set style.

As for creating the style, they're touchy since you have to get the JSON just right, I look forward to something like #470 simplifying this whole interaction. Are you checking the error from NewStyle?

[SteveTalt]

Yes, I am checking for error in new style....and I know this new style works because in my real code when I apply it via setcellstyle it works. Here is an updated version:

package main

import (
	"github.com/excelize"
)

// define globals

var xlfile = "testlocal.xlsx"
var xlsheetname = "test"

// main
func main() {
	var (
		file  *excelize.File
		err   error
		style int
	)
	// open the file
	file, err = excelize.OpenFile(xlfile)
	errorhandler(err)

	// set the cost column to be the number format I want
	style, err = file.NewStyle(`{"custom_number_format": "#,##0.00;(#,##0.00)"}`)

	errorhandler(err)
	err = file.SetColStyle(xlsheetname, "C", style)
	errorhandler(err)
	file.Save()

}

func errorhandler(e error) {
	if e != nil {
		panic(e)
	}
}

[mlh758]

Could you change the title of this issue to something like "SetColStyle not honoring given style" or something? The repair thing ended up not being an issue and when we figure this out it will help people search for it later if they have a similar problem.

[SteveTalt]

Interestingly, if I change the style to this:

style, err = file.NewStyle({"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}})

it does the fill....but starts right below where my data is:

[mlh758]

I think we're missing 7 in the built-in table:
https://github.com/360EntSecGroup-Skylar/excelize/blob/master/styles.go#L29

If I add it to the format map, the xml generated properly creates a new style xfs with the number format set to 7, but the cells don't change.

The built in number format for 7 does work if I create the style and apply it to the cells instead of the column.

[mlh758]

You can use unzip file.xlsx to see what xml was generated. That can help troubleshoot what styles are created and applied.

[mlh758]

If I apply a style to a column in Excel I get both a column level style set:

<cols>
    <col min="1" max="1" width="10.83203125" style="1"/>
</cols>

and each row:

<row r="1" spans="1:1" x14ac:dyDescent="0.2">
    <c r="A1" s="1">
        <v>123</v>
    </c>
</row>

[SteveTalt]

Here is the styles XML file:

styles.xml.zip

This would have the gradient fill since that was the last style i saved to that column. I see it defined in there.

Here is the sheet xml:
sheet1.xml.zip

I see the col style and row style assigned (but they aren't the same). Looks like column style is 3 and row style is 2 if i'm reading that right.

column:

<cols>
   <col collapsed="false" hidden="false" max="3" min="3" style="3" width="9">
   </col>
 </cols>

Example Row:

    <row r="5" spans="1:3">
      <c r="A5" t="s">
        <v>6</v>
      </c>
      <c r="B5" t="s">
        <v>6</v>
      </c>
      <c r="C5" s="2">
        <v>1234.5</v>
      </c>
    </row>