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());
}
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 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 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);
}
}