用npoi方式，遇到一个问题，有的excel用加密软件(盖章，只读等)生成的解析不了，所以换成自带的方式，可以解决。
需要引用系统自带Microsoft.office.interop.excel
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
string f = @"D:\bkintest.xlsx";
CreateExcelFile(f);
WriteToExcel(f, "file", "编号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[];
worksheet.Name = "Work";
//headline
worksheet.Cells[, ] = "员工编号";
worksheet.Cells[, ] = "姓名";
worksheet.Cells[, ] = "A卡";
worksheet.Cells[, ] = "B卡";
worksheet.Cells[, ] = "开通餐卡";
worksheet.Cells[, ] = "备注"; 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[];
mysheet.Activate();
//get activate sheet max row count
int maxrow = mysheet.UsedRange.Rows.Count + ;
mysheet.Cells[maxrow, ] = filename;
mysheet.Cells[maxrow, ] = findString;
mysheet.Cells[maxrow, ] = 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 - , ];
for (int i = ; i <= rowsint - ; 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, ] == null ? "" : arryItem[i, ].ToString();
string i2 = arryCus[i, ] == null ? "" : arryCus[i, ].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 > )
{
this.label1.Text = filenames[];
string strFile = System.IO.Path.GetFileName(filenames[]);
System.Data.DataTable dt = GetDataBySQLNew("McsDW", "select 1 from [McsDW].[dbo].[UserGateWayNumber] where ExcelFileName='" + strFile + "'");
if (dt != null)
{
if (dt.Rows.Count > )
{
MessageBox.Show("数据库里已经导入名称为 " + strFile + " 的excel表，请核对！");
this.label1.Text = "";
}
}
}
} //对比数据
private void button2_Click(object sender, EventArgs e)
{
if (this.label1.Text.Length < )
{
MessageBox.Show("请先选择excel文件！");
return;
}
string sheetIndex = texBoxSheet.Text;
int sheetInt = ;
int.TryParse(sheetIndex, out sheetInt);
if (sheetInt < )
{
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 - , ];
string userCount = "";
Dictionary<string, List<string>> userDic = new Dictionary<string, List<string>>();
for (int i = ; i <= rowsint - ; 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, ] == null ? "" : arryxuhao[i, ].ToString();
string iB = arrydanwei[i, ] == null ? "" : arrydanwei[i, ].ToString();
string iC = arrybumen[i, ] == null ? "" : arrybumen[i, ].ToString();
string iD = arrybianhao[i, ] == null ? "" : arrybianhao[i, ].ToString();
string iE = arryname[i, ] == null ? "" : arryname[i, ].ToString();
string iF = arryAka[i, ] == null ? "" : arryAka[i, ].ToString();
string iG = arryBka[i, ] == null ? "" : arryBka[i, ].ToString();
string iH = arrykai[i, ] == null ? "" : arrykai[i, ].ToString(); if (iA.Contains("计数"))
{
int uxuhaoInt = ;
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(""))
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 < )
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 = ;
System.Data.DataTable dtNewABNull = dtNew.Clone(); foreach (var d in userDic)
{
//Console.Write(d.Key+"----"+d.Value);
//Console.WriteLine("\n");
//namev = d.Value;
remark = "";
bool isExcel = false;
DataRow[] rows = dt.Select("PostionLevel='" + d.Key + "'");
//if (d.Value[3].Trim().Equals("否"))
// continue;
//开通餐卡为空，默认为不开通
if (d.Value[].Trim().Equals(""))
{
//Console.WriteLine(d.Key + "----" + d.Value + "----开通餐卡为空");
//isExcel = true;
//remark = "开通餐卡为空!";
continue;
}
//开通餐卡，填的不是A,B
if (!(d.Value[].Trim().ToUpper().Equals("A") || d.Value[].Trim().ToUpper().Equals("B")))
{
isExcel = true;
remark += "开通餐卡项填写错误，填的不是A,B";
}
//如果开通A卡但是A卡为空,B卡不为空，也提示
else if (d.Value[].Trim().ToUpper().Equals("A") && d.Value[].Trim().Equals("") && !d.Value[].Trim().Equals(""))
{
isExcel = true;
remark += "需要开通A卡但是A卡为空，B卡不为空，请填B卡";
}
//如果开通B卡但是B卡为空，A卡不为空，也提示
else if (d.Value[].Trim().ToUpper().Equals("B") && d.Value[].Trim().Equals("") && !d.Value[].Trim().Equals(""))
{
isExcel = true;
remark += "需要开通B卡但是B卡为空，A卡不为空，请填A卡";
}
else if (rows == null)
{
isExcel = true;
remark += "数据库为空";
}
else if (rows.Length < )
{
isExcel = true;
remark += "数据库找不到该用户";
}
else
{
//判断用户名，中文名字
if (!(d.Value[].Trim().Equals(rows[]["UserDisplayName"].ToString().Trim())))
{
isExcel = true;
remark += "数据库里该用户名不匹配:" + rows[]["UserDisplayName"].ToString();
}
//如果开通A卡，判断A卡和数据库是否匹配
if (d.Value[].Trim().ToUpper().Equals("A") && !(d.Value[].Trim().Equals(rows[]["GatewayNumber"].ToString().Trim())))
{
isExcel = true;
remark += "开通A卡,但数据库里该用户A卡不匹配:" + rows[]["GatewayNumber"].ToString();
}
//如果开通B卡，判断B卡和数据库是否匹配
if (d.Value[].Trim().ToUpper().Equals("B") && !(d.Value[].Trim().Equals(rows[]["GatewayNumber2"].ToString().Trim())))
{
isExcel = true;
remark += "开通B卡,但数据库里该用户B卡不匹配:" + rows[]["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[].Trim().Equals("") && d.Value[].Trim().Equals(""))
{
string abnull = "A,B卡都为空，请尽快启动办卡流程";
dtNewABNull.Rows.Add(new object[] { d.Key, d.Value[], d.Value[], d.Value[], d.Value[], 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[], d.Value[], d.Key, d.Value[], "", System.IO.Path.GetFileName(filePath), d.Value[], abnull, Gender);
passCount++;
continue;
} if (isExcel)
{
DataRow r = dtNew.NewRow();
r["员工编号"] = d.Key;
r["姓名"] = d.Value[];
r["A卡"] = d.Value[];
r["B卡"] = d.Value[];
r["开通餐卡"] = d.Value[];
r["备注"] = remark; dtNew.Rows.Add(r);
}
else
{
string gNum = "";
if (d.Value[].Trim().ToUpper().Equals("A"))
gNum = d.Value[];
else
gNum = d.Value[]; 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[], d.Value[], d.Key, d.Value[], gNum, System.IO.Path.GetFileName(filePath), d.Value[], Gender);
passCount++;
}
} int ucount = ;
int.TryParse(userCount, out ucount);
if (dtNew.Rows.Count > )
{
//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 - ).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 < )
return "";
return dt.Rows[][] == null ? "" : dt.Rows[][].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[];
mysheet.Activate();
//get activate sheet max row count
int maxrow = mysheet.UsedRange.Rows.Count + ;
for (int i = maxrow; i < dt.Rows.Count+maxrow; i++)
{
mysheet.Cells[i, ] = dt.Rows[i-maxrow][];
mysheet.Cells[i, ] = dt.Rows[i - maxrow][];
mysheet.Cells[i, ] = dt.Rows[i - maxrow][];
mysheet.Cells[i, ] = dt.Rows[i - maxrow][];
mysheet.Cells[i, ] = dt.Rows[i - maxrow][];
mysheet.Cells[i, ] = dt.Rows[i - maxrow][];
} 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 < )
return null;
dt = DS.Tables[];
}
return dt;
}
这种方式的话，导入一个excel，进程里就多了一个excel进程，必须kill掉，找到进程ID再kill
public class KillExcel
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary>
/// 强制关闭当前Excel进程
/// </summary>
public static void Kill(IntPtr intPtr)
{
try
{
Process[] ps = Process.GetProcesses();
int ExcelID = ;
GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k
foreach (Process p in ps)
{
if (p.ProcessName.ToLower().Equals("excel"))
{
if (p.Id == ExcelID)
{
p.Kill();
}
}
}
}
catch
{
//不做任何处理
}
}
}