使用VBA将Excel指定单元格数据、字符串或者图表对象插入到Word模板指定书签处
B站视频演示：https://www.bilibili.com/video/BV1T54y1v7pd/
准备工作：
1、首先需要提供一个word模板，并且标记好您要插入书签的位置，定义书签的命名。如图
2、模拟您要插入的Excel原始数据和图表对象
插入代码如下：
Private Sub CommandButton1_Click() Dim App, WrdDoc, Mypath As String On Error Resume Next '定义原始模板的储存路径，默认和excel在同一路径 Mypath = ThisWorkbook.Path & "\模板.doc" '用Set关键字创建Word应用成序对象！ Set App = CreateObject("Word.Application") App.Visible = True '打开这个Word文件！ Set WrdDoc = App.Documents.Open(Mypath) '以当前模板创建一个新的模板 Set word = App.Documents.Add(Mypath) '将excel指定单元格的数据写入之前已经编辑定位好的word书签位置 word.Bookmarks("书签1").Range = Range("b2") word.Bookmarks("书签2").Range = Range("b3") word.Bookmarks("书签3").Range = Range("b4") word.Bookmarks("书签4").Range = Range("b5") word.Bookmarks("书签5").Range = Range("b6") ''插入当前工作表的2个图表对象到指定位置，并显示出来 ''更改word 插入对象的环绕方式 ''http://www.debugease.com/vb/2205943.html With App ThisWorkbook.Worksheets("底稿数据").ChartObjects(1).Activate ActiveChart.ChartArea.Copy .ActiveDocument.Bookmarks("收入情况图").Range.Select .Selection.Paste .ActiveDocument.InlineShapes.Item(1).ConvertToShape .ActiveDocument.Shapes.Item(1).WrapFormat.Type = wdWrapTight ThisWorkbook.Worksheets("底稿数据").ChartObjects(2).Activate ActiveChart.ChartArea.Copy .ActiveDocument.Bookmarks("支出情况图").Range.Select .Selection.Paste .ActiveDocument.InlineShapes.Item(1).ConvertToShape .ActiveDocument.Shapes.Item(1).WrapFormat.Type = wdWrapTight '更改环绕方式 End With Paths = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" '记录“桌面”文件夹的路径 word.SaveAs Filename:=Paths & "2019年X月XXXX食堂收支分析报告" & ".doc" '另存为word报告成品 word.Close ' 关闭文件 App.Quit Set App = Nothing MsgBox "2019年X月XXXX食堂收支分析报告", vbInformation, "报告已生成到桌面" Shell "EXPLORER.EXE " & Left(Paths, Len(Paths) - 1), vbMaximizedFocus '打开桌面 End Sub
附件下载地址：
https://files.cnblogs.com/files/ty1216jhy/%E8%87%AA%E5%8A%A8%E7%94%9F%E6%88%90%E9%A3%9F%E5%A0%82%E6%8A%A5%E5%91%8A.rar
联系QQ：609682901
2020年5月日志：
-----------------------------------------不久之后我将这个小工具做成了VSTO外接程序，小学生代码如下-------------------------------------------------------------
做成excel 外接程序好处就是，代码不容易被篡改，使用相对更稳定，就代码升级没有VBA方便
主要有二段核心代码：
一、生成主要书签数据和生成饼状图
1 public void 刷新主要数据() 2 { 3 try 4 { 5 app = Globals.ThisAddIn.Application; 6 excel.Workbook wbk = app.Workbooks["食堂报告模板2019.xls"]; 7 excel.Worksheet wst1 = wbk.Worksheets["本月数据"]; 8 excel.Worksheet wst2 = wbk.Worksheets["上月数据"]; 9 excel.Worksheet wst3 = wbk.Worksheets["生成数据"]; 10 excel.Worksheet wst4 = wbk.Worksheets["对比数据"]; 11 wst3.Range["b2:b6"].ClearContents(); 12 //提取收入说明 13 int lr1 = wst1.Range["a3"].get_End(excel.XlDirection.xlDown).Row; 14 string str1 = string.Empty; 15 if (checkBox2.Checked == true) 16 { 17 for (int i = 6; i < lr1 + 1; i++) 18 { 19 if ((String)wst1.Range["b" + i].Text != "") 20 { 21 str1 = str1 + "," + "\n" + wst1.Range["a" + i].Text + ":" + wst1.Range["b" + i].Text + "元"; ; 22 } 23 } 24 } 25 else 26 { 27 for (int i = 6; i < lr1 + 1; i++) 28 { 29 if ((String)wst1.Range["b" + i].Text != "") 30 { 31 str1 = str1 + "," + wst1.Range["a" + i].Text + ":" + wst1.Range["b" + i].Text + "元"; 32 } 33 } 34 } 35 int len1 = str1.Length; 36 string str1_1 = str1.Substring(2, len1 - 2) + "。"; 37 wst3.Range["b2"].Value2 = str1_1.Replace(" ", "");//替换字符串中产生的空格 38 39 //提取支出说明 40 int lr2 = wst1.Range["d2"].get_End(excel.XlDirection.xlDown).Row; 41 string str2 = string.Empty; 42 if (checkBox2.Checked == true) 43 { 44 for (int i = 4; i < lr2 + 1; i++) 45 { 46 if ((String)wst1.Range["e" + i].Text != "") 47 { 48 str2 = str2 + "," + "\n" + wst1.Range["d" + i].Text + ":" + wst1.Range["e" + i].Text + "元"; ; 49 } 50 } 51 } 52 else 53 { 54 for (int i = 4; i < lr2 + 1; i++) 55 { 56 if ((String)wst1.Range["e" + i].Text != "") 57 { 58 str2 = str2 + "," + wst1.Range["d" + i].Text + ":" + wst1.Range["e" + i].Text + "元"; ; 59 } 60 } 61 } 62 63 int len2 = str2.Length; 64 string str2_1 = str2.Substring(2, len2 - 2) + "。"; 65 wst3.Range["b3"].Value2 = str2_1.Replace(" ", ""); 66 67 //MessageBox.Show(str1_1); 68 //提取结余数据 69 int a = wst1.Range["b100"].get_End(excel.XlDirection.xlUp).Row; 70 int b = wst2.Range["b100"].End[excel.XlDirection.xlUp].Row; 71 string str3 = "上月结余数:" + wst2.Range["b" + b].Value2 + "元;"; 72 string str4 = "本月结余数:" + string.Format("{0:N2}", wst1.Range["b" + a].Value2) + "元;"; 73 string str5 = "上年结余数:" + wst1.Range["c4"].Value2 + "元;"; 74 string str6 = "上月累计结余数:" + wst2.Range["c" + b].Value2 + "元;"; 75 string str7 = "本月累计结余数:" + wst1.Range["c" + a].Value2 + "元"; 76 string res = str3 + "\n" + str4 + "\n" + str5 + "\n" + str6 + "\n" + str7 + "。"; 77 wst3.Range["b4"].Value2 = res; 78 79 //收入分析 80 if (wst4.Range["d2"].Value < 0) 81 { 82 wst4.Range["f2"].Value2 = "本月收入下滑：" + string.Format("{0:N2}", -wst4.Range["d2"].Value2) + 83 "元，环比下滑" + string.Format("{0:P}", -wst4.Range["e2"].Value2) + "。"; 84 } 85 else 86 if (wst4.Range["d2"].Value > 0) 87 { 88 wst4.Range["f2"].Value2 = "本月收入上涨：" + string.Format("{0:N2}", wst4.Range["d2"].Value2) + 89 "元，环比上涨" + string.Format("{0:P}", wst4.Range["e2"].Value2) + "。"; 90 } 91 else 92 { 93 wst4.Range["f2"].Value2 = "本月收入与上月持平。"; 94 } 95 string res1 = wst4.Range["f2"].Value2; 96 //MessageBox.Show(res1); 97 //支出分析 98 if (wst4.Range["d3"].Value < 0) 99 { 100 wst4.Range["f3"].Value2 = "本月支出下滑：" + string.Format("{0:N2}", -wst4.Range["d3"].Value2) + 101 "元，环比下滑" + string.Format("{0:P}", -wst4.Range["e3"].Value2) + "。"; 102 } 103 else 104 if (wst4.Range["d3"].Value > 0) 105 { 106 wst4.Range["f3"].Value2 = "本月支出上涨：" + string.Format("{0:N2}", wst4.Range["d3"].Value2) + 107 "元，环比上涨" + string.Format("{0:P}", wst4.Range["e3"].Value2) + "。"; 108 } 109 else 110 { 111 wst4.Range["f3"].Value2 = "本月支出与上月持平。"; 112 } 113 string res2 = wst4.Range["f3"].Value2; 114 //MessageBox.Show(res2); 115 //结余分析 116 if (wst4.Range["d4"].Value < 0) 117 { 118 wst4.Range["f4"].Value2 = "本月收支结余下滑：" + string.Format("{0:N2}", -wst4.Range["d4"].Value2) + 119 "元，环比下滑" + string.Format("{0:P}", wst4.Range["e4"].Value2) + "。"; 120 } 121 else 122 if (wst4.Range["d4"].Value > 0) 123 { 124 wst4.Range["f4"].Value2 = "本月收支结余上涨：" + string.Format("{0:N2}", wst4.Range["d4"].Value2) + 125 "元，环比上涨" + string.Format("{0:P}", wst4.Range["e4"].Value2) + "。"; 126 } 127 else 128 { 129 wst4.Range["f4"].Value2 = "本月收支结余与上月持平。"; 130 } 131 string res3 = wst4.Range["f4"].Value2; 132 133 string res_1 = res1 + "主要原因：******" + "\n" + res2 + "主要原因：******" + "\n" + res3 + "主要原因：******"; 134 //MessageBox.Show(res_1); 135 wst3.Range["b5"].Value2 = res_1; 136 137 string res_2 = "【写报告就像拜菩萨一样，全靠诚心！所以为表心意，建议大家还是手动写报告】"; 138 wst3.Range["b6"].Value2 = res_2; 139 } 140 catch (Exception ex) 141 { 142 MessageBox.Show(ex.ToString()); 143 } 144 }
1 public void 生成饼状图() 2 { 3 try 4 { 5 app = Globals.ThisAddIn.Application; 6 excel.Workbook wbk = app.Workbooks["食堂报告模板2019.xls"]; 7 excel.Worksheet wst = wbk.Worksheets["本月数据"]; 8 excel.Worksheet wst4 = wbk.Worksheets["对比数据"]; 9 wst4.Range["j1:n100"].ClearContents(); 10 11 //收入项目和金额 12 int lr = wst.Range["a3"].get_End(excel.XlDirection.xlDown).Row; 13 int i = 2; 14 foreach (excel.Range rng in wst.Range["a6:a" + lr]) 15 { 16 string str = rng.Value2.ToString(); 17 String str1 = (String)rng.Offset[0, 1].Text; 18 string stri = "[^\u4e00-\u9fa5]"; 19 //if (str.Substring(2, 1) != "（" && str1 != "")// 20 //2020年5月新报表格式修改，收入项提取不包含带括号的子项目 21 if (!str.Contains("（") && str1 != "" &&!str.Contains("收入合计") && !str.Contains("收支结余")) 22 { 23 //MessageBox.Show(str); 24 string result = Regex.Replace(str, stri, ""); 25 wst4.Cells[i, "j"] = result; 26 wst4.Cells[i, "k"] = (String)rng.Offset[0, 1].Text; 27 i += 1; 28 } 29 } 30 //支出项目和金额 31 int j = 2; 32 int lr1 = wst.Range["d4"].get_End(excel.XlDirection.xlDown).Row - 1; 33 foreach (excel.Range rng in wst.Range["d5:d" + lr1]) 34 { 35 string str = rng.Value2.ToString(); 36 String str1 = (String)rng.Offset[0, 1].Text; 37 string stri = "[^\u4e00-\u9fa5]"; 38 //2020年5月新报表格式修改，收入项提取不包含带括号的子项目 39 //强制添加不包含str != "1、水电支出" && str != "2、基本伙食支出"，因为下面的维修费和其他支出又必须提取，我靠，做表的人真是随心所欲 40 if (!str.Contains("（") && str1 != "" && !str.Contains("水电支出") && !str.Contains("基本伙食支出") && !str.Contains("人员支出")) 41 { 42 //MessageBox.Show(str); 43 string result = Regex.Replace(str, stri, ""); 44 wst4.Cells[j, "m"] = result; 45 wst4.Cells[j, "n"] = (String)rng.Offset[0, 1].Text; 46 j += 1; 47 } 48 } 49 if ((String)wst.Range["e" + lr1].Text != "") 50 { 51 int m = wst4.Range["m2"].get_End(excel.XlDirection.xlDown).Row + 1; 52 wst4.Range["m" + m].Value2 = wst.Range["d" + lr1].Value2; 53 wst4.Range["n" + m].Value2 = wst.Range["d" + lr1].Offset[0, 1].Value; 54 } 55 wst4.Range["j1"].Value2 = "收入项目"; 56 wst4.Range["k1"].Value2 = "收入金额"; 57 int r = wst4.Range["j2"].get_End(excel.XlDirection.xlDown).Row; 58 #region 此段同样可以生成饼状图，只是同样都是XlChartType.xl3DPie，为何形状会不一样 59 /* 60 */ 61 //excel.ChartObjects chartObjects = (excel.ChartObjects)wst4.ChartObjects(); 62 //excel.ChartObject chartObject = chartObjects.Add(wst4.Range["a8"].Left, wst4.Range["a8"].Top, 362, 200); 63 //excel.Chart chart = chartObject.Chart; 64 //chart.ChartType = excel.XlChartType.xl3DPie; 65 //chart.SetSourceData(wst4.Range["j1:k" + r]); 66 //chart.SetElement(MsoChartElementType.msoElementLegendRight); 67 //chart.Legend.Top = 8.5; 68 //chart.Legend.Height = 200; 69 //wst4.ChartObjects(1).Name = "收入xl3DPie"; 70 /* 71 * 72 */ 73 #endregion 74 wst4.Activate(); 75 wst4.Range["j1:k" + r].Select(); 76 //office版本语法差异 77 if (Single.Parse(app.Version) >= 15) 78 { 79 wst4.Shapes.AddChart2(-1, XlChartType.xl3DPie, wst4.Range["a8"].Left, wst4.Range["a8"].Top, 380, 220, true); 80 } 81 else 82 { 83 wst4.Shapes.AddChart(XlChartType.xl3DPie, wst4.Range["a8"].Left, wst4.Range["a8"].Top, 380, 220); 84 } 85 wst4.ChartObjects(1).Name = "收入xl3DPie"; 86 wst4.ChartObjects("收入xl3DPie").Chart.SetElement(MsoChartElementType.msoElementLegendRight); 87 wst4.ChartObjects("收入xl3DPie").Chart.Legend.Top = 6.5; 88 wst4.ChartObjects("收入xl3DPie").Chart.Legend.Height = 210; 89 //excel.Chart chart_s = wst4.ChartObjects("收入xl3DPie").Chart; 90 //string style = Interaction.InputBox("请输入1-7之间（包含）的数字", "图形样式", "文本内容", -1, -1); 91 //chart_s.ApplyLayout(int.Parse(style),chart_s.ChartType); 92 //获取图像的标题，当更改成无标题的图像是会报错 93 //string sr = wst4.ChartObjects("收入xl3DPie").Chart.ChartTitle.Text; 94 //MessageBox.Show(sr); 95 96 wst4.Range["m1"].Value2 = "支出项目"; 97 wst4.Range["n1"].Value2 = "支出金额"; 98 int k = wst4.Range["m2"].get_End(excel.XlDirection.xlDown).Row; 99 100 wst4.Range["m1:n" + k].Select(); 101 if (Single.Parse(app.Version) >= 15) 102 { 103 wst4.Shapes.AddChart2(-1, XlChartType.xl3DPie, wst4.Range["f8"].Left, wst4.Range["f8"].Top, 380, 220, true); 104 } 105 else 106 { 107 wst4.Shapes.AddChart(XlChartType.xl3DPie, wst4.Range["f8"].Left, wst4.Range["f8"].Top, 380, 220); 108 } 109 110 wst4.ChartObjects(2).Name = "支出xl3DPie"; 111 wst4.ChartObjects("支出xl3DPie").Chart.SetElement(MsoChartElementType.msoElementLegendRight); 112 wst4.ChartObjects("支出xl3DPie").Chart.Legend.Top = 6.5; 113 wst4.ChartObjects("支出xl3DPie").Chart.Legend.Height = 210; 114 string zc = wst4.ChartObjects("支出xl3DPie").Chart.ChartTitle.Text; 115 app.ScreenUpdating = true; 116 //MessageBox.Show(zc); 117 } 118 catch (Exception ex) 119 { 120 MessageBox.Show(ex.ToString()); 121 } 122 123 finally 124 { 125 //检验图表名称 126 string res = ""; 127 excel.Worksheet wst = Globals.ThisAddIn.Application.Workbooks["食堂报告模板2019.xls"].Worksheets["对比数据"]; 128 foreach (excel.Shape shape in wst.Shapes) 129 { 130 if (shape.Type == MsoShapeType.msoChart) 131 { 132 res = shape.Name + ":" + shape.Chart.ChartTitle.Text + "\n" + res; 133 //MessageBox.Show(shape.Chart.ChartTitle.Text); 134 } 135 } 136 MessageBox.Show(res, "已生成图表"); 137 } 138 }
二、导出报告
1 private void Button3_Click(object sender, RibbonControlEventArgs e) 2 { 3 try 4 { 5 app = Globals.ThisAddIn.Application; 6 app.StatusBar = "报告生成中,请勿关闭工作簿..."; 7 System.Windows.Forms.Application.DoEvents(); 8 //DataTable dt = LoadDataFromExcel("D:\\用户目录\\原始数据.xls"); 9 //object oMissing = System.Reflection.Missing.Value; 10 //创建一个Word应用程序实例 11 word._Application oWord = new word.Application(); 12 //设置为不可见 13 oWord.Visible = false; 14 //模板文件地址，这里假设在X盘根目录 15 string localpath = Path.GetDirectoryName(new Uri(Assembly.GetExecutingAssembly().CodeBase).LocalPath); 16 string filename = localpath + "\\模板.doc"; 17 string year = DateTime.Now.Year.ToString(); 18 //object oTemplate = "D:\\用户目录\\模板.doc"; 19 //以模板为基础生成文档 20 //Word._Document oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing); 21 //新版本VS已经取消了对参数的限制，可以不用谢missing 22 word._Document oDoc = oWord.Documents.Add(filename); 23 24 //声明书签数组 25 object[] oBookMark = new object[5]; 26 //赋值书签名 27 oBookMark[0] = "书签1"; 28 oBookMark[1] = "书签2"; 29 oBookMark[2] = "书签3"; 30 oBookMark[3] = "书签4"; 31 oBookMark[4] = "书签5"; 32 //赋值任意数据到书签的位置 33 app = Globals.ThisAddIn.Application; 34 excel.Workbook wbk = app.Workbooks["食堂报告模板2019.xls"]; 35 excel.Worksheet wst1 = wbk.Worksheets["本月数据"]; 36 excel.Worksheet wst2 = wbk.Worksheets["上月数据"]; 37 excel.Worksheet wst3 = wbk.Worksheets["生成数据"]; 38 excel.Worksheet wst4 = wbk.Worksheets["对比数据"]; 39 oDoc.Bookmarks["书签1"].Range.Text = wst3.Range["b2"].Value2; 40 oDoc.Bookmarks["书签2"].Range.Text = wst3.Range["b3"].Value2; 41 oDoc.Bookmarks["书签3"].Range.Text = wst3.Range["b4"].Value2; 42 oDoc.Bookmarks["书签4"].Range.Text = wst3.Range["b5"].Value2; 43 oDoc.Bookmarks["书签5"].Range.Text = wst3.Range["b6"].Value2; 44 //oDoc.Bookmarks.get_Item(ref oBookMark[0]).Range.Text = dt.Rows[0][1].ToString(); 45 //oDoc.Bookmarks.get_Item(ref oBookMark[1]).Range.Text = dt.Rows[1][1].ToString(); 46 //oDoc.Bookmarks.get_Item(ref oBookMark[2]).Range.Text = dt.Rows[2][1].ToString(); 47 //oDoc.Bookmarks.get_Item(ref oBookMark[3]).Range.Text = dt.Rows[3][1].ToString(); 48 //oDoc.Bookmarks.get_Item(ref oBookMark[4]).Range.Text = dt.Rows[4][1].ToString(); 49 /*对WOrd文档的操作大全 50 * https://www.cnblogs.com/xh6300/p/5915717.html 51 */ 52 wst4.ChartObjects("收入xl3DPie").Activate(); 53 wst4.ChartObjects("收入xl3DPie").Chart.ChartArea.Copy(); 54 oWord.ActiveDocument.Bookmarks["收入情况图"].Range.Select(); 55 oWord.Selection.Paste(); 56 word.Shape shape1 = oDoc.Application.ActiveDocument.InlineShapes[1].ConvertToShape(); 57 shape1.WrapFormat.Type = word.WdWrapType.wdWrapTight; 58 59 wst4.ChartObjects("支出xl3DPie").Activate(); 60 wst4.ChartObjects("支出xl3DPie").Chart.ChartArea.Copy(); 61 oWord.ActiveDocument.Bookmarks["支出情况图"].Range.Select(); 62 oWord.Selection.Paste(); 63 word.Shape shape2 = oDoc.Application.ActiveDocument.InlineShapes[1].ConvertToShape(); 64 shape2.WrapFormat.Type = word.WdWrapType.wdWrapTight; 65 66 //弹出保存文件对话框，保存生成的Word 67 SaveFileDialog sfd = new SaveFileDialog 68 { 69 Title = "请选择保存您的新报告", 70 Filter = "Word Document(*.doc)|*.doc", 71 DefaultExt = "Word Document(*.doc)|*.doc", 72 FileName = year + "年X月XXXX食堂收支分析报告" 73 }; 74 if (sfd.ShowDialog() == DialogResult.OK) 75 { 76 object file_name = sfd.FileName; 77 oDoc.SaveAs(file_name); 78 oDoc.Close(); 79 //关闭word 80 oWord.Quit(); 81 MessageBox.Show("报告已导出", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 82 app.StatusBar = "报告已导出。"; 83 } 84 else 85 { 86 MessageBox.Show("导出取消", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Error); 87 app.StatusBar = "报告未导出。"; 88 } 89 } 90 catch (Exception ex) 91 { 92 MessageBox.Show(ex.ToString()); 93 } 94 }
$$如果你觉得文章给你带来了帮助，欢迎打赏
