/// <summary> /// Return entity from xlsx file by rowNum /// </summary> /// <param name="rowNum"></param> /// <returns></returns> private Models.AuthUser GetEntry(int rowNum, string FileName, string Folder) { Models.AuthUser user = new Models.AuthUser(); string FileShareResources = System.Configuration.ConfigurationManager.AppSettings["FileShareResources"].ToString(); string PhonesDruzhba = System.IO.Path.Combine(FileShareResources, "Phones", Folder, FileName); int f = 0; using (var sr = System.IO.File.OpenRead(PhonesDruzhba)) { NPOI.XSSF.UserModel.XSSFWorkbook wb = new NPOI.XSSF.UserModel.XSSFWorkbook(sr); user.isDep = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[1] == null || wb.GetSheet("TDSheet").GetRow(rowNum).Cells[1].ToString().Trim() == "" || int.TryParse(wb.GetSheet("TDSheet").GetRow(rowNum).Cells[1].ToString(), out f); if (user.isDep) { user.DepName = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[0].ToString(); } else { user.FIO = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[1].ToString(); user.post = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[0].ToString(); user.Birthday = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[4].ToString(); user.email = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[8].ToString(); user.phone = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[5].ToString(); user.mobile = wb.GetSheet("TDSheet").GetRow(rowNum).Cells[7].ToString(); } } return(user); }
/// <summary> /// return tree from file /// </summary> /// <param name="range"></param> /// <param name="FileName"></param> /// <param name="Folder"></param> /// <param name="codeOffset"></param> /// <returns></returns> private List <Models.LevelRange> GetSubRanges(Models.LevelRange range, string FileName, string Folder, int codeOffset) { #region ---Get sub ranges string FileShareResources = System.Configuration.ConfigurationManager.AppSettings["FileShareResources"].ToString(); string PhonesDruzhba = System.IO.Path.Combine(FileShareResources, "Phones", Folder, FileName); List <Models.LevelRange> ranges = new List <Models.LevelRange>(); if (range.endRow > range.startRow) { using (var sr = System.IO.File.OpenRead(PhonesDruzhba)) { NPOI.XSSF.UserModel.XSSFWorkbook wb = new NPOI.XSSF.UserModel.XSSFWorkbook(sr); int searchLevel = range.level + 1; int currPosition = range.startRow; int endRangePosition = currPosition; int rangeNumber = 0; while (currPosition <= range.endRow) { if (wb.GetSheet("TDSheet").GetRow(currPosition).Cells[3] != null && wb.GetSheet("TDSheet").GetRow(currPosition).Cells[3].ToString().Trim() == searchLevel.ToString()) { ranges.Add(new Models.LevelRange() { startRow = currPosition + 1, level = searchLevel, code = range.level == 0? range.code = wb.GetSheet("TDSheet").GetRow(currPosition).Cells[1].ToString() : range.code + "." + (codeOffset + rangeNumber).ToString(), entry = GetEntry(currPosition, FileName, Folder) }); if (rangeNumber > 0) { ranges[rangeNumber - 1].endRow = currPosition - 1; } rangeNumber += 1; } currPosition += 1; } ranges[rangeNumber - 1].endRow = currPosition - 1; } } #endregion if (ranges.Count > 0) { range.childLevels = ranges; foreach (var cl in range.childLevels) { cl.childLevels = GetSubRanges(cl, FileName, Folder, codeOffset); SaveEntry(cl, FileName); } return(range.childLevels); } else { return(new List <Models.LevelRange>()); } }
private void SiftButton_Click(object sender, EventArgs e) { try { string filenm = BrowseTB1.Text; if (SheetNameTB.Text == "") { MessageBox.Show("Please Enter a Sheet name."); return; } //This code re-generates the entire Building/Room for all of campus (as listed in the main inventory file) //TODO: change path to generic OneDrive Path for inventory, BasicConfig, and Access Control, revise and rename sheet as well. //TODO: Add xml Wrappers <root> or whatever. This is a complete generation. NPOI.XSSF.UserModel.XSSFWorkbook xssfwb; using (FileStream file = new FileStream(filenm, FileMode.Open, FileAccess.Read)) { xssfwb = new NPOI.XSSF.UserModel.XSSFWorkbook(file); } ISheet sheet = xssfwb.GetSheet(SheetNameTB.Text); int numRows = sheet.LastRowNum; int numCols = sheet.GetRow(0).LastCellNum; List <string> values = new List <string>(); try { using (StreamWriter configFile = File.AppendText(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\SiftedResults.txt")) { for (int row = hasHeaderRow.Checked ? 1 : 0; row <= numRows; row++) //That's dope { for (int col = 0; col < numCols; col++) { string cellVal = sheet.GetRow(row).GetCell(col).StringCellValue; if (!values.Contains(cellVal)) { values.Add(cellVal); } } } foreach (string val in values) { configFile.WriteLine(val); } MessageBox.Show("The Results have been saved to your Desktop."); RestartButton_Click(sender, e); } } catch (NullReferenceException) //Hey man, it just happens sometimes. { //Do nothing } } catch (Exception er) { MessageBox.Show("Error: " + er); } }
private int GetLastRow(string FileName, string Folder) { string FileShareResources = System.Configuration.ConfigurationManager.AppSettings["FileShareResources"].ToString(); string PhonesDruzhba = System.IO.Path.Combine(FileShareResources, "Phones", Folder, FileName); using (var sr = System.IO.File.OpenRead(PhonesDruzhba)) { NPOI.XSSF.UserModel.XSSFWorkbook wb = new NPOI.XSSF.UserModel.XSSFWorkbook(sr); return(wb.GetSheet("TDSheet").LastRowNum); } }
/// <summary> /// Carga los datos desde la hoja de las hojas de Excel especificadas y las /// almacena en la variable estática _rowDataList. /// </summary> /// <returns>True si la lista tiene elementos.</returns> public bool LoadData() { if (_rowDataList == null) { var watch = new Stopwatch(); watch.Start(); _rowDataList = new ConcurrentBag <T>(); int index = -1; try { using (var fs = File.OpenRead(XlsFullPath)) { var workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); _formulaEvaluator = workBook.GetCreationHelper().CreateFormulaEvaluator(); var mapperConfig = MappersConfig.FirstOrDefault(); bool hasMoreMappers = MappersConfig.Count > 1; foreach (var sheetConfig in SheetsConfig) { var sheet = workBook.GetSheet(sheetConfig.SheetName); if (hasMoreMappers) { mapperConfig = MappersConfig.FirstOrDefault(m => m.Name == sheetConfig.Map); } for (int rowIndex = sheetConfig.RowNumberStartData - 1; rowIndex < sheetConfig.RowNumberStopData; rowIndex++) { index = rowIndex; var row = sheet.GetRow(rowIndex); if (row == null) { continue; } var obj = FillObject(mapperConfig, row); //Llenar propiedades derivadas de otras CalculateFields(ref obj); _rowDataList.Add(obj); } } } } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } finally { watch.Stop(); _timeElapsed = watch.ElapsedMilliseconds; } } GC.Collect(); return(_rowDataList.Count > 0); }