//excel数据导出
public DataSet Import(string file)
{
OleDbConnection my_conn;
OleDbDataAdapter my_Adapter;
DataSet ds = new DataSet();
//数据库连接
string my_StrConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + file;
String my_StrSelect1 = "SELECT * FROM [Sheet1$]";
my_conn = new OleDbConnection(my_StrConnection);
my_conn.Open();
try
{
my_Adapter = new OleDbDataAdapter(my_StrSelect1, my_StrConnection);
OleDbCommandBuilder my_Builder1 = new OleDbCommandBuilder(my_Adapter);
my_Adapter.Fill(ds, "Table1");
}
catch { };
my_conn.Close();
my_conn.Dispose();
return ds;
}
操作excel数据到数据库中去
//插入数据到数据库中去
public void insertSalaryList()
{
HttpPostedFile postFileName = WageFileUpload.PostedFile;
string fileName = Server.MapPath("~/WebUI/UpLoad/") + "\\Import.xls";
WageFileUpload.PostedFile.SaveAs(fileName);
DataSet ds = Import(fileName);
int satrtRow = 1;
if (ds.Tables.Count>0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
TSalary salary = new TSalary();
satrtRow += 1;
if (satrtRow < 2)
{
continue;
}
salary.Id = UtilityClass.CreateGuid();
salary.SUId = row[2].ToString();
salary.SBase = UtilityClass.ConvertToDecimal(row[3].ToString());
salary.SJiXiao = UtilityClass.ConvertToDecimal(row[4].ToString());
salary.SJinTie = UtilityClass.ConvertToDecimal(row[5].ToString());
salary.SCanTie = UtilityClass.ConvertToDecimal(row[6].ToString());
salary.SYingFa = UtilityClass.ConvertToDecimal(row[7].ToString());
salary.SYangLaoJin = UtilityClass.ConvertToDecimal(row[8].ToString());
salary.SYiLiaoJin = UtilityClass.ConvertToDecimal(row[9].ToString());
salary.SGongJiJin = UtilityClass.ConvertToDecimal(row[10].ToString());
salary.SShiYeJin = UtilityClass.ConvertToDecimal(row[11].ToString());
salary.SElse = UtilityClass.ConvertToDecimal(row[12].ToString());
salary.SYingKou = UtilityClass.ConvertToDecimal(row[13].ToString());
salary.SJiShui = UtilityClass.ConvertToDecimal(row[14].ToString());
salary.SDaiKou = UtilityClass.ConvertToDecimal(row[15].ToString());
salary.SShiFa = UtilityClass.ConvertToDecimal(row[16].ToString());
salary.STime = tbxWageTime.Text.Trim().ToString();
salary.SYear = Convert.ToDateTime(tbxWageTime.Text.ToString()).Year;
salary.SMonth = Convert.ToDateTime(tbxWageTime.Text.ToString()).Month;
session.Save(salary);
session.Flush();
}
。 }
File.Delete(fileName);
}
注意细节：
excel数据需要先上传到服务器端，方能操作数据，操作完成后删除即可。
