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;
            }
        }

 

转载于:https://www.cnblogs.com/ingstyle/p/4166387.html