none
excel Named Range to XML with Column names RRS feed

  • Question

  • Hello

    I want to export a named rage from excel to SQL i tried to do it with XML Using this code

    Dim

     

    valores = worksheet.Range("clientes").Cells.Value(excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet)

    The problem is the the named range dosn´t include the column names and there are no in the xml schema and i cant insert it in SQL using the Open xml

    any idea to solve this?

    thanks

    • Moved by Bessie Zhao Friday, April 9, 2010 8:40 AM (From:Visual Studio Tools for Office)
    Wednesday, April 7, 2010 8:23 PM

Answers

  • Hi saul_l,

    Thanks for your description.

    Mentioned in my last post, you could get the cell values and the column names. After that, you could use the classes under System.Xml namespace to construct the XML you need (you may refer to forum XML and the .NET Framework) with the values and column names.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    • Marked as answer by saul Laniado Sunday, April 25, 2010 1:55 PM
    Monday, April 19, 2010 9:13 AM

All replies

  • Hello Saul,

    For better support, I will move this thread to OpenXML SDK forum.

    Thanks,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, April 9, 2010 8:39 AM
  • Hi saul_l,

    Thanks for your question.

    One thing I need to be clear about your requirement is that you need both the cell values and the column name of the named range? If so, you could use Open XML SDK to achieve it.

    To get data from a cell with defined name, you could refer to this link. You have to do extra work to get the column name, which is usually stored in "$X1" (where X stands for the column index, it is possible that you don't store the column name in the first row, in which you should replace 1 with the exact row index), then you could get the cell value in similar way as mentioned in the blog post.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Friday, April 9, 2010 9:41 AM
  • I need a xml like this, i need to take it from excel.

    this one i create it from SQL I need XML that i can insert into SQL using OpenXMl Command.

    Thanks

    select

     

    *fromclientesforxmlauto,root('Datos')

    <

     

    Datos>

    <

     

    clientesNoCliente="1"Nombre="Saul"ApellidoP="Laniado"ApellidoM="Roman"Direccion=""Telefono1=""Telefono2=""Celular=""Correo="nuevo"Cobrador=""Titulo="Sr."Zona=""Comentario="" />

    <

     

    clientesNoCliente="37"Nombre="david"ApellidoP="cohen" />

    <

     

    clientesNoCliente="38"Nombre="jorge"ApellidoP="gonzales"ApellidoM="perez" />

    </

     

    Datos>

     

    Friday, April 16, 2010 7:28 PM
  • Hi saul_l,

    Thanks for your description.

    Mentioned in my last post, you could get the cell values and the column names. After that, you could use the classes under System.Xml namespace to construct the XML you need (you may refer to forum XML and the .NET Framework) with the values and column names.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    • Marked as answer by saul Laniado Sunday, April 25, 2010 1:55 PM
    Monday, April 19, 2010 9:13 AM