Word中插入表格与柱状图饼状图技术经验分享
来源:互联网 发布:编程实现一个冒泡排序 编辑:程序博客网 时间:2024/05/22 00:06
最近公司一个项目里要求自动生成报告功能,研究了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); }}
阅读全文
0 0
- Word中插入表格与柱状图饼状图技术经验分享
- PowerPoint 2007中轻松插入Word表格
- c# 在word中插入表格
- 将Excel中内容按条插入Word表格中
- word中插入图片,图表,字符替换,表格填写,合并
- 在word中插入excel表格并自动更新
- 在Word 2007中轻松插入或创建表格
- Word中动态的L连续插入多个表格
- 使用iText在word文档中插入复杂的表格
- 【一步一步学习VBA】WORD 中创建表格并插入文本
- C#在word文档中连续插入表格
- java如何实现向word文档中插入柱状图,并更改颜色
- Delphi与Word(一)合并Word 表格中单元格
- Delphi与Word(一) 合并Word 表格中单元格
- QT操作Word,插入表格
- C#中操作Word(7)—— 如何向Word中插入表格
- word中图标的插入与引用
- C# 操作在Word表格中插入新行(表格含合并行)
- NOIP 冲刺:常见的递推之第二类斯特林数
- eclipse报内存溢出
- 乐观锁与悲观锁
- 我的机器学习之路(三)
- [App] OpenVPN 2.3.14 搭建
- Word中插入表格与柱状图饼状图技术经验分享
- webpack+vue初始化项目基本
- Matlab 自定义自己的 colormap(注意其中的一个坑)
- set path in cmd in windows
- Laravel 精选资源大全
- 算法分析与设计课程总结
- AADL模式
- 一个核物理学霸为何两次收到BlackHat的邀请
- spring容器内建的profile功能实现开发环境、测试环境、生产环境配置自动切换