/// <summary>
/// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
/// </summary>
/// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
/// <param name="configuration">The configuration.</param>
public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
{
workbook = worksheet.Workbook;
this.worksheet = worksheet;
this.configuration = configuration;
FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
}
private void ProcessWorksheet(IXLWorksheet xlWorksheet)
{
if (!_uniqueIdMapping.Any())
_uniqueIdMapping =
_pcfService.GetForChurch(this._churchId)
.Select(i => new { i.UniqueId, i.Id })
.ToDictionary(t => t.UniqueId, t => t.Id);
var rows = xlWorksheet.RowsUsed();
foreach (var row in rows)
{
var pcfUniqueId = row.FirstCellUsed().GetString();
var pcfId =
_uniqueIdMapping.FirstOrDefault(
i => i.Key.Equals(pcfUniqueId, StringComparison.InvariantCultureIgnoreCase)).Value;
if (pcfId != 0)
{
var m = new StagedCells()
{
Name = row.FirstCellUsed().CellRight().GetString(),
UniqueId = UniqueIdGenerator.GenerateUniqueIdForCell(name: row.FirstCellUsed().GetString()),
PCFId = pcfId,
ChurchId = this._churchId
};
_cellService.Insert(m);
}
}
}
private static string GetFieldDataType(IXLWorksheet sheet, int columnIndex)
{
var rows = sheet.RowsUsed()
.Where(x =>
x.Cell(columnIndex).Value != null &&
x.Cell(columnIndex).Value.ToString() != string.Empty)
.ToImmutableArray();
if (rows.Length < 2)
{
return("object");
}
var cell = rows[1].Cell(columnIndex);
if (cell.DataType == XLDataType.Text)
{
return("string");
}
var hasNull = sheet.RowsUsed().Any(x =>
x.Cell(columnIndex).CachedValue == null ||
x.Cell(columnIndex).CachedValue.ToString() == string.Empty);
if (cell.DataType == XLDataType.Number)
{
var isDecimal = rows.Any(x =>
x.Cell(columnIndex).CachedValue != null &&
x.Cell(columnIndex).CachedValue.ToInt32Safe() != x.Cell(columnIndex).CachedValue.ToDecimalSafe());
return((isDecimal ? "decimal" : "int") + (hasNull ? "?" : ""));
}
var dataType = cell.DataType switch
{
XLDataType.Boolean => "bool",
XLDataType.DateTime => "DateTime",
XLDataType.TimeSpan => "TimeSpan",
_ => throw new ArgumentOutOfRangeException(nameof(cell.DataType),
$"Not expected excel column data type: {cell.DataType}")
};
if (hasNull)
{
dataType += "?";
}
return(dataType);
}
}
private void AssertActionSheet(IXLWorksheet worksheet, IList <ExportTagDto> expectedTagData)
{
Assert.IsNotNull(worksheet);
AssertHeadingsInActionsSheet(worksheet);
var expectedData = expectedTagData.SelectMany(t => t.Actions).ToList();
Assert.AreEqual(expectedData.Count + 1, worksheet.RowsUsed().Count());
var rowIdx = 2; // Start at 2 because Row(1) is the header
foreach (var tag in expectedTagData.Where(t => t.Actions.Count > 0))
{
foreach (var action in tag.Actions)
{
var row = worksheet.Row(rowIdx++);
Assert.AreEqual(tag.TagNo, row.Cell(ExcelConverter.ActionSheetColumns.TagNo).Value);
Assert.AreEqual(action.Title, row.Cell(ExcelConverter.ActionSheetColumns.Title).Value);
Assert.AreEqual(action.IsOverDue.ToString().ToUpper(),
row.Cell(ExcelConverter.ActionSheetColumns.OverDue).Value.ToString()?.ToUpper());
Assert.AreEqual(action.Description, row.Cell(ExcelConverter.ActionSheetColumns.Description).Value);
AssertDateTime(action.DueTimeUtc, row.Cell(ExcelConverter.ActionSheetColumns.DueDate));
AssertDateTime(action.ClosedAtUtc, row.Cell(ExcelConverter.ActionSheetColumns.Closed));
}
}
}
private void GetGenreFromWorksheet(IXLWorksheet worksheet)
{
Genre newcat;
var c = (from cat in _context.Genres
where cat.Name.Contains(worksheet.Name)
select cat).ToList();
if (c.Count > 0)
{
newcat = c[0];
}
else
{
newcat = new Genre();
newcat.Name = worksheet.Name;
//newcat.Info = "from EXCEL";
//додати в контекст
_context.Genres.Add(newcat);
}
//перегляд усіх рядків
foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
{
AddBook(row, worksheet, newcat);
}
}
public static DataTable ToDataTable(this IXLWorksheet ws, bool hasHeaderRow = true)
{
int cellIndex = 0;
var dataTable = new DataTable();
foreach (var cell in ws.Row(1).Cells(true))
{
dataTable.Columns.Add(hasHeaderRow ? cell.Value.ToString() : $"Column {++cellIndex}");
}
int rowIndex = 0;
foreach (var row in ws.RowsUsed())
{
if (rowIndex++ == 0 && hasHeaderRow)
{
continue;
}
DataRow dataRow = dataTable.NewRow();
cellIndex = 0;
foreach (var cell in row.CellsUsed())
{
dataRow[cellIndex++] = cell.Value;
}
dataTable.Rows.Add(dataRow);
rowIndex++;
}
return(dataTable);
}
/// <summary>
/// Reads a stream and detects (if specified) the encoding, but falls back to the specified enconding if not detection failed.
/// </summary>
/// <param name="stream"> The stream to read. </param>
public XlsxStreamReader(Stream stream)
{
this.workbook = new XLWorkbook(stream);
this.sheet = workbook.Worksheet(1);
this.rows = sheet.RowsUsed().GetEnumerator();
this.EndOfStream = !this.rows.MoveNext();
}
/// <summary>
/// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
/// </summary>
/// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
/// <param name="configuration">The configuration.</param>
public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
{
workbook = worksheet.Workbook;
this.worksheet = worksheet;
this.configuration = configuration;
FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
}
public List <JobRequest> GetCallList(string filepath)
{
List <JobRequest> jobList = new List <JobRequest>();
try
{
XLWorkbook workbook = new XLWorkbook(filepath);
IXLWorksheet worksheet = workbook.Worksheet(1);
IXLRows rows = worksheet.RowsUsed();
foreach (IXLRow r in rows)
{
if (r.RowNumber() == 1)
{
continue;
}
JobRequest job = new JobRequest();
foreach (IXLCell cell in r.CellsUsed())
{
FillJobRequestInfo(cell, ref job);
}
jobList.Add(job);
}
if (jobList.Count <= 0)
{
throw new Exception("JobRequestInfo List Count is 0");
}
workbook.Dispose();
return(jobList);
}
catch (Exception)
{
throw;
}
}
private void CreateAgencyAndCountry(IXLWorksheet worksheet)
{
SpaceAgencies newAgency;
var a = (from agency in _context.SpaceAgencies
where agency.Name.Contains(worksheet.Name)
select agency).ToList();
if (a.Count > 0)
{
newAgency = a[0];
}
else
{
newAgency = CreateAgency(worksheet);
var row = RowWithIndexInWorksheet(worksheet, 0, 1);
var country = _context.Countires.FirstOrDefault(c => c.CountryName.Contains(row.Cell(3).Value.ToString()));
if (country != null)
{
AttachExistingCountryToAgency(newAgency, country);
}
else
{
country = CreateCountry(worksheet);
_context.Countires.Add(country);
AttachExistingCountryToAgency(newAgency, country);
}
_context.SpaceAgencies.Add(newAgency);
}
foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
{
CreateProgramsForAgency(newAgency, row);
}
}
private void ProcessarLinhas(IXLWorksheet worksheetOriginal, DataTable dataTable)
{
int linhasProcessadas = 0;
bool linhaDoCabecalho = true;
foreach (var linha in worksheetOriginal.RowsUsed())
{
// Ignora processamento para a linha do cabeçalho
if (linhaDoCabecalho)
{
linhaDoCabecalho = false;
continue;
}
if (linhasProcessadas == LINHAS_MAXIMA_POR_ARQUIVO)
{
// Já foram processadas {linhasProcessadas} linhas, finalizando criação de novo excel!
break;
}
dataTable.Rows.Add();
ProcessarColunas(dataTable, linha);
linhasProcessadas++;
}
}
private async Task <List <int> > GetTeamRowsAsync(IXLWorksheet worksheet, string columnName, ExcelCell groupByNameCell)
{
List <int> teamRows = new List <int>();
IXLRows col = await Task.Run(() => worksheet.RowsUsed());
foreach (IXLRow row in col)
{
int columnNumber = XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column);
if (!int.TryParse(await Task.Run(() => Regex.Replace(row.Cell(columnNumber).Address.ToString(), "[^0-9.]", "")), out int currentRowAddress))
{
throw new InvalidCastException("Unable to parse row int from cell address resturned from Excel");
}
if (row.Cell(columnNumber).Value.ToString().Trim() == columnName)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetTeamRowsAsync - Adding { currentRowAddress } to the teamRows list");
}
teamRows.Add(currentRowAddress);
}
}
return(teamRows);
}
public List <Employee> GetEmployees(List <int> ids = null)
{
var resultList = new List <Employee>();
//Get filepath
string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx");
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Loop through the Worksheet rows.
foreach (IXLRow row in workSheet.RowsUsed())
{
var emp = new Employee();
emp.Id = row.Cell(1).GetValue <int>();
emp.FirstName = row.Cell(2).GetValue <string>();
emp.LastName = row.Cell(3).GetValue <string>();
emp.Team = row.Cell(4).GetValue <string>().ToTeam();
emp.Email = row.Cell(5).GetValue <string>();
resultList.Add(emp);
}
}
if (ids != null)
{
resultList = resultList.Where(x => ids.Contains(x.Id)).ToList();
}
return(resultList);
}
protected override void FillData()
{
_worksheet = Workbook.Worksheet(1);
_worksheet.Cell("B2").Value = _worksheet.Cell("E4").Value =
FilterOptions.DateTimeBegin;
_worksheet.Cell("B2").Style.DateFormat.SetFormat("MMMM yyyy");
_worksheet.Cell("E4").Style.DateFormat.SetFormat("MMMM");
var recordNumber = 1;
var rowIndex = _worksheet.RowsUsed().Last().RowNumber() + 2;
var employees = DataOfReport.Select(s => s.Barcode).Distinct();
foreach (var employee in employees)
{
var sheetRecords = DataOfReport.Where(s => s.Barcode == employee).ToList();
var firstRecord = sheetRecords.FirstOrDefault();
AddNewRow(rowIndex);
_worksheet.Cell(rowIndex + 2, 1).Value = recordNumber++;
_worksheet.Cell(rowIndex + 2, 2).Value = firstRecord?.FullName;
_worksheet.Cell(rowIndex + 2, 3).Value = firstRecord?.Post;
foreach (var sheetRecord in sheetRecords)
{
AddShift(rowIndex, sheetRecord.Date.Day, sheetRecord.DayHours, sheetRecord.NightHours);
}
rowIndex += 3;
}
}
public static void FileProcessing(List <Discipline> predmetlist, List <Syllabus> listSyllabus, IXLWorksheet workSheetTitle, IXLWorksheet workSheetPlan, IXLWorksheet workSheetComp)
{
var compDic = CreateCompDic(workSheetComp);
var firstColumn = 'Q' - 'A' + 1;
var lastColumn = ('D' - 'A' + 1) * ('Z' - 'A' + 1);
var firstRow = 6;
var lastRow = workSheetPlan.RowsUsed().Count();
for (int r = firstRow; r < lastRow; r++)
{
var subjectName = workSheetPlan.Cell(r, 3);
if (!string.IsNullOrEmpty(subjectName.Value.ToString()) && !subjectName.Style.Font.Bold)
{
for (int c = firstColumn; c < lastColumn; c += 7)
{
if (!string.IsNullOrEmpty(workSheetPlan.Cell(2, c).Value.ToString()) &&
!string.IsNullOrEmpty(workSheetPlan.Cell(r, c + 1).Value.ToString()))
{
listSyllabus.Add(
new Syllabus(predmetlist, workSheetTitle, workSheetPlan, compDic, r, c)
);
}
}
}
}
}
public static async Task <DataTable> GetDataTableAsync(IMatFileUploadEntry file, List <GridColumnBase> columnList)
{
System.Data.DataTable dataResult = new System.Data.DataTable();
using (var stream = new MemoryStream())
{
await file.WriteToStreamAsync(stream);
using (XLWorkbook workbook = new XLWorkbook(stream))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
bool FirstRow = true;
string readRange = "1:1";
foreach (IXLRow row in worksheet.RowsUsed())
{
if (FirstRow)
{
readRange = GetColumnsNames(columnList, dataResult, row);
FirstRow = false;
}
else
{
AddDatatableRow(dataResult, readRange, row);
}
}
if (FirstRow)
{
throw new System.Exception("No rows in excel");
}
}
}
return(dataResult);
}
public static IEnumerable <ImportModel> EnumerateData(string xlsxpath)
{
// Открываем книгу
using (var workbook = new XLWorkbook(xlsxpath))
{
IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);
{
var totalRows = worksheet.RowsUsed().Count();
// Перебираем диапазон нужных строк
for (int row = 2; row <= totalRows; ++row)
{
// По каждой строке формируем объект
var metric = new ImportModel
{
Name = worksheet.Cell(row, 1).GetValue <string>(),
date_start = Convert.ToDateTime(worksheet.Cell(row, 2).GetValue <string>()),
duration = worksheet.Cell(row, 3).GetValue <int>(),
delay = worksheet.Cell(row, 4).GetValue <int>(),
responsible = worksheet.Cell(row, 6).GetValue <string>(),
};
// И возвращаем его
yield return(metric);
}
}
}
}
private static void ExcelTest()
{
try
{
long loadingTime;
long comparisonTime;
ConsoleUtils.WriteTitle($"Excel files test", 1);
ConsoleUtils.WriteLine();
ConsoleUtils.Write($"Excel file path (default = \n {excelFilePath},\n 'q' to quit) : ");
userInput = Console.ReadLine();
ConsoleUtils.WriteLine();
if (userInput == "q")
{
return;
}
if (!string.IsNullOrEmpty(userInput))
{
excelFilePath = userInput;
}
sw.Restart();
XLWorkbook workbook = new XLWorkbook(excelFilePath);
IXLWorksheet worksheetRef = workbook.Worksheet(1);
IXLWorksheet worksheetNew = workbook.Worksheet(2);
var rowsRef = worksheetRef.RowsUsed();
var rowsNew = worksheetNew.RowsUsed();
sw.Stop();
loadingTime = sw.ElapsedMilliseconds;
ConsoleUtils.WriteLine();
ConsoleUtils.WriteLine($"Excel file loading : {loadingTime} ms");
var excelComparer = new ListComparer <IXLRow>();
var xLRowContentComparer = new XLRowContentComparer();
var xLRowIdentityComparer = new XLRowIdentityComparer();
sw.Restart();
var r = excelComparer.Compare(rowsRef, rowsNew, xLRowIdentityComparer, xLRowContentComparer, (c) => c.RowNumber());
sw.Stop();
comparisonTime = sw.ElapsedMilliseconds;
ConsoleUtils.WriteLine();
ConsoleUtils.WriteLine(ToString(r), InfoType.result);
ConsoleUtils.WriteLine();
ConsoleUtils.WriteLine($"Comparison : {comparisonTime} ms");
executionTime = loadingTime + comparisonTime;
sw.Start();
}
catch (Exception e)
{
ConsoleUtils.WriteLine(e.ToStringEx(), InfoType.error);
}
}
public ActionResult UploadExcel(HttpPostedFileBase file)
{
DataTable dt = new DataTable();
//Checking file content length and Extension must be .xlsx
if (file != null && file.ContentLength > 0 && System.IO.Path.GetExtension(file.FileName).ToLower() == ".xlsx")
{
string path = Path.Combine(Server.MapPath("~/UploadFile"), Path.GetFileName(file.FileName));
//Saving the file
file.SaveAs(path);
//Started reading the Excel file.
using (XLWorkbook workbook = new XLWorkbook(path))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
bool FirstRow = true;
//Range for reading the cells based on the last cell used.
string readRange = "1:1";
foreach (IXLRow row in worksheet.RowsUsed())
{
//If Reading the First Row (used) then add them as column name
if (FirstRow)
{
//Checking the Last cellused for column generation in datatable
readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Columns.Add(cell.Value.ToString());
}
FirstRow = false;
}
else
{
//Adding a Row in datatable
dt.Rows.Add();
int cellIndex = 0;
//Updating the values of datatable
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
cellIndex++;
}
}
}
//If no data in Excel file
if (FirstRow)
{
ViewBag.Message = "Empty Excel File!";
}
Session.Add("ExamTable", dt);
}
}
else
{
//If file extension of the uploaded file is different then .xlsx
ViewBag.Message = "Please select file with .xlsx extension!";
}
return(View(dt));
}
public string[,] ConvertWorksheetTo2dArray(IXLWorksheet worksheet)
{
// Look for the first row used
var firstRowUsed = worksheet.FirstRowUsed();
// Narrow down the row so that it only includes the used part
var residentinfoHeaders = firstRowUsed.RowUsed();
var residentinfo = residentinfoHeaders;
Console.WriteLine($"Show Resident Information: ", residentinfoHeaders.Cell(1).Value);
var i = 1;
while (!residentinfo.Cell(i).IsEmpty())
{
//Console.WriteLine();
Console.WriteLine(residentinfo.Cell(i).Value + "|");
i++;
}
// for(InitializeSomething; BooleanCheckSomething; MutateSomething(s))
//
//
for (var row = firstRowUsed.RowUsed(); !row.IsEmpty(); row = row.RowBelow())
{
foreach (var cell in row.Cells())
{
Console.Write(cell.Value);
Console.Write(" ");
}
Console.WriteLine();
}
//Index's for The Cells in Table
var activeRowCount = worksheet.RowsUsed().Count();
var activeColumnCount = worksheet.ColumnsUsed().Count();
string[,] my2dArrayWithConvertedCells = new string[, ]
{
{ "", "" },
};
//using foreach to create 2d Array
//convert to 2d Array and return the values
var rangeOfFirstNames = worksheet.Range("A1:A5");
foreach (var cell in rangeOfFirstNames.Cells())
{
cell.SetDataType(XLDataType.Text);
var valueofcell = cell.Value.ToString();
}
return(my2dArrayWithConvertedCells);
}
public void Clear()
{
using (var srcRows = _sheet.RowsUsed(true))
foreach (var row in srcRows)
{
row.OutlineLevel = 0;
}
_sheet.Clear();
}
public void Clear()
{
var srcRows = _sheet.RowsUsed(XLCellsUsedOptions.All);
foreach (var row in srcRows)
{
row.OutlineLevel = 0;
}
_sheet.Clear();
}
//public static string CreateUserInsert()
//{
// //
// var userInsertSqlTemplate = "INSERT INTO [ZoneAdminPortal].[dbo].[Users] ([Username],[LastName], [OtherNames] ,[PhoneNo] ,[EmployeeNo], [Email] ,[Designation] ,[Role],[CreationDate] ,[BranchID],[HasApprovalRight], [Status], [Discriminator]) VALUES ('{{username}}', '{{lastname}}', '{{othername}}', '{{phone}}', '{{employee}}', '{{email}}', '{{designation}}',(select top(1) ID from [ZoneAdminPortal].[dbo].[UserRoles] where Name = '{{rolename}}'),'{{date}}',(select top(1) ID from [ZoneAdminPortal].[dbo].[Branch] where Name = '{{branchname}}'), '1','2', 'BranchUser');\n\n";
//}
public static List <GTBankBranches> UploadBTBankBranchesExcel(string filePath = @"C:\temp\GTBank Branches.xlsx")
{
List <GTBankBranches> list = new List <GTBankBranches>();
using (XLWorkbook workbook = new XLWorkbook(filePath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
bool FirstRow = true;
//Range for reading the cells based on the last cell used.
string readRange = "1:6";
foreach (IXLRow row in worksheet.RowsUsed())
{
if (FirstRow)
{
FirstRow = false;
continue;
}
int cellIndex = 1;
var gtbBranchRow = new GTBankBranches();
foreach (IXLCell cell in row.Cells(readRange))
{
switch (cellIndex)
{
case 1:
gtbBranchRow.SN = cell.Value.ToString();
break;
case 2:
gtbBranchRow.BranchCode = cell.Value.ToString();
break;
case 3:
gtbBranchRow.BranchName = cell.Value.ToString();
break;
case 4:
gtbBranchRow.Address = cell.Value.ToString();
break;
case 5:
gtbBranchRow.CityNameBankCode = cell.Value.ToString();
break;
case 6:
gtbBranchRow.State = cell.Value.ToString();
break;
}
;
cellIndex++;
}
list.Add(gtbBranchRow);
}
}
return(list);
}
private static DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
{
DataTable dt = new DataTable();
options = options ?? ReadOptions.DefaultOptions;
//primera fila de títulos
bool firstRow = options.TitlesInFirstRow;
dt.TableName = workSheet.GetNameForDataTable();
if (!options.TitlesInFirstRow)
{
//si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
foreach (var col in workSheet.ColumnsUsed())
{
dt.Columns.Add(col.ColumnLetter());
}
}
Func <IXLRow, bool> getRows = _ => true;
if (options.RowStart != 0)
{
getRows = (r) => (r.RowNumber() >= options.RowStart);
}
foreach (IXLRow row in workSheet.RowsUsed(r => getRows(r)))
{
//Usamos la primera fila para crear las columnas con los títulos
//init with options.TitlesInFirstRow
if (firstRow)
{
foreach (IXLCell cell in row.CellsUsed())
{
dt.Columns.Add(cell.Value?.ToString());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(dt);
}
internal void ParseDataTypeRange()
{
IXLWorksheet dataTypeSheet = Workbook.Worksheets.Worksheet("DataTypes");
IXLRows rows = dataTypeSheet.RowsUsed();
bool firstRow = true;
foreach (IXLRow row in rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
IXLCell typeNameCell = row.Cell("A");
IXLCell nameSpaceCell = row.Cell("B");
IXLCell idCell = row.Cell("C");
string key = typeNameCell.GetString();
if (string.IsNullOrWhiteSpace(key))
{
Logger.Error($"Empty type name in row {row.RowNumber()}");
continue;
}
byte nsByte;
try
{
nsByte = nameSpaceCell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse Namespace of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception);
continue;
}
byte idByte;
try
{
idByte = idCell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse ID of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception);
continue;
}
NodeID dataTypeID = new NodeID
{
Namespace = nsByte,
Value = idByte
};
if (CommonConfig.KnownDataTypes.ContainsKey(key))
{
Logger.Warn($"DataType with name '{key}' already exists. Value will be overwritten from row {row.RowNumber()}");
}
CommonConfig.KnownDataTypes[key] = dataTypeID;
}
}
public void Highlight(IXLWorksheet worksheet, HighlightOptions options)
{
if (worksheet == null)
{
throw new ArgumentNullException("worksheet");
}
var headerRow = worksheet.FirstRowUsed();
var compareRelatedColumns =
FindColumnsBySuffix(headerRow, options.NameOfLeftDataSource, options.NameOfRightDataSource)
.ToList();
var compareRelatedColumnNames = compareRelatedColumns.Select(l => l.Key);
// hide gap & compare result column
foreach (var column in compareRelatedColumns)
{
if (columnNameBuilder.IsGapColumn(column.Key) || columnNameBuilder.IsCompareResultColumn(column.Key))
{
worksheet.Column(column.Value.ColumnNumber).Hide();
}
}
// create formula
var cellValueMissingFormulaFormats = new Dictionary <string, string>();
var cellValueNotEqualFormulaFormats = new Dictionary <string, string>();
var cellValueSimilarFormulaFormats = new Dictionary <string, string>();
var cellValueEqualFormulaFormats = new Dictionary <string, string>();
var underlyingColumns = columnNameBuilder.GetUnderlyingColumnNames(compareRelatedColumnNames, new[] { options.NameOfLeftDataSource, options.NameOfRightDataSource });
foreach (var underlyingColumn in underlyingColumns)
{
var leftColumnName = columnNameBuilder.BuildColumName(options.NameOfLeftDataSource, underlyingColumn);
var rightColumnName = columnNameBuilder.BuildColumName(options.NameOfRightDataSource, underlyingColumn);
var gapColumnName = columnNameBuilder.BuildGapColumnName(underlyingColumn);
var compareColumnName = columnNameBuilder.BuildCompareResultColumnName(underlyingColumn);
var leftColumn =
compareRelatedColumns.FirstOrDefault(l => leftColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var rightColumn =
compareRelatedColumns.FirstOrDefault(l => rightColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var gapColumn =
compareRelatedColumns.FirstOrDefault(l => gapColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var compareColumn =
compareRelatedColumns.FirstOrDefault(l => compareColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
CreateFormula(leftColumn, rightColumn, gapColumn, compareColumn, cellValueMissingFormulaFormats, cellValueSimilarFormulaFormats, cellValueEqualFormulaFormats, cellValueNotEqualFormulaFormats);
}
var contentRows = worksheet.RowsUsed().Skip(1).ToList();
ApplyFormula(contentRows,
cellValueEqualFormulaFormats, cellValueMissingFormulaFormats, cellValueSimilarFormulaFormats, cellValueNotEqualFormulaFormats);
}
private int calcUsedRows()
{
var enumer = worksheet.RowsUsed(false).GetEnumerator();
int count = 0;
while (enumer.MoveNext())
{
++count;
}
return(count);
}
//-> UploadCustomer
public async Task <int> UploadCustomer(UploadCustomer uploadExcel)
{
if (uploadExcel.ExcelFile.ContentLength < 0)
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Not a valid file");
}
if (!uploadExcel.ExcelFile.FileName.EndsWith(".xlsx"))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Only .xlsx is allowed");
}
using (var transaction = db.Database.BeginTransaction())
{
try
{
XLWorkbook Workbook = new XLWorkbook(uploadExcel.ExcelFile.InputStream);
IXLWorksheet WorkSheet = null;
WorkSheet = Workbook.Worksheet("sheet1");
if (!DocumentHelper.SaveExcelFile(uploadExcel.ExcelFile))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Error saving file.");
}
WorkSheet.FirstRow().Delete();//delete header column
int countUpdateRecord = 0;
foreach (var row in WorkSheet.RowsUsed())
{
//var soNumber = row.Cell(1).Value.ToString().Trim().Replace(" ", string.Empty);//Get ist cell. 1 represent column number
//var status = row.Cell(2).Value.ToString().Trim().Replace(" ", string.Empty); ;
var ex = await db.tblCustomers.FirstOrDefaultAsync(x => x.code.ToLower() == row.Cell(1).Value.ToString().Trim().ToLower() && x.deleted == null);
if (ex == null)
{
var record = new tblCustomer();
record.code = row.Cell(1).Value.ToString().Trim();
record.firstName = row.Cell(2).Value.ToString().Trim();
db.tblCustomers.Add(record);
await db.SaveChangesAsync();
countUpdateRecord++;
}
}
transaction.Commit();
return(countUpdateRecord);
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
}
}
private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
{
int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
int ultimaLinhaUsada = worksheet.LastRowUsed().RowNumber();
AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);
worksheet.ShowGridLines = false;
worksheet.ColumnsUsed().AdjustToContents();
worksheet.RowsUsed().AdjustToContents();
}
public static DataTable ReadExcelFile(string filePath)
{
try
{
DataTable dt = new DataTable();
//Started reading the Excel file.
using (XLWorkbook workbook = new XLWorkbook(filePath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
bool FirstRow = true;
//Range for reading the cells based on the last cell used.
string readRange = "1:1";
foreach (IXLRow row in worksheet.RowsUsed())
{
//If Reading the First Row (used) then add them as column name
if (FirstRow)
{
//Checking the Last cellused for column generation in datatable
readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Columns.Add(cell.Value.ToString());
}
FirstRow = false;
}
else
{
//Adding a Row in datatable
dt.Rows.Add();
int cellIndex = 0;
//Updating the values of datatable
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
cellIndex++;
}
}
}
}
return(dt);
}
catch (Exception e)
{
throw e;
}
}
public async Task <List <string> > GetNamesAsync(string excelPath, ExcelCell groupByNameCell)
{
List <string> managerNames = new List <string>();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - Creating a new file stream to extract names from source Excel at { excelPath }");
}
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XLWorkbook excel = new XLWorkbook(fs);
int workSheetCount = excel.Worksheets.Count;
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - workSheetCount = { workSheetCount }");
}
IXLWorksheet worksheet = await Task.Run(() => excel.Worksheet(workSheetCount));
if (_log.IsDebugEnabled)
{
_log.Debug($"Created Worksheet");
}
string nameColumnHeader = worksheet.Row(groupByNameCell.Row)
.Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - nameColumnHeader = { nameColumnHeader }");
}
IXLRows rows = await Task.Run(() => worksheet.RowsUsed());
foreach (IXLRow row in rows)
{
string cellValue = row.Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString().Trim();
if (!(string.IsNullOrEmpty(cellValue) || cellValue == nameColumnHeader))
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetManagerNamesAsync - Adding { cellValue } to manager list");
}
managerNames.Add(cellValue);
}
}
}
return(await Task.Run(() => managerNames.Distinct().ToList()));
}
private DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
{
DataTable dt = new DataTable();
options = options ?? ReadOptions.DefaultOptions;
//TODO: Implementar opción con columnas sin títulos
//primera fila de titulos
bool firstRow = options.TitlesInFirstRow;
dt.TableName = workSheet.GetNameForDataTable();
if (options.TitlesInFirstRow)
{
//si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
foreach (var col in workSheet.ColumnsUsed())
{
dt.Columns.Add(col.ColumnLetter());
}
}
foreach (IXLRow row in workSheet.RowsUsed())
{
//Usamos la primera fila para crear las columnas con los títulos
//init with options.TitlesInFirstRow
if (firstRow)
{
foreach (IXLCell cell in row.CellsUsed())
{
dt.Columns.Add(cell.GetContentWithOutSpaces());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(dt);
}
public static void AutoFitWorksheet(IXLWorksheet ws, int startCol, int startRow, double lineHeight)
{
foreach (var row in ws.RowsUsed().Skip(startRow))
{
var maxLines = 1.0;
foreach (var cell in row.CellsUsed().Skip(startCol))
{
if (cell.Value != null)
{
var lines = cell.Value.ToString().Length / (cell.WorksheetColumn().Width * 1.15);
maxLines = maxLines < lines ? lines : maxLines;
}
}
row.Height = lineHeight * Math.Ceiling(maxLines);
}
}
private void ProcessWorkSheet(IXLWorksheet worksheet)
{
if (worksheet != null)
{
foreach (var row in worksheet.RowsUsed())
{
if (row != null && row.RowBelow() != null)
{
var currentRow = row.RowBelow();
if (currentRow != null && currentRow.CellsUsed().Any())
{
var partnerUniqueId = currentRow.FirstCellUsed().GetString();
var armShort = currentRow.FirstCellUsed().CellRight(1).GetString();
var amount = currentRow.FirstCellUsed().CellRight(2).GetDouble();
var currency = currentRow.FirstCellUsed().CellRight(3).GetString();
var month = currentRow.FirstCellUsed().CellRight(4).GetString();
var year = currentRow.FirstCellUsed().CellRight(5).GetString();
var armId = this._partnershipArmMappings.ContainsKey(armShort)
? this._partnershipArmMappings.FirstOrDefault(i => i.Key.Equals(armShort)).Value
: 0;
var currencyId = this._currencySymbol.ContainsKey(currency)
? this._currencySymbol.FirstOrDefault(i => i.Key.Equals(currency)).Value
: 0;
var partnerId = this._partnerIdMappings.ContainsKey(partnerUniqueId)
? this._partnerIdMappings.FirstOrDefault(i => i.Key.Equals(partnerUniqueId)).Value
: 0;
var item = new StagedPartnership()
{
Amount = (decimal) amount,
ArmId = armId,
ChurchId = _churchId,
CurrencyId = currencyId,
DateCreated = DateTime.Now,
Month = 1,
PartnerId = partnerId,
Year = Convert.ToInt32(year)
};
_stagedPartnershipService.Insert(item);
}
}
}
}
}
private void ProcessWorksheet(IXLWorksheet workSheet)
{
if (workSheet != null)
{
foreach (var row in workSheet.RowsUsed())
{
if (row != null && row.RowBelow() != null)
{
var currentRow = row.RowBelow();
if (currentRow != null && currentRow.CellsUsed().Any())
{
var title = currentRow.FirstCellUsed().GetString();
var firstName = currentRow.FirstCellUsed().CellRight(1).GetString();
var lastName = currentRow.FirstCellUsed().CellRight(2).GetString();
var email = currentRow.FirstCellUsed().CellRight(3).GetString();
var phone = currentRow.FirstCellUsed().CellRight(4).GetString();
var gender = currentRow.FirstCellUsed().CellRight(5).GetString();
var dateOfBirth = currentRow.FirstCellUsed().CellRight(6).GetDateTime();
var pcfUniqueId = currentRow.FirstCellUsed().CellRight(7).GetString();
var cellUniqueId = currentRow.FirstCellUsed().CellRight(8).GetString();
var pcfId = this._pcfIdMappings.ContainsKey(pcfUniqueId) ? this._pcfIdMappings.FirstOrDefault(i => i.Key.Equals(pcfUniqueId)).Value : 0;
var cellId = this._cellIdMappings.ContainsKey(cellUniqueId) ? this._cellIdMappings.FirstOrDefault(i => i.Key.Equals(cellUniqueId)).Value : 0;
var item = new StagedPartner
{
ChurchId = this._churchId,
DateCreated = DateTime.Now,
Email = email,
Phone = phone,
DateOfBirth = dateOfBirth,
Title = title,
FirstName = firstName,
LastName = lastName,
PCFId = pcfId,
CellId = cellId,
Gender = gender,
UniqueId = IDGenerators.UniqueIdGenerator.GenerateUniqueIdForPartner(firstName + lastName),
};
_stagedPartnerService.Insert(item);
}
}
}
}
}
private void ProcessWorksheet(IXLWorksheet xlWorksheet)
{
if(!_uniqueIdIdMapping.Any() )
_uniqueIdIdMapping = _zoneService.GetAll().Select(i => new {i.UniqueId, i.Id}).ToDictionary(t => t.UniqueId, t => t.Id);
var rows = xlWorksheet.RowsUsed();
foreach (var row in rows)
{
var parentUniqueId = row.FirstCellUsed().GetString();
var parentId =
_uniqueIdIdMapping.FirstOrDefault(i => i.Key.Equals(parentUniqueId, StringComparison.InvariantCultureIgnoreCase)).Value;
var name = row.FirstCellUsed().CellRight().GetString();
var m = new StagedGroup()
{
ZoneId = parentId,
Name = name,
UniqueId = UniqueIdGenerator.GenerateUniqueIdForGroup(name)
};
_groupService.Insert(m);
}
}
public static List<TranslationRow> LoadRows(IXLWorksheet worksheet)
{
var result = new List<TranslationRow>();
var cultures = ReadCultures(worksheet).ToList();
foreach (var row in worksheet.RowsUsed().Skip(1))
{
var textValues = row.Cells(1, (cultures.Count * 2) + 2).Select(cell => (cell.Value != null ? cell.Value.ToString() : null)).ToList();
if (textValues.Any())
{
var customer = new TranslationRow
{
ID = textValues[0],
Key = textValues[1]
};
foreach(var culture in cultures)
{
if (culture.TextColumnIndex > 0 && culture.TextColumnIndex < textValues.Count &&
!String.IsNullOrWhiteSpace(textValues[culture.TextColumnIndex]))
customer.Translations.Add(culture.Culture, textValues[culture.TextColumnIndex]);
if (culture.CommentColumnIndex > 0 && culture.CommentColumnIndex < textValues.Count
&& !String.IsNullOrWhiteSpace(textValues[culture.CommentColumnIndex]))
customer.Comments.Add(culture.Culture, textValues[culture.CommentColumnIndex]);
}
result.Add(customer);
}
else
{
break;
}
}
return result;
}
