一.最近做项目要用到Excel导入到数据库,借鉴了其他人的代码自己封装了一个方法,大体思路:
在前端上传excel,在服务端保存excel,将excel读到datetable里,把datetable里的数据循环用Mysqly语句拼接起来,执行Mysql语句
二.代码如下
///上传excel文件接口
[HttpPost]
public ActionResult DataImportAll(HttpPostedFileBase filebase)
{
string tablename = "user"; //导入数据库的表名
List<string> l = new List<string>(); //数据库列名
l.Add("id");
l.Add("name");
l.Add("age");
l.Add("sex");
l.Add("phone"); //把插入表的列名放到一个list里
_upload(filebase,tablename,l);
ViewBag.error = "上传成功";
return View();
}
public string _upload(HttpPostedFileBase filebase,string name,List<string> tablelist)
{
string error = "";
HttpPostedFileBase file = Request.Files["FileUpload"];//获取上传的文件
string FileName;
string savePath;
if (file == null || file.ContentLength <= 0)
{
error = "文件不能为空";
return error;
}
else
{
string filename = Path.GetFileName(file.FileName);//获取上传文件名
int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
error = "文件类型不对,只能导入xls和xlsx格式的文件";
return error;
}
if (filesize >= Maxsize)
{
error = "上传文件超过10M,不能上传";
return error;
}
string path = AppDomain.CurrentDomain.BaseDirectory + "/uploads";
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
}
string result = string.Empty;
string strConn;
strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
//OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
try
{
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select*from[" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(myDataSet, "ExcelInfo");
}
catch (Exception ex)
{
error = ex.Message;
return error;
}
// System.Data.Deleted();
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
DatetoObj(table,name,tablelist);
error = "上传成功";
System.Threading.Thread.Sleep(2000);
return error;
}
//把数据存到list里
public static bool DatetoObj(DataTable table, string name, List<string> tablelist)
{
for (int i = 0; i < table.Rows.Count; i++)
{
List<string> l = new List<string>();
for (int k = 0; k < table.Columns.Count; k++)
{
l.Add(table.Rows[i][k].ToString());
}
AddData1(l, table.Columns.Count,name,tablelist);
}
return true;
}
public static bool AddData1(List<string> model,int count, string name, List<string> tablelist)
{
//拼接MySQL语句
StringBuilder strSQL = new StringBuilder();
strSQL.Append("Insert into ");
strSQL.Append(name);
strSQL.Append("(");
for (int j = 0; j < tablelist.Count-1; j++)
{
strSQL.Append(tablelist[j]); strSQL.Append(",");
}
strSQL.Append(tablelist[tablelist.Count - 1]);
strSQL.Append(") Values('");
//user (id");
//,name,age,sex,phone) Values('");
for (int i = 0; i < count - 1; i++) {
strSQL.Append(model[i]); strSQL.Append("','");
}
strSQL.Append(model[count-1]); strSQL.Append("'");
strSQL.Append(")");
try
{
if (ExecuteNonQuery(strSQL.ToString()) > 0)
{
return true;
}
return false;
}
catch
{
//GLog.LogError("RM_CarRESInfo");
return false;
}
finally
{
}
}
//执行MySQL语句
public static int ExecuteNonQuery(string cmdText)
{
string connstr = "server=;database=;uid=root;pwd=";
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
int res;
try
{
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}