Import XML Map inside a Workbook using Aspose.Cells

Aspose.Cells Product Family
9

Hi,

Thanks for using Aspose.Cells.

Please try this sample code.

C#


DataTable dt = new DataTable();

dt = worksheet.Cells.ExportDataTable(0, 0, 1, 1, true);

dt.WriteXml(“out.xml”);

10

Hi Shakeel,

Thanks for your reply.

I try your sample code and it can generate xml file for the cells you specified. Couple things I would like to check with you.

  1. The ExportDataTable feature doesn't use the user defined XML map in the excel file. We still need to find out the worksheet, first row, first column, total row, total column. Unlike Excel Interop, once we know the XmlMap, we know the cell ranges it map to. We don't need to know the worksheet and find out first row, first column, total row, total column. Is there other similar methods in Aspose can do the similar thing?
  2. When the cell has empty value, the xml tag for that field is skipped. If a cell on row 1 column 2 has no value but row 2 column 2 has value, then number of xml elements for row 1 and row 2 will be different. Is there any parameter we can set to not skip the column even if it has no value? We need the exported xml file to be in this way so that we can do the schema validation against XSD latter on. Currently Excel Interop has this functionality. If Aspose already have this feature, then we don't need to do extra data processing.
  3. Do you have any sample code on how to use Aspose.Cells.XmlMap? Can you describe a bit more about this update "40448 - Support XML Maps"? What functionality XmlMap has?

Thanks.

11

Hi,

Thanks for your query and using Aspose.Cells for .NET.

We will look into your questions and after investigation, we will help/advise you as soon as possible.

12

Hi,

1) If you want to get the area of xmlmap which is importing a xml file, we will give this method later.

2) You could try this code in DataTable:

dt.WriteXml(“D:\out.xml”,XmlWriteMode.WriteSchema);

and sorry about this,aspose did not has this feature now.

3) We will give the documents about xmlmaps later.

We now only support importing an xml file to excel like MS Excel.

Sample code:


workbook.ImportXml(“D:\KPD00003349.xml”, “Sheet1”, 0, 0);


13

Thanks Shakeel for your response. It will be useful if these features can be incorported with Aspose Cell product.

One more question related to this is GetRangeByName method. I want to ExportDataTable with the predefined range specified in excel instead of specifying first row, first column info in the code.

Specifically, I'm trying to using this method to get a name range that already defined in Excel template's Name Manager with below line of code:

Range MyRange = workbook.Worksheets.GetRangeByName("TestRange");

It doesn't work for me. MyRange will is null even my predefined range in Excel is correct.

If I create range at run time with a name then GetRangeByName, it can return the range.

Range range = worksheet.Cells.CreateRange("B6", "M10");
range.Name = "TestRange";
Range MyRange = workbook.Worksheets.GetRangeByName("TestRange");

My question is do we have to create range at run time in order to GenRangeByName? Does GetRangeByName method able to get the range that already defined in Excel? If yes, please let me know how.

Thanks.

14

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the Workbook.Worksheets.Names property to retrieve all the named ranges in your workbook having workbook or sheet scope.

Please see the following sample code, source file and its output.

C#



string filePath = @“F:\Shak-Data-RW\Downloads\test.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


foreach (Name range in workbook.Worksheets.Names)

{

Debug.WriteLine(“------------------------------------”);

Debug.WriteLine(“Refer To: " + range.RefersTo);

Debug.WriteLine(“Text: " + range.Text);

Debug.WriteLine(”------------------------------------”);

}


Output:
------------------------------------
Refer To: =Sheet1!$B$1:$C$4
Text: LoginDetails
------------------------------------

15

Hi,

It looks like your above code sample or workbook.Worksheets.GetRangeByName("TestRange") will work if the name range is static. If the name range is dynamic, then it doesn't work for both methods.

Here is what I mean dynamic name range. In excel file, I have Xml Map imported. When I map the data in excel sheet, it creates table range under Formulas -> name manager. When user continue insert data in the mapped table, the name range change dynamically.

Thanks.

16

Hi,

Thanks for your feedback and considering Aspose.Cells.

Could you please create a sample runnable application and screenshots elaborating your issue more?

We will look into it and help you asap.

17

Hi,

Please see attached sample excel file and screen shot for the dynamic name range and static name range. Your sample code or workbook.Worksheets.GetRangeByName("StaticRange") works for the static range. But fail with Dynamic range like workbook.Worksheets.GetRangeByName("BookTableRange"). Let me know if you can get the dynamic range work.

Thanks.

18

Hi,

Thanks for your screenshot and elaborating this issue.

Please use workbook.Worksheets.GetNamedRangesAndTables() method, it will give you desired results.

Please see the following screenshot for your reference.

Screenshot:

19

Hi, thanks a lot!

This method works for both static and dynamic range.

20

Hi,

Thanks for your feedback.

It’s good to know that GetNamedRangesAndTables() method works for your both cases.

Let us know if you face any other issue, we will be glad to assist you further.

21

Hi guys


I read on Stack Overflow that export to XML (map) could be supported in 2nd or 3rd quarter this year.

We are currently evaluating Aspose.Cells and this is a key feature requirement for us. Any update on pending availability of this feature?

Thanks,
Rick
22
rickd-1:
Hi guys

I read on Stack Overflow that export to XML (map) could be supported in 2nd or 3rd quarter this year.

We are currently evaluating Aspose.Cells and this is a key feature requirement for us. Any update on pending availability of this feature?

Thanks,
Rick
Hi,

Thanks for your posting and using Aspose.Cells.

Please elaborate your requirements more with some detail and screenshots. We will look into it and let you know if it is supported or not.
23

Hi Aspose

Can you tell me if you are supporting XML Mapping Import functionality from Excel Developer tab (please see attached print screen for more details) If yes how this can be achieved in C# - example code would be nice

To be more specific below you can find Office Excel Interop example:

Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
application.Workbooks.Open("C:\\test\\exceltemplate-with-xmlmapping.xslx");

Microsoft.Office.Interop.Excel.XmlMap map = application.ActiveWorkbook.XmlMaps[1];
application.ActiveWorkbook.XmlImportXml("<data><value1>1</value1><value2>2</value2></data>", out map);
application.ActiveWorkbook.SaveAs("c:\\test\\excel-with-xmldata.xlsx");
application.Workbooks.Close();

Best regards
Peter

24

Hi Piotr,

Thanks for your elaboration and considering Aspose.Cells.

We understand your requested feature and logged it in our database as a New Feature request in our database to support it. Hopefully, we will be able to implement it. And in case it is already implemented, we will provide you a sample code. Once there is some news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-43992 - Support XML Mapping Import functionality as it is done from Excel Developer tab

25

Shakeel,

I am also most interested in the progress of this feature. Could you please provide a hyperlink to the status of this new issue? Or is this thread the place where I can expect to be updated?

Thanks,
John

26

Hi John,

Thanks for your question and considering Aspose.Cells.

You can check this thread for updates. Or else if you like to create your own thread, we will link that thread to same issue (CELLSNET-43992) and you will get automatic notification in that thread too. We have also added your comment in our database against this issue for product team consideration. Once, there is some news for you, we will update you asap.

27

I’m tracking this thread too

28

Hi,

Thanks for your posting and considering Aspose.Cells.

We have also logged your comment in our database against this issue and informed the product team about your interest in this feature. Once, there is some update for you, we will let you know asap.