This project has moved and is read-only. For the latest updates, please go here.

Copy/combine multiple spreadsheets to one workbook caused exception

Nov 11, 2013 at 11:37 PM
Edited Nov 11, 2013 at 11:37 PM
I have x number of workbooks containing sheetNamePreValidate and sheetNameDetailEntry . I want to copy and append the data from each of the workbooks to a single set of spreadsheets within a template workbook, i.e., copy wb1sheetNamePreValidate to templatesheetNamePreValidate , wb2sheetNamePreValidate to templatessheetNamePreValidate (last row + 1) etc, same for all spreadsheets in each workbook.
static void Main ( string [] args )
{
  var nameOfWorkBook = @"combinedWorkbook.xlsx";
  var exportFilePath = @"C:\temp\";
  var rawFilesDirectory = @"C:\temp\input";
  var deleteRqwFiles = false;
  IEnumerable<string> filesToMerge = null;

  // The workbook and worksheets used in this application
  IXLWorksheet worksheetPreValidate = null;
  IXLWorksheet worksheetDetailEntry = null;

  // Assign the sheet names to use
  var sheetNamePreValidate = Resources.Resources.PreValidateSheetName;
  var sheetNameDetailEntry = Resources.Resources.DetailEntrySheetName;

  var workbook = new XLWorkbook ( Path.Combine ( exportFilePath, "template.xlsx" ), XLEventTracking.Enabled );

  if ( workbook.Worksheet ( sheetNamePreValidate ) != null && workbook.Worksheet ( sheetNameDetailEntry ) != null )
  {
      worksheetPreValidate = workbook.Worksheet ( sheetNamePreValidate );
      worksheetPreValidate.Columns ().Expand ();
      worksheetPreValidate.SheetView.FreezeColumns ( 0 );
      var rangePreValidate = worksheetPreValidate.Range ( worksheetPreValidate.Cell ( 1, 1 ).Address, 
          worksheetPreValidate.Cell ( 2, worksheetPreValidate.LastColumnUsed ().ColumnNumber () ).Address );

      worksheetDetailEntry = workbook.Worksheet ( sheetNameDetailEntry );
      worksheetDetailEntry.Columns ().Expand ();
      worksheetDetailEntry.SheetView.FreezeColumns ( 0 );
      var rangeDetailEntry = worksheetDetailEntry.Range ( worksheetDetailEntry.Cell ( 1, 1 ).Address, 
          worksheetDetailEntry.Cell ( 2, worksheetDetailEntry.LastColumnUsed ().ColumnNumber () ).Address );

      var dirInfo = new DirectoryInfo ( rawFilesDirectory );
      filesToMerge = dirInfo.GetFiles ( "*.xlsx", SearchOption.TopDirectoryOnly )
          .OrderBy ( f => f.CreationTimeUtc ).Select ( f => f.FullName );
          
      var numberOfFilesProcessed = 0;
      var numberOfTotalFiles = filesToMerge.Count ();

      var numberOfRowsAddedPreValidate = 1;
      var numberOfRowsAddedDetailEntry = 1;
      IXLWorksheet currentWorksheet = null;

      for ( int index = 0; index < numberOfTotalFiles; index++ )
      {
        var filePath = filesToMerge.ElementAtOrDefault ( index );
        var rowCount = 0;
        
        XLWorkbook childWorkbook = new XLWorkbook ( filePath );
        
        if ( childWorkbook.Worksheet ( sheetNamePreValidate ) != null && 
            childWorkbook.Worksheet ( sheetNameDetailEntry ) != null )
        {
          childWorkbook.Worksheet ( sheetNamePreValidate ).Columns ().Expand ();
          
          DisplayConsoleDebugInfo ( "\tCopying PreValidate worksheet." );
          currentWorksheet = childWorkbook.Worksheet ( sheetNamePreValidate );
          
          var firstCellAddress = currentWorksheet.Cell ( 2, 1 );
          var lastRow = currentWorksheet.LastRowUsed ().RowNumber ();
          var lastColumn = currentWorksheet.LastColumnUsed ().ColumnNumber ();
          var lastCellAddress = currentWorksheet.Cell ( lastRow, lastColumn );
          
          var sheetRange = currentWorksheet.Range ( firstCellAddress, lastCellAddress );
          
          rowCount = lastRow - 1;
          var lastPreValidateRow = rangePreValidate.LastRowUsed ().RowNumber ();
          numberOfRowsAddedPreValidate = numberOfRowsAddedPreValidate + rowCount + 1;
          
          rangePreValidate.Cell ( lastPreValidateRow + 1, 1 ).Value = 
              currentWorksheet.Range ( firstCellAddress, lastCellAddress );
          
          childWorkbook.Worksheet ( sheetNameDetailEntry ).Columns ().Expand ();
          currentWorksheet = childWorkbook.Worksheet ( sheetNameDetailEntry );
          
          firstCellAddress = currentWorksheet.Cell ( 2, 1 );
          lastRow = currentWorksheet.LastRowUsed ().RowNumber ();
          lastColumn = currentWorksheet.LastColumnUsed ().ColumnNumber ();
          lastCellAddress = currentWorksheet.Cell ( lastRow, lastColumn );
          
          sheetRange = currentWorksheet.Range ( firstCellAddress, lastCellAddress );
          
          rowCount = lastRow - 1;
          numberOfRowsAddedDetailEntry = numberOfRowsAddedDetailEntry + rowCount + 1;
          var lastDetailEntryRow = rangeDetailEntry.LastRowUsed ().RowNumber ();
          
          rangeDetailEntry.Cell ( lastDetailEntryRow + 1, 1 ).Value = sheetRange;
          
          rangePreValidate = worksheetPreValidate.Range ( worksheetPreValidate.Cell ( 1, 1 ).Address, 
              worksheetPreValidate.Cell ( numberOfRowsAddedPreValidate, 
              worksheetPreValidate.LastColumnUsed ().ColumnNumber () ).Address );
              
          rangeDetailEntry = worksheetDetailEntry.Range ( worksheetDetailEntry.Cell ( 1, 1 ).Address, 
              worksheetDetailEntry.Cell ( numberOfRowsAddedDetailEntry, 
              worksheetDetailEntry.LastColumnUsed ().ColumnNumber () ).Address );
        }
        else
        {
          Console.WriteLine ( string.Format ( "{0} does not contain valid spreadsheet", filePath ) );
        }
      }

      worksheetPreValidate.RangeUsed ().AddToNamed ( "RatingsData" );
      worksheetPreValidate.Range ( worksheetPreValidate.FirstColumnUsed ().FirstCell ().Address, 
            worksheetPreValidate.LastColumnUsed ().FirstCell ().Address ).SetAutoFilter ();
      worksheetDetailEntry.Range ( worksheetDetailEntry.FirstColumnUsed ().FirstCell ().Address, 
            worksheetDetailEntry.LastColumnUsed ().FirstCell ().Address ).SetAutoFilter ();

      var outfile = Path.Combine ( exportFilePath, nameOfWorkBook );
      workbook.SaveAs ( outfile );
  }
}
I usually get the exception when the file is being saved:
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at ClosedXML.Excel.XLWorkbook.GetStyleById(Int32 id) in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLConditionalFormat.GetStyle() in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLConditionalFormat.get_Style() in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLWorkbook.AddDifferentialFormats(WorkbookStylesPart workbookStylesPart, SaveContext context) in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLWorkbook.GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context) in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document) in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLWorkbook.CreatePackage(String filePath) in d:\test\Program.cs:line 0
   at ClosedXML.Excel.XLWorkbook.SaveAs(String file) in d:\test\Program.cs:line 0
   at Toar.LmCat.Bezoar.Program.Main(String[] args) in d:\test\Program.cs:line 255
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   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()
I am at a loss as I can easily generate the separate workbooks using ClosedXML (which I did :) ) but not when I am recombining them.