blob: 08d4b4509c0372ee15fbb9d7d101e52921d4fbce [file] [log] [blame]
/***************************************************************************
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