从Excel文件读取时的铸造值。 [英] Casting value while reading from Excel file.

问题描述

我想从excel使用oldDB读取它时的值。我的代码如下。

 DataTable dtOleDB = new DataTable（）; 
 string constr =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ xlsFileName +; Extended Properties ='Excel 12.0 Xml; HDR = YES;'; 
 OleDbConnection oledbCon = new OleDbConnection（constr）; 
 string cmdText =SELECT Cast（ExceptionID as varchar），[FeatureOptionID]，ExceptionTypeID，ExceptionFeatureOptionID，; 
 cmdText + =AlternateDescription FROM [Exceptions $]; 
 OleDbDataAdapter oleDA = new OleDbDataAdapter（cmdText，oledbCon）; 
 oleDA.Fill（dtOleDB）; 

如果使用不投，其工作正常。但我想像在sql server中一样以varchar格式转换它如何在这里实现这个功能。



谢谢

解决方案

;
OleDbDataAdapter oleDA = new OleDbDataAdapter（cmdText，oledbCon）;
oleDA.Fill（dtOleDB）;

如果在没有强制转换的情况下使用它可以正常工作。但我想像在sql server中一样以varchar格式转换它如何在这里实现这个功能。



谢谢

试试这个，它可能对你有帮助。

cast-a-data-type-the-odbc-excel-driver [ ^ ]

Microsoft.ACE.OLEDB提供程序是Microsoft Access数据库引擎u ses MS Access SQL语法。



您将需要使用 Access中提供的类型转换功能 [ ^ ]。



您还应检查空值之前尝试转换。



Cast（ExceptionID as varchar）将是：



IIf（IsNull（[ExceptionID]），Null，CStr（[ExceptionID]））as strExceptionID



在此示例中，如果Excel单元格为空，则将返回DBNull。如果你想返回一个空字符串，那么它将是：

IIf（IsNull（[ExceptionID]），''，CStr（[ExceptionID]））as strExceptionID

Hi,
I want to cast value while reading it from excel use oldDB. my code is below.

DataTable dtOleDB = new DataTable();
        string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFileName + ";    Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        OleDbConnection oledbCon = new OleDbConnection(constr);
        string cmdText = "SELECT Cast(ExceptionID as varchar), [FeatureOptionID], ExceptionTypeID, ExceptionFeatureOptionID, ";
        cmdText += "AlternateDescription FROM [Exceptions$]";
        OleDbDataAdapter oleDA = new OleDbDataAdapter(cmdText, oledbCon);
        oleDA.Fill(dtOleDB);

If using without cast its working fine. But I want to cast it in varchar format like do in sql server how can I achieve this functionality here.

thanks

解决方案

"; OleDbDataAdapter oleDA = new OleDbDataAdapter(cmdText, oledbCon); oleDA.Fill(dtOleDB);

If using without cast its working fine. But I want to cast it in varchar format like do in sql server how can I achieve this functionality here.

thanks

Try this, it might help you.
cast-a-data-type-with-the-odbc-excel-driver[^]

The Microsoft.ACE.OLEDB provider being the "Microsoft Access Database Engine" uses MS Access SQL syntax.

You will need to use the type conversion functions available in Access[^].

You should also be checking for null values before attempting the conversion.

Cast(ExceptionID as varchar) would be:

IIf(IsNull([ExceptionID]), Null, CStr([ExceptionID])) As strExceptionID

In this example, a DBNull will be returned if the Excel cell is empty. If you want to return an empty String then it would be:
IIf(IsNull([ExceptionID]), '', CStr([ExceptionID])) As strExceptionID

这篇关于从Excel文件读取时的铸造值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！