从excel导入 - 需要更改不同的日期值格式 [英] Import from excel - Need to change different Date Value Formats

问题描述

当我使用ACE.OLEDB从Excel导入数据时，每列的日期格式不同



excel中的我的日期列为16.03.2015 或3/16/2015 12:00:00 AM我需要一个转换器来获取日期值并将其格式化为MM / dd / yyyy格式。



我使用以下代码读取数据

 DataSet ds =  new  DataSet（）; 
  string  myConnStr =  ; 
 
  if （ds！=  null ）
 {
 myConnStr =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + FilePath +  ; +  扩展属性= \Excel 12.0; HDR = YES \; 
} 
  else  
 {
 myConnStr =   Provider = Microsoft.Jet.OLEDB.4.0; Data Source = + FilePath +  ;扩展属性= \Excel 8.0; HDR =是; IMEX = 1 \; 
} 
 
 OleDbConnection myConn =  new  OleDbConnection（myConnStr）; 
 尝试 
 {
 OleDbCommand cmd =  new  OleDbCommand（  select [from [Sheet1 $]，myConn）; 
 OleDbDataAdapter adapter =  new  OleDbDataAdapter（）; 
 adapter.SelectCommand = cmd; 
 myConn.Open（）; 
 adapter.Fill（ds）; 
} 
  catch  
 {} 
 最后 
 {
 myConn.Close（）; 
} 
 

我无法循环数据并进行转换。

解决方案

，myConn）;
OleDbDataAdapter adapter = new OleDbDataAdapter（ ）;
adapter.SelectCommand = cmd;
myConn.Open（）;
adapter.Fill（ds）;
}
catch
{}
finally
{
myConn.Close（）;
}

我无法循环数据并进行转换。

首先，不要使用 IMEX = 1 ，除非......阅读这个 [ ^ ]找出来什么 IMEX 切换意味着。



如果你知道c的集合olumns，你可以在 SELECT 语句中将日期转换为自定义日期格式：

  SELECT 格式（[ColumnName]，  dd / MM / yyyy）作为 MyDate 
  FROM  [Sheet1 

MS Access：格式功能（带日期） [ ^ ]

When I import data from Excel using ACE.OLEDB, the date format from is different for each column

My date column in excel comes as "16.03.2015" or "3/16/2015 12:00:00 AM" I need a converter to get the date Value and format it to "MM/dd/yyyy" format.

I read the data using the following code

DataSet ds = new DataSet();
      string myConnStr = "";

      if (ds != null)
      {
       myConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  FilePath     + "; " + "Extended Properties=\"Excel 12.0;HDR=YES\"";
      }
      else
      {
      myConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath +    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
      }

      OleDbConnection myConn = new OleDbConnection(myConnStr);
      try
      {
          OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]  ",myConn);
          OleDbDataAdapter adapter = new OleDbDataAdapter();
          adapter.SelectCommand = cmd;
          myConn.Open();
          adapter.Fill(ds);
       }
      catch
      { }
      finally
      {
          myConn.Close();
      }

I can't loop the data and convert.

解决方案

",myConn); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; myConn.Open(); adapter.Fill(ds); } catch { } finally { myConn.Close(); }

I can't loop the data and convert.

First of all, do not use IMEX=1 unless... Read this[^] to find out what IMEX switch means.

If you know the collection of columns, you're able to convert date to custom date format in SELECT statement:

SELECT Format([ColumnName], "dd/MM/yyyy") As MyDate
FROM [Sheet1

MS Access: Format Function (with Dates)[^]

这篇关于从excel导入 - 需要更改不同的日期值格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！