使用Excel导入时要先安装管理NuGet程序包-NPOI
public BaseResult ExcelImport()
{
HttpFileCollection filelist = HttpContext.Current.Request.Files;
if (filelist != null && filelist.Count > 0)
{
int t = 0;
string fileExt = string.Empty;
string FileName = string.Empty;
string FilePath = string.Empty;
for (int j = 0; j < filelist.Count; j++)
{
HttpPostedFile file = filelist[j];
FileName = file.FileName;
if (!FileName.Trim().ToLower().EndsWith(".xlsx") && !FileName.Trim().ToLower().EndsWith(".xls"))
{
return new BaseResult { ResCode = ResCode.Fail, ResMsg = "只能上传Excel文件" };
}
string strFileName = file.FileName.ToString();
fileExt = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower();//文件后缀名
if (FileName != "")
{
//先上传文件到服务器固定路径
string dateStr = DateTime.Now.ToString("yyyyMMddHHmmssms");
string filename = Path.GetFileNameWithoutExtension(FileName);
string oldfilekz = Path.GetExtension(FileName);
//string SaveFilePath = HttpContext.Current.Server.MapPath("~/UploadFile/");//路径
FilePath = HostingEnvironment.MapPath("~/UploadExcel/");
DirectoryInfo di = new DirectoryInfo(FilePath);
if (!di.Exists) { di.Create(); }
file.SaveAs(FilePath + filename + dateStr + oldfilekz);
strFileName = FilePath + filename + dateStr + oldfilekz;
}
string strCon = "";
if (FileName.Trim().ToLower().EndsWith(".xls"))
{
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
}
else
{
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileName + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
}
//以Excel做为数据库导入数据
//string strCon = "Provider=Microsoft.Ace.OleDb.12.0;"
//+ "data source=" + strFileName + ";"
//+ "Extended Properties='Excel 12.0;HDR=NO; IMEX=1'";
ArrayList list = new ArrayList();
OleDbConnection con = new OleDbConnection(strCon);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
DataSet ds = new DataSet();
da.Fill(ds, "excelTab");
DataTable dt = new DataTable();
dt = GetExcelTable(ds.Tables["excelTab"]);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取订单编号
string ordergoodsid = dt.Rows[i]["订单编号"].ToString();
//获取快递公司
string expresscompany = dt.Rows[i]["快递公司"].ToString();
//获取快递单号
string expressnum = dt.Rows[i]["快递单号"].ToString();
StringBuilder Sql = new StringBuilder();
//修改语句-发货
Sql.Append("update ordergoods set expresscompany = '" + expresscompany + "',expressnum='" + expressnum + "',delivergoods='1',orderstate='2' where ordergoodsid = '" + ordergoodsid + "'");
//Sql.Append("insert into sf_material values('" + mid + "','" + resourceid + "','" + advtypeid + "','" + dt.Rows[i]["图片支持格式"].ToString() + "','" + dt.Rows[i]["图片限制大小"].ToString() + "','" + dt.Rows[i]["视频支持格式"].ToString() + "','" + dt.Rows[i]["视频限制大小"].ToString() + "','" + dt.Rows[i]["广告时长"].ToString() + "','" + dt.Rows[i]["分辨率"].ToString() + "','" + dt.Rows[i]["其他说明"].ToString() + "','" + username + "','" + insertTime + "','" + status + "','" + dt.Rows[i]["素材规格名称"].ToString() + "')");
list.Add(Sql);
}
if (list.Count != 0)
{
foreach (var item in list)
{
string sql = item.ToString();
ExcuteSQL(item.ToString());
t += 1;
}
}
}
else
{
return new BaseResult { ResCode = ResCode.Fail, ResMsg = "导入的文件格式有误" };
}
}
if (t > 0)
{
return new BaseResult { ResCode = ResCode.Ok, ResMsg = "文件导入成功" };
}
else
{
return new BaseResult { ResCode = ResCode.Fail, ResMsg = "文件导入失败" };
}
}
else
{
return new BaseResult { ResCode = ResCode.Fail, ResMsg = "请选择文件" };
}
}
/// <summary>
/// 去除不必要的标题
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public DataTable GetExcelTable(DataTable table)
{
foreach (DataRow row in table.Rows)
{
if (!(row[0].ToString()).Equals("素材规格名称"))
{
row.Delete();
}
else
{
break;
}
}
table.AcceptChanges();
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Columns.Count; i++)
{
table.Columns[i].ColumnName = table.Rows[0][i].ToString();
}
table.Rows[0].Delete();
table.AcceptChanges();
}
//table = table.GetChanges(DataRowState.Unchanged);
return table;
}
//执行sql语句
public void ExcuteSQL(string sql)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
报错：“未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序”
解决方法：iis应用程序池-高级设置-启用32位应用程序-true