将Excel图表导出为图像 [英] Exporting Excel Charts as Images

问题描述

我编写了以下简单的C#控制台应用程序，用于从Excel工作簿中导出所有图表. 工作得很好，除非自打开文档以来没有将图表滚动到，在这种情况下，会生成一个空的图像文件.

I've written the following simple C# console application to export all the charts from an Excel Workbook. It works just fine unless the chart has not been scrolled to since opening the document, in which case an empty image file is generated.

using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Diagnostics;

namespace ExcelExporter
{
    class ChartExporter
    {
        const string EXPORT_TO_DIRECTORY = @"C:\Users\Sandy\Desktop\Excel\Charts";

        static void Main(string[] args)
        {
            Excel.Application app = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Microsoft.Office.Interop.Excel.Application;

            ConsoleColor c = Console.ForegroundColor;
            Console.ForegroundColor = ConsoleColor.Green;
            Console.Write("Export To: ");
            Console.ForegroundColor = c;
            string exportPath = Console.ReadLine();

            if (exportPath == "")
                exportPath = EXPORT_TO_DIRECTORY;

            Excel.Workbook wb = app.ActiveWorkbook;

            foreach (Excel.Worksheet ws in wb.Worksheets)
            {
                Excel.ChartObjects chartObjects = (Excel.ChartObjects)(ws.ChartObjects(Type.Missing));

                foreach (Excel.ChartObject co in chartObjects)
                {
                    Excel.Chart chart = (Excel.Chart)co.Chart;
//                  app.Goto(co, true);
                    chart.Export(exportPath + @"\" + chart.Name + ".png", "PNG", false);
                }
            }

            Process.Start(exportPath);
        }
    }
}

我尝试了几次滚动到该对象的尝试；例如，指向程序底部的注释行(app.Goto(co, true);)仅适用于范围. 是否可以滚动到ChartObject，或者确保它们正确导出到图像?

I've made a few failed attempts to scroll to the object; the commented out line towards the bottom of the program (app.Goto(co, true);) for example only works for ranges. Is there any way to scroll to ChartObjects, or otherwise ensure that they properly export to images?

要进行测试，请使用工作表，其中的图表向下排了1000多个行(足够远，以至于在打开文档时毫无疑问地看不到它们)；在运行程序之前，关闭并重新打开文档(一旦滚动到该图表，该图表将已经呈现并存储在内存中).

For testing, use a workbook with charts 1000+ rows down (far enough that they are unquestionably out of view when the document opens); close and reopen the document before running the program (once scrolled to, the charts will have already been rendered and stored in memory).

推荐答案

VBA，但相同的想法可能适用于C#...

VBA, but same idea would likely work for C#...

Dim co As ChartObject, sht As Worksheet, x As Long
x = 1
Set sht = ThisWorkbook.Sheets("Sheet1")
For Each co In sht.ChartObjects
    Application.Goto co.TopLeftCell, True
    co.Chart.Export "C:\_stuff\test\chart" & x & ".jpg", "JPG", False
    x = x + 1
Next co

这篇关于将Excel图表导出为图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！