/// <summary>
/// Метод по получению списка объектов справочника "ОбъектыСтроительства".
/// </summary>
/// <returns>Список объектов справочника "ОбъектыСтроительства".</returns>
public static Dictionary <int, DataVersion> GetDataOfDataVersionDirectory()
{
// Прочитаем Excel файл с данными:
Dictionary <int, DataVersion> dataVersions = new Dictionary <int, DataVersion>();
// Открываем файл
XLWorkbook workBook = new XLWorkbook("DataSources\\DataVersions.xlsx");
IXLWorksheet workSheet = workBook.Worksheet(1);
int indexOfLastRow = workSheet.RangeUsed().LastRowUsed().RangeAddress.FirstAddress.RowNumber;
// Читаем со второй строки, так как первая это шапка-заголовок
var wshRows = workSheet.Rows(2, indexOfLastRow);
foreach (IXLRow row in wshRows)
{
IXLCells wshCells = row.Cells("1:3");
int id = Convert.ToInt32(row.Cell(1).Value);
// Создаём и добавляем элементы.
dataVersions.Add(
id,
new DataVersion
{
ID = id,
Name = row.Cell(2).Value.ToString(),
VersionType = row.Cell(3).Value.ToString(),
});
}
return(dataVersions);
}
public static DataTable getDataTableFromExcel(string filePath, int tab)
{
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(tab);
DataTable dt = new DataTable();
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
IXLCells a = row.Cells();
foreach (IXLCell cell in row.Cells(1, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(dt);
}
}
/// <summary>
/// Метод по получению списка объектов справочника "ОбъектыСтроительства".
/// </summary>
/// <returns>Список объектов справочника "ОбъектыСтроительства".</returns>
public static Dictionary <int, ConstructionObject> GetDataOfConstructionObjectsDirectory()
{
// Прочитаем Excel файл с данными:
Dictionary <int, ConstructionObject> objectsOfBuilding = new Dictionary <int, ConstructionObject>();
// Открываем файл
XLWorkbook workBook = new XLWorkbook("DataSources\\ConstructionObjects.xlsx");
IXLWorksheet workSheet = workBook.Worksheet(1);
int indexOfLastRow = workSheet.RangeUsed().LastRowUsed().RangeAddress.FirstAddress.RowNumber;
// Читаем со второй строки, так как первая это шапка-заголовок
var wshRows = workSheet.Rows(2, indexOfLastRow);
foreach (IXLRow row in wshRows)
{
IXLCells wshCells = row.Cells("1:4");
int id = Convert.ToInt32(row.Cell(1).Value);
// Создаём и добавляем элементы в List.
objectsOfBuilding.Add(
id,
new ConstructionObject
{
ID = id,
Name = row.Cell(2).Value.ToString(),
Code = row.Cell(3).Value.ToString(),
Budget = (double)row.Cell(4).Value,
});
}
return(objectsOfBuilding);
}
public static List <string> GetNames_from_cells(IXLCells cells)
{
if (cells == null)
{
return(null);
}
HashSet <string> hs = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
List <string> nameList = new List <string>();
foreach (IXLCell cell in cells)
{
string val_str = cell.Value.ToString();
if (hs.Contains(val_str))
{
int temp_int = 1;
string temp_str = val_str + HssExcel.DupNameSpliter0 + temp_int + HssExcel.DupNameSpliter1;
while (hs.Contains(temp_str))
{
++temp_int;
temp_str = val_str + HssExcel.DupNameSpliter0 + temp_int + HssExcel.DupNameSpliter1;
}
hs.Add(temp_str);
nameList.Add(temp_str);
}
else
{
hs.Add(val_str);
nameList.Add(val_str);
}
}
return(nameList);
}
public void Cells1()
{
var wb = new XLWorkbook();
IXLCells cells = wb.Cells("ABC");
Assert.IsNotNull(cells);
Assert.AreEqual(0, cells.Count());
}
private void SetBorder(IXLCells cells)
{
cells.Style.
Border.SetBottomBorder(XLBorderStyleValues.Thin).
Border.SetTopBorder(XLBorderStyleValues.Thin).
Border.SetLeftBorder(XLBorderStyleValues.Thin).
Border.SetRightBorder(XLBorderStyleValues.Thin);
}
public void Cells3()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue(1).AddToNamed("Result");
IXLCells cells = wb.Cells("Sheet1!Result, ABC");
Assert.IsNotNull(cells);
Assert.AreEqual(1, cells.Count());
Assert.AreEqual(1, cells.First().GetValue <Int32>());
}
public void WsNamedCells()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell", XLScope.Worksheet);
ws.Cell(2, 1).SetValue("B");
IXLCells cells = ws.Cells("TestCell, A2");
Assert.AreEqual("Test", cells.First().GetString());
Assert.AreEqual("B", cells.Last().GetString());
}
IXLAddress GetSpecificHeaderColumn(IXLCells cells, string headerString)
{
return(cells.Where(i => string.Equals(i.Value.ToString().Trim(), headerString, StringComparison.OrdinalIgnoreCase))
.Select(i => i.Address).FirstOrDefault());
}
public List <HistoricoKilometrajeDTO> ImportarDatosInterface(UploadedFileDTO file, string usuario)
{
List <HistoricoKilometrajeDTO> listaResultado = new List <HistoricoKilometrajeDTO>();
List <T_Ihmn> datosAgregar = new List <T_Ihmn>();
DB_MAPRI_Entities context = null;
int columnasDetectadas = 0;
int filaInicio = 0;
DateTime?fechaCreacionArchivo = null;
bool esFilaFecha = false;
try
{
XLWorkbook archivoXLSX = GetWoorkbook(file.Data);
IXLRows nonEmptyDataRows = archivoXLSX.Worksheet("Report").RowsUsed();
foreach (IXLRow dataRow in nonEmptyDataRows)
{
if (columnasDetectadas == listaColumnasGuardar.Count)
{
break;
}
IXLCells cellsUsed = dataRow.CellsUsed();
foreach (IXLCell cellItem in cellsUsed)
{
if (columnasDetectadas == listaColumnasGuardar.Count)
{
break;
}
string valorCelda = cellItem.GetValue <string>().Trim();
if (valorCelda == "Created")
{
esFilaFecha = true;
continue;
}
if (esFilaFecha && fechaCreacionArchivo == null)
{
double baseDate = cellItem.GetValue <double>();
fechaCreacionArchivo = DateTime.FromOADate(baseDate);
}
int rowNumber = cellItem.Address.RowNumber;
int columnNumber = cellItem.Address.ColumnNumber;
string value = cellItem.Value.ToString().Trim();
if (listaColumnasGuardar.ContainsKey(value))
{
if (listaColumnasGuardar[value] == 0)
{
listaColumnasGuardar[value] = columnNumber;
if (filaInicio == 0)
{
filaInicio = rowNumber + 1;
}
columnasDetectadas++;
}
}
}
}
var dataRows = archivoXLSX.Worksheet("Report").RowsUsed().Where(r => r.RowNumber() >= filaInicio);
Parallel.ForEach(dataRows, new ParallelOptions {
MaxDegreeOfParallelism = 10
}, e => AgregarHistoricoKilometrajeDTO(e, fechaCreacionArchivo, ref listaResultado));
datosAgregar.AddRange(
listaResultado.AsParallel().Select(
e => new T_Ihmn()
{
VIN = e.VIN,
Fecha = e.Fecha,
Kilometros = e.Kilometros,
Horas = e.Horas,
Minutos = e.Minutos,
CreationUser = usuario,
Accion = "Alta",
CreationDate = DateTime.Now
})
);
context = new DB_MAPRI_Entities();
context.T_Ihmn.AddRange(datosAgregar);
int saved = context.SaveChanges();
listaResultado.Clear();
listaResultado.AddRange(
datosAgregar.AsParallel().Select(
e => new HistoricoKilometrajeDTO()
{
Id = e.Id,
VIN = e.VIN,
Fecha = e.Fecha,
Kilometros = e.Kilometros,
Horas = e.Horas,
Minutos = e.Minutos
})
);
}
catch (Exception ex)
{
throw ex;
}
finally
{
context?.Dispose();
}
return(listaResultado);
}
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;
}
}