public static XElement ConvertTableToHtml(SpreadsheetDocument sDoc, SmlToHtmlConverterSettings settings, string tableName)
{
var rangeXml = SmlDataRetriever.RetrieveTable(sDoc, tableName);
var xhtml = SmlToHtmlConverter.ConvertToHtmlInternal(sDoc, settings, rangeXml);
return(xhtml);
}
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);
}
}
public void SH001_TableNames(string name, int numberOfTables)
{
var sourceDir = new DirectoryInfo("../../../../TestFiles/");
var sourceXlsx = new FileInfo(Path.Combine(sourceDir.FullName, name));
using var sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, false);
var table = SmlDataRetriever.TableNames(sDoc);
Assert.Equal(numberOfTables, table.Length);
}
public void SH002_SheetNames(string name, int numberOfSheets)
{
var sourceXlsx = new FileInfo(Path.Combine(TestUtil.SourceDir.FullName, name));
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, false))
{
var sheetNames = SmlDataRetriever.SheetNames(sDoc);
Assert.Equal(numberOfSheets, sheetNames.Length);
}
}
public void SH002_SheetNames(string name, int numberOfSheets)
{
DirectoryInfo sourceDir = new DirectoryInfo("../../../../TestFiles/");
FileInfo sourceXlsx = new FileInfo(Path.Combine(sourceDir.FullName, name));
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(sourceXlsx.FullName, true))
{
var sheetNames = SmlDataRetriever.SheetNames(sDoc);
Assert.Equal(numberOfSheets, sheetNames.Length);
}
}
// ***********************************************************************************************************************************
#region PublicApis
public static XElement ConvertTableToHtml(SmlDocument smlDoc, SmlToHtmlConverterSettings settings, string tableName)
{
using (MemoryStream ms = new MemoryStream())
{
ms.Write(smlDoc.DocumentByteArray, 0, smlDoc.DocumentByteArray.Length);
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(ms, false))
{
var rangeXml = SmlDataRetriever.RetrieveTable(sDoc, tableName);
var xhtml = SmlToHtmlConverter.ConvertToHtmlInternal(sDoc, settings, rangeXml);
return(xhtml);
}
}
}
public void Sample1()
{
var fi = new FileInfo(GetFilePath("SampleSpreadsheet.xlsx"));
// Retrieve range from Sheet1
var data = SmlDataRetriever.RetrieveRange(fi.FullName, "Sheet1", "A1:C3");
Log.WriteLine(data.ToString());
// Retrieve entire sheet
data = SmlDataRetriever.RetrieveSheet(fi.FullName, "Sheet1");
Log.WriteLine(data.ToString());
// Retrieve table
data = SmlDataRetriever.RetrieveTable(fi.FullName, "VehicleTable");
Log.WriteLine(data.ToString());
}
private static void Main()
{
var fi = new FileInfo("../../SampleSpreadsheet.xlsx");
// Retrieve range from Sheet1
var data = SmlDataRetriever.RetrieveRange(fi.FullName, "Sheet1", "A1:C3");
Console.WriteLine(data);
// Retrieve entire sheet
data = SmlDataRetriever.RetrieveSheet(fi.FullName, "Sheet1");
Console.WriteLine(data);
// Retrieve table
data = SmlDataRetriever.RetrieveTable(fi.FullName, "VehicleTable");
Console.WriteLine(data);
}
static void Main(string[] args)
{
FileInfo fi = null;
fi = new FileInfo("../../SampleSpreadsheet.xlsx");
// Retrieve range from Sheet1
XElement data = SmlDataRetriever.RetrieveRange(fi.FullName, "Sheet1", "A1:C3");
Console.WriteLine(data);
// Retrieve entire sheet
data = SmlDataRetriever.RetrieveSheet(fi.FullName, "Sheet1");
Console.WriteLine(data);
// Retrieve table
data = SmlDataRetriever.RetrieveTable(fi.FullName, "VehicleTable");
Console.WriteLine(data);
}
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);
}
}
public void CF002(string name, string sheetName, string range, string expected, string expectedColor)
{
var sourceDir = new DirectoryInfo("../../../../TestFiles/");
var sourceXlsx = new FileInfo(Path.Combine(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);
}
using var sDoc = SpreadsheetDocument.Open(sourceCopiedToDestXlsx.FullName, true);
var rangeXml = SmlDataRetriever.RetrieveRange(sDoc, sheetName, range);
var displayValue = (string)rangeXml.Descendants("DisplayValue").FirstOrDefault();
var displayColor = (string)rangeXml.Descendants("DisplayColor").FirstOrDefault();
Assert.Equal(expected, displayValue);
Assert.Equal(expectedColor, displayColor);
}
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);
}
}
static void importFromOpenXML()
{
SmlDocument smldoc = new SmlDocument("C:\\Users\\fabio\\Desktop\\Book1.xlsx");
var rng = SmlDataRetriever.RetrieveRange(smldoc, "Sheet1", 2, 2, 8, 8);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("C:\\Users\\fabio\\Desktop\\Book1.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
int startRow;
int startCol;
findStartData(doc, out startCol, out startRow, 10, 10);
}
//streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "FormulasUpdated.xlsx"));
}
}
public void SH005_ConvertSheet(string name, string sheetName)
{
var sourceDir = new DirectoryInfo("../../../../TestFiles/");
var sourceXlsx = new FileInfo(Path.Combine(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, sourceCopiedToDestXlsx.Name.Replace(".xlsx", dataTemplateFileNameSuffix)));
using var sDoc = SpreadsheetDocument.Open(sourceCopiedToDestXlsx.FullName, true);
var settings = new SmlToHtmlConverterSettings();
var rangeXml = SmlDataRetriever.RetrieveSheet(sDoc, sheetName);
rangeXml.Save(dataXmlFi.FullName);
}
public void CF002(string name, string sheetName, string range, string expected, string expectedColor)
{
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 rangeXml = SmlDataRetriever.RetrieveRange(sDoc, sheetName, range);
string displayValue = (string)rangeXml.Descendants("DisplayValue").FirstOrDefault();
string displayColor = (string)rangeXml.Descendants("DisplayColor").FirstOrDefault();
Assert.Equal(expected, displayValue);
Assert.Equal(expectedColor, displayColor);
}
}
public string[] GetSheetNames(string filePath)
{
var sheets = SmlDataRetriever.SheetNames(filePath);
return(sheets);
}