C#中Oracle数据库转为Excel文件
查资料后整改实现可以将Oracle数据库转为Excel
后续补充
2
using System.Data
using Microsoft.Office.Interop.Excel; 3 using System.Runtime.InteropServices; 4 5 public void SaveAsExcel(string fileName, int lineId) 6 { 7 DataSetToExcel(GetDataBaseTable(lineId), fileName); 24 } 25 26 27 public System.Data.DataTable GetDataBaseTable(int lineId) 28 { 29 string lineIdString = lineId.ToString(); 30 string cmdString = "SELECT * FROM BRANCH_BASIC_INFO WHERE LINE_ID = " + lineIdString; 32 33 System.Data.DataTable dataTable = new System.Data.DataTable(); //DataTable是一个存在内存中的2维表,可以在内存中操作这张表格 35 36 try 37 { 38 using (OracleConnection conn = new OracleConnection(connectionString)) 39 { 40 conn.Open(); 41 using (OracleDataAdapter dataAdaper = new OracleDataAdapter(cmdString, conn)) 42 { 43 dataAdaper.Fill(dataTable); //.Fill()可以填充dataTable和dataSet 44 } 45 conn.Close(); 46 } 47 } 48 catch 49 { 50 return null; 51 } 52 53 return dataTable; 54 55 } 56 57 //dataTable转为Excel表格 58 public bool DataSetToExcel(System.Data.DataTable dataTable, string saveFile) 59 { 61 if (dataTable == null) 62 { 63 return false; 64 } 65 66 Microsoft.Office.Interop.Excel._Application excel; 67 Microsoft.Office.Interop.Excel._Workbook workBook; 68 Microsoft.Office.Interop.Excel._Worksheet workSheet; 69 object misValue = System.Reflection.Missing.Value; 70 71 excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); 72 workBook = excel.Workbooks.Add(misValue); 73 workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet; 74 75 int rowIndex = 1; 76 int colIndex = 0; 77 78 //取得标题 79 foreach (DataColumn col in dataTable.Columns) 80 { 81 colIndex++; 82 excel.Cells[1, colIndex] = col.ColumnName; 83 } 84 85 //取得表格中数据 86 foreach (DataRow row in dataTable.Rows) 87 { 88 rowIndex++; 89 colIndex = 0; 90 91 foreach (DataColumn col in dataTable.Columns) 92 { 93 colIndex++; 94 excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim(); 95 96 // 97 workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; 98 99 } 100 } 101 102 excel.Visible = false; 103 workBook.SaveAs(saveFile 104 , Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal 105 , misValue 106 , misValue 107 , misValue 108 , misValue 109 , Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive 110 , misValue 111 , misValue 112 , misValue 113 , misValue 114 , misValue); 115 116 dataTable = null; 117 workBook.Close(true, misValue, misValue); 118 excel.Quit(); 119 Kill(excel); 120 ReleaseObject(workSheet); 121 ReleaseObject(workBook); 122 ReleaseObject(excel); 123 return true; 124 } 125 126 public void ReleaseObject(object obj) 127 { 128 try 129 { 130 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); 131 obj = null; 132 } 133 catch 134 { 135 obj = null; 136 } 137 finally 138 { 139 GC.Collect(); 140 } 141 } 142 143 [DllImport("User32.dll", CharSet = CharSet.Auto)] 144 public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); 146 public void Kill(Microsoft.Office.Interop.Excel._Application excel) 148 { 149 try 150 { 151 IntPtr t = new IntPtr(excel.Hwnd); 152 int k = 0; 153 154 GetWindowThreadProcessId(t, out k); 155 System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); 156 p.Kill(); 157 } 158 catch { } 159 }
另外,其中出现一个异常 无法嵌入互操作类型
解决办法:将Microsoft.Office.Interop.Excel属性中嵌入互操作类型改为false