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