How can I convert cell style to string format ?

[bluntdel]

As you know, excel will convert "101,102" to "101102" as number format?So how can i get the original value "101,102". Or how can I convert all my cells to string format ? I don't need number_format.

[drewhbestbuddy]

Are you reading 'Number' values from an Excel file? Excelize will read 'Number' values like "101,102" as "101102" and you can convert this to your desired format. To convert "12100122" to "12,100,122", you can use 'humanize' package from this page on Stack Overflow.

Do you want to insert values using excelize and have Excel display commas? If so you can way you can set these values as strings, so that the comma formatting will carry over to Excel.

// will be formatted as 'Text' in Excel, displayed as "101,102"
f.SetCellValue("Sheet1", "A1", "101,102") 

// will be formatted as 'General' in Excel, displayed as "101,102,103"
val := 101102103
f.SetCellValue("Sheet1", "A2", humanize.FormatInteger("#,###.", val)) 

The above values will be read in as "101,102" and "101,102,102", respectively, in excelize,.

[bluntdel]

Thanks for your recommended humanize package.@drewhbestbuddy
But I'm trying to read values from user's file,how can I know it is number format or string format.
Some of them will pass number,others may pass string.So if i get "101102",i can't judge it is "101,102" or real "101102". I should have different ways to handle them.So if I can set col's values as string format,it will be easy.

My Excelize vesion is 1.4.1

[drewhbestbuddy]

I'm not finding how to retrieve the cell value type, or set a cell type excelize. It looks like the type is stored in xlsxC.T, which is in xmlWorksheet.go, but there isn't a function that exposes this type. A function could be added to retrieve the cell type, though it might not be required depending on what you want to do.

You could call SetCellStr(sheet, axis, value string), which sets xlsxC.T to the "str" type, though I don't think it will help in your case, since you are reading values from Excel first. If you read in a cell type 'Number' with "101,102", it would be read in as "101102" and calling SetCellStr would set the value to "101102" formatted as 'Text' (with Excel telling you it's a number is formatted as text).

If the reason to retrieve the type is to make sure the values are parsed correctly, I think this is still possible without knowing what the type is in Excel.

Is the desired behavior to parse the values from the user correctly? What are the types of values expected, and what are the desired values? For instance, if you the only difference is numbers that could be "101102" and "101,102", you could strip out (or add) commas to the number after it is read from excelize.

[bluntdel]

Exactly,the only problem is I can not know what its real type in the Excel.
Anyway, sincerely thanks for your reply.

[drewhbestbuddy]

No problem.

Adding a function like this would allow you to retrieve the xlsxC.T type.

// GetCellType provides a function to get excel cell type from cell.
//
// type,err := f.GetCellType("Sheet1", "A1")
//
func (f *File) GetCellType(sheet, axis string) (string, error) {
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return "", err
	}
	cellData, _, _, err := f.prepareCell(xlsx, sheet, axis)
	if err != nil {
		return "", err
	}

	return cellData.T, nil
}

Though I noticed excelize only assigns this value for SetCellStr and SetCellBool functions. The other set functions leave this blank: cellData.T = "". Not sure if excelize always sets this type when reading the excel file.

[jetking]

I have this problem, too..

[miracle-xiang]

I have this problem, too..

me too...

how do you handle it？

[xuri]

Sorry for the late reply. Excelize library support gets cell data type by the GetCellType function since v2.5.0 (reference issues #417, #520, and #1375). Both types CellTypeUnset and CellTypeNumber are numeric cells. In addition, this library will be supporting the get number format code by the GetStyle function in the next released version v2.8.0 (reference issue #314), and more number format codes will be supported in this version (reference issue #1199). I've closed this issue, if you have any questions, please let me know, and you can reopen this anytime.