c# - 从 Excel 中读取列，重新格式化单元格

Question

我目前正在尝试从 excel 电子表格中读取单元格，它似乎在我不希望它重新格式化单元格时重新格式化。我希望它作为计划文本通过。我已经阅读了几个针对此问题的解决方案并实现了它们，但我仍然遇到同样的问题。

读者将数字中的日期和数字转换为日期。

例子:

Friday, January 29, 2016 comes out to be : 42398

and

40.00 comes out to be : 2/9/1900 12:00:00 AM

代码:

string stringconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + files[0] + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
try {
    OleDbConnection conn = new OleDbConnection(stringconn);
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [CUAnswers$]", conn);
    DataTable dt = new DataTable();
    try {
        printdt(dt);

我试过了

IMEX=0;
HDR=NO;
TypeGuessRows=1;

我是这样打印出来的

public void printdt(DataTable dt) {
        int counter1 = 0;
        int counter2 = 0;
        string temp = "";
        foreach (DataRow dataRow in dt.Rows) {
            foreach (var item in dataRow.ItemArray) {
                temp += " ["+counter1+"]["+counter2+"]"+ item +", ";
                counter2++;
            }
            counter1++;
            logger.Debug(temp);
            temp = "";
            counter2 = 0;
        }
    }

Answer 1

我遇到了类似的问题，只不过是使用 Interop 读取 Excel 电子表格。这对我有用:

var value = (range.Cells[rowCnt, columnCnt] as Range).Value2;
string str = value as string;
DateTime dt;
if (DateTime.TryParse((value ?? "").ToString(), out dt))
{
    // Use the cell value as a datetime
}

编辑以添加新想法

我打算建议将电子表格保存为逗号分隔值。然后 Excel 将单元格转换为文本。在 C# 中解析 CSV 很容易。

这让我想到了如何以编程方式进行转换，这在 Convert xls to csv programmatically 中有所介绍。 .也许接受的答案中的代码就是您要查找的内容:

string ExcelFilename = "c:\\ExcelFile.xls";
DataTable worksheets;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + ExcelFilename + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    worksheets = connection.GetSchema("Tables");
    foreach (DataRow row in worksheets.Rows)
    {
        // For Sheets: 0=Table_Catalog,1=Table_Schema,2=Table_Name,3=Table_Type
        // For Columns: 0=Table_Name, 1=Column_Name, 2=Ordinal_Position
        string SheetName = (string)row[2];
        OleDbCommand command = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", connection);
        OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
        oleAdapter.SelectCommand = command;
        DataTable dt = new DataTable();
        oleAdapter.FillSchema(dt, SchemaType.Source);
        oleAdapter.Fill(dt);
        for (int r = 0; r < dt.Rows.Count; r++)
        {
           string type1 = dr[1].GetType().ToString();
           string type2 = dr[2].GetType().ToString();
           string type3 = dr[3].GetType().ToString();
           string type4 = dr[4].GetType().ToString();
           string type5 = dr[5].GetType().ToString();
           string type6 = dr[6].GetType().ToString();
           string type7 = dr[7].GetType().ToString();
        }
    }
}