private void GestionDuGraphe(Document document, List <int> valeursDuGraphe)
{
foreach (InlineShape inlineShape in document.InlineShapes)
{
if (inlineShape.HasChart == MsoTriState.msoTrue)
{
Microsoft.Office.Interop.Word.Chart graphique = inlineShape.Chart;
Workbook wb = graphique.ChartData.Workbook;
Worksheet ws = wb.Worksheets["Feuil1"];
ws.Range["B2"].Value = valeursDuGraphe[0];
ws.Range["B2"].Value = valeursDuGraphe[1];
ws.Range["C3"].Value = valeursDuGraphe[2];
ws.Range["D4"].Value = valeursDuGraphe[3];
graphique.Refresh();
graphique = null;
// Pour fermer proprement Excel
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
ws = null;
wb = null;
}
}
}
/// <summary>
/// 在指定位置上使用输入的二维数组插入图表
/// </summary>
/// <param name="type">插入的方式 (文件开始或文件结束位置）</param>
/// <param name="value">二维数组</param>
/// <param name="chartType">图表类型</param>
/// <param name="rowHeader">行标题（null表示显示index值）</param>
/// <param name="colHeader">列标题 (null表示显示Column1, Column2值)</param>
public void InsertGraph(InsertionPoint type, double[,] value, WordChartStyle chartType = null, string[] rowHeader = null, string[] colHeader = null)
{
try
{
Word.Range r = GetRange(type);
dynamic oShape;
WordChartStyle style = chartType == null ? _chartStyle : chartType;
if (_version > 12.0)
{
oShape = _wordShapes.AddChart2(-1, (XlChartType)style.ChartStyle, r);
}
else
{
oShape = _wordShapes.AddChart((XlChartType)style.ChartStyle, r);
}
//Demonstrate use of late bound oChart and oChartApp objects to
//manipulate the chart object with MSGraph.
Word.Chart oChart = oShape.Chart;
//object oChartApp = oChart.Application;
ArrayDataToGraphData(value, oChart, rowHeader, colHeader, style.FirstColumnAsXAxis);
}
catch (Exception ex)
{
ErrorParser(ex);
return;
}
}
public static void ImportExcel2(docx.Document wDoc, string excelfile)
{
//Microsoft.Office.Interop.Word.Application wApp = new Microsoft.Office.Interop.Word.Application();
//wApp.Visible = true;
//Document wDoc = wApp.Documents.Open(FileName: wordfile);
//wDoc.
Microsoft.Office.Interop.Word.Chart chart1 = wDoc.Shapes.AddChart().Chart;
xlsx.Workbook excelbook = chart1.ChartData.Workbook;
excelbook.Application.DisplayAlerts = false;
excelbook.ActiveSheet.Name = "___";
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = true;
xlsx.Workbook importBook = excelbook.Application.Workbooks.Open(excelfile);
foreach (xlsx.Worksheet s in importBook.Worksheets)
{
//s.Select();
s.Copy(After: excelbook.Sheets.get_Item(excelbook.Sheets.Count));
//excelbook.Sheets.Add();
//excelbook.ActiveSheet.paste();
}
//excelbook.Sheets.get_Item(1).delete();
importBook.Close();
//excelbook.Application.DisplayAlerts = true;
//excelbook.Sheets.Add();
//excelbook.Application.Visible = true;
//wDoc.Save();
//wApp.Quit();
}
//to check wether user has been open excel.
//User can open excel by two way, 1. click import excel button on rabbion; 2. right click the chart and choose edit data.
private static bool CheckExcel(ref xlsx.Application excel, docx.Document doc)
{
if (excel != null && excel.Visible == false)//has been close the excel application
{
excel.Quit();
excel = null;
}
if (excel == null)//meaning user do not click import button.
{
foreach (docx.InlineShape s in doc.InlineShapes)
{
if (s.HasChart == MsoTriState.msoTrue)
{
docx.Chart c = s.Chart;
try
{
excel = c.ChartData.Workbook.Application;
excel.Visible = true;
break;
}
catch {}//just abort the exception, because the user didn't click the edit data button.
}
}
}
if (excel == null || excel.ActiveWorkbook == null)
{
MessageBox.Show("Please import Excel file first!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
return(true);
}
public static void RefreshData(docx.Document doc)
{
doc.Application.ScreenUpdating = false;
docx.Chart c = null;
//foreach (docx.Shape s in doc.Shapes)
//{
// if (s.HasChart == MsoTriState.msoTrue)
// {
// c = s.Chart;
// c.ChartData.Activate();
// c.ChartData.Workbook.Close();
// }
//}
foreach (docx.InlineShape s in doc.InlineShapes)
{
if (s.HasChart == MsoTriState.msoTrue)
{
c = s.Chart;
c.ChartData.Activate();
c.ChartData.Workbook.Application.Visible = false;
c.ChartData.Workbook.Close();
}
}
doc.Application.ScreenUpdating = true;
}
public static void UpdateGraph1(string outputPath, double[] lastValues, double[] currentValues, int graphIndex)
{
Microsoft.Office.Interop.Word.Application app = new Microsoft.Office.Interop.Word.Application();
app.DisplayAlerts = Microsoft.Office.Interop.Word.WdAlertLevel.wdAlertsNone;
app.Visible = false;
object missing = System.Reflection.Missing.Value;
object templateName = outputPath; //最终的word文档需要写入的位置
object ModelName = outputPath; //word 模板的位置
object count = 1;
object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdLine; //换一行;
Microsoft.Office.Interop.Word.Document wordDoc = app.Documents.Open(ref ModelName, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing);//打开word模板
string[] titles = { "日期", "本周", "上周" };
string[] names = { "周五", "周六", "周日", "周一", "周二", "周三", "周四" }; // 数据名称
int fieldCount = names.Length;
try
{
Microsoft.Office.Interop.Word.InlineShape shape = wordDoc.InlineShapes[graphIndex];
Microsoft.Office.Interop.Word.Chart chart = shape.Chart;
chart.ChartData.Activate();
Microsoft.Office.Interop.Excel.Worksheet book = chart.ChartData.Workbook.Worksheets["Sheet1"];
var data = new object[fieldCount, 3];
for (int i = 0; i < fieldCount; i++)
{
data[i, 0] = names[i];
data[i, 1] = currentValues[i];
data[i, 2] = lastValues[i];
}
book.get_Range("A2", "C" + (fieldCount + 1)).Value = data;
book.get_Range("A1", "C1").Value = titles;
((Microsoft.Office.Interop.Excel.Range)book.Cells[1, titles.Length + 1]).Select();
((Microsoft.Office.Interop.Excel.Range)book.Cells[1, titles.Length + 1]).EntireColumn.Delete(0);
book.Application.DisplayAlerts = false;
book.Application.Quit();
object format = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatDocumentDefault;//保存格式
wordDoc.SaveAs(ref templateName, ref format, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
}
catch
{
}
finally
{
//关闭wordDoc，wordApp对象
object SaveChanges = Microsoft.Office.Interop.Word.WdSaveOptions.wdSaveChanges;
object OriginalFormat = Microsoft.Office.Interop.Word.WdOriginalFormat.wdWordDocument;
object RouteDocument = false;
wordDoc.Close(ref SaveChanges, ref OriginalFormat, ref RouteDocument);
app.Quit(ref SaveChanges, ref OriginalFormat, ref RouteDocument);
}
}
public static void InsertChart(docx.Document wDoc, xlsx.Workbook importBook)
{
docx.Chart chart1 = wDoc.InlineShapes.AddChart().Chart;
xlsx.Workbook excelbook = chart1.ChartData.Workbook;
//查找选中的chart
xlsx.Chart importchart = importBook.ActiveChart;
//foreach (xlsx.Series myser in importchart.SeriesCollection)
//{
// string temp = myser.Formula;
// temp = temp.Substring(9, temp.Length - 9);
//}
}
private void ArrayDataToGraphData(double[,] value, Word.Chart oChart, string[] rowHeader = null, string[] colHeader = null, bool firstColumnAsXAxis = true)
{
string data;
string rc;
string rHeader;
string cHeader;
Excel.Worksheet series = ((Excel.Workbook)oChart.ChartData.Workbook).Worksheets[1];
Excel.ListObject list = series.ListObjects[1];
string colIdx = firstColumnAsXAxis ? "B" : "A";
list.Resize(series.Cells.get_Range(colIdx + "1", ExcelReport.IntToLetter(value.GetLength(1) + 1) + (value.GetLength(0) + 1).ToString()));
for (int i = 0; i < value.GetLength(0); i++)
{
rHeader = "A" + (i + 2).ToString();
if (rowHeader != null)
{
series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = rowHeader[i];
}
else
{
series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = (i).ToString();
}
for (int j = 0; j < value.GetLength(1); j++)
{
data = value[i, j].ToString();
rc = ExcelReport.IntToLetter(j + 2) + (i + 2).ToString();
series.Cells.get_Range(rc, _missing).FormulaR1C1 = data;
cHeader = ExcelReport.IntToLetter(j + 2) + "1";
if (i == 0)
{
if (colHeader != null)
{
series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = colHeader[j];
}
else
{
series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = "Column" + (j).ToString();
}
}
}
}
series.Activate();
((Excel.Workbook)oChart.ChartData.Workbook).Close();
Thread.Sleep(5);
}
public static void Graphics(List <DataPoint> Values, string chartName = null, string legend = null, excel.XlChartType type = excel.XlChartType.xlColumnClustered)
{
if (Values.Count == 0)
{
return;
}
try
{
Supports.GetProfileForm().ChangeState();
word.Application app = new word.Application();
app.Visible = true;
Thread.Sleep(500);
var doc = app.Documents.Add();
word.InlineShape chartShape = doc.InlineShapes.AddOLEObject((Microsoft.Office.Core.XlChartType)type);
chartShape.AlternativeText = "Chart1";
word.Chart chart = chartShape.OLEFormat.Object as word.Chart;
dynamic book = chart.ChartData.Workbook;
//dynamic bookTable = book.Sheets[1].ListObjects("Table1");
//bookTable.DataBodyRange.ClearContents();
//Thread.Sleep(500);
//excel.Chart chart = exApp.Charts[0];
//chart.SetSourceData(exApp.Cells.Range["A2", "E15"]);
//exApp.Cells.Range["A2", "E5"].Clear();
//sheert.ListObjects.Item[1].Resize(sheert.Range["A1", "B" + (Values.Count + 1).ToString()]);
//sheert.Cells[1, 2] = legend;
for (var i = 2; i < Values.Count + 2; i++)
{
//exApp.Cells[i, 1] = DateTime.FromOADate(Values[i - 2].XValue);
//exApp.Cells[i, 2] = Values[i - 2].YValues[0];
}
}
catch (Exception e)
{
MessageBoxTi.Show("Documentation.Graphics " + e.Message);
return;
}
finally
{
Supports.GetProfileForm().ChangeState(true);
}
}
/// <summary>
/// 处理图表
/// </summary>
/// <param name="element"></param>
/// <returns></returns>
private bool SetChart(WordElement element)
{
if (element.Range.InlineShapes != null && element.Range.InlineShapes.Count > 0)
{
List <Word.InlineShape> chartList = element.Range.InlineShapes.Cast <Word.InlineShape>().Where(m => m.Type == Word.WdInlineShapeType.wdInlineShapeChart).ToList();
string startWith = "chart_" + element.Level.ToString() + "_";
foreach (Word.InlineShape item in chartList)
{
Word.Chart chart = item.Chart;
if (!string.IsNullOrEmpty(chart.ChartTitle.Text) && chart.ChartTitle.Text.StartsWith(startWith))
{
WordElement chartElement = new WordElement(null, chart.ChartTitle.Text, element.DataRow);
DataTable dataTable = dataSource.Tables[chartElement.TableIndex];
DataRow[] dataRows = dataTable.Select(chartElement.GroupByString);
int columnCount = dataTable.Columns.Count;
List <int> columns = new List <int>();
foreach (var dr in dataRows)
{
for (int i = chartElement.ColumnStart == -1 ? 0 : chartElement.ColumnStart - 1; i < (chartElement.ColumnEnd == -1 ? columnCount : chartElement.ColumnEnd); i++)
{
if (columns.Contains(i) || dr[i] == null || string.IsNullOrEmpty(dr[i].ToString()))
{
}
else
{
columns.Add(i);
}
}
}
columns.Sort();
columnCount = columns.Count;
int rowsCount = dataRows.Length;
Word.ChartData chartData = chart.ChartData;
//chartData.Activate();
//此处有个比较疑惑的问题, 不执行此条,生成的报告中的图表无法再次右键编辑数据. 执行后可以, 但有两个问题就是第一会弹出Excel框, 处理完后会自动关闭. 第二部分chart的数据range设置总不对
//不知道是不是版本的问题, 谁解决了分享一下,谢谢
Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;
dataWorkbook.Application.Visible = false;
Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1];
//设定范围
string a = (chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount) + "|" + columnCount;
Console.WriteLine(a);
Excel.Range tRange = dataSheet.Range["A1", dataSheet.Cells[(chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount), columnCount]];
Excel.ListObject tbl1 = dataSheet.ListObjects[1];
//dataSheet.ListObjects[1].Delete(); //想过重新删除再添加 这样 原有数据清掉了, 但觉得性能应该会有所下降
//Excel.ListObject tbl1 = dataSheet.ListObjects.AddEx();
tbl1.Resize(tRange);
for (int j = 0; j < rowsCount; j++)
{
DataRow row = dataRows[j];
for (int k = 0; k < columnCount; k++)
{
dataSheet.Cells[j + 2, k + 1].FormulaR1C1 = row[columns[k]];
}
}
if (chartElement.ColumnNameForHead)
{
for (int k = 0; k < columns.Count; k++)
{
dataSheet.Cells[1, k + 1].FormulaR1C1 = dataTable.Columns[columns[k]].ColumnName;
}
}
chart.ChartTitle.Text = chartElement.Name;
//dataSheet.Application.Quit();
}
}
}
return(true);
}
/// <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文档中没有可以进行数据提取的图表");
}
}
