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