JLH/JLHHJSvr/Excutor/ImportMtrlPriceByExcelExcutor.cs

using JLHHJSvr.BLL;
using JLHHJSvr.Com;
using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using LJLib.DAL.SQL;
using LJLib.Net.SPI.Server;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JLHHJSvr.Excutor
{
    internal sealed class ImportMtrlPriceByExcelExcutor : ExcutorBase<ImportMtrlPriceByExcelRequest, ImportMtrlPriceByExcelResponse>
    {
        protected override void ExcuteInternal(ImportMtrlPriceByExcelRequest request, object state, ImportMtrlPriceByExcelResponse rslt)
        {
            var tokendata = BllHelper.GetToken(request.token);
            if (tokendata == null)
            {
                throw new LJCommonException("会话已经中断");
            }

            using (var con = new SqlConnection(GlobalVar.ConnectionString))
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                if (!string.IsNullOrEmpty(request.base64))
                {
                    var typeEndIndex = request.base64.IndexOf(";base64", StringComparison.Ordinal);
                    if (typeEndIndex <= 0)
                    {
                        rslt.ErrMsg = "格式异常，请重新上传";
                        return;
                    }
                    request.filedata = Convert.FromBase64String(request.base64.Substring(typeEndIndex + 8));

                }
                if (request.filedata == null || request.filedata.Length == 0)
                {
                    throw new LJCommonException("excel文件不能为空");
                }
                IWorkbook workbook = null;
                using (var ms = new MemoryStream(request.filedata))
                {
                    if (request.filename.ToLower().IndexOf(".xlsx") > 0)
                    {
                        workbook = new XSSFWorkbook(ms);
                    }
                    else if (request.filename.ToLower().IndexOf(".xls") > 0)
                    {
                        workbook = new HSSFWorkbook(ms);
                    }
                    else
                    {
                        throw new LJCommonException("只支持excel类型文件");
                    }
                }
                var sheet = workbook.GetSheetAt(0); //获取第一个工作表
                if (sheet.LastRowNum <= 0)
                {
                    throw new Exception("该表没有数据");
                }
                IRow row;
                row = sheet.GetRow(0);
                if (row == null)
                {
                    throw new Exception("没有数据");
                }

                IRow headerRow = sheet.GetRow(0);
                List<HeaderPropetry> headers = new List<HeaderPropetry>();
                List<HeaderPropetry> priceHeaders = new List<HeaderPropetry>();
                int k = 0;
                foreach (ICell cell in headerRow.Cells)
                {
                    k++;
                    if(cell.ToString() == "类别" || cell.ToString() == "名称")
                    {
                        headers.Add(new HeaderPropetry { name = cell.ToString(), colIndex = k});
                    }
                    //ICellStyle cellStyle = cell.CellStyle;
                    ////if(cellStyle.FillForegroundColorColor)
                    //byte[] rgb;
                    //if (request.filename.ToLower().IndexOf(".xlsx") > 0)
                    //{
                    //    rgb = ((XSSFColor)cell.CellStyle.FillForegroundColorColor).RGB;
                    //}
                    //else 
                    //{
                    //    rgb = ((HSSFColor)cell.CellStyle.FillForegroundColorColor).RGB;
                    //}
                    //if(rgb.Count() == 3 && rgb[0].ToString() == "255" && rgb[1].ToString() == "204" && rgb[2].ToString() == "153")
                    //if (cell.ToString().IndexOf("价格表") > -1)
                    //{
                    cmd.CommandText = "Select isnull(pricelistid,0) From u_pricelist where pricelistname = @pricelistname";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@pricelistname", cell.ToString().Trim());
                    var pricelistid = Convert.ToInt32(cmd.ExecuteScalar());
                    if (pricelistid > 0)
                    {
                        priceHeaders.Add(new HeaderPropetry { name = cell.ToString(), colIndex = k, keyId = pricelistid });
                    }
                        
                    //}
                    //k++;
                }
                if (headers.Count <= 0 || priceHeaders.Count <= 0)
                {
                    rslt.ErrMsg = "excel格式不正确";
                    return;
                }
                using (cmd.Transaction = con.BeginTransaction())
                {
                    try
                    {


                        for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                         {
                            row = sheet.GetRow(rowIndex);
                            if (row == null) continue;

                            var mtrltype = string.Empty;
                            var mtrlname = string.Empty;
                            foreach (var head in headers)
                            {
                                ICell cell = row.GetCell(head.colIndex);
                                if (cell == null || cell.ToString() == "") continue;
                                if (head.name == "类别")
                                {
                                    mtrltype = GetCellValue(cell).ToString();
                                }
                                else if (head.name == "名称")
                                {
                                    mtrlname = GetCellValue(cell).ToString();
                                }
                            }
                            if (!string.IsNullOrEmpty(mtrltype) && !string.IsNullOrEmpty(mtrlname))
                            {

                                foreach (var pricename in priceHeaders)
                                {
                                    var mtrlid = 0;
                                    cmd.CommandText = @"Select isnull(u_mtrl_price.mtrlid,0) from u_mtrl_price 
                            where u_mtrl_price.mtrltype = (select isnull(mtrltypeid,0) from u_mtrltype where mtrltype = @mtrltype)
                            and u_mtrl_price.name = @mtrlname";
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.AddWithValue("@mtrltype", mtrltype);
                                    cmd.Parameters.AddWithValue("@mtrlname", mtrlname);
                                    mtrlid = Convert.ToInt32(cmd.ExecuteScalar());
                                    if (mtrlid == 0)
                                    {
                                        //throw new Exception(string.Format("类别:{0},名称:{1}的物料类别或物料不存在",mtrltype,mtrlname));
                                        break;
                                    }
                                    var updatePrice = new u_mtrl_price_pricelist { mtrlid = mtrlid, pricelistid = pricename.keyId };
                                    ICell cell = row.GetCell(pricename.colIndex);
                                    updatePrice.price = Convert.ToDecimal(GetCellValue(cell));
                                    DbSqlHelper.InsertOrUpdate(cmd, updatePrice, "price");
                                }
                            }
                        }
                        cmd.Transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        cmd.Transaction.Rollback();
                        rslt.ErrMsg = ex.Message;
                    }
                }
               
            }
        }
        private object GetCellValue(ICell cell)
        {
            if (cell == null) return null;
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                default:
                    return cell.ToString().Trim();
            }
        }
        public class HeaderPropetry
        {
            public string name { get; set; }
            public int colIndex { get; set; }
            public int keyId { get; set; }
        }
    }
}