NDBGJK5.0/Kingo.Plugin.YJJK/ModelEntity/CellHelper.cs

using Aspose.Cells;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.ExtensionMethod;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading;

namespace Kingo.Plugin.YJJK.ModelEntity
{
    public static class CellHelper
    {
        public static object Calculate(this Cell cell)
        {
            Worksheet sheet = null;
            Cells cells = null;
            object result = null;
            try
            {
                if (cell == null) return null;
                sheet = cell.Worksheet;// workbook.Worksheets[0]; //工作表
                cells = sheet.Cells;//单元格

                if (cell.IsFormula)
                {
                    string formula = cell.Formula;
                    double value = 0;
                    if (formula.Contains("SUM"))
                    {
                        formula = formula.Replace("=SUM(", "").Replace(")", "");
                        string[] cellArr = formula.Split(',');
                        foreach (var cellItems in cellArr)
                        {
                            if (string.IsNullOrWhiteSpace(cellItems))
                                continue;
                            string[] cellList = cellItems.Split(':');
                            if (cellList.Length == 1)
                            {
                                value += cells[cellList[0]].Value.ToDouble();
                                continue;
                            }
                            DataTable rangDt = cells.CreateRange(cellList[0], cellList[1]).ExportDataTable();
                            if (rangDt.Rows.Count == 1 && rangDt.Columns.Count > 1)
                            {
                                for (int c = 0; c < rangDt.Columns.Count; c++)
                                {
                                    value += rangDt.Rows[0][c].ToDouble();
                                }
                            }
                            else if (rangDt.Rows.Count > 1 && rangDt.Columns.Count == 1)
                            {
                                for (int r = 0; r < rangDt.Rows.Count; r++)
                                {
                                    value += rangDt.Rows[r][0].ToDouble();
                                }
                            }
                            else if (rangDt.Rows.Count == 1 && rangDt.Columns.Count == 1)
                            {
                                value += rangDt.Rows[0][0].ToDouble();
                            }
                            rangDt.Clear();
                            rangDt.Dispose();
                        }
                    }
                    else if (formula.Contains("+") || formula.Contains("-"))
                    {
                        formula = formula.Replace("=", "");
                        string strform = formula.Replace("+", ",").Replace("-", ",");
                        string[] str2 = strform.Split(',');
                        List<double> values = new List<double>();
                        foreach (var item in str2)
                        {
                            values.Add(cells[item].Value.ToDouble());
                        }
                        List<string> fh = new List<string>();
                        for (int c = 0; c < formula.Length; c++)
                        {
                            if (formula[c] == '+' || formula[c] == '-')
                            {
                                fh.Add(formula[c].ToTrim());
                            }
                        }
                        for (int v = 0; v < values.Count; v++)
                        {
                            if (v == 0)
                                value = values[v];
                            else
                            {
                                if (fh[v - 1] == "+")
                                {
                                    value += values[v];
                                }
                                else if (fh[v - 1] == "-")
                                {
                                    value -= values[v];
                                }
                            }
                        }

                    }
                    cell.Value = value.ToDouble(2);
                }
                else
                {
                    result = cell.Value;
                }
            }
            catch (Exception exc)
            {
                LogAPI.Debug(exc.Message);
                result = cell.Value;
            }
            finally
            {
                GC.Collect();
            }
            return result = cell.Value.ToDouble();
        }

        /// <summary>读取excel
        /// 默认第一行为表头
        /// </summary>
        /// <param name="strFileName">excel文档绝对路径</param>
        /// <param name="rowIndex">rownum</param>
        /// <returns></returns>
        public static void Import(string strFileName)
        {
            Workbook workbook = null;
            Worksheet sheet = null;
            Cells cells = null;
            try
            {
                Thread.Sleep(1500);
                workbook = new Workbook(strFileName); //工作簿
                sheet = workbook.Worksheets[0]; //工作表
                cells = sheet.Cells;//单元格
                Dictionary<string, double> keyValuePairs = GetKeyValuePairs(strFileName);
                foreach (Cell item in cells)
                {
                    if (item.IsFormula)
                    {
                        item.Value = keyValuePairs.Where(p => p.Key == item.Name).Select(p => p.Value).FirstOrDefault();
                    }
                }
                workbook.Save(strFileName);
            }
            catch (Exception exc)
            {
                LogAPI.Debug(exc.Message);
                throw exc;
            }
            finally
            {
                GC.Collect();
            }
        }
        public static Dictionary<string, double> GetKeyValuePairs(string strFileName)
        {
            IWorkbook hssfworkbook;
            List<ICell> cells = null;
            IRow headRow = null;
            Dictionary<string, double> keyValuePairs = new Dictionary<string, double>();
            try
            {
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = WorkbookFactory.Create(file);
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook);
                    for (int i = 0; i < sheet.LastRowNum + 1; i++)
                    {
                        headRow = sheet.GetRow(i);
                        cells = headRow.Cells;
                        foreach (ICell item in cells)
                        {
                            try
                            {
                                if (eva.Evaluate(item) == null)
                                    continue;
                                if (eva.Evaluate(item).CellType == CellType.Numeric || eva.Evaluate(item).CellType == CellType.Formula)
                                {
                                    keyValuePairs.Add(item.Address.ToString(), eva.Evaluate(item).NumberValue);
                                    //item.SetCellValue(eva.Evaluate(item).NumberValue);
                                    //item.SetCellType(CellType.Numeric);
                                }
                                continue;
                            }
                            catch (Exception ex)
                            {
                                throw;
                            }

                        }
                    }
                    //sheet.ForceFormulaRecalculation = true;
                    //FileStream file2 = new FileStream(strFileName, FileMode.Truncate);
                    //hssfworkbook.Write(file2);
                    //hssfworkbook.Close();
                    //file2.Close();
                }
                return keyValuePairs;
            }
            catch (Exception ex)
            {
                LogAPI.Debug(ex.Message);
                throw ex;
            }
        }

        public static void SetFileToDisk(DataTable dataTable, string path, int Worksheetindex)
        {
            Workbook workbook = null;
            Worksheet sheet = null;
            Cells cells = null;
            try
            {
                workbook = new Workbook(path); //工作簿
                sheet = workbook.Worksheets[Worksheetindex]; //工作表
                cells = sheet.Cells;//单元格
                foreach (DataRow row in dataTable.Rows)
                {
                    var x = Convert.ToInt32(row["Cells_X"]);
                    var y = Convert.ToInt32(row["Cells_Y"]);
                    var value = row["BGMJ"].ToDouble();
                    cells[x, y].Value = value;
                }
                workbook.Save(path);
            }
            catch (Exception exc)
            {
                LogAPI.Debug(exc.Message);
                throw exc;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}