非正规打法搞定C#动态生成 Word图表 (MSGraph)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/geyunfei_hit/article/details/2513585

新工作,新环境,很任务 最近刚换份工作,找了个小程序员当,想踏实的学几年技术,一进来,BOSS就给出了个难题,

---用C# 动态生成含有报表图表的word文件.

刚开始,觉得没什么难的,不就是一个图表吗? 原来也做过基于模板的Excel报表,应该没问题..

从网上先找一个范例研究一下....

给果,大失所望,在网上只找到了一个如下的所谓"范例":

 

object oMissing = System.Reflection.Missing.Value;
    object oEndOfDoc = "//endofdoc"; /* /endofdoc is a predefined bookmark */

    //Start Word and create a new document.
    Word._Application oWord;
    Word._Document oDoc;
    oWord = new Word.Application();
    oWord.Visible = true;
    oDoc = oWord.Documents.Add(ref oMissing, ref oMissing,
        ref oMissing, ref oMissing);

    //Insert a paragraph at the beginning of the document.
    Word.Paragraph oPara1;
    oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing);
    oPara1.Range.Text = "Heading 1";
    oPara1.Range.Font.Bold = 1;
    oPara1.Format.SpaceAfter = 24;    //24 pt spacing after paragraph.
    oPara1.Range.InsertParagraphAfter();

    //Insert a paragraph at the end of the document.
    Word.Paragraph oPara2;
    object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oPara2 = oDoc.Content.Paragraphs.Add (ref oRng);
    oPara2.Range.Text = "Heading 2";
    oPara2.Format.SpaceAfter = 6;
    oPara2.Range.InsertParagraphAfter();

    //Insert another paragraph.
    Word.Paragraph oPara3;
    oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
    oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:";
    oPara3.Range.Font.Bold = 0;
    oPara3.Format.SpaceAfter = 24;
    oPara3.Range.InsertParagraphAfter();

    //Insert a 3 x 5 table, fill it with data, and make the first row
    //bold and italic.
    Word.Table oTable;
     Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oTable = oDoc.Tables.Add(wrdRng, 3, 5, ref oMissing, ref oMissing);
    oTable.Range.ParagraphFormat.SpaceAfter = 6;
    int r, c;
    string strText;
    for(r = 1; r <= 3; r++)
        for(c = 1; c <= 5; c++)
        {
            strText = "r" + r + "c" + c;
            oTable.Cell(r, c).Range.Text = strText;
        }
    oTable.Rows[1].Range.Font.Bold = 1;
    oTable.Rows[1].Range.Font.Italic = 1;

    //Add some text after the table.
    Word.Paragraph oPara4;
    oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oPara4 = oDoc.Content.Paragraphs.Add(ref oRng);
    oPara4.Range.InsertParagraphBefore();
    oPara4.Range.Text = "And here's another table:";
    oPara4.Format.SpaceAfter = 24;
    oPara4.Range.InsertParagraphAfter ();

    //Insert a 5 x 2 table, fill it with data, and change the column widths.
    wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oTable = oDoc.Tables.Add(wrdRng, 5, 2, ref oMissing, ref oMissing);
    oTable.Range.ParagraphFormat.SpaceAfter = 6;
    for(r = 1; r <= 5; r++)
        for(c = 1; c <= 2; c++)
        {
            strText = "r" + r + "c" + c;
            oTable.Cell (r, c).Range.Text = strText;
        }
    oTable.Columns[1].Width = oWord.InchesToPoints(2); //Change width of columns 1 & 2
    oTable.Columns[2].Width = oWord.InchesToPoints(3);

    //Keep inserting text. When you get to 7 inches from top of the
    //document, insert a hard page break.
    object oPos;
    double dPos = oWord.InchesToPoints(7);
    oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertParagraphAfter();
    do
    {
        wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
        wrdRng.ParagraphFormat.SpaceAfter = 6;
        wrdRng.InsertAfter("A line of text");
        wrdRng.InsertParagraphAfter();
        oPos = wrdRng.get_Information
                       (Word.WdInformation.wdVerticalPositionRelativeToPage);
    }
    while(dPos >= Convert.ToDouble(oPos));
    object oCollapseEnd = Word.WdCollapseDirection.wdCollapseEnd;
    object oPageBreak = Word.WdBreakType.wdPageBreak;
    wrdRng.Collapse(ref oCollapseEnd);
    wrdRng.InsertBreak(ref oPageBreak);
    wrdRng.Collapse(ref oCollapseEnd);
    wrdRng.InsertAfter("We're now on page 2. Here's my chart:");
    wrdRng.InsertParagraphAfter();

    //Insert a chart.
    Word.InlineShape oShape;
    object oClassType = "MSGraph.Chart.8";
    wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    oShape = wrdRng.InlineShapes.AddOLEObject(ref oClassType, ref oMissing,
        ref oMissing, ref oMissing, ref oMissing,
        ref oMissing, ref oMissing, ref oMissing);

    //Demonstrate use of late bound oChart and oChartApp objects to
    //manipulate the chart object with MSGraph.
    object oChart;
    object oChartApp;
    oChart = oShape.OLEFormat.Object;
    oChartApp = oChart.GetType().InvokeMember("Application",
         BindingFlags.GetProperty, null, oChart, null);

    //Change the chart type to Line.
    object[] Parameters = new Object[1];
    Parameters[0] = 4; //xlLine = 4
    oChart.GetType().InvokeMember("ChartType", BindingFlags.SetProperty,
        null, oChart, Parameters);

    //Update the chart image and quit MSGraph.
    oChartApp.GetType().InvokeMember("Update",
        BindingFlags.InvokeMethod, null, oChartApp, null);
    oChartApp.GetType().InvokeMember("Quit",
        BindingFlags.InvokeMethod, null, oChartApp, null);
    //... If desired, you can proceed from here using the Microsoft Graph
    //Object model on the oChart and oChartApp objects to make additional
    //changes to the chart.

    //Set the width of the chart.
    oShape.Width = oWord.InchesToPoints(6.25f);
    oShape.Height = oWord.InchesToPoints(3.57f);

    //Add text after the chart.
    wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
    wrdRng.InsertParagraphAfter();
    wrdRng.InsertAfter("THE END.");

    //Close this form.
    this.Close();

对着代码研究一下,无法就是新建文字段之类的,而看到我最关注的报表时,大失所望,只有这么可怜的一段..

//Demonstrate use of late bound oChart and oChartApp objects to
    //manipulate the chart object with MSGraph.
    object oChart;
    object oChartApp;
    oChart = oShape.OLEFormat.Object;
    oChartApp = oChart.GetType().InvokeMember("Application",
         BindingFlags.GetProperty, null, oChart, null);

    //Change the chart type to Line.
    object[] Parameters = new Object[1];
    Parameters[0] = 4; //xlLine = 4
    oChart.GetType().InvokeMember("ChartType", BindingFlags.SetProperty,
        null, oChart, Parameters);

    //Update the chart image and quit MSGraph.
    oChartApp.GetType().InvokeMember("Update",
        BindingFlags.InvokeMethod, null, oChartApp, null);
    oChartApp.GetType().InvokeMember("Quit",
        BindingFlags.InvokeMethod, null, oChartApp, null);
    //... If desired, you can proceed from here using the Microsoft Graph
    //Object model on the oChart and oChartApp objects to make additional
    //changes to the chart.

MSGraph是什么? 怎么动态设定数据? 上述可怜的代码中都没有提,最郁闷的是,所有调用都以InvokMember实现,

这意味着如果看不到真正的说明,跟本无法知道调用的接口是什么....

上网去找MSGraph.Chart.8

还是一无所获......

头一次遇到这种问题,感觉头都胀了....

静下心来想一下,没办法,想办法找这个动态连接库吧..

先全C盘找MSGraph.Dll  一无所获,后来在调试时,发现在生成图表时,会有一个Graph的进程,去搜它,结果在office安装目录 office11下找到了. 将这个程序加到引用中,用对像浏览器浏览...结果..

呵呵 ,看到了下面的东东

 哈哈,应该就是这个了.试探了几个函数,果然没错.

下一步,就是设计一个Word文档模板,进行测试了.

先新建一个word文件,插入一个图表,之后给这个图表设置好书签,保存为模板Report(如果这些你不明白,建议先去学一下word...)

这回,再在程序里做如下测试:

 object oMissing = System.Reflection.Missing.Value;
            object oEndOfDoc = "//endofdoc"; /* /endofdoc is a predefined bookmark */
            object remarkOverTable = "OverTable";

            //Start Word and create a new document.
            Microsoft.Office.Interop.Word._Application oWord;
            Microsoft.Office.Interop.Word._Document oDoc;
            oWord = new Microsoft.Office.Interop.Word.Application();
            oWord.Visible = true ;
            object  Template = "Report.dot";
            oDoc = oWord.Documents.Add(ref Template, ref oMissing,
                ref oMissing, ref oMissing);

            //得到书签
            Microsoft.Office.Interop.Word.Range TestRemark = oDoc.Bookmarks.get_Item(ref remarkOverTable).Range;


            TestRemark.InlineShapes[0].OLEFormat.Open();// 打开OLE对像,注意这一步一定要有
            Microsoft.Office.Interop.Graph.Chart _testChart = 
                (Microsoft.Office.Interop.Graph.Chart)(TestRemark.InlineShapes[0].OLEFormat.Object);
            Microsoft.Office.Interop.Graph.Application _testApp =
                _testChart.Application;
            object[] Values = new object[] { "91%", "92%", "93%", "94%", "95%" };
            for (int i = 0, j = System.Convert.ToInt32('A'); i < Values.Length; i++)
            {
                //这里的行列式为循环下来填写的是A2,B2,C2,D2.... OK?
                _testChart.Application.DataSheet.Cells.set_Item(2, System.Convert.ToString(
                    (char)( j+ i)),
                    Values[0]);
            }
            _testApp.Quit();
           

大功告成,哈哈

写的有点乱,但是有如下建议:

1.对像浏览器是一个很好用的东西,在一些情况下,用其去看未知DLL的接口,不失为一种好选择.

2.在调试过程中,"监视"窗体其实是一个很用的对外接口,对于刚得到接口的DLL,在不知道具体函数,类型的功能时,可以在程序声明,实例化对像后设置断点,在监视窗体中试验性的执行各种函数,以了解其功能.

展开阅读全文

C#控制word里的图表

09-06

[img=http://hi.csdn.net/attachment/201109/6/78129_1315310811rrlj.png][/img]rnrn上面的图,是word里的一个图表,我现在想用代码控制里面的数值变化,从而改变图表。有没有办法实现?rnrnrn[code=C#] rnpublic static void a()rn rn Microsoft.Office.Interop.Word.Application appWord = null;//应用程序rn Microsoft.Office.Interop.Word.DocumentClass doc = null;//文档rn tryrn rn appWord = new Microsoft.Office.Interop.Word.Application();rn appWord.Visible = false;rn object objTrue = true;rn object objFalse = false;rn object objTemplate = "f:\\1.doc";//Server.MapPath("person.dot");//模板路径rn object objDocType = WdDocumentType.wdTypeDocument;rn doc = (DocumentClass)appWord.Documents.Add(ref objTemplate, ref objFalse, ref objDocType, ref objTrue);rnrnrn //int c = doc.grrnrnrn //第一步生成word文档rn //定义书签变量rn object obDD_Name = "bm_Name";//姓 名rn object obDD_Sex = "bm_Sex";//性 别rn object obDD_Birthday = "bm_Birthday"; //出生年月rn object obpic = "pic";rn object obtable = "obtable";rn object Nothing = System.Reflection.Missing.Value;rn //InlineShape shape = appWord.Selection.InlineShapes.AddPicture(@"F:\Picture\_DSC1602.JPG", ref Nothing, ref Nothing, ref Nothing);rn //doc.Bookmarks 这里可获取word里的书签对象,我想能不能获取word里的所有图表对象,那样应该就能控制单个图表了。rn Dictionary dic = new Dictionary();rn foreach (Bookmark bk in doc.Bookmarks)rn rn dic.Add(bk.Name, bk);rn rn rn //第三步 给书签赋值rn //给书签赋值rn doc.Bookmarks.get_Item(ref obDD_Name).Range.Text = "张三"; //姓 名rn doc.Bookmarks.get_Item(ref obDD_Sex).Range.Text = "男";//性 别rn doc.Bookmarks.get_Item(ref obDD_Birthday).Range.Text = "24";//年龄rn doc.Bookmarks.get_Item(ref obpic).Range.InlineShapes.AddPicture(@"F:\_DSC1602.JPG", ref Nothing, ref Nothing, ref Nothing);rnrnrn //文档中插入表格rn //doc.Bookmarks.get_Item(ref obtable).Range.Tables.Add(doc.Bookmarks.get_Item(ref obtable).Range, 12, 3, ref Nothing, ref Nothing);rn Microsoft.Office.Interop.Word.Table newTable = doc.Tables.Add(doc.Bookmarks.get_Item(ref obtable).Range, 12, 3, ref Nothing, ref Nothing);rn newTable.Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle;rn newTable.Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle;rn //给文档的最后一行再添加内容rn doc.Paragraphs.Last.Range.Text = "";rnrn //第四步 生成wordrn object filename = "F:\\1.doc";rn object miss = System.Reflection.Missing.Value;rn doc.SaveAs(ref filename, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss);rn object missingValue = Type.Missing;rn object doNotSaveChanges = WdSaveOptions.wdDoNotSaveChanges;rn doc.Close(ref doNotSaveChanges, ref missingValue, ref missingValue);rn appWord.Application.Quit(ref miss, ref miss, ref miss);rn doc = null;rn appWord = null;rnrn rn catch (System.Exception ex)rn rn //捕捉异常,如果出现异常则清空实例,退出word,同时释放资源rn string aa = ex.ToString();rn object miss = System.Reflection.Missing.Value;rn object missingValue = Type.Missing;rn object doNotSaveChanges = WdSaveOptions.wdDoNotSaveChanges;rn doc.Close(ref doNotSaveChanges, ref missingValue, ref missingValue);rn appWord.Application.Quit(ref miss, ref miss, ref miss);rn doc = null;rn appWord = null;rn rn rnrn[/code]rnrn我要做的是生成一个复杂的word文档,里面白表格、图片、图表什么的,现在就差不能控制图表了,大家给点意见 论坛

@@关于在C#动态生成excel图表问题?

07-27

关于在程序中生成excel图表,在网上也有不少例子,实现的方法也有多种,有用vba宏的、有用GDI划的。不过一个个试来都得不到理想的效果。rn我的需求其实很简单,就是在程序中得到数据集,然后生成excel文件,并根据这些数据再生成诸如柱状图、饼图类的图表。rn完整程序如下:rnusing System;rnusing System.Collections;rnusing System.ComponentModel;rnusing System.Data;rnusing System.Drawing;rnusing System.Web;rnusing System.Web.SessionState;rnusing System.Web.UI;rnusing System.Web.UI.WebControls;rnusing System.Web.UI.HtmlControls;rnusing Microsoft.Office.Interop.Excel;rnrnnamespace saveToexcelrnrn /// rn /// WebForm1 的摘要说明。rn /// rn public class WebForm1 : System.Web.UI.Pagern rn //定义所要使用的Excel对象rn Application ThisApplication = null;rn Workbooks m_objBooks = null;rn _Workbook ThisWorkbook = null;rn protected System.Web.UI.WebControls.Button Button1;rn Worksheet xlSheet = null;rn private void Page_Load(object sender, System.EventArgs e)rn rn // 在此处放置用户代码以初始化页面rn rnrn /// rn /// 删除多余的Sheetrn /// rn private void DeleteSheet()rn rn foreach (Worksheet ws in ThisWorkbook.Worksheets)rn if (ws != ThisApplication.ActiveSheet)rn rn ws.Delete();rn rn foreach (Chart cht in ThisWorkbook.Charts)rn cht.Delete();rn rnrn /// rn /// 创建一个Sheet,用来存数据rn /// rn private void AddDatasheet()rn rn xlSheet = (Worksheet)ThisWorkbook.rn Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet,rn Type.Missing, Type.Missing);rnrn xlSheet.Name = "数据";rn rnrn /// rn /// 用生成的随机数作数据,实际情况数据可以从数据库中取出rn /// rn private void LoadData()rn rn Random ran = new Random();rn for (int i = 1; i <= 12; i++)rn rn xlSheet.Cells[i, 1] = i.ToString()+"月";rn xlSheet.Cells[i, 2] = ran.Next(2000).ToString();rn xlSheet.Cells[i, 3] = "2008-7-" + i.ToString(); rn xlSheet.Cells[i, 4] ="北京欢迎你";rn rn rnrn /// rn /// 创建统计图 rn /// rn private void CreateChart()rn rnrn // TODO: 生成一个统计图对象:rn Chart xlChart = (Chart)ThisWorkbook.Charts.rn Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);rnrn // TODO: 设定数据来源rn Range cellRange = (Range)xlSheet.Cells[1, 1];rn //Range cellRange = (Range)xlSheet.get_Range("A1","B1");rn //Range(xlSheet.Cells(2, 1),xlSheet.Cells(3, 1)).Select;rnrn // TODO: 通过向导生成Chartrn xlChart.ChartWizard(cellRange.CurrentRegion ,rn XlChartType.xl3DColumn, Type.Missing,rn XlRowCol.xlColumns, 1, 0, true,rn "访问量比较", "日期", "访问量",rn "");rnrn // TODO: 设置统计图Sheet的名称rn xlChart.Name = "统计";rnrn // TODO: 让12个Bar都显示不同的颜色rn ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);rn grp.GapWidth = 20;rn grp.VaryByCategories = true;rnrn // TODO: 让Chart的条目的显示形状变成圆柱形,并给它们显示加上数据标签rn Series s = (Series)grp.SeriesCollection(1);rn s.BarShape = XlBarShape.xlCylinder;rn s.HasDataLabels = true;rnrn // TODO: 设置统计图的标题和图例的显示rn xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;rn xlChart.ChartTitle.Font.Size = 24;rn xlChart.ChartTitle.Shadow = true;rn xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;rnrn // TODO: 设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴rn Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);rn valueAxis.AxisTitle.Orientation = -90;rnrn Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);rn categoryAxis.AxisTitle.Font.Name = "MS UI Gothic";rn rnrnrnrn #region Web 窗体设计器生成的代码rn override protected void OnInit(EventArgs e)rn rn //rn // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。rn //rn InitializeComponent();rn base.OnInit(e);rn rn rn /// rn /// 设计器支持所需的方法 - 不要使用代码编辑器修改rn /// 此方法的内容。rn /// rn private void InitializeComponent()rn rn this.Button1.Click += new System.EventHandler(this.Button1_Click);rn this.Load += new System.EventHandler(this.Page_Load);rnrn rn #endregionrnrn private void Button1_Click(object sender, System.EventArgs e)rn rn string sPath = Server.MapPath("TestExcel2.xls");rn tryrn rn ThisApplication = new Application();rn m_objBooks = (Workbooks)ThisApplication.Workbooks;rn ThisWorkbook = (_Workbook)(m_objBooks.Add(Type.Missing));rnrn ThisApplication.DisplayAlerts = false;rnrn DeleteSheet();rn AddDatasheet();rn LoadData();rn CreateChart();rnrn ThisWorkbook.SaveAs(sPath, Type.Missing, Type.Missing,rn Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,rn Type.Missing, Type.Missing, Type.Missing, Type.Missing,rn Type.Missing);rnrn rn catch (Exception ex)rn rn Response.Write(ex.Message);rn rn finallyrn rn ThisWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);rn ThisApplication.Workbooks.Close();rnrn ThisApplication.Quit();rn System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisWorkbook);rn System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisApplication);rn ThisWorkbook = null;rn ThisApplication = null;rn GC.Collect();rn rn rn rnrn这是根据网上一个例子稍加改动后做的,不过与我想要的有些差距。我也查过一些资料,但还是没得到理想的效果。rn主要有两个问题:rn(1)在生成图表时,它是把所有的列都当作了数据源,而我只想要数据列和日期列,也就是B列和C列,让日期(C列)作为x轴。值(B列)是圆柱体值。rn(2)怎样选择在生成图表时的样式,如是要生成圆柱图、饼图、曲线图类的,我也试着改过xlChart.ChartWizard(cellRange.CurrentRegion ,XlChartType.xl3DColumn, Type.Missing,XlRowCol.xlColumns, 1, 0, true,"访问量比较", "日期", "访问量","")中的XlChartType.xl3DColumn,好像不起作用。rn友请诸如孟子、思归等各路高手,先谢谢了,呵呵。 论坛

没有更多推荐了,返回首页