C#_DataTable匯出Execl為自訂標題

標籤：

        public bool ExportExcel(DataTable tb, string path, string tbName)        {            //excel 2003格式            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";            //Excel 2007格式            //string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";            try            {                using (OleDbConnection con = new OleDbConnection(connString))                {                    con.Open();                    StringBuilder strSQL = new StringBuilder();                    strSQL.Append("CREATE TABLE ").Append("[" + tbName + "]");                    strSQL.Append("(");                    for (int i = 0; i < tb.Columns.Count; i++)                    {                        switch (tb.Columns[i].ColumnName)                        {                            case "ID":                                tb.Columns[i].ColumnName = "編號";                                break;                            case "name":                                tb.Columns[i].ColumnName = "姓名";                                break;                            case "phone":                                tb.Columns[i].ColumnName = "電話";                                break;                            case "address":                                tb.Columns[i].ColumnName = "地址";                                break;                            case "scheduleLength":                                tb.Columns[i].ColumnName = "預定下單長度";                                break;                            case "welfare":                                tb.Columns[i].ColumnName = "福利的類型";                                break;                            case "commodityType":                                tb.Columns[i].ColumnName = "搶購商品類型";                                break;                            case "discount":                                tb.Columns[i].ColumnName = "折扣";                                break;                            case "time":                                tb.Columns[i].ColumnName = "日期";                                break;                            case "remarks":                                tb.Columns[i].ColumnName = "備忘";                                break;                        }                        strSQL.Append("[" + tb.Columns[i].ColumnName + "] text,");                    }                    strSQL = strSQL.Remove(strSQL.Length - 1, 1);                    strSQL.Append(")");                    OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);                    cmd.ExecuteNonQuery();                    string column = string.Empty;                    for (int i = 0; i < tb.Rows.Count; i++)                    {                        strSQL.Clear();                        StringBuilder strfield = new StringBuilder();                        StringBuilder strvalue = new StringBuilder();                        for (int j = 0; j < tb.Columns.Count; j++)                        {                            switch (tb.Columns[j].ColumnName)                            {                                case "ID":                                    tb.Columns[j].ColumnName = "編號";                                    break;                                case "name":                                    tb.Columns[j].ColumnName = "姓名";                                    break;                                case "phone":                                    tb.Columns[j].ColumnName = "電話";                                    break;                                case "address":                                    tb.Columns[j].ColumnName = "地址";                                    break;                                case "scheduleLength":                                    tb.Columns[j].ColumnName = "預定下單長度";                                    break;                                case "welfare":                                    tb.Columns[j].ColumnName = "福利的類型";                                    break;                                case "commodityType":                                    tb.Columns[j].ColumnName = "搶購商品類型";                                    break;                                case "discount":                                    tb.Columns[j].ColumnName = "折扣";                                    break;                                case "time":                                    tb.Columns[j].ColumnName = "日期";                                    break;                                case "remarks":                                    tb.Columns[j].ColumnName = "備忘";                                    break;                            }                            strfield.Append("[" + tb.Columns[j].ColumnName + "]");                            strvalue.Append("‘" + tb.Rows[i][j].ToString() + "‘");                            if (j != tb.Columns.Count - 1)                            {                                strfield.Append(",");                                strvalue.Append(",");                            }                            else                            {                            }                        }                        cmd.CommandText = strSQL.Append(" insert into [" + tbName + "]( ")                            .Append(strfield.ToString())                            .Append(") values (").Append(strvalue).Append(")").ToString();                        cmd.ExecuteNonQuery();                    }                    con.Close();                }                return true;            }            catch (Exception ex)            {                return false;            }        }

 

C#_DataTable匯出Execl為自訂標題