Aspose.Cells ASP.NET trouble setting cell formatting to text

Aspose.Cells Product Family
1

What is the easiest way to set the cell formatting to text for a whole worksheet?

Dim s As New Style

s.Number = 49

I can do the following code, but it only allows adding the style to an individual cell, not the collection of cells...

The reason I need to change the formatting of the cell to text is because the .ExportDataTable does not properly assign the correct data type to some of the columns...

Is there a better way of doing this?

2

Hi,


Well, you may loop through your desired columns in the worksheet and apply your desired style formatting accordingly.

See the sample code segment below.

Sample code:

’ Create a worksheet object and obtain the first sheet.
Dim sheet As Worksheet = wb.Worksheets(0)
’ Define the style object.
Dim style As Style
’Define the styleflag object
Dim styleflag As StyleFlag

’ Loop through all the columns in the worksheet to format them.
For i As Integer = 0 To 255

style = sheet.Cells.Columns(CByte(i)).Style
style.Number = 49
styleflag = New StyleFlag()
styleflag.NumberFormat = True
sheet.Cells.Columns(CByte(i)).ApplyStyle(style, styleflag)

Next i
’…

Thank you.

3

Hi,

Another approach is to create a range of all the cells in your desired worksheet and then apply a style. It will save you from iterating through your columns.

Please see the source.xls files, I have applied a style having background color with a number pattern to all the cells. Please see the output file and screenshot.

I have used the latest version:
Aspose.Cells for .NET v7.0.2.0

C#


string path = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(path);

Worksheet worksheet = workbook.Worksheets[0];


string lastCell = worksheet.Cells.LastCell.Name;


Range rng = worksheet.Cells.CreateRange(“A1:” + lastCell);


Style st = workbook.CreateStyle();

//set the number pattern

st.Number = 2;


//set the background color to yellow

st.Pattern = BackgroundType.Solid;

st.ForegroundColor = Color.Yellow;


StyleFlag flag = new StyleFlag();

flag.All = true;


rng.ApplyStyle(st, flag);


workbook.Save(path + “.out.xlsx”);

Screenshot: