CSharp DocumentFormat.OpenXML and Excel - Copy Data in Worksheet from one spreadsheet to another (Have code)

Question

I would like your help, please. I'm creating a process where it will read Excel file and copy the worksheets over to the new spreadsheet.

I'm using DocumentFormat.OpenXML to create a new spreadsheet and then it will loop through each Excel spreadsheet and copy the worksheet.

I'm using DocumentFormat because I'm required to run the process on production server and using Word/Excel libraries (Interop libraries) on the server are strongly advised against.

The problem is it's not copying the data. It does copy the spreadsheet tab name. I can create a new worksheet in the new spreadsheet for each one in the current Excel file. When I opened up the Excel spreadsheet, the application gave an error message saying.

"We found a problem with some content in 'Test.xlsx'. Do you want use to try to recover as much as we can? Uf you trust the source of this workbook, click Yes".

I clicked Yes and Excel recovered the spreadsheet. It got the spreadsheet worksheet names. As a test, I renamed the worksheets' name like Bob, Martin, Trevor. The process copied the worksheet's name into the new spreadsheet. So I can get access to the worksheet. For some reason, it's not copying the data inside the

My code is below. If you need to replicate the issue, you need to have DocumentFormat.OpenXML (nuget) in your Visual Studio project and install OpenXML SDK 2.5.

DocumentFormat.OpenXML (from nuget)

https://www.nuget.org/packages/DocumentFormat.OpenXml/

OpenXML SDK 2.5

https://www.microsoft.com/en-gb/download/details.aspx?id=30425

Here's the namespaces I've used in the top of my code.

Namespaces

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml;
using System.IO;
using ExcelDataReader;

Test function - Merge Excel Files. It will retrieve all Excel files from the directory: "C:\lab\excel". It will then create a new spreadsheet called "Test.xlsx" in "C:\lab\".

private static void mergeExcelFiles()
        {
            string mergeFilePath = "C:\\lab\\Test.xlsx";
            string dirPath = "C:\\lab\\excel";
            string[] files = System.IO.Directory.GetFiles(dirPath, "*.xlsx");

            // delete file
            try
            {
                System.IO.File.Delete(mergeFilePath);
            } catch (Exception ex)
            {
                Console.WriteLine("Can't delete file. Ex: {0}", ex.Message);
            }             

            // CREATE SPREADSHEET
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(mergeFilePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                int sheetID = 1;

                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                WorksheetPart worksheetPart = worksheetPart = workbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorksheetPart>();
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                workbookPart.Workbook.Sheets = sheets;

                System.Diagnostics.Debug.WriteLine("Document: Before - How many worksheets?: " + document.WorkbookPart.WorksheetParts.Count());

                foreach (var doc in files)
                {
                    string fullDocumentPath = doc;

                    // CHECK IF FILE EXISTS
                    if (!System.IO.File.Exists(fullDocumentPath))
                    {
                        continue;
                    }

                    // CREATE WORKSHEETS
                    using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument tempSpreadsheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fullDocumentPath, true))
                    {
                        WorkbookPart tempWorkbookPart = tempSpreadsheet.GetPartsOfType<WorkbookPart>().FirstOrDefault();
                        DocumentFormat.OpenXml.Spreadsheet.Workbook tempWorkbook = tempWorkbookPart.Workbook;

                        DocumentFormat.OpenXml.Spreadsheet.Sheet tempSheet = tempWorkbook.Sheets.FirstOrDefault() as DocumentFormat.OpenXml.Spreadsheet.Sheet;

                        if (tempSheet == null)
                        {
                            continue;
                        }

                        tempSheet.SheetId = (uint) sheetID;

                        workbookPart.Workbook.Sheets.AppendChild(tempSheet.CloneNode(true));
                        workbookPart.Workbook.Save();

                        sheetID += 1;
                    }

                }

                document.Save();

                System.Diagnostics.Debug.WriteLine("Document: After - How many worksheets?: " + document.WorkbookPart.WorksheetParts.Count());

            }

        }

Can anyone please advise why the data in the spreadsheets are not being copied over to the new spreadsheet?

Answer 1

In your code: you're getting the excel filename, store it in string array, then trying to delete that excel file. Create a spreadsheet document and then try to store it as the same mergeFilePath you just try to delete.

Based on your descriptions, you wanted to copy an excel file from the template that you already have stored somewhere in the directory and copy it, then make some modifications to the excel file. I've simplified the code:

    public static void mergeExcelFiles()
    {
        string template = @"Template.xlsx"; // look in your bin folder
        string fileName = @"Test.xlsx";
        File.Copy(template, fileName, true);

        using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
        {
            // Instatiate new SheetData
            SheetData sheetData = new SheetData();
            // Instatiate WorkbookPart from SpreadsheetDocument
            WorkbookPart workbookPart = document.WorkbookPart;
            // Get the (first)worksheet
            //Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
            Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().First();
            if (theSheet != null)
            {
                sheetData = ((WorksheetPart)workbookPart.GetPartById(theSheet.Id)).Worksheet.GetFirstChild<SheetData>();
            }

            Row row = new Row();
            Cell cell = new Cell();
            cell.DataType = CellValues.InlineString;
            cell.CellReference = "A2";
            Text text = new Text("Some text");
            InlineString inlineString = new InlineString(text);
            cell.AppendChild(inlineString);
            row.RowIndex = (UInt32)2;
            row.AppendChild(cell);
            // Append new row to SheetData
            sheetData.AppendChild(row);

            workbookPart.Workbook.Save();
        }
    }