Free Support Forum - aspose.com

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

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

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>

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

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 http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Worksheet.AutoFitColumn.html

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

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?

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)

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