private static void TestExtractRecordsUsingStream()
{
var provider = new ExcelNPOIStorage(typeof(RaRecord))
{
StartColumn = 0
};
var stream = new FileStream(Directory.GetCurrentDirectory() + @"\test.xlsx", FileMode.Open, FileAccess.Read);
var res = provider.ExtractRecords(stream).Cast <RaRecord>();
}
private static void TestBlankFields()
{
var provider = new ExcelNPOIStorage(typeof(RaRecord))
{
FileName = Directory.GetCurrentDirectory() + @"\testBlankFields.xlsx",
StartColumn = 0
};
var workbook = new XSSFWorkbook(provider.FileName);
var row = workbook.GetSheet("Sheet2").GetRow(0);
var firstLineFields = row.Cells.Select(c => c.StringCellValue.Trim());
var unusedPropertyNames = provider.FieldFriendlyNames.Except(firstLineFields).ToList();
foreach (var propertyName in unusedPropertyNames)
{
provider.RemoveField(propertyName);
}
var res = (RaRecord[])provider.ExtractRecords();
}
public override void Run()
{
//-> File: ExcelExample.cs
// Create an excel storage for specific class
// startRow = 2 & startColumn = 1 -> for skipping column header names
ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(Student), 2, 1);
// Set storage file name -> represents the excel file name we want to read
storage.FileName = "Students.xlsx";
// Read from excel file
Student[] students = storage.ExtractRecords() as Student[];
Console.WriteLine("\t\tStudents from file:");
foreach (Student s in students)
{
Console.WriteLine(s);
}
// Make some changes
students[0].StudentNumber = 420;
Console.WriteLine(Environment.NewLine + "Student {0} edited.", students[0].FullName);
students[1].Course = "Jiu-Jitsu";
Console.WriteLine("Student {0} edited.", students[1].FullName);
Console.WriteLine(Environment.NewLine + "\t\tEdited students:");
foreach (Student s in students)
{
Console.WriteLine(s);
}
// Insert students to excel storage
// This method will save out excel file
storage.InsertRecords(students);
Console.WriteLine(Environment.NewLine + "Changes saved.");
//-> /File
}
private static void Main(string[] args)
{
/*var provider = new ExcelStorage(typeof(RaRecord)) {
* StartRow = 2,
* StartColumn = 1,
* SheetName = "Sheet2",
* FileName = "test.xlsx"
* };*/
//Dynamic Records
var cb = new DelimitedClassBuilder("Customer", "|")
{
IgnoreFirstLines = 1,
IgnoreEmptyLines = true
};
cb.AddField("BirthDate", typeof(DateTime));
cb.LastField.TrimMode = TrimMode.Both;
cb.LastField.FieldNullValue = DateTime.Today;
cb.AddField("Name", typeof(string));
cb.LastField.FieldQuoted = true;
cb.LastField.QuoteChar = '"';
cb.AddField("Age", typeof(int));
var providerWithDynamicRecord = new ExcelNPOIStorage(cb.CreateRecordClass())
{
FileName = Directory.GetCurrentDirectory() + @"\testDynamicRecords.xlsx"
};
providerWithDynamicRecord.StartRow = 1;
providerWithDynamicRecord.StartColumn = 1;
dynamic dynamicRecord = Activator.CreateInstance(providerWithDynamicRecord.RecordType);
dynamicRecord.Name = "Jonh";
dynamicRecord.Age = 1;
dynamicRecord.BirthDate = DateTime.Now;
var valuesList = new List <dynamic>
{
dynamicRecord,
dynamicRecord
};
var columnsHeaders = ((System.Reflection.TypeInfo)(dynamicRecord.GetType())).DeclaredFields.Select(x => x.Name).ToList();
providerWithDynamicRecord.ColumnsHeaders = columnsHeaders;
providerWithDynamicRecord.InsertRecords(valuesList.ToArray());
//General export of excel with date time columns
var provider = new ExcelNPOIStorage(typeof(RaRecord))
{
FileName = Directory.GetCurrentDirectory() + @"\test.xlsx"
};
provider.StartRow = 0;
provider.StartColumn = 0;
var records = new List <RaRecord>();
records.Add(new RaRecord()
{
Level = 123.123m,
Name = "Dickie",
Startdate = DateTime.Now
});
var values = new List <int>
{
1,
2,
3
};
records.Add(new RaRecord()
{
Level = null,
Name = "Bavo",
Project = "too many",
Startdate = DateTime.Now,
ListOfIds = string.Join(",", values.Select(n => n.ToString(CultureInfo.InvariantCulture)).ToArray())
});
provider.HeaderRows = 4;
provider.InsertRecords(records.ToArray());
var res = (RaRecord[])provider.ExtractRecords();
}
private void LeerExcel()
{
try
{
ProductosExcel[] resCab;
if (FormatoCSVRadioButton.Checked)
{
FileStorage provider = new FileStorage(typeof(ProductosExcel), Server.MapPath("TempExcel\\" + ((Entidades.Sesion)Session["Sesion"]).Cuit.Nro + "-" + Session.SessionID + "-Precios.csv"));
//ExcelNPOIStorage provider = new ExcelNPOIStorage(typeof(ProductosExcel));
//provider..StartRow = 0;
//provider.StartColumn = 0;
resCab = (ProductosExcel[])provider.ExtractRecords();
}
else
{
ExcelNPOIStorage provider = new ExcelNPOIStorage(typeof(ProductosExcel));
provider.StartRow = 0;
provider.StartColumn = 0;
provider.FileName = Server.MapPath("TempExcel\\" + ((Entidades.Sesion)Session["Sesion"]).Cuit.Nro + "-" + Session.SessionID + "-Precios.xls");
resCab = (ProductosExcel[])provider.ExtractRecords();
}
List <Entidades.ListaPrecio> listasPrecio = new List <Entidades.ListaPrecio>();
Entidades.Sesion sesion = (Entidades.Sesion)Session["Sesion"];
//if (resCab.Length > 1)
//{
// MensajeLabel.Text = "Proceso cancelado: La cabecera del archivo excel debe tener un solo renglón.";
// return;
//}
if (resCab[0].Lista01 == null || resCab[0].Lista01.Trim().Equals(string.Empty))
{
MensajeLabel.Text = "Proceso cancelado: No está informada la primer lista de precios en la planilla excel.";
return;
}
else
{
try
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista01.Trim()));
if (!resCab[0].Lista02.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista02.Trim()));
}
if (!resCab[0].Lista03.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista03.Trim()));
}
if (!resCab[0].Lista04.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista04.Trim()));
}
if (!resCab[0].Lista05.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista05.Trim()));
}
if (!resCab[0].Lista06.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista06.Trim()));
}
if (!resCab[0].Lista07.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista07.Trim()));
}
if (!resCab[0].Lista08.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista08.Trim()));
}
if (!resCab[0].Lista09.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista09.Trim()));
}
if (!resCab[0].Lista10.Trim().Equals(String.Empty))
{
listasPrecio.Add(new Entidades.ListaPrecio(resCab[0].Lista10.Trim()));
}
}
catch
{
}
}
if (listasPrecio.Count == 0)
{
MensajeLabel.Text = "Proceso cancelado: No hay ninguna Lista de precios definida.";
return;
}
ViewState["ListasPrecio"] = listasPrecio;
//Leer detalle del excel
//ExcelNPOIStorage provider = new ExcelNPOIStorage(typeof(ProductosExcel));
//provider.StartRow = 3;
//provider.StartColumn = 1;
//provider.FileName = Server.MapPath("Temp\\ExcelAProcesar.xlsx");
//ProductosExcel[] resDet = (ProductosExcel[])provider.ExtractRecords();
//Completar la Matriz de Precios
CrearYCompletarMatrizDePrecios(listasPrecio, resCab);
//Actualizar los precios de la MatrizDePrecios
DataTable dt = (DataTable)ViewState["MatrizDePrecios"];
List <Entidades.ListaPrecio> listasPrecioNew = (List <Entidades.ListaPrecio>)ViewState["ListasPrecio"];
RN.Precio.ImpactarMatriz(listasPrecioNew, dt, sesion);
MensajeLabel.Enabled = true;
MensajeLabel.Text = "PROCESO CONCLUIDO SATISFACTORIAMENTE.\n";
string listasImp = "Listas importadas: ";
foreach (Entidades.ListaPrecio lp in listasPrecio)
{
if (listasImp != "Listas importadas: ")
{
listasImp += ", ";
}
listasImp += lp.Id;
}
MensajeLabel.Text += listasImp + "\n";
MensajeLabel.Text += "Cantidad total de artículos: " + dt.Rows.Count;
MensajeLabel.Enabled = false;
}
catch (Exception ex)
{
MensajeLabel.Text = EX.Funciones.Detalle(ex);
}
}