Hi,
I am exporting 3 grids into an excel workbook, 3 different worksheetsEach time if a worksheet already exists, I have to erase worksheet and write grid contents. I am doing as below and export works good first time but runs into issues second or third time
I get an exception with error "Index was out of range. Must be non-negative and less than the size of the collection"
Workbook workbook = Workbook.Load(FilePath);if (workbook.Worksheet.Exists(WorksheetName)){ Worksheet worksheet = workbook.Worksheets[WorksheetName];
workbook.Worksheets.Remove(worksheet); workbook.Worksheets.Add(WorksheetName);}else{ workbook.Worksheets.Add(WorksheetName);}
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets[WorksheetName];
ultraGridExcelExporter.Export(grid, workbook);
Any suggestion will be helpful
Thx
It's hard to guess with so little information.
Can you post a small sample project which demonstrates the issue? If so, I would be happy to take a look.
If you can't do that, then just posting the call stack might help.
Mike, here is the stack trace.
mscorlib.dll!System.ThrowHelper.ThrowArgumentOutOfRangeException(System.ExceptionArgument argument, System.ExceptionResource resource) + 0x41 bytes mscorlib.dll!System.ThrowHelper.ThrowArgumentOutOfRangeException() + 0x10 bytes mscorlib.dll!System.Collections.Generic.List<int>.this[int].get(int index = 21) + 0x2b bytes Infragistics2.Excel.v9.1.dll!Infragistics.Excel.WorkbookColorCollection.TryGetUnusedUserColorIndex(out int indexIntoUserPalette = -1) + 0x149 bytes Infragistics2.Excel.v9.1.dll!Infragistics.Excel.WorkbookColorCollection.Add(System.Drawing.Color color = "{Name=InactiveCaption, ARGB=(255, 128, 128, 128)}", Infragistics.Excel.ColorableItem item = CellBorder) + 0x80f bytes Infragistics2.Excel.v9.1.dll!Infragistics.Excel.WorksheetCellFormatData.BottomBorderColor.set(System.Drawing.Color value = "{Name=InactiveCaption, ARGB=(255, 128, 128, 128)}") + 0x59 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.GetCellFormatFromAppearanceData(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.AppearanceData apData = {Infragistics.Win.AppearanceData}) + 0x26e bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ResolvedHeader(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.UltraWinGrid.HeaderBase header = {}) + 0x150 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ExportHeaderCell(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.UltraWinGrid.UltraGridRow row = {Infragistics.Win.UltraWinGrid.UltraGridRow}, Infragistics.Win.UltraWinGrid.GridExportCache.DocumentHeaderInfo documentColumnInfo = {Infragistics.Win.UltraWinGrid.GridExportCache.DocumentHeaderInfo}) + 0x12c bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ExportColumnHeaderCells(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.UltraWinGrid.UltraGridRow row = {Infragistics.Win.UltraWinGrid.UltraGridRow}, Infragistics.Win.UltraWinGrid.UltraGridBand band = {MinGen}, bool setRowHeights = true) + 0x622 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessHeaderRows(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.UltraWinGrid.UltraGridRow row = {Infragistics.Win.UltraWinGrid.UltraGridRow}, Infragistics.Win.UltraWinGrid.UltraGridBand band = {MinGen}) + 0x117 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessGridRowInternal(Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper exportHelper = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}, Infragistics.Win.UltraWinGrid.UltraGridRow row = {Infragistics.Win.UltraWinGrid.UltraGridRow}, Infragistics.Win.UltraWinGrid.ProcessRowParams processRowParams = {Infragistics.Win.UltraWinGrid.ProcessRowParams}) + 0x250 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper.ProcessRow(Infragistics.Win.UltraWinGrid.UltraGridRow row = {Infragistics.Win.UltraWinGrid.UltraGridRow}, Infragistics.Win.UltraWinGrid.ProcessRowParams processRowParams = {Infragistics.Win.UltraWinGrid.ProcessRowParams}) + 0x36 bytes Infragistics2.Win.UltraWinGrid.v9.1.dll!Infragistics.Win.UltraWinGrid.RowsCollection.InternalTraverseRowsHelper(Infragistics.Win.UltraWinGrid.IUltraGridExporter exporter = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}) + 0xc6 bytes Infragistics2.Win.UltraWinGrid.v9.1.dll!Infragistics.Win.UltraWinGrid.UltraGrid.Export(Infragistics.Win.UltraWinGrid.IUltraGridExporter exporter = {Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper}) + 0x1d0 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(Infragistics.Win.UltraWinGrid.UltraGrid grid = {Infragistics.Win.UltraWinGrid.UltraGrid}, Infragistics.Excel.Worksheet worksheet = Worksheet: "xxxx Report - xxxxxx", int startRow = 1, int startColumn = 1) + 0x1d9 bytes Infragistics2.Win.UltraWinGrid.ExcelExport.v9.1.dll!Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(Infragistics.Win.UltraWinGrid.UltraGrid grid = {Infragistics.Win.UltraWinGrid.UltraGrid}, Infragistics.Excel.Workbook workbook = {Infragistics.Excel.Workbook}, int startRow = 1, int startColumn = 1) + 0x92 bytes xxxxxx.UI.Common.ExcelReportControl.ExportToExcel(Infragistics.Win.UltraWinGrid.UltraGrid grid = {Infragistics.Win.UltraWinGrid.UltraGrid}, int startRow = 1, int startColumn = 1, bool clearWorkSheet = true) Line 222 + 0x22 bytes C#
Thanks for quick reply. Tried setting StandardPalette with no luck, also I guess that was by default. For now as a work around I am deleting the old workbook completely and doing export for all worksheets with no problems as it works for the first time. I used to run into problem on exporting again or rewriting the same worksheet again. On debugging during exception, found workbook was using Custompalette.
Mike,
Trying to load an ultragrid and export it. Runs good for the first 3 sheets, from worksheet 4 i get an exception with error message "The maximum number of colors in an excel workbook is :56". The stack trace is as below. I have export data onto 14 different worksheets in a single workbook.
Any suggestion will be helpful, if not I will be forced to read load a dataset and write data from dataset into excel. please advice..
at Infragistics.Excel.WorkbookColorCollection.AddInternal(Int32 indexIntoUserPalette, Color color)\r\n at Infragistics.Excel.WorkbookColorCollection.Add(Color color, ColorableItem item)\r\n at Infragistics.Excel.WorksheetCellFormatData.set_FillPatternForegroundColor(Color value)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.GetCellFormatFromAppearanceData(UltraGridExporterHelper exportHelper, AppearanceData apData)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ResolvedHeader(UltraGridExporterHelper exportHelper, HeaderBase header)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ExportHeaderCell(UltraGridExporterHelper exportHelper, UltraGridRow row, DocumentHeaderInfo documentColumnInfo)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ExportColumnHeaderCells(UltraGridExporterHelper exportHelper, UltraGridRow row, UltraGridBand band, Boolean setRowHeights)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessHeaderRows(UltraGridExporterHelper exportHelper, UltraGridRow row, UltraGridBand band)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.ProcessGridRowInternal(UltraGridExporterHelper exportHelper, UltraGridRow row, ProcessRowParams processRowParams)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExporterHelper.ProcessRow(UltraGridRow row, ProcessRowParams processRowParams)\r\n at Infragistics.Win.UltraWinGrid.RowsCollection.InternalTraverseRowsHelper(IUltraGridExporter exporter)\r\n at Infragistics.Win.UltraWinGrid.UltraGrid.Export(IUltraGridExporter exporter)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(UltraGrid grid, Worksheet worksheet, Int32 startRow, Int32 startColumn)\r\n at Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter.Export(UltraGrid grid, Workbook workbook, Int32 startRow, Int32 startColumn)\r\n at xxxxx.ExcelReportControl.ExportToExcel(UltraGrid grid, Int32 startRow, Int32 startColumn, Boolean clearWorkSheet) in
The DefaultWorkbookPalette property defaults to CustomPalette. So setting it to StandardPalette should avoid this exception. If that's not working, then I don't know why you are getting that error. It should be impossible.
If you can provide a small sample project demonstrating this exception, I will be happy to take a look at it.
Mike, Enclosed is a sample version that can reproduce error. Please let me know for solution
Thanks
Okay, the problem here is that the palette mode applies to the entire workbook. The DefaultWorkbookPaletteMode will apply the palette mode to any workbook that it creates, but in your sample, you are opening up an existing workbook from a file, which already have a palette, so there's nothing the exporter can do here.
If you are creating your own workbook object and passing it in to the export method, then you have to create the Workbook with the correct palette mode initially.
Workbook workbook = new Workbook( WorkbookPaletteMode.StandardPalette );
Hi Joseph,
I answered this question here.
Hi Mike,
How do you convert the columns for export to excel as numbers when they are bound to a datatable and the columns in question are of datatype double within the datatable/