如何获得excel表格单元格的值使用他们的字段名
本文关键字:他们的 字段 何获得 excel 表格 单元格 | 更新日期: 2023-09-27 18:18:03
我想根据标题从Excel工作表中提取数据,我在工作表中指定。我使用ClosedXML并使用单元格号码获取数据。下面是我的代码。
FileInfo fi = new FileInfo(path1);
//Open uploaded workbook
var workBook = new XLWorkbook(fi.FullName);
//Get the first sheet of workbook
var worksheet = workBook.Worksheet(1);
var firstRowUsed = worksheet.FirstRowUsed();
var categoryRow = firstRowUsed.RowUsed();
/Get the column names from first row of excel
Dictionary<int, string> keyValues = new Dictionary<int,string>();
for (int cell = 1; cell <= categoryRow.CellCount(); cell++)
{
keyValues.Add(cell, categoryRow.Cell(cell).GetString());
}
//Get the next row
categoryRow = categoryRow.RowBelow();
while (!categoryRow.Cell(coCategoryId).IsEmpty())
{
int count = 1;
var pc = new ExpandoObject();
while (count <= categoryRow.CellCount())
{
// let this go through-if the data is bad, it will be rejected by SQL
var data = categoryRow.Cell(count).Value;
((IDictionary<string, object>)pc).Add(keyValues[count], data);
//((IDictionary<string, object>)pc).Add(keyValues[count], data);
fName = categoryRow.Cell(1).Value.ToString();
lName = categoryRow.Cell(2).Value.ToString();
userGender = categoryRow.Cell(3).Value.ToString();
roleTitle = categoryRow.Cell(4).Value.ToString();
mobileNumber = categoryRow.Cell(5).Value.ToString();
CurrentCity = categoryRow.Cell(6).Value.ToString();
country = categoryRow.Cell(7).Value.ToString();
birthDate = DateTime.UtcNow.ToLocalTime();
这里是代码现在我想提取数据根据字段名在excel表中的名称,姓氏,城市....等。怎么做呢
您可以使用OLEDB从excel文件中提取数据并将其存储在数据表中,并做任何您想做的,
这是一个函数,将返回您的excel文件在DataTable格式
private System.Data.DataTable call()
{
System.Data.DataTable dt = new System.Data.DataTable();
string FilePath = Server.MapPath("~/Reports/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(FilePath);
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string conStr = "";
switch (extension)
{
case ".xls": //Excel 97-03
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
case ".xlsx": //Excel 07
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
}
conStr = String.Format(conStr, FilePath, "YES");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
System.Data.DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT [ColumnName1] , [ColumnName2] From [report$A10:U16384] where [ColumnName1] is not null";//any Condition
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
return dt;
}