C#•12mo ago

I'm currently grappling with an issue while merging two Excel workbooks (`array1` and `array2`)

I'm currently grappling with an issue while merging two Excel workbooks (array1 and array2). The challenge arises when I add sheets from array2 to array1 using the following code:

private byte[] MergeExcelWorksheets(byte[] array1, byte[] array2)
{
    using (MemoryStream ms1 = new MemoryStream(array1))
    using (MemoryStream ms2 = new MemoryStream(array2))
    using (ExcelPackage package1 = new ExcelPackage(ms1))
    using (ExcelPackage package2 = new ExcelPackage(ms2))
    {
        var workbook1 = package1.Workbook;
        var workbook2 = package2.Workbook;

        // Iterate through sheets in workbook2
        foreach (var sourceSheet in workbook2.Worksheets)
        {
            if (!workbook1.Worksheets.Any(sheet => sheet.Name == sourceSheet.Name))
            {
                var clonedSheet = workbook1.Worksheets.Add(sourceSheet.Name, sourceSheet);
            }
        }

        return package1.GetAsByteArray();
    }
}

private byte[] MergeExcelWorksheets(byte[] array1, byte[] array2)
{
    using (MemoryStream ms1 = new MemoryStream(array1))
    using (MemoryStream ms2 = new MemoryStream(array2))
    using (ExcelPackage package1 = new ExcelPackage(ms1))
    using (ExcelPackage package2 = new ExcelPackage(ms2))
    {
        var workbook1 = package1.Workbook;
        var workbook2 = package2.Workbook;

        // Iterate through sheets in workbook2
        foreach (var sourceSheet in workbook2.Worksheets)
        {
            if (!workbook1.Worksheets.Any(sheet => sheet.Name == sourceSheet.Name))
            {
                var clonedSheet = workbook1.Worksheets.Add(sourceSheet.Name, sourceSheet);
            }
        }

        return package1.GetAsByteArray();
    }
}

- I'm trying to merge two Excel workbooks (array1 and array2), and while the sheets from array1 are created successfully, they seem to be empty when additional sheets from array2 are added. The perplexing part is that the sheets from array2 are being integrated correctly, but the corresponding sheets from array1 appear to lose their data.

5 Replies

ThatDaniel•12mo ago

might be some bug in the package you are using. Do their docs have anything about merging workbooks?

canton7•12mo ago

Excel workbooks are very complicated, and if you're using OpenXML directly you need to handle that complexity yourself For example, sheets can reference data kept in common locations (IIRC?) in order to store things more efficiently If you can, definitely use a package which makes life a bit easier. I don't know whether any of them support merging though... Look at ClosedXML, NPOI, and others

Ami2OP•12mo ago

I didn't find any Seems I'm close to find the solution but the only problem I facing now is If I'm returing the right data return package1.GetAsByteArray(); And the solution bellow return just the sheets of the first workbook but the sheets of the second one not created

        private byte[] MergeExcelWorksheets(byte[] array1, byte[] array2)
        {
            using (MemoryStream ms1 = new MemoryStream(array1))
            using (MemoryStream ms2 = new MemoryStream(array2))
            using (ExcelPackage package1 = new ExcelPackage(ms1))
            using (ExcelPackage package2 = new ExcelPackage(ms2))
            {
                var workbook1 = package1.Workbook;
                var workbook2 = package2.Workbook;

                // Iterate through sheets in workbook2
                foreach (var sourceSheet in workbook2.Worksheets)
                {
                    if (!workbook1.Worksheets.Any(sheet => sheet.Name == sourceSheet.Name))
                    {
                        // Clone the sheet and add it to workbook1
                        var clonedSheet = workbook1.Worksheets.Add(sourceSheet.Name, sourceSheet);
                    }
                }

                return ms1.ToArray();
            }
        }

        private byte[] MergeExcelWorksheets(byte[] array1, byte[] array2)
        {
            using (MemoryStream ms1 = new MemoryStream(array1))
            using (MemoryStream ms2 = new MemoryStream(array2))
            using (ExcelPackage package1 = new ExcelPackage(ms1))
            using (ExcelPackage package2 = new ExcelPackage(ms2))
            {
                var workbook1 = package1.Workbook;
                var workbook2 = package2.Workbook;

                // Iterate through sheets in workbook2
                foreach (var sourceSheet in workbook2.Worksheets)
                {
                    if (!workbook1.Worksheets.Any(sheet => sheet.Name == sourceSheet.Name))
                    {
                        // Clone the sheet and add it to workbook1
                        var clonedSheet = workbook1.Worksheets.Add(sourceSheet.Name, sourceSheet);
                    }
                }

                return ms1.ToArray();
            }
        }

canton7•12mo ago

That's not surprising, as you never save any of the changes to workbook1

Ami2OP•12mo ago

I already this package1.Save(); after Iterate through sheets in workbook2 but nothing changes @canton7 note I'm call the result here

 var bytes = _service.MergeExcelWorksheets();

 if (bytes == null)
     return null;

 var fileStream = new MemoryStream(bytes);

 var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 var fileDownloadName = string.Format("test{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));

 fileStream.Position = 0;
 var fsr = new FileStreamResult(fileStream, contentType);
 fsr.FileDownloadName = fileDownloadName;

 var bytes = _service.MergeExcelWorksheets();

 if (bytes == null)
     return null;

 var fileStream = new MemoryStream(bytes);

 var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 var fileDownloadName = string.Format("test{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));

 fileStream.Position = 0;
 var fsr = new FileStreamResult(fileStream, contentType);
 fsr.FileDownloadName = fileDownloadName;

Gaming

Programming

I'm currently grappling with an issue while merging two Excel workbooks (`array1` and `array2`)