初试C#中使用NPOI实现Excel的导入导出

首先需要在项目中安装NPOI支持库，在NuGet中搜索NPOI直接安装即可。

安装图解一

安装图解二

 //导入数据
        public static DataTable Import(string filePath,string sheetName = "")
        {
            var excelType = Path.GetExtension(filePath).ToLower();
            DataTable dt;
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                dt = ImportExcel(stream, excelType, sheetName);
            }
            return dt;
        }
        
        private static DataTable ImportExcel(Stream stream, string type, string sheetName)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook ;
            try
            {
                if (type != ".xls")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }
                ISheet sheet = null;
                //获取工作表 默认取第一张
                if (string.IsNullOrWhiteSpace(sheetName))
                    sheet = workbook.GetSheetAt(0);
                else
                    sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                    return null;
                IEnumerator rows = sheet.GetRowEnumerator();
                #region 获取表头
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null)
                    {
                        dt.Columns.Add(cell.ToString());
                    }
                    else
                    {
                        dt.Columns.Add("");
                    }
                }
                #endregion
                #region 获取内容
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            //判断单元格是否为日期格式
                            if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                if (row.GetCell(j).DateCellValue.Year >= 1970)
                                {
                                    dataRow[j] = row.GetCell(j).DateCellValue.ToString();
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                    }
                    dt.Rows.Add(dataRow);
                }
                #endregion
            }
            catch (Exception ex)
            {
                dt = null;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                    stream.Dispose();
                }
            }
            return dt;
        }

  
//导出数据
protected void ExportExcel(DataTable dt)
        {
            HttpContext curContext = System.Web.HttpContext.Current;
            //设置编码及附件格式 
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string fullName = HttpUtility.UrlEncode("FileName.xlsx", Encoding.UTF8);
            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8));
            //attachment后面是分号
            byte[] data = TableToExcel(dt, fullName).GetBuffer();
            curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer());
            curContext.Response.End();
        }
        public MemoryStream TableToExcel(DataTable dt, string file)
        {
            //创建workbook 
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx")
                workbook = new XSSFWorkbook();
            else if (fileExt == ".xls")
                workbook = new HSSFWorkbook();
            else workbook = null;
            //创建sheet 
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //表头 
            IRow headrow = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell headcell = headrow.CreateCell(i);
                headcell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //表内数据 
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转化为字节数组 
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            //ms.Position = 0;
            return ms;
        }