How many columns when I add a new worksheet


#1

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


#2

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.


#3

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


#4

How do you call the AutofitColumn method? Could you please post your code here? And do you use the latest hotfix v2.9.7?


#5

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


#6

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.


#7

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


#8

Please try this attached fix.


#9

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?


#10

Could you please post your generated file here? I will check it ASAP.