Excel Interop应用图表模板 [英] Excel Interop Apply Chart Template

问题描述

问题概述:

我正在为使用带有本机" excel支持(microsoft.office.interop.excel)和 EPPlus 库.

I am automating the report generation (excel) for a customer using C# with "native" excel support (microsoft.office.interop.excel) and the EPPlus library.

我的客户在图表设计方面不太灵活，因此我的图表必须与他们的样式完全相同.

My customer is not very flexible about charts design so my charts must have the exact same style as theirs.

没问题，我使用Excel 2010导出了图表模板

No problem, i exported their chart templates using Excel 2010

什么不起作用:

我无法通过代码应用任何图表模板

I can't apply any chart template via code

我尝试过的事情:

1-EPPlus:不支持将模板加载到图表

1 - EPPlus : Have no support for loading templates to charts

2-InteropExcel:无法应用引发异常的模板，这是我的代码:

2 - InteropExcel : Fails on applying the template raising an exception, here is my code:

        Application excelApp = null;
        Workbook    workbook = null;
        Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

        try
        {
            excelApp  = new Microsoft.Office.Interop.Excel.Application();
            workbook  = excelApp.Workbooks.Open(config.DiretorioRelatorio);
            worksheet = workbook.Sheets[Consts.RECOVERED_SHEET];

            string template = config["templatePath"]; // .crtx file

            ChartObjects charts = worksheet.ChartObjects ();
            ChartObject chart   = ((ChartObject)charts.Item (0));
            chart.Chart.ApplyChartTemplate(template);

        }
        catch (Exception ex)
        {
            Console.WriteLine (ex.Message);
        }
        finally
        {
            workbook.Save ();
            workbook.Close ();
            excelApp.Quit ();

            ReleaseObject (worksheet);
            ReleaseObject (workbook);
            ReleaseObject (excelApp);
        }

此代码要么抛出:

1-excel interop HRESULT: 0x800A03EC(在将ChartObjects [0]转换为ChartObject时)

1 - excel interop HRESULT: 0x800A03EC (on casting ChartObjects[0] to ChartObject)

2-The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

TL:DR:

如何使用C#将文件中的图表模板应用于电子表格上的现有图表?

How can i apply a chart template from a file, to an existing chart on my spreadsheet using C# ?

Pastebin代码: ExcelInteropProblem

Pastebin code : ExcelInteropProblem

推荐答案

VBA中的索引不是从零开始的，因此，从.Net到Excel Interop时，即使C#中的索引是从1开始，也要更改它:

Indexes in VBA aren't zero based, so when going from .Net to Excel Interop, you start at 1 even though indexes in C# are, so change this:

ChartObject chart   = ((ChartObject)charts.Item (0));

对此:

ChartObject chart   = ((ChartObject)charts.Item (1));

这篇关于Excel Interop应用图表模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！