How to mix Excel Interop with ClosedXml: styles

Question

I am trying to achieve the following in an Excel VSTO add-in:

Copying/applying cell formatting from the original version of an Excel file.

Reason: while processing Excel files, we occasionally need to change the style/coloring of a file in order to be able to process certain cells only. After the file has been processed, we need to restore the original cell formats. For that purpose, I wrote a VSTO add-in.

It reads the orignal file, iterates over all used cells and applies the formatting of each to the respective cell (address) in the final file. It works fine, but it opens the original file via Interop - which can be a problem because the file name does not change. Also: not ideal performance-wise. Also: the user sees the original file during the operation which can cause confusion.

Therefore I would like to "open" the original file via ClosedXml. This is where I get stuck:

var xl = Globals.ThisAddIn.Application;
var dest = xl.ActiveWorkbook; //Interop
try
{
    var org = new XLWorkbook(pfad); //ClosedXml
    foreach (IXLWorksheet sheet in org.Worksheets)
    {
        var used = sheet.RangeUsed(true);
        Excel.Worksheet dsheet = dest.Sheets[sheet.Name];
        foreach (IXLCell cel in used.Cells(false))
        {
            var adr = cel.Address;
            var interior = dsheet.Range[adr].Interior;
            interior.Color = cel.Style.Fill.BackgroundColor;
            interior.Pattern = cel.Style.Fill.PatternColor;

            //Crash!
            //HRESULT: 0x800A03EC
            //...
            //...

            var borders = dsheet.Range[adr].Borders;
            var orgbord = cel.Style.Border;

            borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = orgbord.TopBorder;
            //Crash!
            //HRESULT: 0x800A03EC
            //...
            //...
        }
    }
    org.Dispose();
}
catch (Exception ex)
{
        log.WriteLine(ex.Message);
}

I think the trouble is that in Excel interop, things like Color/TintAndShade etc. as well as borders linestyle/Weight etc. are much more granularly defined than in ClosedXml which makes the two kind of incompatible?

Has anyone here tried something similar before and knows how to do this?

Would be great if I could somehow solve this so I'm not stuck with all interop.

Attraction

Answer 1

Never mind. I solved this by simply closing the active workbook, doing all the grunt work with ClosedXml, then re-opening:

var xl = Globals.ThisAddIn.Application;
var dest = xl.ActiveWorkbook;
var dpfad = dest.FullName;
dest.Close();
var xdest = new XLWorkbook(dpfad);
var org = new XLWorkbook(pfad);
foreach (IXLWorksheet sheet in org.Worksheets)
{
    var used = sheet.RangeUsed(true);
    IXLWorksheet dsheet;
    xdest.TryGetWorksheet(sheet.Name, out dsheet);

    foreach (IXLCell cel in used.Cells(false))
    {
        var dcel = dsheet.Range(cel.WorksheetRow().RowNumber(),
            cel.WorksheetColumn().ColumnNumber(), cel.WorksheetRow().RowNumber(),
            cel.WorksheetColumn().ColumnNumber());
        dcel.Style = cel.Style;
    }
}
xdest.Save();
xdest.Dispose();
org.Dispose();
xl.Workbooks.Open(dpfad);

Attraction

It's also possible to get XML data from a workbook without closing it be copying to the clipboard and the writing your own parsing logic for it. We did that. However, the major problem with that approach is that the clipboard is not reliable e.g. it poses a security risk when the lock screen is down and so doesn't work in that case e.g. see #57095933. So this solution is probably safer (though I haven't tested it yet). – Anthologize