public void TableShowHeader()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C");
ws.RangeUsed().CreateTable().SetShowHeaderRow(false);
IXLTable table = ws.Tables.First();
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox1.xlsx");
Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true));
Assert.AreEqual(null, table.HeadersRow());
Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
Assert.AreEqual("C", table.DataRange.LastCell().GetString());
table.SetShowHeaderRow();
IXLRangeRow headerRow = table.HeadersRow();
Assert.AreNotEqual(null, headerRow);
Assert.AreEqual("Categories", headerRow.Cell(1).GetString());
table.SetShowHeaderRow(false);
ws.FirstCell().SetValue("x");
table.SetShowHeaderRow();
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox2.xlsx");
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox3.xlsx");
Assert.AreEqual("x", ws.FirstCell().GetString());
Assert.AreEqual("Categories", ws.Cell("A2").GetString());
Assert.AreNotEqual(null, headerRow);
Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
Assert.AreEqual("C", table.DataRange.LastCell().GetString());
}
public void TableShowHeader()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C");
IXLTable table = ws.RangeUsed().CreateTable();
Assert.AreEqual("Categories", table.Fields.First().Name);
table.SetShowHeaderRow(false);
Assert.AreEqual("Categories", table.Fields.First().Name);
Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true));
Assert.AreEqual(null, table.HeadersRow());
Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
Assert.AreEqual("C", table.DataRange.LastCell().GetString());
table.SetShowHeaderRow();
IXLRangeRow headerRow = table.HeadersRow();
Assert.AreNotEqual(null, headerRow);
Assert.AreEqual("Categories", headerRow.Cell(1).GetString());
table.SetShowHeaderRow(false);
ws.FirstCell().SetValue("x");
table.SetShowHeaderRow();
Assert.AreEqual("x", ws.FirstCell().GetString());
Assert.AreEqual("Categories", ws.Cell("A2").GetString());
Assert.AreNotEqual(null, headerRow);
Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
Assert.AreEqual("C", table.DataRange.LastCell().GetString());
}
}
public string GetExcelColumnLetter(IXLTable table, string columnHeader)
{
var cell = table.HeadersRow().CellsUsed(c => c.Value.ToString() == columnHeader).FirstOrDefault();
if (cell != null)
{
return(cell.WorksheetColumn().ColumnLetter());
}
return(null);
}
private string GetFullHeaderName(string fragmentNazwyNaglowka, IXLTable table)
{
var headers = new List <string>();
foreach (var cell in table.HeadersRow().Cells())
{
headers.Add(cell.GetString());
}
var fullHeaderName = headers.Where(s => s.ToLower().Contains(fragmentNazwyNaglowka.ToLower()))
.FirstOrDefault();
return(fullHeaderName);
}
public void Inserting_Column_Sets_Header()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C");
IXLTable table = ws.RangeUsed().CreateTable();
table.InsertColumnsAfter(1);
Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString());
}
}
private void Validator_Column(List <TemplateFormatCAC> templateCAC, IXLTable table)
{
try
{
using (table)
{
DTOValidacionArchivo validation = null;
foreach (TemplateFormatCAC template in templateCAC)
{
// Si el numero de columnas del formato es diferente a las del archivo
if (templateCAC.Count != table.HeadersRow().CellCount())
{
string concatHeader = "";
table.HeadersRow().CellsUsed().Select(m => m.Value.ToString()).ToList().Except(templateCAC.Select(m => m.Name).ToList()).ForEach(m => concatHeader = $"{concatHeader} {m}");
//table.HeadersRow().CellsUsed().ForEach(m => concatHeader = $"{concatHeader} {m.Value}");
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_COLUMNS_NOT_EQUALS, table.HeadersRow().CellCount(), templateCAC.Count),
Valor = $"{concatHeader}",
Celda = $"{table.HeadersRow().RangeAddress}",
Fila = $"{table.HeadersRow().RangeAddress.FirstAddress.RowNumber}",
Columna = $"{table.HeadersRow().RangeAddress.LastAddress.ColumnLetter}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Column), validation.Columna, validation.Valor, "ERROR_COLUMNS_NOT_EQUALS", validation.ToString(), table.Worksheet.Name));
validator_result.Add(validation);
}
var field = table.Fields.Where(m => m.Name == template.Name).FirstOrDefault();
//Validacion de existencia de columna
if (field == null)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_COLUMN_MISSING_NAME, template.Name),
Valor = template.Name,
Celda = $"A1",
Fila = $"1",
Columna = $"A"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Column), validation.Columna, validation.Valor, "ERROR_COLUMN_MISSING_NAME", validation.ToString(), table.Worksheet.Name));
validator_result.Add(validation);
}
else
{
int field_index = field.Index + 1;
IXLCells cells = table.Column(field_index).CellsUsed();
var values = cells.Select(m => m.Value).ToList();
values.RemoveAll(item => item == null || item.ToString() == "");
var duplicateKeys = values.GroupBy(x => x).Where(group => group.Count() > 1).Select(group => group.Key);
//Validacion duplicados
bool hasDuplicates = template.Duplicate == false && duplicateKeys.Count() > 0;
//Validación de existencia de filas
if (table.Column(field_index).CellsUsed().Count() == 0)
{
var first = cells.OrderBy(m => m.Address).FirstOrDefault() != null?cells.OrderBy(m => m.Address).FirstOrDefault().Address.ToString() : "";
var last = cells.OrderByDescending(m => m.Address).FirstOrDefault() != null?cells.OrderByDescending(m => m.Address).FirstOrDefault().Address.ToString() : "";
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_COLUMN_NOT_DATA, template.Name),
Valor = "",
Celda = $"{first}:{last}",
Fila = $"{table.HeadersRow().RangeAddress.FirstAddress.RowNumber}",
Columna = $"{table.HeadersRow().RangeAddress.LastAddress.ColumnLetter}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Column), validation.Columna, validation.Valor, "ERROR_COLUMN_NOT_DATA", validation.ToString(), table.Worksheet.Name));
validator_result.Add(validation);
}
if (hasDuplicates)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_COLUMN_NOT_DUPLICATE_DATA, template.Name),
Valor = "",
Celda = $"{table.Column(field.Name).RangeAddress}",
Fila = $"{table.Column(field.Name).RangeAddress.FirstAddress.RowNumber}",
Columna = $"{table.Column(field.Name).RangeAddress.LastAddress.ColumnLetter}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Column), validation.Columna, validation.Valor, "ERROR_COLUMN_NOT_DUPLICATE_DATA", validation.ToString(), table.Worksheet.Name));
validator_result.Add(validation);
}
}
}
}
}
catch (Exception ex)
{
validator_result.Add(ExceptionWriter(ex));
throw ex;
}
}
private void Validator_Row(List <TemplateFormatCAC> templateCAC, IXLTable table)
{
try
{
using (table)
{
DTOValidacionArchivo validation;
int header_index = table.HeadersRow().RowNumber();
int rowCount = table.RowsUsed().Count();
for (int row_index = 1; row_index <= rowCount; row_index++)
{
var row = table.Row(row_index);
if (row_index != header_index)
{
bool existErrors = false;
row.DeleteComments();
row.Clear(XLClearOptions.Formats);
foreach (var cell in row.Cells())
{
int columnNumber = cell.Address.ColumnNumber - 1 > 0 ? (cell.Address.ColumnNumber - 1) : 0;
var field = table.Fields.Where(m => m.Index == columnNumber).FirstOrDefault();
var headerrow = field != null?templateCAC.Where(m => m.Name == field.Name).FirstOrDefault() : null;
if (headerrow != null)
{
bool response = Validator_Cell(headerrow, cell);
if (response)
{
existErrors = response;
}
}
else
{
var fieldname = field != null ? field.Name : "";
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_COLUMN_NOT_ALLOWED, fieldname),
Valor = cell.Value.ToString(),
Celda = $"{cell.Address.ColumnLetter}{cell.Address.RowNumber}",
Fila = $"{row_index}",
Columna = $"{cell.Address.ColumnLetter}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Row), validation.Columna, validation.Valor, "ERROR_COLUMN_NOT_ALLOWED", validation.ToString(), table.Worksheet.Name));
validator_result.Add(validation);
}
}
if (existErrors == false)
{
Auditor.SaveLog(null, table.Worksheet.Name, row_index, row);
}
existErrors = false;
}
Auditor.SaveLog("true", table.Worksheet.Name, row_index, row);
}
}
}
catch (Exception ex)
{
validator_result.Add(ExceptionWriter(ex));
throw ex;
}
}