Shulou(Shulou.com)06/02 Report--

The main content of this article is to explain "how to use C # to solve the problem of inconsistent data types of Oledb connection Excel". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use C # to solve the problem of inconsistent data types in Oledb connections and Excel.

When using Microsoft.Jet.OLEDB.4.0 to connect to Excel and read data, it is very efficient to use traditional COM to read data. However, compared with the traditional COM operation Excel, there is a problem of data type conversion.

Because when using OLEDB connection Excel to read data, you need to determine the type of data. The connection string is used by default:

View source

Print?

1. String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + "; Extended Properties='Excel 8.0"

When connecting to Excel using the above connection string, you may encounter problems with inconsistent data types. The so-called data type inconsistency means that there may be multiple data types in the same column, such as floating-point numbers, strings, dates, and so on. When such a situation occurs, the read data will be empty and may even report an error, such as "illegal date format" and other exceptions. When this kind of problem occurs, we all think of reading all the data as character data, but what data type to read is not controlled by us, it is controlled by OLEDB, at least for the time being, I haven't found a way to control the output data type. Because I also tried to use the convert,cast function to type the output column, but when oledb connects to Excel, the SQL I use does not support these functions. Therefore, the problem can only be solved from other angles. I have also searched for many solutions on the Internet, and the most comprehensive solution is: http://www.douban.com/note/18510346/. The following is a list of online comparisons of ways to solve the problem:

A more convenient method is provided here, but only if the row is a field name or the data type of the row is character. In this way, everyone will understand. First, modify the connection string to:

View source print? / / 2003 (Microsoft.Jet.Oledb.4.0) string strConn = string.Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0}; Extended Properties='Excel 8.0 × Extended Properties='Excel 8.0);", excelFilePath); / / 2010 (Microsoft.ACE.OLEDB.12.0) string strConn = string.Format ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0}; Extended Properties='Excel 8.0}; HDR 8.0)

Here, HDR is set to NO, because I use * * rows as data reads, and IMEX=1 means to determine the data type of the column based on the first 8 rows. If there is character data, then force the mixed data to be converted to text. Here you can see why it is necessary to ensure the character type of * behavior. If you can force the data type of a column to be character, you are not afraid of any type of data that appears in the column. All you need to do is to reset the field name and delete * * records after getting the data. The code is as follows:

View source print? 01. DataTable dt = new DataTable (); 02. 03. Using (OleDbCommand cmd = new OleDbCommand ()) {04. Cmd.Connection = conn; 05. Cmd.CommandType = CommandType.Text; 06. Cmd.CommandTimeout = 6; 07. Cmd.CommandText = string.Format ("select * from [{0} $]", sheetName); 08. 09. OleDbDataAdapter adapter = new OleDbDataAdapter (cmd); 10. Adapter.Fill (dt); 11.} 12. 13. If (dt.Rows.Count > 0) {14. DataRow dr = dt.Rows [0]; 15. 16. For (int col = 0; col < dt.Columns.Count; col++) {17. Dt.ColumnName = drcol] .ColumnName = drcol] .ToString (); 18.} 19. 20. Dt.Rows [0] .Delete () 21. Dt.AcceptChanges (); 22. } at this point, I believe you have a deeper understanding of "how to use C # to solve the problem of inconsistent Excel data types in Oledb connections". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.