C# DataTable WriteToExcel

1：帮助类

  1 public class ExcelHander
  2     {
  3         private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  4         private Workbook m_objBook;
  5         private Workbooks m_objBooks;
  6         private Application m_objExcel;
  7         private Missing miss = Missing.Value;
  8         public static Missing MissValue = Missing.Value;
  9         private Worksheet sheet;
 10 
 11         public void CloseExcelApplication()
 12         {
 13             try
 14             {
 15                 foreach (Process process in Process.GetProcessesByName("excel"))
 16                 {
 17                     process.Kill();
 18                 }
 19             }
 20             catch (Exception exception)
 21             {
 22                 throw new Exception(exception.Message + exception.StackTrace, exception.InnerException);
 23             }
 24         }
 25 
 26         public void CreateExceFile()
 27         {
 28             this.m_objExcel = new ApplicationClass();
 29             this.UserControl(false);
 30             this.m_objBooks = this.m_objExcel.Workbooks;
 31             this.m_objBook = this.m_objBooks.Add(this.miss);
 32             this.sheet = (Worksheet)this.m_objBook.ActiveSheet;
 33         }
 34 
 35         public string GetAix(int x, int y)
 36         {
 37             char[] chArray = this.AList.ToCharArray();
 38             if (x >= 0x1a)
 39             {
 40                 return "";
 41             }
 42             string str = "";
 43             return (str + chArray[x - 1].ToString() + y.ToString());
 44         }
 45 
 46         public Range getRange(int x1, int y1, int x2, int y2)
 47         {
 48             return this.sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
 49         }
 50 
 51         public object getValue(int x, int y)
 52         {
 53             return this.sheet.get_Range(this.GetAix(x, y), this.miss).Cells.get_Value(Missing.Value);
 54         }
 55 
 56         public void insertRow(int y)
 57         {
 58             Range range = this.sheet.get_Range(this.GetAix(1, y), this.GetAix(0x19, y));
 59             range.Copy(this.miss);
 60             range.Insert(XlDirection.xlDown, this.miss);
 61             range.get_Range(this.GetAix(1, y), this.GetAix(0x19, y));
 62             range.Select();
 63             this.sheet.Paste(this.miss, this.miss);
 64         }
 65 
 66         public void mergeCell(int x1, int y1, int x2, int y2)
 67         {
 68             this.sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Merge(Missing.Value);
 69         }
 70 
 71         public Worksheet NewSheet()
 72         {
 73             return (Worksheet)this.m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 74         }
 75 
 76         public void OpenExcelFile(string filename)
 77         {
 78             this.m_objExcel = new ApplicationClass();
 79             this.UserControl(false);
 80             this.m_objExcel.Workbooks.Open(filename, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss);
 81             this.m_objBooks = this.m_objExcel.Workbooks;
 82             this.m_objBook = this.m_objExcel.ActiveWorkbook;
 83             this.sheet = (Worksheet)this.m_objBook.ActiveSheet;
 84         }
 85 
 86         public void past()
 87         {
 88             string link = "a,b,c,d,e,f,g";
 89             this.sheet.Paste(this.sheet.get_Range(this.GetAix(10, 10), this.miss), link);
 90         }
 91 
 92         public void ReleaseExcel()
 93         {
 94             this.m_objExcel.Quit();
 95             Marshal.ReleaseComObject(this.m_objExcel);
 96             Marshal.ReleaseComObject(this.m_objBooks);
 97             Marshal.ReleaseComObject(this.m_objBook);
 98             Marshal.ReleaseComObject(this.sheet);
 99             GC.Collect();
100         }
101 
102         public void SaveAs(string FileName)
103         {
104             this.m_objBook.SaveAs(FileName, this.miss, this.miss, this.miss, this.miss, this.miss, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, this.miss, this.miss, this.miss, this.miss);
105             this.m_objBook.Close(false, this.miss, this.miss);
106         }
107 
108         public void setBorder(int x1, int y1, int x2, int y2, int Width)
109         {
110             this.sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders.Weight = Width;
111         }
112 
113         public void setValue(int x, int y, string text)
114         {
115             this.sheet.get_Range(this.GetAix(x, y), this.miss).set_Value(this.miss, text);
116         }
117 
118         public void setValue(int x, int y, string text, System.Drawing.Font font, Color color)
119         {
120             this.setValue(x, y, text);
121             Range range = this.sheet.get_Range(this.GetAix(x, y), this.miss);
122             range.Font.Size = font.Size;
123             range.Font.Bold = font.Bold;
124             range.Font.Color = color;
125             range.Font.Name = font.Name;
126             range.Font.Italic = font.Italic;
127             range.Font.Underline = font.Underline;
128         }
129 
130         public void UserControl(bool usercontrol)
131         {
132             if (this.m_objExcel != null)
133             {
134                 this.m_objExcel.UserControl = usercontrol;
135                 this.m_objExcel.DisplayAlerts = usercontrol;
136                 this.m_objExcel.Visible = usercontrol;
137             }
138         }
139 
140         public Worksheet CurrentSheet
141         {
142             get
143             {
144                 return this.sheet;
145             }
146             set
147             {
148                 this.sheet = value;
149             }
150         }
151 
152         public Workbook CurrentWorkBook
153         {
154             get
155             {
156                 return this.m_objBook;
157             }
158             set
159             {
160                 this.m_objBook = value;
161             }
162         }
163 
164         public Workbooks CurrentWorkBooks
165         {
166             get
167             {
168                 return this.m_objBooks;
169             }
170             set
171             {
172                 this.m_objBooks = value;
173             }
174         }
175     }

View Code

2：实现方法

 1 public static void WirteToExcel()
 2         {
 3             if (ConfigReader.ReportTable.Rows.Count > 0)
 4             {
 5                 ExcelHander excelwirter = new ExcelHander();
 6                 int allNum = ConfigReader.ReportTable.Rows.Count;//统计下载总数
 7                 int resultfulNum = ConfigReader.ReportTable.Select("连接状态='有 效'").Length;//有效的下载连接数
 8                 int errorNum = ConfigReader.ReportTable.Select("连接状态='无 效'").Length;//无效的下载连接数
 9                 try
10                 {
11                     excelwirter.CreateExceFile();
12                     #region  写入细节
13                     int i = 1;
14                     int j = 1;
15                     foreach (var item in ConfigReader.ReportTable.Columns)
16                     {
17                         excelwirter.setValue(j, 1, ConfigReader.ReportTable.Columns[j - 1].ToString(), new System.Drawing.Font("TimesNewRoman", 12, FontStyle.Bold), Color.Black);
18                         j++;
19                     }
20                     i = 2;
21                     j = 1;
22                     foreach (DataRow dr in ConfigReader.ReportTable.Rows)
23                     {
24                         if (dr[2].ToString() == "无 效")
25                         {
26                             excelwirter.setValue(1, i, dr[0].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Red);
27                             excelwirter.setValue(2, i, dr[1].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Red);
28                             excelwirter.setValue(3, i, dr[2].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Red);
29                             excelwirter.setValue(4, i, dr[3].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Red);
30                         }
31                         else
32                         {
33                             excelwirter.setValue(1, i, dr[0].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Black);
34                             excelwirter.setValue(2, i, dr[1].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Black);
35                             excelwirter.setValue(3, i, dr[2].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Black);
36                             excelwirter.setValue(4, i, dr[3].ToString(), new System.Drawing.Font("TimesNewRoman", 10, FontStyle.Regular), Color.Black);
37                         }
38                         i++;
39                     }
40                     #endregion
41                     #region//写入汇总
42                     excelwirter.setValue(1, ++i, "下载总数：" + allNum.ToString(), new System.Drawing.Font("TimesNewRoman", 12, FontStyle.Bold), Color.Black);
43                     excelwirter.setValue(1, ++i, "连接状态正常数：" + resultfulNum.ToString(), new System.Drawing.Font("TimesNewRoman", 12, FontStyle.Bold), Color.Black);
44                     excelwirter.setValue(1, ++i, "连接异常数：" + errorNum.ToString(), new System.Drawing.Font("TimesNewRoman", 12, FontStyle.Bold), Color.Black);
45                     #endregion
46                     excelwirter.SaveAs(ConfigReader.WorkingPath + "report.xlsx");
47                 }
48                 catch (Exception)
49                 {
50                     Console.WriteLine("写入异常，请确定excel文件没有被独占");
51                 }
52                 finally
53                 {
54                     excelwirter.ReleaseExcel();
55                     excelwirter.CloseExcelApplication();
56                 }
57             }
58         }

View Code