如何将Excel Interop与ClosedXml混合:样式 [英] How to mix Excel Interop with ClosedXml: styles

问题描述

我正在尝试在Excel VSTO加载项中实现以下目标:

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

从Excel文件的原始版本复制/应用单元格格式.

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

原因:在处理Excel文件时，有时我们需要更改文件的样式/颜色，以便仅处理某些单元格.处理完文件后，我们需要恢复原始单元格格式.为此，我编写了一个VSTO加载项.

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.

它读取原始文件，遍历所有使用的单元格，并将每个单元格的格式应用于最终文件中的相应单元格(地址).它可以正常工作，但是可以通过Interop打开原始文件-这可能是一个问题，因为文件名没有更改.另外:不理想的性能.另外:用户在操作过程中会看到原始文件，这可能会引起混乱.

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.

因此，我想通过ClosedXml打开"原始文件.这就是我被困住的地方:

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);
}

我认为麻烦在于，在Excel互操作中，诸如Color/TintAndShade等以及边框线型/权重等比在ClosedXml中更细粒度地定义，这使得两种不兼容?

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.

推荐答案

没关系.我只需关闭活动工作簿，使用ClosedXml完成所有繁琐的工作，然后重新打开即可解决此问题:

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);

这篇关于如何将Excel Interop与ClosedXml混合:样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！