/// <summary>
/// 读取excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Multiselect = false;//该值确定是否可以选择多个文件
dialog.Title = "请选择文件";
dialog.Filter = "所有文本文件(*.xls)|*.xls";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + dialog.FileName + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
//遍历DataSet中第一个表的多行多列
foreach (DataRow mDr in ds.Tables[0].Rows)
{
string strNo = "";
string strName = "";
int age = 0;
string strJob = "";
string strState = "";
string strRemark = "";
int index=0;
foreach (DataColumn mDc in ds.Tables[0].Columns)
{
switch (index)
{
case 0:
strNo = mDr[mDc].ToString();
break;
case 1:
strName = mDr[mDc].ToString();
break;
case 2:
age = int.Parse(mDr[mDc].ToString());
break;
case 3:
strJob = mDr[mDc].ToString();
break;
case 4:
strState = mDr[mDc].ToString();
break;
case 5:
strRemark = mDr[mDc].ToString();
break;
}
index++;
}
string strSql = string.Format("insert into t_users(no,name,age,job,state,remark)values('{0}','{1}',{2},'{3}','{4}','{5}')", strNo, strName, age, strJob, strState, strRemark);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringMid, CommandType.Text, strSql, null);
}
conn.Close();
MessageBox.Show("导入成功");
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
SaveFileDialog kk = new SaveFileDialog();
kk.Title = "保存EXECL文件";
kk.Filter = "EXECL文件(*.xls) |*.xls |所有文件(*.*) |*.*";
kk.FilterIndex = 1;
kk.FileName = DateTime.Now.ToString("yyyy-MM-dd");
if (kk.ShowDialog() == DialogResult.OK)
{
string FileName = kk.FileName;
if (File.Exists(FileName))
File.Delete(FileName);
FileStream objFileStream;
StreamWriter objStreamWriter;
string strLine = "";
objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
strLine = strLine + "编号" + System.Convert.ToChar(9);
strLine = strLine + "名称" + System.Convert.ToChar(9);
strLine = strLine + "年龄" + System.Convert.ToChar(9);
strLine = strLine + "岗位" + System.Convert.ToChar(9);
strLine = strLine + "状态" + System.Convert.ToChar(9);
strLine = strLine + "备注" + System.Convert.ToChar(9);
objStreamWriter.WriteLine(strLine);
strLine = "";
string strsql = "select * from t_users";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringMid, CommandType.Text, strsql))
{
while(reader.Read())
{
strLine = strLine + reader["no"].ToString() + System.Convert.ToChar(9);
strLine = strLine + reader["name"].ToString() + System.Convert.ToChar(9);
strLine = strLine + reader["age"].ToString() + System.Convert.ToChar(9);
strLine = strLine + reader["job"].ToString() + System.Convert.ToChar(9);
strLine = strLine + reader["state"].ToString() + System.Convert.ToChar(9);
strLine = strLine + reader["remark"].ToString() + System.Convert.ToChar(9);
objStreamWriter.WriteLine(strLine);
strLine = "";
}
}
objStreamWriter.Close();
MessageBox.Show(this, "导出EXCEL成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// 读取txt
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Multiselect = false;//该值确定是否可以选择多个文件
dialog.Title = "请选择文件";
dialog.Filter = "所有文本文件(*.txt)|*.txt";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string file = dialog.FileName;
FileInfo finfo = new FileInfo(file);
FileStream fs = finfo.Open(FileMode.OpenOrCreate,
FileAccess.ReadWrite);
StreamReader sr = new StreamReader(fs);
string strList = sr.ReadLine();
while (strList != null)
{
string strNo=strList.Split(' ')[0];
string strName=strList.Split(' ')[1];
int age=int.Parse(strList.Split(' ')[2]);
string strJob=strList.Split(' ')[3];
string strState=strList.Split(' ')[4];
string strRemark=strList.Split(' ')[5];
//写入数据库
string strSql = string.Format("insert into t_users(no,name,age,job,state,remark)values('{0}','{1}',{2},'{3}','{4}','{5}')", strNo, strName, age, strJob,strState,strRemark);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringMid, CommandType.Text, strSql, null);
strList = sr.ReadLine();
}
sr.Close();
fs.Close();
MessageBox.Show("导入成功");
}
}
/// <summary>
/// 导出txt
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
SaveFileDialog kk = new SaveFileDialog();
kk.Title = "保存TXT文件";
kk.Filter = "TXT文件(*.txt) |*.txt";
kk.FilterIndex = 1;
kk.FileName = DateTime.Now.ToString("yyyy-MM-dd");
if (kk.ShowDialog() == DialogResult.OK)
{
FileInfo finfo = new FileInfo(kk.FileName);
FileStream fs = finfo.Open(FileMode.Create, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(fs);
for (int i = 0; i < dataGridView1.RowCount; i++)
{
sw.WriteLine(dataGridView1.Rows[i].Cells[0].Value.ToString() + ' ' + dataGridView1.Rows[i].Cells[1].Value.ToString() + ' ' + dataGridView1.Rows[i].Cells[2].Value.ToString() + ' ' + dataGridView1.Rows[i].Cells[3].Value.ToString() + ' ' + dataGridView1.Rows[i].Cells[4].Value.ToString() + ' ' + dataGridView1.Rows[i].Cells[5].Value.ToString());
}
sw.Close();
fs.Close();
MessageBox.Show(this, "导出TXT成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}