用npoi方式，遇到一个问题，有的excel用加密软件(盖章，只读等)生成的解析不了，所以换成自带的方式，可以解决。
需要引用系统自带Microsoft.office.interop.excel
public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string f = @"D:kintest.xlsx"; CreateExcelFile(f); WriteToExcel(f, "file2", "编号2", "姓名2"); } private void CreateExcelFile(string FileName) { if (File.Exists(FileName)) { File.Delete(FileName); } //create object Nothing = System.Reflection.Missing.Value; var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Nothing); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1]; worksheet.Name = "Work"; //headline worksheet.Cells[1, 1] = "员工编号"; worksheet.Cells[1, 2] = "姓名"; worksheet.Cells[1, 3] = "A卡"; worksheet.Cells[1, 4] = "B卡"; worksheet.Cells[1, 5] = "开通餐卡"; worksheet.Cells[1, 6] = "备注"; worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); workBook.Close(false, Type.Missing, Type.Missing); app.Quit(); KillExcel.Kill(new IntPtr(app.Hwnd)); app = null; } private void WriteToExcel(string excelName, string filename, string findString, string replaceString) { //open object Nothing = System.Reflection.Missing.Value; var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]; mysheet.Activate(); //get activate sheet max row count int maxrow = mysheet.UsedRange.Rows.Count + 1; mysheet.Cells[maxrow, 1] = filename; mysheet.Cells[maxrow, 2] = findString; mysheet.Cells[maxrow, 3] = replaceString; mybook.Save(); mybook.Close(false, Type.Missing, Type.Missing); mybook = null; //quit excel app app.Quit(); KillExcel.Kill(new IntPtr(app.Hwnd)); app = null; } private void OpenExcel(string strFileName) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application if (excel == null) { //Response.Write("<script>alert('Can't access excel')</script>"); } else { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一个工作薄 //Worksheet ws = (Worksheet)wb.Worksheets.get_Item(2); Worksheet ws = (Worksheet)excel.ActiveSheet; //取得总记录行数 (包括标题列) int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数 //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数 //取得数据范围区域 (不包括标题列) Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint); //item Range rng2 = ws.Cells.get_Range("E2", "E" + rowsint); //Customer object[,] arryItem = (object[,])rng1.Value2; //get range's value object[,] arryCus = (object[,])rng2.Value2; //将新值赋给一个数组 string[,] arry = new string[rowsint - 1, 2]; for (int i = 1; i <= rowsint - 1; i++) { //Item_Code列 //arry[i - 1, 0] = arryItem[i, 1].ToString(); //Customer_Name列 //arry[i - 1, 1] = arryCus[i, 1].ToString(); string i1 = arryItem[i, 1] == null ? "" : arryItem[i, 1].ToString(); string i2 = arryCus[i, 1] == null ? "" : arryCus[i, 1].ToString(); Console.WriteLine(i1 + "--" + i2); } //Response.Write(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]); Console.WriteLine("wb.ReadOnly--" + wb.ReadOnly); wb.Close(); } excel.DisplayAlerts = false; excel.Quit(); KillExcel.Kill(new IntPtr(excel.Hwnd)); excel = null; ////Process[] procs = Process.GetProcessesByName("excel"); ////foreach (Process pro in procs) ////{ //// pro.Kill();//没有更好的方法,只有杀掉进程 ////} } //选择excel private void button3_Click(object sender, EventArgs e) { OpenFileDialog f = new OpenFileDialog(); f.Multiselect = true; f.ShowDialog(); string[] filenames = f.FileNames; if (filenames.Length > 0) { this.label1.Text = filenames[0]; string strFile = System.IO.Path.GetFileName(filenames[0]); System.Data.DataTable dt = GetDataBySQLNew("McsDW", "select 1 from [McsDW].[dbo].[UserGateWayNumber] where ExcelFileName='" + strFile + "'"); if (dt != null) { if (dt.Rows.Count > 0) { MessageBox.Show("数据库里已经导入名称为 " + strFile + " 的excel表，请核对！"); this.label1.Text = ""; } } } } //对比数据 private void button2_Click(object sender, EventArgs e) { if (this.label1.Text.Length < 1) { MessageBox.Show("请先选择excel文件！"); return; } string sheetIndex = texBoxSheet.Text; int sheetInt = 1; int.TryParse(sheetIndex, out sheetInt); if (sheetInt < 1) { MessageBox.Show("sheet序号请填写整数！"); return; } ReadFromExcelFile(this.label1.Text, sheetInt); } private void ReadFromExcelFile(string filePath, int sheetInt) { string extension = System.IO.Path.GetExtension(filePath); try { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application if (excel == null) { //Response.Write("<script>alert('Can't access excel')</script>"); } else { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 Workbook wb = excel.Application.Workbooks.Open(filePath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); //取得第一个工作薄 //Worksheet ws = (Worksheet)wb.Worksheets.get_Item(2); Worksheet ws = (Worksheet)excel.ActiveSheet; //取得总记录行数 (包括标题列) int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数 //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数 //取得数据范围区域 (不包括标题列) Range rngxuhao = ws.Cells.get_Range("A3", "A" + rowsint); Range rngdanwei = ws.Cells.get_Range("B3", "B" + rowsint); Range rngbumen = ws.Cells.get_Range("C3", "C" + rowsint); Range rngbianhao = ws.Cells.get_Range("D3", "D" + rowsint); Range rngname = ws.Cells.get_Range("E3", "E" + rowsint); Range rngAka = ws.Cells.get_Range("F3", "F" + rowsint); Range rngBka = ws.Cells.get_Range("G3", "G" + rowsint); Range rngkai = ws.Cells.get_Range("H3", "H" + rowsint); object[,] arryxuhao = (object[,])rngxuhao.Value2; //get range's value object[,] arrydanwei = (object[,])rngdanwei.Value2; object[,] arrybumen = (object[,])rngbumen.Value2; object[,] arrybianhao = (object[,])rngbianhao.Value2; object[,] arryname = (object[,])rngname.Value2; object[,] arryAka = (object[,])rngAka.Value2; object[,] arryBka = (object[,])rngBka.Value2; object[,] arrykai = (object[,])rngkai.Value2; //将新值赋给一个数组 string[,] arry = new string[rowsint - 1, 2]; string userCount = ""; Dictionary<string, List<string>> userDic = new Dictionary<string, List<string>>(); for (int i = 1; i <= rowsint - 1; i++) { //Item_Code列 //arry[i - 1, 0] = arryItem[i, 1].ToString(); //Customer_Name列 //arry[i - 1, 1] = arryCus[i, 1].ToString(); string iA = arryxuhao[i, 1] == null ? "" : arryxuhao[i, 1].ToString(); string iB = arrydanwei[i, 1] == null ? "" : arrydanwei[i, 1].ToString(); string iC = arrybumen[i, 1] == null ? "" : arrybumen[i, 1].ToString(); string iD = arrybianhao[i, 1] == null ? "" : arrybianhao[i, 1].ToString(); string iE = arryname[i, 1] == null ? "" : arryname[i, 1].ToString(); string iF = arryAka[i, 1] == null ? "" : arryAka[i, 1].ToString(); string iG = arryBka[i, 1] == null ? "" : arryBka[i, 1].ToString(); string iH = arrykai[i, 1] == null ? "" : arrykai[i, 1].ToString(); if (iA.Contains("计数")) { int uxuhaoInt = 0; int.TryParse(iH, out uxuhaoInt); userCount = uxuhaoInt.ToString(); } if (iA.Equals("") || iA.Equals("序号")) { continue; } if (iD.Equals("")) { continue; } if (iA.Contains("计数") || iA.Contains("负责人") || iA.Contains("开通餐卡")) { break; } List<string> ulist = new List<string>(); ulist.Add(iB); ulist.Add(iC); ulist.Add(iE); ulist.Add(iH); ulist.Add(iF); ulist.Add(iG); userDic.Add(iD, ulist); Console.WriteLine(iA + "--" + iB + "--" + iC + "--" + iD + "--" + iE + "--" + iF + "--" + iG + "--" + iH); } //Response.Write(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]); Console.WriteLine("wb.ReadOnly--" + wb.ReadOnly); wb.Close(); if (userCount.Equals("") && userCount.Equals("0")) userCount = "没有计数"; DoUser(userDic, filePath, userCount, sheetInt); } excel.DisplayAlerts = false; excel.Quit(); KillExcel.Kill(new IntPtr(excel.Hwnd)); excel = null; } catch (Exception e) { //只在Debug模式下才输出 Console.WriteLine(e.Message); MessageBox.Show(e.Message); } } private void DoUser(Dictionary<string, List<string>> userDic, string filePath, string userCount, int sheetInt) { if (userDic.Count < 1) return; string namev = ""; string tip = ""; System.Data.DataTable dtNew = new System.Data.DataTable(); try { // System.Data.DataTable dt = GetDataBySQLNew("McsDW", "select * from UserViewAll_DW "); dtNew.Columns.Add("员工编号", Type.GetType("System.String")); dtNew.Columns.Add("姓名", Type.GetType("System.String")); dtNew.Columns.Add("A卡", Type.GetType("System.String")); dtNew.Columns.Add("B卡", Type.GetType("System.String")); dtNew.Columns.Add("开通餐卡", Type.GetType("System.String")); dtNew.Columns.Add("备注", Type.GetType("System.String")); string sql = "", sqlabnull = ""; string remark = ""; int passCount = 0; System.Data.DataTable dtNewABNull = dtNew.Clone(); foreach (var d in userDic) { //Console.Write(d.Key+"----"+d.Value); //Console.WriteLine(" "); //namev = d.Value; remark = ""; bool isExcel = false; DataRow[] rows = dt.Select("PostionLevel='" + d.Key + "'"); //if (d.Value[3].Trim().Equals("否")) // continue; //开通餐卡为空，默认为不开通 if (d.Value[3].Trim().Equals("")) { //Console.WriteLine(d.Key + "----" + d.Value + "----开通餐卡为空"); //isExcel = true; //remark = "开通餐卡为空!"; continue; } //开通餐卡，填的不是A,B if (!(d.Value[3].Trim().ToUpper().Equals("A") || d.Value[3].Trim().ToUpper().Equals("B"))) { isExcel = true; remark += "开通餐卡项填写错误，填的不是A,B"; } //如果开通A卡但是A卡为空,B卡不为空，也提示 else if (d.Value[3].Trim().ToUpper().Equals("A") && d.Value[4].Trim().Equals("") && !d.Value[5].Trim().Equals("")) { isExcel = true; remark += "需要开通A卡但是A卡为空，B卡不为空，请填B卡"; } //如果开通B卡但是B卡为空，A卡不为空，也提示 else if (d.Value[3].Trim().ToUpper().Equals("B") && d.Value[5].Trim().Equals("") && !d.Value[4].Trim().Equals("")) { isExcel = true; remark += "需要开通B卡但是B卡为空，A卡不为空，请填A卡"; } else if (rows == null) { isExcel = true; remark += "数据库为空"; } else if (rows.Length < 1) { isExcel = true; remark += "数据库找不到该用户"; } else { //判断用户名，中文名字 if (!(d.Value[2].Trim().Equals(rows[0]["UserDisplayName"].ToString().Trim()))) { isExcel = true; remark += "数据库里该用户名不匹配:" + rows[0]["UserDisplayName"].ToString(); } //如果开通A卡，判断A卡和数据库是否匹配 if (d.Value[3].Trim().ToUpper().Equals("A") && !(d.Value[4].Trim().Equals(rows[0]["GatewayNumber"].ToString().Trim()))) { isExcel = true; remark += "开通A卡,但数据库里该用户A卡不匹配:" + rows[0]["GatewayNumber"].ToString(); } //如果开通B卡，判断B卡和数据库是否匹配 if (d.Value[3].Trim().ToUpper().Equals("B") && !(d.Value[5].Trim().Equals(rows[0]["GatewayNumber2"].ToString().Trim()))) { isExcel = true; remark += "开通B卡,但数据库里该用户B卡不匹配:" + rows[0]["GatewayNumber2"].ToString(); } //string kastr = GetKa(rows[0]["GatewayNumber2"], rows[0]["GatewayNumber"]); //if (kastr.Equals("") || kastr.Equals("没有卡号")) //{ // isExcel = true; // remark += "数据库里该用户卡号有误:" + kastr; //} } string Gender = GetGender(d.Key); //如果A卡和B卡都为空，也提示 if (d.Value[4].Trim().Equals("") && d.Value[5].Trim().Equals("")) { string abnull = "A,B卡都为空，请尽快启动办卡流程"; dtNewABNull.Rows.Add(new object[] { d.Key, d.Value[2], d.Value[4], d.Value[5], d.Value[3], abnull }); sqlabnull += string.Format(" insert into UserGateWayNumberABNull(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Remark,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", d.Value[0], d.Value[1], d.Key, d.Value[2], "", System.IO.Path.GetFileName(filePath), d.Value[3], abnull, Gender); passCount++; continue; } if (isExcel) { DataRow r = dtNew.NewRow(); r["员工编号"] = d.Key; r["姓名"] = d.Value[2]; r["A卡"] = d.Value[4]; r["B卡"] = d.Value[5]; r["开通餐卡"] = d.Value[3]; r["备注"] = remark; dtNew.Rows.Add(r); } else { string gNum = ""; if (d.Value[3].Trim().ToUpper().Equals("A")) gNum = d.Value[4]; else gNum = d.Value[5]; sql += string.Format(" insert into UserGateWayNumber(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", d.Value[0], d.Value[1], d.Key, d.Value[2], gNum, System.IO.Path.GetFileName(filePath), d.Value[3], Gender); passCount++; } } int ucount = 0; int.TryParse(userCount, out ucount); if (dtNew.Rows.Count > 0) { //DataRow r = dtNew.NewRow(); //r["员工编号"] = filePath; //r["姓名"] = ""; //r["A卡"] = ""; //r["B卡"] = ""; //r["开通餐卡"] = ""; //r["备注"] = ""; //dtNew.Rows.Add(r); //加入AB都为空的用户 foreach (DataRow ro in dtNewABNull.Rows) { dtNew.Rows.Add(ro.ItemArray); } //加入excel名 dtNew.Rows.Add(new object[] { filePath, "", "", "", "", "" }); DataTableToExcel2(dtNew); tip = "总共处理了" + userDic.Count + "条数据，其中异常的有" + (dtNew.Rows.Count - 1).ToString() + "条。请查看D:/食堂餐卡统计.xlsx"; } else if (ucount != passCount && !userCount.Equals("没有计数")) { tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + passCount + "条， excel表中计数的有" + ucount + "条。数据不匹配，请核查！"; } else { GetDataBySQLNew("McsDW", sql + sqlabnull); tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + (passCount - dtNewABNull.Rows.Count).ToString() + "条，数据已经录入到[McsDW].[dbo].[UserGateWayNumber]表中, AB卡都为空但是需要开通卡的用户数据有" + dtNewABNull.Rows.Count + "条，此数据录入到表UserGateWayNumberABNull"; } } catch (Exception e) { MessageBox.Show(e.Message + "--" + namev); } Console.Write("总共" + userDic.Count); MessageBox.Show(tip); } private string GetGender(string pl) { string sql = "select Gender from [McsDW].[dbo].[UserViewAll_DW] where PostionLevel='" + pl + "'"; System.Data.DataTable dt = GetDataBySQLNew("McsDW", sql); if (dt == null || dt.Rows.Count < 1) return ""; return dt.Rows[0][0] == null ? "" : dt.Rows[0][0].ToString(); } private bool DataTableToExcel2(System.Data.DataTable dt) { bool result = false; string f = @"D:食堂餐卡统计.xlsx"; CreateExcelFile(f); WriteToExcel(f, dt); return result; } private void WriteToExcel(string excelName, System.Data.DataTable dt) { //open object Nothing = System.Reflection.Missing.Value; var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]; mysheet.Activate(); //get activate sheet max row count int maxrow = mysheet.UsedRange.Rows.Count + 1; for (int i = maxrow; i < dt.Rows.Count+maxrow; i++) { mysheet.Cells[i, 1] = dt.Rows[i-maxrow][0]; mysheet.Cells[i, 2] = dt.Rows[i - maxrow][1]; mysheet.Cells[i, 3] = dt.Rows[i - maxrow][2]; mysheet.Cells[i, 4] = dt.Rows[i - maxrow][3]; mysheet.Cells[i, 5] = dt.Rows[i - maxrow][4]; mysheet.Cells[i, 6] = dt.Rows[i - maxrow][5]; } mybook.Save(); mybook.Close(false, Type.Missing, Type.Missing); mybook = null; //quit excel app app.Quit(); KillExcel.Kill(new IntPtr(app.Hwnd)); app = null; } private System.Data.DataTable GetDataBySQLNew(string database, string sql) { System.Data.DataTable dt = new System.Data.DataTable(); string strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;"; if (database.Equals("McsDW")) { strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;"; } else if (database.Equals("McsSP")) { strServer = "Server=127.0.0.0; DataBase=McsSP; user id=sqluser; password=Pass@word123;"; } using (System.Data.SqlClient.SqlConnection SqlCnn = new System.Data.SqlClient.SqlConnection(strServer)) { SqlCnn.Open(); System.Data.SqlClient.SqlDataAdapter SqlDa = new System.Data.SqlClient.SqlDataAdapter(sql, SqlCnn); DataSet DS = new DataSet(); SqlDa.Fill(DS); SqlDa.Dispose(); SqlCnn.Close(); if (DS == null || DS.Tables == null || DS.Tables.Count < 1) return null; dt = DS.Tables[0]; } return dt; }
这种方式的话，导入一个excel，进程里就多了一个excel进程，必须kill掉，找到进程ID再kill
1 public class KillExcel 2 { 3 [DllImport("User32.dll", CharSet = CharSet.Auto)] 4 public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); 5 6 7 /// <summary> 8 /// 强制关闭当前Excel进程 9 /// </summary> 10 public static void Kill(IntPtr intPtr) 11 { 12 try 13 { 14 Process[] ps = Process.GetProcesses(); 15 int ExcelID = 0; 16 GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k 17 foreach (Process p in ps) 18 { 19 if (p.ProcessName.ToLower().Equals("excel")) 20 { 21 if (p.Id == ExcelID) 22 { 23 p.Kill(); 24 } 25 } 26 } 27 } 28 catch 29 { 30 //不做任何处理 31 } 32 } 33 }