我正在尝试将数据表传递给类，并收到以下消息：输入字符串格式不正确

Question

我正在使用一个excel文件读取字段，并将其传递给一个类，以便在传递dt时能够输入DB。作为Enumerable，它在"；Linea"；领域这个单元格有时带有$符号。我认为这就是产生错误的原因，所以我试图替换字符，然后将其转换为int，因为它是一个金额字段。

using (var streamExcel = System.IO.File.Create(combineFilePath))
{
await file.CopyToAsync(streamExcel);
}
using (var excelWorkbook = new XLWorkbook(combineFilePath))
{
IXLWorksheet workSheet = excelWorkbook.Worksheet(2);
workSheet.Clear(XLClearOptions.AllFormats);

DataTable dt = new DataTable();
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//for row number check
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(1, 50))
{
if (cell.Address.ColumnNumber == 11)
{
workSheet.Cell(11, 11).Style.NumberFormat.Format = "#,##0";
cell.SetDataType(XLDataType.Number);
}
dt.Rows[dt.Rows.Count - 1][i] = cell.CachedValue.ToString();
i++;
}
}
}
try
{

var companys = dt.AsEnumerable().Select(row => new Company
{

Name = row.Field<string>("Nombre"),
Rut = row.Field<string>("Rut"),
Address = row.Field<string>("Dirección"),
AddressNumber = row.Field<string>(@"Nº"),
Location = row.Field<string>("Comuna"),
Region = row.Field<string>("Región"),
Giro = row.Field<string>("Giro Cliente"),
Linea = Convert.ToInt32(row.Field<string>("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", "")),
Observations = row.Field<string>("Observaciones Comité"),

}).ToList();
UserId = _companyService.AddList(companys);
}
catch (Exception e)
{
}

Answer 1

要直观地显示它的故障位置，可以执行以下操作：

try{

var companysB = dt.AsEnumerable().Select(row => new 
{
Name = row.Field<string>("Nombre"),
LineaRaw = row.Field<string>("Monto línea Aprobada"),
LineaProcessed = row.Field<string>("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", ""),
})
.ToList();
}

在"companysB"上设置断点，并在填充后进行检查。LineaRaw/LineaProcessed中的一个或多个将不是数字。

Answer 2

错误是当；Monto línea Aprobada"；单元格有一个空白值，我这样做了：

var n = 0;
string cellEmty = "";
foreach (DataRow rowEmpty in dt.Rows)
{
cellEmty = rowEmpty["Monto línea Aprobada"].ToString();
if (string.IsNullOrEmpty(cellEmty))
{
cellEmty = "0";
dt.Rows[n].SetField("Monto línea Aprobada", cellEmty);
}

n++;
}