Hello,
I am trying to export data from Excel as XML (using the Workbook.ExportXml Method) to later parse it with another library against an XSD file.
The Excel files contain cells formatted as numbers. For these cells, a group separator is used to improve readibility.
The XML parser needs the numbers to not be separated. If a group separator is present, the validation will fail.
As a workaround, the group separator for all cells in the Excel file needs to be disabled like so:
image.png (9.2 KB)
This makes it harder to fill the Excel file, as long numbers are difficult to read.
I have tried playing with the WorkbookSettings.Region Property as well as the WorkbookSettings.NumberGroupSeparator Property. Unfortunately, I can’t seem to find a way to remove the group separator instead of setting it to a specific character.
Is there a way to override all numbered-formatted cells by removing the group separator for them?
Best Regards
@niciuffo,
Could you please share your sample code, template file, generated output and expected output for our analysis.
@ahsaniqbalsidiqui,
Thank you for your reply.
I have created a minimally working example:
Files (contains: Excel file with embedded XML Map, raw XSD file, XML data exported from Excel and XML exported from Aspose.Cells): Example.zip (7.9 KB)
The Excel has a single cell (A1) with format Number and group separator enabled.
When exporting the XML data from Excel (via the menu Developer -> XML -> Export), the number does not have any group separators (wanted behavior):
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Test>
<TestDecimal>123456.78</TestDecimal>
</Test>
When exporting the data as XML with Aspose.Cells, the group separator is there (even when not explicitly setting it with the method wb.Settings.NumberGroupSeparator = ' ';:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<TestDecimal>123 456.78</TestDecimal>
</Test>
This is the code I’m using:
using System;
using System.IO;
using System.Xml;
using Aspose.Cells;
namespace Aspose.Cells.MinimalWorkingExample
{
class Program
{
private const string ResourcesFolder = "Resources";
private const string FileName = "Example.xlsx";
private const string xsdFilename = "xmlMaps.xsd";
private const string xmlFilename = "extractedXML.xml";
static void Main(string[] args)
{
string sourceDir = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ResourcesFolder);
string path = Path.Combine(sourceDir, FileName);
string xmlPath = Path.Combine(sourceDir, xmlFilename);
GetXmlMapFile(path, xmlPath);
Console.WriteLine("XML extracted to: " + xmlPath);
Console.ReadLine();
}
static void GetXmlMapFile(string filePath, string destination)
{
// Load sample Excel file having XML Map
Workbook wb = new Workbook(filePath);
wb.Settings.Region = CountryCode.Switzerland;
wb.Settings.NumberGroupSeparator = ' '; // No matter what I set here, I cannot remove the group separator character
XmlMap map = wb.Worksheets.XmlMaps[0];
// Exports its XML Data to file.
wb.ExportXml(map.Name, destination);
}
}
}
Thank you for your support.
Best Regards
@niciuffo,
You may cope with it by setting the custom formatting for those cells easily. See the following (updated) sample code for your reference. I have tested using it and it works as expected:
e.g.
Sample code:
using System;
using System.IO;
using System.Xml;
using Aspose.Cells;
namespace Aspose.Cells.MinimalWorkingExample
{
class Program
{
private const string ResourcesFolder = "Resources";
private const string FileName = "Example.xlsx";
private const string xsdFilename = "xmlMaps.xsd";
private const string xmlFilename = "extractedXML.xml";
static void Main(string[] args)
{
string sourceDir = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ResourcesFolder);
string path = Path.Combine(sourceDir, FileName);
string xmlPath = Path.Combine(sourceDir, xmlFilename);
GetXmlMapFile(path, xmlPath);
Console.WriteLine("XML extracted to: " + xmlPath);
Console.ReadLine();
}
static void GetXmlMapFile(string filePath, string destination)
{
// Load sample Excel file having XML Map
Workbook wb = new Workbook(filePath);
Worksheet worksheet = wb.Worksheets[0];
IEnumerator iter = worksheet.Cells.GetEnumerator();
while (iter.MoveNext())
{
Cell cell = iter.Current as Aspose.Cells.Cell;
if (cell.IsNumericValue)
{
Style style = cell.GetStyle();
style.Custom = "0.00";
cell.SetStyle(style);
}
}
XmlMap map = wb.Worksheets.XmlMaps[0];
// Exports its XML Data to file.
wb.ExportXml(map.Name, destination);
}
}
}
Hope, this helps a bit.
Hello @Amjad_Sahi,
Thank you for your code. I could work around the issue by setting a custom style based on the cells’ types.
There is no way to automatically obtain an XML export that is exactly the same as when exporting from Excel, right?
Best Regards
@niciuffo,
The formatting is changed in accordance with the locale when you open the file into MS Excel. So, it is better to use and set custom formatting before rendering to XML to cope with it.