jiance-analysis/DPTools/components/TZCommonFunc.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Uninpho.DBOperation.Model;
using ClosedXML.Excel;
using System.IO;
using DevExpress.XtraEditors;

namespace Uninpho.Tools.components
{
     class catchExcelError
    {
        public int row;
        public int column;
        public string message;
    }
    /// <summary>
    /// 公共函数类
    /// 保存通用数据
    /// </summary>
    static class TZCommonFunc
    {
        private static string[] ErrorM = new string[] { "不能为空", "数字格式错误","未知错误" };
        //excel数据规范异常记录
        private static List<catchExcelError> dataError = new List<catchExcelError>();
        public static List<string> dataSsccess = new List<string>();
        //public static List<T_quxian_account> Listdata_qx { get; set; }
        //public static List<T_daocha_account> Listdata_dc { get; set; }
        //public static List<T_guanjie_account> Listdata_gj { get; set; }
        public static List<T_quxian_account> Listdata_qx = new List<T_quxian_account>();
        public static List<T_daocha_account> Listdata_dc = new List<T_daocha_account>();
        public static List<T_guanjie_account> Listdata_gj = new List<T_guanjie_account>();
        public static List<T_quxian_account> Listdata_qx_ = new List<T_quxian_account>(); //临时
        public static List<T_daocha_account> Listdata_dc_ = new List<T_daocha_account>(); //临时
        public static List<T_guanjie_account> Listdata_gj_ = new List<T_guanjie_account>(); //临时
        //获取局名信息
        public static List<string> Qxjm_List = new List<string>();
        public static List<string> Dcjm_List = new List<string>();
        public static List<string> Gjjm_List = new List<string>();

        public static void initList()
        {
            Listdata_qx.Clear();
            Listdata_dc.Clear();
            Listdata_gj.Clear();
            dataError.Clear();
        }

        /// <summary>
        /// 导入数据公共函数
        /// </summary>
        /// <param name="tablename">数据类型名称</param>
        public static bool GetTabledata(TableName tablename)
        {
            //DataSet ds = new DataSet();//新建数据集
            dataSsccess.Clear();
            OpenFileDialog fd = new OpenFileDialog();//首先根据打开文件对话框，选择excel表格
            fd.Multiselect = true;
            fd.Filter = "表格(*.xls,*.xlsx)| *.xls;*.xlsx|所有文件(*.*)|*.*";//打开文件对话框筛选器
            //fd.Filter = "xls文件(*.xls)|*.xls|xlsx文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";//打开文件对话框筛选器
            string[] filenames;//文件完整的路径名
            if (fd.ShowDialog() == DialogResult.OK)
            {
                dataError.Add(new catchExcelError() { message = "test" });
                filenames = fd.FileNames;
                foreach (var strPath in filenames)
                {
                    initList();
                    string path = strPath;
                    try
                    {
                        if (Path.GetExtension(strPath) == ".xls")
                        {
                            path = Application.StartupPath + "//result//temptz.xlsx";
                            if (File.Exists(path))
                            {
                                File.Delete(path);
                            }
                            NPOIExt.ConvertToXlsx(strPath, path);
                        }
                    }
                    catch (Exception)
                    {
                        XtraMessageBox.Show("文件"+ Path.GetFileName(strPath) + "损坏！请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        continue;
                    }

                    try
                    {
                        #region lpy's
                        //  /*OleDbConnection Con;
                        //string strExtension = System.IO.Path.GetExtension(strPath);                    
                        //switch (strExtension)
                        //{
                        //    case ".xls":
                        //        Con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        //        break;
                        //    case ".xlsx":
                        //        Con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        //        break;
                        //    default:
                        //        Con = null;
                        //        break;
                        //}*/
                        //                    //64
                        //                    string strCon = "provider=Microsoft.ACE.OLEDB.12.0;data source=" + strPath + ";extended properties=excel 8.0";//关键是红色区域
                        //                    //32
                        //                    //string strCon = "provider=microsoft.jet.oledb.4.0;data source=" + strPath + ";extended properties=excel 8.0";//关键是红色区域
                        //                    OleDbConnection Con = new OleDbConnection(strCon);//建立连接
                        //                    string strSql = "select * from [Sheet1$]";//表名的写法也应注意不同，对应的excel表为sheet1，在这里要在其后加美元符号$，并用中括号
                        //                    OleDbCommand Cmd = new OleDbCommand(strSql, Con);//建立要执行的命令
                        //                    OleDbDataAdapter da = new OleDbDataAdapter(Cmd);//建立数据适配器
                        //                    DataSet Table_data = new DataSet();
                        //                    da.Fill(Table_data, "datatable");//把数据适配器中的数据读到数据集中的一个表中（此处表名为datatable，可以任取表名）
                        //                    //指定datagridview1的数据源为数据集ds的第一张表（也就是shyman表），也可以写ds.Table["shyman"]
                        #endregion

                        List<List<string>> exceldata;
                        switch (tablename)
                        {
                            case TableName.QX_Data:
                                #region 读excel
                                Listdata_qx_.Clear();
                                try
                                {
                                    exceldata = readExcel(path, 16);

                                }
                                catch
                                {
                                    XtraMessageBox.Show("读取文件：“" + Path.GetFileName(strPath) + "”失败，怀疑文件已损坏！请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    continue;
                                }
                                for (int i = 0; i < exceldata.Count; i++)
                                {
                                    T_quxian_account onescvdata = new T_quxian_account();
                                    int ii = 0;
                                    Type t = onescvdata.GetType();//获得该类的Type
                                    foreach (PropertyInfo pi in t.GetProperties())
                                    {
                                        catchExcelError EE = new catchExcelError();
                                        EE.row = i + 2;
                                        EE.column = ii;
                                        EE.message = ErrorM[0];
                                        string tt = pi.PropertyType.Name;
                                        if (pi.Name != "Id" && ii != exceldata[i].Count)
                                        {
                                            if (pi.Name == "Xianming" || pi.Name == "Hangbie" || pi.Name == "Qxfx")
                                            {
                                                if (exceldata[i][ii] == "")
                                                {
                                                    dataError.Add(EE);
                                                    ii++;
                                                    continue;
                                                }
                                                pi.SetValue(onescvdata, exceldata[i][ii]);
                                            }
                                            else if (pi.Name == "Qzlc" || pi.Name == "Jslc" || pi.Name == "Qhhxc" || pi.Name == "Zhhxc" || pi.Name == "Chaogao")
                                            {
                                                if (exceldata[i][ii] == "")
                                                {
                                                    dataError.Add(EE);
                                                    ii++;
                                                    continue;
                                                }
                                                if (pi.Name == "Qzlc" && (exceldata[i][ii].Contains('(') || exceldata[i][ii].Contains('-')))
                                                {
                                                    ii++;
                                                    pi.SetValue(onescvdata, Convert.ToDecimal(0));
                                                    continue;
                                                }
                                                try
                                                {
                                                    pi.SetValue(onescvdata, Convert.ToDecimal(exceldata[i][ii]));
                                                }
                                                catch
                                                {
                                                    EE.message = ErrorM[1];
                                                    dataError.Add(EE);
                                                }
                                            }
                                            else
                                            {
                                                if (tt == "String")
                                                {
                                                    pi.SetValue(onescvdata, exceldata[i][ii]);
                                                }
                                                else
                                                {
                                                    string cha = exceldata[i][ii].Replace("(", "-");
                                                    cha = cha.Replace("（", "-");
                                                    cha = cha.Replace(")", "");
                                                    cha = cha.Replace("）", "");
                                                    try
                                                    {
                                                        pi.SetValue(onescvdata, Convert.ToDecimal(cha));
                                                    }
                                                    catch
                                                    {
                                                        EE.message = ErrorM[1];
                                                        dataError.Add(EE);
                                                    }                                         
                                                }
                                            }
                                            ii++;
                                        }
                                    }
                                    Listdata_qx_.Add(onescvdata);
                                }
                                if (dataError.Count == 0)
                                {
                                    Listdata_qx = Listdata_qx.Concat(Listdata_qx_).ToList();
                                }
                                #endregion
                                //Listdata_qx = DataTableToList<T_quxian_account>(Table_data.Tables[0], TableName.QX_Data);
                                ////生成唯一编码id
                                //foreach (var item in Listdata_qx)
                                //{
                                //    string uuid = System.Guid.NewGuid().ToString("N");
                                //    item.Id = uuid;
                                //}
                                break;
                            case TableName.DC_Data:
                                #region 读excel
                                Listdata_dc_.Clear();
                                try
                                {
                                    exceldata = readExcel(path, 18);
                                }
                                catch
                                {
                                    XtraMessageBox.Show("读取文件：“" + Path.GetFileName(strPath) + "”失败，怀疑文件已损坏！请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                                    continue;
                                }
                                for (int i = 0; i < exceldata.Count; i++)
                                {
                                    T_daocha_account onescvdata = new T_daocha_account();
                                    int ii = 0;
                                    Type t = onescvdata.GetType();//获得该类的Type
                                    foreach (PropertyInfo pi in t.GetProperties())
                                    {
                                        catchExcelError EE = new catchExcelError();
                                        EE.row = i + 2;
                                        EE.column = ii;
                                        EE.message = ErrorM[0];
                                        string tt = pi.PropertyType.Name;
                                        if (pi.Name != "Id" && ii != exceldata[i].Count)
                                        {
                                            if (pi.Name == "Xlm" || pi.Name == "Xingbie" || pi.Name == "Czm" || pi.Name == "Dcbh")
                                            {
                                                if (exceldata[i][ii] == "")
                                                {
                                                    dataError.Add(EE);
                                                    ii++;
                                                    continue;
                                                }
                                                pi.SetValue(onescvdata, exceldata[i][ii]);
                                            }
                                            else if (pi.Name == "Jgjlc" || pi.Name == "Zch" || pi.Name == "Quanchang")
                                            {
                                                if (exceldata[i][ii] == "")
                                                {
                                                    dataError.Add(EE);
                                                    ii++;
                                                    continue;
                                                }
                                                string cha = exceldata[i][ii].Replace("(", "-");
                                                cha = cha.Replace("（", "-");
                                                cha = cha.Replace(")", "");
                                                cha = cha.Replace("）", "");
                                                try
                                                {
                                                    pi.SetValue(onescvdata, Convert.ToDecimal(cha));
                                                }
                                                catch
                                                {
                                                    EE.message = ErrorM[1];
                                                    dataError.Add(EE);
                                                }
                                            }
                                            else
                                            {
                                                if (tt == "String")
                                                {
                                                    pi.SetValue(onescvdata, exceldata[i][ii]);
                                                }
                                                else
                                                {
                                                    string cha = exceldata[i][ii].Replace("(", "-");
                                                    cha = cha.Replace("（", "-");
                                                    cha = cha.Replace(")", "");
                                                    cha = cha.Replace("）", "");
                                                    try
                                                    {
                                                        pi.SetValue(onescvdata, Convert.ToDecimal(cha));
                                                    }
                                                    catch
                                                    {
                                                        EE.message = ErrorM[1];
                                                        dataError.Add(EE);
                                                    }
                                                }
                                            }
                                            ii++;
                                        }
                                    }
                                    Listdata_dc_.Add(onescvdata);
                                }
                                if (dataError.Count == 0)
                                {
                                    Listdata_dc = Listdata_dc.Concat(Listdata_dc_).ToList();
                                }
                                #endregion

                                break;
                            case TableName.GJ_Data:
                                #region 读excel
                                Listdata_gj_.Clear();
                                try
                                {
                                    exceldata = readExcel(path, 6);
                                }
                                catch
                                {
                                    XtraMessageBox.Show("读取文件：“" + Path.GetFileName(strPath) + "”失败，怀疑文件已损坏！请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    continue;
                                }
                                for (int i = 0; i < exceldata.Count; i++)
                                {
                                    T_guanjie_account onescvdata = new T_guanjie_account();
                                    int ii = 0;
                                    Type t = onescvdata.GetType();//获得该类的Type
                                    foreach (PropertyInfo pi in t.GetProperties())
                                    {
                                        catchExcelError EE = new catchExcelError();
                                        EE.row = i + 2;
                                        EE.column = ii ;
                                        EE.message = ErrorM[0];
                                        string tt = pi.PropertyType.Name;
                                        if (pi.Name != "Id" && ii != exceldata[i].Count)
                                        {
                                            if (exceldata[i][ii] == "")
                                            {
                                                dataError.Add(EE);
                                                ii++;
                                                continue;
                                            }
                                            if (tt == "String")
                                            {
                                                pi.SetValue(onescvdata, exceldata[i][ii]);
                                            }
                                            else
                                            {
                                                string cha = exceldata[i][ii].Replace("(", "-");
                                                cha = cha.Replace("（", "-");
                                                cha = cha.Replace(")", "");
                                                cha = cha.Replace("）", "");
                                                try
                                                {
                                                    pi.SetValue(onescvdata, Convert.ToDecimal(cha));
                                                }
                                                catch
                                                {
                                                    EE.message = ErrorM[1];
                                                    dataError.Add(EE);
                                                }
                                            }
                                            ii++;
                                        }
                                    }
                                    Listdata_gj_.Add(onescvdata);
                                }
                                if (dataError.Count == 0)
                                {
                                    Listdata_gj = Listdata_gj.Concat(Listdata_gj_).ToList();
                                }
                                #endregion
                                break;
                            default: break;
                        }

                    }
                    catch (Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);//捕捉异常
                    }
                    if (dataError.Count != 0)
                    {
                        string mess = "文件:" + Path.GetFileName(strPath) + "终止导入！\r\n";
                        foreach (var item in dataError)
                        {
                            mess += "第“" + item.row + "”行,第“" + GetColumnChar(item.column) + "”列数据导入错误：" + item.message + "\r\n";
                        }
                        XtraMessageBox.Show(mess, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else if(!(dataError.Count == 1 && dataError[0].message == "test"))
                    {
                        dataSsccess.Add("文件:" + Path.GetFileName(strPath) + "导入成功！");
                    }
                }
                return true;
            }
            return false;
        }

        private static List<List<string>> readExcel(string path,int cellcount)
        {
            List<List<string>> datares = new List<List<string>>();
            var workbook = new XLWorkbook(path);
            IXLWorksheet XLWorksheet = workbook.Worksheet(1);
            int rowcount = XLWorksheet.RowCount();

            for (int i = 2; i <= rowcount; i++)
            {
                if (XLWorksheet.Row(i).Cell(1).IsEmpty())
                {
                    break;
                }
                List<string> cellres = new List<string>();
                for (int j = 1; j <= cellcount; j++)
                {
                    //获得单元格
                    var cell = XLWorksheet.Row(i).Cell(j);
                        cellres.Add(cell.Value.ToString());
                }
                datares.Add(cellres);
            }
            return datares;
        }

        /// <summary>
        /// datatable转成实体类
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="dt">datatable数据</param>
        /// <returns></returns>
        private static List<T> DataTableToList<T>(DataTable dt, TableName tablename) where T : class, new()
        {
            Assist_quxian assist_quxian = new Assist_quxian();
            Assist_daocha assist_daocha = new Assist_daocha();
            Assist_guanjie assist_guanjie = new Assist_guanjie();
            if (dt == null || dt.Rows.Count == 0)
                return null;
            List<T> result = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    T res = new T();
                    for (int i = 0; i < dr.Table.Columns.Count; i++)
                    {
                        PropertyInfo propertyInfo = null;
                        switch (tablename)
                        {
                            case TableName.QX_Data:                                
                                propertyInfo = res.GetType().GetProperty(GetTypeValue(dr.Table.Columns[i].ColumnName, assist_quxian), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                                break;
                            case TableName.DC_Data:                                
                                propertyInfo = res.GetType().GetProperty(GetTypeValue(dr.Table.Columns[i].ColumnName, assist_daocha), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                                break;
                            case TableName.GJ_Data:                                
                                propertyInfo = res.GetType().GetProperty(GetTypeValue(dr.Table.Columns[i].ColumnName, assist_guanjie), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                                break;
                            default: break;
                        }
                        
                        if (propertyInfo != null)
                        {
                            var value = dr[i];
                            switch (propertyInfo.PropertyType.FullName)
                            {
                                case "System.Decimal":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0.0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToDecimal(value), null); break;
                                case "System.String":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, "-", null); break; }
                                    propertyInfo.SetValue(res, value.ToString(), null); break;
                                case "System.Int32":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToInt32(value), null); break;
                                case "System.Double":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0.0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToDouble(value), null); break;
                                default:
                                    propertyInfo.SetValue(res, value, null); break;
                            }
                        }
                    }
                    result.Add(res);
                }
                catch (Exception ex)
                {
                    ex = new Exception(string.Format("请检查第{0}行数据",result.Count+2));
                    throw ex;
                }

            }
            return result;
        }

        /// <summary>
        /// 辅助类转化器
        /// </summary>
        /// <typeparam name="T">类</typeparam>
        /// <param name="s">属性值</param>
        /// <param name="t">实例化类</param>
        /// <returns></returns>
        private static string GetTypeValue<T>(string s, T t)
        {
            string columnName = "";
            Type assistType = t.GetType();
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static);
            for (int i = 0; i < assistType.GetFields().Length; i++)
            {
                if (assistType.GetFields()[i] != null)
                {
                    if (assistType.GetFields()[i].GetValue(t) != null && assistType.GetFields()[i].GetValue(t).ToString() == s)
                    {
                        columnName = assistType.GetFields()[i].Name;
                        //Debug.LogError(assistType.GetFields()[i].Name);
                        break;
                    }
                }
            }
            return columnName;
        }

        /// <summary>
        /// excel列数转为字母列号
        /// </summary>
        /// <param name="col"></param>
        /// <returns></returns>
        private static string GetColumnChar(int col)
        {
            var a = col / 26;
            var b = col % 26;

            if (a > 0) return GetColumnChar(a - 1) + (char)(b + 65);

            return ((char)(b + 65)).ToString();
        }
    }
}