public JsonResult LoadExcel() { List <DET_AGENTEC> ld = new List <DET_AGENTEC>(); if (Request.Files.Count > 0) { HttpPostedFileBase file = Request.Files["FileUpload"]; ////string extension = System.IO.Path.GetExtension(file.FileName); IExcelDataReader reader = ExcelReaderFactory.CreateReader(file.InputStream); // 2. Use the AsDataSet extension method DataSet result = reader.AsDataSet(); // The result of each spreadsheet is in result.Tables // 3.DataSet - Create column names from first row DataTable dt = result.Tables[0]; ld = objAList(dt); reader.Close(); } List <Flujos> ff = new List <Flujos>(); List <USUARIO> usuarios = new List <USUARIO>(); List <CLIENTE> clientes = new List <CLIENTE>(); List <PAI> paises = new List <PAI>(); foreach (DET_AGENTEC da in ld) { Flujos f = new Flujos(); ////---------------------------------------USUARIO f.USUARIOC_ID = da.USUARIOC_ID; f.USUARIOC_IDX = true; USUARIO u = usuarios.FirstOrDefault(x => x.ID.Equals(f.USUARIOC_ID)); if (u == null) { u = db.USUARIOs.Where(x => x.ID.Equals(f.USUARIOC_ID) && x.ACTIVO == true).FirstOrDefault(); if (u == null) { f.USUARIOC_IDX = false; } else { usuarios.Add(u); } } if (!f.USUARIOC_IDX) { f.USUARIOC_ID = "<span class='red white-text'>" + f.USUARIOC_ID + "</span>"; } ////---------------------------------------PAIS f.PAIS_ID = da.PAIS_ID; f.PAIS_IDX = true; PAI p = paises.FirstOrDefault(x => x.LAND.Equals(f.PAIS_ID)); if (p == null) { p = db.PAIS.Where(x => x.LAND.Equals(f.PAIS_ID) && x.ACTIVO && x.SOCIEDAD_ID != null).FirstOrDefault(); if (p == null) { f.PAIS_IDX = false; } else { paises.Add(p); } } if (!f.PAIS_IDX) { f.PAIS_ID = "<span class='red white-text'>" + f.PAIS_ID + "</span>"; } ////---------------------------------------CLIENTE f.KUNNR = da.KUNNR; f.KUNNRX = true; CLIENTE c = clientes.FirstOrDefault(x => x.KUNNR.Equals(f.KUNNR)); if (c == null) { c = db.CLIENTEs.Where(cc => cc.KUNNR.Equals(f.KUNNR) && cc.ACTIVO).FirstOrDefault(); if (c == null) { f.KUNNRX = false; } else { clientes.Add(c); } } if (!f.KUNNRX) { f.KUNNR = "<span class='red white-text'>" + f.KUNNR + "</span>"; } f.VERSION = da.VERSION.ToString(); f.POS = da.POS.ToString(); ////---------------------------------------USUARIOA f.USUARIOA_ID = da.USUARIOA_ID; f.USUARIOA_IDX = true; USUARIO ua = usuarios.FirstOrDefault(x => x.ID.Equals(f.USUARIOA_ID)); if (ua == null) { ua = db.USUARIOs.Where(x => x.ID.Equals(f.USUARIOA_ID) && x.ACTIVO == true).FirstOrDefault(); if (ua == null) { f.USUARIOA_IDX = false; } else { usuarios.Add(ua); } } if (!f.USUARIOA_IDX) { f.USUARIOA_ID = "<span class='red white-text'>" + f.USUARIOA_ID + "</span>"; } if (da.MONTO == null) { f.MONTO = null; } else { f.MONTO = da.MONTO.ToString(); } f.PRESUPUESTO = da.PRESUPUESTO.ToString(); ff.Add(f); } JsonResult jl = Json(ff, JsonRequestBehavior.AllowGet); return(jl); }
public bool ReadExcelFile(string pathToFile) { string zipFilePath = HttpContext.Current.Server.MapPath("~/UploadedFiles"); string extractImagesTo = HttpContext.Current.Server.MapPath("~/UploadedFiles"); string pathToExcelFileOnServer = string.Empty; using (ZipArchive archive = ZipFile.OpenRead(pathToFile)) { foreach (ZipArchiveEntry entry in archive.Entries) { if (entry.FullName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || entry.FullName.EndsWith("xls", StringComparison.OrdinalIgnoreCase)) { if (File.Exists(Path.Combine(zipFilePath, entry.Name))) { File.Delete(Path.Combine(zipFilePath, entry.Name)); } pathToExcelFileOnServer = Path.Combine(zipFilePath, entry.Name); entry.ExtractToFile(Path.Combine(zipFilePath, entry.Name)); } else { if (!entry.FullName.EndsWith(".png", StringComparison.OrdinalIgnoreCase) && !entry.FullName.EndsWith(".jpg", StringComparison.OrdinalIgnoreCase)) { continue; } if (File.Exists(Path.Combine(extractImagesTo, entry.Name))) { File.Delete(Path.Combine(extractImagesTo, entry.Name)); } entry.ExtractToFile(Path.Combine(extractImagesTo, entry.Name)); } } } if (!string.IsNullOrEmpty(pathToExcelFileOnServer)) { using (var stream = File.Open(pathToExcelFileOnServer, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); var dt = new DataTable(); dt.Columns.Add("productID"); dt.Columns.Add("title"); dt.Columns.Add("description"); dt.Columns.Add("price"); dt.Columns.Add("image"); foreach (DataTable table in result.Tables) { foreach (DataRow dr in table.Rows.Cast <DataRow>().Skip(1)) //Skipping header { dt.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString()); } } using (var sqlBulkCopy = new SqlBulkCopy(DbConnectionAbstractClass.ConnectionString)) { sqlBulkCopy.BatchSize = 5000; sqlBulkCopy.DestinationTableName = "dbo.Products"; sqlBulkCopy.ColumnMappings.Add("productId", "productID"); sqlBulkCopy.ColumnMappings.Add("title", "title"); sqlBulkCopy.ColumnMappings.Add("description", "description"); sqlBulkCopy.ColumnMappings.Add("price", "price"); sqlBulkCopy.ColumnMappings.Add("image", "imageName"); sqlBulkCopy.WriteToServer(dt); } } } return(true); } return(false); }
public ActionResult <List <KPIDataSource> > ProcessInput([FromForm] KPIInput input) { using (var stream = input.KPIInputExcel.OpenReadStream()) using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); List <KPIDataSource> kpiDataSources = new List <KPIDataSource>(); foreach (DataTable table in result.Tables) { string dsName = ""; List <int> enumToLookFor = new List <int>(); KPIDataSource kpiDataSource = new KPIDataSource(); try { dsName = table.Rows[0].ItemArray[0].ToString(); if (dsName.EndsWith("Table", StringComparison.InvariantCultureIgnoreCase)) { dsName = dsName.Substring(0, dsName.LastIndexOf("Table", StringComparison.InvariantCultureIgnoreCase)).Trim(); //colNum = int.Parse(table.Rows[0].ItemArray[1].ToString()); kpiDataSource.Name = dsName; // process headers for (int col = 0; col < table.Rows[1].ItemArray.Length; col++) { string label = table.Rows[1].ItemArray[col].ToString(); if (string.IsNullOrEmpty(label)) { continue; } ColumnDetails colDetails = new ColumnDetails(); kpiDataSource.Columns.Add(colDetails); if (label.EndsWith("List")) { colDetails.IsEnumeration = true; colDetails.Label = label.Substring(0, label.LastIndexOf("List", StringComparison.InvariantCultureIgnoreCase)).Trim(); enumToLookFor.Add(col); } else { colDetails.Label = label; } } kpiDataSources.Add(kpiDataSource); if (table.Rows.Count < 3) { continue; } // process data for (int dataRowIndex = 2; dataRowIndex < table.Rows.Count; dataRowIndex++) { for (int dataCol = 0; dataCol < table.Rows[dataRowIndex].ItemArray.Length; dataCol++) { if (enumToLookFor.IndexOf(dataCol) > -1) { kpiDataSource.Columns[dataCol].Enumeration.Add( table.Rows[dataRowIndex].ItemArray[dataCol].ToString()); } } } foreach (var enumCol in enumToLookFor) { kpiDataSource.Columns[enumCol].Enumeration = kpiDataSource.Columns[enumCol].Enumeration.Distinct().ToList(); } } } catch { continue; } } return(kpiDataSources); } }
public List <Student> ReadCSVFile(string filePath) { List <Student> students = new List <Student>(); FileInfo file = new FileInfo(filePath); var fullName = file.FullName; if (file != null && file.Length > 0) { var filepath = fullName; if (file.Extension == ".xls" || file.Extension == ".xlsx") { // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx) System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); //open file and returns as Stream using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var t = reader.AsDataSet(new ExcelDataSetConfiguration() { // Gets or sets a value indicating whether to set the DataColumn.DataType // property in a second pass. UseColumnDataType = true, // Gets or sets a callback to obtain configuration options for a DataTable. ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { // Gets or sets a value indicating the prefix of generated column names. EmptyColumnNamePrefix = "Column", // Gets or sets a value indicating whether to use a row from the // data as column names. UseHeaderRow = true, // Gets or sets a callback to determine which row is the header row. // Only called when UseHeaderRow = true. ReadHeaderRow = (rowReader) => { // F.ex skip the first row and use the 2nd row as column headers: //rowReader.Read(); }, // Gets or sets a callback to determine whether to include the // current row in the DataTable. FilterRow = (rowReader) => { return(true); }, // Gets or sets a callback to determine whether to include the specific // column in the DataTable. Called once per column after reading the // headers. FilterColumn = (rowReader, columnIndex) => { return(true); } } }); foreach (var col in t.Tables[0].Columns) { var c = col as DataColumn; c.ColumnName = c.ColumnName .Replace(" ", "") .Replace("(", "") .Replace(")", "") .Replace("-", "") .Replace("/", ""); } students = t.Tables[0].ToList <Student>(); } } } } return(students); }
public UserDataImportWindowViewModel() { this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.TwitchIDColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.TwitchUsernameColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.MixerUsernameColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.LiveViewingHoursColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.LiveViewingMinutesColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.OfflineViewingHoursColumn)); this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.OfflineViewingMinutesColumn)); foreach (CurrencyModel currency in ChannelSession.Settings.Currency.Values) { this.Columns.Add(new UserDataImportColumnViewModel(currency.Name)); } foreach (UserDataImportColumnViewModel column in this.Columns) { this.columnDictionary[column.Name] = column; } this.UserDataFileBrowseCommand = this.CreateCommand(() => { string filePath = ChannelSession.Services.FileService.ShowOpenFileDialog("Valid Data File Types|*.txt;*.csv;*.xls;*.xlsx"); if (!string.IsNullOrEmpty(filePath)) { this.UserDataFilePath = filePath; } }); this.ImportButtonCommand = this.CreateCommand(async() => { this.ImportButtonState = false; try { int usersImported = 0; int failedImports = 0; if (string.IsNullOrEmpty(this.UserDataFilePath) || !File.Exists(this.UserDataFilePath)) { await DialogHelper.ShowMessage(Resources.InvalidDataFile); return; } if (!this.Columns[0].ColumnNumber.HasValue && !this.Columns[1].ColumnNumber.HasValue) { await DialogHelper.ShowMessage(Resources.DataFileRequiredColumns); return; } List <UserDataImportColumnViewModel> importingColumns = new List <UserDataImportColumnViewModel>(); foreach (UserDataImportColumnViewModel column in this.Columns.Skip(2)) { if (column.ColumnNumber.HasValue && column.ColumnNumber <= 0) { await DialogHelper.ShowMessage(Resources.DataFileInvalidColumn); return; } importingColumns.Add(column); } Dictionary <string, CurrencyModel> nameToCurrencies = new Dictionary <string, CurrencyModel>(); foreach (CurrencyModel currency in ChannelSession.Settings.Currency.Values) { nameToCurrencies[currency.Name] = currency; } List <List <string> > lines = new List <List <string> >(); string extension = Path.GetExtension(this.UserDataFilePath); if (extension.Equals(".txt") || extension.Equals(".csv")) { string fileContents = await ChannelSession.Services.FileService.ReadFile(this.UserDataFilePath); if (!string.IsNullOrEmpty(fileContents)) { foreach (string line in fileContents.Split(new string[] { "\n", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)) { List <string> splits = new List <string>(); foreach (string split in line.Split(new char[] { ' ', '\t', ',', ';' })) { splits.Add(split); } lines.Add(splits); } } else { await DialogHelper.ShowMessage(Resources.DataFileImportFailed); } } else if (extension.Equals(".xls") || extension.Equals(".xlsx")) { using (var stream = File.Open(this.UserDataFilePath, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); if (result.Tables.Count > 0) { for (int i = 0; i < result.Tables[0].Rows.Count; i++) { List <string> values = new List <string>(); for (int j = 0; j < result.Tables[0].Rows[i].ItemArray.Length; j++) { values.Add(result.Tables[0].Rows[i].ItemArray[j].ToString()); } lines.Add(values); } } } } } if (lines.Count > 0) { foreach (List <string> line in lines) { try { long twitchID = 0; if (this.columnDictionary[UserDataImportColumnViewModel.TwitchIDColumn].ArrayNumber >= 0) { long.TryParse(line[this.columnDictionary[UserDataImportColumnViewModel.TwitchIDColumn].ArrayNumber], out twitchID); } string twitchUsername = null; if (this.columnDictionary[UserDataImportColumnViewModel.TwitchUsernameColumn].ArrayNumber >= 0) { twitchUsername = line[this.columnDictionary[UserDataImportColumnViewModel.TwitchUsernameColumn].ArrayNumber]; } string mixerUsername = null; if (this.columnDictionary[UserDataImportColumnViewModel.MixerUsernameColumn].ArrayNumber >= 0) { mixerUsername = line[this.columnDictionary[UserDataImportColumnViewModel.MixerUsernameColumn].ArrayNumber]; } bool newUser = true; UserDataModel user = null; if (twitchID > 0 && !string.IsNullOrEmpty(twitchUsername)) { user = await ChannelSession.Settings.GetUserDataByPlatformID(StreamingPlatformTypeEnum.Twitch, twitchID.ToString()); if (user != null) { newUser = false; } else { UserViewModel userViewModel = await UserViewModel.Create(new Twitch.Base.Models.NewAPI.Users.UserModel() { id = twitchID.ToString(), login = twitchUsername, display_name = twitchUsername, }); user = userViewModel.Data; } } else if (twitchID > 0) { user = await ChannelSession.Settings.GetUserDataByPlatformID(StreamingPlatformTypeEnum.Twitch, twitchID.ToString()); if (user != null) { newUser = false; } else { Twitch.Base.Models.NewAPI.Users.UserModel twitchUser = await ChannelSession.TwitchUserConnection.GetNewAPIUserByID(twitchID.ToString()); if (twitchUser != null) { UserViewModel userViewModel = await UserViewModel.Create(twitchUser); user = userViewModel.Data; } } } else if (!string.IsNullOrEmpty(twitchUsername)) { user = await ChannelSession.Settings.GetUserDataByPlatformUsername(StreamingPlatformTypeEnum.Twitch, twitchUsername); if (user != null) { newUser = false; } else { Twitch.Base.Models.NewAPI.Users.UserModel twitchUser = await ChannelSession.TwitchUserConnection.GetNewAPIUserByLogin(twitchUsername); if (twitchUser != null) { UserViewModel userViewModel = await UserViewModel.Create(twitchUser); user = userViewModel.Data; } } } else if (!string.IsNullOrEmpty(mixerUsername)) { // TODO //#pragma warning disable CS0612 // Type or member is obsolete // UserDataModel mixerUserData = ChannelSession.Settings.GetUserDataByUsername(StreamingPlatformTypeEnum.Mixer, mixerUsername); //#pragma warning restore CS0612 // Type or member is obsolete // if (mixerUserData != null) // { // newUser = false; // } // else // { // user = new UserDataModel() // { // MixerID = uint.MaxValue, // MixerUsername = mixerUsername // }; // } } if (user != null) { if (newUser) { ChannelSession.Settings.SetUserData(user); } int iValue = 0; if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.LiveViewingHoursColumn, line, out iValue)) { user.ViewingHoursPart = iValue; } if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.LiveViewingMinutesColumn, line, out iValue)) { user.ViewingMinutesPart = iValue; } if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.OfflineViewingHoursColumn, line, out iValue)) { user.OfflineViewingMinutes = iValue; } if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.OfflineViewingMinutesColumn, line, out iValue)) { user.OfflineViewingMinutes = iValue; } foreach (var kvp in nameToCurrencies) { if (this.GetIntValueFromLineColumn(kvp.Key, line, out iValue)) { kvp.Value.SetAmount(user, iValue); } } ChannelSession.Settings.UserData.ManualValueChanged(user.ID); usersImported++; this.ImportButtonText = string.Format("{0} {1}", usersImported, MixItUp.Base.Resources.Imported); } else { failedImports++; } } catch (Exception ex) { Logger.Log(LogLevel.Error, "User Data Import Failure - " + line); Logger.Log(ex); failedImports++; } } } await ChannelSession.SaveSettings(); if (failedImports > 0) { await DialogHelper.ShowMessage(string.Format(Resources.ImportWithFailures, usersImported, failedImports)); } else { await DialogHelper.ShowMessage(string.Format(Resources.ImportSuccess, usersImported)); } } catch (Exception ex) { Logger.Log(ex); await DialogHelper.ShowMessage(Resources.ImportFailed); } this.ImportButtonText = MixItUp.Base.Resources.ImportData; this.ImportButtonState = true; }); }
private void GetExclData(string FilePath) { try { //Get Data from Excel File using (FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read)) { IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream); DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true // Use first row as ColumnNames } }); if (result.Tables.Count > 0) { switch (YearSource) { #region Getting Components Data case YearsOf.Components: comlist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_PART { PartNo = r.Field <object>("Part No.").ToString(), PartName = r.Field <object>("Part Name").ToString(), WholeQty = Convert.ToDecimal(r.Field <object>("Whole Qty.")), WholeUnit = r.Field <object>("Whole Unit").ToString(), ConversionQty = Convert.ToDecimal(r.Field <object>("Conversion Qty.")), ConversionUnit = r.Field <object>("Conversion Unit").ToString(), WholePrice = Convert.ToDecimal(r.Field <object>("Whole Price")), ConversionPrice = Convert.ToDecimal(r.Field <object>("Conversion Price")), ExpDate = Convert.ToDateTime(r.Field <object>("Exp. Date")), ExpRateUS = Convert.ToDecimal(r.Field <object>("Exp. Rate(US)")), ExpRateYEN = Convert.ToDecimal(r.Field <object>("Exp. Rate(YEN)")), PreviousPrice = Convert.ToDecimal(r.Field <object>("Previous Price")) }).ToList(); break; #endregion #region Getting Tabulation Data case YearsOf.MaintainanceTable: switch (YearofMaintenanceTable_Sub) { case MaintainanceTableSub.PlasticInjection: pilist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_PI { MoldNo = r.Field <object>("Mold No.").ToString(), MoldName = r.Field <object>("Mold Name").ToString(), Oz = r.Field <object>("Oz").ToString(), PurgePerG = Convert.ToDecimal(r.Field <object>("Purge/Gram")), SPH = Convert.ToDecimal(r.Field <object>("Shots/Hour")), Cavity = Convert.ToDecimal(r.Field <object>("Cavity")), PPH = Convert.ToDecimal(r.Field <object>("Pieces/Hour")) }).ToList(); break; case MaintainanceTableSub.VacuumPlating: vplist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_VP { PartNo = r.Field <object>("Part No.").ToString(), PartName = r.Field <object>("Part Name").ToString(), SourceData = r.Field <object>("Source Data").ToString(), }).ToList(); break; case MaintainanceTableSub.Assembly: assylist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_ASSY { PartNo = r.Field <object>("Part No.").ToString(), PartName = r.Field <object>("Part Name").ToString(), RATEPERHOUR = Convert.ToDecimal(r.Field <object>("Rate/Hour")) }).ToList(); break; } break; #endregion #region Getting Process Data case YearsOf.ProcessSetup: proclist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_PROCESS { SubProcessCode = r.Field <object>("Subprocess Code").ToString(), ProcessCode = r.Field <object>("Process Code").ToString(), ItemDescription = r.Field <object>("Description").ToString(), StandardA = Convert.ToDecimal(r.Field <object>("SP/HC")), StandardB = Convert.ToDecimal(r.Field <object>("Cavity/PH")), Remarks = r.Field <object>("Remarks").ToString() }).ToList(); break; #endregion } } } } catch { throw new Exception("Invalid Type or File is in Use!"); } }
//, out int count public List <PayslipMaster> GetRec(PaySlipMasterViewModel model) { List <PayslipMaster> payslipMasters = new List <PayslipMaster>(); var fileName = $"{Directory.GetCurrentDirectory()}{@"\wwwroot\files"}" + "\\" + model.ExcelFile.FileName; System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); //int cnt = 0; using (var stream = System.IO.File.Open(fileName, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { while (reader.Read()) { if (!reader.GetValue(0).ToString().Contains("SNO")) { bool exists = db.PayslipMasters.Any(p => p.Month == model.Month && p.Year == model.Year && p.EmployeeCode == reader.GetValue(1).ToString()); if (exists) { continue; } MonthsEnum month = GetFinDate(model.Month); var payslip = new PayslipMaster() { EmployeeCode = reader.GetValue(1).ToString(), WorkingDays = Convert.ToInt32(reader.GetValue(2).ToString()), //GrossPay = Convert.ToDouble(reader.GetValue(3).ToString()), Basic = Convert.ToDouble(reader.GetValue(3).ToString()), HRA = Convert.ToDouble(reader.GetValue(4).ToString()), TA = Convert.ToDouble(reader.GetValue(5).ToString()), LTA = Convert.ToDouble(reader.GetValue(6).ToString()), CEA = Convert.ToDouble(reader.GetValue(7).ToString()), SPL = Convert.ToDouble(reader.GetValue(8).ToString()), Arrears = Convert.ToDouble(reader.GetValue(9).ToString()), MonthlyGross = Convert.ToDouble(reader.GetValue(10).ToString()), EmpEPF = Convert.ToDouble(reader.GetValue(11).ToString()), EmpESI = Convert.ToDouble(reader.GetValue(12).ToString()), TAX = Convert.ToDouble(reader.GetValue(13).ToString()), TDS = Convert.ToDouble(reader.GetValue(14).ToString()), OtherDeduction = Convert.ToDouble(reader.GetValue(15).ToString()), TotalDeductions = Convert.ToDouble(reader.GetValue(16).ToString()), TakeHome = Convert.ToDouble(reader.GetValue(17).ToString()), Month = model.Month, Year = model.Year, CreatedBy = User.Identity.Name, CreatedDate = DateTime.Now, FinDate = new DateTime(model.Year, (int)month, (int)month == 3 ? 31 : 1) }; payslipMasters.Add(payslip); //cnt++; } } } } //Inserted = true; //count = cnt; return(payslipMasters); }
public XLSFileReader(FileStream openFileStream) { XlsFileReader = ExcelReaderFactory.CreateReader(openFileStream); }
/// <summary> /// Gets a datareader from an excel file (can be xls or xlsx). /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static IDataReader GetDataReader(string filePath) { var fs = new FileStream(filePath, FileMode.Open); return(ExcelReaderFactory.CreateReader(fs)); }
private void CreateDictionary() { using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); var table = result.Tables[6]; if (!table.Rows[0][0].ToString().Contains("Pos")) { throw new Exception("wrong table"); } int key = -1; string subtype = ""; foreach (DataRow row in table.Rows) { string val = (row[0]).ToString(); int num = -1; if (int.TryParse(val, out num)) { key = num; propertys[key] = row[2].ToString(); subtype = ""; } else if (val.StartsWith("-")) { key = -1; } else if (key > 0) { if (!string.IsNullOrWhiteSpace(row[1].ToString())) { string key_name = subtype + row[1].ToString(); if (descDict[key].ContainsKey(key_name)) { throw new Exception("double entrys in description at key " + key + " with value " + row[1].ToString()); } switch (key) { case 3: break; default: descDict[key].Add(key_name, row[2].ToString()); break; } } else { // subtype description string potential_subkey = row[2].ToString().Trim(); switch (key) { case 6: if (potential_subkey.Contains("ZFL, ZFS, ZFG")) { subtype = "Z_"; } else if (potential_subkey.Contains("AZP")) { subtype = "AZP_"; } break; case 7: if (potential_subkey.Contains("Pumpengröße 5-31cm3")) { subtype = "ZA_"; } else if (potential_subkey.Contains("Pumpengröße 32-50cm3")) { subtype = "ZB_"; } else if (potential_subkey.Contains("Pumpengröße 19cm3")) { subtype = "019_"; } else if (potential_subkey.Contains("Pumpengröße 32-45cm3")) { subtype = "032_"; } else if (potential_subkey.Contains("Pumpengröße 63cm3")) { subtype = "063_"; } else if (potential_subkey.Contains("Pumpengröße 80cm3")) { subtype = "080_"; } else if (potential_subkey.Contains("Pumpengröße 100cm3")) { subtype = "100_"; } else if (potential_subkey.Contains("Pumpengröße 140cm3")) { subtype = "140_"; } break; case 12: if (potential_subkey.Contains("Leistungsregler S1, S2, S3")) { subtype = "S_"; } else if (potential_subkey.Contains("Digitalregler D1…D8")) { subtype = "D_"; } else if (potential_subkey.Contains("Doppelzahnradpumpe")) { subtype = "Z_"; } break; default: break; } } } } } } }
private void btnBrowse_Click(object sender, EventArgs e) { labelSelectChart.Text = ""; labelBasicStacked.Visible = false; labelLineChart.Visible = false; labelDoughNutChart.Visible = false; labelRowChart.Visible = false; labelColumnChart.Visible = false; labelPieChart.Visible = false; labelSelectChart.Visible = false; pictureBoxColumn.Visible = false; pictureBoxPie.Visible = false; pictureBoxRow.Visible = false; pictureBoxDoughNut.Visible = false; pictureBoxStacked.Visible = false; pictureBoxLine.Visible = false; pieChart_Population.Visible = false; cartesianChartStacked.Visible = false; pieChart1.Visible = false; cartesianChartLine.Visible = false; labelLineChart.Visible = false; pictureBoxLine.Visible = false; cartesianChart_Basic_Row.Visible = false; cartesianChartBasicColumn.Visible = false; comboBoxSheetSelected.Text = " "; using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|CSV|*.csv", ValidateNames = true, Multiselect = false }) if (openFileDialog.ShowDialog() == DialogResult.OK) { string path = openFileDialog.FileName; FileInfo info = new FileInfo(path); textBoxFilePath.Text = openFileDialog.FileName; textBoxFileType.Text = info.Extension; textBoxfilename.Text = info.Name; if (info.Extension == ".csv" || info.Extension == "CSV") { labelExcelTable.ResetText(); try { using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read)) { using (IExcelDataReader reader = ExcelReaderFactory.CreateCsvReader(stream)) { result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = false } }); tableCollection = result.Tables; comboBoxSheetSelected.Items.Clear(); foreach (DataTable table in tableCollection) { comboBoxSheetSelected.Items.Add(table.TableName); } labelExcelTable.Text = "Excel Data in Tabular Form is As Follow" + "->Total No of Rows are ::" + reader.RowCount; //DataSet copy = result.Copy(); //firstLiveChart(copy); //if (filena) //if (textBoxfilename.Text == "Pie_Chart.xlsx") //{ // labelPieChart.Visible = true; // pictureBoxPie.Visible = true; //} } } } catch (Exception error) { MessageBox.Show(error.Message, "Wrong File Format Selected", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { labelExcelTable.ResetText(); try { using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read)) { using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) { result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); tableCollection = result.Tables; //result is from the Class dataset (which Stores in memory relational databases) comboBoxSheetSelected.Items.Clear(); foreach (DataTable table in tableCollection) { comboBoxSheetSelected.Items.Add(table.TableName); } labelExcelTable.Text = "Excel Data in Tabular Form is As Follow" + "->Total No of Rows are ::" + reader.RowCount; } } } catch (Exception error) { MessageBox.Show(error.Message, "Wrong File Format Selected", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } }
/// <summary> /// Основная функция для вытягивания из Excel нужных данных /// </summary> private void Sta() { //Сюда сохраняем все листы. Создаем таблицу для всех Листов с названием этого листа. List <DataTable> AllSheets = new List <DataTable>(); //Открываем для чтения наш файл using (var stream = File.Open(FilePath, FileMode.Open, FileAccess.Read)) { //Наш открытый поток читаем как файл Excel using (var reader = ExcelReaderFactory.CreateReader(stream)) { //Номер листа int sheetNumber = 0; //Читаем по листам do { //Название листа var nameList = reader.Name; //Таблица для текущего листа DataTable currentSheet = new DataTable(nameList); //Количество столбцов var columnCount = reader.FieldCount; //Добавляем в таблицу пустые столбцы с названием А+"номер" for (int i = 0; i < columnCount; i++) { //Новый столбец DataColumn columnNew = new DataColumn { DataType = System.Type.GetType("System.String"), ColumnName = "A" + (i + 1) }; //Добавляем столбец в таблицу currentSheet.Columns.Add(columnNew); } //Читаем построчно! while (reader.Read()) { //Новая строка DataRow rowNew = currentSheet.NewRow(); //Проходим по всем столбцам for (int i = 0; i < columnCount; i++) { //Значение ячейки string cellValue = null; //Проверка что текущая ячейка не пуста if (reader.GetValue(i) != null) { //Удаляем лишние пробелы cellValue = reader.GetValue(i).ToString().Trim(); } if (string.IsNullOrWhiteSpace(cellValue)) { cellValue = null; } //Добавляем в строку с название столбца А+"номер" rowNew["A" + (i + 1)] = cellValue; } //Добавляем готовую стоку в таблицу currentSheet.Rows.Add(rowNew); } //Добавляем готовую таблицу листа в Список всех листов AllSheets.Add(currentSheet); //Кол-во листов +1 sheetNumber++; //Читаем следующий лист } while (reader.NextResult()); } } //Получит готовые Курсы GetCourses(AllSheets); //Получить готовый титульный лист GetTitle(AllSheets); //Вывод на консоль ConsoleOut(); }
private async Task <IActionResult> LoadFile(FileInfo selectedFile) { if (selectedFile.Extension.ToUpper() == ".CSV") //not necessary as already filtering... { //use the CSVReader to read in the data string message; DataTable dataTable = CSVConverter.CSVDataToDataTable(selectedFile.OpenRead(), Program.UserCulture); //loading from disk so allow the CSV parser to just use the current culture if (dataTable == null) //then failed to be read { message = "The CSV data needs to be in the correct format."; } else //got datatable but need to check { message = dataTable.CheckDataTable(); } if (message == null) { //save in database await SaveDatasetToDatabase(selectedFile.Name, dataTable); try //to delete but if fails doesn't matter { System.IO.File.Delete(selectedFile.FullName); } catch (IOException) { //ignore error } TempData["InfoMessage"] = "File imported successfully!"; } else { TempData["ErrorMessage"] = message; } return(RedirectToAction("Index")); //return to file loader/loaded files display screen } else if (selectedFile.Extension.ToUpper() == ".XLS" || selectedFile.Extension.ToUpper() == ".XLSX") //excel { //read in xls file using (FileStream stream = selectedFile.OpenRead()) { using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream)) { DataSet excelDataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); //if there is more than one worksheet then ask the user which sheet they want, otherwise automatically load it if (excelDataSet.Tables.Count == 0) { TempData["ErrorMessage"] = "Error reading file. No sheets with valid data could be found in the Excel workbook."; return(RedirectToAction("Index")); } else if (excelDataSet.Tables.Count == 1) //one excel sheet { DataTable dataTable = excelDataSet.Tables[0]; string message = dataTable.CheckDataTable(); if (message == null) { //save in database await SaveDatasetToDatabase(selectedFile.Name, dataTable); try //to delete... { System.IO.File.Delete(selectedFile.FullName); } catch (IOException) { //...if fails doesn't matter } TempData["InfoMessage"] = "File imported successfully!"; } else { TempData["ErrorMessage"] = message; } return(RedirectToAction("Index")); //return to file loader/loaded files display screen } else //more than 1 excel sheet { List <string> tableNames = new List <string>(); foreach (DataTable t in excelDataSet.Tables) { tableNames.Add(t.TableName); } TempData["TableNames"] = tableNames; return(RedirectToAction("SheetSelector", new { filename = selectedFile.FullName })); //go to sheet selection screen } } } } else { throw new ArgumentException("File format not recognised."); } }
public async Task <bool> ImportExcelForLearners(string fileName) { Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); await using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false, } }; var dataSet = reader.AsDataSet(conf); var dataTable = dataSet.Tables[0]; for (var i = 1; i < dataTable.Rows.Count; i++)//Each row of the file { var person = new Person() { NationalId = dataTable.Rows[i].ItemArray[0].ToString(), Title = dataTable.Rows[i].ItemArray[1].ToString(), FirstName = dataTable.Rows[i].ItemArray[2].ToString(), LastName = dataTable.Rows[i].ItemArray[3].ToString(), PersonsDob = DateTime.ParseExact(dataTable.Rows[i].ItemArray[4].ToString(), "yyyy/MM/dd HH:mm:ss", CultureInfo.CurrentCulture), GenderId = (await _lookUpService.GetGenders()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[5].ToString()))?.id, NationalityId = (await _lookUpService.GetNationalities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[6].ToString()))?.id, CitizenshipStatusId = (await _lookUpService.GetCitizenships()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[7].ToString()))?.id, DisabilityStatusId = (await _lookUpService.GetDisabilities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[8].ToString()))?.id, HomeLanguageId = (await _lookUpService.GetHomeLanguages()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[9].ToString()))?.id, EquityId = (await _lookUpService.GetEquities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[10].ToString()))?.id, Email = dataTable.Rows[i].ItemArray[11].ToString(), PhoneNumber = dataTable.Rows[i].ItemArray[12].ToString(), CreatedBy = "DataImport", DateCreated = DateTime.Now, }; var homeAddress = new Address() { HouseNo = dataTable.Rows[i].ItemArray[13].ToString(), StreetName = dataTable.Rows[i].ItemArray[14].ToString(), SuburbId = _lookUpService.GetSuburbs().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[15].ToString()))?.id, CityId = _lookUpService.GetCities().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[16].ToString()))?.id, ProvinceId = _lookUpService.GetProvinces().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[17].ToString()))?.id, CountryId = _lookUpService.GetCountries().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[18].ToString()))?.id, PostalCode = dataTable.Rows[i].ItemArray[19].ToString(), AddressTypeId = 1, CreatedBy = "DataImport", DateCreated = DateTime.Now, }; var postalAddress = new Address() { HouseNo = dataTable.Rows[i].ItemArray[20].ToString(), StreetName = dataTable.Rows[i].ItemArray[21].ToString(), SuburbId = _lookUpService.GetSuburbs().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[22].ToString()))?.id, CityId = _lookUpService.GetCities().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[23].ToString()))?.id, ProvinceId = _lookUpService.GetProvinces().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[24].ToString()))?.id, CountryId = _lookUpService.GetCountries().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[25].ToString()))?.id, PostalCode = dataTable.Rows[i].ItemArray[26].ToString(), AddressTypeId = 1, CreatedBy = "DataImport", DateCreated = DateTime.Now, }; person.Address.Add(homeAddress); person.Address.Add(postalAddress); var learner = new Learner { SchoolId = _lookUpService.GetSchools().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[27].ToString())).id, SchoolGradeId = _lookUpService.GetSchoolGrades().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[28].ToString())).id, YearSchoolCompleted = Utils.GetDate(dataTable.Rows[i].ItemArray[29].ToString()), AppliedYn = Const.FALSE, RecruitedYn = Const.FALSE, CreatedBy = "DataImport", DateCreated = DateTime.Now, Person = person, }; learner.Person = person; _learnerContext.Learner.Add(learner); await _learnerContext.SaveChangesAsync(); } } } return(true); }
public Customer ReadFile(string filePath) { Customer customer = new Customer(); using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { while (reader.Read()) { if (reader.GetFieldType(0) == typeof(string) && reader.GetString(0) != null && reader.GetFieldType(1) == typeof(string)) { string key = reader.GetString(0).Trim(); string value = reader.GetString(1); switch (key) { case "RAZÃO SOCIAL": customer.CompanyName = value; break; case "NOME FANTASIA": customer.FantasyName = value; break; case "ENDEREÇO": customer.Address = value; break; case "BAIRRO": customer.District = value; break; case "CEP": customer.CEP = value; break; case "CIDADE": customer.City = value; break; case "ESTADO": customer.State = value; break; case "CNPJ": customer.CNPJ = value; break; case "I.E.": customer.IE = value; break; case "TELEFONE": customer.Phone = value; break; case "CELULAR": customer.CellularNumber = value; break; case "CONTATO": customer.Contact = value; break; case "E-MAIL": customer.Emails.Add(value); break; default: break; } } else if (reader.GetFieldType(0) == typeof(string) && reader.GetString(0) == null && reader.GetFieldType(1) == typeof(string) && reader.GetString(1) != null) { string value = reader.GetString(1); if (value.Contains("@")) { customer.Emails.Add(value); } } } } } return(customer); }
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1) { var rowsRead = 0; var rowsSkipped = 0; List <string> headerColumns = new List <string>(); List <int> columnIndexes; List <ExcelCell> orderedExcelCells = rootPath.ModeSettings.ExcelModeSettings.GetOrderedExcelCells(); Dictionary <string, object> excelCellsValues = new Dictionary <string, object>(); if (orderedExcelCells.Count > 0) { var cellsStreamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal); using (var memoryStream = new MemoryStream()) { cellsStreamWrapper.Stream.CopyTo(memoryStream); memoryStream.Position = 0; using (var reader = ExcelReaderFactory.CreateReader(cellsStreamWrapper.Stream)) { var currentRow = 0; foreach (var cell in orderedExcelCells) { while (reader.Read() && currentRow < cell.RowIndex) { currentRow++; } var rawValue = reader.GetValue(cell.ColumnIndex); if (!excelCellsValues.TryAdd(cell.ColumnName, rawValue)) { excelCellsValues.Add(cell.GetUniqueName(), rawValue); } currentRow++; } } } // close down stream cellsStreamWrapper.Close(); } var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal); using (var memoryStream = new MemoryStream()) { streamWrapper.Stream.CopyTo(memoryStream); memoryStream.Position = 0; using (var reader = ExcelReaderFactory.CreateReader(memoryStream)) { // skip lines if (rootPath.SkipLines > 0) { while (reader.Read() && rowsSkipped < rootPath.SkipLines) { rowsSkipped++; } } else { reader.Read(); } // get desired column indexes columnIndexes = rootPath.ModeSettings.ExcelModeSettings.GetAllExcelColumnIndexes(); if (columnIndexes.Count == 0) { columnIndexes = Enumerable.Range(0, reader.FieldCount).ToList(); } // get column names foreach (var i in columnIndexes) { if (rootPath.ModeSettings.ExcelModeSettings.HasHeader) { var field = reader.GetValue(i)?.ToString(); if (string.IsNullOrWhiteSpace(field)) { field = $"NO_HEADER_COLUMN_{i}"; } if (headerColumns.Contains(field)) { headerColumns.Add($"{field}_DUPLICATE_{i}"); } else { headerColumns.Add(field); } } else { headerColumns.Add($"COLUMN_{i}"); } } // setup db table var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] ("); foreach (var column in headerColumns) { querySb.Append( $"[{column}] VARCHAR({int.MaxValue}),"); } foreach (var cell in excelCellsValues) { querySb.Append( $"[{cell.Key}] VARCHAR({int.MaxValue}),"); } querySb.Length--; querySb.Append(");"); var query = querySb.ToString(); Logger.Debug($"Create table query: {query}"); var cmd = new SqlDatabaseCommand { Connection = _conn, CommandText = query }; cmd.ExecuteNonQuery(); // prepare insert cmd with parameters querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] ("); foreach (var column in headerColumns) { querySb.Append($"[{column}],"); } foreach (var cell in excelCellsValues) { querySb.Append($"[{cell.Key}],"); } querySb.Length--; querySb.Append(") VALUES ("); foreach (var column in headerColumns) { var paramName = $"@param{headerColumns.IndexOf(column)}"; querySb.Append($"{paramName},"); cmd.Parameters.Add(paramName); } foreach (var cell in excelCellsValues) { var paramName = $"@param{cell.Key.Replace(" ", "")}"; querySb.Append($"{paramName},"); cmd.Parameters.Add(paramName); } querySb.Length--; querySb.Append(");"); query = querySb.ToString(); Logger.Debug($"Insert record query: {query}"); cmd.CommandText = query; // read records var trans = _conn.BeginTransaction(); try { // read all lines from file while (reader.Read() && rowsRead < limit) { foreach (var column in headerColumns) { var rawValue = reader.GetValue(columnIndexes[headerColumns.IndexOf(column)]) ?.ToString(); cmd.Parameters[$"@param{headerColumns.IndexOf(column)}"].Value = rawValue; } foreach (var cell in excelCellsValues) { var rawValue = cell.Value?.ToString(); cmd.Parameters[$"@param{cell.Key.Replace(" ", "")}"].Value = rawValue; } cmd.ExecuteNonQuery(); rowsRead++; // commit every 1000 rows if (rowsRead % 1000 == 0) { trans.Commit(); trans = _conn.BeginTransaction(); } } // commit any pending inserts trans.Commit(); // close down stream streamWrapper.Close(); } catch (Exception e) { // rollback on error trans.Rollback(); Logger.Error(e, e.Message); throw; } } } return(rowsRead); }
public async Task <IActionResult> ParseCompaniesKK(string Url) { try { if (string.IsNullOrEmpty(Url)) { Url = Startup.Configuration["eGovCompaniesDownloadUrl"]; } //скачивание страницы в виде HTML HtmlDocument html = new HtmlDocument(); using (var client = new WebClient()) { using (Stream stream = client.OpenRead(new Uri(Url))) { var reader = new StreamReader(stream, Encoding.UTF8); var html_ = reader.ReadToEnd(); html.LoadHtml(html_); } } // получение ссылок на zip-файлы со скачанной страницы HtmlNode root = html.DocumentNode; List <string> fileUrls = new List <string>(); foreach (HtmlNode node in root.Descendants()) { if (node.Name == "a") { if (node.GetAttributeValue("href", "").Trim().Contains("getImg?id=ESTAT")) { fileUrls.Add($"{(new Uri(Url).Scheme)}://{(new Uri(Url).Host)}{node.GetAttributeValue("href", "").Trim()}"); } } } //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086117"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086118"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086119"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086120"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086121"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086123"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086124"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086125"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086126"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086127"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086128"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086129"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086131"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086132"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086133"); //fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086134"); // скачивание zip-файлов int fileCount = 0; string sContentRootPath = _hostingEnvironment.WebRootPath; sContentRootPath = Path.Combine(sContentRootPath, "Downloads"); List <string> zipFiles = new List <string>(); foreach (string url in fileUrls) { using (var client = new WebClient()) { fileCount++; string fileName = Path.ChangeExtension(fileCount.ToString(), ".zip"); var filePath = Path.Combine(sContentRootPath, Path.GetFileName(fileName)); zipFiles.Add(filePath); client.Headers.Add("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.33 Safari/537.36"); client.DownloadFile(url, filePath); } } // разархивирование zip-файлов List <string> xlsFiles = new List <string>(); foreach (string zipFile in zipFiles) { Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); using (ZipFile zip = ZipFile.Read(zipFile)) { foreach (ZipEntry filefromzip in zip) { filefromzip.Extract(sContentRootPath, ExtractExistingFileAction.OverwriteSilently); xlsFiles.Add(Path.Combine(sContentRootPath, filefromzip.FileName)); } } } //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\11.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\15.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\19.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\23.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\27.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\31.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\35.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\39.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\43.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\47.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\51.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\55.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\59.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\63.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\71.xls"); //xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\75.xls"); // получение данных с Excel-файлов List <CompanyKK> companiesKKNew = new List <CompanyKK>(); foreach (string xlsFile in xlsFiles) { var file = new FileInfo(xlsFile); Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); using (var stream = System.IO.File.Open(xlsFile, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { do { while (reader.Read()) { } } while (reader.NextResult()); var result = reader.AsDataSet(); for (var i = 4; i < result.Tables[0].Rows.Count; i++) { string[] dateRegister = result.Tables[0].Rows[i][3].ToString().Split('.'); companiesKKNew.Add(new CompanyKK() { BIN = result.Tables[0].Rows[i][0].ToString(), NameKK = result.Tables[0].Rows[i][1].ToString(), NameRU = result.Tables[0].Rows[i][2].ToString(), DateRegister = !string.IsNullOrEmpty(result.Tables[0].Rows[i][3].ToString()) ? (DateTime?)(new DateTime(Convert.ToInt32(dateRegister[2]), Convert.ToInt32(dateRegister[1]), Convert.ToInt32(dateRegister[0]))) : null, OKED = result.Tables[0].Rows[i][4].ToString(), ActivityKindKK = result.Tables[0].Rows[i][5].ToString(), ActivityKindRU = result.Tables[0].Rows[i][6].ToString(), OKEDSecondary = result.Tables[0].Rows[i][7].ToString(), KRP = result.Tables[0].Rows[i][8].ToString(), KRPNameKK = result.Tables[0].Rows[i][9].ToString(), KRPNameRU = result.Tables[0].Rows[i][10].ToString(), CATO = result.Tables[0].Rows[i][11].ToString(), LocalityKK = result.Tables[0].Rows[i][12].ToString(), LocalityRU = result.Tables[0].Rows[i][13].ToString(), LegalAddress = result.Tables[0].Rows[i][14].ToString(), HeadName = result.Tables[0].Rows[i][15].ToString() }); } } } } int importedCount = 0, deletedCount = 0; foreach (CompanyKK companyKK in _context.CompanyKK.Where(c => c.ExpiredDateTime == null)) { if (companiesKKNew.FirstOrDefault(c => c.NameRU == companyKK.NameRU) == null) { companyKK.ExpiredDateTime = null; _context.Entry(companyKK).State = EntityState.Modified; deletedCount++; } } foreach (CompanyKK companyKK in companiesKKNew) { if (_context.CompanyKK.FirstOrDefault(c => c.NameRU == companyKK.NameRU) == null) { _context.CompanyKK.Add(companyKK); importedCount++; } } _context.SaveChanges(); //385 830 were //386 603 new return(Ok($"Imported: {importedCount.ToString()}, deleted: {deletedCount.ToString()}")); } catch (Exception exp) { return(Json(exp.Message)); } }
public ActionResult UploadToServer(HttpPostedFileBase file) { ViewBag.Message = null; string _path = null; try { if (file.ContentLength > 0) { string _FileName = Path.GetFileName(file.FileName); _path = Path.Combine(Server.MapPath("~/UploadFiles/"), _FileName); file.SaveAs(_path); } } catch (Exception ErrorMessage) { ViewBag.Message = ErrorMessage.Message; return(View()); } finally { if (ViewBag.Message == null) { FileStream stream = new FileStream(_path, FileMode.OpenOrCreate, FileAccess.Read); var reader = ExcelReaderFactory.CreateReader(stream); if (reader.Name == "Sheet1") { reader.Read(); //SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS;Integrated Security=sspi;database=FileAnalysis"); SqlConnection con = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS; Initial Catalog = FileAnalysis; User ID = sa; Password = Passw0rd@12;"); while (reader.Read()) { int i; con.Open(); SqlCommand cmd = new SqlCommand("insertintoppmdetailsoffile @ProjectNumber,@ProjectName,@ResourceNumber,@ResourceName,@TaskName,@Summary,@DateMentioned,@HoursMentioned,@ResourceRole,@ResourceType,@BillingCode,@ResourceHourlyRate,@ProgrameeProjectManager,@AfeDescrimination,@ProgrameeGroup,@Programee,@ProgrameeManager,@BussinessLead,@UAVP,@ITSABuildingCategory,@FundingCategory,@AFENumber,@ServiceCategory,@BillingRateOnShore,@BillingRateOffShore", con); cmd.Parameters.AddWithValue("@ProjectNumber", reader[0].ToString()); cmd.Parameters.AddWithValue("@ProjectName", reader.GetString(1)); cmd.Parameters.AddWithValue("@ResourceNumber", reader[2].ToString()); cmd.Parameters.AddWithValue("@ResourceName", reader[3].ToString()); cmd.Parameters.AddWithValue("@TaskName", reader.GetString(4)); cmd.Parameters.AddWithValue("@Summary", reader.GetString(5)); cmd.Parameters.AddWithValue("@DateMentioned", reader.GetDateTime(6)); cmd.Parameters.AddWithValue("@HoursMentioned", reader.GetDouble(7)); cmd.Parameters.AddWithValue("@ResourceRole", reader.GetString(8)); cmd.Parameters.AddWithValue("@ResourceType", reader.GetString(9)); cmd.Parameters.AddWithValue("@BillingCode", reader.GetString(10)); cmd.Parameters.AddWithValue("@ResourceHourlyRate", reader.GetDouble(11)); cmd.Parameters.AddWithValue("@ProgrameeProjectManager", reader.GetString(12)); cmd.Parameters.AddWithValue("@AfeDescrimination", reader.GetString(13)); cmd.Parameters.AddWithValue("@ProgrameeGroup", reader.GetString(14)); cmd.Parameters.AddWithValue("@Programee", reader.GetString(15)); cmd.Parameters.AddWithValue("@ProgrameeManager", reader.GetString(16)); cmd.Parameters.AddWithValue("@BussinessLead", reader.GetString(17)); if (reader.GetString(18) == null || reader.GetString(18) == "") { cmd.Parameters.AddWithValue("@UAVP", "Not Avilable"); } else { cmd.Parameters.AddWithValue("@UAVP", reader[18].ToString()); } cmd.Parameters.AddWithValue("@ITSABuildingCategory", reader.GetString(19)); cmd.Parameters.AddWithValue("@FundingCategory", reader.GetString(20)); cmd.Parameters.AddWithValue("@AfeNumber", reader.GetString(21)); cmd.Parameters.AddWithValue("@ServiceCategory", reader.GetString(22)); cmd.Parameters.AddWithValue("@BillingRateOnShore", reader[23].ToString()); cmd.Parameters.AddWithValue("@BillingRateOffShore", reader[24].ToString()); i = cmd.ExecuteNonQuery(); con.Close(); } } MissingDates(); FillingTotalHours(); } } return(RedirectToAction("GettingAll")); }
//POI入库 public string AnalyzePOIData(string filePath, string type) { string result = "ok"; try { if (type == "upload") { db.Execute("delete from T_POI"); } DataTable sourceTable = db.GetDataTable("select top 0 * from T_POI"); DataTable districtTable = db.GetDataTable("select * from D_District"); DataTable poiTable = db.GetDataTable("select * from D_POICategory"); using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var data = reader.AsDataSet(); DataTable poi = data.Tables[0]; for (var i = 1; i < poi.Rows.Count; i++) { DataRow dr = poi.Rows[i]; string district = dr[5].ToString(); int dCode = 0; foreach (DataRow ddr in districtTable.Rows) { if (district == ddr["MC"].ToString()) { dCode = Convert.ToInt32(ddr["DM"]); break; } } int poiCode = 0; string category = dr[1].ToString(); foreach (DataRow ddr in poiTable.Rows) { if (category == ddr["MC"].ToString()) { poiCode = Convert.ToInt32(ddr["DM"]); break; } } DataRow insert = sourceTable.NewRow(); insert["Name"] = dr[0].ToString(); double[] tp = Wgs84ToGcj02(Convert.ToDouble(dr[4]), Convert.ToDouble(dr[3])); insert["Longitude"] = tp[0].ToString(); insert["Latitude"] = tp[1].ToString(); insert["Address"] = dr[2].ToString(); insert["DistrictCode"] = dCode; insert["Area"] = dr[6].ToString(); insert["Category"] = poiCode; if (type == "edit") { db.Execute("delete from T_POI where Name = '" + dr[0].ToString() + "'"); } sourceTable.Rows.Add(insert); } db.ExecuteTransactionScopeInsert(sourceTable, "T_POI"); } } } catch (Exception ex) { result = ex.Message + "||" + ex.StackTrace; } return(result); }
public List <lichHoc> dongBoLicHoc(WebDriverWait wait, ChromeDriver chromeDriver, string MSSV) { if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"))) { System.IO.File.Delete(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls")); } List <lichHoc> records = new List <lichHoc>(); chromeDriver.Url = "http://qldt.actvn.edu.vn/CMCSoft.IU.Web.Info/Reports/Form/StudentTimeTable.aspx"; wait.Until(driver => driver.FindElement(By.Id("drpType")).Displayed); ((IJavaScriptExecutor)chromeDriver).ExecuteScript("document.getElementById('drpType').value = 'B'"); ((IJavaScriptExecutor)chromeDriver).ExecuteScript("document.getElementById('btnView').click()"); for (var i = 0; i < 30; i++) { if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"))) { break; } Thread.Sleep(1000); } chromeDriver.Close(); chromeDriver.Quit(); if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"))) //helps to check if the zip file is present { using (var stream = System.IO.File.Open(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"), FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult()); // 2. Use the AsDataSet extension method DataSet result = reader.AsDataSet(); DataTable dt = result.Tables[0]; //List<string> lstTinh = db.AsEnumerable().Select(x => x["Column0"].ToString()).Distinct().ToList(); if (dt.Rows.Count > 2) { foreach (DataRow row in dt.Rows) { if (dt.Rows.IndexOf(row) > 10) { if (row[0].ToString() != "") { List <DateTime> lstNgayHoc = new List <DateTime>(); string caHoc = row[8].ToString(); string monHoc = row[3].ToString(); string maHocPhan = row[1].ToString(); string lopHocPhan = row[4].ToString(); string giaoVien = row[7].ToString(); DateTime f = DateTime.Parse(row[10].ToString().Split('-')[0]); DateTime t = DateTime.Parse(row[10].ToString().Split('-')[1]); switch (row[0].ToString()) { case "2": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Monday); break; case "3": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Tuesday); break; case "4": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Wednesday); break; case "5": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Thursday); break; case "6": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Friday); break; case "7": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Saturday); break; } for (int i = 0; i < lstNgayHoc.Count; i++) { lichHoc lh = new lichHoc(); lh.mssv = MSSV; lh.lopHocPhan = lopHocPhan; lh.caHoc = caHoc; lh.maHocPhan = maHocPhan; lh.monHoc = monHoc; lh.giaoVien = giaoVien; lh.ngayHoc = lstNgayHoc[i].ToString("dd/MM/yyyy"); records.Add(lh); } } else { return(records); } } } return(records); } else { return(records); } } } } return(records); }
private void ImportExcelGraphikToolStripMenuItem_Click(object sender, EventArgs e) { string filePath = ""; string fileName = ""; DataTableCollection tableCollection; DialogResult dr = MessageBox.Show("При импорте все старые данные будут перезаписаны на новые\n" + "без возможности отмены действия.\n" + "Вы уверены, что хотите импортировать данные?", "Подтверждение действия", MessageBoxButtons.YesNo); if (dr == DialogResult.Yes) { try { string excelPath; using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls" }) { if (openFileDialog.ShowDialog() == DialogResult.OK) { filePath = openFileDialog.FileName; fileName = Path.GetFileName(filePath); this.Cursor = CursorOnLoad.ChangeCoursor(this.Cursor); excelPath = openFileDialog.FileName; using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read)) { using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) { DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { } }); tableCollection = result.Tables; string sqlDelProcess = "DELETE FROM StudyProcess"; using (SqlConnection connection = new SqlConnection(Strings.ConStr))//Удаление старого ГУП { connection.Open(); SqlCommand command = new SqlCommand(sqlDelProcess, connection); command.ExecuteNonQuery(); } foreach (DataTable table in tableCollection) { DataTable dt = tableCollection[table.TableName.ToString()]; dataGridView1.DataSource = dt; int startX = -1, startY = -1; string[] datesStart = new string[0]; string[] datesEnd = new string[0]; int datesLen = 0; DateTime tempDate; int coord; bool horoshVnosit = false; string groupNum = ""; string twoNumGr = ""; for (int i = 0; i < dt.Rows.Count; i++) { coord = 0; for (int j = 0; j < dt.Columns.Count; j++) { if (dataGridView1[j, i].Value.ToString().Trim().ToLower() == "группа") { startX = j; startY = i; } string s = dataGridView1[j, i].Value.ToString().Trim().ToLower(); Regex regex = new Regex(@"^\w*\s?(\d{4}\s?-\s?\d{4})\w*"); MatchCollection matches = regex.Matches(s); if (matches.Count > 0) { string sqlEditGYP = String.Format("UPDATE SystemTable SET StudyProcessPeriod = N'{0}'", matches[0]); using (SqlConnection connection = new SqlConnection(Strings.ConStr)) { connection.Open(); SqlCommand command = new SqlCommand(sqlEditGYP, connection); command.ExecuteNonQuery(); } StudyProcessPeriod(); } if (i == startY + 2 && startY != -1) { if (dataGridView1[j, i].Value.ToString().Trim() != "") { datesLen++; Array.Resize(ref datesStart, datesLen); Array.Resize(ref datesEnd, datesLen); datesStart[datesLen - 1] = dataGridView1[j, i].Value.ToString().Trim(); tempDate = DateTime.Parse(datesStart[datesLen - 1]).AddDays(6); datesEnd[datesLen - 1] = tempDate.ToString("dd/MM/yyyy"); } } if (groupNum != "" && !horoshVnosit) { twoNumGr = groupNum[0].ToString(); twoNumGr += groupNum[1].ToString(); int temp; try { temp = Convert.ToInt32(twoNumGr); } catch { horoshVnosit = true; } } if (i >= startY + 6 && startY != -1 && !horoshVnosit) { if (j == 0) { groupNum = dataGridView1[j, i].Value.ToString().Trim(); } else { if (dataGridView1[j, i].Value.ToString().Trim() != "") { string typeValue = dataGridView1[j, i].Value.ToString().Trim().ToLower(); string sqlAddProcessLine = String.Format("INSERT INTO StudyProcess " + "(GroupNumber, WeekDateStart, WeekDateEnd, WeekType) " + "VALUES (N'{0}',N'{1}',N'{2}',N'{3}')" , groupNum, datesStart[coord], datesEnd[coord], typeValue); using (SqlConnection connect = new SqlConnection(Strings.ConStr)) { connect.Open(); SqlCommand command = new SqlCommand(sqlAddProcessLine, connect); int h = command.ExecuteNonQuery(); if (h == 0) { MessageBox.Show("Error!!"); } } coord++; } } } } } } } } } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } StudyProcessPeriod(); } string nameFromTable = ""; string sqlProv = String.Format("SELECT StydyProcessNowFileName FROM SystemTable"); using (SqlConnection connection = new SqlConnection(Strings.ConStr)) { connection.Open(); SqlCommand command = new SqlCommand(sqlProv, connection); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { reader.Read(); if (!reader.IsDBNull(0)) { nameFromTable = reader.GetString(0); } } } if (nameFromTable != "") { File.Delete(Strings.excel_shablons_folder + nameFromTable); } if (!File.Exists(Strings.excel_shablons_folder + fileName)) { File.Copy(filePath, Strings.excel_shablons_folder + fileName); } string sqlEditStud = String.Format("UPDATE SystemTable SET StydyProcessNowFileName = N'{0}'", fileName); using (SqlConnection connection = new SqlConnection(Strings.ConStr)) { connection.Open(); SqlCommand command = new SqlCommand(sqlEditStud, connection); command.ExecuteNonQuery(); } this.Cursor = CursorOnLoad.ChangeCoursor(this.Cursor); }
public ExcelExtensionReponse Test(string fileName, string[] validationSet, string[] regexSet, string[] columnLengths) { ExcelExtensionReponse excelExtensionReponse = new ExcelExtensionReponse(); string JSONString = string.Empty; IExcelDataReader excelReader; try { FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read); if (fileName.Contains("xlsx")) { excelReader = ExcelReaderFactory.CreateReader(stream); } else { excelReader = ExcelReaderFactory.CreateBinaryReader(stream); } DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); excelReader.Close(); List <string> getValidationErrors; bool getDesiredColumnExistence = ColExistence(result, validationSet, out getValidationErrors); if (getDesiredColumnExistence) { for (int i = 0; i < result.Tables[0].Columns.Count; i++) { bool regexStatus = regexValdiation(result.Tables[0].DefaultView.ToTable(false, result.Tables[0].Columns[i].ColumnName), regexSet[i], Int32.Parse(columnLengths[i])); if (!regexStatus) { throw new Exception("'" + result.Tables[0].Columns[i].ColumnName + "' column has identified invalid data"); } } foreach (string dataColumn in validationSet) { if (DataExtensions.HasNull(result.Tables[0].DefaultView.ToTable(true, dataColumn))) { throw new Exception("'" + dataColumn + "' column has identified invalid data"); } } var regexItem = new Regex("[^0-9a-zA-Z]+"); for (int i = 0; i < result.Tables[0].Columns.Count; i++) { if (regexItem.IsMatch(result.Tables[0].Columns[i].ColumnName.ToString())) { result.Tables[0].Columns[i].ColumnName = "S_" + Regex.Replace(result.Tables[0].Columns[i].ColumnName, @"[^0-9a-zA-Z]+", ""); } } result.AcceptChanges(); JSONString = JsonConvert.SerializeObject(result.Tables[0]); excelExtensionReponse.ExcelExtensionReponseData = JSONString; excelExtensionReponse.success = true; } else { excelExtensionReponse.exception = new ArgumentException("Required Columns " + JsonConvert.SerializeObject(getValidationErrors) + " are not found"); } } catch (Exception ex) { excelExtensionReponse.exception = ex; } return(excelExtensionReponse); }
private List <CaboFrowardMVC.Models.Masivo> UploadRecordsToDataBase(string fileName) { try { string pasaporte = ""; string rut = ""; string dv = ""; string patente = ""; string tipoVehiculo = ""; string lancha = ""; string nave = ""; string chofer = ""; string motivo = ""; string pc = ""; string dvEmpresa = ""; string NombreEmpresa = ""; var records = new List <CaboFrowardMVC.Models.Masivo>(); using (var stream = System.IO.File.Open(Path.Combine(Server.MapPath("~/Content/Files/"), fileName), FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { while (reader.Read()) { if (reader.GetValue(6) != null) { pasaporte = reader.GetValue(6).ToString(); } else { pasaporte = ""; } if (reader.GetValue(1) != null) { rut = reader.GetValue(1).ToString(); } else { rut = ""; } if (reader.GetValue(2) != null) { dv = reader.GetValue(2).ToString(); } else { dv = ""; } if (reader.GetValue(11) != null) { dvEmpresa = reader.GetValue(11).ToString(); } else { dvEmpresa = ""; } if (reader.GetValue(12) != null) { NombreEmpresa = reader.GetValue(12).ToString(); } else { NombreEmpresa = ""; } if (reader.GetValue(13) != null) { patente = reader.GetValue(13).ToString(); } else { patente = ""; } if (reader.GetValue(14) != null) { tipoVehiculo = reader.GetValue(14).ToString(); } else { tipoVehiculo = ""; } if (reader.GetValue(15) != null) { lancha = reader.GetValue(15).ToString(); } else { lancha = ""; } if (reader.GetValue(16) != null) { nave = reader.GetValue(16).ToString(); } else { nave = ""; } if (reader.GetValue(17) != null) { chofer = reader.GetValue(17).ToString(); } else { chofer = "N"; } if (reader.GetValue(18) != null) { motivo = reader.GetValue(18).ToString(); } else { motivo = "0"; } if (reader.GetValue(19) != null) { pc = reader.GetValue(19).ToString(); } else { pc = ""; } if (pasaporte != "" || rut != "") { records.Add(new CaboFrowardMVC.Models.Masivo { idNacionalidad = reader.GetValue(0).ToString(), Rut = rut, Dv = dv, Nombre = reader.GetValue(3).ToString(), ApellidoPat = reader.GetValue(4).ToString(), ApellidoMat = reader.GetValue(5).ToString(), pasaporte = pasaporte, idLocacion = reader.GetValue(7).ToString(), FechaDesde = reader.GetValue(8).ToString(), FechaHasta = reader.GetValue(9).ToString(), RutEmpresa = reader.GetValue(10).ToString(), Patente = patente, TipVehiculo = tipoVehiculo, Lancha = lancha, Nave = nave, Motivo = motivo, PC = pc, DvEmpresa = dvEmpresa, NombreEmpresa = NombreEmpresa, Chofer = chofer }); } } } } return(records); } catch (Exception exp) { String R = exp.Message.ToString(); return(null); } //if (records.Any()) //{ // db.Users.AddRange(records); // db.SaveChanges(); //} }
public ImportResultDto GetImportDetails <T>(IFormFile file, ImportType importOption, int userId) where T : class { if (file.Length <= 0) { return(new ImportResultDto()); } Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); using var ms = new MemoryStream(); file.CopyTo(ms); var reader = ExcelReaderFactory.CreateReader(ms, new ExcelReaderConfiguration() { FallbackEncoding = Encoding.UTF8 }); var workbook = reader.AsDataSet(); if (workbook.Tables.Count <= 0) { return(null); } var table = reader.AsDataSet().Tables[0]; var rows = (from DataRow row in table.Rows select row).ToList(); switch (importOption) { case ImportType.AlternativeNoUpdate: var importDetails = rows.Skip(1).Take(rows.Count() - 1).Select(currentRow => { int.TryParse(currentRow[0].ToString() ?? "0", out var cartonNo); if (currentRow.ItemArray.Length != 2) { throw new ServiceException(new ErrorMessage[] { new ErrorMessage() { Code = string.Empty, Message = $"Invalid file format" } }); } return(new ExcelParseAlternativeNoUpdateViewModel() { AlternativeNo = currentRow[1].ToString(), CartonNo = cartonNo }); }); var result = importDetails.Where(importDetails => importDetails.CartonNo != 0).ToList(); return(_importDataManagerRepository.GetAlternativeNoUpdateResult(result, userId)); case ImportType.DestructionDateUpdate: var importDestructionDetails = rows.Skip(1).Take(rows.Count() - 1).Select(currentRow => { int.TryParse(currentRow[0].ToString() ?? "0", out var cartonNo); if (currentRow.ItemArray.Length != 3) { throw new ServiceException(new ErrorMessage[] { new ErrorMessage() { Code = string.Empty, Message = $"Invalid file format" } }); } return(new ExcelParseDestructioDateUpdateViewModel() { DestructionDate = currentRow[1].ToString(), DestructionTimeFrame = currentRow[2].ToString(), CartonNo = cartonNo }); }); var resultDestruction = importDestructionDetails.Where(importDestructionDetails => importDestructionDetails.CartonNo != 0).ToList(); return(_importDataManagerRepository.GetDestructionDateUpdateResult(resultDestruction, userId)); default: throw new Exception("Import not implemented"); } }
public bool LerExcel(string caminho) { bool sucesso = false; try { FileStream stream = File.Open(caminho, FileMode.Open, FileAccess.Read); using (var reader = ExcelReaderFactory.CreateReader(stream)) { do { bool resultTitulo = false; string tituloCorreto = ""; try { reader.Read(); string titulo = reader.GetString(0); titulo = titulo + ',' + reader.GetString(1); titulo = titulo + ',' + reader.GetString(2); titulo = titulo + ',' + reader.GetString(3); titulo = titulo + ',' + reader.GetString(4); titulo = titulo + ',' + reader.GetString(5); titulo = titulo + ',' + reader.GetString(6); titulo = titulo + ',' + reader.GetString(7); titulo = titulo + ',' + reader.GetString(8); titulo = titulo + ',' + reader.GetString(9); titulo = titulo + ',' + reader.GetString(10); titulo = titulo + ',' + reader.GetString(11); string tituloCor = "Divisao,Unidade,Tipo,Regime,Duracao,Data,Hora,Sala,Disciplina,Turma,Especial,Curso"; tituloCorreto = tituloCor; resultTitulo = string.Compare(titulo, tituloCorreto, true) == 0; } catch { resultTitulo = false; } if (resultTitulo) { while (reader.Read()) { Prova p = new Prova(); try { string nomeTurma = ""; string nomeDisciplina = ""; string nomeCurso = ""; p.Divisao = PreencheDivisao(reader.GetString(0)); p.Unidade = PreencheUnidade(reader.GetString(1)); p.Tipo = PreencheTipoProva(reader.GetString(2)); p.Regime = PreencheTipoCurso(reader.GetString(3)); p.Duracao = reader.GetDateTime(4).TimeOfDay; p.Data = reader.GetDateTime(5); p.Hora = reader.GetDateTime(6).TimeOfDay; try { p.Sala_Codigo = RetornaSala(reader.GetString(7)); } catch { p.Sala_Codigo = RetornaSala(reader.GetDouble(7).ToString()); } nomeDisciplina = reader.GetString(8); nomeCurso = reader.GetString(11); p.Disciplina_Codigo = RetornaDisciplina(nomeDisciplina, TipoCursoConversor.TipoCursoParaNumero(p.Regime), nomeCurso); nomeTurma = reader.GetString(9); p.Turma_Codigo = RetornaTurma(nomeTurma, TipoCursoConversor.TipoCursoParaNumero(p.Regime)); p.Especial = RetornaEspecial(reader.GetString(10)); } catch { System.Windows.Forms.MessageBox.Show("Erro ao validar dados da planilha, por favor, verifique o arquivo e tente novamente.", "Erro"); } if (p.Sala_Codigo <= 0 | p.Disciplina_Codigo <= 0 | p.Turma_Codigo <= 0) { sucesso = false; break; } else { ListaProvas.Add(p); sucesso = true; } sucesso = true; } } else { System.Windows.Forms.MessageBox.Show("Formato de Planilha Incorreto, por favor utilizar uma planilha no formato:\n Divisao,Unidade,Tipo,Regime,Duracao,Data,Hora,Sala,Disciplina,Turma,Especial,Curso", "Erro"); } } while (reader.NextResult()); } } catch { System.Windows.MessageBox.Show("O arquivo está aberto. Por favor, feche-o antes de continuar"); } return(sucesso); }
private void BtnOpen_Click(object sender, EventArgs e) { categoryItemsLength = categoryItems.Count + 1; using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx", ValidateNames = true }) { FileStream fsYear = File.Open("Ekonomi_base_sheets.xlsx", FileMode.Open, FileAccess.Read); FileStream fsMonth = File.Open("seb_" + monthStrings[monthIndex] + "_2020.xlsx", FileMode.Open, FileAccess.Read); IExcelDataReader readerYear, readerMonth; if (ofd.FilterIndex == 1) { readerYear = ExcelReaderFactory.CreateOpenXmlReader(fsYear); readerMonth = ExcelReaderFactory.CreateOpenXmlReader(fsMonth); } else { readerYear = ExcelReaderFactory.CreateReader(fsYear); readerMonth = ExcelReaderFactory.CreateReader(fsMonth); } monthSet = readerMonth.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); yearSet = new DataSet(); DataTable monthTable = new DataTable("Januari"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Februari"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Mars"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("April"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Maj"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Juni"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Juli"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Augusti"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("September"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Oktober"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("November"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("December"); yearSet.Tables.Add(monthTable); monthTable = new DataTable("Summering"); yearSet.Tables.Add(monthTable); // remove unnecessary columns DataTable dt = monthSet.Tables[0]; if (monthIndex > 9) { dt.Columns[3].Expression = ""; } else { dt.Columns.Remove(dt.Columns[5]); dt.Columns.Remove(dt.Columns[2]); dt.Columns.Remove(dt.Columns[1]); } monthSet.Tables[0].AcceptChanges(); foreach (DataRow row in monthSet.Tables[0].Rows) { string year = row[0].ToString(); // remove rows with a an empty description if (row[1] == DBNull.Value) { row.Delete(); } //radera alla rader som inte börjar med ett datum else if (!year.ToString().Contains("2020")) { row.Delete(); } else if (row[1].ToString().Contains("ISK")) { row.Delete(); } else if (row[1].ToString().Contains("PREMIE FÖRS")) { row.Delete(); } else if (Convert.ToInt32(row[2]) > 0) { row.Delete(); } } //important to reorder table when we delete monthSet.Tables[0].AcceptChanges(); // sumerize induvidual caregory monthSet.Tables[0].AcceptChanges(); tempDataTable = monthSet.Tables[0].Clone(); DataRow titleRow = tempDataTable.NewRow(); var hyra = new List <string>() { "HYRES" }; categorization(hyra); var bredband = new List <string>() { "TELEN", "COMV" }; categorization(bredband); var basutgifter = new List <string>() { "DFS", "SPOTIFY", "AEA", "SVEGOT", "WORLDCLASS", "ENKLA VARDAG", "CSN", "EON ", "E ON", "DINA FÖRSÄ" }; categorization(basutgifter); var mat = new List <string>() { "COOP", "NETT", "ICA", "WILLYS", "LIDL", "SELECTA", "PIZZ", "PIZ", "VISUALISERIN", "ENOTEKET", "PRESSBYRÅN", "RESQ CLUB", "GRILLEN", "ESPRESSO", "RESTAURANG", "KROG", "CAFE", "CIRCLE K", "PREEM", "MAX", "KOND", "HEMKÖP", "FALAFEL", "PIGEONSTREET" }; categorization(mat); var sprit = new List <string>() { "SYSTEM", "SALIGA", "ARBIS", "CROMWELL", "LERO", "VÄRDENS", "KARHUSET", "BROOKLYN", "TRÄDGÅR", "LION", "WATTS", "S 12", "O LEARYS", "BAR", "SOFO", "SKANETRAFIKE", "HUSET UNDER", "SODERKELLARE", "BROADWAY", "RESTAURANG K", "BORGEN" }; categorization(sprit); var swish = new List <string>() { "467" }; categorization(swish); var resor = new List <string>() { "ÖSTG", "SNELLTAGET", "SJ INTERNETB", "TAXI", "SJ MOB", "SJ AB" }; categorization(resor); var klader = new List <string>() { "BROTHERS", "MQ", "DRESSMAN INTERNETB", "SELLPY", "HM" }; categorization(klader); // we dont need to insert rows from category 'others' because they are already in source table titleRow = tempDataTable.NewRow(); titleRow[0] = categoryItems[categoryItems.Count - 1].title; tempDataTable.Rows.InsertAt(titleRow, tempDataTable.Rows.Count); // sumerize category others foreach (DataRow row in monthSet.Tables[0].Rows) { categoryItems[categoryItems.Count - 1].sum += Convert.ToInt32(row[2]); } monthSet.Tables[0].AcceptChanges(); int rowIndex = 0; // insert categorized tabel from tempDataTable to monthSet table foreach (DataRow row in tempDataTable.Rows) { DataRow newRow = monthSet.Tables[0].NewRow(); newRow[0] = row[0]; newRow[1] = row[1]; newRow[2] = row[2]; monthSet.Tables[0].Rows.InsertAt(newRow, rowIndex); rowIndex++; } addSumerizedCategories(monthSet, yearSet); readerMonth.Close(); readerYear.Close(); } }
private void Import(bool useOffset) { var result = ImportFileDialog.ShowDialog(this); if (result != DialogResult.OK) { return; } var strings = new Dictionary <string, string>(); try { using (var stream = File.Open(ImportFileDialog.FileName, FileMode.Open, FileAccess.Read)) { // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx) using (var reader = ExcelReaderFactory.CreateReader(stream)) { var content = reader.AsDataSet(); var table = content.Tables[0]; for (var i = 0; i < table.Rows.Count; i++) { string key; string value; if (useOffset) { key = table.Rows[i][0].ToString(); value = table.Rows[i][2].ToString(); } else { key = table.Rows[i][1].ToString(); value = table.Rows[i][2].ToString(); } if (!string.IsNullOrEmpty(key) && !strings.ContainsKey(key)) { strings.Add(key, value); } } } } foreach (var subtitle in _subtitles) { var key = useOffset ? subtitle.Offset.ToString() : subtitle.Text; if (!string.IsNullOrEmpty(key) && strings.ContainsKey(key)) { subtitle.Translation = strings[key]; } } SubtitleGridView.Invalidate(); UpdateLabel(); } catch (Exception e) { MessageBox.Show($"No se ha podido abrir el fichero.\r\n{e.GetType()}: {e.Message}", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static void FillDB(string path, IProgress <double> controller) { FileStream stream; IExcelDataReader reader; stream = File.Open(path.Remove(path.Length - 6, 6) + "xlsx", FileMode.Open, FileAccess.Read); reader = ExcelReaderFactory.CreateReader(stream); SQLiteConnection database; SQLiteCommand command; int currentRow; string format; string transactionType; string Paid = ""; database = new SQLiteConnection("Data Source=" + path + ";Version=3;"); database.Open(); currentRow = 2; //Skip first row reader.Read(); while (reader.Read()) { transactionType = Convert.ToString(reader.GetValue(8)); if (transactionType == null) { break; } else if (Convert.ToString(reader.GetValue(5)).StartsWith("leg")) { //Do nothing - ignore customer events currentRow++; continue; } else if (transactionType == "PaymentCashless") { Paid = Convert.ToString(reader.GetValue(12)).Replace(",", "."); } else if (transactionType == "DebtBuyout" || transactionType == "BusinessBonus" || transactionType == "RechargeWebRefund") { //Ignore reload events currentRow++; continue; } format = "INSERT INTO CardInfo " + "(AccountID, Date, Paid, Product, Machine) values " + "(\"" + Convert.ToString(reader.GetValue(7)) + "\", " + "\"" + Convert.ToString(reader.GetValue(0)) + "\", " + Paid + ", " + "\"" + Convert.ToString(reader.GetValue(10)) + "\"," + "\"" + Convert.ToString(reader.GetValue(14)) + "\")"; command = new SQLiteCommand(format, database); command.ExecuteNonQuery(); currentRow++; controller.Report(Convert.ToDouble(currentRow - 2)); } stream.Close(); stream.Dispose(); database.Close(); }
public async Task SaveClients(IFormFile file) { var a = file.OpenReadStream(); var reader = ExcelReaderFactory.CreateReader(a).AsDataSet(new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }); foreach (DataTable dataTable in reader.Tables) { foreach (DataRow dataRow in dataTable.Rows) { var address = dataRow[1].ToString(); var house = await _context.Houses.FirstOrDefaultAsync(x => x.Address == address); if (house == null) { house = new House { Address = address }; var response = await Geocoder.GeocodeAddress(house.Address); house.Latitude = response.Data.Items[0].Coordinates[0]; house.Longitude = response.Data.Items[0].Coordinates[1]; await _context.Houses.AddAsync(house); await _context.SaveChangesAsync(); var entrancesList = new List <Models.Entrance>(); for (var i = 0; i < response.Data.Items[0].Entrances.Length; i++) { entrancesList.Add(new Models.Entrance { HouseId = house.Id, Number = i + 1, Latitude = response.Data.Items[0].Entrances[i].Coordinates[0], Longitude = response.Data.Items[0].Entrances[i].Coordinates[1] }); } await _context.Entrances.AddRangeAsync(entrancesList); await _context.SaveChangesAsync(); } var enter = await _context.Entrances.FirstOrDefaultAsync(x => x.HouseId == house.Id && x.Number == int.Parse(dataRow[2].ToString())); if (enter != null) { var client = new Client { EntranceId = enter.Id, FullName = dataRow[0].ToString(), ApartmentNumber = int.Parse(dataRow[3].ToString()), PhoneNumber = dataRow[4].ToString(), TariffPlan = dataRow[5].ToString() }; await _context.Clients.Upsert(client).On(x => new { x.EntranceId, x.ApartmentNumber }).RunAsync(); } } } }
public ActionResult UpdateSpreadSheet(HttpPostedFileBase file) { var then = DateTime.UtcNow; var dataSet = GetDataSetFromFile(file.InputStream); var model = DatasetToModel(dataSet); var updatedModel = _service.UpdateStockCheck(model); var stringModel = ModelToStringArray(updatedModel); var now = DateTime.UtcNow; var diff = now - then; return(File(WriteCsvToMemory(stringModel), "text/csv", file.FileName + " Updated " + DateTime.UtcNow.ToString("yyyy-MM-dd") + ".csv")); StockCheckModel DatasetToModel(DataSet ds) { var table = ds.Tables[0]; var columns = GetColumnNames(table); var data = ds.Tables[0].AsEnumerable().Skip(1).Select(x => new StockCheckModelItem { Number = x.Field <object>("Number")?.ToString() ?? "", Name = x.Field <string>("Name") ?? "", ColourName = x.Field <string>("Colour") ?? "", Quantity = x.Field <double>("Quantity"), Condition = x.Field <string>("Cond") ?? "", Description = x.Field <string>("Desc") ?? "", Location = x.Field <object>("Location")?.ToString() ?? "", OtherFields = x.ItemArray.Skip(7).Select(y => y.ToString()) }).ToList(); return(new StockCheckModel { Items = data.ToList(), ColumnNames = columns }); } IEnumerable <string> GetColumnNames(DataTable t) { var names = new List <string>(); var header = t.Rows[0]; var i = 0; header.ItemArray.ForEach(x => { t.Columns[i].ColumnName = x.ToString(); names.Add(x.ToString()); i++; }); return(names); } DataSet GetDataSetFromFile(Stream fileStream) { using (var reader = ExcelReaderFactory.CreateReader(fileStream)) { var thing = reader.HeaderFooter; return(reader.AsDataSet()); } } IEnumerable <IEnumerable <string> > ModelToStringArray(StockCheckModel m) { var arr = m.Items.Select(item => { var strs = new List <string> { item.Number, item.Name, item.ColourName, item.Quantity.ToString(), item.Condition, item.Description, item.Location }; strs = strs.Concat(item.OtherFields).ToList(); return(strs); }).ToList(); arr.Insert(0, m.ColumnNames.ToList()); return(arr); } }