我把excel当成一个数据库来查询,但是遇到一个问题,当一个字段(第一个字段mpg)里有小数(一位小数)而且还有整数的时候,读出的结果里有整数的那个记录的那个字段是空值,不知道为什么,代码我看上去是很标准的读取数据的代码,百思不得其解,望大虾们请教。
public DataSet GetDataSetFromExcel(string FileName , string strSQL )
{
try
{
//Skapa connectionstring till excel-filen
StringBuilder connectionStringBuilder = new StringBuilder();
connectionStringBuilder.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
connectionStringBuilder.Append("Data Source=");
connectionStringBuilder.Append(FileName);
connectionStringBuilder.Append(";Extended Properties=");
connectionStringBuilder.Append(Convert.ToChar(34));
connectionStringBuilder.Append("Excel 8.0;HDR=Yes;IMEX=2");
connectionStringBuilder.Append(Convert.ToChar(34)); String connectionString = connectionStringBuilder.ToString(); //Anslut till filen
OleDbConnection connection =new OleDbConnection(connectionString);
connection.Open(); //Skapa SQL-kommando
OleDbCommand command = new OleDbCommand(strSQL, connection);//"SELECT * FROM [" + WorksheetName + "$]", connection); //Skapa data-adapter
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command; DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet); connection.Close(); dataAdapter.Dispose();
command.Dispose();
connection.Dispose(); //Returnera dataset:et
return dataSet;
}
catch(Exception e)
{
System.Windows.MessageBox.Show(e.ToString(), "error");
return null;
}
}mpg cylinders horsepower accel year weight origin make model price
43.1 4 48 21.5 78 1985 GERMANY Volkswagen Rabbit Dl 2400
36.1 4 66 14.4 78 1800 USA Ford Fiesta 1900
32.8 4 52 19.4 78 1985 JAPAN Mazda GLC Deluxe 2200
39.4 4 70 18.6 78 2070 JAPAN Datsun B210 GX 2725
36.1 4 60 16.4 78 1800 JAPAN Honda Civic CVCC 2250
19.9 8 110 15.5 78 3365 USA Oldsmobile Cutlass 3300
19.4 8 140 13.2 78 3735 USA Dodge Diplomat 3125
20.2 8 139 12.8 78 3570 USA Mercury Monarch 2850
19.2 6 105 19.2 78 3535 USA Pontiac Phoenix 2800
20.5 6 95 18.2 78 3155 USA Chevrolet Malibu 3275
20.2 6 85 15.8 78 2965 USA Ford Fairmont A 2375
25.1 4 88 15.4 78 2720 USA Ford Fairmont M 2275
20.5 6 100 17.2 78 3430 USA Plymouth Volare 2700
19.4 6 90 17.2 78 3210 USA AMC Concord 2300
20.6 6 105 15.8 78 3380 USA Buick Century 3300
20.8 6 85 16.7 78 3070 USA Mercury Zephyr 2425
18.6 6 110 18.7 78 3620 USA Dodge Aspen 2700
18.1 6 120 15.1 78 3410 USA AMC Concord D1 2425
19.2 8 145 13.2 78 3425 USA Chevrolet MonteCarlo 3900
17.7 6 165 13.4 78 3445 USA Buick RegalTurbo 4400
18.1 8 139 11.2 78 3205 USA Ford Futura 2525
17.5 8 140 13.7 78 4080 USA Dodge Magnum XE 3000
30 4 68 16.5 78 2155 USA Chevrolet Chevette 2100
public DataSet GetDataSetFromExcel(string FileName , string strSQL )
{
try
{
//Skapa connectionstring till excel-filen
StringBuilder connectionStringBuilder = new StringBuilder();
connectionStringBuilder.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
connectionStringBuilder.Append("Data Source=");
connectionStringBuilder.Append(FileName);
connectionStringBuilder.Append(";Extended Properties=");
connectionStringBuilder.Append(Convert.ToChar(34));
connectionStringBuilder.Append("Excel 8.0;HDR=Yes;IMEX=2");
connectionStringBuilder.Append(Convert.ToChar(34)); String connectionString = connectionStringBuilder.ToString(); //Anslut till filen
OleDbConnection connection =new OleDbConnection(connectionString);
connection.Open(); //Skapa SQL-kommando
OleDbCommand command = new OleDbCommand(strSQL, connection);//"SELECT * FROM [" + WorksheetName + "$]", connection); //Skapa data-adapter
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command; DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet); connection.Close(); dataAdapter.Dispose();
command.Dispose();
connection.Dispose(); //Returnera dataset:et
return dataSet;
}
catch(Exception e)
{
System.Windows.MessageBox.Show(e.ToString(), "error");
return null;
}
}mpg cylinders horsepower accel year weight origin make model price
43.1 4 48 21.5 78 1985 GERMANY Volkswagen Rabbit Dl 2400
36.1 4 66 14.4 78 1800 USA Ford Fiesta 1900
32.8 4 52 19.4 78 1985 JAPAN Mazda GLC Deluxe 2200
39.4 4 70 18.6 78 2070 JAPAN Datsun B210 GX 2725
36.1 4 60 16.4 78 1800 JAPAN Honda Civic CVCC 2250
19.9 8 110 15.5 78 3365 USA Oldsmobile Cutlass 3300
19.4 8 140 13.2 78 3735 USA Dodge Diplomat 3125
20.2 8 139 12.8 78 3570 USA Mercury Monarch 2850
19.2 6 105 19.2 78 3535 USA Pontiac Phoenix 2800
20.5 6 95 18.2 78 3155 USA Chevrolet Malibu 3275
20.2 6 85 15.8 78 2965 USA Ford Fairmont A 2375
25.1 4 88 15.4 78 2720 USA Ford Fairmont M 2275
20.5 6 100 17.2 78 3430 USA Plymouth Volare 2700
19.4 6 90 17.2 78 3210 USA AMC Concord 2300
20.6 6 105 15.8 78 3380 USA Buick Century 3300
20.8 6 85 16.7 78 3070 USA Mercury Zephyr 2425
18.6 6 110 18.7 78 3620 USA Dodge Aspen 2700
18.1 6 120 15.1 78 3410 USA AMC Concord D1 2425
19.2 8 145 13.2 78 3425 USA Chevrolet MonteCarlo 3900
17.7 6 165 13.4 78 3445 USA Buick RegalTurbo 4400
18.1 8 139 11.2 78 3205 USA Ford Futura 2525
17.5 8 140 13.7 78 4080 USA Dodge Magnum XE 3000
30 4 68 16.5 78 2155 USA Chevrolet Chevette 2100
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货