I have an Excel workbook that has column C as number stored as text. What is the C# syntax to convert it to number? I know that this VBA will do the job
我有一个Excel工作簿,其中C列作为文本存储。将它转换为数字的C#语法是什么?我知道这个VBA会完成这项工作
Range("C:C").Select
With Selection
Selection.NumberFormat = "0.00%"
.Value = .Value
End With
And I tried this with C# - but it left the numbers stored as text.
我用C#尝试了这个 - 但它将数字存储为文本。
ExcelWorksheet ws3 = pck.Workbook.Worksheets.Add("New Sheet");
ws3.Cells["A1"].LoadFromDataTable(tableforme, true);
ws3.View.FreezePanes(2, 4);
ws3.Cells["C:C"].Style.Numberformat.Format = "#,##0.00";
ws3.Cells["C:C"].Style.Numberformat.Format = "0%";
What must I do in C# to convert a column that is numbers stored as text.
我必须在C#中做什么来转换作为文本存储的数字的列。
3 个解决方案
#1
2
Converting the cell values to the .NET type works for me:
将单元格值转换为.NET类型对我有用:
var values = new List<object[]>()
{
new string[] { "0.001", "1.001", "2.002" },
new string[] { "3.003", "4.004", "5.005" },
new string[] { "6.006", "7.007", "8.008" }
};
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["A1"].LoadFromArrays(values);
foreach (var cell in sheet.Cells["C:C"])
{
cell.Value = Convert.ToDecimal(cell.Value);
}
sheet.Cells["C:C"].Style.Numberformat.Format = "#,##0.00";
File.WriteAllBytes(OUTPUT, package.GetAsByteArray());
}
#2
1
I don't understand what kuujinbo is doing with all that package stuff. Much simpler to do the same thing you did with VB:
我不明白kuujinbo正在做什么包装的东西。使用VB做同样的事情要简单得多:
using Excel = Microsoft.Office.Interop.Excel;
...
Excel.Range rangeOfValues = ws3.Range("C:C");
rangeOfValues.Cells.NumberFormat = "#0";
rangeOfValues.Value = rangeOfValues.Value;
#3
0
Have you tried using 'w3.Columns.NumberFormat' instead of 'Cells[].Style.NumberFormat.Format'
您是否尝试过使用'w3.Columns.NumberFormat'代替'Cells [] .Style.NumberFormat.Format'
Its what I'm using in a couple places in an Excel VSTO app and it works great.
它是我在Excel VSTO应用程序中的几个地方使用的,它工作得很好。
#1
2
Converting the cell values to the .NET type works for me:
将单元格值转换为.NET类型对我有用:
var values = new List<object[]>()
{
new string[] { "0.001", "1.001", "2.002" },
new string[] { "3.003", "4.004", "5.005" },
new string[] { "6.006", "7.007", "8.008" }
};
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["A1"].LoadFromArrays(values);
foreach (var cell in sheet.Cells["C:C"])
{
cell.Value = Convert.ToDecimal(cell.Value);
}
sheet.Cells["C:C"].Style.Numberformat.Format = "#,##0.00";
File.WriteAllBytes(OUTPUT, package.GetAsByteArray());
}
#2
1
I don't understand what kuujinbo is doing with all that package stuff. Much simpler to do the same thing you did with VB:
我不明白kuujinbo正在做什么包装的东西。使用VB做同样的事情要简单得多:
using Excel = Microsoft.Office.Interop.Excel;
...
Excel.Range rangeOfValues = ws3.Range("C:C");
rangeOfValues.Cells.NumberFormat = "#0";
rangeOfValues.Value = rangeOfValues.Value;
#3
0
Have you tried using 'w3.Columns.NumberFormat' instead of 'Cells[].Style.NumberFormat.Format'
您是否尝试过使用'w3.Columns.NumberFormat'代替'Cells [] .Style.NumberFormat.Format'
Its what I'm using in a couple places in an Excel VSTO app and it works great.
它是我在Excel VSTO应用程序中的几个地方使用的,它工作得很好。