Hello!
Im trying to add new sheet in existing Excel file. Excel file has set password to modify. It isnt working only on one pc right now. Any ideas what could be wrong? Im using Infragistic 15.1.20151.2179 version.
Message: Unable to cast object of type 'Infragistics.Documents.Excel.Workbook' to type 'Infragistics.Documents.Excel.Serialization.WorksheetReference'.Stack stack: at Infragistics.Documents.Excel.Serialization.BIFF8.BiffRecords.EXTERNNAMERecord.Save(WorkbookSaveManagerExcel2003 saveManager) at Infragistics.Documents.Excel.Serialization.WorkbookSaveManagerExcel2003.WriteRecord(BIFF8RecordType type) at Infragistics.Documents.Excel.Serialization.WorkbookSaveManagerExcel2003.WriteExternalReferences() at Infragistics.Documents.Excel.Serialization.WorkbookSaveManagerExcel2003.WriteWorkbookGlobals() at Infragistics.Documents.Excel.Serialization.WorkbookSaveManagerExcel2003.SaveCore() at Infragistics.Documents.Excel.Serialization.WorkbookSaveManager.<>c__DisplayClassb.<Save>b__9() at Infragistics.Documents.Core.Async.Try(Func`1 try, Action finally) at Infragistics.Documents.Excel.Serialization.WorkbookSaveManager.Save() at Infragistics.Documents.Excel.Workbook.<>c__DisplayClass43.<>c__DisplayClass45.<SaveBIFF8File>b__41(WorkbookSaveManagerExcel2003 saveManager) at Infragistics.Documents.Core.Async.UsingHelper`1.<Execute>b__a() at Infragistics.Documents.Core.Async.Try(Func`1 try, Action finally) at Infragistics.Documents.Core.Async.UsingHelper`1.Execute() at Infragistics.Documents.Excel.Workbook.<>c__DisplayClass43.<>c__DisplayClass45.<SaveBIFF8File>b__40(Stream workbookStream) at Infragistics.Documents.Core.Async.UsingHelper`1.<Execute>b__a() at Infragistics.Documents.Core.Async.Try(Func`1 try, Action finally) at Infragistics.Documents.Core.Async.UsingHelper`1.Execute() at Infragistics.Documents.Excel.Workbook.<>c__DisplayClass43.<SaveBIFF8File>b__3f(StructuredStorageManager structuredStorageManager) at Infragistics.Documents.Core.Async.UsingHelper`1.<Execute>b__a() at Infragistics.Documents.Core.Async.Try(Func`1 try, Action finally) at Infragistics.Documents.Core.Async.UsingHelper`1.Execute() at Infragistics.Documents.Excel.Workbook.SaveBIFF8File(Stream stream, WorkbookSaveOptions saveOptions) at Infragistics.Documents.Excel.Workbook.SaveHelper(Stream stream, WorkbookSaveOptions saveOptions) at Infragistics.Documents.Excel.Workbook.Save(String fileName, WorkbookSaveOptions saveOptions) at frmReport.WriteToExcel() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()Target site: Void Save(Infragistics.Documents.Excel.Serialization.WorkbookSaveManagerExcel2003)
Hello Andris,
Thank you for posting in our forum.
In order to be able to investigate your issue in deeper details I will need a little more information related to your scenario:
Can you send me a sample where the issue can be reproduced?
Since you have mentioned that it's not working on one pc, I really wonder the pc settings you have and any details would be highly appreciated.
thanks,
Josheela
Code is something like this.
There is manual excel formating in UltraGridExporter events with setting up formats like this
private void InitializeSum(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.InitializeSummaryEventArgs e) { e.ExcelFormatStr = e.Summary.SourceColumn.Format; } private void InitializeColumn(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventArgs e) { e.ExcelFormatStr = e.Column.Format; }
//But general code:
//Opening existing xls fileWorkbook oWorkBook = new Infragistics.Documents.Excel.Workbook();oWorkBook = Workbook.Load(xlsFilePath);if (oWorkBook.IsProtected) oWorkBook.Unprotect(password);
//removing sheet by name if exists and then adding new one with same nameif (oWorkBook.Worksheets.ToList().Select(x => x.Name == newSheetName).Count() > 0){ for (int i = 0; i < oWorkBook.Worksheets.Count; i++) { if (oWorkBook.Worksheets[i].Name == newSheetName) { oWorkBook.Worksheets.RemoveAt(i); } }}
oWorkSheet = oWorkBook.Worksheets.Add(newSheetName);
//doing some manual formating in events and adding export data from filled Ultragrid to newly added worksheet UltraGridExcelExporter ugrdReport;ugrdReport = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter(this.components);ugrdReport.HeaderRowExporting += new Infragistics.Win.UltraWinGrid.ExcelExport.HeaderRowExportingEventHandler(HeaderRowExporting);ugrdReport.SummaryRowExporting += new Infragistics.Win.UltraWinGrid.ExcelExport.SummaryRowExportingEventHandler(SummaryRowExporting);ugrdReport.InitializeColumn += new Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventHandler(InitializeColumn);ugrdReport.RowExporting += new Infragistics.Win.UltraWinGrid.ExcelExport.RowExportingEventHandler(RowExporting);ugrdReport.BandSpacing = Infragistics.Win.UltraWinGrid.ExcelExport.BandSpacing.None;ugrdReport.InitializeSummary += new EventHandler<InitializeSummaryEventArgs>(InitializeSum);ugrdReport.SummaryCellExported += new Infragistics.Win.UltraWinGrid.ExcelExport.SummaryCellExportedEventHandler(ugrdReport_SummaryCellExported);ugrdReport.ExportFormulas = false;
ugrdEEZurnals.Export(myUltragrid, oWorkSheet, 4, 0);
//setting up file write protection passowrsoWorkBook.SetFileWriteProtectionPassword(password);
//And saving oWorkBook.Save(xlsFilePath);
Hi Andris,
There is always that one machine eh! If the environment matches the other machines this certainly is odd. What differences exist between the machine that isn't working and the others that do work? Would this other machine have a different version of the Infragistics tools being used or installed? Is the sample Josheela added reflective of what you are doing in your application? Does the sample work locally for you and what happens on the problematic machine?
Thank You very much will check sample.
I created a sample using the code provided, but failed in reproducing the issue. Instead of using Stream to open the file, I directly opened it to the WorkBook from the file.I am attaching my sample here.Please let me know if you need any more assistance on this case.
Thanks,Josheela
I am following up to inform you that I am working on your case and will have our engineering staff to look at it. I am hoping to have a solution before end of day today.
Thanks.