|
以前在B/s上写过EXCEL导入导出，其实代码都是差不多的，本人第一次写笔记，说的可能有些模糊
#region 导出EXCEL
private void DataGridViewToExcel(DataTable dt, string filename)
{
try
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.Title = "保存为Excel文件";
dlg.FileName = filename;
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string colHeaders = "", ls_item = "";
//写入列标题
colHeaders += "部门" + "t";
colHeaders += "登录账号" + "t";
colHeaders += "姓名" + "t";
colHeaders += "密码" + "t";
colHeaders += "电话" + "t";
colHeaders += "手机" + "t";
colHeaders += "Email" + "t";
colHeaders += "生日" + "t";
colHeaders += "过期时间";
sw.WriteLine(colHeaders);
DataRow[] myRow = dt.Select();
//写入列内容
foreach (DataRow row in myRow)
{
ls_item += row["dept_name"].ToString() + "t";
ls_item += row["user_loginName"].ToString() + "t";
ls_item += row["user_realName"].ToString() + "t";
ls_item += "" + "t";
ls_item += row["user_telephone"].ToString() + "t";
ls_item += row["user_mobile"].ToString() + "t";
ls_item += row["user_email"].ToString() + "t";
ls_item += row["user_birthday"].ToString() + "t";
ls_item += row["user_expiredTime"].ToString();
sw.WriteLine(ls_item);
ls_item = "";
}
sw.Close();
myStream.Close();
MessageBox.Show("导出[" + filename + "]成功", "提示");
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
#endregion
#region Excel导入
private void tsinuser_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "表格文件 (*.xls)|*.xls";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
InExcelData(openFileDialog.FileName);
}
}
private bool InExcelData(string filePath)
{
try
{
HG_dms_folderEntity folderEnt = new HG_dms_folderEntity(); string folderid;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(strConn);
con.Open();
string[] names = GetExcelSheetNames(con);//GetExcelSheetNames(filePath);
if (names != null)
{
if (names.Length > 0)
{
foreach (string name in names)
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName$]要如此格式
OleDbDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
if (odr[0].ToString() != "")
{
if (odr[0].ToString() == "部门")//过滤列头 按你的实际Excel文件
continue;
if (userLogic.CheckUserLogin(odr[1].ToString()) == 0)
{
……………………写入数据库
}
}
}
odr.Close();
}
}
}
con.Close();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
///
/// 查询表名
///
///
///
public static string[] GetExcelSheetNames(OleDbConnection con)
{
try
{
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
var sheet = new string[dt.Rows.Count];
for (int i = 0, j = dt.Rows.Count; i < j; i++)
{
sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
}
return sheet;
}
catch
{
return null;
}
}
#endregion
|
