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 }
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 }