public void ShouldInsertInSharedStringTable()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.AllTypes.xlsx"))
using (var ms = new MemoryStream())
{
rs.CopyTo(ms);
var doc = SpreadsheetDocument.Open(ms, true);
var newId = SpreadsheetHelper.InsertInSharedString(doc, "new value");
Assert.AreEqual(4, newId);
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet1");
var cell = SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("B"), 12);
cell.CellValue = new Spreadsheet.CellValue("4");
doc.Save();
Assert.AreEqual("4", cell.InnerText);
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldSetCellStyles()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.Styles.xlsx"))
using (var ms = new MemoryStream())
{
rs.CopyTo(ms);
ms.Position = 0;
using (var excel = new Excel(ms, ExcelMode.Open))
{
var style = excel["Sheet1"].Styles["B", 2];
for (var line = 3; line <= 6; line++)
{
excel["Sheet1"].Styles["B", line] = style;
}
excel.Save();
}
using (var excel = new Excel(ms, ExcelMode.OpenReadOnly))
{
var sheet = SpreadsheetHelper.GetSheetData(excel.OpenXmlDocument, "Sheet1");
var b = BaseAZ.Parse("B");
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 2).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 3).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 4).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 5).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 6).StyleIndex.InnerText);
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldCleanCellsIfSetEmpty()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.AllTypes.xlsx"))
using (var ms = new MemoryStream())
{
rs.CopyTo(ms);
using (var excel = new Excel(ms, ExcelMode.Open))
{
excel["Sheet1"].Values["B", 2] = null;
excel["Sheet1"].Values["B", 3] = null;
excel["Sheet1"].Values["B", 4] = null;
excel["Sheet1"].Values["B", 5] = null;
excel["Sheet1"].Values["B", 6] = null;
excel["Sheet1"].Values["B", 7] = null;
excel["Sheet1"].Values["B", 8] = null;
excel["Sheet1"].Values["B", 9] = null;
excel["Sheet1"].Values["B", 10] = null;
excel["Sheet1"].Values["B", 11] = null;
excel["Sheet1"].Values["B", 12] = null;
excel["Sheet1"].Values["B", 13] = null;
excel["Sheet1"].Values["B", 14] = null;
excel["Sheet1"].Values["B", 15] = null;
excel["Sheet1"].Values["B", 16] = null;
excel["Sheet1"].Values["B", 17] = null;
excel["Sheet1"].Values["B", 18] = null;
excel.Save();
}
using (var excel = new Excel(ms, ExcelMode.OpenReadOnly))
{
Assert.IsNull(excel["Sheet1"].Values["B", 2].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 3].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 4].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 5].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 6].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 7].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 8].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 9].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 10].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 11].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 12].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 13].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 14].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 15].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 16].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 17].ToString());
Assert.IsNull(excel["Sheet1"].Values["B", 18].ToString());
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldCreateSheetCells()
{
using (var ms = new MemoryStream())
{
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3", createIfDoesntExists: true);
Action <string, uint, int> setCell = (column, line, value) =>
{
var azColumn = BaseAZ.Parse(column);
var valueStr = value.ToString();
SpreadsheetHelper.GetCell(sheetData, azColumn, line, createIfDoesntExists: true).CellValue
= new DocumentFormat.OpenXml.Spreadsheet.CellValue(valueStr);
};
setCell("C", 3, 5);
setCell("C", 1, 8);
setCell("C", 5, 2);
setCell("A", 3, 4);
setCell("A", 1, 7);
setCell("A", 5, 1);
setCell("E", 3, 6);
setCell("E", 1, 9);
setCell("E", 5, 3);
doc.Save();
doc.Dispose();
}
using (var doc = SpreadsheetDocument.Open(ms, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3");
Assert.AreEqual("1", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 5).CellValue.InnerText);
Assert.AreEqual("2", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 5).CellValue.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 5).CellValue.InnerText);
Assert.AreEqual("4", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 3).CellValue.InnerText);
Assert.AreEqual("5", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 3).CellValue.InnerText);
Assert.AreEqual("6", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 3).CellValue.InnerText);
Assert.AreEqual("7", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 1).CellValue.InnerText);
Assert.AreEqual("8", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 1).CellValue.InnerText);
Assert.AreEqual("9", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 1).CellValue.InnerText);
doc.Dispose();
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShoudWriteAndReadACell()
{
using (var ms = new MemoryStream())
{
using (var excel = new Excel(ms, ExcelMode.Create))
{
excel["test"].Values["b", 2] = "info";
excel.Save();
}
using (var excel = new Excel(ms, ExcelMode.OpenReadOnly))
{
var value = excel["test"].Values["b", 2].ToString();
Assert.AreEqual("info", value);
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldSetCells()
{
using (var ms = new MemoryStream())
{
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
var sheet = SpreadsheetHelper.GetSheetData(doc, "Sheet3", createIfDoesntExists: true);
SpreadsheetHelper.SetValue(doc, null, "A", CellValues.SharedString, sheet, BaseAZ.Parse("A"), 5);
SpreadsheetHelper.SetValue(doc, null, "B", CellValues.SharedString, sheet, BaseAZ.Parse("C"), 5);
SpreadsheetHelper.SetValue(doc, null, "C", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 5);
SpreadsheetHelper.SetValue(doc, null, "125", null, sheet, BaseAZ.Parse("A"), 3);
SpreadsheetHelper.SetValue(doc, null, "458", CellValues.SharedString, sheet, BaseAZ.Parse("C"), 3);
SpreadsheetHelper.SetValue(doc, null, "4.586", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 3);
SpreadsheetHelper.SetValue(doc, null, "1", CellValues.Boolean, sheet, BaseAZ.Parse("A"), 1);
SpreadsheetHelper.SetValue(doc, null, "0", CellValues.Boolean, sheet, BaseAZ.Parse("C"), 1);
SpreadsheetHelper.SetValue(doc, null, "info", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 1);
doc.Save();
doc.Dispose();
}
using (var doc = SpreadsheetDocument.Open(ms, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3");
Assert.AreEqual("A", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 5));
Assert.AreEqual("B", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 5));
Assert.AreEqual("C", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 5));
Assert.AreEqual("125", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 3));
Assert.AreEqual("458", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 3));
Assert.AreEqual("4.586", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 3));
Assert.AreEqual("1", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 1));
Assert.AreEqual("0", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 1));
Assert.AreEqual("info", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 1));
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldCreateSheetData()
{
using (var ms = new MemoryStream())
{
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
SpreadsheetHelper.GetSheetData(doc, "Sheet3", createIfDoesntExists: true);
doc.Save();
doc.Dispose();
}
using (var doc = SpreadsheetDocument.Open(ms, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3");
Assert.IsNotNull(sheetData);
doc.Dispose();
}
DumpGeneratedExcelFiles.Dump(ms);
}
}