This project has moved. For the latest updates, please go here.

SaveAs throws System.Collections.Generic.KeyNotFoundException

Aug 15, 2011 at 12:04 PM

Hi there,

we are trying to use this nice wrapper in one of our current projects. So far we managed to do what we wanted to achieve but are struggeling to save the changes to a new file.
Whenever we call the SaveAs method on the workbook object we receive following exception

Die KwisServerLogic.Test.ExcelTest.Test_Excel_Stuff-Testmethode hat eine Ausnahme ausgelöst: System.Collections.Generic.KeyNotFoundException: Der angegebene Schlüssel war nicht im Wörterbuch angegeben.
bei System.Collections.Generic.Dictionary`2.get_Item(TKey key)
bei ClosedXML.Excel.XLWorkbook.<>c__DisplayClass1d7.<CollapseColumns>b__1cf(KeyValuePair`2 kp)
bei System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
bei System.Linq.Buffer`1..ctor(IEnumerable`1 source)
bei System.Linq.OrderedEnumerable`1.<GetEnumerator>d__0.MoveNext()
bei ClosedXML.Excel.XLWorkbook.CollapseColumns(Columns columns, Dictionary`2 sheetColumns)
bei ClosedXML.Excel.XLWorkbook.GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context)
bei ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document)
bei ClosedXML.Excel.XLWorkbook.CreatePackage(Stream stream, Boolean newStream)
bei ClosedXML.Excel.XLWorkbook.SaveAs(Stream stream)
bei Gdf.Kwis.Libraries.KwisServerLogic.Excel.WriteToExcelFile.<>c__DisplayClass8.<SaveExcelFile>b__7() in WriteToExcelFile.cs: line 125.
bei Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionManagerImpl.Process(Action action, String policyName) in c:\EntLib\Entlib\Source\Blocks.Desktop\ExceptionHandling\Src\ExceptionHandling\ExceptionManagerImpl.cs: line 191.
bei Gdf.Kwis.Libraries.KwisServerLogic.Excel.WriteToExcelFile.SaveExcelFile(String path) in WriteToExcelFile.cs: line 110.
bei KwisServerLogic.Test.ExcelTest.Test_Excel_Stuff() in ExcelTest.cs: line 79.

 We call the SaveAs method in following piece of code:

        public void SaveExcelFile(string path)
        {
            _excMgr.Process(() =>
                                {
                                    if (!path.EndsWith(@"\"))
                                    {
                                        path = path + @"\";
                                    }
                                    var p = path + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + "_" + _filename;

                                    //not working for some reasons
                                    //_workbook.SaveAs(p);

                                    //not working either
                                    FileStream fs = new FileStream(p,FileMode.Create,FileAccess.ReadWrite);
                                    using (MemoryStream ms = new MemoryStream())
                                    {
                                        _workbook.SaveAs(ms);
                                        //TODO: attach to output stream or something
                                        ms.WriteTo(fs);
                                    }
                                }, "KwisBackendLogicPolicy");
        }

Neither saving directly to a file or saving to a stream is working. The latter creates an empty file at given filepath p. 

Are we missing an important step? What's inside System.Collection.Generic.Dictionary`2? My guess would be an error with the zipping of the inner xml files of the xsls/xslm files, but I'm probably totally wrong with my assumption.

Thanks for your help.
Marc 

 

Coordinator
Aug 15, 2011 at 7:28 PM

This has to be a threading issue. Try the same on a single thread to see if it works there.

Aug 16, 2011 at 7:33 AM

Hi MDeLeon,
thanks for your quick reply. 

_excMgr is an instance of the ExceptionManager object of the Enterprise Library 5.0. I removed all exception wrappings done by the Process() method but the exception is still thrown.

Any other idea what could cause this exception?

Cheers,
Marc

Coordinator
Aug 16, 2011 at 5:05 PM

Can you save a workbook with just a "Hello" in one cell? If not then it's definitely a problem with your environment/architecture.

Aug 17, 2011 at 10:41 AM

Just tried that. It works without problems.

Coordinator
Aug 17, 2011 at 5:00 PM

Can you post the code you're using to create the file?

Aug 18, 2011 at 3:46 PM
Edited Aug 18, 2011 at 3:48 PM

The workbook and _worksheet are opened in the constructor by invoking following code

 

_excMgr.Process(() =>
                                    {
                                        _workbook = new XLWorkbook(filePath);
                                        _worksheet = _workbook.Worksheet(worksheet);

                                        if(siteId != -1 || period != -1)
                                        {
                                            this.SiteId = siteId;
                                            this.Period = period;
                                            GetAvailableTablesOnSheet();
                                        }
                                        else
                                        {
                                            GetAvailableTablesOnSheet();
                                            SetUpParametersFromSheetSetupTable();
                                        }

                                    }, "BackendLogicPolicy");

 

That runs perfectly fine. Also, GetAvailableTablesOnsheet finds all available tables in a sheet (minus the setup table, if exisiting). 
On a later point, I am calling the InsertData() method that puts stuff from the database into the sheet.

 

        public void InsertData()
        {
            _excMgr.Process(() =>
                                {
                                    var rawData = DatabaseAccess.DataAccessFacade.GetSignals(this.SiteId, this.Period);

                                    if (rawData.Count > 0)
                                    {
                                        foreach (var table in _tables)
                                        {
                                            foreach (var row in table.Rows())
                                            {
                                                var values = GetValuesForColumns(row);
                                                if (values != null)
                                                {
                                                    var query = LinQueryBuilder(values);

                                                    var result = rawData.AsQueryable().Where(query).FirstOrDefault();

                                                    if (result.Value != null)
                                                    {
                                                        row.Field("Values").Value = result.Value.Projected_Value;
                                                    }
                                                    else
                                                    {
                                                        //testing!
                                                        //row.Field("Values").Value = 99;
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }, "BackendLogicPolicy"); 
        }

 

That works without any exception. DB calls return expected values and these are written into the Value fields. But as I call SaveExcelFile (see code in the first post) I receive the exception when it hits _workbook.SaveAs(p).

Any help would be appreciated.

Cheers,
Marc 

 

Coordinator
Aug 19, 2011 at 4:58 AM

Try with the latest release (v0.58.0). It should solve this problem.

Aug 19, 2011 at 8:42 AM

Yep, it works now. Thanks for your help.

Would you mind explaining the issue to me?

Cheers,
Marc

Coordinator
Aug 19, 2011 at 3:15 PM

I fixed it based on the error log you supplied first. The CollapseColumns expected a dictionary of continuous keys and the code that calls it supposedly made sure this happened. Somehow (and I have no idea how) your code managed to send a dictionary that CollapseColumns doesn't expect. I modified CollapseColumns so it doesn't break in those situations.

Short of debugging your code this is the best I can do...