using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Threading;
using System.Data.OracleClient;
添加以上引用
public void Button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "工作薄(*.xls)|*.xlsx";
CreateTempExcelTable();
if (openFileDialog1.FilterIndex == 1 && openFileDialog1.ShowDialog() == DialogResult.OK)
{
splashScreenManager2.ShowWaitForm();//显示waitform
splashScreenManager2.SetWaitFormCaption("请稍等");
string fileName = openFileDialog1.FileName;
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(fileName);
Excel.Worksheet sheet = workbook.Sheets[1];
int number = 0;//记录插入有效行数
int flag = 0;//数据行是否符合标准,再决定是否把该行加入dt中
int i = 2;//从第二行开始录入,第一行为字段名
while (((Excel.Range)sheet.Cells[i, 1]).Text != "")
{
DataRow dr = dt_ReciveExcelFileWage.NewRow();
for (int j = 1; j <= dt_ReciveExcelFileWage.Columns.Count + 2; j++)//从第二列开始循环,编码,Excel中的第三列和第四列不用存储
{
if (j == 1)
{
dr[j - 1] = GetStaffIdByFcode(((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text);
}
else if (j == 2 || j == 3)
{
continue;
}
else
{
if (((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text != "")
{
if (IsFloat(((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text))
{
dr[j - 3] = ((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text;
}
else
{
flag++;
break;
}
}
}
}
if (flag == 0)
{
dt_ReciveExcelFileWage.Rows.Add(dr);
number++;
}
else
{
flag = 0;
}
i++;
}
i = i - 2;
Kill(excel);
splashScreenManager2.CloseWaitForm();//关闭waitform
DialogResult dia = MessageService.ShowMessage("共导入" + i + "条记录,其中有效记录" + number + "条,是否确定导入", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dia == DialogResult.OK)
{//用事物存储表dt_
splashScreenManager2.ShowWaitForm();
splashScreenManager2.SetWaitFormDescription("正在保存数据...");
splashScreenManager2.SetWaitFormCaption("请稍等");
if (DoWork(dt_ReciveExcelFileWage) == true)
{
MessageService.ShowMessage("成功存储" + number + "条记录", "提示");
}
else
{
MessageService.ShowMessage("导入失败", "提示");
}
splashScreenManager2.CloseWaitForm();
}
else
{
dt_ReciveExcelFileWage = null;
}
}
else
{
dt_ReciveExcelFileWage = null;
}
}
#region 获取excel中列
/// <summary>
/// 获取excel中列
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
private string getCode(string num)
{
switch (num)
{
case "1":
return "A";
case "2":
return "B";
case "3":
return "C";
case "4":
return "D";
case "5":
return "E";
case "6":
return "F";
case "7":
return "G";
case "8":
return "H";
case "9":
return "I";
case "10":
return "J";
case "11":
return "K";
case "12":
return "L";
case "13":
return "M";
case "14":
return "N";
case "15":
return "O";
case "16":
return "P";
case "17":
return "Q";
case "18":
return "R";
case "19":
return "S";
case "20":
return "T";
case "21":
return "U";
case "22":
return "V";
case "23":
return "W";
case "24":
return "X";
case "25":
return "Y";
case "26":
return "Z";
default:
return "A";
}
}
#endregion
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}