1.将Excel档读取入DataSet
#region CheckExcel
private static bool CheckFileExt(string fileNameExt)
{
if (fileNameExt != ".xls" && fileNameExt != ".xlsx")
{
return false;
}
else
{
return true;
}
throw new NotImplementedException();
}
#endregion
#region GetFileName
private static string GetFileName(string path)
{
if (path.Contains("\\"))
{
string[] arr = path.Split('\\');
return arr[arr.Length - 1];
}
else
{
string[] arr = path.Split('/');
return arr[arr.Length - 1];
}
throw new NotImplementedException();
}
#endregion
#region ExecleDs
public static DataSet ExecleDs(string extension, string filenameurl, string table)
{
string strConn = "";
if (extension == ".xls")
{
strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
else if (extension == ".xlsx")
{
strConn = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filenameurl + "; Extended Properties = 'Excel 12.0;HDR = NO; IMEX=1'";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
string tablenames = GetExcelTableNames(filenameurl);
string[] tables = tablenames.Split(';');
if (tables.Length >0 )
{
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + tables[0] + "]", conn);
odda.Fill(ds, table);
}
return ds;
}
#endregion
#region GetExcelTableNames 获得ExcelSheet名称
public static string GetExcelTableNames(string excelFileName)
{
string tableName = null;
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
tableName += dt.Rows[i][2].ToString().Trim() + ";";
}
}
}
return tableName;
}
#endregion
2.将DS内容导入Table。
#region Excel导入db
public static string UpLoadItemFile(string fileNamePath,int begCol,int colcounts, string insertSql)
{
try
{
if (insertSql == null || insertSql.Length <= 0)
{
return "0|InsertSqlError!";
}
string ret_str = "";
int rows = 0;
//获取要保存的文件信息
FileInfo file = new FileInfo(fileNamePath);
//获得文件扩展名
string fileNameExt = file.Extension;
string fileName = GetFileName(fileNamePath);
//存入的文件路径
//验证合法的文件
if (CheckFileExt(fileNameExt))
{
//文件上传成功,需进一步抓取文件内容信息
DataSet ds = ExecleDs(fileNameExt, fileNamePath, fileName); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
return "0|Error|Excel表为空表,无数据!";
}
else
{
int isvalid = 0;
string insert_sql_last = "";
for (int i = 0; i < dr.Length; i++)
{
isvalid = 0;
string insert_data = "";
for (int c = begCol; c < colcounts; c++)
{
string data = "";
if (dr[i][c] == null || dr[i][c].ToString().Length == 0)
{
data = "";
}
else
{
data = dr[i][c].ToString();
isvalid = 1;
}
string strdata = data.Replace("'", "''");
insert_data = insert_data + "'" + strdata + "',";
}
if (isvalid == 0)
{
//return "-1|Error!第" + (i + 1).ToString() + "行数据为空!";
continue;
}
insert_data = insert_data + "'System',";
insert_data = insert_data + "'"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Trim()+"',";
insert_data = insert_data + "'System',";
insert_data = insert_data +"'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Trim() + "',";
ret_str = ret_str + "Insert Item " + insert_data.Substring(0, insert_data.Length - 1) + " ;\n\n";
insert_data = insert_data.Substring(0, insert_data.Length - 1) + ")";
insert_sql_last = insertSql + insert_data;
//进行insert操作
if (Sqlhelper.ExecuteSql(insert_sql_last) <0)
{
return "-1|InsertError!";
}
}//for dr
} //excel rows
//return ret_str;
return "导入成功!<br/>|" + ret_str;
}
else
{
return "0|Errorfile|" + "文件格式非法";
}
}
catch (Exception e)
{
return "0|Errorfile|" + "文件上传失败,错误原因:" + e.Message;
}
}
#endregion
此处方法有多种,可以根据实际需要做。导入即可。