Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
            }
        }
Beispiel #4
0
        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);
            }
        }
Beispiel #5
0
        /// <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);
        }