Datatable另存為Excel的方法,Datatable導出到Excel的方法

在數據程序的開放中,有時需要把數據中的查詢結果保存到Excel文件中,下面的過程實現了這個功能.

使用說明:參數excel為要保存的excel文件名;tb為內存中的有數據的Datatable;tbname是插入到excel文件中的工作表的名稱.程序中已經對部分數據類型進行了分別處理,如有特殊需要請自己修改!

        static public void TableToExcel(string excel, DataTable tb,string tbname)
        {
            try
            {
                OleDbConnection connex = GetConnFromExcel(excel); //獲取到excel文件的oledb連接
                string ct = "CREATE TABLE " + tbname + " (";                //以下生成一個sql命令向excel中插入一個表
                foreach (DataColumn clmn in tb.Columns)
                {
                    switch (clmn.DataType.Name)                                     //根據不同數據類型分別處理
                    {
                        case "Decimal":
                            ct += clmn.ColumnName + " Decimal,";
                            break;
                        case "Double":
                            ct += clmn.ColumnName + " Double,";
                            break;
                        default:
                            ct += clmn.ColumnName + " string,";
                            break;
                    }
                }
                ct = ct.Substring(0, ct.Length - 1) + ")";
                OleDbCommand cmd1 = new OleDbCommand(ct, connex);
                cmd1.ExecuteNonQuery();                                                              //向excel中插入一個表

                foreach (DataRow r in tb.Rows)                                                       //下面向excel中一行一行寫入數據
                {
                    string fs = "", vs = "";
                    foreach (DataColumn clmn in tb.Columns)
                    {
                        fs += clmn.ColumnName + ",";
                        if (r[clmn.ColumnName] == DBNull.Value)
                        {
                            vs += "null,";
                            continue;
                        }
                        switch (clmn.DataType.Name)                                                   //根據不同數據類型分別處理
                        {
                            case "Decimal":
                                vs += ((decimal)r[clmn.ColumnName]).ToString("0.00") + ",";
                                break;
                            case "Double":
                                vs += ((double)r[clmn.ColumnName]).ToString("0.00") + ",";
                                break;
                            case "DateTime":
                                vs += "'" + ((DateTime)r[clmn.ColumnName]).ToShortDateString() + "',";
                                break;
                            default:
                                vs += "'" + r[clmn.ColumnName].ToString() + "',";
                                break;
                        }
                    }
                    string sqlstr = "insert into [" + tbname + "$] (" + fs.Substring(0, fs.Length - 1) + ") values (" + vs.Substring(0, vs.Length - 1) + ")";
                    OleDbCommand cmd = new OleDbCommand(sqlstr, connex);
                    cmd.ExecuteNonQuery();                                            //向excel中插入數據
                }
                connex.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }