//private void aButton1_Click(object sender, EventArgs e)
//{
// if (openFileDialog1.ShowDialog() == DialogResult.OK)
// {
// Application.DoEvents();
// textBoxFile.Text = openFileDialog1.FileName;
// textBoxFile.SelectionStart = textBoxFile.Text.Length;
// ds = GetDataSet(textBoxFile.Text, MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_AwardUserSheetName), "1=1");
// if (ds != null)
// {
// DGVGameUser.AutoGenerateColumns = false;
// DGVGameUser.DataSource = ds.Tables[0];
// labelUserCount.Text = "(" + ds.Tables[0].Rows.Count.ToString() + ")";
// }
// else
// {
// DGVGameUser.Rows.Clear();
// labelUserCount.Text = "(0)";
// }
// }
//}
public DataSet GetDataSet(string filename, string tname, string wherestr)//返回excel中不把第一行当做标题看待的数据集
{
try
{
//OleDbDataAdapter read column donot support ko-kr ,first read the sheet
string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0 Xml;HDR=yes\"";
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(sConnectionString);
string sql_select_commands = "Select * from [" + tname + "$] where len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_UserNo)
+ "])>0 and len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_RoleNo)
+ "])>0 and len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_ZoneNo) + "])>0";
if (wherestr.Length != 0 && wherestr != "1=1")
{
sql_select_commands += " where " + wherestr;
}
System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(sql_select_commands, connection);
DataSet ds = new DataSet();
adp.Fill(ds, "table_a");
adp.Dispose();
connection.Close();
return(ds);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message.ToString()); return(null);
}
}
private void SelectFileComplate(object data)
{
string path = data as string;
gridReceiveRole.Columns.Clear();
if (string.IsNullOrEmpty(path))
{
MsgBox.Show(LanguageResource.Language.Tip_PleaseSelectDataFile, LanguageResource.Language.Tip_Tip, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
txtFile.Text = path;
StringBuilder error = new StringBuilder();
//加载文件中的数据
DataSet ds = OledbFile.GetDataSet(path, SystemConfig.DefaultSheetName, "len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_RoleNo) + "])>0", error);
string msg = error.ToString();
if (!string.IsNullOrEmpty(msg))
{
MsgBox.Show(msg, LanguageResource.Language.Tip_Tip, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//剔除多余列
rolesTable = ds.Tables[0].Copy();//是否含有指定的列数据
int ci = 0;
DataColumn[] origin = new DataColumn[rolesTable.Columns.Count];
rolesTable.Columns.CopyTo(origin, 0);
foreach (DataColumn item in origin)
{
string name = item.ColumnName;
if (excelColumns.Contains(name.Trim()))
{
ci++;
}
else
{
rolesTable.Columns.Remove(item);//不能直接使用datatable进行遍历移除【 集合已修改；可能无法执行枚举操作】
}
}
if (ci < excelColumns.Length)
{
MsgBox.Show(LanguageResource.Language.Tip_ExcelTemplateError, LanguageResource.Language.Tip_Tip, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
gridReceiveRole.DataSource = rolesTable;
}