将EXCEL数据读出,该功能的实现代码网络上已重复出现多次,我贴出此段代码是因为遇到一个问题:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
{
ArrayList list = new ArrayList();
string connectString = " Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= " + filePath + " ; Extended properties=Excel 8.0; " ;
OleDbConnection con = new OleDbConnection(connectString);
con.Open();
DataTable sheetNames = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object [] { null , null , null , " TABLE " });
con.Close();
foreach (DataRow dr in sheetNames.Rows )
{
list.Add(dr[ 2 ]);
}
return list;
}
private List < Map > ExportToDataBase( string filePath, string sheetName)
{
List < Map > maps = new List < Map > ();
string connectString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filePath + " ;Extended properties=Excel 8.0 " ;
OleDbConnection con = new OleDbConnection(connectString);
OleDbCommand cmd = new OleDbCommand( " select * from [ " + sheetName + " ] " , con);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
con.Close();
}
我Excel表中只有一个表格County,查询却返回两个表格名称:Country 和 Country$Print_Titles, 纳闷中,未找出问题
在利用SQL语句:select * from [" + sheetName + "]"; 读取表格数据时从Country中可正常读取,而Country$Print_Titles试图读取时抛出异常。
将读出的数据更新至MYSQL,使用存储过程时遇到的问题及解决方法总结如下:
1.出错信息:Illegal mix of collations for operation ‘=’ 参考自:http://www.blogguy.cn/show-654-1.html
简单的解决方法例示:
select country_name from country where country_ID = (countryID) ;
2.存储过程中参数在传入中文字符时出错,可通过 添加编码信息解决,如下:
map_chineseName varchar(200)
3.修改Mysql默认编码格式:
status-->查看 Server Characterset,Db characterset,Client characterset,Conn.characterset 修改可通过安装路径下的配置文件 my.ini记事本修改
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
# created and no character set is defined
default - character - set = gb2312
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values , you need to specify it as an option during the
# MySQL client library initialization.
#
[ client ]
default - character - set = gb2312
port = 3306
[ mysql ]
default - character - set = gb2312
注意:mysql 存储过程参数类型
in: 存储过程内部修改此参数,外部不可见
out:从存储过程内部传值给调用者,在存储过程内部,该参数初始化为NULL,无论调用者是否给存储过程参数设值
inout:调用者可以通过inout参数传递给存储过程,还可以从存储过程内部传值给调用者
具体可参考自:http://www.blogjava.net/nonels/archive/2008/10/09/233324.html
mysql存储过程的写法可参见 http://www.cnblogs.com/hsqzzzl/archive/2008/02/21/1076646.html