如何使用其字段名称获取excel表格单元格的值 [英] How to get the value of excel sheet cell using their Field Name
本文介绍了如何使用其字段名称获取excel表格单元格的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想根据我在工作表中指定的标题从Excel工作表中提取数据.我正在使用ClosedXML并使用Number单元格获取数据.以下是我的代码.
I want to Extract data from Excel sheet according to header i have specifies in the Sheet. I am using ClosedXML and getting data using cell Number.Below is my Code.
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工作表中的字段名称提取数据的代码,例如名称,姓氏,城市...等.
Here is the code now i want to extract data According to field name in excel sheet as Name,lastname,city ....etc.How to do that.
推荐答案
您可以使用OLEDB来从excel文件中提取数据并将其存储在Datatable中,然后执行所需的任何操作,
You can Do It with OLEDB to Extract the data from excel file and store it in a Datatable and do whatever you want ,
这是一个函数,它将以DataTable格式返回您的excel文件
Here is a function which will return your excel file in DataTable format
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;
}
这篇关于如何使用其字段名称获取excel表格单元格的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文