急求一段用C#写的将excel数据导入到sql server中以及从sql server中导出的代码

答案：给你一个类吧，数据库存放在本地计算机上，其中Constr变量中的DataBase、UId及Pwd这三个参数需要自己设定，然后将这个类的命名空间改为你项目的命名空间即可。
class ExportOrInput
    {
        //从Excel中导入数据到Sql Server
        public static string Constr = "Server=.;DataBase=***;UId=***;Pwd=***";
        public static bool ExcelExportToSqlServer(string excelFilePath, string sheetName, string connectionString, string tabName)
        {
            DataSet ds = new DataSet();
            try
            {
                //获取Excel某表的全部信息
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strCon);
                conn.Open();
                string strCmd = string.Format("Select * from [{0}$]", sheetName);
                OleDbDataAdapter oda = new OleDbDataAdapter(strCmd, conn);
                conn.Close();
                oda.Fill(ds, sheetName);
                if (ds.Tables.Count == 0)
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            try
            {
                DataTable dt = ds.Tables[0];
                string sql = "if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tabName + "]') ";
                sql += "and OBJECTPROPERTY(id, N'IsUserTable') = 1) ";
                sql += "create Table [" + tabName + "](";
                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    sql += "[" + dt.Columns[i].ColumnName + "] [varchar] (50)";
                    if (i != dt.Columns.Count - 1) sql += ",";
                }
                sql += ")";
                bool issucc = UpDataInfo(connectionString, sql);//传递一个连接字符串和sql语句更新数据库,此处创建表
                if (!issucc) return false;
                using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
                {
                    sbc.DestinationTableName = tabName;//设置数据库表名
                    sbc.WriteToServer(dt);
                }
            }
            catch
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 传递一个连接字符串和sql语句更新数据库
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool UpDataInfo(string connStr, string sql)
        {
            try
            {
                SqlConnection con = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                int i = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
            return true;
        }
        //从Sql Server中导出数据到Excel
        public static void SqlServerExportToExcel(DataTable dt, string strExcelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            excel.Visible = true;
            Microsoft.Office.Interop.Excel._Workbook wkb = excel.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel._Worksheet wks = (Microsoft.Office.Interop.Excel._Worksheet)wkb.ActiveSheet;
            wks.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;

            int rowIndex = 1;
            int colIndex = 0;

            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = "   " + row[col.ColumnName].ToString();
                }
            }
            wkb.SaveAs(strExcelFileName, true, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            wkb.Close(false, true, null);
            excel.Quit();
        }
}

上一个：C#和数据库连接以后怎样把数据导入到excel表中啊？
下一个：excel中删除a表中的一整行时，b表中引用a表的那一整行也自动被删除，删除的不仅仅是内容也包括整行单元格