Import XML Map inside a Workbook using Aspose.Cells

Aspose.Cells Product Family
1

Dear Madam od Sir,

Does Aspose.Cells support XML maps ?

I have an Excel file with attached xml schema and cells mapped to the schema.
Is it possible to import/export xml data in such excel using Aspose ?
I can do it in Excel from Developer tab using Import/Export buttons, but I want to do it programmatically, without starting Excel.

Best regards,

Darko

2

Hi,

I am afraid, the feature is not available at the moment, we will soon look into it.

I have logged your feature request into our issue tracking system with an issue id: CELLSNET-17192.

Thank you.

3

Is this feature available at this time? We are considering using Aspose.Cells, but importing from XML is a key feature. We are currently serializing C# structures to XML and then importing them with Excel COM Interop, but we would like to use a solution that doesn't require running Excel on the server.

4

Hi,

Thanks for your interest in Aspose.Cells.

Well, I am afraid, this feature is not available yet. We will let you know once it is available.

5

I am another vote for this functionality. I just ran into it as we are converting all of our MS Office code to work with Aspose components instead.

6

Hi,

Thanks for your showing interest in Aspose.Cells.

We have added another request for this feature

7

The issues you have found earlier (filed as CELLSNET-40448) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
8

Hi,

I have downloaded the trial version of Aspose.Cells for .NET v7.3.0 to check out this feature "40448 - Support XML Maps".

It does able to retrieve XmlMap object from the excel sheet, but not much info available in this object. If I want to export excel data to xml, does Aspose XML Maps has such functionality? So far I don't see we can do that. Please advise if there is such feature available in Aspose product.

As one of the user mentioned before, we can do it in Excel from Developer tab using Import/Export button. Currently in our project, we have used Excel Interop XmlMap to programmatically export excel data to xml based on user define xml map. However, Excel Interop is not a server side solution and it requires Excel client to install on server. Plus, Microsoft not recommend office automation on server side. That's why I'm looking for an alternate better solution. Please respond if Aspose product has such functionality.

Thanks for your help!

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.