C# 导出 Excel
[一篮饭特稀原创,转载请注明出自http://www.cnblogs.com/wanghafan/p/3228240.html]
1 using System; 2 using System.Data; 3 using System.IO; 4 using System.Windows.Forms; 5 using Excel = Microsoft.Office.Interop.Excel; 6 using System.Collections.Generic; 7 8 namespace PlaneExcel 9 { 10 static class Export2ExcelAction 11 { 12 /// <summary> 13 /// 单表 14 /// </summary> 15 /// <param name="FileName"></param> 16 /// <param name="dataTable"></param> 17 /// <param name="SheetName"></param> 18 /// <param name="form"></param> 19 public static void Export2Excel(string FileName, DataTable dataTable, string SheetName, System.Windows.Forms.Form form) 20 { 21 Excel.Application excelApplication = null; 22 try 23 { 24 excelApplication = new Excel.Application(); 25 } 26 catch (Exception ex) 27 { 28 System.Windows.Forms.MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel..."); 29 return; 30 } 31 excelApplication.DisplayAlerts = false; 32 Excel.Workbooks workbooks = excelApplication.Workbooks; 33 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//建工作簿 34 Excel.Worksheet TableASheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); 35 TableASheet.Name = SheetName; 36 TableASheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; 37 Excel.Range range = TableASheet.get_Range("A1", Type.Missing); 38 int ColumnIndex = 1; 39 foreach (DataColumn dc in dataTable.Columns) 40 { 41 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + 1, Type.Missing); 42 range.Value2 = dc.ColumnName; 43 ColumnIndex++; 44 } 45 int RowIndex = 2; 46 foreach (DataRow dr in dataTable.Rows) 47 { 48 ColumnIndex = 1; 49 foreach (DataColumn dc in dataTable.Columns) 50 { 51 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + RowIndex, Type.Missing); 52 range.Value2 = dr[dc.ColumnName].ToString(); 53 ColumnIndex++; 54 } 55 RowIndex++; 56 } 57 try 58 { 59 ((Excel.Worksheet)workbook.Sheets.Item["Sheet1"]).Delete(); 60 workbook.Saved = true; 61 workbook.SaveCopyAs(FileName); 62 } 63 catch (FileNotFoundException fileEx) 64 { 65 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,\n请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); 66 return; 67 } 68 catch (Exception ex) 69 { 70 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,\n请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); 71 return; 72 } 73 finally 74 { 75 workbook.Close(); 76 workbooks.Close(); 77 excelApplication.Quit(); 78 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); 79 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); 80 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication); 81 GC.Collect(); 82 } 83 84 if (MessageBox.Show("导出成功,是否立即打开?", "导出结果", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.OK) 85 System.Diagnostics.Process.Start(FileName); 86 } 87 /// <summary> 88 /// 多表 89 /// </summary> 90 /// <param name="FileName"></param> 91 /// <param name="li_dt"></param> 92 /// <param name="li_SheetName"></param> 93 /// <param name="form"></param> 94 public static void Export2Excel(string FileName, List<DataTable> li_dt, List<string> li_SheetName, System.Windows.Forms.Form form) 95 { 96 Excel.Application excelApplication = null; 97 try 98 { 99 excelApplication = new Excel.Application(); 100 } 101 catch (Exception ex) 102 { 103 System.Windows.Forms.MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel..."); 104 return; 105 } 106 excelApplication.DisplayAlerts = false; 107 Excel.Workbooks workbooks = excelApplication.Workbooks; 108 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 109 for (int i = 0; i < li_dt.Count; i++) 110 { 111 Excel.Worksheet TableASheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); 112 TableASheet.Name = li_SheetName[i]; 113 TableASheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; 114 Excel.Range range = TableASheet.get_Range("A1", Type.Missing); 115 int ColumnIndex = 1; 116 foreach (DataColumn dc in li_dt[i].Columns) 117 { 118 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + 1, Type.Missing); 119 range.Value2 = dc.ColumnName; 120 ColumnIndex++; 121 } 122 int RowIndex = 2; 123 foreach (DataRow dr in li_dt[i].Rows) 124 { 125 ColumnIndex = 1; 126 foreach (DataColumn dc in li_dt[i].Columns) 127 { 128 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + RowIndex, Type.Missing); 129 range.Value2 = dr[dc.ColumnName].ToString(); 130 ColumnIndex++; 131 } 132 RowIndex++; 133 } 134 } 135 try 136 { 137 ((Excel.Worksheet)workbook.Sheets.Item["Sheet1"]).Delete(); 138 workbook.Saved = true; 139 workbook.SaveCopyAs(FileName); 140 } 141 catch (FileNotFoundException fileEx) 142 { 143 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,\n请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); 144 return; 145 } 146 catch (Exception ex) 147 { 148 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,\n请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); 149 return; 150 } 151 finally 152 { 153 workbook.Close(); 154 workbooks.Close(); 155 excelApplication.Quit(); 156 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); 157 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); 158 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication); 159 GC.Collect(); 160 } 161 162 if (MessageBox.Show("导出成功,是否立即打开?", "导出结果", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.OK) 163 System.Diagnostics.Process.Start(FileName); 164 } 165 public static string NunberToChar(int number) 166 { 167 if (1 <= number && 26 >= number) 168 { 169 int num = number + 64; 170 System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding(); 171 byte[] btNumber = new byte[] { (byte)num }; 172 return asciiEncoding.GetString(btNumber); 173 } 174 else if (number > 26) 175 { 176 int leftValue = 0; 177 int rightValue = 0; 178 leftValue = number / 26; 179 rightValue = number % 26; 180 181 if (number % 26 == 0) 182 { 183 leftValue = leftValue - 1; 184 rightValue = ((number - 1) % 26) + 1; 185 } 186 return NunberToChar(leftValue) + NunberToChar(rightValue); 187 } 188 return string.Empty; 189 } 190 191 } 192 }