Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Jul 21, 2021 4:41 pm

Hey, (Spire.XLS - 9.2.16.19046 as part of the free spire office)

I am using freespire office (xls) to write values to specific cells in a spreadsheet.

I the spreadsheet that is opened has the cells formatted to "Text" but when I set the value
Code: Select all
 Worksheet.Range("C22").value = "75.00"


it converts the cell to a number format and strips the .00

if I set the format to "0.00"

Code: Select all
WS.Range[Cell].NumberFormat = "0.00";


it shows the formatting however the value in the cell is still in the stripped state.

The only way to seem to get the cell value to retain the .00 is to set specifically to the Text formatting...

but I can't seem to find a way to do that with freespire office, and I can't seem to prevent the setting of the cell value from changing the format.

I have attached a screen shot that hopefully conveys the issue.

Template Settings.png
The template used, showing the cell is set to Text format before changing value with free spire.
Template Settings.png (38.48 KiB) Viewed 3269 times


After setting the value programatically.png
After Setting the value programmatically with free spire
After setting the value programatically.png (76.45 KiB) Viewed 3269 times

Thandius
 
Posts: 11
Joined: Tue Jun 16, 2020 2:56 pm

Thu Jul 22, 2021 5:59 am

Hello,

Thanks for your inquiry.
You can refer to the following code to achieve a similar effect.
Code: Select all
            //worksheet.Range["C22"].Value = "75.00";
            worksheet.Range["C22"].Text = "75.00";
            worksheet.Range["C22"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

If this is not you want, please provide your input file and your desired output for further investigation. Thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Jul 22, 2021 12:37 pm

PERFECT!!

using your format the following provides the functionality I need. Appreciate the response!!

Code: Select all
           
            WS.Range["R63"].Text = Charge.ToString("F");
            WS.Range["R63"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

            WS.Range["V63"].Text = Paid.ToString("F");
            WS.Range["V63"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

            WS.Range["Y63"].Text = (Charge - Paid).ToString("F");
            WS.Range["Y63"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
        }

Thandius
 
Posts: 11
Joined: Tue Jun 16, 2020 2:56 pm

Fri Jul 23, 2021 1:13 am

Hello,

You are welcome.
If you encounter any issues related to our products in the future, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS