1 using System;
2 using System.Collections.Generic;
3 using System.Collections.ObjectModel;
4 using System.IO;
5 using System.Drawing;
6 using System.Text.RegularExpressions;
7
8 using Modal;
9
10 using Microsoft.Office.Interop.Excel;
11
12 namespace Common
13 {
14 /// <summary>
15 /// Excel操作类
16
17 /// </summary>
18 public class ExcelOprtate
19 {
20 #region 私有字段
21
22 /// <summary>
23 /// Excel应用程序对象
24 /// </summary>
25 private Application _myExcelApp;
26
27 /// <summary>
28 /// Excel 应用程序内的单个工作簿
29
30 /// </summary>
31 private Workbook _myWorkbook;
32
33 /// <summary>
34 /// Worksheets 集合的成员
35
36 /// </summary>
37 private Worksheet _myWorksheet;
38
39 /// <summary>
40 /// 单元格对象
41
42 /// </summary>
43 private Range _Range;
44 private Range _myRange;
45
46 /// <summary>
47 /// 文件路径
48 /// </summary>
49 private string _strFilePath;
50
51 /// <summary>
52 /// 文件名
53
54 /// </summary>
55 private string _strFileName;
56
57 /// <summary>
58 /// 数据库源（对象集合）
59 /// </summary>
60 private ObservableCollection<object> _objdataSource;
61
62 /// <summary>
63 /// 数据源（DataTable）
64
65 /// </summary>
66 private System.Data.DataTable _dtdataSource;
67
68 /// <summary>
69 /// 记录Excel进程创建时间
70 /// </summary>
71 private DateTime _StartTime;
72
73 /// <summary>
74 /// 列名集合
75 /// </summary>
76 private List<string> _lColumns;
77
78 private string MyFlag;
79
80 #endregion
81
82 #region 构造函数
83
84
85 /// <summary>
86 /// 构造函数
87
88 /// </summary>
89 /// <param name="strFilePath"></param>
90 public ExcelOprtate(string strFilePath,string Flag)
91 {
92 _myExcelApp = new Application();
93 _StartTime = DateTime.Now;
94 _myExcelApp.Visible = false;//设置Excel文件不可见
95
96 _strFilePath = strFilePath;
97 FilllCloumns(Flag);
98 MyFlag = Flag;
99 }
100
101 #endregion
102
103 #region 公有属性
104
105
106 /// <summary>
107 /// 文件名
108
109 /// </summary>
110 public string FileName
111 {
112 get { return _strFileName; }
113 set { _strFileName = value; }
114 }
115
116 /// <summary>
117 /// 文件路径
118 /// </summary>
119 public string FilePath
120 {
121 get { return _strFilePath; }
122 set { _strFilePath = value; }
123 }
124
125 /// <summary>
126 /// 数据库源（对象集合）
127 /// </summary>
128 public ObservableCollection<object> ObjDataSource
129 {
130 get { return _objdataSource; }
131 set { _objdataSource = value; }
132 }
133
134 /// <summary>
135 /// 数据源（DataTable）
136
137 /// </summary>
138 public System.Data.DataTable DtDataSource
139 {
140 get { return _dtdataSource; }
141 set { _dtdataSource = value; }
142 }
143
144 /// <summary>
145 /// 单元格集合
146
147 /// </summary>
148 public Range MyRange
149 {
150 get { return _myRange; }
151 set { _myRange = value; }
152 }
153
154 /// <summary>
155 /// 列集合
156
157 /// </summary>
158 public List<string> lColumns
159 {
160 get { return _lColumns; }
161 set { _lColumns = value; }
162 }
163
164 #endregion
165
166 #region 功能函数（公有）
167
168 #region 改Sheet名
169
170
171 /// <summary>
172 /// 改Sheet名
173
174 /// </summary>
175 /// <param name="strName">Sheet名</param>
176 /// <param name="sheetIndex">Sheet索引</param>
177 public void ChangeSheetName(string strName, int sheetIndex)
178 {
179 try
180 {
181 _myWorksheet = _myWorkbook.Sheets[sheetIndex] as Worksheet; //第一个sheet页
182
183 _myWorksheet.Name = strName;
184 }
185 catch (IOException ex)
186 {
187 throw ex;
188 }
189 catch (Exception ex)
190 {
191 throw ex;
192 }
193 }
194
195
196 #endregion
197
198 #region 删除Sheet
199
200 /// <summary>
201 /// 删除Sheet
202 /// </summary>
203 /// <param name="strName"></param>
204 public void DeleteSheetName(int sheetIndex)
205 {
206 try
207 {
208 _myExcelApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。
209
210 (_myExcelApp.ActiveWorkbook.Sheets[sheetIndex] as Worksheet).Delete();
211 }
212 catch (IOException ex)
213 {
214 throw ex;
215 }
216 catch (Exception ex)
217 {
218 throw ex;
219 }
220 }
221
222 #endregion
223
224 #region 复制新增Sheet
225
226 /// <summary>
227 /// 增加Sheet
228 /// </summary>
229 /// <param name="strName"></param>
230 public void AddSheet(int sheetIndex)
231 {
232 try
233 {
234 _myWorksheet.Copy(Type.Missing, _myWorkbook.Sheets[sheetIndex]);
235 //复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个
236
237 }
238 catch (IOException ex)
239 {
240 throw ex;
241 }
242 catch (Exception ex)
243 {
244 throw ex;
245 }
246 }
247
248 #endregion
249
250 #region 导出Excel文件
251
252 /// <summary>
253 /// 导出Excel文件
254 /// </summary>
255 public int Export(ObservableCollection<MyReport> objdataSouece)
256 {
257 if (_myExcelApp == null) return 0;
258 try
259 {
260 WriteData(objdataSouece);
261 return 1;
262 }
263 catch (IOException ex)
264 {
265 throw ex;
266 }
267
268 }
269
270 /// <summary>
271 /// 导出Excel文件
272 /// </summary>
273 /// <param name="dt">System.Data.DataTable </param>
274 public int Export(System.Data.DataTable dt)
275 {
276 if (_myExcelApp == null) return 0;
277 try
278 {
279 WriteData(dt);
280 return 1;
281 }
282 catch (IOException ex)
283 {
284 throw ex;
285 }
286 }
287
288 /// <summary>
289 /// 导出Excel文件
290 /// </summary>
291 /// <param name="dt"></param>
292 /// <param name="FontSize">字体大小</param>
293 /// <returns></returns>
294 public int Export(System.Data.DataTable dt, int FontSize)
295 {
296 if (_myExcelApp == null) return 0;
297 try
298 {
299 WriteData(dt, FontSize);
300
301 //给单元格加边框
302
303 int RowCount = dt.Rows.Count + 1;
304 for (int i = 1; i <= RowCount; i++)
305 {
306 GetRange("A" + i + "", "E" + i + "");
307
308 //横向加边框
309
310 SetBorderAround(_myRange);
311
312 //单元格内容居中显示
313
314 SetFontAlignment(_myRange, XlHAlign.xlHAlignCenter, XlHAlign.xlHAlignCenter);
315
316 //文本自动换行
317 _myRange.WrapText = true;
318 }
319
320 GetRange("A1", "A" + RowCount + "");
321 SetBorderAround(_myRange);//竖向加边框
322
323 GetRange("B1", "B" + RowCount + "");
324 SetBorderAround(_myRange);
325 GetRange("C1", "C" + RowCount + "");
326 SetBorderAround(_myRange);
327 GetRange("D1", "D" + RowCount + "");
328 SetBorderAround(_myRange);
329 GetRange("E1", "E" + RowCount + "");
330 SetBorderAround(_myRange);
331
332 return 1;
333 }
334 catch (IOException ex)
335 {
336 throw ex;
337 }
338 }
339
340 public int OnlyExport(System.Data.DataTable dt, int FontSize)
341 {
342 if (_myExcelApp == null) return 0;
343 try
344 {
345 WriteOnlyData(dt, FontSize);
346
347 //给单元格加边框
348
349 int RowCount = dt.Rows.Count + 1;
350 for (int i = 1; i <= RowCount; i++)
351 {
352 GetRange("A" + i + "", "G" + i + "");
353
354 //横向加边框
355
356 SetBorderAround(_myRange);
357
358 //单元格内容居中显示
359
360 SetFontAlignment(_myRange, XlHAlign.xlHAlignCenter, XlHAlign.xlHAlignCenter);
361
362 _myRange.RowHeight = 30;//行高
363
364 _myRange.WrapText = true; //文本自动换行
365 }
366
367 GetRange("A1", "A" + RowCount + "");
368 SetBorderAround(_myRange);//竖向加边框
369
370 GetRange("B1", "B" + RowCount + "");
371 SetBorderAround(_myRange);
372 GetRange("C1", "C" + RowCount + "");
373 SetBorderAround(_myRange);
374 GetRange("D1", "D" + RowCount + "");
375 SetBorderAround(_myRange);
376 GetRange("E1", "E" + RowCount + "");
377 SetBorderAround(_myRange);
378 GetRange("F1", "F" + RowCount + "");
379 SetBorderAround(_myRange);
380 GetRange("G1", "G" + RowCount + "");
381 SetBorderAround(_myRange);
382
383 return 1;
384 }
385 catch (IOException ex)
386 {
387 throw ex;
388 }
389 }
390
391 #endregion
392
393 #region 设置单元格字体
394
395
396 /// <summary>
397 /// 设置单元格字体
398
399 /// </summary>
400 /// <param name="range">当前选中单元格集合</param>
401 /// <param name="fontSize">字体大小</param>
402 /// <param name="fontName">字体类型名称</param>
403 /// <param name="isUnderLine">是否显示下划线</param>
404 public void SetRangeFontStyle(Range range, int fontSize, string fontName, bool isUnderLine, Color clBlue)
405 {
406 try
407 {
408 if (_myRange == null) return;
409 range.Font.Size = fontSize;//设置字体大小
410 range.Font.Name = fontName;//设置字体的种类
411 range.Font.Underline = isUnderLine; //设置字体是否有下划线
412 range.Font.Color = clBlue; //字体颜色
413 }
414 catch (IOException ex)
415 {
416 throw ex;
417 }
418 }
419
420 #endregion
421
422 #region 设置单元格格式
423
424
425 /// <summary>
426 /// 设置单元格格式
427
428 /// </summary>
429 /// <param name="range">当前选中单元格集合</param>
430 /// <param name="horizontalAlignment">单元格字体对齐方式</param>
431 /// <param name="columnWidth">单元格宽度</param>
432 public void SetRangeCellsStyle(Range range, XlHAlign horizontalAlignment, int columnWidth, Color background, int borders)
433 {
434 try
435 {
436 if (_myRange == null) return;
437 range.HorizontalAlignment = horizontalAlignment;//设置字体在单元格内的对其方式
438 range.ColumnWidth = columnWidth;//设置单元格的宽度
439 range.Cells.Interior.Color = background;//设置单元格的背景色 // Color.FromArgb(255, 204, 153).ToArgb();
440 range.Borders.LineStyle = borders; //设置单元格边框粗细
441 }
442 catch (IOException ex)
443 {
444 throw ex;
445 }
446 catch (Exception ex)
447 {
448 throw ex;
449 }
450 }
451
452 #endregion
453
454 #region 单元格加边框
455
456 /// <summary>
457 /// 单元格加边框
458 /// </summary>
459 /// <param name="range"></param>
460 public void SetBorderAround(Range range)
461 {
462 try{
463 if (_myRange == null) return;
464 //给单元格加边框
465 range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
466 System.Drawing.Color.Black.ToArgb());
467 }
468 catch (IOException ex)
469 {
470 throw ex;
471 }
472 }
473
474 #endregion
475
476 #region 设置边框样式
477
478 /// <summary>
479 /// 设置边框样式
480 /// </summary>
481 /// <param name="range">当前选中单元格集合</param>
482 /// <param name="borders">设置边框位置，取值范围为：top、down、left、right</param>
483 /// <remarks>
484 /// 默认情况下是加全部边框
485
486 /// </remarks>
487 public void SetBorderEdge(Range range, string borders)
488 {
489 if (_myRange == null) return;
490 try
491 {
492 switch (borders)
493 {
494 case "top":
495 range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle =
496 Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框
497 break;
498 case "down":
499 range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle =
500 Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格下边框为无边框
501 break;
502 case "left":
503 range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle =
504 Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格左边框为无边框
505 break;
506 case "right":
507 range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle =
508 Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格右边框为无边框
509 break;
510 default:
511 SetBorderAround(range); //默认是加全部边框
512 break;
513 }
514 }
515 catch (IOException ex)
516 {
517 throw ex;
518 }
519 catch (Exception ex)
520 {
521 throw ex;
522 }
523
524 }
525
526 #endregion
527
528 #region 设置文本对齐方式
529
530 /// <summary>
531 /// 设置文本对齐方式
532 /// </summary>
533 /// <param name="range">当前选中单元格集合</param>
534 /// <param name="xlhCenter">水平方向</param>
535 /// <param name="xlvCenter">垂直方向</param>
536 public void SetFontAlignment(Range range, XlHAlign xlhCenter, XlHAlign xlvCenter)
537 {
538 try
539 {
540 if (_myRange == null) return;
541 range.EntireColumn.AutoFit(); //自动调整列宽
542 range.HorizontalAlignment = xlhCenter; // 文本水平居中方式
543 range.VerticalAlignment = xlvCenter; //文本垂直居中方式
544 }
545 catch (IOException ex)
546 {
547 throw ex;
548 }
549 catch (Exception ex)
550 {
551 throw ex;
552 }
553
554 }
555
556 #endregion
557
558 #region 文本自动换行
559
560 /// <summary>
561 /// 文本自动换行
562 /// </summary>
563 /// <param name="range">当前选中单元格集合</param>
564 /// <param name="wrapText">文本自动换行标志</param>
565 public void SetWrapText(Range range, bool wrapText)
566 {
567 try
568 {
569 if (_myRange == null) return;
570 range.WrapText = wrapText; //文本自动换行
571 }
572 catch (IOException ex)
573 {
574 throw ex;
575 }
576 catch (Exception ex)
577 {
578 throw ex;
579 }
580 }
581
582 #endregion
583
584 #region 填充颜色
585
586 /// <summary>
587 /// 填充颜色
588 /// </summary>
589 /// <param name="range">当前选中单元格集合</param>
590 /// <param name="colorIndex">颜色索引</param>
591 public void SetInteriorColor(Range range, int colorIndex)
592 {
593 try
594 {
595 if (_myRange == null) return;
596 range.Interior.ColorIndex = colorIndex;//39; //填充颜色为淡紫色
597
598 }
599 catch (IOException ex)
600 {
601 throw ex;
602 }
603 catch (Exception ex)
604 {
605 throw ex;
606 }
607 }
608
609 #endregion
610
611 #region 设置保存是是否弹出对话框
612
613 /// <summary>
614 /// 设置保存是是否弹出对话框
615 /// </summary>
616 /// <param name="displayAlerts">是否弹出标志</param>
617 public void SetSaveDisplayAlerts(bool displayAlerts)
618 {
619 _myExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
620
621 }
622
623 #endregion
624
625 #region 获取单元格集合range
626
627 /// <summary>
628 /// 获取单元格集合range
629 /// </summary>
630 /// <param name="startCellsName">开始列名</param>
631 /// <param name="endCells">结束列名</param>
632 /// <remarks>
633 ///
634 /// </remarks>
635 public void GetRange(string startCellsName, string endCellsName)
636 {
637 try
638 {
639 if (ValidateString(startCellsName) == 0 && ValidateString(endCellsName) == 0)
640 _myRange = (Range)_myWorksheet.get_Range(startCellsName, endCellsName);
641 _myRange.NumberFormatLocal = "@"; //设置单元格格式为文本
642
643 }
644 catch (IOException ex)
645 {
646 throw ex;
647 }
648 catch (Exception ex)
649 {
650 throw ex;
651 }
652 }
653
654 #endregion
655
656 #region 合并单元格
657
658
659 /// <summary>
660 /// 合并单元格
661
662 /// </summary>
663 public void MergeRange()
664 {
665 if (_myRange == null) return;
666 try
667 {
668 _myRange.Merge(0);
669
670 }
671 catch (IOException ex)
672 {
673 throw ex;
674 }
675 catch (Exception ex)
676 {
677 throw ex;
678 }
679 }
680
681 #endregion
682
683 #region 给合并单元格赋值
684
685
686 /// <summary>
687 /// 给合并单元格赋值
688
689 /// </summary>
690 /// <param name="strValue">单元格值</param>
691 public void SetValueToMerge(string strValue)
692 {
693 try
694 {
695 if (_myRange == null) return;
696 _myRange.Value2 = strValue;
697 }
698 catch (IOException ex)
699 {
700 throw ex;
701 }
702 catch (Exception ex)
703 {
704 throw ex;
705 }
706 }
707
708 #endregion
709
710 #region 写列头
711
712
713 /// <summary>
714 /// 写列头
715
716 /// </summary>
717 public void SetColumnTitle()
718 {
719 if (_lColumns == null) return;
720 int cCount = 1;
721 foreach (string str in _lColumns)
722 {
723 WriteData(str, 1, cCount);
724 cCount += 1;
725 }
726 GetRange("A1", "J1");
727 SetRangeFontStyle(_myRange, 12, "黑体", false, System.Drawing.Color.Black);
728 }
729
730 /// <summary>
731 /// 写列头
732
733 /// </summary>
734 /// <param name="FontSize">字体大小</param>
735 public void SetColumnTitle(int FontSize)
736 {
737 if (_lColumns == null) return;
738 int cCount = 1;
739 foreach (string str in _lColumns)
740 {
741 WriteData(str, 1, cCount);
742 cCount += 1;
743 }
744 GetRange("A1", "E1");
745 SetRangeFontStyle(_myRange, FontSize, "黑体", false, System.Drawing.Color.Black);
746 }
747
748 /// <summary>
749 /// 写列头
750
751 /// </summary>
752 /// <param name="B_Rows">开始列名(例：A)</param>
753 /// <param name="E_Rows">结束列名(例：G)</param>
754 /// <param name="FontSize">字体大小</param>
755 public void SetColumnTitle(string B_Rows,string E_Rows,int FontSize)
756 {
757 if (_lColumns == null) return;
758 int cCount = 1;
759 foreach (string str in _lColumns)
760 {
761 WriteData(str, 1, cCount);
762 cCount += 1;
763 }
764 GetRange("" + B_Rows + "1", "" + E_Rows + "1");
765 SetRangeFontStyle(_myRange, FontSize, "黑体", false, System.Drawing.Color.Black);
766 }
767
768 #endregion
769
770 #region 保存对Excel文件的操作
771
772
773 /// <summary>
774 /// 保存成功返回文件路径
775 /// </summary>
776 /// <param name="fileName">文件名称</param>
777 /// <param name="filePath">文件路径</param>
778 public void Save()//(string fileName, string filePath)
779 {
780 try
781 {
782 _myWorkbook.Saved = true;
783 _myWorkbook.Save();
784 }
785 catch (Exception ex)
786 {
787 throw ex;
788 }
789 }
790
791 /// <summary>
792 /// 另存为
793
794 /// </summary>
795 /// <param name="fileName">文件名</param>
796 /// <param name="filePath">保存路径</param>
797 public void SaveAs(string fileName, string filePath)
798 {
799 try
800 {
801 _myWorkbook.SaveAs(filePath + fileName, Type.Missing, Type.Missing,
802 Type.Missing, Type.Missing,
803 Type.Missing, XlSaveAsAccessMode.xlNoChange,
804 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
805 }
806 catch (Exception ex)
807 {
808 throw ex;
809 }
810 }
811
812 /// <summary>
813 /// 另存为
814
815 /// </summary>
816 /// <param name="filePath">保存路径</param>
817 public void SaveAs(string filePath)
818 {
819 try
820 {
821 _myWorkbook.SaveAs(filePath, Type.Missing, Type.Missing,
822 Type.Missing, Type.Missing,
823 Type.Missing, XlSaveAsAccessMode.xlNoChange,
824 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
825 }
826 catch (Exception ex)
827 {
828 throw ex;
829 }
830 }
831
832 #endregion
833
834 #region 释放所有资源
835
836
837 /// <summary>
838 /// 释放所使用的系统资源，保存文件
839 /// </summary>
840 public void Close()
841 {
842 _myWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
843 _myWorksheet = null;
844 _myExcelApp.Quit();
845 _myExcelApp = null;
846 }
847
848 /// <summary>
849 /// 关闭Excel进程
850 /// </summary>
851 /// <returns></returns>
852 public bool KillAllExcel()
853 {
854 try
855 {
856 if (_myExcelApp != null)
857 {
858 _myExcelApp.Quit();
859 System.Runtime.InteropServices.Marshal.ReleaseComObject(_myExcelApp);
860 //释放COM组件，其实就是将其引用计数减1
861 //System.Diagnostics.Process theProc;
862 foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
863 {
864 //先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了，
865 //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它
866 // if (theProc.CloseMainWindow() == false)
867 {
868 if ((_StartTime.Second - theProc.StartTime.Second) >= 1 &&
869 (_StartTime.Second - theProc.StartTime.Second) < 2)
870 {
871 theProc.Kill();
872 }
873 }
874 }
875 _myExcelApp = null;
876 return true;
877 }
878 }
879 catch
880 {
881 return false;
882 }
883 return true;
884 }
885
886
887 #endregion
888
889 #region 打开现有Excel文件
890
891 /// <summary>
892 /// 打开成功返回Excel应用对象
893 /// </summary>
894 private Workbook Open()
895 {
896 if (_myExcelApp == null) return null;
897 try
898 {
899 if (File.Exists(_strFilePath))
900 {
901 _myWorkbook = _myExcelApp.Workbooks.Open(_strFilePath, Type.Missing, Type.Missing, Type.Missing,
902 Type.Missing, Type.Missing, Type.Missing,
903 Type.Missing, Type.Missing, Type.Missing,
904 Type.Missing, Type.Missing, Type.Missing, Type.Missing);
905 }
906 else
907 {
908 _myExcelApp.Workbooks.Add(Type.Missing);
909 _myWorkbook = _myExcelApp.Workbooks[1];
910 }
911 }
912 catch (Exception ex)
913 {
914 throw ex;
915 }
916 return _myWorkbook;
917 }
918
919 #endregion
920
921 #endregion
922
923 #region 功能函数（私有）
924
925 /// <summary>
926 /// 填充列集合
927
928 /// </summary>
929 private void FilllCloumns(string Flag)
930 {
931 if (Flag == "MPage")
932 {
933 if (_lColumns != null) return;
934 _lColumns = new List<string>();
935 _lColumns.Add("患者来源");
936 _lColumns.Add("患者姓名");
937 _lColumns.Add("性别");
938 _lColumns.Add("影像号");
939 _lColumns.Add("状态");
940 _lColumns.Add("检查号");
941 _lColumns.Add("设备类型");
942 _lColumns.Add("检查部位");
943 _lColumns.Add("登记日期");
944 _lColumns.Add("急诊");
945 }
946 if(Flag =="Invent")
947 {
948 if (_lColumns != null) return;
949 _lColumns = new List<string>();
950 _lColumns.Add("生产厂商");
951 _lColumns.Add("药品名称");
952 _lColumns.Add("剂量");
953 _lColumns.Add("数量");
954 _lColumns.Add("日期");
955 }
956 if (Flag == "OnlyPrint")
957 {
958 if (_lColumns != null) return;
959 _lColumns = new List<string>();
960 _lColumns.Add("患者姓名");
961 _lColumns.Add("住院号");
962 _lColumns.Add("科室");
963 _lColumns.Add("检查号");
964 _lColumns.Add("检查项目");
965 _lColumns.Add("时间");
966 _lColumns.Add("接收人");
967 }
968 }
969
970 /// <summary>
971 /// 写数据到Excel文件
972 /// </summary>
973 /// <param name="dtdataSouece"></param>
974 /// <returns></returns>
975 private void WriteData(string str, int rIndex, int cIndex)
976 {
977 Open();
978 _myWorksheet = (Worksheet)_myWorkbook.Sheets[1];
979 _myWorksheet.Name = "Sheet1";
980 ((Range)(_myWorksheet.Cells[rIndex, cIndex])).NumberFormatLocal = "@"; //设置单元个为文本格式
981 _myWorksheet.Cells[rIndex, cIndex] = str;
982 }
983
984 /// <summary>
985 /// 写数据到Excel文件
986 /// </summary>
987 /// <param name="dt"></param>
988 private void WriteData(System.Data.DataTable dt)
989 {
990 if (dt == null || dt.Rows.Count <= 0) return;
991 if (_lColumns == null) return;
992 int rCount = 2;
993 foreach (System.Data.DataRow dr in dt.Rows)
994 {
995 int cCount = 1;
996 foreach (string dc in _lColumns)
997 {
998 WriteData(dr[dc].ToString(), rCount, cCount);
999 cCount += 1;
1000 }
1001 rCount += 1;
1002 }
1003 }
1004
1005 /// <summary>
1006 /// 写数据到Excel文件
1007 /// </summary>
1008 /// <param name="dt"></param>
1009 /// <param name="FontSize">字体大小</param>
1010 private void WriteData(System.Data.DataTable dt, int FontSize)
1011 {
1012 if (dt == null || dt.Rows.Count <= 0) return;
1013 if (_lColumns == null) return;
1014 int rCount = 2;
1015 foreach (System.Data.DataRow dr in dt.Rows)
1016 {
1017 int cCount = 1;
1018 foreach (string dc in _lColumns)
1019 {
1020 WriteData(dr[dc].ToString(), rCount, cCount);
1021 cCount += 1;
1022 }
1023 GetRange("A" + rCount + "", "E" + rCount + "");
1024
1025 SetRangeFontStyle(_myRange, FontSize, "宋体", false, System.Drawing.Color.Black);
1026 rCount += 1;
1027 }
1028 }
1029
1030 private void WriteOnlyData(System.Data.DataTable dt, int FontSize)
1031 {
1032 if (dt == null || dt.Rows.Count <= 0) return;
1033 if (_lColumns == null) return;
1034 int rCount = 2;
1035 foreach (System.Data.DataRow dr in dt.Rows)
1036 {
1037 int cCount = 1;
1038 foreach (string dc in _lColumns)
1039 {
1040 WriteData(dr[dc].ToString(), rCount, cCount);
1041 cCount += 1;
1042 }
1043 GetRange("A" + rCount + "", "G" + rCount + "");
1044
1045 SetRangeFontStyle(_myRange, FontSize, "宋体", false, System.Drawing.Color.Black);
1046 rCount += 1;
1047 }
1048 }
1049
1050 /// <summary>
1051 /// 写数据到Excel文件
1052 /// </summary>
1053 /// <param name="dtdataSouece">ObservableCollection(MyReport)</param>
1054 private void WriteData(ObservableCollection<MyReport> objdataSouece)
1055 {
1056 if (objdataSouece == null) return;
1057 int rCount = 2;
1058 foreach (MyReport mr in objdataSouece)
1059 {
1060 //患者来源，患者姓名，性别，影像号，状态，检查号，设备类型，检查部位，登记日期，是否急诊
1061 WriteData(mr.ADM_ID_ISS, rCount, 1);//A
1062 WriteData(mr.PATNAME, rCount, 2);//B
1063 WriteData(mr.SEX, rCount, 3);//C
1064 WriteData(mr.PATIENTID, rCount, 4);//D
1065 WriteData(mr.STATUS, rCount, 5);//E
1066 WriteData(mr.REQ_PROC_ID, rCount, 6);//F
1067 WriteData(mr.MODALITY, rCount, 7);//G
1068 WriteData(mr.PARTOFCHECK, rCount, 8);//H
1069 WriteData(mr.ENROLDATE, rCount, 9);//I
1070 WriteData((mr.ISEXIGENCE?"急诊":"非急诊"), rCount, 10);//J
1071 //WriteData(mr.PK.ToString(), rCount, 11);//K
1072 //WriteData(mr.IMG_SERV_REG_PK.ToString(), rCount, 12);//L
1073 //WriteData(mr.REQ_PROC_PK.ToString(), rCount, 13);//M
1074 //WriteData(mr.REPORT_REQ_PK.ToString(), rCount, 14);//N
1075 //WriteData(mr.GENERAL_REPORT_PK.ToString(), rCount, 15);//O
1076 rCount += 1;
1077 }
1078 }
1079
1080 /// <summary>
1081 /// 验证输入的单元格列名是否正确
1082 /// </summary>
1083 /// <param name="strValidate">验证字符串</param>
1084 /// <returns></returns>
1085 private int ValidateString(string strValidate)
1086 {
1087 return 0;
1088 //Regex macth = new Regex(@"^[A-Z]\dd$");
1089 //if (macth.IsMatch(strValidate))
1090 //{
1091 // return 0;
1092 //}
1093 //else
1094 //{
1095 // return 1;
1096 //}
1097 }
1098 #endregion
1099
1100 #region DataTable导出到EXCEL
1101 public void ExprotExcel(System.Data.DataTable dt, string FileName)
1102 {
1103 //创建Excel
1104 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
1105 Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing); //创建工作簿（WorkBook：即Excel文件主体本身）
1106
1107 Worksheet ExcelSheet = (Worksheet)ExcelBook.Worksheets[1]; //创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
1108
1109 //Range range = null;
1110
1111 // 如果数据中存在数字类型 可以让它变文本格式显示
1112
1113 ExcelSheet.Cells.NumberFormat = "@";
1114 ExcelSheet.Name = "Sheet1"; //设置工作表名
1115
1116 ExcelSheet.Cells[1, 1] = "患者来源";
1117 ExcelSheet.Cells[1, 2] = "患者姓名";
1118 ExcelSheet.Cells[1, 3] = "性别";
1119 ExcelSheet.Cells[1, 4] = "影像号";
1120 ExcelSheet.Cells[1, 5] = "状态";
1121 ExcelSheet.Cells[1, 6] = "检查号";
1122 ExcelSheet.Cells[1, 7] = "设备类型";
1123 ExcelSheet.Cells[1, 8] = "检查部位";
1124 ExcelSheet.Cells[1, 9] = "登记日期";
1125 ExcelSheet.Cells[1, 10] = "急诊";
1126
1127 //将数据导入到工作表的单元格
1128
1129 for (int i = 0; i < dt.Rows.Count; i++)
1130 {
1131 for (int j = 0; j < dt.Columns.Count; j++)
1132 {
1133 ExcelSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); //Excel单元格第一个从索引1开始
1134
1135 }
1136 }
1137
1138 //表格属性设置
1139
1140 _Range = (Range)ExcelSheet.get_Range("A1", "J1"); //获取Excel多个单元格区域：本例做为Excel表头
1141 //range.Merge(0); //单元格合并动作 要配合上面的get_Range()进行设计
1142 //ExcelSheet.Cells[1, 1] = "Excel单元格赋值"; //Excel单元格赋值
1143
1144 _Range.Font.Size = 12; //设置字体大小
1145 //range.Font.Underline = true; //设置字体是否有下划线
1146 _Range.Font.Name = "黑体"; //设置字体的种类
1147 _Range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
1148 //range.ColumnWidth = 15; //设置单元格的宽度
1149 //range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
1150 //range.Borders.LineStyle = 1; //设置单元格边框的粗细
1151 //range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.FromArgb(255, 204, 153).ToArgb()); //给单元格加边框
1152 //range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框
1153 _Range.EntireColumn.AutoFit(); //自动调整列宽
1154 //range.WrapText = true; //文本自动换行
1155 //range.Interior.ColorIndex = 39; //填充颜色为淡紫色
1156 //range.Font.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //字体颜色
1157 ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
1158
1159 ExcelBook.SaveAs(FileName); //将其进行保存到指定的路径
1160 ExcelBook.Close();
1161 ExcelApp.Quit();
1162 KillAllExcel(ExcelApp); //释放可能还没释放的进程
1163
1164 }
1165
1166 /// 释放Excel进程
1167 /// </summary>
1168 /// <param name="excelApp"></param>
1169 /// <returns></returns>
1170 public bool KillAllExcel(Microsoft.Office.Interop.Excel.Application ExcelApp)
1171 {
1172 try
1173 {
1174 if (ExcelApp != null)
1175 {
1176 ExcelApp.Quit();
1177 System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
1178 //释放COM组件，其实就是将其引用计数减1
1179 //System.Diagnostics.Process theProc;
1180 foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
1181 {
1182 //先关闭图形窗口。如果关闭失败.有的时候在状态里看不到图形窗口的excel了，
1183 //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要释放它
1184 if (theProc.CloseMainWindow() == false)
1185 {
1186 theProc.Kill();
1187 }
1188 }
1189 ExcelApp = null;
1190 return true;
1191 }
1192 }
1193 catch
1194 {
1195 return false;
1196 }
1197 return true;
1198 }
1199
1200
1201 // <summary>
1202 /// 获取当前列列名,并得到EXCEL中对应的列
1203
1204 /// </summary>
1205 /// <param name="count"></param>
1206 /// <returns></returns>
1207 public string ChangeASC(int count)
1208 {
1209 string ascstr = "";
1210
1211 switch (count)
1212 {
1213 case 1:
1214 ascstr = "A";
1215 break;
1216 case 2:
1217 ascstr = "B";
1218 break;
1219 case 3:
1220 ascstr = "C";
1221 break;
1222 case 4:
1223 ascstr = "D";
1224 break;
1225 case 5:
1226 ascstr = "E";
1227 break;
1228 case 6:
1229 ascstr = "F";
1230 break;
1231 case 7:
1232 ascstr = "G";
1233 break;
1234 case 8:
1235 ascstr = "H";
1236 break;
1237 case 9:
1238 ascstr = "I";
1239 break;
1240 case 10:
1241 ascstr = "J";
1242 break;
1243 case 11:
1244 ascstr = "K";
1245 break;
1246 case 12:
1247 ascstr = "L";
1248 break;
1249 case 13:
1250 ascstr = "M";
1251 break;
1252 case 14:
1253 ascstr = "N";
1254 break;
1255 default:
1256 ascstr = "O";
1257 break;
1258 }
1259 return ascstr;
1260 }
1261 #endregion
1262
1263 #region Mpage 打印患者费用列表
1264
1265 /// <summary>
1266 /// 打印患者费用列表
1267
1268 /// </summary>
1269 /// <param name="DT">费用集合</param>
1270 /// <param name="PatNum">检查次数</param>
1271 /// <param name="TotalFee">费用总额</param>
1272 /// <param name="sDate">检查日期</param>
1273 public void PrintFeeListExcel(System.Data.DataTable DT, string PatNum, string TotalFee, string sDate, string filePath)
1274 {
1275 try
1276 {
1277 //创建Excel
1278 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
1279 Workbook ExcelBook = ExcelApp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing,
1280 Type.Missing, Type.Missing, Type.Missing,
1281 Type.Missing, Type.Missing, Type.Missing,
1282 Type.Missing, Type.Missing, Type.Missing, Type.Missing);
1283
1284 Worksheet ExcelSheet = (Worksheet)ExcelBook.Worksheets[1]; //创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
1285
1286
1287 ExcelSheet.Cells[2, 1] = "检查日期：" + sDate + " 打印时间："+DateTime.Now.ToString("yyyy-MM-dd") ;
1288 ExcelSheet.Cells.NumberFormat = "@";
1289 //写数据
1290
1291 for (int i = 0; i < DT.Rows.Count; i++)
1292 {
1293 for (int j = 0; j < DT.Columns.Count; j++)
1294 {
1295 //Excel单元格第一个从索引1开始
1296
1297 ExcelSheet.Cells[i + 4, j + 1] = DT.Rows[i][j].ToString();
1298 }
1299 }
1300
1301 //表格属性设置
1302
1303 for (int n = 0; n < DT.Rows.Count + 2; n++)
1304 {
1305 string start = "A" + (n + 2).ToString();
1306 string end = ChangeASC(DT.Columns.Count) + (n + 2).ToString();
1307
1308 //获取Excel多个单元格区域
1309
1310 Range _Range = (Range)ExcelSheet.get_Range(start, end);
1311 if (n > 0)//设置表头
1312 {
1313 _Range.Font.Size = 12; //设置字体大小
1314 _Range.Font.Name = "宋体"; //设置字体的种类
1315
1316 // _Range.EntireColumn.AutoFit(); //自动调整列宽
1317 _Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
1318 }
1319 }
1320 //添加合计
1321 Microsoft.Office.Interop.Excel.Range range;
1322 range = ExcelSheet.get_Range((object)ExcelSheet.Cells[DT.Rows.Count + 4, 1], (object)ExcelSheet.Cells[DT.Rows.Count + 4, DT.Columns.Count]);
1323 range.Merge(ExcelSheet.get_Range((object)ExcelSheet.Cells[DT.Rows.Count + 4, 1], (object)ExcelSheet.Cells[DT.Rows.Count + 4, DT.Columns.Count]).MergeCells);
1324 // range.Font.Name = "黑体";//设置字体
1325 // range.Font.Bold = false;//设置加粗
1326 range.Font.Size = 14;//字体型号
1327 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
1328
1329 ExcelSheet.Cells[DT.Rows.Count + 4, 1] = "总费用额(元): ¥" + TotalFee + " 检查总数(次):" + PatNum;
1330
1331 ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
1332
1333 //ExcelBook.SaveCopyAs(@"C:\MedPACS 5.0\config\temp.xlsx"); //将其进行保存到指定的路径
1334 ExcelSheet.PrintOutEx(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
1335 Type.Missing, Type.Missing, Type.Missing,
1336 Type.Missing, Type.Missing);
1337 //释放可能还没释放的进程
1338
1339 ExcelBook.Close();
1340 ExcelApp.Quit();
1341 KillAllExcel(ExcelApp);
1342
1343 // GlobalVar.ShowMsgInfo("打印成功!");
1344
1345 }
1346 catch (Exception ex)
1347 {
1348 GlobalVar.ShowMsgWarning("打印患者费用列表失败！"+ex.Message);
1349 }
1350 }
1351 #endregion
1352
1353 }
1354
1355 }
浙公网安备 33010602011771号