C# Excel操作NPOI

EXCEL导入

• 接口ExcelImport
• ExcelToDataTable
• 去除datatable中的空行RemoveEmpty

EXCEL导出 (请求方式必须为表单提交)

• 接口ExportPlantData(Void)
• 接口ExportPlantData
• 新建类 重写Npoi流方法

EXCEL导入

接口ExcelImport

		/// <summary>
        ///导入Excel  先将文件保存到服务器本地 然后访问服务器文件地址，获取文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<Result> ExcelImport()
        {
            var request = HttpContext.Current.Request;
            var tableName=request.Params["TableName"];
            var server = HttpContext.Current.Server;
            HttpPostedFile postFile;
            if (request.Files.Count > 0)
            {
                //单文件上传，多文件for
                postFile = request.Files[0];
            }
            else
            {
                return Result.Failed("无文件");
            }
            try
            {
                var filePath = server.MapPath("~/UpLoad/");
                if (!Directory.Exists(filePath))
                {
                    Directory.CreateDirectory(filePath);
                }
                var fileName = Path.GetFileName(postFile.FileName);
                var fileExtention = Path.GetExtension(fileName);
                if (fileExtention != ".xls" && fileExtention != ".xlsx")
                {
                    return Result.Failed("文件格式非.xls 或 .xlsx");
                }
                var saveFileName = filePath + Guid.NewGuid() + fileExtention;
                postFile.SaveAs(saveFileName);
                _deleteFile = saveFileName;
                var excelToList = await _service.ExcelToList(tableName, saveFileName);
                if (excelToList == null || excelToList.Count<1 || excelToList[0] == null || excelToList[0].Count < 1)
                    return Result.Failed("文件为空");
                return await _service.ExcelImport(tableName, excelToList) ? Result.Successed : Result.Failed("操作失败");
            }
            catch (Exception ex)
            {
                return Result.Failed(ex.ToString());
            }
            finally
            {
                if (!string.IsNullOrEmpty(_deleteFile))
                    File.Delete(_deleteFile);
            }
        }

ExcelToDataTable

    /// </summary>
    /// <param name="filePath">Excel位置</param>
    /// <param name="tableName">Table名字</param>
    /// <param name="sheetIndex">sheet索引</param>
    /// <returns></returns>
    public static Task<DataTable> ExcelToDataTable(string filePath, string tableName, int sheetIndex)
    {	
        return Task.Run(() =>
        {
            DataTable fileToTable = new DataTable();
            if (!string.IsNullOrEmpty(tableName))
            {
                fileToTable.TableName = tableName;
            }
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workBook = null;
                try {
                    workBook = new XSSFWorkbook(file);
                }
                catch {
                    workBook = new HSSFWorkbook(file);
                }
                ISheet sheet = workBook.GetSheetAt(sheetIndex);
                //列头
                foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                {
                    fileToTable.Columns.Add(item.ToString(), typeof(string));
                }
                //写入内容
                System.Colle