.net - How to retrieve complex excel file with merged cells and save as xml file using vb.net? -
i have can retrieve excel file , save xml file.
imports microsoft.office.interop.excel imports system.xml imports system.io module module1 sub main() try dim excel application = new application dim filename string = "person" dim file_extension string dim path string = "c:\users\" dim w workbook try file_extension = "xlsx" w = excel.workbooks.open(path & filename + "." & file_extension) catch ex exception file_extension = "xls" w = excel.workbooks.open(path & filename + "." & file_extension) end try integer = 1 w.sheets.count dim sheet worksheet = w.sheets(i) dim r range = sheet.usedrange dim array(,) object = r.value(xlrangevaluedatatype.xlrangevaluedefault) if array isnot nothing dim bound0 integer = array.getupperbound(0) dim bound1 integer = array.getupperbound(1) dim settings xmlwritersettings = new xmlwritersettings() settings.indent = true using writer xmlwriter = xmlwriter.create(filename + ".xml", settings) writer.writestartdocument() writer.writestartelement(filename) j integer = 2 bound0 writer.writestartelement(sheet.name) x integer = 1 bound1 writer.writeelementstring(array(1, x), array(j, x)) next writer.writeendelement() next writer.writeendelement() writer.writeenddocument() end using end if next w.close() catch ex exception console.writeline("ms excel file invalid.") console.writeline(ex.message) console.readkey() end try end sub end module when have this, example, excel file:
filename: person.xlsx sheet name: personfile
name age gender john 5 m jane 4 f then xml file return way.
<person> <personfile> <name>john</name> <age>5</age> <gender>m</gender> </personfile> <personfile> <name>jane</name> <age>4</age> <gender>f</gender> </personfile> </person> which saved person.xml
now question is... if excel file has merged cells? how solve error? when excel file has merged cells, returns
error: index , length must refer location within string parameter name: length here's sample excel file supposed retrieve. 
p.s. there combo boxes too.
this works on test sheet made couple of different merged cell situations:
private sub main try dim excel application = new application dim filename string = "person" dim file_extension string dim path string = "c:\users\" dim w workbook try file_extension = "xlsx" w = excel.workbooks.open(path & filename + "." & file_extension) catch ex exception file_extension = "xls" w = excel.workbooks.open(path & filename + "." & file_extension) end try integer = 1 w.sheets.count dim sheet object = w.sheets(i) dim r object = sheet.usedrange 'changes original code begin here dim bound0 integer = r.rows.count dim bound1 integer = r.columns.count dim array(bound0, bound1) object integer = 1 bound0 b integer = 1 bound1 try array(a, b) = r.cells(a, b).value catch array(a, b) = nothing end try next next if array isnot nothing 'i left in, though can't imagine how needed dim settings xmlwritersettings = new xmlwritersettings() settings.indent = true using writer xmlwriter = xmlwriter.create(filename + ".xml", settings) writer.writestartdocument() writer.writestartelement(filename) j integer = 2 bound0 writer.writestartelement(sheet.name) x integer = 1 bound1 if array(j, x) isnot nothing dim h integer = x until array(1, h) isnot nothing h -= 1 loop writer.writeelementstring(array(1, h), array(j, x)) 'no more changes code after point end if next writer.writeendelement() next writer.writeendelement() writer.writeenddocument() end using end if next w.close() catch ex exception console.writeline("ms excel file invalid.") console.writeline(ex.message) console.readkey() end try end sub
Comments
Post a Comment