TG.WXCRM.V4/WebHelper/ExcelImport/CsvComUploadTool.cs

using LumenWorks.Framework.IO.Csv;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using WX.CRM.Common;
using WX.CRM.Model.Entity;

namespace WX.CRM.WebHelper.ExcelImport
{
    public abstract class CsvComUploadTool : ComUploadTool
    {

        /// <summary>
        /// excel类型转换
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public object GetObjectValue(object cell, string type)
        {
            string cellStr = cell.ToString().Trim();
            object obj = new object();
            switch (type)
            {
                case "System.String": obj = cellStr; break;
                case "System.Decimal":
                    obj = cellStr.GetDecimal(0);
                    break;
                case "System.Boolean": obj = Convert.ToBoolean(cellStr); break;
                case "System.DateTime":
                    obj = cellStr.GetObjectByDateTime();
                    break;
                default: obj = cellStr; break;
            }
            return obj;
        }
        #region CreateDataTable (生成待批量入库的DataTable)

        /// <summary>
        /// 生成待批量入库的DataTable
        /// </summary>
        /// <param name="file">上传的文件对象</param>
        /// <param name="datatype">数据文件类型，枚举</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns></returns>
        public override DataTable CreateDataTable(HttpRequestBase Request, out ValidationErrors errMsg, decimal isNew)
        {
            //1、缓存中读取此datatype对应的数据字典，创建datatable
            //2、根据数据字典的关键字，找出每一列对应的excel中字段的序号
            //3、循环生成每一条datarow记录
            errMsg = new ValidationErrors();
            DataTable tab = new DataTable();
            HttpPostedFileBase file = Request.Files["uploadFile"];
            Dictionary<string, int> excelDic = new Dictionary<string, int>();//存储excel 的列 和对应索引
            try
            {
                using (CsvReader csv = new CsvReader(new StreamReader(file.InputStream, Encoding.GetEncoding("gb2312")), true))
                {
                    csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;
                    int fieldCount = csv.FieldCount;
                    string[] headers = csv.GetFieldHeaders();
                    List<BAS_EXCELIMPORTDICT> modelList = DataCacheHelper.GetCache().GetList_ExcelImportDict(ExcelDataType);//字典
                    //--------------------------表头--------------------------
                    for (int i = 0; i < headers.Length; i++)
                    {
                        string head = headers[i].Trim();
                        excelDic.Add(head, i);
                    }
                    foreach (BAS_EXCELIMPORTDICT model in modelList)
                    {
                        for (int i = 0; i < headers.Length; i++)
                        {
                            string head = headers[i].Trim();
                            if (head == model.EXCEL_COLNAME.Trim())
                            {
                                model.ColumnIndex = i;
                                break;
                            }
                            else if (head.Contains(model.EXCEL_COLNAME.Trim()))
                            {
                                model.ColumnIndex = i;
                                continue;
                            }
                        }
                        DataColumn column = new DataColumn();
                        column.Caption = model.EXCEL_COLNAME;
                        column.ColumnName = model.DB_COLNAME;
                        if (!string.IsNullOrEmpty(model.COLTYPE))
                            column.DataType = Type.GetType(model.COLTYPE);
                        tab.Columns.Add(column);
                    }
                    List<BAS_EXCELIMPORTDICT> noColumnIndexList = modelList.Where(m => m.ISPRIMARY == 1 && m.ColumnIndex == -1).ToList();
                    if (noColumnIndexList.Count > 0)
                    {

                        string erromessage = "excel中找不到列：";
                        foreach (var model in noColumnIndexList)
                        {
                            erromessage += "[" + model.EXCEL_COLNAME + "]";
                        }
                        errMsg.Add(erromessage);
                        return tab;
                    }

                    //--------------------------数据--------------------------
                    while (csv.ReadNextRecord())
                    {
                        DataRow dataRow = tab.NewRow();
                        for (int i = 0; i < modelList.Count; i++)
                        {
                            BAS_EXCELIMPORTDICT model = modelList[i];
                            object colvalue = new object();
                            if (model.ColumnIndex != -1)
                            {
                                var cell = csv[model.ColumnIndex].Trim();
                                if (model.VALUEFUNCTION == null || model.VALUEFUNCTION != "CUSTOMFORMATCOLNUM")
                                    colvalue = GetObjectValue(cell, model.COLTYPE);//根据引获取值
                                else
                                    colvalue = cell;
                            }
                            if (model.ColumnIndex == -1)
                                colvalue = DBNull.Value;

                            if (!string.IsNullOrWhiteSpace(model.VALUEFUNCTION))
                            {
                                if (model.VALUEFUNCTION == "CREATERESID")//--------------创建客户ID
                                {
                                    colvalue = CreateResId(colvalue.ToString());
                                }
                                else if (model.VALUEFUNCTION == "CUSTOMFORMATCOLNUM")//--特殊格式化函数
                                {
                                    colvalue = CustomFormatColnum(modelList, csv, model, excelDic);
                                }
                                else if (model.VALUEFUNCTION == "FORMDATAFORMAT")//---从上传界面获取的 数据
                                {
                                    colvalue = FormDataFormat(Request, model);
                                }
                            }

                            dataRow[i] = colvalue;
                        }

                        tab.Rows.Add(dataRow);
                    }
                    RemoveCacheData();//先移除缓存
                    SaveToCache(tab);
                    return tab;
                }
            }
            catch (Exception ex) { errMsg.Add(ex.ToString()); return tab; }
        }
        #endregion
        #region 子类可重写的实现
        /// <summary>
        /// 单元格数据格式化（供子类重写的格式化计算函数）
        /// </summary>
        /// <param name="dictList">表字典</param>
        /// <param name="excelRow">这行excel数据</param>
        /// <param name="dictModel">该单元格字典对象</param>
        /// <param name="dictModel">存储excel 的列 和对应索引</param>
        /// <returns></returns>
        protected virtual object CustomFormatColnum(List<BAS_EXCELIMPORTDICT> dictList, CsvReader rowReader, BAS_EXCELIMPORTDICT dictModel, Dictionary<string, int> excelDic)
        {
            return rowReader[dictModel.ColumnIndex];
        }
        #endregion
    }
}