程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C#中數據庫數據如何導出至Excel表格

C#中數據庫數據如何導出至Excel表格

編輯：C#入門知識

有時候需要將數據庫的數據導出至Excel表格表格，以便進行查看和分析，那麼如何導出呢？下面用代碼來實現。
首先，新建一個工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle數據庫為例（只要讀出DataTable或DataSet就行了，哪種數據庫沒關系）。

1、創建一個表格，並插入如下數據。

drop table TABLETESTEXCEL;
create table TABLETESTEXCEL
(
col_id NUMBER not null,
col_name VARCHAR2(32),
col_age NUMBER,
col_sex VARCHAR2(4),
col_work VARCHAR2(32),
col_mony FLOAT
);
數據：

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (1, '吳一', 25, '男', '.NET', 5000);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (2, '孫二', 24, '男', 'JAVA', 4999);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (3, '張三', 25, '男', 'PHP', 5001);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (4, '李四', 26, '男', 'DELPHI', 5002);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (5, '王五', 27, '男', 'C++', 5003);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (6, '趙六', 25, '男', 'C', 4008);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (7, '燕七', 25, '男', '數據庫', 4007);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (8, '胡八', 25, '男', 'JSP', 5005);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (9, '錢九', 25, '男', 'ASP.NET', 4005);

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (10, '沈十', 25, '男', 'VB', 4000);
commit;
2、C#代碼實現

數據庫操作的類：

public class DataBaseHelper
{
    public static DataTable ExecuterQuery(string connectionString, string commandSql)
    {
        DataTable dataTable = new DataTable();

        try
        {
          using (OracleConnection oracleConnection =
          new OracleConnection(connectionString))
          {
            oracleConnection.Open();

            using (OracleDataAdapter oracleDataAdapter =
            new OracleDataAdapter(commandSql,oracleConnection))
                {
                   oracleDataAdapter.Fill(dataTable);
                }

                oracleConnection.Close();
          }
        }
        catch
        {
          return null;
        }

     return dataTable;
    }
}

public class DataBaseDao
{
   public static DataTable GetDataBaseTable()
   {
      string sql = " SELECT * FROM tableTestExcel";

      return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql);
   }
}
導出Excel的類：

public class DataChangeExcel
{
        /// <summary>
        /// 數據庫轉為excel表格
         /// </summary>
       /// <param name="dataTable">數據庫數據</param>
       /// <param name="SaveFile">導出的excel文件</param>
        public static void DataSetToExcel(DataTable dataTable, string SaveFile)
        {
            Excel.Application excel;

            Excel._Workbook workBook;

            Excel._Worksheet workSheet;

            object misValue = System.Reflection.Missing.Value;

            excel = new Excel.ApplicationClass();

            workBook = excel.Workbooks.Add(misValue);

            workSheet = (Excel._Worksheet)workBook.ActiveSheet;

            int rowIndex = 1;

            int colIndex = 0;

            //取得標題
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;

                excel.Cells[1, colIndex] = col.ColumnName;
            }

            //取得表格中的數據
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;

                colIndex = 0;

                foreach (DataColumn col in dataTable.Columns)
                {
                   colIndex++;

                   excel.Cells[rowIndex, colIndex] =

                        row[col.ColumnName].ToString().Trim();

                    //設置表格內容居中對齊
                      workSheet.get_Range(excel.Cells[rowIndex, colIndex],

                        excel.Cells[rowIndex, colIndex]).HorizontalAlignment =

                        Excel.XlVAlign.xlVAlignCenter;
                }
            }

            excel.Visible = false;

            workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue,

                misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,

                misValue, misValue, misValue, misValue, misValue);

            dataTable = null;

            workBook.Close(true, misValue, misValue);

            excel.Quit();

            PublicMethod.Kill(excel);//調用kill當前excel進程

            releaseObject(workSheet);

            releaseObject(workBook);

            releaseObject(excel);

        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
}
關閉進程的類:

public class PublicMethod
{
   [DllImport("User32.dll", CharSet = CharSet.Auto)]

   public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

   public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
   {
       try
       {
         IntPtr t = new IntPtr(excel.Hwnd);

         int k = 0;

         GetWindowThreadProcessId(t, out k);

         System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

         p.Kill();
        }
        catch
        { }
   }
}
寫好了如上的類，那麼開始調用吧，調用：