protected void btn_ok_Click(object sender, EventArgs e)
{
try
{
string x=txtpath.Text;
DataSet ds = GetConnect(x);
DataTable xDataTable = ds.Tables[0];
for (int i = 0; i < xDataTable.Rows.Count; i++)
{
var voucherscode = (xDataTable.Rows[i ][0] + "").Substring(1).Trim();
var startime = Convert.ToDateTime(xDataTable.Rows[i ][1]);
var endtime = Convert.ToDateTime(xDataTable.Rows[i][2]);
var conversionProduct = xDataTable.Rows[i ][3];
var status = 0;
SQLiteHelper.ExecuteNonQuery("Insert into [Vouchers] (VouchersCode,StarTime,EndTime,ConversionProduct,Status)values('" + voucherscode + "','" + startime + "','" + endtime + "','" + conversionProduct + "','" + status + "')");
}
lblshow.InnerText = "导入成功！";
}
catch(Exception ex)
{
lblshow.InnerText = "导入失败！";
}
}
/// <summary>
/// 导出EXCEL表中的数据到 myDataSet
/// </summary>
public static DataSet GetConnect(string FilePath)
{
DataSet myDataSet = new DataSet();
string strCon = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + FilePath + "; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
if (Path.GetExtension(FilePath).ToLower() == ".xlsx")
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection myConn = new OleDbConnection(strCon);
//条件查询EXCEL表
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
//打开数据链接，得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//创建一个 DataSet对象 //得到自己的DataSet对象
myCommand.Fill(myDataSet, "[Sheet1$]");
//关闭此数据链接
myConn.Close();
return myDataSet;
}