/// <summary>
/// 提取Word中的图表中的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <remarks>如果将Excel中的Chart粘贴进Word，而且是以链接的形式粘贴的。在后期操作中，此Chart所链接的源Excel文件丢失，此时在Word中便不能直接提取到Excel中的数据了。</remarks>
public void ExtractDataFromWordChart(object sender, RibbonControlEventArgs e)
{
Chart cht = null;
Selection sele = _app.Selection;
//先查看文档中有没有InlineShape类型的Chart
InlineShapes ilshps = default(InlineShapes);
InlineShape ilshp = default(InlineShape);
ilshps = sele.InlineShapes;
foreach (InlineShape tempLoopVar_ilshp in ilshps)
{
ilshp = tempLoopVar_ilshp;
if (ilshp.HasChart == Office.MsoTriState.msoTrue)
{
cht = ilshp.Chart;
break;
}
}
//再查看文档中有没有Shape类型的Chart（即不是嵌入式图形的Chart，而是浮动式图形）
if (cht == null)
{
ShapeRange shps = default(ShapeRange);
Shape shp = default(Shape);
shps = sele.ShapeRange;
foreach (Shape tempLoopVar_shp in shps)
{
shp = tempLoopVar_shp;
if (shp.HasChart == Office.MsoTriState.msoTrue)
{
cht = shp.Chart;
break;
}
}
}
//对Chart中的数据进行提取
if (cht != null)
{
//
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory,
Environment.SpecialFolderOption.None);
var ExcelFilePath = Path.Combine(new[] { desktopPath, "Word 图表数据.xlsx" }); //用来保存数据的Excel工作簿的路径。
Microsoft.Office.Interop.Excel.Application ExcelApp =
default(Microsoft.Office.Interop.Excel.Application);
Workbook Wkbk = default(Workbook);
Worksheet sht = default(Worksheet);
bool blnExcelFileExists = false; //此Excel工作簿是否存在
if (File.Exists(ExcelFilePath.ToString()))
{
blnExcelFileExists = true;
// 直接打开外部的文档
Wkbk = (Workbook)Interaction.GetObject(ExcelFilePath.ToString(), null);
// 打开一个Excel文档，以保存Word图表中的数据
ExcelApp = Wkbk.Application;
}
else
{
// 先创建一个Excel进程，然后再在其中添加一个工作簿。
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Wkbk = ExcelApp.Workbooks.Add();
}
sht = Wkbk.Worksheets[1]; // 用工作簿中的第一个工作表来存放数据。
sht.UsedRange.Value = null;
//
SeriesCollection seriesColl = (SeriesCollection)cht.SeriesCollection();
//这里不能定义其为Excel.SeriesCollection
Series Chartseries;
//开始提取数据
short col = (short)1;
dynamic X = default(dynamic); // 这里只能将X与Y的数据类型定义为Object，不能是Object()或者Object(,)
object Y = null;
string Title = "";
// 这里不能用For Each Chartseries in SeriesCollection来引用seriesCollection集合中的元素。
for (var i = 1; i <= seriesColl.Count; i++)
{
// 在VB.NET中，seriesCollection集合中的第一个元素的下标值为1。
Chartseries = seriesColl[i];
X = Chartseries.XValues;
Y = Chartseries.Values;
Title = Chartseries.Name;
// 将数据存入Excel表中
int PointsCount = Convert.ToInt32(X.Length);
if (PointsCount > 0)
{
sht.Cells[1, col].Value = Title;
sht.Range[sht.Cells[2, col], sht.Cells[PointsCount + 1, col]].Value =
ExcelApp.WorksheetFunction.Transpose(X);
sht.Range[sht.Cells[2, col + 1], sht.Cells[PointsCount + 1, col + 1]].Value =
ExcelApp.WorksheetFunction.Transpose(Y);
col = (short)(col + 3);
}
}
if (blnExcelFileExists)
{
Wkbk.Save();
}
else
{
Wkbk.SaveAs(Filename: ExcelFilePath);
}
sht.Activate();
ExcelApp.Windows[Wkbk.Name].Visible = true; //取消窗口的隐藏
ExcelApp.Windows[Wkbk.Name].Activate();
ExcelApp.Visible = true;
if (ExcelApp.WindowState == XlWindowState.xlMinimized)
{
ExcelApp.WindowState = XlWindowState.xlNormal;
}
}
else
{
MessageBox.Show("此Word文档中没有可以进行数据提取的图表");
}
}
