C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式(3)

当前位置:

首页 > Python基础教程 >

C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式(3)

{ if (cellValue is decimal || cellValue is double || cellValue is int) { cell.SetCellValue(Convert.ToDouble(cellValue)); } else if (cellValue is DateTime) { cell.SetCellValue((DateTime)cellValue); } else if (cellValue is bool) { cell.SetCellValue((bool)cellValue); } else { cell.SetCellValue(cellValue.ToString()); } } } } } public static void SetDeepUpdateCellValue(ISheet sheet, int rowIndex, int columnIndex, object cellValue, string outputFormat, bool isCoordinateExpress, List<IExcelCellPointDeepUpdate> excelDeepUpdateList) { if (sheet != null) { //更新起始单元格数据 ICell nextCell = ExcelHelper.GetOrCreateCell(sheet, rowIndex, columnIndex); CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress); #region 执行单元格深度更新策略 ICellModel startCellPosition = new CellModel { RowIndex = rowIndex, ColumnIndex = columnIndex }; ICellModel nextCellPosition = null; Action<IExcelCellPointDeepUpdate> actionDeepUpdateAction = (excelDeepUpdate) => { //获取起始执行单元格位置 nextCellPosition = excelDeepUpdate.GetNextCellPoint(startCellPosition); //执行深度更新，一直到找不到下个单元格为止 do { nextCell = ExcelHelper.GetOrCreateCell(sheet, nextCellPosition.RowIndex, nextCellPosition.ColumnIndex); if (nextCell != null) { CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress); nextCellPosition = excelDeepUpdate.GetNextCellPoint(nextCellPosition); } } while (nextCell != null); }; foreach (var excelDeepUpdate in excelDeepUpdateList) { actionDeepUpdateAction(excelDeepUpdate); } #endregion } } /// <summary> /// 数字转字母 /// </summary> /// <param name="columnIndex"></param> /// <returns></returns> public static string GetExcelColumnPosition(int number) { var a = number / 26; var b = number % 26; if (a > 0) { return CellFactory.GetExcelColumnPosition(a - 1) + (char)(b + 65); } else { return ((char)(b + 65)).ToString(); } } /// <summary> /// 字母转数字 /// </summary> /// <param name="columnPosition"></param> /// <returns></returns> public static int GetExcelColumnIndex(string zm) { int index = 0; char[] chars = zm.ToUpper().ToCharArray(); for (int i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1); } return index - 1; } }

　　b-CellModel 单元格定义类

				
public class CellModel : ICellModel
    {
        public int RowIndex { get; set; }
        public int ColumnIndex { get; set; }
        public object CellValue { get; set; }

        public bool IsCellFormula { get; set; }

        public CellModel() { }

        /// <summary>
        /// 默认初始化对象
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="cellValue"></param>
        public CellModel(int rowIndex, int columnIndex, object cellValue = default(object)) : this(rowIndex, columnIndex, cellValue, false)
        {
        }

        /// <summary>
        /// 默认初始化对象
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="cellValue"></param>
        /// <param name="isCellFormula"></param>
        public CellModel(int rowIndex, int columnIndex, object cellValue, bool isCellFormula)
        {
            this.RowIndex = rowIndex;
            this.ColumnIndex = columnIndex;
            this.CellValue = cellValue;
            this.IsCellFormula = isCellFormula;
        }

        /// <summary>
        /// 获取单元格位置
        /// </summary>
        /// <returns></returns>
        public string GetCellPosition()
        {
            return CellFactory.GetExcelColumnPosition(this.ColumnIndex) + (this.RowIndex + 1).ToString();
        }
    }

    public class CellModelColl : List<CellModel>, IList<CellModel>
    {
        public CellModelColl() { }
        public CellModelColl(int capacity) : base(capacity)
        {

        }

        /// <summary>
        /// 根据行下标，列下标获取单元格数据
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public CellModel this[int rowIndex, int columnIndex]
        {
            get
            {
                CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
                return cell;
            }
            set
            {
                CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
                if (cell != null)
                {
                    cell.CellValue = value.CellValue;
                }
            }
        }

        public CellModel CreateOrGetCell(int rowIndex, int columnIndex)
        {
            CellModel cellModel = this[rowIndex, columnIndex];
            if (cellModel == null)
            {
                cellModel = new CellModel()
                {
                    RowIndex = rowIndex,
                    ColumnIndex = columnIndex
                };
                this.Add(cellModel);
            }
            return cellModel;
        }

        public CellModel GetCell(string cellStringValue)
        {
            CellModel cellModel = null;

            cellModel = this.FirstOrDefault(m => m.CellValue.ToString().Equals(cellStringValue, System.StringComparison.OrdinalIgnoreCase));

            return cellModel;
        }

        /// <summary>
        /// 所有一行所有的单元格数据
        /// </summary>
        /// <param name="rowIndex">行下标</param>
        /// <returns></returns>
        public List<CellModel> GetRawCellList(int rowIndex)
        {
            List<CellModel> cellList = null;
            cellList = this.FindAll(m => m.RowIndex == rowIndex);

            return cellList ?? new List<CellModel>(0);
        }

        /// <summary>
        /// 所有一列所有的单元格数据
        /// </summary>
        /// <param name="columnIndex">列下标</param>
        /// <returns></returns>
        public List<CellModel> GetColumnCellList(int columnIndex)
        {
            List<CellModel> cellList = null;
            cellList = this.FindAll(m => m.ColumnIndex == columnIndex);

            return cellList ?? new List<CellModel>(0);
        }

    }
				

　　c-ExcelCellExpressDeepUpdate<T> 单元格表达式深度更新类

				
public class ExcelCellExpressDeepUpdate<T> : IExcelCellExpressDeepUpdate<T>
        {
            private Regex cellPointRegex = new Regex("[A-Z]+[0-9]+");

            private Action<ICellModel> updateCellPointFunc { get; set; }
            public Func<T, bool> CheckContinuteFunc { get; set; }

            public ExcelCellExpressDeepUpdate(Action<ICellModel> updateCellPointFunc, Func<T, bool> checkIsContinuteFunc)
            {
                this.updateCellPointFunc = updateCellPointFunc;
                this.CheckContinuteFunc = checkIsContinuteFunc;
            }

            public bool IsContinute(T t)
            {
                return this.CheckContinuteFunc(t);
            }

            public string GetNextCellExpress(string currentExpress)
            {
                string nextCellExpress = currentExpress;

                List<ICellModel> cellModelList = this.GetCellModelList(currentExpress);
                string oldPointStr = null;
                string newPointStr = null;
                foreach (var item in cellModelList)
                {
                    oldPointStr = item.GetCellPosition();
                    this.updateCellPointFunc(item);
                    newPointStr = item.GetCellPosition();

                    nextCellExpress = nextCellExpress.Replace(oldPointStr, newPointStr);
                }
                return nextCellExpress;
            }


            private List<ICellModel> GetCellModelList(string cellExpress)
            {
                List<ICellModel> cellModelList = new List<ICellModel>(100);
                MatchCollection matchCollection = this.cellPointRegex.Matches(cellExpress);

                foreach (Match matchItem in matchCollection)
                {
                    cellModelList.Add(CellFactory.GetCellByExcelPosition(matchItem.Value));
                }
                return cellModelList;
            }

        }
				

　　d-ExcelCellPointDeepUpdate 单元格坐标深度更新类

				
public class ExcelCellPointDeepUpdate : IExcelCellPointDeepUpdate
    {
        private Action<ICellModel> updateCellPointFunc { get; set; }


        public ExcelCellPointDeepUpdate(Action<ICellModel> updateCellPointFunc)
        {
            this.updateCellPointFunc = updateCellPointFunc;
        }

        public ICellModel GetNextCellPoint(ICellModel cellModel)
        {
            ICellModel nextCell = null;

            ICellModel cell = new CellModel(cellModel.RowIndex, cellModel.ColumnIndex);
            if (cellModel != null && this.updateCellPointFunc != null)
            {
                this.updateCellPointFunc(cell);
                if (cell.RowIndex != cellModel.RowIndex || cell.ColumnIndex != cellModel.ColumnIndex)
                {
                    nextCell = cell;
                }
            }

            return nextCell;
        }

    }
				

　　e-ICellModel 单元格抽象接口

				
public interface ICellModel
    {
        int RowIndex { get; set; }
        int ColumnIndex { get; set; }
        object CellValue { get; set; }

        bool IsCellFormula { get; set; }

        string GetCellPosition();

    }
				

　　f-IExcelCellDeepUpdate 单元格深度更新接口

			/// <summary>
    /// 单元格深度更新接口
    /// </summary>
    public interface IExcelCellDeepUpdate : IExcelDeepUpdate
    {
    }
		

　　g-IExcelCellExpressDeepUpdate<T> 单元格表达式深度更新接口

			public interface IExcelCellExpressDeepUpdate<T> : IExcelCellDeepUpdate
    {
        string GetNextCellExpress(string currentExpress);
        bool IsContinute(T t);

    }
		

　　h-IExcelCellPointDeepUpdate 单元格坐标深度更新接口

				
/// <summary>
    /// 单元格坐标深度更新接口
    /// </summary>
    public interface IExcelCellPointDeepUpdate : IExcelCellDeepUpdate
    {
        ICellModel GetNextCellPoint(ICellModel cellModel);
    }
				

　　i-IExcelDeepUpdate Excel深度更新大抽象接口

			/// <summary>
    /// Excel深度更新策略接口
    /// </summary>
    public interface IExcelDeepUpdate
    {
    }
		

　　j-IExcelTitleDeepUpdate Excel标题深度更新接口

			/// <summary>
    /// Excel标题深度更新策略
    /// </summary>
    public interface IExcelTitleDeepUpdate : IExcelDeepUpdate
    {
    }
		

　　深度更新使用示例一：

string path = @"C:\Users\Administrator\Desktop\控制台测试\Test\WebApplication1\WebApplication1\2020年2月 paypal凭证.xlsx";
            ExcelFileDescription excelFileDescription = new ExcelFileDescription(new ExcelCellExpressDeepUpdate<AccountMultiCurrencyTransactionSource_Summary>(m => m.RowIndex += 15, m => m.BeginingBalance > 0));
            IWorkbook workbook = ExcelHelper.GetExcelWorkbook(path);
            ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: "chictoo+7");
            List<AccountMultiCurrencyTransactionSource_Summary> dataList = ExcelHelper.ReadCellData<AccountMultiCurrencyTransactionSource_Summary>(workbook, sheet, excelFileDescription);

/// <summary>
    /// 账户_多币种交易报表_数据源
    /// </summary>
    public class AccountMultiCurrencyTransactionSource_Summary
    {
        [ExcelCellExpressRead("A2")]
        public string AccountName { get; set; }

        /// <summary>
        /// 期初
        /// </summary>
        [ExcelCellExpressReadAttribute("B3")]
        public double BeginingBalance { get; set; }
/// <summary>
        /// 收款
        /// </summary>
        [ExcelCellExpressReadAttribute("B4")]
        [ExcelTitle(3)]
        public double TotalTransactionPrice { get; set; }
}

　　总结：时间有限，没有来得及进行深度的抽象和优化，优化有机会再继续吧。　

/// <summary>
    /// 账户_多币种交易报表_数据源
    /// </summary>
    public class AccountMultiCurrencyTransactionSource_Summary
    {
        [ExcelCellExpressRead("A2")]
        public string AccountName { get; set; }

        /// <summary>
        /// 期初
        /// </summary>
        [ExcelCellExpressReadAttribute("B3")]
        public double BeginingBalance { get; set; }
/// <summary>
        /// 收款
        /// </summary>
        [ExcelCellExpressReadAttribute("B4")]
        [ExcelTitle(3)]
        public double TotalTransactionPrice { get; set; }
}

*感谢您的阅读。喜欢的、有用的就请大哥大嫂们高抬贵手“推荐一下”吧！你的精神支持是博主强大的写作动力。欢迎转载！
*博主的文章是自己平时开发总结的经验，由于博主的水平不高，不足和错误之处在所难免，希望大家能够批评指出。