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 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 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);
}
}