Free Support Forum -

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

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?


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.


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


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”);