1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Diagnostics;
  5 using System.IO;
  6 using System.Linq;
  7 using System.Reflection;
  8 using Excel = Microsoft.Office.Interop.Excel;
  9 using Word = Microsoft.Office.Interop.Word;
 10 
 11 namespace FlyLolo.WordReport.Demo
 12 {
 13     public class WordReportHelper
 14     {
 15         private Word.Application wordApp = null;
 16         private Word.Document wordDoc = null;
 17         private DataSet dataSource = null;
 18         private object line = Word.WdUnits.wdLine;
 19         private string errorMsg = "";
 20 
 21         /// <summary>
 22         /// 根据模板文件,创建数据报告
 23         /// </summary>
 24         /// <param name="templateFile">模板文件名(含路径)</param>
 25         /// <param name="newFilePath">新文件路径)</param>
 26         /// <param name="dataSource">数据源,包含多个datatable</param>
 27         /// <param name="saveFormat">新文件格式:</param>
 28         public bool CreateReport(string templateFile, DataSet dataSource, out string errorMsg, string newFilePath, ref string newFileName, int saveFormat = 16)
 29         {
 30             this.dataSource = dataSource;
 31             errorMsg = this.errorMsg;
 32             bool rtn = OpenTemplate(templateFile)
 33                 && SetContent(new WordElement(wordDoc.Range(), dataRow: dataSource.Tables[dataSource.Tables.Count - 1].Rows[0]))
 34                 && UpdateTablesOfContents()
 35                 && SaveFile(newFilePath, ref newFileName, saveFormat);
 36 
 37             CloseAndClear();
 38             return rtn;
 39         }
 40 
 41         /// <summary>
 42         /// 打开模板文件
 43         /// </summary>
 44         /// <param name="templateFile"></param>
 45         /// <returns></returns>
 46         private bool OpenTemplate(string templateFile)
 47         {
 48             if (!File.Exists(templateFile))
 49             {
 50                 return false;
 51             }
 52 
 53             wordApp = new Word.Application();
 54             wordApp.Visible = true;//使文档可见,调试用
 55             wordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone;
 56             object file = templateFile;
 57             wordDoc = wordApp.Documents.Open(ref file, ReadOnly: false);
 58             return true;
 59         }
 60 
 61         /// <summary>
 62         /// 为指定区域写入数据
 63         /// </summary>
 64         /// <param name="element"></param>
 65         /// <returns></returns>
 66         private bool SetContent(WordElement element)
 67         {
 68             string currBookMarkName = string.Empty;
 69             string startWith = "loop_" + (element.Level + 1).ToString() + "_";
 70             foreach (Word.Bookmark item in element.Range.Bookmarks)
 71             {
 72                 currBookMarkName = item.Name;
 73 
 74                 if (currBookMarkName.StartsWith(startWith) && (!currBookMarkName.Equals(element.ElementName)))
 75                 {
 76                     SetLoop(new WordElement(item.Range, currBookMarkName, element.DataRow, element.GroupBy));
 77                 }
 78 
 79             }
 80 
 81             SetLabel(element);
 82 
 83             SetTable(element);
 84 
 85             SetChart(element);
 86 
 87             return true;
 88         }
 89 
 90         /// <summary>
 91         /// 处理循环
 92         /// </summary>
 93         /// <param name="element"></param>
 94         /// <returns></returns>
 95         private bool SetLoop(WordElement element)
 96         {
 97             DataRow[] dataRows = dataSource.Tables[element.TableIndex].Select(element.GroupByString);
 98             int count = dataRows.Count();
 99             element.Range.Select();
100 
101             //第0行作为模板  先从1开始  循环后处理0行;
102             for (int i = 0; i < count; i++)
103             {
104 
105                 element.Range.Copy();  //模板loop复制
106                 wordApp.Selection.InsertParagraphAfter();//换行 不会清除选中的内容,TypeParagraph 等同于回车,若当前有选中内容会被清除. TypeParagraph 会跳到下一行,InsertParagraphAfter不会, 所以movedown一下.
107                 wordApp.Selection.MoveDown(ref line, Missing.Value, Missing.Value);
108                 wordApp.Selection.Paste(); //换行后粘贴复制内容
109                 int offset = wordApp.Selection.Range.End - element.Range.End; //计算偏移量
110 
111                 //复制书签,书签名 = 模板书签名 + 复制次数
112                 foreach (Word.Bookmark subBook in element.Range.Bookmarks)
113                 {
114                     if (subBook.Name.Equals(element.ElementName))
115                     {
116                         continue;
117                     }
118 
119                     wordApp.Selection.Bookmarks.Add(subBook.Name + "_" + i.ToString(), wordDoc.Range(subBook.Start + offset, subBook.End + offset));
120                 }
121 
122                 SetContent(new WordElement(wordDoc.Range(wordApp.Selection.Range.End - (element.Range.End - element.Range.Start), wordApp.Selection.Range.End), element.ElementName + "_" + i.ToString(), dataRows[i], element.GroupBy));
123             }
124 
125             element.Range.Delete();
126 
127             return true;
128         }
129 
130         /// <summary>
131         /// 处理简单Label
132         /// </summary>
133         /// <param name="element"></param>
134         /// <returns></returns>
135         private bool SetLabel(WordElement element)
136         {
137             if (element.Range.Bookmarks != null && element.Range.Bookmarks.Count > 0)
138             {
139                 string startWith = "label_" + element.Level.ToString() + "_";
140                 string bookMarkName = string.Empty;
141                 foreach (Word.Bookmark item in element.Range.Bookmarks)
142                 {
143                     bookMarkName = item.Name;
144 
145                     if (bookMarkName.StartsWith(startWith))
146                     {
147                         bookMarkName = WordElement.GetName(bookMarkName);
148 
149                         item.Range.Text = element.DataRow[bookMarkName].ToString();
150                     }
151                 }
152             }
153 
154             return true;
155         }
156 
157         /// <summary>
158         /// 填充Table
159         /// </summary>
160         /// <param name="element"></param>
161         /// <returns></returns>
162         private bool SetTable(WordElement element)
163         {
164             if (element.Range.Tables != null && element.Range.Tables.Count > 0)
165             {
166                 string startWith = "table_" + element.Level.ToString() + "_";
167                 foreach (Word.Table table in element.Range.Tables)
168                 {
169                     if (!string.IsNullOrEmpty(table.Title) && table.Title.StartsWith(startWith))
170                     {
171                         WordElement tableElement = new WordElement(null, table.Title, element.DataRow);
172 
173                         TableConfig config = new TableConfig(table.Descr);
174 
175                         object dataRowTemplate = table.Rows[config.DataRow];
176                         Word.Row SummaryRow = null;
177                         DataRow SummaryDataRow = null;
178                         DataTable dt = dataSource.Tables[tableElement.TableIndex];
179                         DataRow[] dataRows = dataSource.Tables[tableElement.TableIndex].Select(tableElement.GroupByString); ;
180 
181                         if (config.SummaryRow > 0)
182                         {
183                             SummaryRow = table.Rows[config.SummaryRow];
184                             SummaryDataRow = dt.Select(string.IsNullOrEmpty(tableElement.GroupByString) ? config.SummaryFilter : tableElement.GroupByString + " and  " + config.SummaryFilter).FirstOrDefault();
185                         }
186 
187                         foreach (DataRow row in dataRows)
188                         {
189                             if (row == SummaryDataRow)
190                             {
191                                 continue;
192                             }
193 
194                             Word.Row newRow = table.Rows.Add(ref dataRowTemplate);
195                             for (int j = 0; j < table.Columns.Count; j++)
196                             {
197                                 newRow.Cells[j + 1].Range.Text = row[j].ToString(); ;
198                             }
199 
200                         }
201 
202                         ((Word.Row)dataRowTemplate).Delete();
203 
204                         if (config.SummaryRow > 0 && SummaryDataRow != null)
205                         {
206                             for (int j = 0; j < SummaryRow.Cells.Count; j++)
207                             {
208                                 string temp = SummaryRow.Cells[j + 1].Range.Text.Trim().Replace("\r\a", "");
209 
210                                 if (!string.IsNullOrEmpty(temp) && temp.Length > 2 && dt.Columns.Contains(temp.Substring(1, temp.Length - 2)))
211                                 {
212                                     SummaryRow.Cells[j + 1].Range.Text = SummaryDataRow[temp.Substring(1, temp.Length - 2)].ToString();
213                                 }
214                             }
215                         }
216 
217                         table.Title = tableElement.Name;
218                     }
219 
220 
221                 }
222             }
223 
224             return true;
225         }
226 
227         /// <summary>
228         /// 处理图表
229         /// </summary>
230         /// <param name="element"></param>
231         /// <returns></returns>
232         private bool SetChart(WordElement element)
233         {
234             if (element.Range.InlineShapes != null && element.Range.InlineShapes.Count > 0)
235             {
236                 List<Word.InlineShape> chartList = element.Range.InlineShapes.Cast<Word.InlineShape>().Where(m => m.Type == Word.WdInlineShapeType.wdInlineShapeChart).ToList();
237                 string startWith = "chart_" + element.Level.ToString() + "_";
238                 foreach (Word.InlineShape item in chartList)
239                 {
240                     Word.Chart chart = item.Chart;
241                     if (!string.IsNullOrEmpty(chart.ChartTitle.Text) && chart.ChartTitle.Text.StartsWith(startWith))
242                     {
243                         WordElement chartElement = new WordElement(null, chart.ChartTitle.Text, element.DataRow);
244 
245                         DataTable dataTable = dataSource.Tables[chartElement.TableIndex];
246                         DataRow[] dataRows = dataTable.Select(chartElement.GroupByString);
247 
248                         int columnCount = dataTable.Columns.Count;
249                         List<int> columns = new List<int>();
250 
251                         foreach (var dr in dataRows)
252                         {
253                             for (int i = chartElement.ColumnStart == -1 ? 0 : chartElement.ColumnStart - 1; i < (chartElement.ColumnEnd == -1 ? columnCount : chartElement.ColumnEnd); i++)
254                             {
255                                 if (columns.Contains(i) || dr[i] == null || string.IsNullOrEmpty(dr[i].ToString()))
256                                 {
257 
258                                 }
259                                 else
260                                 {
261                                     columns.Add(i);
262                                 }
263                             }
264                         }
265                         columns.Sort();
266                         columnCount = columns.Count;
267                         int rowsCount = dataRows.Length;
268 
269                         Word.ChartData chartData = chart.ChartData;
270 
271                         //chartData.Activate();
272                         //此处有个比较疑惑的问题, 不执行此条,生成的报告中的图表无法再次右键编辑数据. 执行后可以, 但有两个问题就是第一会弹出Excel框, 处理完后会自动关闭. 第二部分chart的数据range设置总不对
273                         //不知道是不是版本的问题, 谁解决了分享一下,谢谢
274 
275                         Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;
276                         dataWorkbook.Application.Visible = false;
277 
278                         Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1];
279                         //设定范围  
280                         string a = (chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount) + "|" + columnCount;
281                         Console.WriteLine(a);
282 
283                         Excel.Range tRange = dataSheet.Range["A1", dataSheet.Cells[(chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount), columnCount]];
284                         Excel.ListObject tbl1 = dataSheet.ListObjects[1];
285                         //dataSheet.ListObjects[1].Delete(); //想过重新删除再添加  这样 原有数据清掉了, 但觉得性能应该会有所下降
286                         //Excel.ListObject tbl1 = dataSheet.ListObjects.AddEx();
287                         tbl1.Resize(tRange);
288                         for (int j = 0; j < rowsCount; j++)
289                         {
290                             DataRow row = dataRows[j];
291                             for (int k = 0; k < columnCount; k++)
292                             {
293                                 dataSheet.Cells[j + 2, k + 1].FormulaR1C1 = row[columns[k]];
294                             }
295                         }
296 
297                         if (chartElement.ColumnNameForHead)
298                         {
299                             for (int k = 0; k < columns.Count; k++)
300                             {
301                                 dataSheet.Cells[1, k + 1].FormulaR1C1 = dataTable.Columns[columns[k]].ColumnName;
302                             }
303                         }
304                         chart.ChartTitle.Text = chartElement.Name;
305                         //dataSheet.Application.Quit();
306                     }
307                 }
308             }
309 
310             return true;
311         }
312 
313         /// <summary>
314         /// 更新目录
315         /// </summary>
316         /// <returns></returns>
317         private bool UpdateTablesOfContents()
318         {
319             foreach (Word.TableOfContents item in wordDoc.TablesOfContents)
320             {
321                 item.Update();
322             }
323 
324             return true;
325         }
326 
327         /// <summary>
328         /// 保存文件
329         /// </summary>
330         /// <param name="newFilePath"></param>
331         /// <param name="newFileName"></param>
332         /// <param name="saveFormat"></param>
333         /// <returns></returns>
334         private bool SaveFile(string newFilePath, ref string newFileName, int saveFormat = 16)
335         {
336             if (string.IsNullOrEmpty(newFileName))
337             {
338                 newFileName = DateTime.Now.ToString("yyyyMMddHHmmss");
339 
340                 switch (saveFormat)
341                 {
342                     case 0:// Word.WdSaveFormat.wdFormatDocument
343                         newFileName += ".doc";
344                         break;
345                     case 16:// Word.WdSaveFormat.wdFormatDocumentDefault
346                         newFileName += ".docx";
347                         break;
348                     case 17:// Word.WdSaveFormat.wdFormatPDF
349                         newFileName += ".pdf";
350                         break;
351                     default:
352                         break;
353                 }
354             }
355 
356             object newfile = Path.Combine(newFilePath, newFileName);
357             object wdSaveFormat = saveFormat;
358             wordDoc.SaveAs(ref newfile, ref wdSaveFormat);
359             return true;
360         }
361 
362         /// <summary>
363         /// 清理
364         /// </summary>
365         private void CloseAndClear()
366         {
367             if (wordApp == null)
368             {
369                 return;
370             }
371             wordDoc.Close(Word.WdSaveOptions.wdDoNotSaveChanges);
372             wordApp.Quit(Word.WdSaveOptions.wdDoNotSaveChanges);
373             System.Runtime.InteropServices.Marshal.ReleaseComObject(wordDoc);
374             System.Runtime.InteropServices.Marshal.ReleaseComObject(wordApp);
375             wordDoc = null;
376             wordApp = null;
377             GC.Collect();
378             KillProcess("Excel", "WINWORD");
379         }
380 
381         /// <summary>
382         /// 杀进程..
383         /// </summary>
384         /// <param name="processNames"></param>
385         private void KillProcess(params string[] processNames)
386         {
387             //Process myproc = new Process();
388             //得到所有打开的进程  
389             try
390             {
391                 foreach (string name in processNames)
392                 {
393                     foreach (Process thisproc in Process.GetProcessesByName(name))
394                     {
395                         if (!thisproc.CloseMainWindow())
396                         {
397                             if (thisproc != null)
398                                 thisproc.Kill();
399                         }
400                     }
401                 }
402             }
403             catch (Exception)
404             {
405                 //throw Exc;
406                 // msg.Text+=  "杀死"  +  processName  +  "失败！";  
407             }
408         }
409     }
410 
411     /// <summary>
412     /// 封装的Word元素
413     /// </summary>
414     public class WordElement
415     {
416         public WordElement(Word.Range range, string elementName = "", DataRow dataRow = null, Dictionary<string, string> groupBy = null, int tableIndex = 0)
417         {
418             this.Range = range;
419             this.ElementName = elementName;
420             this.GroupBy = groupBy;
421             this.DataRow = dataRow;
422             if (string.IsNullOrEmpty(elementName))
423             {
424                 this.Level = 0;
425                 this.TableIndex = tableIndex;
426                 this.Name = string.Empty;
427                 this.ColumnNameForHead = false;
428             }
429             else
430             {
431                 string[] element = elementName.Split('_');
432                 this.Level = int.Parse(element[1]);
433                 this.ColumnNameForHead = false;
434                 this.ColumnStart = -1;
435                 this.ColumnEnd = -1;
436 
437                 if (element[0].Equals("label"))
438                 {
439                     this.Name = element[2];
440                     this.TableIndex = 0;
441                 }
442                 else
443                 {
444                     this.Name = element[4];
445                     this.TableIndex = int.Parse(element[2]) - 1;
446 
447                     if (!string.IsNullOrEmpty(element[3]))
448                     {
449                         string[] filters = element[3].Split(new string[] { "XX" }, StringSplitOptions.RemoveEmptyEntries);
450                         if (this.GroupBy == null)
451                         {
452                             this.GroupBy = new Dictionary<string, string>();
453                         }
454                         foreach (string item in filters)
455                         {
456                             if (!this.GroupBy.Keys.Contains(item))
457                             {
458                                 this.GroupBy.Add(item, dataRow[item].ToString());
459                             }
460 
461                         }
462                     }
463 
464                     if (element[0].Equals("chart") && element.Count() > 5)
465                     {
466                         this.ColumnNameForHead = element[5].Equals("1");
467                         this.ColumnStart = string.IsNullOrEmpty(element[6]) ? -1 : int.Parse(element[6]);
468                         this.ColumnEnd = string.IsNullOrEmpty(element[7]) ? -1 : int.Parse(element[7]);
469                     }
470                 }
471             }
472         }
473 
474         public Word.Range Range { get; set; }
475         public int Level { get; set; }
476         public int TableIndex { get; set; }
477         public string ElementName { get; set; }
478 
479         public DataRow DataRow { get; set; }
480         public Dictionary<string, string> GroupBy { get; set; }
481 
482         public string Name { get; set; }
483 
484         public bool ColumnNameForHead { get; set; }
485         public int ColumnStart { get; set; }
486         public int ColumnEnd { get; set; }
487 
488         public string GroupByString
489         {
490             get
491             {
492                 if (GroupBy == null || GroupBy.Count == 0)
493                 {
494                     return string.Empty;
495                 }
496 
497                 string rtn = string.Empty;
498                 foreach (string key in this.GroupBy.Keys)
499                 {
500                     rtn += "and " + key + " = '" + GroupBy[key] + "' ";
501                 }
502                 return rtn.Substring(3);
503             }
504         }
505 
506         public static string GetName(string elementName)
507         {
508             string[] element = elementName.Split('_');
509 
510 
511             if (element[0].Equals("label"))
512             {
513                 return element[2];
514             }
515             else
516             {
517                 return element[4];
518             }
519         }
520     }
521 
522     /// <summary>
523     /// Table配置项
524     /// </summary>
525     public class TableConfig
526     {
527         public TableConfig(string tableDescr = "")
528         {
529             this.DataRow = 2;
530             this.SummaryRow = -1;
531 
532             if (!string.IsNullOrEmpty(tableDescr))
533             {
534                 string[] element = tableDescr.Split(',');
535                 foreach (string item in element)
536                 {
537                     if (!string.IsNullOrEmpty(item))
538                     {
539                         string[] configs = item.Split(':');
540                         if (configs.Length == 2)
541                         {
542                             switch (configs[0].ToLower())
543                             {
544                                 case "data":
545                                 case "d":
546                                     this.DataRow = int.Parse(configs[1]);
547                                     break;
548                                 case "summary":
549                                 case "s":
550                                     this.SummaryRow = int.Parse(configs[1]);
551                                     break;
552                                 case "summaryfilter":
553                                 case "sf":
554                                     this.SummaryFilter = configs[1];
555                                     break;
556                                 default:
557                                     break;
558                             }
559                         }
560                     }
561                 }
562             }
563 
564         }
565         public int DataRow { get; set; }
566         public int SummaryRow { get; set; }
567         public string SummaryFilter { get; set; }
568     }
569 }