Formatting not applied to range of cells

Aspose.Cells Product Family
1

Hello Folks,


We are evaluating Aspose.Cells for our export to excel requirements. Have few questions, hope you could help me.

First of all thank you guys for providing us a great tool to export our data to Pivot excel with out much hassle.

Note:-We are using ASP.NET 4.0(IIS-7.5)

1. We are not able to apply number(date/currency) format to range of cells. In the excel sheet it shows a warning “Number stored as text”, but if I right click on the cell and go to Format Cell, required number format is applied, but it wont reflect in the cell.(Please refer attached excel).
Code which apply this formatting:

foreach (var column in columnsToBeNumberFormatted)
{
var numberStyle = new Style {Number = 3};
var flag = new StyleFlag {NumberFormat = true};
var numberRange = dataWorksheet.Cells.CreateRange(3, column, dataTable.Rows.Count, 1);

numberRange.ApplyStyle(numberStyle, flag);

dataWorksheet.Cells.AddRange(numberRange);
}

2. Now the second question, How can we test the load Aspose puts on our webserver?. We use both normal excel and pivot excel in our application and we have a need to export around 7 million records with 25 columns. So we need to check how much load it puts on our server, if its too much load then we can deploy it in a separate server(or some other alternative to reduce the load).


2

Hi Anil,

Thanks for your posting and using Aspose.Cells.

1 )

Actually, the values 1112 entered in cells A4, A7 etc are strings not numbers, therefore even if you will format them as number, they will not change. You should convert these strings into numbers so that formatting could be applied.

Please see the following sample code. It reads your Asset+overview.xlsx file and convert string values to numeric values. I have attached the output xlsx file for your reference. As you can see in the output file, all values 1112 have been formatted as 1,112.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Asset+overview.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells.ConvertStringToNumericValue();


workbook.Save(“output.xlsx”);


Similarly, if you will enter some numeric value as a string, it will not be formatted as number.

e.g Cell.PutValue("1234"); now 1234 will be entered as string and formatting will not be applied on it.

But, if you enter it like Cell.PutValue("1234", true); then Aspose.Cells will convert this string to number before entering in cells and formatting will be applied.

Similarly, if you enter it like Cell.PutValue(1234); then this is not string but it is a number so formatting will be applied on it.

2 )

Aspose.Cells is a very light weight component and it does not take much system resources and consume time while entering thousands of records. You can create some console application and test it in a standalone environment.

As an example, we have tested it by entering 1 million rows in 16 columns and it took almost 2.5 minutes on Windows 7 - 64 bit with 8 GB RAM.

Please check this post for your reference.

( https://forum.aspose.com/t/78535 )
3

Hi Shakeel,


Thanks for your quick reply. This would help.

Regards,
Anil