C# Excel 导入Excel 留档

最新推荐文章于 2024-10-21 15:06:42 发布

原创最新推荐文章于 2024-10-21 15:06:42 发布 · 660 阅读

0 ·

CC 4.0 BY-SA版权

技术学习及存档专栏收录该内容

20 篇文章

订阅专栏

本文介绍了如何将Excel文件中的数据导入到数据库中，并详细解释了读取Excel文件、检查文件扩展名、获取文件名、解析Excel表名、执行SQL查询以填充数据集等关键步骤。同时，还提供了一个实例来展示如何将数据集内容导入到特定的数据库表中，包括文件上传、验证文件合法性、读取数据、验证数据有效性并进行插入操作。

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