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 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

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: 3099
Joined: Tue Sep 06, 2016 8:30 am

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: 3099
Joined: Tue Sep 06, 2016 8:30 am

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

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 292 times
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

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: 3099
Joined: Tue Sep 06, 2016 8:30 am

Thu Dec 27, 2018 1:31 pm

Hi Betsy,

I use the excel to html conversion code but in the code didn't convert red color value in excel to html its only covert number,I want color also please help me out

Thanks
Narayan,

3cm5
 
Posts: 2
Joined: Fri Dec 21, 2018 7:31 am

Fri Dec 28, 2018 2:47 am

Hi Narayan,

Thanks for your inquiry.
To help us locate your issue accurately, please provide your input Excel file and the code you were using here or send it to us(support@e-iceblue.com) via email.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Jan 04, 2019 2:52 am

Hi Narayan,

Hope you are doing well.
How is your issue going now? Could you please give us some feedback at your convenience?

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Return to Spire.XLS