英文:
C# with ClosedXML, First row is Treated as Header row
问题
我试图打印A1单元格，应该打印'Tomer'，但出于某种原因它打印'Noam'。我是否错误地使用了ClosedXML？还是有其他方法可以将所有数据获取到DataTable中，以便我可以使用正确的单元格地址获取值？
test.xlsx 结构：
我构建了一个简单的C#控制台应用程序来演示我的观点。
程序：
using c__closedxml;
using ClosedXML.Excel;
using System.Data;
string path = Path.Combine(AppContext.BaseDirectory, "test.xlsx");
using (var wb = new XLWorkbook(path)) {
var worksheet = wb.Worksheets.First();
DataTable dt = worksheet.RangeUsed().AsTable().AsNativeDataTable();
Console.WriteLine(Service.getValueByCell(dt, "A1"));
}
用于记录单元格内容的自定义函数：
public static string getValueByCell(DataTable dt, string cell) {
string refLetter = "";
string refNum = "";
for (int i = 0; i < cell.Length; i++) {
char curChar = cell[i];
if (ValidateNumber(curChar)) {
refNum += curChar;
} else {
refLetter += curChar;
}
}
int col = GetNumberFromExcelColumn(refLetter);
int row = Convert.ToInt32(refNum);
Console.WriteLine($"在第 {col} 列按行 {row} 搜索");
Console.WriteLine(JsonConvert.SerializeObject(dt.Rows[0].Table));
return dt.Rows[row - 1][col - 1].ToString();
}
DataTable中的表确认第一行被视为标题：
Console.WriteLine(JsonConvert.SerializeObject(dt.Rows[0].Table)); // 打印如下内容:
[
{
"Tomer": "Noam",
"Cohen": "Aharoni",
"31": 33.0
}
]
感谢任何帮助！
1: https://i.stack.imgur.com/gOGgI.png
英文:
I'm trying to print A1 cell, it should print 'Tomer' but for some reason it prints 'Noam'.
Am I using the ClosedXML wrong? Or is there a different way in which I can get all data to DataTable as I can get the value with the correct cell address?
test.xlsx structure:
I've built a simple minimal C# console app demonstrating my point.
Program:
using c__closedxml;
using ClosedXML.Excel;
using System.Data;
string path = Path.Combine(AppContext.BaseDirectory, "test.xlsx");
using (var wb = new XLWorkbook(path)) {
var worksheet = wb.Worksheets.First();
DataTable dt = worksheet.RangeUsed().AsTable().AsNativeDataTable();
Console.WriteLine(Service.getValueByCell(dt, "A1"));
}
Custom function to log the cell content:
public static string getValueByCell(DataTable dt, string cell) {
string refLetter = "";
string refNum = "";
for (int i = 0; i < cell.Length; i++) {
char curChar = cell[i];
if (ValidateNumber(curChar)) {
refNum += curChar;
} else {
refLetter += curChar;
}
}
int col = GetNumberFromExcelColumn(refLetter);
int row = Convert.ToInt32(refNum);
Console.WriteLine($"Searching for column {col} by row {row}");
Console.WriteLine(JsonConvert.SerializeObject(dt.Rows[0].Table));
return dt.Rows[row - 1][col - 1].ToString();
}
The table inside DataTable confirms that the first row is treated as a header:
Console.WriteLine(JsonConvert.SerializeObject(dt.Rows[0].Table)); // prints the following:
[
{
"Tomer": "Noam",
"Cohen": "Aharoni",
"31": 33.0
}
]
Any help is appreciated!
答案1
得分: 1
AsTable() 使用您范围的第一行作为列标题创建一个Excel表格。这类似于在Excel中按下Ctrl+T。AsNativeDataTable() 将其转换为一个 System.Data.DataTable 实例。其余的行填充了数据。
要么首先插入实际的标题，要么使用不同的方法，避免使用这2个转换方法来引用数据。
英文:
AsTable() creates an Excel table using the first row of your range as column headers. This is like pressing Ctrl+T in Excel. AsNativeDataTable() converts that to a System.Data.DataTable instance. The rest of the rows populate the data.
Either first insert actual headers or use a different approach, avoiding the 2 conversion methods, to reference the data.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论