Пример #1
0
        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);
                }
        }
Пример #2
0
        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);
                }
        }
Пример #3
0
        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);
                }
        }
Пример #4
0
        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);
            }
        }
Пример #5
0
        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);
            }
        }
Пример #6
0
        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);
            }
        }
Пример #7
0
        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);
            }
        }