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); }
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); }
//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); }