匯入excel檔案

using System;
using System.Windows.Forms;
using System.Text;
using System.Data;
using System.Reflection;
using System.Xml;
using System.Net;
using System.Net.Sockets;
using System.IO;
using System.IO.Ports;
using System.Collections;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Drawing;
using System.Text.RegularExpressions;

/* OrBit-Script指令碼
* 作 者：
* 功能描述：
* 版 本 號：
* 釋出日期:
* 最後修改:
*/
namespace ScriptProj
{
public class OrBitClass
{

public string ExcelFileName = "";
//上傳行數
public string FileCount = "";
public List<string> nbrs = new List<string>();
//MessageBox 結果
public DialogResult dialogResult;
/// <summary>
/// MES事務外掛介面全域性物件，它提供了MES事務物件的所有的介面屬性與介面方法
/// </summary>
public OrBitScript.OrBitAPI Scripter = new OrBitScript.OrBitAPI();

/// <summary>
/// 指令碼物件例項化時載入時的方法
/// </summary>
public void ScriptLoad()
{
//to do..
}


/// <summary>
/// 指令碼在退出前的詢問方法
/// </summary>
/// <returns>True則退出，False則取消退出</returns>
public bool ScriptClosing()
{
//to do..
return true;
}

/// <summary>
/// MES事務物件在關閉時解除安裝此指令碼物件的方法
/// </summary>
public void ScriptUnLoad()
{
//to do..
}

/// <summary>
/// Winsocket訊息接受方法
/// </summary>
/// <param name="MsgString">訊息的內容</param>
public void WinsocketMessage(string MsgString)
{
//to do..

}

/// <summary>
/// MES事務外掛Tab頁選中時的指令碼物件執行的方法
/// </summary>
public void TabActive()
{
//to do..
}

/// <summary>
/// 容器中按鈕觸發時的通用檢查前置事件
/// </summary>
/// <param name="ButtonName">按鈕名</param>
/// <returns>True表示繼續,False表示退回</returns>
public bool ButtonClickBefore(string ButtonName)
{
//to do..
return true;
}

/// <summary>
/// 容器中按鈕通用觸發事件
/// </summary>
/// <param name="ButtonName">按鈕名</param>
public void ButtonClick(string ButtonName)
{

if (ButtonName=="ImportData")
{
OpenFileDialog ofd=new OpenFileDialog();
ofd.FilterIndex=1;
ofd.Filter="文件(*.xls;*.xlsx)|*.xls;*.xlsx";
dialogResult=ofd.ShowDialog();
string str=ofd.FileName.ToString();
ExcelFileName=str.Substring(str.LastIndexOf(@"\"));
//MessageBox.Show(ExcelFileName);
// if(dialogResult==DialogResult.OK)
// {
// Scripter.SetParameterValue("FilePath",ofd.FileName);
// }
DataSet ds = new DataSet();

try
{
// //填充資料
// cmd.Fill(ds);
// //關閉連線
// conn.Close();

ds=xsldata(ofd.FileName);
ds.Tables[0].TableName="Table";
// Write to xml
//ds.WriteXml("D:\\Test.xml");
//MessageBox.Show(ds.GetXml());
//Scripter.SetParameterValue("test",ds.GetXml());
Scripter.SetParameterValue("XMLA",ds.GetXml());


//執行儲存過程
string sql="exec Txn_GX_ImportInspectionDataDoMethod @XMLA='"+ds.GetXml()+"'" ;
//MessageBox.Show("AA"+ds.GetXml());
Scripter.GetSqlDataSet(sql);
//MessageBox.Show("BB"+ds.GetXml());
string XMLA = Scripter.GetParameterValue("XMLA");
if((!nbrs.Contains(XMLA)) && XMLA != "")
{
Scripter.RunMethod("LoadXML");
MessageBox.Show("資料匯入成功");
}

}
catch
{
return ;
}
//
}
//to do..
}
private DataSet xsldata(string filepath)
{
try
{

string st = ExcelFileName.Substring(ExcelFileName.LastIndexOf(".") + 1);

string strForRe = "";
if (st == "xls")
{
strForRe = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
+ filepath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";


}
else if (st == "xlsx")
{
strForRe = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source= "
+ filepath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
}

OleDbConnection connection =
new OleDbConnection(strForRe);


connection.Open();

string tableName = "";
DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
tableName = table.Rows[0]["Table_Name"].ToString();

string str2 = "SELECT * FROM [" + tableName + "]";

OleDbDataAdapter adapter = new OleDbDataAdapter(str2, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "[" + tableName + "]");
connection.Close();
return dataSet;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);

return null;
}
}
/// <summary>
/// 容器中按鈕觸發時的通用檢查後置事件
/// </summary>
/// <param name="ButtonName">按鈕名</param>
/// <param name="SPResult">儲存過程執行的結果值</param>
/// <param name="SPMessage">儲存過程執行的返回訊息</param>
public void ButtonClickAfter(string ButtonName,bool SPResult,string SPMessage)
{
//to do..
}

/// <summary>
/// 容器中網格或下拉選單選中某行時的觸發事件
/// </summary>
/// <param name="ParameterName">物件引數名</param>
/// <param name="CellA">選中行第一列的值</param>
/// <param name="CellB">選中行第二列的值</param>
public void RowSelected(string ParameterName, string CellA, string CellB)
{
//to do..
}

/// <summary>
/// 容器中可輸入控制元件獲取游標焦點後觸發的事件
/// </summary>
/// <param name="ParameterName">物件引數名</param>
public void ParameterEnter(string ParameterName)
{
//to do..
}

/// <summary>
/// 容器中可輸入控制元件按回車後觸發的事件
/// </summary>
/// <param name="ParameterName">物件引數名</param>
/// <param name="ParameterValue">輸入值</param>
/// <returns>True跳到下一個控制元件，False表示留在原控制元件中</returns>
public bool EnterPressed(string ParameterName, string ParameterValue)
{
//to do..
return true;
}


/// <summary>
/// 批號掃描成功後觸發的事件
/// </summary>
public void LotSNScanned(string LotSN)
{
//to do..
}

/// <summary>
/// 動態事務批號傳送前觸發的事件
/// </summary>
/// <returns>True允許執行Move，False終止</returns>
public bool LotMoveBefore(string LotId)
{
//to do..
return true;
}

/// <summary>
/// 動態事務批號傳送後觸發的事件
/// </summary>
public void LotMoved(string LotId)
{
//to do..
}

/// <summary>
/// 復位按鈕觸發的事件
/// </summary>
public void LotReset()
{
//to do..
}

}
}