//获取Excel中的期初库存信息
/// <summary>
/// Function:获取这个Excel中的所有表
/// Coder:徐臻
/// Time:20008-12-18
/// </summary>
/// <param name="FilePath">文件路径</param>
/// <returns>返回这个Excel表的数据集</returns>
public static DataSet GetDataSet(string FilePath)
{
string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbConnection conn = new OleDbConnection(OledbConnectionString);
ArrayList SheetNameList = new ArrayList();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = "";
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
SheetNameList.Add(SheetName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
DataSet dsExcel = new DataSet();
try
{
string strSql = "";
for (int i = 0; i < SheetNameList.Count; i++)
{
strSql = "select * from [" + (string)SheetNameList[i] + "]";
OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn);
DataTable dtExcel = new DataTable((string)SheetNameList[i]);
oleExcelDataAdapter.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch (Exception ex)
{
throw ex;
}
}
protected void ibtnImport_Click(object sender, ImageClickEventArgs e)
{
//判断上传文件是否符合要求
if (System.IO.Path.GetExtension(FileUpload1.FileName) != ".xls")
{
ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alter('hao')</script>");
return;
}
else
{
string filePath = ExcelLead.ReturnUpperDirectory(AppDomain.CurrentDomain.BaseDirectory) + "//ycqcgl//EquipmentExcelTemp//" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + FileUpload1.FileName;
FileUpload1.SaveAs(filePath);
string fileName = FileUpload1.FileName;
int start = fileName.IndexOf('.');
fileName = fileName.Substring(0, start);
GetDate(filePath,fileName);
}
}
//获取导入数据
public void GetDate(string FilePath,string fileName)
{
DataSet ds = new DataSet();
ds = ExcelLead.GetDataSet(FilePath);
int num = 0;
SqlConnection conn = new SqlConnection(GlobalThings.ConnectionString);
try
{
for (int i = 0; i < ds.Tables.Count; i++)
{
num += ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
string str = "insert into Equipment_Temp (名称,型号,数量,单位,厂家,仓库,器材类型) values('" + ds.Tables[i].Rows[j]["名称"].ToString() + "','" + ds.Tables[i].Rows[j]["规格"].ToString() + "','" + ds.Tables[i].Rows[j]["数量"].ToString() + "','" + ds.Tables[i].Rows[j]["单位"].ToString() + "','" + ds.Tables[i].Rows[j]["厂家"].ToString() + "','" + ds.Tables[i].TableName.ToString() + "','" + fileName + "')";
SqlCommand comm = new SqlCommand(str, conn);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
comm.ExecuteNonQuery();
}
}
lblOrder.Text = "共" + num + "条数据库加载成功";
}
catch (Exception ep)
{
lblOrder.Text = "数据加载失败,引起失败的原因为:" + ep.Message;
}
finally
{
conn.Close();
}
}