public SerialiseUsingWorksheetSpec()
{
using (var serialiser = new ExcelSerializer(Worksheet))
{
Run(serialiser);
}
}
public void ChangeToExcelSerializer()
{
ExcelSerializer <User> serializer = new ExcelSerializer <User>();
serializer.setBinding(System.Reflection.BindingFlags.NonPublic);//all fields are public so no fields return
serializer.Serialize(new User("Toto", "Titi"));
}
public SerialiseUsingWorkbookSpec()
{
using (var serialiser = new ExcelSerializer(Workbook))
{
Run(serialiser);
}
}
public void Run()
{
AllValuesAreOfSameType();
//// set headers
//Sheet.Row(Row).Style.Font.Bold = true;
foreach (var _object in Objects)
{
var mergeDef = ExcelSerializer.GetRange(Row, Column, Row + 1, Column);
Sheet.Cells[mergeDef].Merge = true;
Sheet.Cells[Row, Column].Value = _object.Key;
Sheet.Cells[Row, Column].Style.Font.Bold = true;
Sheet.Cells[Row, Column].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
var dvw = new DirectValueWriter(_object.Value.Value as Dictionary <string, ObjectWrapper>, Sheet, Row, Column + 1);
dvw.Run();
Row += 2;
UpdatedColumns.Add(Column);
}
foreach (var updatedColumnIndex in UpdatedColumns)
{
Sheet.Column(updatedColumnIndex).AutoFit();
}
}
public SerialiseUsingPathAndSheetnameSpec()
{
using (var serialiser = new ExcelSerializer(Path, WorksheetName))
{
Run(serialiser);
}
}
public override void Write <Y>(Y listElements, string path)
{
ExcelSerializer <T> serializer = new ExcelSerializer <T>(_PropertiesToSerialize);
ICollection <object[]> objects = serializer.SerializeList(listElements);
Write(objects, path);
}
public SerialiseUsingPathSpec()
{
using (var serialiser = new ExcelSerializer(Path))
{
Run(serialiser);
}
}
protected void Run(ExcelSerializer serialiser)
{
using (var writer = new CsvWriter(serialiser)) {
writer.Configuration.AutoMap <Person>();
writer.WriteRecords(Values);
}
}
public void TestDeserialize()
{
using (var stream = new MemoryStream())
{
var excel = new ExcelSerializer();
var wb = TestData.CreateWorkbookProperties();
excel.Serialize(stream, TestData.ArrayOfModels, wb);
stream.Flush();
stream.Position = 0;
var wb2 = new WorkbookProperties();
var results = excel.Deserialize(stream, wb2);
var page1 = TestData.ArrayOfModels[0].OfType <DataModel>();
var page2 = TestData.ArrayOfModels[1].OfType <DataModel2>();
var result1 = results[0].OfType <DataModel>();
var result2 = results[1].OfType <DataModel2>();
Assert.IsTrue(page1.SequenceEqual(result1), "Serialize/Deserialize round trip");
Assert.IsTrue(page2.SequenceEqual(result2), "Serialize/Deserialize round trip");
}
}
public SerialiseUsingWorkbookAndSheetnameSpec()
{
using (var serialiser = new ExcelSerializer(Workbook, WorksheetName))
{
Run(serialiser);
}
}
public void serializeCSVListWithJustNameHeader()
{
ListSerializable <User> users = new UserList();
users.Add(new User("Toto", "Titi"));
users.Add(new User("Tata", "Roro"));
ExcelSerializer <User> serializer = new ExcelSerializer <User>(new StringList {
"Name"
});
List <object[]> objects = serializer.SerializeList <UserList>(users).ToList();
Assert.IsNotNull(objects);
object[] line1 = objects[0];
object[] line2 = objects[1];
Assert.AreEqual("Toto", line1[0]);
Assert.AreEqual("Tata", line2[0]);
try
{
Assert.IsNull(line1[1]);
Assert.IsNull(line2[1]);
}
catch
{
Assert.IsTrue(true);
}
}
public SerialiseUsingRangeSpec()
{
var range = Worksheet.Cells[StartRow, StartColumn, StartRow + Values.Length, StartColumn + 1];
using (var serialiser = new ExcelSerializer(Package, range)) {
Run(serialiser);
}
}
public SerialiseUsingStreamAndSheetnameSpec(ITestOutputHelper outputHelper)
: base(outputHelper, "serialise_by_workbook_and_sheetname", "a_different_sheet_name")
{
using var stream = new MemoryStream();
using var serialiser = new ExcelSerializer(stream, WorksheetName);
Run(serialiser);
bytes = stream.ToArray();
}
public SerialiseUsingStreamSpec(ITestOutputHelper outputHelper)
: base(outputHelper, "serialise_by_workbook")
{
using var stream = new MemoryStream();
using var serialiser = new ExcelSerializer(stream);
Run(serialiser);
bytes = stream.ToArray();
}
public SerialiseUsingRangeSpec()
{
var range = Worksheet.Range(StartRow, StartColumn, StartRow + Values.Length, StartColumn + 1);
using (var serialiser = new ExcelSerializer(range))
{
Run(serialiser);
}
}
public SerialiseUsingPathWithOffsetsSpec()
{
using (var serialiser = new ExcelSerializer(Path)
{
ColumnOffset = StartColumn - 1, RowOffset = StartRow - 1
}) {
Run(serialiser);
}
}
public SerialiseUsingPathWithOffsetsSpec(ITestOutputHelper outputHelper)
: base(outputHelper, "serialise_by_path_with_offsets", "Export", startRow: 5, startColumn: 5)
{
using var serialiser = new ExcelSerializer(Path)
{
ColumnOffset = StartColumn - 1,
RowOffset = StartRow - 1
};
Run(serialiser);
}
public void serializeExcel()
{
ExcelSerializer <User> serializer = new ExcelSerializer <User>();
object[] obj = serializer.Serialize(new User("Toto", "Titi"));
Assert.IsNotNull(obj);
Assert.AreEqual("Toto", obj[0]);
Assert.AreEqual("Titi", obj[1]);
}
public override void Write(T element, string path)
{
ExcelSerializer <T> serializer = new ExcelSerializer <T>(_PropertiesToSerialize);
object[] objectSerialize = serializer.Serialize(element);
ICollection <object[]> objects = new List <object[]>()
{
objectSerialize
};
Write(objects, path);
}
private MemoryStream GenerateRawData()
{
var s = new ExcelSerializer();
s.HasHeaderRecord = true;
var testData = GetTestData();
var stream = s.GenerateRawData(testData);
stream.Position = 0;
return(stream);
}
public static void TestFormatting()
{
var data = DataModelColumnFormatting.GenerateData(1000);
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\TestFormatting.xlsx";
using (var stream = new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
var excel = new ExcelSerializer();
var wb = TestData.CreateWorkbookProperties();
excel.Serialize(stream, new IEnumerable[] { data }, wb);
}
}
private bool OutputResult(IEnumerable <Transaction> transactions, Dictionary <string, Payment> payments)
{
using (var serializer = new ExcelSerializer(_outFile))
{
serializer.Serialize(transactions);
var paymentList = payments
.OrderBy(payment => payment.Value.Account)
.ThenBy(payment => payment.Value.Date).Select(payment => payment.Value);
serializer.Serialize(paymentList);
}
return(true);
}
private static IList <IList> TestReadExcel()
{
var wbProps = new WorkbookProperties();
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\TestWriteExcel.xlsx";
using (var fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite | FileShare.Delete))
{
var xls = new ExcelSerializer();
var result = xls.Deserialize(fs, wbProps);
var test = result[0].Cast <DataModel>().ToArray();
return(result);
}
}
public void TestSerialize()
{
using (var stream = new MemoryStream())
{
var excel = new ExcelSerializer();
var wb = TestData.CreateWorkbookProperties();
excel.Serialize(stream, TestData.ArrayOfModels, wb);
stream.Flush();
// The size changes all the time!
// Assert.AreEqual(9149, stream.Position, "Serialized byte count are equal.");
}
}
public ActionResult ExportExcel(short status = 1, string ProductCode = null, string Caption = null, int?Category = null, int?Brand = null, int StockMin = int.MinValue, int StockMax = int.MaxValue)
{
List <CatalogProductsExcelModel> model = m_catalogWorker.GetExcelData(status, ProductCode, Caption, Category, Brand, StockMin, StockMax);
try
{
MemoryStream ms = new MemoryStream();
Stream output = (Stream)ms;
ExcelSerializer.Serialize <CatalogProductsExcelModel>(model, ref output);
return(File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Urunler.xlsx"));
}
catch (Exception ex)
{
this.ShowError(ex);
return(null);
}
}
public void serializeExcelList()
{
ListSerializable <User> users = new UserList();
users.Add(new User("Toto", "Titi"));
users.Add(new User("Tata", "Roro"));
ExcelSerializer <User> serializer = new ExcelSerializer <User>();
List <object[]> objects = serializer.SerializeList <UserList>(users).ToList();
Assert.IsNotNull(objects);
object[] line1 = objects[0];
object[] line2 = objects[1];
Assert.AreEqual("Toto", line1[0]);
Assert.AreEqual("Titi", line1[1]);
Assert.AreEqual("Tata", line2[0]);
Assert.AreEqual("Roro", line2[1]);
}
public void TestExcelWrite()
{
var serializer = new ExcelSerializer();
serializer.HasHeaderRecord = true;
StreamTransport transport = new StreamTransport();
var build = new OutputBuilder()
.SetSerializer(serializer)
.AddListData(GetTestData());
var dataIntegrator = new Integrator();
var result = dataIntegrator.SendData(build, transport);
Stream ResultStream = transport.GetLastRawData();
Assert.True(ResultStream.Length > 0, "Stream cannot be empty");
}
private static void TestWriteExcel(IEnumerable dm1 = null)
{
var wb = TestData.CreateWorkbookProperties();
wb.Culture = CultureInfo.GetCultureInfo("de-DE");
if (dm1 == null)
{
dm1 = DataModel.GenerateData(100, true).OrderBy(m => m.MyInt).SplitBy(m => m.MyInt).ToArray();
}
var dm2 = DataModel.GenerateData(100, true).Select(m => new DataModel2(m));
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\TestWriteExcel.xlsx";
using (var fs = new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite | FileShare.Delete))
{
var xls = new ExcelSerializer();
xls.Serialize(fs, new IEnumerable[] { /*dm2,*/ dm1 }, wb);
fs.Flush(true);
}
}
public void Run()
{
AllValuesAreOfSameType();
foreach (var _object in ObjectLists)
{
var objectList = _object.Value.Value as List <object>;
if (!objectList.Any())
{
continue;
}
var mergeDef = ExcelSerializer.GetRange(Row, Column, Row + objectList.Count, Column);
Console.WriteLine(mergeDef);
Sheet.Cells[mergeDef].Merge = true;
Sheet.Cells[Row, Column].Value = _object.Key;
Sheet.Cells[Row, Column].Style.Font.Bold = true;
Sheet.Cells[Row, Column].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
foreach (var item in objectList)
{
var dvw = new DirectValueWriter(as Dictionary <string, ObjectWrapper>, Sheet, Row, Column + 1, false);
dvw.Run();
}
Row += objectList.Count;
Row += 1;
UpdatedColumns.Add(Column);
}
foreach (var updatedColumnIndex in UpdatedColumns)
{
Sheet.Column(updatedColumnIndex).AutoFit();
}
}
public void TestExcelRead()
{
List <ExcelTestClass> data = new List <ExcelTestClass>();
var serializer = new ExcelSerializer();
serializer.HasHeaderRecord = true;
StreamTransport transport = new StreamTransport();
transport.InputStream = GenerateRawData();
var dataIntegrator = new Integrator();
var build = new InputBuilder()
.SetSerializer(serializer)
.ReadAll(data);
dataIntegrator.ReceiveData(build, transport);
Assert.NotNull(data);
Assert.Equal(2, data.Count);
Assert.Equal("Thomas Jefferson", data[1].Name);
Assert.Equal(34.8M, data[0].NetWorth);
}
