public xlBook ReadToBook(SpreadsheetDocument document)
{
using (_docProvider = new DocDictionaries(document, _config.AutoDispose))
{
xlBook result = new xlBook();
for (int i = 0; i < document.WorkbookPart.WorksheetParts.Count(); i++)
{
result.AddSheet(_docProvider.sheetNames[i]);
var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().SingleOrDefault(s => s.Name == _docProvider.sheetNames[i]);
var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
foreach (var cell in worksheetPart.Worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
{
try
{
if (cell == null) { continue; }
result[i].Cells.Add(ReadCell(cell));
}
catch (Exception)
{
if (_config.ContinueOnRowReadingError) continue; else throw;
}
}
}
return result;
}
}
public void WriteEmptyBook()
{
var book = new xlBook();
book.AddSheet("test1");
book.Sheets.First().AddCell(12.3, "B2", xlContentType.Double);
book.Sheets.First().AddCell(DateTime.Now, "C3", xlContentType.Date);
book.Sheets.First().AddCell("asdasd", "D4", xlContentType.SharedString);
var err = xlWriter.Create(book).SaveToStream(new System.IO.MemoryStream());
if (err.Count() > 0)
Assert.Fail("Ошибка сохранения:\n{0}", string.Join("\n", err.Select(x => x.Description)));
}
public void TestMethod1()
{
var book = new xlBook();
var sh = book.AddSheet("sheet1");
sh.AddCell("Field", "A1", xlContentType.SharedString);
sh.AddCell("1", "A2", xlContentType.SharedString);
sh.AddCell(1, "A3", xlContentType.Double);
sh.AddCell(1, "A4", xlContentType.Integer);
sh.AddCell(1, "A5", xlContentType.Void);
var memstream = new MemoryStream();
var err = xlWriter.Create(book).SaveToStream(memstream);
Assert.IsFalse(err.Any(), string.Join("\n", err.Select(x => x.Description)));
var result = XLOC.XlConverter.FromStream(memstream).ReadToArray<MyClass>();
Assert.AreEqual(4, result.Count());
Assert.IsTrue(result.All(x => x.Field == 1));
}
public void ReadToArrayWithoutNullableColumns()
{
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sh = book.AddSheet("sheet1");
#region Captions
//=================================================
sh.AddCell("Поле 1", "A1", xlContentType.SharedString);
sh.AddCell("Какая-то дата", "B1", xlContentType.SharedString);
sh.AddCell("Мультизагаловок2", "C1", xlContentType.SharedString);
sh.AddCell("дробь", "E1", xlContentType.SharedString);
//=================================================
#endregion
#region Data
//=================================================
sh.AddCell(1, "A2", xlContentType.Integer);
sh.AddCell(DateTime.Now, "B2", xlContentType.Date);
sh.AddCell("Какая-то строка", "C2", xlContentType.SharedString);
sh.AddCell("0.15", "E2", xlContentType.Double);
sh.AddCell(2, "A3", xlContentType.Integer);
sh.AddCell(DateTime.Now, "B3", xlContentType.Date);
sh.AddCell("Какая-то строка", "C3", xlContentType.SharedString);
sh.AddCell("0.25", "E3", xlContentType.Double);
//=================================================
#endregion
xlWriter.Create(book).SaveToStream(memstream);
TestExcelClass[] data = XlConverter.FromStream(memstream).ReadToEnumerable<TestExcelClass>().ToArray();
Assert.AreEqual(2, data.Count());
Assert.IsTrue(data.All(x => !x.intProperty2.HasValue));
}
}
public void TestInitialize()
{
xl = new xlBook();
xs = xl.AddSheet("test");
}
public void ReadBook(string path)
{
try
{
using (var file = File.Open(path, FileMode.Open))
{
var streamReader = XLOC.XlConverter.FromStream(file);
xl = streamReader.ReadToBook();
}
var fileReader = XLOC.XlConverter.FromFile(path);
xl = fileReader.ReadToBook();
}
catch (Exception ex) { Assert.Fail(string.Format("Ошибка чтения\n{0}", ex.Message)); }
}
public void Initialize()
{
var xl = new xlBook();
xl.AddSheet("testList");
xs = xl.Sheets.First();
}
public void ReadToArrayWithNullableColumns()
{
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sh = book.AddSheet("sheet1");
#region Captions
//=================================================
sh.AddCell("Поле 1", "A1", xlContentType.SharedString);
sh.AddCell("Какая-то дата", "B1", xlContentType.SharedString);
sh.AddCell("Мультизагаловок2", "C1", xlContentType.SharedString);
sh.AddCell("дробь", "E1", xlContentType.SharedString);
sh.AddCell("Поле 3", "F1", xlContentType.SharedString);
//=================================================
#endregion
#region Data
//=================================================
sh.AddCell(1, "A2", xlContentType.SharedString);
sh.AddCell(DateTime.Now, "B2", xlContentType.Date);
sh.AddCell("Какая-то строка", "C2", xlContentType.SharedString);
sh.AddCell("0.15", "E2", xlContentType.Double);
sh.AddCell("", "F2", xlContentType.SharedString);
sh.AddCell(2, "A3", xlContentType.Integer);
sh.AddCell(DateTime.Now, "B3", xlContentType.Date);
sh.AddCell("Какая-то строка", "C3", xlContentType.SharedString);
sh.AddCell("0.25", "E3", xlContentType.Double);
sh.AddCell("", "F3", xlContentType.SharedString);
//=================================================
#endregion
xlWriter.Create(book).SaveToStream(memstream);
TestExcelClass[] data = XLOC.XlConverter.FromStream(memstream, new XLOCConfiguration { CellReadingErrorEvent = (s, e) => { throw new Exception(e.Exception.Message); } }).ReadToArray<TestExcelClass>();
Assert.AreEqual(2, data.Count());
Assert.IsTrue(data.All(x => !x.intProperty3.HasValue));
}
}
public void AutoDisposing()
{
int countShouldBe = 4;
using (var ms = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("test");
sheet.AddCell("Поле 1", $"A1", xlContentType.SharedString);
sheet.AddCell("Какая-то дата", $"B1", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", $"C1", xlContentType.SharedString);
sheet.AddCell("дробь", $"AB1", xlContentType.SharedString);
sheet.AddCell("noize", $"AC1", xlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", xlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"{(i / 100M).ToString("E")}", $"AB{i}", xlContentType.Double);
sheet.AddCell($"noize", $"AC{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(ms);
var data = XlConverter.FromStream(ms, new XLOCConfiguration { SkipMode = SkipModeEnum.Auto, ContinueOnRowReadingError = false, AutoDispose = false }).ReadToEnumerable<TestExcelClass>();
Assert.AreEqual(countShouldBe, data.Count());
Assert.IsTrue(data.All(x => x.decimalProperty != 0));
}
}
public void SkiperAuto()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("test");
int skip = new Random().Next(4) + 1;
for (int i = 0; i < skip; i++)
{
sheet.AddCell($"Caption{i + 1}", $"A{i + 1}", xlContentType.SharedString);
}
sheet.AddCell("Поле 1", $"A{++skip}", xlContentType.SharedString);
sheet.AddCell("Какая-то дата", $"B{skip}", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", $"C{skip}", xlContentType.SharedString);
sheet.AddCell("дробь", $"E{skip}", xlContentType.SharedString);
for (int i = ++skip; i < skip + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", xlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(memstream);
var convertor = XlConverter.FromStream(memstream, new XLOCConfiguration { SkipMode = SkipModeEnum.Auto, SkipCount = 1 });
Assert.AreEqual(countShouldBe, convertor.ReadToArray<TestExcelClass>().Count());
}
}
public void SkiperManual()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("test");
sheet.AddCell("Caption", "A1", xlContentType.SharedString);
sheet.AddCell("Caption2", "A2", xlContentType.SharedString);
sheet.AddCell("Поле 1", "A3", xlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B3", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок2", "D3", xlContentType.SharedString);
sheet.AddCell("дробь", "E3", xlContentType.SharedString);
for (int i = 4; i < 4 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", xlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(memstream);
var convertor = XlConverter.FromStream(memstream, new XLOCConfiguration { SkipMode = SkipModeEnum.Manual, SkipCount = 2 });
Assert.AreEqual(countShouldBe, convertor.ReadToArray<TestExcelClass>().Count());
}
}
public void CellEventTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("sheet1");
sheet.AddCell("Поле 1", "A1", xlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B1", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", xlContentType.SharedString);
sheet.AddCell("дробь", "E1", xlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell("A", $"A{i}", xlContentType.SharedString);
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(memstream);
bool result = true;
XlConverter.FromStream(memstream, new XLOCConfiguration { CellReadingErrorEvent = (s, e) => { if (e.Reference != "A2") result = false; } }).ReadToArray<TestExcelClass>();
Assert.IsFalse(result);
TestExcelClass[] data = XlConverter.FromStream(memstream).ReadToEnumerable<TestExcelClass>().ToArray();
}
}
public void ValidationEventTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("sheet1");
sheet.AddCell("Какая-то дата", "B1", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", xlContentType.SharedString);
sheet.AddCell("дробь", "E1", xlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(memstream);
XlConverter.FromStream(memstream, new XLOCConfiguration { ValidationFailureEvent = (s, e) => { if (!e.MissingFields.Contains("Поле 1")) Assert.Fail(); } }).ReadToArray<TestExcelClass>();
TestExcelClass[] data = XlConverter.FromStream(memstream).ReadToEnumerable<TestExcelClass>().ToArray();
}
}
public void MultiCaptionTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new xlBook();
var sheet = book.AddSheet("sheet1");
sheet.AddCell("Поле 1", "A1", xlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B1", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", xlContentType.SharedString);
sheet.AddCell("Мультизагаловок2", "D1", xlContentType.SharedString);
sheet.AddCell("дробь", "E1", xlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", xlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", xlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", xlContentType.SharedString);
sheet.AddCell($"Какая-то строка{i}", $"D{i}", xlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", xlContentType.Double);
}
xlWriter.Create(book).SaveToStream(memstream);
var isValid = true;
TestExcelClass[] data = XlConverter.FromStream(memstream, new XLOCConfiguration { ValidationFailureEvent = (s, e) => isValid = false }).ReadToEnumerable<TestExcelClass>().ToArray();
Assert.AreEqual(0, data.Count());
Assert.IsFalse(isValid);
}
}
//=================================================
internal xlBookWriter(xlBook book)
{
_xlBook = book;
}