Handling range- number format and horizontal alignment in Aspose.Excel

Aspose.Cells Product Family
1

Hi,
I am in the process of porting over some VB code that was using MS Excel and is now using Aspose.Excel. I am having some difficulty converting the following so I was hoping you could point me in the right direction:


Dim xlSheet AS Excel.WorksheetWith xlSheet

'Set Decimal place formatting

.Range(.Cells(3, 11), .Cells(rowNum, 11)).NumberFormat = priceFormat

.Range(.Cells(3, 12), .Cells(rowNum, 17)).NumberFormat = amountFormat

With .Range(.Cells(3, 19), .Cells(rowNum, 20))

.NumberFormat = DATE_FORMAT

.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft

End WithEnd With


I think the same can be achieved in Aspose.Excel by importing Aspose.Excel and declaring “Dim xlSheet as Worksheet”.

What I am trying to do is use the:
xlsheet.Cells.CreateRange function
Style.Number attribute
and Style.HorizontalAlignment = TextAlignmentType.Left

In order to achieve the same effect of setting number formats and alignments over a range, but I’m not too sure how Aspose’s range works, could you tell me what would deliver equivalent functionality in Aspose to the code snippet above?
thanks


(On a side note my code never looks right when I copy it into this forum! It appears fine for me but when I preview the post it gets a bit bunched up like the 2 End With statements in this example, no matter how many lines I put between them they still show up as you see them now!)
2

In addition to this query…
Is it correct to say that the Style.ShrinkToFit attribute is equivalent to MS Excel’s Columns.AutoFit()?

Also I have learnt a bit more about Aspose’s range, could you tell me is the following (taken from earlier post) a correct equivalent?
In MS Excel:
'Set Decimal place formatting
.Range(.Cells(3, 11), .Cells(rowNum,11).NumberFormat = priceFormat

In Aspose:
'Set Decimal place formatting
.Cells.CreateRange(3, 11, rowNum, 1).Style.Number = priceFormat
<?xml:namespace prefix = o ns = “urnTongue Tiedchemas-microsoft-comSurprisefficeSurpriseffice” /><oStick out tongue>

</oStick out tongue>

3

The following is the equivalent code to your sample code:

Dim ExcelObject as Excel = new Excel()
Dim xlsSheet as Worksheet = ExcelObject.Worksheets(0)
Dim cells as Cells = xlsSheet.Cells

Dim Style1 as Style = ExcelObject.Styles(ExcelObject.Styles.Add())
Style1.Number = priceFormat

Dim Range1 as Range = cells.CreateRange(3, 11, rowNum - 2, 1)
Range1.Style = Style1

Dim Style2 as Style = ExcelObject.Styles(ExcelObject.Styles.Add())
Style2.Number = DATE_FORMAT
Style2.HorizontalAlignment = TextAlignmentType.Left

Dim Range2 as Range = cells.CreateRange(3, 19, rowNum - 2, 2)
Range2.Style = Style2

4

ShrinkToFit attribute is equilalent to “Shrink to fit” option when you format a cell. If you want to autofit a column, please check Worksheet.AutoFitColumn method at
Worksheet.AutoFitColumn

5

Great thanks,
I’m just wondering why is it rowNum - 2?
I’m guessing its - 1 to count for the fact index starts at 0 but why - 2?

“Dim Range2 as Range = cells.CreateRange(3, 19, rowNum - 2, 2)”

6

One more question…

That autofit method does the business but I also need to set specific column width like so:
MS Excel:

.Columns(19).ColumnWidth = COL_WIDTH_TRADEDATE

I’ve looked for an aspose equivalent in Column, Columns and Style but I can’t find anything, is there a way to set column width like this in Aspose?

7

The third paramter is number of rows and fourth is number of columns. In your sample MS Excel code, your rowNum is 5, so the equivalent code is:

Dim Range2 as Range = cells.CreateRange(3, 19, 5 - 3 + 1, 2)

8

Please check Cells.SetColumnWidth method at http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.SetColumnWidth.html