public void ReadEnterpriseSiteArea()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(header);
                WriteRow(writer, "A2", 1, "Enterprise", "", "My Enterprise");
                WriteRow(writer, "A3", 2, "Site", "", "My Site");
                WriteRow(writer, "A4", 3, "Area", "", "My Area");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    command.Execute();
                }

                Assert.That(hierarchy, Is.Not.Null);
                Assert.That(hierarchy.GetCount(), Is.EqualTo(3));
                Assert.That(hierarchy.Enterprise, Is.Not.Null);
                Assert.That(hierarchy.Enterprise.Name, Is.EqualTo("My Enterprise"));
                Assert.That(hierarchy.Enterprise.Site, Is.Not.Empty);
                Assert.That(hierarchy.Enterprise.Site.Count, Is.EqualTo(1));
                Assert.That(hierarchy.Enterprise.Site[0].Name, Is.EqualTo("My Site"));
                Assert.That(hierarchy.Enterprise.Site[0].Area, Is.Not.Empty);
                Assert.That(hierarchy.Enterprise.Site[0].Area[0].Name, Is.EqualTo("My Area"));
                Assert.That(hierarchy.Enterprise.Site[0].Area[0].Area1, Is.Empty);
            }
        }
        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            if (worksheetWriter == null) return;

            worksheetWriter.Dispose();
            worksheetWriter = null;
        }
        public void Read2SitesWithAreas()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(header);
                WriteRow(writer, "A2", 1, "Enterprise", "", "My Enterprise");
                WriteRow(writer, "A3", 2, "Site", "", "Site 1");
                WriteRow(writer, "A4", 3, "Area", "", "Area A");
                WriteRow(writer, "A5", 2, "Site", "", "Site 2");
                WriteRow(writer, "A6", 3, "Area", "", "Area B");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    command.Execute();
                }

                Assert.That(hierarchy, Is.Not.Null);
                Assert.That(hierarchy.GetCount(), Is.EqualTo(5));
                Assert.That(hierarchy.Enterprise.FullName, Is.EqualTo("My Enterprise"));
                Assert.That(hierarchy.Enterprise.Site[0].FullName, Is.EqualTo("My Enterprise.Site 1"));
                Assert.That(hierarchy.Enterprise.Site[0].Area[0].FullName, Is.EqualTo("My Enterprise.Site 1.Area A"));
                Assert.That(hierarchy.Enterprise.Site[1].FullName, Is.EqualTo("My Enterprise.Site 2"));
                Assert.That(hierarchy.Enterprise.Site[1].Area[0].FullName, Is.EqualTo("My Enterprise.Site 2.Area B"));
            }
        }
Exemple #4
0
        public void Write()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
                {
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
                    writer.Write("One");
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B1"));

                    writer.MoveTo("D3");
                    writer.Write("New value at D3");
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("E3"));
                }
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
                reader.MoveTo("A1");
                Assert.That(reader.Read(), Is.EqualTo("One"));
                Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B1"));
                reader.MoveTo("D3");
                Assert.That(reader.Read(), Is.EqualTo("New value at D3"));
                Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("E3"));
            }
        }
Exemple #5
0
        public void Write2Rows()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
                {
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
                    writer.WriteRow(new List <string> {
                        "One", "Two", "Three"
                    });
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A2"));
                    writer.WriteRow(new List <string> {
                        "Four", "Five", "Six"
                    });
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A3"));
                }
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
                List <string>    list   = reader.ReadRow();
                Assert.That(list, Is.Not.Empty);
                Assert.That(list, Is.EquivalentTo(new List <string> {
                    "One", "Two", "Three"
                }));
                list = reader.ReadRow();
                Assert.That(list, Is.Not.Empty);
                Assert.That(list, Is.EquivalentTo(new List <string> {
                    "Four", "Five", "Six"
                }));
            }
        }
Exemple #6
0
 public void AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
 {
     if (worksheetWriter == null)
     {
         throw new ArgumentNullException("worksheetWriter");
     }
     if (FileContentStream == null)
     {
         throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
     }
     using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
     {
         //Access the main Workbook part, which contains data
         WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
         Sheet        ss           = ExcelUtility.FindSheet(sheetName, workbookPart);
         if (ss == null)
         {
             throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
         }
         WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
         if (worksheetPart != null)
         {
             worksheetWriter.CreateErrorRows(worksheetPart);
             worksheetPart.Worksheet.Save();
         }
     }
 }
 public void AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
 {
     if (worksheetWriter == null)
     {
         throw new ArgumentNullException("worksheetWriter");
     }
     if (FileContentStream == null)
     {
         throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
     }
     using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
     {
         //Access the main Workbook part, which contains data
         WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
         Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
         if (ss == null)
         {
             throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
         }
         WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
         if (worksheetPart != null)
         {
             worksheetWriter.CreateErrorRows(worksheetPart);
             worksheetPart.Worksheet.Save();
         }
     }
 }
 private static void WriteRow(IWorksheetWriter writer, string address, params string[] values)
 {
     writer.MoveTo(address);
     foreach (string value in values)
     {
         writer.Write(value);
     }
 }
Exemple #9
0
        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            if (worksheetWriter == null)
            {
                return;
            }

            worksheetWriter.Dispose();
            worksheetWriter = null;
        }
 private static void WriteRow(IWorksheetWriter writer, string address, int depth, string isa95Type, string isa95Class, string name)
 {
     writer.MoveTo(address);
     writer.Write(depth.ToString("0"));
     writer.Write(isa95Type);
     writer.Write(isa95Class);
     for (int i = 1; i < depth; i++)
     {
         writer.Write("");
     }
     writer.Write(name);
 }
        private void WriteHeaderRow(IWorksheetWriter worksheetWriter)
        {
            foreach (string headerColumn in _headerColumns)
            {
                worksheetWriter
                .SetBackgroundColor(Color.WhiteSmoke)
                .Write(headerColumn)
                .MoveRight();
            }

            worksheetWriter
            .NewLine();
        }
            public void Dispose()
            {
                if (writer != null)
                {
                    writer.Dispose();
                    writer = null;
                }

                if (spreadsheet != null)
                {
                    spreadsheet.Dispose();
                    spreadsheet = null;
                }
            }
            public void Dispose()
            {
                if (writer != null)
                {
                    writer.Dispose();
                    writer = null;
                }

                if (spreadsheet != null)
                {
                    spreadsheet.Dispose();
                    spreadsheet = null;
                }
            }
Exemple #14
0
        public void GetCurrentCell()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
                {
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
                    writer.MoveTo("B2");
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B2"));

                    writer.MoveTo(10, 20);
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("T10"));
                }
            }
        }
Exemple #15
0
 private void WriteTestValues()
 {
     using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
     {
         IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests");
         writer.WriteRow(new List <string> {
             "A-1", "B-1", "C-1"
         });
         writer.WriteRow(new List <string> {
             "A-2", "B-2", "C-2"
         });
         writer.WriteRow(new List <string> {
             "A-3", "B-3", "C-3"
         });
     }
 }
        public void ReadInvalidDepth()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(header);
                WriteRow(writer, "A2", 10, "Enterprise", "", "My Enterprise");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    Assert.Throws <InvalidOperationException>(command.Execute);
                }
            }
        }
Exemple #17
0
 public ExcelFileGenerator(ExcelColumnFactory factory,
                           IWorksheetWriter worksheetWriter,
                           ISharedStringsWriter sharedStringsWriter,
                           IStylesWriter stylesWriter,
                           Stream templateStream,
                           CompressionStrategy compressionStrategy,
                           CompressionLevel compressionLevel,
                           Func <IEnumerable <PropertyInfo>, IEnumerable <PropertyInfo> > propertiesFilter)
 {
     _factory             = factory;
     _worksheetWriter     = worksheetWriter;
     _sharedStringsWriter = sharedStringsWriter;
     _stylesWriter        = stylesWriter;
     _templateStream      = templateStream;
     _compressionStrategy = compressionStrategy;
     _compressionLevel    = compressionLevel;
     _propertiesFilter    = propertiesFilter;
 }
        public void ReadInvalidDepth()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(header);
                WriteRow(writer, "A2", "", "", "", "My Enterprise");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
                    Assert.That(ex.Message, Is.StringContaining("My Enterprise").And.StringContaining("Level 4"));
                }
            }
        }
Exemple #19
0
        public void ReadOnlyWorksheet()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                Assert.That(spreadsheet.IsReadOnly, Is.False);
                IWorksheetWriter worksheet = spreadsheet.WriteToWorksheet("New sheet");
                worksheet.WriteRow(new List <string> {
                    "One", "Two", "Three"
                });
                Assert.That(worksheet, Is.Not.Null);
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Assert.That(spreadsheet.IsReadOnly, Is.True);
                Assert.Throws <InvalidOperationException>(() => spreadsheet.WriteToWorksheet("Should fail"));
            }
        }
        public void HeaderWithIncorrectNames()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(new List <string> {
                    "Column 1", "Column 2", "Column 3", "Column 4"
                });
                WriteRow(writer, "A2", "My Enterprise");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
                    Assert.That(ex.Message, Is.StringContaining("Column 1").And.StringContaining("Level 1"));
                }
            }
        }
 public void InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
 {
     if (worksheetWriter == null)
     {
         throw new ArgumentNullException("worksheetWriter");
     }
     byte[] byteArray = File.ReadAllBytes(templatePath);
     MemoryStream stream = new MemoryStream(byteArray);
     using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
     {
         // Change from template type to workbook type
         spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
         WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
         if (worksheetPart != null)
         {
             worksheetWriter.CreateHeader(worksheetPart);
         }
     }
     FileContentStream = stream;
 }
        public void ReadEmpty()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(header);
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    command.Execute();
                }

                Assert.That(hierarchy, Is.Not.Null);
                Assert.That(hierarchy.Enterprise, Is.Null);
                Assert.That(hierarchy.GetCount(), Is.EqualTo(0));
            }
        }
Exemple #23
0
        public void ReadRow()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests");
                List <string>    list   = new List <string> {
                    "One", "Two", "Three"
                };
                writer.WriteRow(list);
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
                List <string>    list   = reader.ReadRow();
                Assert.That(list, Is.Not.Empty);
                Assert.That(list, Is.EquivalentTo(new List <string> {
                    "One", "Two", "Three"
                }));
            }
        }
Exemple #24
0
        public void InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
        {
            if (worksheetWriter == null)
            {
                throw new ArgumentNullException("worksheetWriter");
            }
            byte[]       byteArray = File.ReadAllBytes(templatePath);
            MemoryStream stream    = new MemoryStream(byteArray);

            using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
            {
                // Change from template type to workbook type
                spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
                WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
                if (worksheetPart != null)
                {
                    worksheetWriter.CreateHeader(worksheetPart);
                }
            }
            FileContentStream = stream;
        }
        public void Level2HeaderWithGreaterDepth()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(new List <string> {
                    "Level 1", "Level 2"
                });
                WriteRow(writer, "A2", "My Enterprise");
                WriteRow(writer, "A3", "", "My Site");
                WriteRow(writer, "A4", "", "", "My Area");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
                    Assert.That(ex.Message, Is.StringContaining("row 4"));
                }
            }
        }
Exemple #26
0
        public void MoveToAndWrite()
        {
            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
                {
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
                    writer.MoveTo("B2");
                    writer.WriteRow(new List <string> {
                        "One", "Two", "Three"
                    });
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B3"));
                    writer.WriteRow(new List <string> {
                        "Four", "Five", "Six"
                    });
                    Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B4"));
                }
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                using (IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests"))
                {
                    Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("A1"));
                    Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string>()));

                    reader.MoveTo("B2");
                    Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B2"));
                    Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
                        "One", "Two", "Three"
                    }));
                    Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B3"));
                    Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
                        "Four", "Five", "Six"
                    }));
                }
            }
        }
        public void Level2HeaderEnterpriseOnly()
        {
            using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
            {
                writer.WriteRow(new List <string> {
                    "Level 1", "Level 2"
                });
                WriteRow(writer, "A2", "My Enterprise");
            }

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                Hierarchy hierarchy = Hierarchy.Empty();
                using (ICommand command = CreateCommand(spreadsheet, hierarchy))
                {
                    command.Execute();
                }

                Assert.That(hierarchy, Is.Not.Null);
                Assert.That(hierarchy.Enterprise, Is.Not.Null);
                Assert.That(hierarchy.Enterprise.Name, Is.EqualTo("My Enterprise"));
                Assert.That(hierarchy.GetCount(), Is.EqualTo(1));
            }
        }
Exemple #28
0
        public void ReadAndWrite()
        {
            List <string> list = new List <string> {
                "One", "Two", "Three"
            };

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
            {
                IWorksheetWriter worksheet = spreadsheet.WriteToWorksheet("New sheet");
                Assert.That(worksheet, Is.Not.Null);
                worksheet.WriteRow(list);
            }

            List <string> result;

            using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
            {
                IWorksheetReader worksheet = spreadsheet.ReadWorksheet("New sheet");
                Assert.That(worksheet, Is.Not.Null);
                result = worksheet.ReadRow();
            }

            Assert.That(result, Is.EquivalentTo(list));
        }
 public Worksheet(string filename, string name)
 {
     spreadsheet = ExcelSpreadsheet.CreateNew(filename);
     writer      = spreadsheet.WriteToWorksheet(name);
 }
 /// <summary>
 /// Initializes a new instance of the <see cref="ExcelWriterCommand"/> class.
 /// </summary>
 /// <param name="spreadsheet">The spreadsheet.</param>
 /// <param name="name">The name.</param>
 protected ExcelWriterCommand(IExcelSpreadsheet spreadsheet, string name)
 {
     worksheetWriter = spreadsheet.WriteToWorksheet(name);
 }
Exemple #31
0
 /// <summary>
 /// Initializes a new instance of the <see cref="ExcelWriterCommand"/> class.
 /// </summary>
 /// <param name="spreadsheet">The spreadsheet.</param>
 /// <param name="name">The name.</param>
 protected ExcelWriterCommand(IExcelSpreadsheet spreadsheet, string name)
 {
     worksheetWriter = spreadsheet.WriteToWorksheet(name);
 }
 private static void WriteRow(IWorksheetWriter writer, string address, int depth, string isa95Type, string isa95Class, string name)
 {
     writer.MoveTo(address);
     writer.Write(depth.ToString("0"));
     writer.Write(isa95Type);
     writer.Write(isa95Class);
     for (int i = 1; i < depth; i++)
     {
         writer.Write("");
     }
     writer.Write(name);
 }
 private static void WriteRow(IWorksheetWriter writer, string address, params string[] values)
 {
     writer.MoveTo(address);
     foreach (string value in values)
     {
         writer.Write(value);
     }
 }
 public Worksheet(string filename, string name)
 {
     spreadsheet = ExcelSpreadsheet.CreateNew(filename);
     writer = spreadsheet.WriteToWorksheet(name);
 }