public override void Run()
{
//-> File: ExcelExample.cs
// Create an excel storage for specific class
// By default start row/column is 2/B (index 1)
ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(Student));
// Set storage file name -> that will be excel output file name
// Extension must be .xlsx or .xls
storage.FileName = "Students.xlsx";
// Sheet name is not required. By default sheet name will be "Sheet0"
storage.SheetName = "Students";
storage.ColumnsHeaders.Add("Student number");
storage.ColumnsHeaders.Add("Student name");
storage.ColumnsHeaders.Add("Course name");
// Test data
int count = 3;
Student[] students = new Student[count];
students[0] = CreateStudent(0, "Chuck Norris", "Karate");
students[1] = CreateStudent(1, "Steven Seagal", "Aikido");
students[2] = CreateStudent(2, "Dennis Ritchie", "Programming");
// Insert students to excel storage
// This method will save out excel file
storage.InsertRecords(students);
//-> /File
}
private static void Main(string[] args)
{
/*var provider = new ExcelStorage(typeof(RaRecord)) {
* StartRow = 2,
* StartColumn = 1,
* SheetName = "Sheet2",
* FileName = "test.xlsx"
* };*/
var provider = new ExcelNPOIStorage(typeof(RaRecord))
{
SheetName = "SheetBavo",
FileName = "test.xlsx"
};
provider.StartRow = 1;
provider.StartColumn = 0;
var records = new List <RaRecord>();
records.Add(new RaRecord()
{
Level = 123.123m,
Name = "Dickie"
});
records.Add(new RaRecord()
{
Level = null,
Name = "Bavo",
Project = "too many",
Startdate = DateTime.Now
});
provider.InsertRecords(records.ToArray());
//var res = (RaRecord[])provider.ExtractRecords();
}
public void Exportar(Type tipo, string nomeArquivo)
{
if (tipo == null || nomeArquivo == null)
{
return;
}
var engine = new FileHelperEngine <Cotacao>();
ExcelNPOIStorage arquivo = CriarExcel(tipo, nomeArquivo);
List <Cotacao> cotacoes = new List <Cotacao>();
var records = engine.ReadFile(@"Input\" + nomeArquivo);
foreach (var record in records)
{
cotacoes.Add(
new Cotacao
{
NegociacaoID = record.NegociacaoID,
DataPregao = record.DataPregao,
PrecoAbertura = record.PrecoAbertura,
PrecoFechamento = record.PrecoFechamento
});
}
arquivo.InsertRecords(cotacoes.ToArray());
}
private ExcelNPOIStorage CriarExcel(Type tipo, string nomeArquivo)
{
ExcelNPOIStorage arquivo = new ExcelNPOIStorage(tipo);
//O nome do arquivo excel ficará com mesmo nome do arquivo lido no diretório input
arquivo.FileName = $"{nomeArquivo.Substring(0, nomeArquivo.IndexOf("."))}.xlsx";
//O nome da tabela tem o mesmo da classe
arquivo.SheetName = nameof(tipo);
//Obtem-se os atributos da classe passada como parametro no metódo
PropertyInfo[] props = tipo.GetProperties();
foreach (var item in props)
{
MemberInfo property = tipo.GetProperty(item.Name);
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), true)
.Cast <DisplayNameAttribute>().Single();
string displayName = attribute.DisplayName;
//O Dispaly dos atributos da classe são usados para criar o nome das colunas do arquivo excel
arquivo.ColumnsHeaders.Add(displayName);
}
return(arquivo);
}
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
}
public static ImportDataDelegate <OwnScore> CreateCoolOwnScoreImportDataFromExcelDelegate()
{
ExcelNPOIStorage excelNPOIStorage = new ExcelNPOIStorage(typeof(OwnScore))
{
FileName = Directory.GetCurrentDirectory() + @"\Files\OwnScore.xls",
StartRow = 8,
StartColumn = 4
};
return(delegate(IObjectSpace os, object[] args) {
//throw new NotImplementedException("TODO: you can use the FileHelpers library to get the data from Excel and then fill your persistent object.");
object masterObject = null;
int index = 0;
if (args != null)
{
if (args.Length == 2)
{
masterObject = args[0];
index = Convert.ToInt32(args[1]);
}
}
//if (index < doc.DocumentElement.ChildNodes.Count)
//{
// OwnScore ownScore = os.CreateObject<OwnScore>();
// Office office = os.FindObject<Office>(CriteriaOperator.Parse("OfficeId=?", index));
// ownScore.Office = office;
// ownScore.Result = index;
// ownScore.Score = index;
// ownScore.WeightScore = index;
// return ownScore;
//}
return null;
});
}
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 ExportGR(object ignored)
{
ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(ImportCLassModel), 0, 0);
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Export GR");
if (!Directory.Exists(path))
{
try
{
Directory.CreateDirectory(path);
}
catch (IOException ie)
{
Console.WriteLine("IO Error: " + ie.Message);
}
catch (Exception e)
{
Console.WriteLine("General Error: " + e.Message);
}
}
storage.FileName = path +
string.Concat(@"\GoodsReceiveExport_" + DateTime.Now.ToString("yyyy-MM-dd_HHmm",
System.Globalization.CultureInfo.InvariantCulture)) +
".xlsx";
storage.ColumnsHeaders.Add("Document Date");
storage.ColumnsHeaders.Add("Posting Date");
storage.ColumnsHeaders.Add("Purchase Order");
storage.ColumnsHeaders.Add("Vendor");
storage.ColumnsHeaders.Add("Delivery Note");
storage.ColumnsHeaders.Add("Bill Of Lading");
storage.ColumnsHeaders.Add("Header Text");
storage.ColumnsHeaders.Add("Material");
storage.ColumnsHeaders.Add("Material Short Text");
storage.ColumnsHeaders.Add("OK");
storage.ColumnsHeaders.Add("Quantity");
storage.ColumnsHeaders.Add("Eun");
storage.ColumnsHeaders.Add("Mvmt Type");
storage.ColumnsHeaders.Add("Storage Loc");
storage.ColumnsHeaders.Add("Plant");
storage.ColumnsHeaders.Add("Storage Bin");
storage.ColumnsHeaders.Add("Quantity Received");
storage.ColumnsHeaders.Add("Transaction Reason");
storage.ColumnsHeaders.Add("Received By");
storage.ColumnsHeaders.Add("Received On");
ObservableCollection <ImportCLassModel> importObj = new ObservableCollection <ImportCLassModel>();
ImportCLassModel temp = new ImportCLassModel();
using (var db = new InventoryContext())
{
foreach (var rec in grCollection)
{
var grTxn = db.GRTransactions.Where(p => p.GRID == rec.ID);
if (grTxn != null && grTxn.Count() > 0)
{
foreach (var item in grTxn)
{
temp = new ImportCLassModel()
{
DocumentDate = rec.DocumentDate,
PostingDate = rec.PostingDate,
PurchaseOrder = rec.PurchaseOrder,
Vendor = rec.Vendor ?? string.Empty,
DeliveryNote = rec.DeliveryNote ?? string.Empty,
BillOfLading = rec.BillOfLading ?? string.Empty,
HeaderText = rec.HeaderText ?? string.Empty,
Material = rec.Material,
MaterialShortText = rec.MaterialShortText,
Ok = rec.Ok == null || rec.Ok == false ? "Not Complete" : "Completed",
Quantity = rec.Quantity,
Eun = rec.Eun,
MvmtType = rec.MvmtType ?? string.Empty,
StorageLoc = rec.StorageLoc,
Plant = rec.Plant,
StorageBin = rec.StorageBin,
QuantityReceived = item.Quantity,
ReasonDesc = item.Reason != null ? item.Reason.ReasonDesc : string.Empty,
ReceivedBy = item.CreatedBy,
ReceivedOn = Convert.ToString(item.CreatedOn),
};
importObj.Add(temp);
}
}
else
{
temp = new ImportCLassModel()
{
DocumentDate = rec.DocumentDate,
PostingDate = rec.PostingDate,
PurchaseOrder = rec.PurchaseOrder,
Vendor = rec.Vendor ?? string.Empty,
DeliveryNote = rec.DeliveryNote ?? string.Empty,
BillOfLading = rec.BillOfLading ?? string.Empty,
HeaderText = rec.HeaderText ?? string.Empty,
Material = rec.Material,
MaterialShortText = rec.MaterialShortText,
Ok = rec.Ok == null || rec.Ok == false ? "Not Complete" : "Completed",
Quantity = rec.Quantity,
Eun = rec.Eun,
MvmtType = rec.MvmtType ?? string.Empty,
StorageLoc = rec.StorageLoc,
Plant = rec.Plant,
StorageBin = rec.StorageBin,
QuantityReceived = 0,
ReasonDesc = string.Empty,
ReceivedBy = string.Empty,
ReceivedOn = string.Empty
};
importObj.Add(temp);
}
}
}
if (importObj != null)
{
storage.InsertRecords(importObj.ToArray());
this.confirmDeleteInteractionRequest.Raise(
new Confirmation
{
Content = "Exported successfully. Open file?",
Title = "Notification"
},
c => { InteractionResultMessage = c.Confirmed ? OpenExportedFile(storage.FileName) : "NOT OK!"; });
}
}
private void Export(object ignored)
{
ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(ExportCLassModel), 0, 0);
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Export GI");
if (!Directory.Exists(path))
{
try
{
Directory.CreateDirectory(path);
}
catch (IOException ie)
{
Console.WriteLine("IO Error: " + ie.Message);
}
catch (Exception e)
{
Console.WriteLine("General Error: " + e.Message);
}
}
storage.FileName = path +
string.Concat(@"\GoodsIssueExport_" + DateTime.Now.ToString("yyyy-MM-dd_HHmm",
System.Globalization.CultureInfo.InvariantCulture)) +
".xlsx";
storage.ColumnsHeaders.Add("SAP No.");
storage.ColumnsHeaders.Add("Text");
storage.ColumnsHeaders.Add("Quantity");
storage.ColumnsHeaders.Add("Eun");
storage.ColumnsHeaders.Add("Transfer Type");
storage.ColumnsHeaders.Add("Production No.");
storage.ColumnsHeaders.Add("Location To");
storage.ColumnsHeaders.Add("Location From");
storage.ColumnsHeaders.Add("Issued By");
storage.ColumnsHeaders.Add("Issued On");
ObservableCollection <ExportCLassModel> exportObj = new ObservableCollection <ExportCLassModel>();
foreach (var gr in GoodsIssues)
{
exportObj.Add(new ExportCLassModel
{
SAPNo = gr.GoodsReceive.Material,
Text = gr.Text,
Quantity = gr.Quantity,
Eun = gr.GoodsReceive.Eun,
TransferType = gr.TransferType.Contains("POST") ? "Transfer Posting" : "Transfer to Production",
ProductionNo = gr.ProductionNo,
LocationTo = (gr.Location1 != null) ? gr.Location1.LocationDesc : string.Empty,
LocationFrom = (gr.Location != null) ? gr.Location.LocationDesc : string.Empty,
IssuedBy = gr.CreatedBy,
IssuedOn = Convert.ToString(gr.CreatedOn)
});
}
if (exportObj != null)
{
storage.InsertRecords(exportObj.ToArray());
this.confirmDeleteInteractionRequest.Raise(
new Confirmation
{
Content = "Exported successfully. Open file?",
Title = "Notification"
},
c =>
{
if (c.Confirmed)
{
if (OpenExportedFile(storage.FileName))
{
OnLoaded();
}
}
});
}
}
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);
}
}
private void Export()
{
ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(FCUImportCLassModel), 0, 0);
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Export FG");
if (!Directory.Exists(path))
{
try
{
Directory.CreateDirectory(path);
}
catch (IOException ie)
{
Console.WriteLine("IO Error: " + ie.Message);
}
catch (Exception e)
{
Console.WriteLine("General Error: " + e.Message);
}
}
storage.FileName = path +
string.Concat(@"\FCUExport_" + DateTime.Now.ToString("yyyy-MM-dd_HHmm",
System.Globalization.CultureInfo.InvariantCulture)) +
".xlsx";
storage.ColumnsHeaders.Add("Project");
storage.ColumnsHeaders.Add("Unit Tag");
storage.ColumnsHeaders.Add("Part No.");
storage.ColumnsHeaders.Add("Model");
storage.ColumnsHeaders.Add("Quantity");
storage.ColumnsHeaders.Add("Item");
storage.ColumnsHeaders.Add("Serial No.");
storage.ColumnsHeaders.Add("Quantity Received");
storage.ColumnsHeaders.Add("Shipped By");
storage.ColumnsHeaders.Add("Shipped On");
ObservableCollection <FCUImportCLassModel> importObj = new ObservableCollection <FCUImportCLassModel>();
foreach (var fcu in fcuCollection)
{
importObj.Add(new FCUImportCLassModel
{
Project = fcu.Project,
UnitTag = fcu.UnitTag,
PartNo = fcu.PartNo,
Model = fcu.Model,
Qty = fcu.Qty,
Item = fcu.Item,
SerialNo = fcu.SerialNo,
QtyReceived = fcu.QtyReceived.GetValueOrDefault(),
ShippedBy = fcu.CreatedBy,
ShippedOn = Convert.ToString(fcu.CreatedOn)
});
}
if (importObj != null)
{
storage.InsertRecords(importObj.ToArray());
}
if (MessageBox.Show("Exported successfully. Open file?", "Notification", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
OpenExportedFile(storage.FileName);
}
}
