How to keep number as input text exactly

[zhenlei1970]

Hello,

I'd like to keep the cell value as input exactly. For example, when I set the value "01", I do not want it to be changed as "1" or "1.00". I tried to set the NumberFormat to "@" but it does not work. The NumberFormat was changed to "0.00" after the Value was set. And the value changed to "1.00". Sample code is below:

var workbook = new Workbook();
workbook.LoadFromFile("test1.xlsx");

var sheet = workbook.Worksheets[0];
sheet.Range[1, 1].NumberFormat = "@"; //I'd like to keep the text
sheet.Range[1, 1].Value = "01";
Console.WriteLine(sheet.Range[1, 1].NumberFormat);
workbook.SaveToFile("ntest1.xlsx");
Console.ReadKey();

Regards

Lei

[rachel.lei]

Hello,

Thanks for your inquiry.
I did an initial test with the latest Spire.XLS Pack(Hotfix) Version:10.9.16 but found the NumberFormat "@" worked well, as shown below. If you are using an older version, please try again with the latest version. If the issue still occurs after trying, please tell us your OS information (e.g. Windows 7, 64bit) and Region settings(e.g. China/Chinese) for further investigation. Thanks in advance.

My_output.png (31.9 KiB) Viewed 909 times

Sincerely,
Rachel
E-iceblue support team

[zhenlei1970]

Thanks for your reply. I'll try the new version.

[rachel.lei]

Hello,

You are welcome.
If there are other questions, just feel free to contact us.
Wish you all the best!

Sincerely,
Rachel
E-iceblue support team

[zhenlei1970]

Hello Rachel,

I installed the lastest version 10.9.16. It works in simple case. However there is still the issue when I set data from database. Finally I found the reason. If there are blank characters in the string, there will be the issue.

Code: Select all

            var workbook = new Workbook();
            var sheet = workbook.ActiveSheet;// workbook.Worksheets[0];
            sheet.Range[1, 1].NumberFormat = "@"; //I'd like to keep the text
            sheet.Range[1, 1].Value = "01";
            Console.WriteLine(sheet.Range[1, 1].NumberFormat);
            sheet.Range[2, 1].NumberFormat = "@"; //I'd like to keep the text
            sheet.Range[2, 1].Value = " 01 ";
            Console.WriteLine(sheet.Range[2, 1].NumberFormat);
            workbook.SaveToFile("ntest2.xlsx");
            Console.ReadKey();
 

You can see if the value set to " 01 ", the NumberFormat will be changed to "0.00".

Currently I can solve the issue by removing the blanks.

Regards

Lei

[rachel.lei]

Hello,

Thanks for your feedback.
Please use the "Text" property instead to keep the text, as shown below. If there are any questions, please feel free to let us know.

Code: Select all

            var workbook = new Workbook();
            var sheet = workbook.ActiveSheet;// workbook.Worksheets[0];
            sheet.Range[2, 1].NumberFormat = "@"; //I'd like to keep the text
            sheet.Range[2, 1].Text = " 01 ";
            Console.WriteLine(sheet.Range[2, 1].NumberFormat);
            workbook.SaveToFile("ntest2.xlsx");
            Console.ReadKey();

Sincerely,
Rachel
E-iceblue support team

[zhenlei1970]

Hello Rachel,

Thanks for your reply.

It works when I change "Value" to "Text". Could you please tell me the difference between the two properties? When should we use "Text" or "Value"? Currently we're using "Value" in our project which was translated from NPOI. Can I change "Value" to "Text" safely?

Regards

Lei

[rachel.lei]

Hello,

Thanks for your feedback.
I learned from our Dev team that when using the "Value" property, our product will infer the data type according to the input value, and set it to the default number format for that type. Thus, if you want to keep the cell value as input exactly, I'd suggest you use the "Text" property. If you have further questions in this regard, just feel free to write back.

Sincerely,
Rachel
E-iceblue support team

[zhenlei1970]

Thanks for your reply. I'll do more tests on the properties.

[rachel.lei]

Hello,

You are welcome.
Feel free to contact us if you need further assistance.

Sincerely,
Rachel
E-iceblue support team