protected void Btn_Sure_Click(object sender, EventArgs e)
{
//FileUpload为c#控件中上传文件的ID
if (FileUpload.FileName!=""){
string path = Server.HtmlEncode(Request.PhysicalApplicationPath);
string excel_path = path + "load_file\\" + System.Guid.NewGuid().ToString() + ".xlsx"; //获取上传的excel文件
FileUpload.SaveAs(excel_path);//save file
DataSet ExcelData = ExcelRead(excel_path);
ArrayList LoadData = new ArrayList(); 定义一个空ArrayList 保存从excel中读取的数据
foreach (DataTable dt in ExcelData.Tables)
{
foreach (DataRow dr in dt.Rows)
{
ArrayList ExcelRow = new ArrayList();
foreach (DataColumn dc in dt.Columns)
{
ExcelRow.Add(dr[dc]);
}
LoadData.Add(ExcelRow);
}
}
// 检查用户是否都存在
for (int i = 0; i < LoadData.Count; i++)
{
ArrayList rowData = (ArrayList)LoadData[i];
//取出集合中的数据
string site_name = rowData[0].ToString();string c_year_product = rowData[1].ToString();
string c_day_product = rowData[2].ToString();
string c_year = rowData[3].ToString();
string c_day = rowData[4].ToString();
//将ArrayList 中的数据添加到指定的数据库表中(省略)
}Response.Redirect("success.aspx");
}
}
{
string fileType = file.Substring(file.LastIndexOf(".") + 1); //获取excel的后缀,由后缀来判断excel的版本
string ConnectionString = "";
if (fileType == "xlsx") 2007版以上
{
ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file);
// ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";" +
// ";Extended ProPerties= 'Excel 12.0;HDR=Yes;'";
}
else { //xls版本
ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file);
}
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [Sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, ConnectionString);
ds = new DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
return ds;
}