Word中插入表格与柱状图饼状图技术经验分享

    最近公司一个项目里要求自动生成报告功能，研究了1周多，主要实现方式是通过调用微软Office COM组件来实现操作word文档，生成段落，表格，及各种图表。

本人发现操作word地方也有几个坑人的地方。

   1.画柱状图时，要想设置图形的标题，你必须先设置 chart.HasTitle = true;不然chart.ChartTitle.Text会引发异常,这儿很奇怪饼图并不需要这么设置，微软程序员也有坑人的时候。

   2.在多线程操作生成word报告时，必须加锁，否则 Microsoft.Office.Interop.Word.Chart chart = WordDoc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumnClustered, range).Chart;这个会调用EXcel，都两个线程都执行到这儿的时候，会提示

  3.报告生成完成后必须杀死Excel.exe进程。否则的话进程会越来越多，直到内存溢出。

 try
            {

                //System.Data.DataTable dt = LoadDataFromExcel("C://Users//Admin//Desktop//35c9a52cf739423fb0bf4231b0906bf7//TACLAC匹配核查 .xlsx");
                System.Data.DataTable dt = Operate.ReadEXCEL("C:\\Users\\Admin\\Desktop\\35c9a52cf739423fb0bf4231b0906bf7\\TACLAC.xlsx");


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

                //模板文件地址，这里假设在X盘根目录
                object oTemplate = "C://Users//Admin//Desktop//NetworkCheck.dotx";
                //Start Word and create a new document.   
                Word.Application oWord;

                Word.Document oDoc;
                oWord = new Word.Application();
                oWord.Visible = false;
                oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing);

                添加页眉   
                //String HeaderText = "网络健康检查报告";
                //Word.WdParagraphAlignment wdAlign = Word.WdParagraphAlignment.wdAlignParagraphCenter;
                //Word.WdColor fontcolor = Word.WdColor.wdColorBlue;
                //float fontsize = 10;
                //AddHeader.AddSimpleHeader(oWord, HeaderText, wdAlign, fontcolor, fontsize);

                //Insert a paragraph at the beginning of the document.   
                //Word.Paragraph oPara1;
                //oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing);
                //oPara1.Range.Text = "1.TAC/LAC插花核查";
                //oPara1.Range.Font.Bold = 1;
                //oPara1.Format.SpaceAfter = 24;    //24 pt spacing after paragraph.   
                //oPara1.Range.InsertParagraphAfter();
                //oDoc.Bookmarks.Add("report1Tb", oPara1.Range);
                Word.Paragraph oPara2;
                oPara2 = oDoc.Content.Paragraphs.Add(ref oMissing);
                oPara2.Format.SpaceAfter = 6;
                oPara2.Range.Text = "";
                oPara2.Range.InsertParagraphAfter();
                oDoc.Bookmarks.Add("report1Chart", oPara2.Range);

                //Word.Paragraph oPara3;
                //oPara3 = oDoc.Content.Paragraphs.Add(ref oMissing);
                //oPara3.Format.SpaceAfter = 6;
                //oPara3.Range.Text = "";
                //oPara3.Range.InsertParagraphAfter();
                //oDoc.Bookmarks.Add("report1Excel", oPara3.Range);

                插入表格
                //OperateWord.AddSimpleTable("report1Tb", oDoc, dt);


                //Insert a chart.   
                //object oEndOfDoc1 = "report1Chart";

                //string[] arrayNames = new string[dt.Rows.Count];
                //int[] arrayValues = new int[dt.Rows.Count];
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    arrayNames[i] =dt.Rows[i]["问题分类"].ToString();
                //    arrayValues[i] =Convert.ToInt32(dt.Rows[i]["数量"]);
                //}
                //OperateWord.AddPieChart(oDoc, oEndOfDoc1,arrayNames,arrayValues,"汇总");
                OperateWord.AddBarChart("report1Chart", oDoc);
                object mark = "report1Excel";
               // OperateWord.AddExcelObject(oWord, oDoc, mark);
                string path = System.AppDomain.CurrentDomain.BaseDirectory;
                Random rd = new Random();
                string wordFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + rd.Next(100)+".docx";
                Object fileName = (Object)String.Concat(path, wordFileName);
                Object fileFormat = (Object)Word.WdSaveFormat.wdFormatDocument;


                oDoc.SaveAs(ref fileName, ref oMissing, ref oMissing, ref oMissing,
                ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                ref oMissing, ref oMissing);
                oDoc.Close(ref oMissing, ref oMissing, ref oMissing);
                //关闭word
                oWord.Quit(ref oMissing, ref oMissing, ref oMissing);
                //Close this form.   
            }
            catch (Exception ex)
            {

            }
            finally
            {

            }

using System;
using System.Linq;
using Wd = Microsoft.Office.Interop.Word;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace Utils.Word
{
    public class OperateWord
    {
        public static readonly object objChar = new object();//锁住生成图表防止并行插入图表引起错误
        
        //增加一个table
        public static void AddSimpleTable(string bookmark, Wd.Document WordDoc, System.Data.DataTable dt)
        {
            try
            {
                Wd.WdColor oColor = Wd.WdColor.wdColorPaleBlue;
                Object Nothing = System.Reflection.Missing.Value;
                object oStart = bookmark;
                Wd.Range range = WordDoc.Bookmarks.get_Item(ref oStart).Range;//表格插入位置
                int numrows = dt.Rows.Count;
                int numcolumns = dt.Columns.Count;
                //文档中创建表格   
                Microsoft.Office.Interop.Word.Table newTable = WordDoc.Tables.Add(range, numrows + 1, numcolumns, ref Nothing, ref Nothing);
                //设置表格样式   
                newTable.Borders.OutsideLineStyle = Wd.WdLineStyle.wdLineStyleSingle;
                newTable.Borders.InsideLineStyle = Wd.WdLineStyle.wdLineStyleSingle;
                newTable.PreferredWidthType = Wd.WdPreferredWidthType.wdPreferredWidthPercent;
                newTable.PreferredWidth = 100;//设置为100%显示
                for (int c = 1; c <= numcolumns; c++)
                {
                    //newTable.Columns[c].Width = 100f;
                    newTable.Cell(1, c).Range.Text = dt.Columns[c - 1].ColumnName;//表头
                    newTable.Cell(1, c).Range.Font.Bold = 1;//表头加粗
                    newTable.Cell(1, c).Range.Shading.BackgroundPatternColor = oColor;
                }
                for (int r = 1; r <= numrows; r++)
                {
                    for (int c = 1; c <= numcolumns; c++)
                    {

                        //填充表格内容   
                        newTable.Cell((r + 1), c).Range.Text = dt.Rows[r - 1][c - 1].ToString();
                        if (c == 1)
                        {
                            newTable.Cell((r + 1), c).Range.Font.Bold = 1;//列表第一列加粗
                        }
                    }

                }

                //在表格中增加行   
                //WordDoc.Content.Tables[1].Rows.Add(ref Nothing);
            }
            catch (Exception ex)
            {

            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="WordDoc"></param>
        /// <param name="bookMark"></param>
        /// <param name="arrayNames">名称集合</param>
        /// <param name="arrayValues">值集合</param>
        /// <param name="chartName">图表名称</param>
        //画饼状图
        public static void AddPieChart(Wd.Document WordDoc, object bookMark, string[] arrayNames, int[] arrayValues, string chartName)
        {
            Workbook workbook = null;
            try
            {
                lock (objChar)
                {
                    object obookMark = bookMark;
                    Wd.Bookmark thisMark = WordDoc.Bookmarks.get_Item(ref obookMark);


                    object istart = thisMark.Start;
                    object iend = thisMark.End;
                    Microsoft.Office.Interop.Word.Range range = WordDoc.Range(ref istart, ref iend);

                    string[] names = arrayNames;
                    int[] values = arrayValues;

                    int count = names.Length;
                    Microsoft.Office.Interop.Word.Chart chart = WordDoc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DPie, range).Chart;
                    chart.ChartTitle.Text = chartName;
                    chart.ChartTitle.Font.Size = 10;
                    Microsoft.Office.Interop.Word.Series s;


                    workbook = ((Workbook)chart.ChartData.Workbook);
                    Worksheet book = workbook.Worksheets["Sheet1"];
                    //workbook.Application.ScreenUpdating = false;
                    workbook.Application.WindowState = XlWindowState.xlMinimized;
                    workbook.Application.DisplayAlerts = false;
                    //workbook.Application.Visible = false;
                    var data = new object[count, 2];
                    Enumerable.Range(0, count).ToList().ForEach(i =>
                    {
                        data[i, 0] = names[i];
                        data[i, 1] = values[i];
                    });
                    book.Cells.ClearContents();
                    //book.get_Range("A2", "B" + (count + 1)).Value = data;
                    Microsoft.Office.Interop.Excel.Range rng = book.get_Range("A2", "A2");
                    rng.get_Resize(data.GetUpperBound(0) + 1, data.GetUpperBound(1) + 1).Value2 = data;
                    chart.SetSourceData("'Sheet1'!" + rng.get_Resize(data.GetUpperBound(0) + 1, data.GetUpperBound(1) + 1).get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing), Type.Missing);


                    s = (Microsoft.Office.Interop.Word.Series)chart.SeriesCollection(1);
                    s.Shadow = false;
                    s.HasDataLabels = true;
                    //显示百分比  
                    s.DataLabels().ShowPercentage = true;
                    s.DataLabels().Font.Size = 8;
                    //显示分组名  
                    s.DataLabels().ShowCategoryName = true;
                    //显示数值  
                    s.DataLabels().ShowValue = true;
                    //显示格式代码
                    s.DataLabels().NumberFormat = "0.00%";
                    s.HasLeaderLines = true;

                    book.get_Range("B1").Value = chartName;



                    workbook.RefreshAll();
                    chart.Refresh();

                   
                    workbook.Close();//结束EXCEL进程
                    workbook.Application.Quit();
                    KillSpecialExcel(workbook.Application);//杀死excel进程
                    System.Threading.Thread.Sleep(100);
                }
            }
            catch (Exception ex)
            {
                if (workbook != null)
                {
                    try
                    {
                        workbook.Close();//结束EXCEL进程
                    }
                    catch (Exception ex1)
                    {


                    }
                    KillSpecialExcel(workbook.Application);//杀死excel进程
                }
            }

        }

        /// <summary>
        /// 画柱状图
        /// </summary>
        /// <param name="WordDoc"></param>
        /// <param name="bookMark"></param>
        /// <param name="data">图数据</param>
        /// <param name="chartName">图名称</param>
        public static void AddColumnChart(Wd.Document WordDoc, object bookMark, object[,] data, string chartName)
        {
            Workbook workbook = null;
            try
            {
                lock (objChar)
                {
                    Wd.Bookmark thisMark = WordDoc.Bookmarks.get_Item(ref bookMark);

                    object istart = thisMark.Start;
                    object iend = thisMark.End;
                    Microsoft.Office.Interop.Word.Range range = WordDoc.Range(ref istart, ref iend);

                    if (thisMark.Range.InlineShapes.Count > 0)
                    {
                        Wd.InlineShape shape = thisMark.Range.InlineShapes[1];
                        shape.Delete();
                    }

                    Microsoft.Office.Interop.Word.Chart chart = WordDoc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumnClustered, range).Chart;

                    chart.Legend.Position = Microsoft.Office.Interop.Word.XlLegendPosition.xlLegendPositionBottom;
                    chart.HasTitle = true;//先将标题设为true，不然chart.ChartTitle.Text会引发异常,这儿很奇怪饼图并不需要这么设置，微软程序员也有坑人的时候。
                    chart.ChartTitle.Text = chartName;
                    chart.ChartTitle.Font.Size = 10;
                    workbook = ((Workbook)chart.ChartData.Workbook);
                    //workbook.Application.ScreenUpdating = false;
                    workbook.Application.WindowState = XlWindowState.xlMinimized;
                    workbook.Application.DisplayAlerts = false;
                    //workbook.Application.Visible = false;
                    Worksheet book = workbook.Worksheets["Sheet1"];

                    //System.Data.DataTable table = new System.Data.DataTable();
                    //table.Columns.Add("name", typeof(string));
                    //table.Columns.Add("num", typeof(int));
                    //table.Columns.Add("num1", typeof(int));
                    //for (int i = 1; i < 5; i++)
                    //{
                    //    System.Data.DataRow dr = table.NewRow();
                    //    dr["name"] = "name" + i.ToString();
                    //    dr["num"] = i * 2 + 100;
                    //    dr["num1"] = i * 2 + 120;
                    //    table.Rows.Add(dr);
                    //}

                    //var data = new object[table.Rows.Count + 1, table.Columns.Count];
                    //for (int i = 0; i < table.Columns.Count; i++)
                    //{
                    //    data[0, i] = table.Columns[i].ColumnName;
                    //}
                    //for (int i = 0; i < table.Rows.Count; i++)
                    //{
                    //    for (int j = 0; j < table.Columns.Count; j++)
                    //    {
                    //        data[i + 1, j] = table.Rows[i][j];
                    //    }
                    //}

                    book.Cells.ClearContents();
                    Microsoft.Office.Interop.Excel.Range rng = book.get_Range("A1", "A1");
                    rng.get_Resize(data.GetUpperBound(0) + 1, data.GetUpperBound(1) + 1).Value2 = data;
                    chart.SetSourceData("'Sheet1'!" + rng.get_Resize(data.GetUpperBound(0) + 1, data.GetUpperBound(1) + 1).get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing), Type.Missing);
                    Microsoft.Office.Interop.Word.Series s;
                    int num = data.GetLength(1);//获取第二维的个数
                    for (int i = 1; i < num; i++)
                    {
                        s = (Microsoft.Office.Interop.Word.Series)chart.SeriesCollection(i);
                        s.HasDataLabels = true;
                        s.DataLabels().Font.Size = 8;
                        //显示数值  
                        s.DataLabels().ShowValue = true;
                    }

                    workbook.RefreshAll();
                    chart.Refresh();
                    workbook.Close();//结束EXCEL进程
                    workbook.Application.Quit();
                    KillSpecialExcel((Microsoft.Office.Interop.Excel.Application)workbook.Application);

                    System.Threading.Thread.Sleep(100);

                }
            }
            catch (Exception ex)
            {
                if (workbook != null)
                {
                    try
                    {
                        workbook.Close();//结束EXCEL进程
                    }
                    catch (Exception ex1)
                    {


                    }
                    KillSpecialExcel(workbook.Application);//杀死excel进程
                }
            }
            
        }
        /// <summary>
        /// 在word文档中插入excel文件对象
        /// </summary>
        /// <param name="oWord"></param>
        /// <param name="WordDoc"></param>
        /// <param name="bookMark"></param>
        /// <param name="filePath"></param>
        /// <param name="fileName"></param>
        public static void AddExcelObject(Microsoft.Office.Interop.Word.Application oWord, Wd.Document WordDoc, object bookMark, string filePath, string fileName)
        {
            try
            {
                object range = WordDoc.Bookmarks.get_Item(ref bookMark).Range;//excel插入位置
                WordDoc.Activate();//激活当前文档

                object type = @"Excel.Sheet.12";//插入的excel 格式，这里我用的是excel 2010，所以是.12
                                                //object filename = @"C:\\Users\\Admin\\Desktop\\35c9a52cf739423fb0bf4231b0906bf7\\TACLAC.xlsx";//插入的excel的位置
                object filename = filePath;//插入的excel的位置
                object linkToFile = false;
                object displayAsIcon = true;
                object iconFileName = "C:\\windows\\Installer\\{90160000-0012-0000-1000-0000000FF1CE}\\xlicons.exe";
                object iconIndex = 1;
                object iconLabel = fileName;
                oWord.Selection.InlineShapes.AddOLEObject(ref type, ref filename, ref linkToFile, ref displayAsIcon, ref iconFileName, ref iconIndex, ref iconLabel, ref range);//执行插入操作
            }
            catch (Exception ex)
            {

            }


        }
        /// <summary>
        /// 杀死excel进程
        /// </summary>
        /// <param name="m_objExcel"></param>
        public static void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);

                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// 杀死word进程
        /// </summary>
        /// <param name="m_objWord"></param>
        public static void KillSpecialWord(Microsoft.Office.Interop.Word.Application m_objWord)
        {
            try
            {
                if (m_objWord != null)
                {
                    IntPtr p = FindWindowEx(System.IntPtr.Zero, System.IntPtr.Zero, null, m_objWord.Caption);
                    int lpdwProcessId;
                    GetWindowThreadProcessId(p, out lpdwProcessId);

                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }
        }

        /// <summary>
        /// 结束EXCEL.EXE进程的方法
        /// </summary>
        /// <param name="m_objExcel">EXCEL对象</param>
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parent"></param>
        /// <param name="childe"></param>
        /// <param name="strclass"></param>
        /// <param name="strname"></param>
        /// <returns></returns>
        [DllImport("user32.dll")]
        static extern System.IntPtr FindWindowEx(System.IntPtr parent, System.IntPtr childe, string strclass, string strname);
    }
}