Hi,
I am having a problem with column count. I added a new worksheet to my excel and inserted some data. When I do the columns.count on that new added worksheet, it returns 0 as total column.
Thank you
Columns/Column are used to set style for columns. They are not used for count the columns of data.
To serve your need, I will add new RowCount/ColumnCount properties to return the count.
Thank you for your quick reply. Actually the problem happened when I use autofitcolumn function. Then I did columns.count, it showed me only 2 columns in the current worksheet.
The error message is shown below. The current worksheet has 23 columns, but aspose recognizes as 2 only. Looks like I am autofit some columns that out of range.
Run Test: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.Collections.ArrayList.get_Item(Int32 index)
at Aspose.Excel.Style.a(Worksheets A_0, Int32 A_1)
at Aspose.Excel.Cell.get_Style()
at Aspose.Excel.aa.a(Cell A_0, Boolean A_1)
at Aspose.Excel.al.a(Worksheet A_0, Byte A_1, Int32 A_2, Int32 A_3)
at Aspose.Excel.Worksheet.AutoFitColumn(Int32 columnIndex)
at PandaReports.cfcPandaReport.cfcExcelReport.rpt_FormatAlignAndAutofitColumns(Worksheet& targetWorksheet, Int32 iBaseCol, Int32 iColCount, Int32 iMaxColSize) in C:\vss\Abyss\Reports\Panda Reports\Chatham.Panda.PandaReports\cfcExcelReport.vb:line 2453
How do you call the AutofitColumn method? Could you please post your code here? And do you use the latest hotfix v2.9.7?
targetWorksheet is the new aspose worksheet I added.
iBaseCol is the column where I would like to start formatting.
iColCount is the total number of columns I added to the new worksheet(targetWorksheet)
iMaxColsize is the constant value to ensure that column size is no larger than certain number.
At run time, it tells me total columns(iColCount) I added to targetWorkSheet is 23, but count columns returns 2 as targetWorkSheet total columns. iBaseCol is 4 when the time I pass the parameter
Error message shows as follows
Run Test: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.Collections.ArrayList.get_Item(Int32 index)
at Aspose.Excel.Style.a(Worksheets A_0, Int32 A_1)
at Aspose.Excel.Cell.get_Style()
at Aspose.Excel.y.a(Cell A_0, Boolean A_1)
at Aspose.Excel.ak.a(Worksheet A_0, Byte A_1, Int32 A_2, Int32 A_3)
at Aspose.Excel.Worksheet.AutoFitColumn(Int32 columnIndex)
Code:
Public Function rpt_FormatAlignAndAutofitColumns(ByRef targetWorksheet As Aspose.Excel.Worksheet, ByVal iBaseCol As Integer, ByVal iColCount As Integer, ByVal iMaxColSize As Integer)
'-----------------------------------------------------------------------------------
'Definition: Given an excel application, a start column, number of columns, and maximum
'column size, autofit each column, left justify the first column, and ensure that each
'column is less than or equal in width to the smallest column size.
'-----------------------------------------------------------------------------------
'align and autofit the column of cells, and check to
For i As Integer = 0 To iColCount - 1
targetWorksheet.AutoFitColumn(iBaseCol + i) ' *********where error occurs
'if its the first column; left justify it
If i = 0 Then
targetWorksheet.Cells.Columns(iBaseCol + i).Style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
End If
If targetWorksheet.Cells.GetColumnWidth(iBaseCol + i) > iMaxColSize Then
targetWorksheet.Cells.SetColumnWidth(iBaseCol + i, iMaxColSize)
End If
Next i
End Function
It looks like a style setting problem but I cannot find it in my machine. Are you using v2.9.7? If yes, could you please post more of your sample code here? And if you use a template, also please upload it here.
Thank you very much.
At foor loop, I call WriteEffectivenessTests method, by passing the aspose.excel, a new worksheet, and dataset
For Each iDataRow In iDataSet.GetItem(“ds_EffTestList”).iDataTable.Rows
workSheetCount = workSheetCount + 1
excelApp.Worksheets.Add()
Dim xls As Aspose.Excel.Worksheet = excelApp.Worksheets(workSheetCount)
WriteEffectivenessTests(excelApp, xls, iDataSet, iDataRow.Item(“et_test_id”))
next
Below are WriteEffectivenessTests function, where those parameters are passed here. The error occurs at where I highlighted with RED, rpt_FormatAlignAndAutofitColumns function is the one I posted yesterday. I am using 2.9.7, and the error message was also posted in the previous post.
Thank you
Dim xTestsAnchorcell As Aspose.Excel.Cell = targetWorkSheet.Cells(“E19”)
Dim xAnchorCell As Aspose.Excel.Cell = targetWorkSheet.Cells(“C7”)
Dim iBaseCell As Aspose.Excel.Cell
Dim vColCountHedge As Integer = 0, vColCountHdgItm As Integer = 0, vColCursor As Integer = 0
Dim vRowCount As Integer = 0, i As Integer = 0
If iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iDataTable.Rows.Count > 0 Then
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + 1, xTestsAnchorcell.Column + vColCountHedge)
vColCountHedge = iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iColumnCountOutputtedColumns
rpt_OutputColumnHeaders(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId), iBaseCell, RPTCOLUMNHEADERSFONTBOLD, RPTCOLUMNHEADERSFONTSIZE)
vRowCount = rpt_OutputDumpFromDataTable(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId), targetWorkSheet.Cells(iBaseCell.Row + 1, iBaseCell.Column))
iBaseCell = targetWorkSheet.Cells(iBaseCell.Row + 1, iBaseCell.Column)
rpt_FormatAllColumns(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId), iBaseCell)
vColCountHedge = iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iColumnCountOutputtedColumns
vColCursor = vColCountHedge
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
For i = 0 To vColCountHedge - 1
rpt_OutputSelectionAddAllBorders(targetWorkSheet.Cells(iBaseCell.Row - 1, iBaseCell.Column + i))
Next
iBaseCell = xTestsAnchorcell
iBaseCell.PutValue(“Hedge Payment Amounts”)
rpt_FormatASelectionFontProperties(iBaseCell, “Arial”, 12, True, False, False, 0)
rpt_FormatMergeCells(targetWorkSheet, iBaseCell, 0, vColCountHedge)
iBaseCell.Style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iDataTable.Rows.Count + 1, xTestsAnchorcell.Column)
rpt_OutputSelectionUnderline(targetWorkSheet, iBaseCell, vColCountHedge, 2)
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iDataTable.Rows.Count + 2, xTestsAnchorcell.Column)
rpt_SubtotalTableColumns(targetWorkSheet, iBaseCell, iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId))
End If
If iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId).iDataTable.Rows.Count > 0 Then
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + 1, xTestsAnchorcell.Column + vColCursor + 2)
vColCountHdgItm = iDataSet.GetItem(“ds_EffTestHedgeCashFlows_” & iTestId).iColumnCountOutputtedColumns
rpt_OutputColumnHeaders(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId), iBaseCell, RPTCOLUMNHEADERSFONTBOLD, RPTCOLUMNHEADERSFONTSIZE)
vRowCount = System.Math.Max(rpt_OutputDumpFromDataTable(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId), targetWorkSheet.Cells(iBaseCell.Row + 1, iBaseCell.Column)), vRowCount)
iBaseCell = targetWorkSheet.Cells(iBaseCell.Row + 1, iBaseCell.Column)
rpt_FormatAllColumns(targetWorkSheet, iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId), iBaseCell)
vColCountHdgItm = iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId).iColumnCountOutputtedColumns
vColCursor = vColCursor + vColCountHdgItm
For i = 0 To vColCountHdgItm - 1
rpt_OutputSelectionAddAllBorders(targetWorkSheet.Cells(iBaseCell.Row - 1, iBaseCell.Column + vColCountHdgItm - 1))
Next
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row, xTestsAnchorcell.Column + vColCountHedge + 2)
iBaseCell.PutValue(“100% Effective Payment Amounts”)
rpt_FormatASelectionFontProperties(iBaseCell, “Arial”, 12, True, False, False, 0)
rpt_FormatMergeCells(targetWorkSheet, iBaseCell, 0, vColCountHdgItm)
iBaseCell.Style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId).iDataTable.Rows.Count + 1, xTestsAnchorcell.Column)
iBaseCell = targetWorkSheet.Cells(iBaseCell.Row, iBaseCell.Column + vColCountHedge + 2)
rpt_OutputSelectionUnderline(targetWorkSheet, iBaseCell, vColCountHdgItm, 2)
iBaseCell = targetWorkSheet.Cells(iBaseCell.Row + 1, iBaseCell.Column)
rpt_SubtotalTableColumns(targetWorkSheet, iBaseCell, iDataSet.GetItem(“ds_EffTestHdgItmCashFlows_” & iTestId))
vColCursor += 2 + vColCountHdgItm
End If
If iDataSet.GetItem(“ds_EffTestHdgItmSubtotals_” & iTestId).iDataTable.Rows.Count > 0 Then
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + vRowCount + 4, xTestsAnchorcell.Column)
iBaseCell.PutValue(“Hedge Payment Amounts:”)
rpt_FormatASelectionFontProperties(iBaseCell, “Arial”, 12, True, True, False, 0)
rpt_FormatMergeCells(targetWorkSheet, iBaseCell, 0, 3)
For i = 0 To (iDataSet.GetItem(“ds_EffTestHedgeSubtotals_” & iTestId).iDataTable.Columns.Count / 2) - 1
targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column).PutValue(iDataSet.GetItem(“ds_EffTestHedgeSubtotals_” & iTestId).iDataTable.Columns(i * 2).Caption)
rpt_FormatMergeCells(targetWorkSheet, targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column), 0, 3)
targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column).PutValue(iDataSet.GetItem(“ds_EffTestHedgeSubtotals_” & iTestId).iDataTable.Rows(0).Item(i * 2))
rpt_FormatSelectedCellsUsingFormatCode(targetWorkSheet.Cells.CreateRange(iBaseCell.Row + i + 1, iBaseCell.Column + 1, 1, 1), cfcReportData.dsReportColumn_FormatCode.eFormatCode_Transactions_CurrencyAmount_ToTwoPlacesDefaultStyle, iDataSet.GetItem(“ds_EffTestHedgeSubtotals_” & iTestId).iDataTable.Rows(0).Item((i * 2) + 1))
Next i
iBaseCell = targetWorkSheet.Cells(xTestsAnchorcell.Row + vRowCount + 6 + i, xTestsAnchorcell.Column)
iBaseCell.PutValue(“100% Effective Payment Amounts:”)
rpt_FormatASelectionFontProperties(iBaseCell, “Arial”, 12, True, True, False, 0)
rpt_FormatMergeCells(targetWorkSheet, iBaseCell, 0, 3)
For i = 0 To (iDataSet.GetItem(“ds_EffTestHdgItmSubtotals_” & iTestId).iDataTable.Columns.Count / 2) - 1
targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column).PutValue(iDataSet.GetItem(“ds_EffTestHdgItmSubtotals_” & iTestId).iDataTable.Columns(i * 2).Caption)
rpt_FormatMergeCells(targetWorkSheet, targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column), 0, 3)
targetWorkSheet.Cells(iBaseCell.Row + i + 1, iBaseCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestHdgItmSubtotals_” & iTestId).iDataTable.Rows(0).Item(i * 2))
rpt_FormatSelectedCellsUsingFormatCode(targetWorkSheet.Cells.CreateRange(iBaseCell.Row + i + 1, iBaseCell.Column + 1, 1, 1), cfcReportData.dsReportColumn_FormatCode.eFormatCode_Transactions_CurrencyAmount_ToTwoPlacesDefaultStyle, iDataSet.GetItem(“ds_EffTestHdgItmSubtotals_” & iTestId).iDataTable.Rows(0).Item((i * 2) + 1))
Next i
iBaseCell = targetWorkSheet.Cells(iBaseCell.Row + vRowCount + 8 + (2 * i), iBaseCell.Column)
iBaseCell.PutValue(“Effectiveness Score:”)
rpt_FormatASelectionFontProperties(iBaseCell, “Arial”, 12, True, True, False, 0)
targetWorkSheet.Cells(iBaseCell.Row, iBaseCell.Column + 3).PutValue(iDataSet.GetItem(“ds_EffTestScore_” & iTestId).iDataTable.Rows(0).Item(0))
rpt_FormatSelectedCellsUsingFormatCode(targetWorkSheet.Cells.CreateRange(iBaseCell.Row, iBaseCell.Column + 3, 1, 1), cfcReportData.dsReportColumn_FormatCode.eFormatCode_Transactions_RateToTwoPlaces)
End If
'####################################################################3/21/2005########################
xAnchorCell.PutValue(“Effectiveness Test Report”)
rpt_FormatASelectionFontProperties(xAnchorCell, “Arial”, 18, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 1, xAnchorCell.Column).PutValue(“Current Test”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 1, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 1, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“testname”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 1, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 2, xAnchorCell.Column).PutValue(“Client Reference Number”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 2, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 2, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“ClientCRN”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 2, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 3, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 3, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“HedgeList”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 3, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 4, xAnchorCell.Column).PutValue(“Hedged Item”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 4, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 4, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“HedgedItemList”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 4, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 5, xAnchorCell.Column).PutValue(“Test Method”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 5, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 5, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“TestMethod”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 5, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 6, xAnchorCell.Column).PutValue(“Test Period”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 6, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 6, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“TestPeriod”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 6, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column).PutValue(“Notes”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“et_note”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column + 1).Style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Top
targetWorkSheet.Cells(xAnchorCell.Row + 7, xAnchorCell.Column).Style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Top
If iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“et_note”).Length > 0 Then
targetWorkSheet.AutoFitColumn(3)
End If
targetWorkSheet.Cells(xAnchorCell.Row + 8, xAnchorCell.Column).PutValue(“Effectiveness Score”)
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 8, xAnchorCell.Column), “Arial”, 12, True, False, False, 11)
targetWorkSheet.Cells(xAnchorCell.Row + 8, xAnchorCell.Column + 1).PutValue(iDataSet.GetItem(“ds_EffTestDetails_” & iTestId).iDataTable.Rows(0).Item(“et_effectiveness_score”))
rpt_FormatASelectionFontProperties(targetWorkSheet.Cells(xAnchorCell.Row + 8, xAnchorCell.Column + 1), “Arial”, 10, False, False, False, 0)
iBaseCell = targetWorkSheet.Cells(xAnchorCell.Row + 8, xAnchorCell.Column)
rpt_OutputDoubleUnderlineSelection(targetWorkSheet, iBaseCell, 3)
targetWorkSheet.Cells.Columns(3).Style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Right
targetWorkSheet.Cells.Columns(4).Style.HorizontalAlignment = aspose.Excel.TextAlignmentType.Left
xAnchorCell.Style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
rpt_FormatAlignAndAutofitColumns(targetWorkSheet, xTestsAnchorcell.Column, vColCursor, RPTMAXCOLSIZE)
'#End If
End Function
Please try this attached fix.
Thank you. GREAT thanks a million. it works.
Now I have a new problem, after this is done, when I open up the file by MS excel, it says too many format, need to remove some…blah…blah… Is that an aspose issue or MS excel issue?
Could you please post your generated file here? I will check it ASAP.