| /*************************************************************************** |
| |
| Copyright (c) Microsoft Corporation 2012-2015. |
| |
| This code is licensed using the Microsoft Public License (Ms-PL). The text of the license can be found here: |
| |
| http://www.microsoft.com/resources/sharedsource/licensingbasics/publiclicense.mspx |
| |
| Published at http://OpenXmlDeveloper.org |
| Resource Center and Documentation: http://openxmldeveloper.org/wiki/w/wiki/powertools-for-open-xml.aspx |
| |
| Developer: Eric White |
| Blog: http://www.ericwhite.com |
| Twitter: @EricWhiteDev |
| Email: eric@ericwhite.com |
| |
| ***************************************************************************/ |
| |
| using System; |
| using System.Collections.Generic; |
| using System.Drawing; |
| using System.Drawing.Imaging; |
| using System.IO; |
| using System.Linq; |
| using System.Text; |
| using System.Threading.Tasks; |
| using System.Xml.Linq; |
| using DocumentFormat.OpenXml.Packaging; |
| using OpenXmlPowerTools; |
| using Xunit; |
| |
| #if !ELIDE_XUNIT_TESTS |
| |
| namespace OxPt |
| { |
| public class ShTests |
| { |
| // PowerShell oneliner that generates InlineData for all files in a directory |
| // dir | % { '[InlineData("' + $_.Name + '")]' } | clip |
| |
| [Theory] |
| [InlineData("SH101-SimpleFormats.xlsx", "Sheet1")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1")] |
| [InlineData("SH103-No-SharedString.xlsx", "Sheet1")] |
| [InlineData("SH104-With-SharedString.xlsx", "Sheet1")] |
| [InlineData("SH105-No-SharedString.xlsx", "Sheet1")] |
| [InlineData("SH106-9-x-9-Formatted.xlsx", "Sheet1")] |
| [InlineData("SH108-SimpleFormattedCell.xlsx", "Sheet1")] |
| [InlineData("SH109-CellWithBorder.xlsx", "Sheet1")] |
| [InlineData("SH110-CellWithMasterStyle.xlsx", "Sheet1")] |
| [InlineData("SH111-ChangedDefaultColumnWidth.xlsx", "Sheet1")] |
| [InlineData("SH112-NotVertMergedCell.xlsx", "Sheet1")] |
| [InlineData("SH113-VertMergedCell.xlsx", "Sheet1")] |
| [InlineData("SH114-Centered-Cell.xlsx", "Sheet1")] |
| [InlineData("SH115-DigitsToRight.xlsx", "Sheet1")] |
| [InlineData("SH116-FmtNumId-1.xlsx", "Sheet1")] |
| [InlineData("SH117-FmtNumId-2.xlsx", "Sheet1")] |
| [InlineData("SH118-FmtNumId-3.xlsx", "Sheet1")] |
| [InlineData("SH119-FmtNumId-4.xlsx", "Sheet1")] |
| [InlineData("SH120-FmtNumId-9.xlsx", "Sheet1")] |
| [InlineData("SH121-FmtNumId-11.xlsx", "Sheet1")] |
| [InlineData("SH122-FmtNumId-12.xlsx", "Sheet1")] |
| [InlineData("SH123-FmtNumId-14.xlsx", "Sheet1")] |
| [InlineData("SH124-FmtNumId-15.xlsx", "Sheet1")] |
| [InlineData("SH125-FmtNumId-16.xlsx", "Sheet1")] |
| [InlineData("SH126-FmtNumId-17.xlsx", "Sheet1")] |
| [InlineData("SH127-FmtNumId-18.xlsx", "Sheet1")] |
| [InlineData("SH128-FmtNumId-19.xlsx", "Sheet1")] |
| [InlineData("SH129-FmtNumId-20.xlsx", "Sheet1")] |
| [InlineData("SH130-FmtNumId-21.xlsx", "Sheet1")] |
| [InlineData("SH131-FmtNumId-22.xlsx", "Sheet1")] |
| |
| public void SH005_ConvertSheet(string name, string sheetName) |
| { |
| FileInfo sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name)); |
| |
| var sourceCopiedToDestXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", "-1-Source.xlsx"))); |
| if (!sourceCopiedToDestXlsx.Exists) |
| File.Copy(sourceXlsx.FullName, sourceCopiedToDestXlsx.FullName); |
| |
| var dataTemplateFileNameSuffix = "-2-Generated-XmlData-Entire-Sheet.xml"; |
| var dataXmlFi = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", dataTemplateFileNameSuffix))); |
| using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true)) |
| { |
| var settings = new SmlToHtmlConverterSettings(); |
| var rangeXml = SmlDataRetriever.RetrieveSheet(sDoc, sheetName); |
| rangeXml.Save(dataXmlFi.FullName); |
| } |
| } |
| |
| [Theory] |
| [InlineData("SH101-SimpleFormats.xlsx", "Sheet1", "A1:B10")] |
| [InlineData("SH101-SimpleFormats.xlsx", "Sheet1", "A4:B8")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "C2:C2")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "A9:A9")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "I1:I1")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "I9:I9")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "A1:I9")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "A2:D4")] |
| [InlineData("SH102-9-x-9.xlsx", "Sheet1", "C5:G7")] |
| [InlineData("SH103-No-SharedString.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH104-With-SharedString.xlsx", "Sheet1", "A4:A7")] |
| [InlineData("SH105-No-SharedString.xlsx", "Sheet1", "A4:A7")] |
| [InlineData("SH106-9-x-9-Formatted.xlsx", "Sheet1", "A1:I9")] |
| [InlineData("SH108-SimpleFormattedCell.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH109-CellWithBorder.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH110-CellWithMasterStyle.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH111-ChangedDefaultColumnWidth.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH112-NotVertMergedCell.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH113-VertMergedCell.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH114-Centered-Cell.xlsx", "Sheet1", "A1:A1")] |
| [InlineData("SH115-DigitsToRight.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH116-FmtNumId-1.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH117-FmtNumId-2.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH118-FmtNumId-3.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH119-FmtNumId-4.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH120-FmtNumId-9.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH121-FmtNumId-11.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH122-FmtNumId-12.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH123-FmtNumId-14.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH124-FmtNumId-15.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH125-FmtNumId-16.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH126-FmtNumId-17.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH127-FmtNumId-18.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH128-FmtNumId-19.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH129-FmtNumId-20.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH130-FmtNumId-21.xlsx", "Sheet1", "A1:A10")] |
| [InlineData("SH131-FmtNumId-22.xlsx", "Sheet1", "A1:A10")] |
| |
| public void SH004_ConvertRange(string name, string sheetName, string range) |
| { |
| FileInfo sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name)); |
| |
| var sourceCopiedToDestXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", "-1-Source.xlsx"))); |
| if (!sourceCopiedToDestXlsx.Exists) |
| File.Copy(sourceXlsx.FullName, sourceCopiedToDestXlsx.FullName); |
| |
| var dataTemplateFileNameSuffix = string.Format("-2-Generated-XmlData-{0}.xml", range.Replace(":", "")); |
| var dataXmlFi = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", dataTemplateFileNameSuffix))); |
| using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true)) |
| { |
| var settings = new SmlToHtmlConverterSettings(); |
| var rangeXml = SmlDataRetriever.RetrieveRange(sDoc, sheetName, range); |
| rangeXml.Save(dataXmlFi.FullName); |
| } |
| } |
| |
| |
| [Theory] |
| [InlineData("SH001-Table.xlsx", "MyTable")] |
| [InlineData("SH003-TableWithDateInFirstColumn.xlsx", "MyTable")] |
| [InlineData("SH004-TableAtOffsetLocation.xlsx", "MyTable")] |
| [InlineData("SH005-Table-With-SharedStrings.xlsx", "Table1")] |
| [InlineData("SH006-Table-No-SharedStrings.xlsx", "Table1")] |
| [InlineData("SH107-9-x-9-Formatted-Table.xlsx", "Table1")] |
| [InlineData("SH007-One-Cell-Table.xlsx", "Table1")] |
| [InlineData("SH008-Table-With-Tall-Row.xlsx", "Table1")] |
| [InlineData("SH009-Table-With-Wide-Column.xlsx", "Table1")] |
| |
| public void SH003_ConvertTable(string name, string tableName) |
| { |
| FileInfo sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name)); |
| |
| var sourceCopiedToDestXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", "-1-Source.xlsx"))); |
| if (!sourceCopiedToDestXlsx.Exists) |
| File.Copy(sourceXlsx.FullName, sourceCopiedToDestXlsx.FullName); |
| |
| var dataXmlFi = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, sourceXlsx.Name.Replace(".xlsx", "-2-Generated-XmlData.xml"))); |
| using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true)) |
| { |
| var settings = new SmlToHtmlConverterSettings(); |
| var rangeXml = SmlDataRetriever.RetrieveTable(sDoc, tableName); |
| rangeXml.Save(dataXmlFi.FullName); |
| } |
| } |
| |
| [Theory] |
| [InlineData("Spreadsheet.xlsx", 2)] |
| public void SH002_SheetNames(string name, int numberOfSheets) |
| { |
| FileInfo sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name)); |
| using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true)) |
| { |
| var sheetNames = SmlDataRetriever.SheetNames(sDoc); |
| Assert.Equal(numberOfSheets, sheetNames.Length); |
| } |
| } |
| |
| [Theory] |
| [InlineData("SH001-Table.xlsx", 1)] |
| [InlineData("SH002-TwoTablesTwoSheets.xlsx", 2)] |
| public void SH001_TableNames(string name, int numberOfTables) |
| { |
| FileInfo sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name)); |
| using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true)) |
| { |
| var table = SmlDataRetriever.TableNames(sDoc); |
| Assert.Equal(numberOfTables, table.Length); |
| } |
| } |
| } |
| } |
| |
| #endif |