Of Stack

Detailed Explanation of C Using NPOI to Import Excel

  • 2021-12-11 18:42:40
  • OfStack

This article illustrates how C # uses NPOI to import Excel. Share it for your reference, as follows:

NPOI is a third library developed by Chinese for excel operation. Baidu Encyclopedia is introduced as follows: NPOI

This article mainly introduces how to use NPOI to read Excel data.

First, introduce the assembly: 


using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Web;

Then navigate to the file location: 


string path = "~/ Upload a file /custompersonsalary/" + id + "/"+id+".xls";
string filePath = Server.MapPath(path);
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite) // Open .xls Documents

Next, write the data from the xls file to workbook: 


HSSFWorkbook wk = new HSSFWorkbook(fs); // Put xls Write data in a file wk Medium

wk.NumberOfSheets Is the total number of tables in the xls file.

wk.GetSheetAt(i) Is to get the data of the i table.

Through a loop: 


for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets Yes xls Total number of tables in the file

Store the data of each table separately in ISheet Object: 


ISheet sheet = wk.GetSheetAt(i); // Read the current table data

In this way, the data of a certain table exists temporarily sheet Object.
Next, we can pass sheet.LastRowNum To get the number of rows, sheet.GetRow(j) To get the data on line j: 


for (j = 1; j <= sheet.LastRowNum; j++) //LastRowNum  Is the total number of rows in the current table 
{
  IRow row = sheet.GetRow(j); // Read the current row data

Every 1 row of data exists in the IRow object.

We can pass row.LastCellNum To get the number of columns, row.Cells[i] To get the i column data. 


row.Cells[2].ToString();

One thing to note here is that if the data in the cell is calculated by formula, row.Cells[i] The formula will be returned, which needs to be changed to: 


row.Cells[2].NumericCellValue

You can return the calculation results.

Finally, an example of importing Excel data into an entity that I used in my project is as follows: 


/// <summary>
/// 导入操作
/// </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)) //打开123.xls文件
 {
  //定义1个工资详细集合
  List<HR_StaffWage_Details> staffWageList = new List<HR_StaffWage_Details>();
  try
  {
   HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
   for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是xls文件中总共的表数
   {
    ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
    for (j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
    {
     IRow row = sheet.GetRow(j); //读取当前行数据
     if (row != null)
     {
      //for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
      //{
      //如果某1行的员工姓名，部门，岗位和员工信息表不对应，退出。
      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");//生成编号
      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;//工数
      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;//计件工资
      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;//医补
      hr_sw.SW_D_DustFee = row.Cells[12].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[12].NumericCellValue.ToString()) : 0;//防尘费
      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;//防暑费
      hr_sw.SW_D_Wash = row.Cells[16].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[16].NumericCellValue.ToString()) : 0;//澡费
      hr_sw.SW_D_Subsidy = row.Cells[17].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[17].NumericCellValue.ToString()) : 0;//补助
      hr_sw.SW_D_Bonus = row.Cells[18].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[18].NumericCellValue.ToString()) : 0;//奖金
      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;//养老保险
      hr_sw.SW_D_MedicalInsurance = row.Cells[21].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[21].NumericCellValue.ToString()) : 0;//医疗保险
      hr_sw.SW_D_Lunch = row.Cells[22].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[22].NumericCellValue.ToString()) : 0;//餐费
      hr_sw.SW_D_DeLunch = row.Cells[23].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[23].NumericCellValue.ToString()) : 0;//扣餐费
      hr_sw.SW_D_De = row.Cells[24].NumericCellValue.ToString() != "" ? Convert.ToDouble(row.Cells[24].NumericCellValue.ToString()) : 0;//扣项
      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;//应发金额
      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;//实发金额
      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) {
   //错误定位
   int k = j;
  }
  //再将list转入数据库
  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);
  }
  //将总计赋予主表
  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";
}

Finally, one point to note is that even if some cells in Excel are empty, they still occupy one position, so special attention should be paid when operating.

For more readers interested in C # related content, please check the topics on this site: "Summary of C # Operating Excel Skills", "Summary of C # Programming Thread Use Skills", "Summary of XML File Operation Skills in C #", "C # Common Control Usage Tutorial", "WinForm Control Usage Summary", "C # Data Structure and Algorithm Tutorial", "C # Array Operation Skills Summary" and "C # Object-Oriented Programming Introduction Tutorial"

I hope this article is helpful to everyone's C # programming.

Related articles: