Reading XML in VB.net

1. Might it not be better to move this to a RDBMS like Microsoft SQL Server? Architecturally, there's nothing wrong in using Xml as a delivery mechanisam as well as a data-describing protocol.
2. Since you are interested in say the last 3 rows, it would be advisable to move all unused rows to another file.

I would strongly recommend against using Xml as your long-term storage platform unless you have purchased Tamino Server (Xml Database by Software AG).  Architecturally, what you have mentioned is still not enough and I am not convinced that you fully appreciate what you are asking for and the consequences.

my preference was for a RDBMS so maybe that is the better way to go. a guy I know suggested XML for storage but I'd never used it before - perhaps he just got caught up in the whole pro-XML thing...

what more do you need to know architecturally?

1. Is this a WinForm or WebForm application?
2. How does the client send the data to the server?
3. What kind of logic is used to show archived data?

Use xml for what it is best for:
1. Delivery mechanism across heterogeneous environments.
2. Data describing across heterogeneous environments.


Now, there might not be any need for the client to post Xml in some situations. You just plug straight into ADO.NET.

1. must admit I don't know. I'm a student on placement and have been assigned to converting excel spreadsheets into webpages. the starting point is using exceleverywhere to convert the spreadsheet from Excel to asp.net. exceleverywhere has limits in that some formulae aren't supported so these need to be coded in vb, and it cannot read values from a database (it just displays a form where the user types in there value and clicks submit to update results)
2. the idea was that the client populated an xml data file with their measurements which we read using vb, carry out the calculations Excel can't do, and leave the other calculations to the source excel everywhere creates. previously the client populated our excel spreadsheet, we did the calculations (we don't show the client the business logic), and they read the calculated values out of excel to display on their own UI. the client wants to get rid of excel and make everything web-enabled.
3. the archived data will be used to plot graphs comparing measurements over a period of e.g. week, month, year...for them to compare how equipment is performing over a period of time

it seems after what you've said that we'll use RDBMS instead of XML (the idea with XML was that we wouldn't have to code the system for different client according to their database systems etc. and just tell each client to populate an xml file with the measurement fields needed)

1a. It seems that you have two distinct needs. The first is the need to convert excel spreadsheets to ASP.NET syntax. This need might be best met by a WinForm or WebForm application.
1b. The second need where the ASP.NET page is to be display.
1c. You would by now realise the difference between the actual data and the UI layer which is implemented in the ASP.NET page. So the question here is how are you actually converting Excel spreadsheets to ASP.NET pages?? If the data is inside the page, surely, you are as good as writing static HTML pages.
1d. What you need is the ability to port data from Excel to an RDBMS Source from which you can query in a more optimised manner.
1e. You can then leverage off Dundas Charts (commercial product) or use the free Reporting Services for Microsoft SQL Server (if you chose MS SQL Server). The report definition files incidentally are in XML and it is plugged into the VS.NET environment, so you can display charts from data from SQL Server.
1f. Allows you to move away from Excel Spreadsheet.

Additionally,
You can have the clients load the OWC (Office Web Components 2000 and above) and provide an Xml source from the web to this ActiveX Object. The OWC Excel spreadsheet is a subset of the spreadsheet available in Excel itself. In this case, you marry the need to move away from Excel and the need of the clients to have the "old" Excel look. Having said that, it will not be an overnight coding effort to pull this off. It will take more than that but it can be a satisfactory situation where the data is in MSSQL and the display is via Excel OWC.

Reference
=======
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2kta/html/ofintrowbcom.asp

The OWC applies to version 2000 and above.

here's an example of what exceleverywhere produces for a simple spreadsheet. my initial idea was to work from this, populating the I/O from a RDBMS and add the extra functions into the vb.net file to populate the other fields (LINEST is a required feature that ExcelEverywhere doesn't support).  there is a lot of stuff happening in the measurements spreadsheets.   I am more and more feeling that Exceleverywhere is not going to do the job.

asp.net file:  
-------------

<%@ Page language="JScript" EnableViewState="false"  Inherits="ExpertExchange.ExcelEverywhere" Src="ExpertExchange.vb" CodeBehind="ExpertExchange.vb"%>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>
<title>ExpertExchange.xls</title><meta name="GENERATOR" content="ExcelEverywhere for ASP and ASP.NET version 3.3.4 (build 4)"></meta>
<!-- Parts of this page Copyright (C) 2002-2004 Framtidsforum I&M AB, Sweden --><script runat="server">
// Run-time Library for server
//
/*
  Some important restrictions from the EULA regarding the run-time library

* Customers must keep the copyright notice
  'Parts of this page Copyright (C) 2002-2004 Framtidsforum I&M AB, Sweden'
  on all resulting source files.
* You have a royalty-free right to reproduce and distribute the
  run-time library as an integral part of the source files partly
  constructed using the Software. You are not permitted to expose,
  either directly or indirectly, any API that allows programmatic
  access to the run-time libraries.
* Your source files or software product(s) are targeted at end-users,
  and are not a development tool. */


function myIsNaN(x){return(isNaN(x)||(typeof x=='number'&&!isFinite(x)));};function sum2(arr,rt,rb){var sum=0;for(var ii=rt;ii<=rb;ii++){sum+=arr[ii]};return sum};function sum3(arr,rt,ct,rb,cb){var sum=0;for(var ii=rt;ii<=rb;ii++){for(var jj=ct;jj<=cb;jj++){sum+=arr[ii][jj]}};return sum};function sumgeneral(cnt,vsum,vcnt,x){var sum=vsum;for(var ii=0;ii<x.length;ii++){sum+=sum3(x[ii][0],x[ii][1],x[ii][2],x[ii][3],x[ii][4]);};return sum;};function eeparseFloat(str){str=String(str).replace(eedecreg,".");var res=parseFloat(str);if(isNaN(res)){return 0;}else{return res;}};function eedisplayFloat(x){if(myIsNaN(x)){return Number.NaN;}else{return String(x).replace(/\./g,eedec);}};

// initialization
var x = new Object;
var tmp;

function setReset() : void {
  x.p1B1 = 10;
x.p1B2 = 9;
x.p1B3 = 8;
;
}
// locale defs for server
var eeisus=0;var eetrue="TRUE";var eefalse="FALSE";var eedec=".";var eeth=",";var eedecreg=new RegExp("[.]","g");var eethreg=new RegExp(",","g");
// calc start

var arr1xB1B3=new Array(3);var eecm1=new Array(new Array(arr1xB1B3,0,0,2,0));function calc(data){arr1xB1B3[0][0]=data.p1B1;arr1xB1B3[1][0]=data.p1B2;arr1xB1B3[2][0]=data.p1B3;var c1B4=(sumgeneral(3,0,0,eecm1));data.p1B4=c1B4;};

function initArrays() : void {
for (var ii=0; ii<3; ii++) { arr1xB1B3[ii] = new Array(1) ; for (var jj=0; jj<1; jj++) { arr1xB1B3[ii][jj]=0 } };;
}

function fromForm() : void {

      tmp=p1B1.Value;x.p1B1=eeparseFloat(tmp);
      tmp=p1B2.Value;x.p1B2=eeparseFloat(tmp);
      tmp=p1B3.Value;x.p1B3=eeparseFloat(tmp);
}

function toForm() : void {
 
  p1B4.Text=eedisplayFloat(x.p1B4);
  p1B3.Value=eedisplayFloat(x.p1B3);
  p1B2.Value=eedisplayFloat(x.p1B2);
  p1B1.Value=eedisplayFloat(x.p1B1);
}

function docalc() : void {
  calc(x);
}


function getObject() { return x; }

</script>
<!-- ExcelEverywhere Header start -->

<link rel="stylesheet" type="text/css" href="./styles.css"> </link>
<link rel="stylesheet" type="text/css" href="./styles_screen.css" media="screen"> </link>
<link rel="stylesheet" type="text/css" href="./styles_print.css" media="print"> </link>
<script language="javascript">
<!-- locale defs for client -->
var eeisus=0;var eetrue="TRUE";var eefalse="FALSE";var eedec=".";var eeth=",";var eedecreg=new RegExp("[.]","g");var eethreg=new RegExp(",","g");
<!-- library for client -->

function myIsNaN(x){return(isNaN(x)||(typeof x=='number'&&!isFinite(x)));};function eeparseFloat(str){str=String(str).replace(eedecreg,".");var res=parseFloat(str);if(isNaN(res)){return 0;}else{return res;}};function eedisplayFloat(x){if(myIsNaN(x)){return Number.NaN;}else{return String(x).replace(/\./g,eedec);}};
</script>
<!-- ExcelEverywhere Header end -->
</head><body onload='<%= (panel_to_show == "panel1")?"document.formc.p1B1.focus();":"" %>'>

<!-- ExcelEverywhere Body start -->

   <form id="formc" name="formc" method="post" action="" runat="server">

   <input name="xl_sheet_no" id="xl_sheet_no" type="hidden" value="<%= panel_to_show %>">
   <input name="xl_postback" id="xl_postback" type="hidden" value="1">
     
     



     

<div class="eebuttonbar_top">

<input class="eebuttons" type="submit" value="Update" name="xl_update_top">
<input class="eebuttons" type="submit" value="Reset" name="xl_reset_top">
<input class="eebuttons" style="margin-left:30px" type="submit" value="Submit" name="xl_submit_top"  >
<input class="eebuttons" type="button" value="Print" name="xl_print_top" onclick="window.print();">




</div>


<div id="panel1" style='display:<%= (panel_to_show == "panel1")?"block":"none" %>'>
     <table  style='border-collapse:collapse;width:96pt'  border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF" >
       <col width="48.00" />
       <col width="48.00" />
            <tr style='height:13pt'>
                  <td    class='ee100'    >
                            <asp:Label id="p1A1" runat="server">Value 1</asp:Label>
                  </td>
                  <td    class='ee103'    >
                    <input  name="p1B1" id="p1B1" type="text" runat="server"
                     onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="1"
                     style=' width:100% '
         class='ee105' >
                  </td>
            </tr>
            <tr style='height:13pt'>
                  <td    class='ee100'    >
                            <asp:Label id="p1A2" runat="server">Value 2</asp:Label>
                  </td>
                  <td    class='ee103'    >
                    <input  name="p1B2" id="p1B2" type="text" runat="server"
                     onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="2"
                     style=' width:100% '
         class='ee105' >
                  </td>
            </tr>
            <tr style='height:13pt'>
                  <td    class='ee100'    >
                            <asp:Label id="p1A3" runat="server">Value 3</asp:Label>
                  </td>
                  <td    class='ee103'    >
                    <input  name="p1B3" id="p1B3" type="text" runat="server"
                     onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="3"
                     style=' width:100% '
         class='ee105' >
                  </td>
            </tr>
            <tr style='height:13pt'>
                  <td    class='ee100'    >
                            <asp:Label id="p1A4" runat="server">Total</asp:Label>
                  </td>
                  <td    class='ee106'    >
                            <asp:Label id="p1B4" runat="server" />
                  </td>
            </tr>
      </table>
      <p></p>
         </div>
 
<div class="eebuttonbar_bottom">

<input class="eebuttons" type="submit" value="Update" name="xl_update_bottom">
<input class="eebuttons" type="submit" value="Reset" name="xl_reset_bottom">
<input class="eebuttons" style="margin-left:30px" type="submit" value="Submit" name="xl_submit_bottom"  >
<input class="eebuttons" type="button" value="Print" name="xl_print_bottom" onclick="window.print();">



<span style="margin-left:60px;"> <a href='http://www.exceleverywhere.com' target='_blank' tabindex='-1'>
<img style='vertical-align: bottom;' border='0' src='powered_by_exceleverywhere.png' width='73' height='26' alt='Powered By ExcelEverywhere'></a>
</span>

</div>

 
</form>
<script language="javascript">
function reset_onclick(x){document.formc.reset();postcode();};function postcode(){};function eequerystring(){var querystring=document.location.search;if(querystring.length>0){variables=(querystring.substring(1)).split("&");var variable;var key;var value;for(var ii=0;ii<variables.length;ii++){variable=variables[ii].split("=");key=unescape(variable[0]);value=unescape(variable[1]);if(document.formc[key]!=null){document.formc[key].value=value;}}}}function initial_update(){postcode('');eequerystring();}
</script>

<!-- ExcelEverywhere Body end -->


<noscript>The browser does not support JavaScript. The calculations created using  <a href='http://www.exceleverywhere.com' target='_blank'>ExcelEverywhere</a>  will not work. Please access the web page using another browser.<p></p></noscript>
</body>
</html>




VB.net file
------------

' This file will not be overwritten!
'
' This is the recommended location for backend integrations

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Namespace  ExpertExchange
    Public MustInherit class ExcelEverywhere : Inherits System.Web.UI.Page

        public Overridable Sub initArrays()
        end sub
        public Overridable Sub setReset()
        end sub
        public Overridable Sub fromForm()
        end sub
        public Overridable Sub docalc()
        end sub
        public Overridable Sub toForm()
        end sub

        ' You can either access the values as presented to the user
        ' by accessing then ASPX-fields or you can get the unformatted
        ' values by using getObject.
        ' see http:'www.exceleverywhere.com/faq/faq-asp.net.html on
        ' how to access the values in the Object
        public Overridable function getObject() as Object
        end function
       
        'All the ASPX-fields as of when this file was first generated.
     'The list is not updated, since this file is not overwritten.
     'In order to get the new list. Delete this file.

        'protected p1B1 as HtmlInputText
        'protected p1B2 as HtmlInputText
        'protected p1B3 as HtmlInputText
        'protected p1B4 as Label

        Protected Sub server_recalc(ByVal objSender As System.Object, ByVal objArgs As System.EventArgs )
           'no code here since Page_load handles the recalculation
        End Sub

        Protected panel_to_show as string
       
        Public Sub Page_Load(ByVal objSender As System.Object, ByVal objArgs As System.EventArgs )
            initArrays
            setReset

            panel_to_show = "panel1"

           
            ' TIP: Add code here to set attributes of getObject to override default values
            if IsPostBack then
              panel_to_show = Request.Params("xl_sheet_no")
           if Request.Params("xl_update_top") <> "" or Request.Params("xl_update_bottom") <> "" or Request.Params("xl_submit_top") <> "" or Request.Params("xl_submit_bottom") <> "" then
             'it wasn't a reset
                   fromForm
               
                   'TIP: Add code here to read attributes from getObject if you want to save the
                   'values entered by the user
                 end if
            end if
            docalc

         if Request.Params("xl_submit_top") <> "" or Request.Params("xl_submit_bottom") <> "" then
            'submit was pressed: save and redirect to confirmation page

               'TIP: Add code here to read attributes from x if you want to save the
               'values entered by the user and the calculated values.
            end if

            getObject
            toForm

        End Sub
    End Class
End Namespace


--------------



clients say they don't want to install excel. excel is good in the sense that non-programmers (measurement engineers) can create the spreadsheets.
is it possible to use an application to read/write from RDBMS to an excel spreadsheets without the client needing to have excel on their systems?  the clients populate a database just now with their measurements. what I need is to write these measurements to Excel, let excel do its calculations, and get the solutions onto a webpage.

---------------

unfortunately the OWC is a no-no, or I'd have gone with this. Client's don't want activex object used. the client is only interested in viewing a few of the simplers sheets in our spreadsheet (the ones that display the end results of all the engineering calculations)

I've looked at various charting products - and Dundas is our choice!  

thanks for help thusfar

thanks for your advice. I'll be reviewing things completely!

most welcome =)