Ejemplo n.º 1
0
        public List <int> GetDataInLine(string excelFilePath, string sheetActive, int number_cell, string FileUploadedName, int number_row)
        {
            List <int> output = new List <int>();
            int        index = 0, columnIndex = 0;

            string          oleConnString   = GetOleDbConnectionString(excelFilePath);
            OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
            OleDbCommand    oleReadCommand  = new OleDbCommand();
            SqlTransaction  transaction     = null;

            try
            {
                //GET EXCEL DATA
                string sqlOle = "select * from [" + sheetActive + "$]";
                oleDbConnection.Open();
                oleReadCommand.Connection  = oleDbConnection;
                oleReadCommand.CommandText = sqlOle;


                using (OleDbDataReader dataReader = oleReadCommand.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        index++;
                        if (index < number_row)
                        {
                            continue;
                        }
                        columnIndex = 1;

                        for (int i = 0; i < number_cell; i++)
                        {
                            MappingExcelDB column = new MappingExcelDB();
                            column.Value = dataReader[column.ExcelColumn + columnIndex];
                            output.Add(Int32.Parse(column.Value.ToString()));
                            columnIndex += 2;
                        }
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                this.ERROR = ex.ToString();
            }

            finally
            {
                this.DBConnection.CloseDBConnect();
                oleReadCommand.Dispose();
                oleDbConnection.Close();
                oleDbConnection.Dispose();
            }
            return(output);
        }
Ejemplo n.º 2
0
        public ExcelModel GetXMLConfig(string xmlFilePath)
        {
            ExcelModel output  = new ExcelModel();
            JObject    jObject = BI_Project.Helpers.Utility.JTokenHelper.GetXML2Jobject(xmlFilePath);

            output.FolderUploadedDirectory = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.UploadedDirectory");
            output.DBTableName             = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.database.tablename");
            string strstartRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.@started_row");

            output.ExcelStartRow = Int32.Parse(strstartRow);
            string strnumberRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_row");
            //number_row = Int32.Parse(strnumberRow);

            string strnumberCell = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_cell");

            //number_cell = Int32.Parse(strnumberCell);
            output.ExcelSheetName         = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.#text");
            output.FolderHelpDocumentPath = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.HelpDocumentPath");
            output.LangNote             = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.Note.#cdata-section");
            output.FolderFileNativeName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.FileNativeName");

            //LAY CAC COT TUONG UNG
            IEnumerable <JToken> columns = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..mapping.column");
            int index = 0;

            foreach (JToken column in columns)
            {
                MappingExcelDB mappingExcel = new MappingExcelDB();
                mappingExcel.ColumnName  = column.SelectToken("..name").Value <string>();
                mappingExcel.ExcelColumn = index;
                mappingExcel.Datatype    = column.SelectToken("..datatype").Value <string>();
                output.DBLstColumns.Add(mappingExcel);
                index++;
            }

            //LAY CAC THAM SO PARAMETERS
            IEnumerable <JToken> paras = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..paras.para");

            index = 0;
            foreach (JToken para in paras)
            {
                //XMLParaModel
                XMLParaModel paraXMLModel = new XMLParaModel();
                paraXMLModel.Name = para.SelectToken("..name").Value <string>();
                paraXMLModel.Row  = para.SelectToken("..row").Value <Int32>();
                //paraXMLModel.Column = para.SelectToken("..column").Value<Int32>();
                paraXMLModel.DataType = para.SelectToken("..datatype").Value <string>();
                paraXMLModel.Active   = para.SelectToken("..active").Value <string>();
                paraXMLModel.Value    = para.SelectToken("..value").Value <string>();

                output.XmlParas.Add(paraXMLModel.Name, paraXMLModel);
            }

            return(output);
        }
Ejemplo n.º 3
0
        //public List<MappingExcelDB> GetColumnList(string xmlFilePath, ref string uploadFolder, ref string tablename, ref int startRow, ref string sheetActive,
        //ref string helpDocumentPath, ref string note, ref string fileNativeName)
        //{
        //    List<MappingExcelDB> output = new List<MappingExcelDB>();
        //    JObject jObject = BI_Project.Helpers.Utility.JTokenHelper.GetXML2Jobject(xmlFilePath);

        //    uploadFolder = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.UploadedDirectory");
        //    tablename = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.database.tablename");
        //    string strstartRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.@started_row");
        //    startRow = Int32.Parse(strstartRow);
        //    sheetActive = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.#text");
        //    helpDocumentPath = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.HelpDocumentPath");
        //    note = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.Note.#cdata-section");
        //    fileNativeName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.FileNativeName");

        //    IEnumerable<JToken> columns = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..mapping.column");


        //    int index = 0;

        //    foreach (JToken column in columns)
        //    {
        //        MappingExcelDB mappingExcel = new MappingExcelDB();
        //        mappingExcel.ColumnName = column.SelectToken("..name").Value<string>();
        //        mappingExcel.ExcelColumn = index;
        //        mappingExcel.Datatype = column.SelectToken("..datatype").Value<string>();
        //        output.Add(mappingExcel);
        //        index++;
        //    }

        //    return output;

        //}

        //public int Import2Database(int userid, string excelFilePath, string tablename, int startRow, string sheetActive,
        //    string helpDocumentPath, string note, string fileNativeName, string navetiveFile, int uploadRoleId, List<MappingExcelDB> lstMapping, string FileUploadedName)
        //{
        //    int output = 0;
        //    int index = 0, columnIndex = 0;

        //    string oleConnString = GetOleDbConnectionString(excelFilePath);
        //    OleDbConnection oleDbConnection = new OleDbConnection(oleConnString);
        //    OleDbCommand oleReadCommand = new OleDbCommand();
        //    SqlTransaction transaction = null;
        //    try
        //    {


        //        //GET EXCEL DATA
        //        string sqlOle = "select * from [" + sheetActive + "$]";
        //        oleDbConnection.Open();
        //        oleReadCommand.Connection = oleDbConnection;
        //        oleReadCommand.CommandText = sqlOle;


        //        int idHistory = 0;
        //        //******************INSERT INTO HISTORY*********************************************************
        //        this.DBConnection.OpenDBConnect();
        //        this.InsertHistory(userid, uploadRoleId, helpDocumentPath, note, navetiveFile, FileUploadedName, ref idHistory);
        //        string sqlInsertDW = "insert into " + tablename + "(";
        //        string sqlInsertDWValues = " values (";

        //        bool isCommplateGenerateSQL = false;

        //        _dbStagingConnection.OpenDBConnect();
        //        transaction = _dbStagingConnection.SQLDBConnect.BeginTransaction();
        //        _dbStagingConnection.command.Transaction = transaction;
        //        using (OleDbDataReader dataReader = oleReadCommand.ExecuteReader())
        //        {
        //            while (dataReader.Read())
        //            {
        //                index++;
        //                if (index < startRow) continue;
        //                this._dbStagingConnection.command.Parameters.Clear();
        //                columnIndex = 0;

        //                int countNullColumn = 0;
        //                foreach (MappingExcelDB column in lstMapping)
        //                {
        //                    columnIndex++;
        //                    column.Value = dataReader[column.ExcelColumn];
        //                    if (!isCommplateGenerateSQL)
        //                    {
        //                        sqlInsertDW += column.ColumnName + ",";
        //                        sqlInsertDWValues += "@" + column.ColumnName + ",";
        //                    }
        //                    //if (column.Value == null)
        //                    //    throw new Exception("Value in the column " + column.ColumnName + ", row " + index.ToString() + "  is null ");

        //                    try
        //                    {
        //                        if (column.Value.ToString().Trim() == "")
        //                        {
        //                            countNullColumn++;
        //                        }
        //                    }
        //                    catch (Exception)
        //                    {
        //                        countNullColumn++;
        //                    }
        //                    this._dbStagingConnection.command.Parameters.Add(new SqlParameter("@" + column.ColumnName, column.Value ?? (object)DBNull.Value));
        //                }
        //                if (!isCommplateGenerateSQL)
        //                {
        //                    sqlInsertDW = sqlInsertDW.Trim(',') + ")";
        //                    sqlInsertDWValues = sqlInsertDWValues.Trim(',') + ")";
        //                    isCommplateGenerateSQL = true;
        //                }

        //                //*****************************INSERT INTO DATABASE******************************************************
        //                //if (countNullColumn == lstMapping.Count) throw new Exception("The row is null");
        //                //this._dbStagingConnection.command.CommandText = sqlInsertDW + sqlInsertDWValues;
        //                //this._dbStagingConnection.command.CommandType = CommandType.Text;
        //                //this._dbStagingConnection.command.ExecuteNonQuery();
        //                if (countNullColumn == lstMapping.Count) break;
        //                else
        //                {
        //                    this._dbStagingConnection.command.CommandText = sqlInsertDW + sqlInsertDWValues;
        //                    this._dbStagingConnection.command.CommandType = CommandType.Text;
        //                    this._dbStagingConnection.command.ExecuteNonQuery();
        //                }


        //            }

        //        }
        //        transaction.Commit();
        //        //******************UPDATE ENDDATE HISTORY***************************************************************
        //        this.UpdateHistory(idHistory, index);

        //    }
        //    catch (Exception ex)
        //    {
        //        if (null != transaction) transaction.Rollback();
        //        this.ERROR = "ERROR in line=" + index.ToString() + ", column=" + columnIndex + ":::::" + ex.ToString();
        //        this.ERROR_USER = "******" + index.ToString() + ", column=" + columnIndex;
        //    }

        //    finally
        //    {
        //        this.DBConnection.CloseDBConnect();
        //        oleReadCommand.Dispose();
        //        oleDbConnection.Close();
        //        oleDbConnection.Dispose();
        //        _dbStagingConnection.CloseDBConnect();
        //    }
        //    return output;
        //}


        /*****************************************************GET DATA DOANH SO*********************************/
        public List <MappingExcelDB> GetColumnList(string xmlFilePath, ref string uploadFolder, ref string tablename, ref int startRow, ref string sheetActive,
                                                   ref string helpDocumentPath, ref string note, ref string fileNativeName, ref int number_row, ref int number_cell, ref List <string> lstCellName)
        {
            List <MappingExcelDB> output = new List <MappingExcelDB>();
            JObject jObject = BI_Project.Helpers.Utility.JTokenHelper.GetXML2Jobject(xmlFilePath);

            uploadFolder = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.UploadedDirectory");
            tablename    = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.database.tablename");
            string strstartRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.@started_row");

            startRow = Int32.Parse(strstartRow);
            string strnumberRow = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_row");

            number_row = Int32.Parse(strnumberRow);

            string strnumberCell = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell.@number_cell");

            number_cell      = Int32.Parse(strnumberCell);
            sheetActive      = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.sheet_active.#text");
            helpDocumentPath = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.HelpDocumentPath");
            note             = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.Note.#cdata-section");
            fileNativeName   = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.FileNativeName");

            //get list cell name
            string cellName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject, "excel_source.ExchangingData.excel.cell_name");

            string[] lstName = null;
            if (cellName != null && cellName != "")
            {
                lstName = cellName.Split(',');
                for (int i = 0; i < lstName.Length; i++)
                {
                    lstCellName.Add(lstName[i].ToString());
                }
            }

            IEnumerable <JToken> columns = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, "..mapping.column");


            int index = 0;

            foreach (JToken column in columns)
            {
                MappingExcelDB mappingExcel = new MappingExcelDB();
                mappingExcel.ColumnName  = column.SelectToken("..name").Value <string>();
                mappingExcel.ExcelColumn = index;
                mappingExcel.Datatype    = column.SelectToken("..datatype").Value <string>();
                output.Add(mappingExcel);
                index++;
            }

            return(output);
        }