public void TableInsertBelowFromRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Value");
IXLTable table = ws.Range("A1:A2").CreateTable();
table.SetShowTotalsRow()
.Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum;
IXLTableRow row = table.DataRange.FirstRow();
row.Field("Value").Value = 1;
row = row.InsertRowsBelow(1).First();
row.Field("Value").Value = 2;
row = row.InsertRowsBelow(1).First();
row.Field("Value").Value = 3;
Assert.AreEqual(1, ws.Cell(2, 1).GetDouble());
Assert.AreEqual(2, ws.Cell(3, 1).GetDouble());
Assert.AreEqual(3, ws.Cell(4, 1).GetDouble());
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx");
}
/// <summary>
/// Permite insertar el paciente prioritario
/// </summary>
/// <param name="row">Fila del archivo procesada</param>
#region Add Data
private tbl_paciente_prioritario insertPriorityPatient(IXLTableRow row)
{
try
{
tbl_paciente_prioritario currentPatient;
int rownumber = row.RowNumber();
var tempKeyPair = wrapper.GetValue(rownumber);
if (tempKeyPair == null)
{
currentPatient = new tbl_paciente_prioritario
{
id = Guid.NewGuid(),
apellidos = $"{row.Field("Primer_Apellido").Value.ToString()} {row.Field("Segundo_Apellido").Value.ToString()}",
cedula = $"{row.Field("Tipo_Identificacion").Value.ToString()} {row.Field("Numero_Identificacion").Value.ToString()}",
nombres = $"{row.Field("Primer_Nombre").Value.ToString()} {row.Field("Segundo_Nombre").Value.ToString()}",
numContacto = $"{row.Field("Numero_Telefonico").Value.ToString()}",
idArchivo = Guid.Parse(idArchivo)
};
}
else
{
currentPatient = tempKeyPair;
}
wrapper.Add(row.RowNumber(), currentPatient);
return(currentPatient);
}
catch (Exception ex)
{
IOUtilities.WriteExceptionLog(ex, Configuration.GetClassName <PriorityPatient>());
}
return(null);
}
private string ReadCellValueByDateTime(IXLTableRow row, int fieldIndex)
{
switch(row.Field(fieldIndex).DataType)
{
case XLCellValues.Number:
return row.Field(fieldIndex).GetDouble().ConvertToDateTime().ToString("M月dd日");
case XLCellValues.Text:
return row.Field(fieldIndex).GetString();
default:
return "";
}
}
public override (int, string) ParseAddress(List <string> columns, IXLTableRow row)
{
(var index, var cell) = GetCellValue(columns, row, "address,주소");
if (index == -1)
{
SetErrorCell(row.Field(0), RequiredErrorString);
}
else if (string.IsNullOrEmpty(cell))
{
SetErrorCell(row.Field(index), RequiredErrorString);
}
return(index, cell);
}
public static T GetField <T>(IXLTableRow row, string fieldName)
{
object value;
try
{
var item = row.Field(fieldName);
if (item.HasFormula)
{
value = item.CachedValue;
}
else
{
value = item.Value;
}
}
catch (Exception)
{
return(default(T));
}
if (null != value && !"".Equals(value))
{
return((T)Convert.ChangeType(value, typeof(T), CultureInfo.InvariantCulture));
}
return(default(T));
}
private void RowValidator(IXLTableRow row)
{
try
{
List <TemplateRulesPriorityPatient> templatePriority = this.GetTemplate();
foreach (TemplateRulesPriorityPatient template in templatePriority)
{
if (!row.Field(template.Name).GetString().Trim().Equals(""))
{
ColumnValidator(template, row.Field(template.Name), row);
}
}
}
catch (Exception ex)
{
IOUtilities.WriteExceptionLog(ex, Configuration.GetClassName <PriorityPatient>());
}
}
public static DynamicDataRowGroupProperty Create(IXLTableRow row, string name)
{
var column = row.Field(name);
if (column == null)
{
throw new ArgumentException(string.Format("Column '{0}' was not found.", name));
}
return(new DynamicDataRowGroupProperty(name, column.GetType()));
}
public override (int, string) ParseAddress(List <string> columns, IXLTableRow row)
{
(var index, var cell) = GetCellValue(columns, row, "address,주소");
if (index == -1)
{
SetErrorCell(row.Field(0), RequiredErrorString);
}
else if (string.IsNullOrEmpty(cell))
{
SetErrorCell(row.Field(index), RequiredErrorString);
}
else
{
if (!int.TryParse(cell, out int address)) //숫자가 아니면 에러!
{
SetErrorCell(row.Field(index), InvalidErrorString);
return(index, null);
}
}
return(index, cell);
}
public void TableInsertBelowFromData()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Value");
IXLTable table = ws.Range("A1:A2").CreateTable();
table.SetShowTotalsRow()
.Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum;
IXLTableRow row = table.DataRange.FirstRow();
row.Field("Value").Value = 1;
row = table.DataRange.InsertRowsBelow(1).First();
row.Field("Value").Value = 2;
row = table.DataRange.InsertRowsBelow(1).First();
row.Field("Value").Value = 3;
Assert.AreEqual(1, ws.Cell(2, 1).GetDouble());
Assert.AreEqual(2, ws.Cell(3, 1).GetDouble());
Assert.AreEqual(3, ws.Cell(4, 1).GetDouble());
}
}
public virtual (int, string, int, string) ParseVariableIdAndName(List <string> columns, IXLTableRow row)
{
int index2 = -1;
string cellValue2 = null;
(var index, var cellValue) = GetCellValue(columns, row, "variable,변수");
if (index == -1)
{
SetErrorCell(row.Field(0), RequiredErrorString);
}
else if (string.IsNullOrEmpty(cellValue))
{
SetErrorCell(row.Field(index), RequiredErrorString);
}
else
{
//VariableName에는 VariableId 수정전 문자를 할당
(index2, cellValue2) = GetCellValue(columns, row, "variablename,변수이름");
if (index2 != -1)
{
if (string.IsNullOrEmpty(row.Field(index2).Value.ToString()))
{
row.Field(index2).Value = cellValue;
}
}
//특수문자(&,',",<,>,%,#,$), 공백 제거
var temp = cellValue.Replace(" ", "_");
foreach (var item in EscapeCharacters)
{
temp = temp.Replace(item, "");
}
if (temp != cellValue)
{
cellValue = temp;
row.Field(index).Value = temp;
SetWarningCell(row.Field(index), "");
}
}
return(index, cellValue, index2, cellValue2);
}
public virtual AddressMap ParseAddressMap(AddressMap input, List <string> columns, IXLTableRow row)
{
if (input == null)
{
input = new AddressMap();
}
try
{
int index = -1;
int index2 = -1;
string cellValue = null;
string cellValue2 = null;
int intCell = -1;
DataType dataType = DataType.None;
AddressMapParseErrors = new List <(string, string)>();
List <string> metaDataColumns = columns.DeepCopy(); //필수항목을 뺀 metaDatas 파싱할 항목
//VariableId, VariableName
(index, cellValue, index2, cellValue2) = ParseVariableIdAndName(columns, row);
if (!string.IsNullOrEmpty(cellValue))
{
input.VariableId = cellValue;
input.VariableName = cellValue2;
input.SetCellAddress("Variableid", row.Field(index)?.Address?.ToString());
input.SetCellAddress("VariableName", row.Field(index2)?.Address?.ToString());
metaDataColumns.Remove(cellValue);
metaDataColumns.Remove(cellValue2);
}
//Address
(index, cellValue) = ParseAddress(columns, row);
if (!string.IsNullOrEmpty(cellValue))
{
input.Address = cellValue;
input.SetCellAddress("Address", row.Field(index)?.Address?.ToString());
metaDataColumns.Remove(cellValue);
}
//Size
(index, cellValue, intCell) = ParseSize(columns, row);
if (intCell > -1)
{
input.Size = intCell;
input.SetCellAddress("Size", row.Field(index)?.Address?.ToString());
metaDataColumns.Remove(cellValue);
}
//decimalplace
(index, cellValue, intCell) = ParseDecimalPoint(columns, row);
if (intCell > -1)
{
input.DecimalPoint = intCell;
input.SetCellAddress("DecimalPoint", row.Field(index)?.Address?.ToString());
metaDataColumns.Remove(cellValue);
}
//datatype
(index, cellValue, dataType) = ParseDataType(columns, row);
if (dataType != DataType.None)
{
input.DataType = dataType;
input.SetCellAddress("DataType", row.Field(index)?.Address?.ToString());
metaDataColumns.Remove(cellValue);
}
#region MetaDatas (아직 파싱안한 컬럼(metaDataColumns)을 파싱)
input.MetaDatas = new Dictionary <string, string>();
foreach (var item in metaDataColumns)
{
(index, cellValue) = GetCellValue(columns, row, item);
input.MetaDatas.Add(item, cellValue);
input.SetCellAddress(item, row.Field(index)?.Address?.ToString());
}
#endregion
}
catch (Exception ex)
{
logger.Error($"ex={ex}");
}
return(input);
}
/// <summary>
/// Crea cada registro a insertar en la tabla
/// </summary>
/// <param name="row">Fila de datos a insertar</param>
/// <returns>Registro CAC</returns>
private tbl_cac createRecord(IXLTableRow row)
{
tbl_cac cacRecord = new tbl_cac();
cacRecord.id = Guid.NewGuid();
cacRecord.idArchivo = Guid.Parse(idArchivo);
cacRecord.primerNombre = row.Field("Primer_Nombre").GetString().Trim(' ').Length > 200 ? row.Field("Primer_Nombre").GetString().Trim(' ').Substring(0, 200) : row.Field("Primer_Nombre").GetString().Trim(' ');
cacRecord.segundoNombre = row.Field("Segundo_Nombre").GetString().Trim(' ').Length > 200 ? row.Field("Segundo_Nombre").GetString().Trim(' ').Substring(0, 200) : row.Field("Segundo_Nombre").GetString().Trim(' ');
cacRecord.primerApellido = row.Field("Primer_Apellido").GetString().Trim(' ').Length > 200 ? row.Field("Primer_Apellido").GetString().Trim(' ').Substring(0, 200) : row.Field("Primer_Apellido").GetString().Trim(' ');
cacRecord.segundoApellido = row.Field("Segundo_Apellido").GetString().Trim(' ').Length > 200 ? row.Field("Segundo_Apellido").GetString().Trim(' ').Substring(0, 200) : row.Field("Segundo_Apellido").GetString().Trim(' ');
cacRecord.tipoIdentificacion = row.Field("Tipo_Identificacion").GetString().Trim(' ').Length > 10 ? row.Field("Tipo_Identificacion").GetString().Trim(' ').Substring(0, 5) : row.Field("Tipo_Identificacion").GetString().Trim(' ');
cacRecord.numIdentificacion = row.Field("Numero_Identificacion").GetString().Trim(' ').Length > 10 ? row.Field("Numero_Identificacion").GetString().Trim(' ').Substring(0, 5) : row.Field("Numero_Identificacion").GetString().Trim(' ');
cacRecord.sexo = row.Field("Sexo").GetString().Trim(' ').Length > 2 ? row.Field("Sexo").GetString().Trim(' ').Substring(0, 2) : row.Field("Sexo").GetString().Trim(' ');
cacRecord.diagHiperArterial = row.Field("Diagnostico_Hipertension_Arterial").GetString().Trim(' ').Length > 5 ? row.Field("Diagnostico_Hipertension_Arterial").GetString().Trim(' ').Substring(0, 5) : row.Field("Diagnostico_Hipertension_Arterial").GetString().Trim(' ');
cacRecord.diagDiabetesMellitus = row.Field("Diagnostico_Diabetes_Mellitus").GetString().Trim(' ').Length > 5 ? row.Field("Diagnostico_Diabetes_Mellitus").GetString().Trim(' ').Substring(0, 5) : row.Field("Diagnostico_Diabetes_Mellitus").GetString().Trim(' ');
cacRecord.etiologiaCAC = row.Field("Etiologia_CAC").GetString().Trim(' ').Length > 5 ? row.Field("Etiologia_CAC").GetString().Trim(' ').Substring(0, 5) : row.Field("Etiologia_CAC").GetString().Trim(' ');
cacRecord.PesoKG = row.Field("Peso_Kilogramos").GetString().Trim(' ').Length > 5 ? row.Field("Peso_Kilogramos").GetString().Trim(' ').Substring(0, 5) : row.Field("Peso_Kilogramos").GetString().Trim(' ');
cacRecord.tallaCtms = row.Field("Talla_centimetros").GetString().Trim(' ').Length > 5 ? row.Field("Talla_centimetros").GetString().Trim(' ').Substring(0, 5) : row.Field("Talla_centimetros").GetString().Trim(' ');
cacRecord.tensionArtSistolica = row.Field("Tension_Arterial_Sistolica_mmHg").GetString().Trim(' ').Length > 5 ? row.Field("Tension_Arterial_Sistolica_mmHg").GetString().Trim(' ').Substring(0, 5) : row.Field("Tension_Arterial_Sistolica_mmHg").GetString().Trim(' ');
cacRecord.tensionArtDiastolica = row.Field("Tension_Arterial_Diastolica_mmHg").GetString().Trim(' ').Length > 5 ? row.Field("Tension_Arterial_Diastolica_mmHg").GetString().Trim(' ').Substring(0, 5) : row.Field("Tension_Arterial_Diastolica_mmHg").GetString().Trim(' ');
cacRecord.creatinina = row.Field("Creatinina").GetString().Trim(' ').Length > 5 ? row.Field("Creatinina").GetString().Trim(' ').Substring(0, 5) : row.Field("Creatinina").GetString().Trim(' ');
cacRecord.hemoglobinaGlicosilada = row.Field("Hemoglobina_Glicosilada").GetString().Trim(' ').Length > 5 ? row.Field("Hemoglobina_Glicosilada").GetString().Trim(' ').Substring(0, 5) : row.Field("Hemoglobina_Glicosilada").GetString().Trim(' ');
cacRecord.albuminuria = row.Field("Albuminuria").GetString().Trim(' ').Length > 5 ? row.Field("Albuminuria").GetString().Trim(' ').Substring(0, 5) : row.Field("Albuminuria").GetString().Trim(' ');
cacRecord.creatinuria = row.Field("Creatinuria").GetString().Trim(' ').Length > 5 ? row.Field("Creatinuria").GetString().Trim(' ').Substring(0, 5) : row.Field("Creatinuria").GetString().Trim(' ');
cacRecord.LDL = row.Field("LDL").GetString().Trim(' ').Length > 5 ? row.Field("LDL").GetString().Trim(' ').Substring(0, 5) : row.Field("LDL").GetString().Trim(' ');
cacRecord.PTH = row.Field("PTH").GetString().Trim(' ').Length > 5 ? row.Field("PTH").GetString().Trim(' ').Substring(0, 5) : row.Field("PTH").GetString().Trim(' ');
cacRecord.tasaFiltracionGlomerular = row.Field("Tasa_Filtracion_Glomerular_TFG").GetString().Trim(' ').Length > 5 ? row.Field("Tasa_Filtracion_Glomerular_TFG").GetString().Trim(' ').Substring(0, 5) : row.Field("Tasa_Filtracion_Glomerular_TFG").GetString().Trim(' ');
cacRecord.diagEnferRenalCronicoERC = row.Field("Diagnostico_Enfermedad_Renal_Cronico_ERC").GetString().Trim(' ').Length > 5 ? row.Field("Diagnostico_Enfermedad_Renal_Cronico_ERC").GetString().Trim(' ').Substring(0, 5) : row.Field("Diagnostico_Enfermedad_Renal_Cronico_ERC").GetString().Trim(' ');
cacRecord.estadioERC = row.Field("Estadio_ERC").GetString().Trim(' ').Length > 5 ? row.Field("Estadio_ERC").GetString().Trim(' ').Substring(0, 5) : row.Field("Estadio_ERC").GetString().Trim(' ');
cacRecord.fecUltimaLDL = Convert.ToDateTime(row.Field("Fecha_Ultimo_LDL").GetString());
cacRecord.fecUltimaCreatinuria = Convert.ToDateTime(row.Field("Fecha_Ultima_Creatinuria").GetString());
cacRecord.fecHemoGlicosilada = Convert.ToDateTime(row.Field("Fecha_Ultima_Hemoglobina_Glicosilada").GetString());
cacRecord.fecUltimaAlbuminuria = Convert.ToDateTime(row.Field("Fecha_Ultima_Albuminuria").GetString());
cacRecord.fechaPTH = Convert.ToDateTime(row.Field("Fecha_PTH").GetString());
cacRecord.fecUltimaCreatinina = Convert.ToDateTime(row.Field("Fecha_Ultima_Creatinina").GetString());
cacRecord.fechaNacimiento = Convert.ToDateTime(row.Field("Fecha_Nacimiento").GetString());
cacRecord.fecDiagHiperArterial = Convert.ToDateTime(row.Field("Fecha_Diagnostico_Hipertension_Arterial").GetString());
cacRecord.fecDiadDiabetesMellitus = Convert.ToDateTime(row.Field("Fecha_Diagnostico_Diabetes_Mellitus").GetString());
cacRecord.idOrganizacion = idOrganizacion;
return(cacRecord);
}