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