.Net NPOI 上傳excel文件、提交後臺獲取excel里的數據

1、導入NPOI.dll

2、添加類NPOIExcel.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

public class NPOIExcel
{
    internal IWorkbook Book { get; set; }

    private int sheetID = 0;
    /// <summary>
    /// 當前活動的SheetID，所有的操作將指向這個Sheet
    /// </summary>
    public int ActiveSheetID
    {
        get
        {
            return sheetID;
        }
        set
        {
            sheetID = value;
        }
    }

    /// <summary>
    /// 當前活動的SheetName，所有的操作將指向這個Sheet
    /// </summary>
    public string ActiveSheetName
    {
        get
        {
            return Book.GetSheetAt(sheetID).SheetName;
        }
        set
        {
            sheetID = Book.GetSheetIndex(value);
        }
    }
    /// <summary>
    /// 當前活動的Sheet，所有的操作將指向這個Sheet
    /// </summary>
    public ISheet ActiveSheet
    {
        get
        {
            return Book.GetSheetAt(sheetID);
        }
    }

    /// <summary>
    /// 第一行非空行的行號
    /// </summary>
    public int FirstRowNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).FirstRowNum;
        }
    }

    /// <summary>
    /// 最後一行非空行的行號
    /// </summary>
    public int LastRostNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).LastRowNum;
        }
    }

    /// <summary>
    /// 無模板的Excel生成或操作
    /// </summary>
    public NPOIExcel()
    {
        Book = new HSSFWorkbook();
        Book.CreateSheet();
    }

    public NPOIExcel(Stream fileStream, string fileName)
    {
        if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
        {
            Book = new HSSFWorkbook(fileStream);
        }
        else
        {
            Book = new XSSFWorkbook(fileStream);
        }
    }
    /// <summary>
    /// 帶模板或數據的Excel生成或操作
    /// </summary>
    /// <param name="fileName"></param>
    public NPOIExcel(string fileName)
    {
        Book = CreateBook(fileName);
    }

    /// <summary>
    /// 創建Excel Book
    /// </summary>
    /// <param name="fileName">模板文件名</param>
    /// <returns></returns>
    private IWorkbook CreateBook(string fileName)
    {
        FileInfo file = new FileInfo(fileName);
        if (!file.Exists)
        {
            File.Create(fileName).Close();
        }
        FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        IWorkbook book;
        if (file.Extension == ".xls")
        {
            book = new HSSFWorkbook(fs);
        }
        else
        {
            book = new XSSFWorkbook(fs);
        }
        fs.Close();
        if (book.NumberOfSheets == 0)
        {
            book.CreateSheet();
        }
        return book;
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet()
    {
        return Book.CreateSheet();
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <param name="sheetName">新建Sheet的名稱</param>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet(string sheetName)
    {
        return Book.CreateSheet(sheetName);
    }

    /// <summary>
    /// 設置行高
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    /// <param name="height">高度</param>
    public void SetRowHeight(int rowIndex, float height)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.Height = (short)(height * 20);
    }

    /// <summary>
    /// 設置列寬
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="columnIndex">列號</param>
    /// <param name="width">寬度</param>
    public void SetColumnWidth(int columnIndex, short width)
    {
        Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * 256);
    }

    /// <summary>
    /// 獲取或設置預設行高
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    public short DefaultRowHeight
    {
        get
        {
            return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / 20);
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultRowHeight = value * 20;
        }
    }

    /// <summary>
    /// 獲取或設置預設列寬
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    public int DefaultColWidth
    {
        get
        {
            return Book.GetSheetAt(sheetID).DefaultColumnWidth;
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
        }
    }

    /// <summary>
    /// 某一列的列寬自動調整大小
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="colIndex">列號</param>
    public void AutoColWidth(int colIndex)
    {
        Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
    }

    /// <summary>
    /// 隱藏一行
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    public void HiddenRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.ZeroHeight = true;
    }

    /// <summary>
    /// 刪除一行
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    public void RemoveRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row != null)
        {
            ActiveSheet.RemoveRow(row);
        }
    }

    /// <summary>
    /// 讀取單元格的值
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    /// <param name="columnIndex">列號</param>
    /// <returns>單元格的值</returns>
    public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 設置單元格的值
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    /// <param name="columnIndex">列號</param>
    /// <param name="value">單元格的值</param>
    public void SetValue(int rowIndex, int columnIndex, object value)
    {
        SetValue(rowIndex, columnIndex, value, false);
    }

    /// <summary>
    /// 設置單元格的值
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    /// <param name="columnIndex">列號</param>
    /// <param name="value">單元格的值</param>
    /// <param name="isFormula">是否是公式</param>
    public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        if (value == null)
        {
            cell.SetCellValue("");
        }
        if (isFormula)
        {
            cell.SetCellFormula(value.ToString());
        }
        else
        {
            if (value is short)
            {
                cell.SetCellValue((short)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            else if (value is long)
            {
                cell.SetCellValue((long)value);
            }
            else if (value is float)
            {
                cell.SetCellValue((float)value);
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value == null)
            {
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="type">驗證類型</param>
    /// <param name="operatorType">驗證方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="type">驗證類型</param>
    /// <param name="operatorType">驗證方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">數據格式</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="type">驗證類型</param>
    /// <param name="operatorType">驗證方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">數據格式</param>
    /// <param name="AlertMassage">報錯信息</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
    {
        CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
        DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, 0, null, null);
        switch (type)
        {
            case NPOIDataType.Integer:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Float:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Date:
                if (formate == "")
                {
                    formate = "yyyy/MM/dd";
                }
                constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
                break;
            case NPOIDataType.Time:
                constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.TextLength:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
                break;
            default:
                break;
        }

        HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
        if (!string.IsNullOrEmpty(AlertMassage))
        {
            dataValidate1.CreateErrorBox("Error", AlertMassage);
        }
        ActiveSheet.AddValidationData(dataValidate1);

    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="dataRange">值系列</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
    {

        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="dataRange">值系列</param>
    /// <param name="alertMassage">報錯信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="formula">計算公式</param>
    /// <param name="alertMassage">報錯信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 設置一個區域內的單元格的值範圍
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行</param>
    /// <param name="EndRowIndex">結束行</param>
    /// <param name="startColInex">開始列</param>
    /// <param name="endColIndex">結束列</param>
    /// <param name="formula">計算公式</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
    {
        SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
    }

    /// <summary>
    /// 生成單元格樣式
    /// </summary>
    /// <returns>與當前Excel相關的單元格樣式</returns>
    public ICellStyle CreateCellStyle()
    {
        return Book.CreateCellStyle();
    }

    /// <summary>
    /// 生成字體
    /// </summary>
    /// <returns>與當前Excel相關的字體</returns>
    public IFont CreateFont()
    {
        return Book.CreateFont();
    }

    /// <summary>
    /// 設置單元格樣式
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行號</param>
    /// <param name="columnIndex">列號</param>
    /// <param name="style">樣式</param>
    public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        cell.CellStyle = style;
    }

    /// <summary>
    /// 合併單元格
    /// 註：只對當前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">開始行號</param>
    /// <param name="startColumnIndex">