本文實(shí)例講述了C#使用NPOI導(dǎo)入Excel的方法。分享給大家供大家參考，具體如下：
NPOI是由國(guó)人開(kāi)發(fā)的一個(gè)進(jìn)行excel操作的第三方庫(kù)。百度百科介紹如下：NPOI
本文主要介紹如何使用NPOI將Excel數(shù)據(jù)讀取。
首先引入程序集：
using System.IO;using System.Reflection;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Web;
然后定位到文件位置：
string path = "~/上傳文件/custompersonsalary/" + id + "/"+id+".xls";string filePath = Server.MapPath(path);FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite) //打開(kāi).xls文件
接下來(lái)，將xls文件中的數(shù)據(jù)寫(xiě)入workbook中：
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的數(shù)據(jù)寫(xiě)入wk中
wk.NumberOfSheets是xls文件中總共的表的個(gè)數(shù)。
wk.GetSheetAt(i)是獲取第i個(gè)表的數(shù)據(jù)。
通過(guò)循環(huán)：
for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是xls文件中總共的表數(shù)
將每個(gè)表的數(shù)據(jù)單獨(dú)存放在ISheet對(duì)象中：
ISheet sheet = wk.GetSheetAt(i); //讀取當(dāng)前表數(shù)據(jù)
這樣某張表的數(shù)據(jù)就暫存在sheet對(duì)象中了。
接下來(lái)，我們可以通過(guò)sheet.LastRowNum來(lái)獲取行數(shù)，sheet.GetRow(j)來(lái)獲取第j行數(shù)據(jù)：
for (j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是當(dāng)前表的總行數(shù){ IRow row = sheet.GetRow(j); //讀取當(dāng)前行數(shù)據(jù)每一行的數(shù)據(jù)又存在IRow對(duì)象中。
我們可以通過(guò)row.LastCellNum來(lái)獲取列數(shù)，row.Cells[i]來(lái)獲取第i列數(shù)據(jù)。
row.Cells[2].ToString();
這里需要注意一點(diǎn)的就是，如果單元格中數(shù)據(jù)為公式計(jì)算而出的話，row.Cells[i]會(huì)返回公式，需要改為:
row.Cells[2].NumericCellValue
就可以返回計(jì)算結(jié)果了。
最后將我在項(xiàng)目中用到的一段導(dǎo)入Excel數(shù)據(jù)賦予實(shí)體的示例如下：
/// <summary>/// 導(dǎo)入操作/// </summary>/// @author: 劉放/// @date: 2015/10/17/// <param name="id">主表id</param>/// <returns>如果成功，返回ok，如果失敗，返回不滿足格式的姓名</returns>public string InDB(string id){ int j=0; StringBuilder sbr = new StringBuilder(); string path = "~/上傳文件/custompersonsalary/" + id + "/"+id+".xls"; string filePath = Server.MapPath(path); using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)) //打開(kāi)123.xls文件 { //定義一個(gè)工資詳細(xì)集合 List<HR_StaffWage_Details> staffWageList = new List<HR_StaffWage_Details>(); try { HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的數(shù)據(jù)寫(xiě)入wk中 for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是xls文件中總共的表數(shù) { ISheet sheet = wk.GetSheetAt(i); //讀取當(dāng)前表數(shù)據(jù) for (j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是當(dāng)前表的總行數(shù) { IRow row = sheet.GetRow(j); //讀取當(dāng)前行數(shù)據(jù) if (row != null) { //for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是當(dāng)前行的總列數(shù) //{ //如果某一行的員工姓名，部門，崗位和員工信息表不對(duì)應(yīng)，退出。 SysEntities db = new SysEntities(); if (CommonHelp.IsInHR_StaffInfo(db, row.Cells[2].ToString(), row.Cells[0].ToString(), row.Cells[1].ToString()) == false)//姓名，部門，崗位 { //返回名字以便提示 return row.Cells[2].ToString(); } //如果符合要求，這將值放入集合中。 HR_StaffWage_Details hr_sw = new HR_StaffWage_Details(); hr_sw.Id = Result.GetNewIdForNum("HR_StaffWage_Details");//生成編號(hào) hr_sw.SW_D_Name = row.Cells[2].ToString();//姓名 hr_sw.SW_D_Department = row.Cells[0].ToString();//部門 hr_sw.SW_D_Position = row.Cells[1].ToString();//職位 hr_sw.SW_D_ManHour = row.Cells[3].ToString() != "" ? Convert.ToDouble(row.Cells[3].ToString()) : 0;//工數(shù) hr_sw.SW_D_PostWage = row.Cells[4].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[4].NumericCellValue.ToString()) : 0;//基本工資 hr_sw.SW_D_RealPostWage = row.Cells[5].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[5].NumericCellValue.ToString()) : 0;//崗位工資 hr_sw.SW_D_PieceWage = row.Cells[6].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[6].NumericCellValue.ToString()) : 0;//計(jì)件工資 hr_sw.SW_D_OvertimePay = row.Cells[7].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[7].NumericCellValue.ToString()) : 0;//加班工資 hr_sw.SW_D_YearWage = row.Cells[8].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[8].NumericCellValue.ToString()) : 0;//年假工資 hr_sw.SW_D_MiddleShift = row.Cells[9].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[9].NumericCellValue.ToString()) : 0;//中班 hr_sw.SW_D_NightShift = row.Cells[10].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[10].NumericCellValue.ToString()) : 0;//夜班 hr_sw.SW_D_MedicalAid = row.Cells[11].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[11].NumericCellValue.ToString()) : 0;//醫(yī)補(bǔ) hr_sw.SW_D_DustFee = row.Cells[12].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[12].NumericCellValue.ToString()) : 0;//防塵費(fèi) hr_sw.SW_D_Other = row.Cells[13].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[13].NumericCellValue.ToString()) : 0;//其他 hr_sw.SW_D_Allowance = row.Cells[14].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[14].NumericCellValue.ToString()) : 0;//津貼 hr_sw.SW_D_Heat = row.Cells[15].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[15].NumericCellValue.ToString()) : 0;//防暑費(fèi) hr_sw.SW_D_Wash = row.Cells[16].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[16].NumericCellValue.ToString()) : 0;//澡費(fèi) hr_sw.SW_D_Subsidy = row.Cells[17].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[17].NumericCellValue.ToString()) : 0;//補(bǔ)助 hr_sw.SW_D_Bonus = row.Cells[18].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[18].NumericCellValue.ToString()) : 0;//獎(jiǎng)金 hr_sw.SW_D_Fine = row.Cells[19].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[19].NumericCellValue.ToString()) : 0;//罰款 hr_sw.SW_D_Insurance = row.Cells[20].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[20].NumericCellValue.ToString()) : 0;//養(yǎng)老保險(xiǎn) hr_sw.SW_D_MedicalInsurance = row.Cells[21].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[21].NumericCellValue.ToString()) : 0;//醫(yī)療保險(xiǎn) hr_sw.SW_D_Lunch = row.Cells[22].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[22].NumericCellValue.ToString()) : 0;//餐費(fèi) hr_sw.SW_D_DeLunch = row.Cells[23].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[23].NumericCellValue.ToString()) : 0;//扣餐費(fèi) hr_sw.SW_D_De = row.Cells[24].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[24].NumericCellValue.ToString()) : 0;//扣項(xiàng) hr_sw.SW_D_Week = row.Cells[25].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[25].NumericCellValue.ToString()) : 0;//星期 hr_sw.SW_D_Duplex = row.Cells[26].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[26].NumericCellValue.ToString()) : 0;//雙工 hr_sw.SW_D_ShouldWage = row.Cells[27].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[27].NumericCellValue.ToString()) : 0;//應(yīng)發(fā)金額 hr_sw.SW_D_IncomeTax = row.Cells[28].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[28].NumericCellValue.ToString()) : 0;//所得稅 hr_sw.SW_D_FinalWage = row.Cells[29].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[29].NumericCellValue.ToString()) : 0;//實(shí)發(fā)金額 hr_sw.SW_D_Remark = row.Cells[30].ToString();//備注 hr_sw.SW_Id = id;//外鍵 hr_sw.SW_D_WageType = null;//工資類型 staffWageList.Add(hr_sw); } } } } catch (Exception e) { //錯(cuò)誤定位 int k = j; } //再將list轉(zhuǎn)入數(shù)據(jù)庫(kù) double allFinalWage = 0; foreach (HR_StaffWage_Details item in staffWageList) { SysEntities db = new SysEntities(); db.AddToHR_StaffWage_Details(item); db.SaveChanges(); allFinalWage +=Convert.ToDouble(item.SW_D_FinalWage); } //將總計(jì)賦予主表 SysEntities dbt = new SysEntities(); HR_StaffWage sw = CommonHelp.GetHR_StaffWageById(dbt, id); sw.SW_WageSum = Math.Round(allFinalWage,2); dbt.SaveChanges(); } sbr.ToString(); return "OK";}最后需要注意一點(diǎn)的就是，Excel中即使某些單元格內(nèi)容為空，但是其依舊占據(jù)了一個(gè)位置，所以在操作的時(shí)候需要格外注意。
希望本文所述對(duì)大家C#程序設(shè)計(jì)有所幫助。
新聞熱點(diǎn)
疑難解答
圖片精選
網(wǎng)友關(guān)注
