Setting column to be Number Format

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

Setting column to be Number Format

Postby conorpboland » Wed Aug 30, 2017 1:38 pm

Hi, I am loading in a CSV file and creating a pivot table in a worksheet.
When I write the data back to excel the columns are storing 1's and 0's as type text so when I pivot and SUM them they don't add together as they are of type text. In the excel file I can select Convert to Number and they are good.

How can I get the cells to be set to Number Format through spire. I have Set NumberFormat = "0" but to no avail.
conorpboland
 
Posts: 2
Joined: Wed Aug 30, 2017 9:34 am

Re: Setting column to be Number Format

Postby Betsy.jiang » Thu Aug 31, 2017 2:55 am

Dear conorpboland,

Thanks for your inquiry.
Please add following code.
Code: Select all
range.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

If there is any question, please let me know.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1424
Joined: Tue Sep 06, 2016 8:30 am

Re: Setting column to be Number Format

Postby Betsy.jiang » Fri Sep 01, 2017 8:04 am

Dear conorpboland,

Did you add the code I provided ? Did it help you solve your issue ?
Could you please give us some feedback ?

Thanks,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1424
Joined: Tue Sep 06, 2016 8:30 am

Re: Setting column to be Number Format

Postby conorpboland » Mon Sep 04, 2017 12:46 pm

Hi Betsy, No this did not work as the columns still see the 1's adn 0's as text and would not sum them for me.
As a work around I had to loop the sheet and convert which is not ideal

Another question you may be able to help with. Can I dynamically select the end of a range of data if I dont know what the last column is?

I have been trying something with endcell but it is not working. D46 could be any number of rows

// chart.DataRange = sheet2.Range["A2:D46"];
chart.DataRange = sheet2.Range["A2:D2"].EndCell;

CellRange dataRange = sheet.Range["A1:I10000"];
// dataRange.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

foreach(CellRange r in dataRange)
{
if (r.Cells[0].Value == "1")
{
r.Cells[0].NumberValue = 1;
r.Cells[0].NumberFormat = "0";
}
if (r.Cells[0].Value == "0")
{
r.Cells[0].NumberValue = 0;
r.Cells[0].NumberFormat = "0";
}
}
conorpboland
 
Posts: 2
Joined: Wed Aug 30, 2017 9:34 am

Re: Setting column to be Number Format

Postby Betsy.jiang » Tue Sep 05, 2017 2:07 am

Dear conorpboland,

Thanks for your feedback.
Sorry that the code didn't work on your side. Yet I confirmed the situation you mentioned could work on my side, maybe there was something different between us caused that issue. Please try to use the latest Spire.XLS Pack Hotfix Version:7.12.76. And below is my entire code and the attachment is my input document.
Code: Select all
            Workbook workbook = new Workbook();
            //load a csv file
            workbook.LoadFromFile(@"F:\sample document\Data.csv", ",", 1, 1);
            Worksheet sheet = workbook.Worksheets[0];
            //get the all range on the sheet
            CellRange range = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn];           
            range.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
            //also need to set the number format
            range.NumberFormat = "0";
            //this formula could work
            sheet.Range["A20"].Formula = "=SUM(B2+B3)";
            //save to excel file
            workbook.SaveToFile("11501.xlsx", ExcelVersion.Version2010);


For your second question, please refer to the code below.
Code: Select all
            CellRange lastCell= range.Cells[range.CellsCount - 1];

Hope it helps.

Sincerely,
Betsy
E-iceblue support team
Attachments
SampleFiles.zip
(5.18 KiB) Downloaded 2 times
User avatar
Betsy.jiang
 
Posts: 1424
Joined: Tue Sep 06, 2016 8:30 am

Re: Setting column to be Number Format

Postby Betsy.jiang » Fri Sep 08, 2017 8:17 am

Dear conorpboland,

Did you test the code I provided ? Did it help you solve your issue ?

Thanks,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1424
Joined: Tue Sep 06, 2016 8:30 am


Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 0 guests