Question

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

Answer 1

给你一个类吧，数据库存放在本地计算机上，其中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();
}
}