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