winform实现EXCEL的导⼊功能实现demo如图
功能介绍
1.点击导⼊按钮进⾏选择需要导⼊的Excel⽂件
格式定义 fileDialog.Filter = @“Excel表格 | *.xlsx | Excel | *.xls”;
2.导⼊成功后点击保存到数据库。将导⼊的数据写⼊数据库表
代码
using Oracle.ManagedDataAccess.Client; using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace ExcelDemo1
{
public partial class Form1 : Form
{
//建⽴⼀个tabel放数据
DataTable m_dtTable =null;
public Form1()
{
InitializeComponent();
}
#region/// ⽅法⼀：；微软⾃带excel 操作类
/// <summary>
/
// 项⽬运⾏：设置 Any CPU
///引⽤：System.Data.OleDb
///点击事件导⼊按钮：
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel1_Click(object sender, EventArgs e)
{
//打开⼀个⽂件选择框
OpenFileDialog ofd =new OpenFileDialog();
ofd.Title ="Excel⽂件";
ofd.FileName ="";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统⽂件夹，可以使⽤System.Environ ment类的静态⽅法GetFolderPath()。该⽅法接受⼀个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统⽬录
ofd.Filter ="Excel⽂件| *.xlsx;*.xls";
ofd.ValidateNames =true;//⽂件有效性验证ValidateNames，验证⽤户输⼊是否是⼀个有效的Windows⽂件名
ofd.CheckFileExists =true;//验证路径有效性
ofd.CheckPathExists =true;//验证⽂件有效性
string strName =string.Empty;
if(ofd.ShowDialog()== DialogResult.OK)
{
strName = ofd.FileName;
}
if(strName =="")
{
MessageBox.Show("没有选择Excel⽂件！⽆法进⾏数据导⼊");
return;
}
//调⽤导⼊数据⽅法
EcxelToDataGridView(strName);
}
public void EcxelToDataGridView(string filePath)
{
string strConn ="";
if(filePath.EndsWith(".xls"))
{
strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source ="+ filePath +"; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1;'";
//strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={0}; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1;'", filePath); //报错
}
else if(filePath.EndsWith(".xlsx"))
{
//strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source ={0}; Extended Propert
ies = 'Excel 12.0;HDR=Yes;IMEX=1;'", filePa th);//报错
strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="+ filePath +"; Extended Properties = 'Excel 12.0;HDR=Yes;IMEX=1;'";
}
//根据路径打开⼀个Excel⽂件并将数据填充到DataSet中
//导⼊时包含Excel中的第⼀⾏数据，并且将数字和字符混合的单元格视为⽂本进⾏导⼊
System.Data.OleDb.OleDbConnection conn =new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel ="";
System.Data.OleDb.OleDbDataAdapter myCommand =null;
DataSet dd =null;
strExcel ="select * from [sheet1$]";
myCommand =new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
dd =new DataSet();
myCommand.Fill(dd,"table1");
m_dtTable = dd.Tables[0];
//在DataGridView中显⽰导⼊的数据
dgvData.DataSource = m_dtTable;
conn.Close();
}
#endregion
#endregion
#region连接数据库demo
private void btnOracle_Click(object sender, EventArgs e)
{
OracleConnection conn =null;
try
{
conn =OpenConn();
//查询
OracleDataAdapter oda =new OracleDataAdapter("select * from IMES.M_EMP ", conn);
DataTable dt =new DataTable();
//数据绑定到DataTable⾥
oda.Fill(dt);
/
/ DataTable绑定到dataGridView
this.dgvData.DataSource = dt;
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
writeline方法的作用finally
{
CloseConn(conn);
}
}
/// <summary>
/// 打开数据库
/// </summary>
/// <returns></returns>
public static OracleConnection OpenConn()
{
OracleConnection conn =new OracleConnection();
conn.ConnectionString ="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.17.39.9)(PORT=1521))(CONNECT_DATA=(S ERVICE_NAME=imests)));Persist Security Info=True;User ID=imes;Password=imes;";
conn.Open();
return conn;
}
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="conn"></param>
static void CloseConn(OracleConnection conn)
{
if(conn ==null){return;}
try
{
if(conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
conn.Dispose();
}
}
#endregion
#endregion
#region将Excel的数据在datagridview中的写⼊数据库
private void btnSave_Click(object sender, EventArgs e)
{
//Excel中的变量
string m_Type ="";
string m_Ipn ="";
string m_RellId ="";
string m_MatDesc ="";
string m_VendorCode ="";
string m_VendorName ="";
string m_LotNo ="";
string m_DateCode ="";
string m_SafeNo ="";
string m_ExpDate ="";
string m_Qty ="";
string m_Uom ="";
OracleConnection conn =null;
if(dgvData.Rows.Count ==0)
{
MessageBox.Show("请先导⼊数据后再进⾏保存数据");
return;
}
DataRow[] dataRows = m_dtTable.Select("IPN is not null ");
//DataColumn[] dataColumns = m_dtTable.Select("ColumnName is not null");
int iStartDateFlag =3;
bool bNeedCheck =true;
int iClomnsCount = m_dtTable.Columns.Count;
conn =OpenConn();
for(int j =0; j < dataRows.Length; j++)
{
m_Type = dataRows[j][0].ToString();
m_RellId = dataRows[j][1].ToString();
m_Ipn = dataRows[j][2].ToString();
m_MatDesc = dataRows[j][3].ToString();
m_VendorCode= dataRows[j][4].ToString();
m_VendorName= dataRows[j][5].ToString();
m_LotNo= dataRows[j][6].ToString();
m_DateCode= dataRows[j][7].ToString();
m_SafeNo = dataRows[j][8].ToString();
m_ExpDate= dataRows[j][9].ToString();
m_Qty= dataRows[j][10].ToString();
m_Uom= dataRows[j][11].ToString();
//查询
OracleDataAdapter oda =new OracleDataAdapter("select * from IMES.P_GLUE_STATUS  where SERIAL_NUMBER= '"+ m_RellId+"' ", conn);                DataTable dt =new DataTable();
//数据绑定到DataTable⾥
oda.Fill(dt);
if(dt.Rows.Count >=1)
{
MessageBox.Show("RellId："+ m_RellId +"已存在，請确认导⼊的数据是否正确");
break;
}
oda =new OracleDataAdapter("select * from IMES.M_PART  where IPN= '"+ m_Ipn +"' ", conn);
DataTable Ipndt =new DataTable();
//数据绑定到DataTable⾥
oda.Fill(Ipndt);
if(Ipndt.Rows.Count <1)
{

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。