NPOI操作Exce

操作ExceL

    public class ExcelHelper
    {
        /// <summary>
        /// 将excel转化为DataTable
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(FileStream stream)
        {
            IWorkbook workbook = WorkbookFactory.Create(stream);

            var sheet1 = workbook.GetSheetAt(0);//获取第一个sheet

            DataTable table = new DataTable();
            var rowHead = sheet1.GetRow(0);//获取第一行即标头
            int cellCount = rowHead.LastCellNum; //第一行的列数
            //把第一行的数据添加到datatable的列名
            for (int i = rowHead.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(rowHead.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet1.LastRowNum; //总行数

            //把每行数据添加到datatable中
            for (int i = (sheet1.FirstRowNum + 1); i < sheet1.LastRowNum + 1; i++)
            {
                IRow row = sheet1.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }

                table.Rows.Add(dataRow);
            }
            workbook = null; //清空工作簿--释放资源
            sheet1 = null;  //清空sheet
            return table;

        }

    }

/// <summary>
        /// 修改Excel
        /// </summary>
        /// <param name="model">要修改的实体</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        /// <returns></returns>
        public int Update(EditObject model, int coluid, int rowid)
        {

            int result = 1;

            var workbook = GetIWorkbook();
            if (workbook == null)
            {
                return 0;
            }

         
                var sheet1 = workbook.GetSheetAt(0);

                Type type = model.GetType();
                PropertyInfo[] p = type.GetProperties();

                for (int i = 0; i < p.Length; i++)
                {
                    if (sheet1.GetRow(rowid + 1) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(rowid + 1).GetCell(i) == null)
                    {
                        sheet1.GetRow(rowid + 1).CreateCell(i);
                    }

                    if (sheet1.GetRow(rowid + 1).GetCell(i) != null)
                    {
                        if (p[i].GetValue(model, null) != null)
                        {
                            sheet1.GetRow(rowid + 1).GetCell(i).SetCellValue(p[i].GetValue(model, null).ToString());
                        }

                    }

                }
            }