Columns and Values (Urgent)

's Computer Specifications

Very urgent and I am very close but need a little help to get me over the
edge........

I need to write these columns to a html file with each row containing these
columns (seperated by breaks). Currently this code write all columns to a
single row which works but is not what the boss wants. If anyone has
suggestions how you would do a report like this I very much would like to
hear them as well. vb.net windows app.

sw.WriteLine("<html>")
sw.WriteLine("<head>")
sw.WriteLine("</head>")
sw.WriteLine("<body>")
Dim i, r, c As Integer
For i = 0 To dshistory1.Tables.Count - 1
sw.WriteLine("<center>")
sw.WriteLine("<table border=1 width='100%'
style='border-collapse: collapse' cellspacing='3'
bordercolorlight='#000000'>")
sw.WriteLine("<tr>")
sw.WriteLine("<b>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
sw.WriteLine("</td>")
sw.WriteLine("</tr>")
For r = 0 To dshistory1.Tables(i).Rows.Count - 1

sw.WriteLine("<tr>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next

sw.WriteLine("</table>")
sw.WriteLine("</center>")
'sw.WriteLine("</hr>")
Next
sw.WriteLine("</body>")
sw.WriteLine("</html>")
sw.Close()

What I am trying to do is this(and make it all fit in a landscape print
situation

Row 1 is the title ("Sales Report" - not in loop)

(Row 1)
CUSTNO CUSTNAME SLSMNO
(Row 2 is the values in the columns above)

(Row 3)
LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES JULSALES
JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
(row 4 is the values in the columns above)

etc on and so forth with the profit, orders, arbal, and GM columns.

These are the names of the columns in the table.

<xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
<xs:element name="CUSTNAME" type="xs:string" minOccurs="0" />
<xs:element name="SLSMN" type="xs:int" minOccurs="0" />
<xs:element name="LYTMSALES" type="xs:double" minOccurs="0" />
<xs:element name="LYTMPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="LYTMGM" type="xs:double" minOccurs="0" />
<xs:element name="LYTMORDERS" type="xs:int" minOccurs="0" />
<xs:element name="LYTMARBAL" type="xs:double" minOccurs="0" />
<xs:element name="LYTDSALES" type="xs:double" minOccurs="0" />
<xs:element name="LYTDPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="LYTDGM" type="xs:double" minOccurs="0" />
<xs:element name="LYTDORDERS" type="xs:int" minOccurs="0" />
<xs:element name="LYTDARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JANSALES" type="xs:double" minOccurs="0" />
<xs:element name="JANGM" type="xs:double" minOccurs="0" />
<xs:element name="JANPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="JANORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JANARBAL" type="xs:double" minOccurs="0" />
<xs:element name="FEBSALES" type="xs:double" minOccurs="0" />
<xs:element name="FEBPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="FEBGM" type="xs:double" minOccurs="0" />
<xs:element name="FEBORDERS" type="xs:int" minOccurs="0" />
<xs:element name="FEBARBAL" type="xs:double" minOccurs="0" />
<xs:element name="MARSALES" type="xs:double" minOccurs="0" />
<xs:element name="MARPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="MARGM" type="xs:double" minOccurs="0" />
<xs:element name="MARORDERS" type="xs:int" minOccurs="0" />
<xs:element name="MARARBAL" type="xs:double" minOccurs="0" />
<xs:element name="APRSALES" type="xs:double" minOccurs="0" />
<xs:element name="APRPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="APRGM" type="xs:double" minOccurs="0" />
<xs:element name="APRORDERS" type="xs:int" minOccurs="0" />
<xs:element name="APRARBAL" type="xs:double" minOccurs="0" />
<xs:element name="MAYSALES" type="xs:double" minOccurs="0" />
<xs:element name="MAYPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="MAYGM" type="xs:double" minOccurs="0" />
<xs:element name="MAYORDERS" type="xs:int" minOccurs="0" />
<xs:element name="MAYARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JUNESALES" type="xs:double" minOccurs="0" />
<xs:element name="JUNEPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="JUNEGM" type="xs:double" minOccurs="0" />
<xs:element name="JUNEORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JUNEARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JULYSALES" type="xs:double" minOccurs="0" />
<xs:element name="JULYPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="JULYGM" type="xs:double" minOccurs="0" />
<xs:element name="JULYORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JULYARBAL" type="xs:double" minOccurs="0" />
<xs:element name="AUGSALES" type="xs:double" minOccurs="0" />
<xs:element name="AUGPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="AUGGM" type="xs:double" minOccurs="0" />
<xs:element name="AUGORDERS" type="xs:int" minOccurs="0" />
<xs:element name="AUGARBAL" type="xs:double" minOccurs="0" />
<xs:element name="SEPTSALES" type="xs:double" minOccurs="0" />
<xs:element name="SEPTPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="SEPTGM" type="xs:double" minOccurs="0" />
<xs:element name="SEPTORDERS" type="xs:int" minOccurs="0" />
<xs:element name="SEPTARBAL" type="xs:double" minOccurs="0" />
<xs:element name="OCTSALES" type="xs:double" minOccurs="0" />
<xs:element name="OCTPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="OCTGM" type="xs:double" minOccurs="0" />
<xs:element name="OCTORDERS" type="xs:int" minOccurs="0" />
<xs:element name="OCTARBAL" type="xs:double" minOccurs="0" />
<xs:element name="NOVSALES" type="xs:double" minOccurs="0" />
<xs:element name="NOVPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="NOVGM" type="xs:double" minOccurs="0" />
<xs:element name="NOVORDERS" type="xs:int" minOccurs="0" />
<xs:element name="NOVARBAL" type="xs:double" minOccurs="0" />
<xs:element name="DECSALES" type="xs:double" minOccurs="0" />
<xs:element name="DECPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="DECGM" type="xs:double" minOccurs="0" />
<xs:element name="DECORDERS" type="xs:int" minOccurs="0" />
<xs:element name="DECARBAL" type="xs:double" minOccurs="0" />

's Computer Specifications

Scorpion,
Although you send a lot of information, I have to asume a lot.
I asume you make a file on a media like a disk, that you read with a
browser, a text editor or something like that.

In the code I see one little error, but as far as I know does that mostly
affects nothing (the bold tags are to not fine placed)
sw.WriteLine("<tr>")
sw.WriteLine("<b>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
-----> sw.WriteLine("</td>") ' is not opened
sw.WriteLine("</tr>")

Maybe if you give some more information I or someone else can help you.
Cor

's Computer Specifications

Scorpion,
I write is not opened, but I mean I think it has not to be closed
Cor

's Computer Specifications

Scorpion,
I don't see any real problems. I can run your code 'as is' and the output
appears mostly as you describe it. When the HTML is viewed in design mode or
the browser, in source view there is only one line, but 'who cares' is html
source. (if you care, then change the Write to WriteLine...)

Maybe I am reading your description wrong.

Do you want:

c1 c2 c3 c4
v1 v2 v3 v4

Or do you want:

c1 v1
c2 v2
c3 v4
c4 v4

Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.

I did notice a couple of potential problems:

Is sw a StreamWriter? Why aren't you using a System.Web.UI.HtmlTextWriter
instead? Although this is a windows app, you can reference the System.Web
assembly and still use the HtmlTextWriter. This way you are better assured
of having well formed HTML. Granted a StreamWriter is easier...

When you write the column names, you have an extra /td on the end of the
row.

> sw.WriteLine("</b>")
> sw.WriteLine("</td>") <- ???
> sw.WriteLine("</tr>")

Rather than then use <b> around the headers, consider using <th> for each
header cell.

> sw.WriteLine("<tr>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<th>{0}</th>",
> dshistory1.Tables(i).Columns(c).ColumnName)
> Next
> sw.WriteLine("</tr>")

This also allows you to control how it is displayed via a CSS file.

Rather than use For To, consider a For Each loop, IMO it cleans up the code,
even more so in VS.NET 2003

' VS.NET 2003 syntax
' (some stuff omitted for clarity)
For Each table As DataTable In dshistory.Tables
For Each column As DataColumn In table.Columns
sw.Write("<th>{0}</th>", column.ColumnName)
Next
For Each row As DataRow In table.Rows
For Each column As DataColumn In table.Columns
sw.Write("<td>{0}</td>", row.Item(column))
Next
Next
Next

For VS.NET 2002 you will need to use some dims:
Dim table As DataTable
Dim column As DataColumn
Dim row As DataRow

Which has the added benefit of causing the program to run quicker, as you
are not dereferencing 3 or 4 objects to get at a value, you are accessing
the object directly. And I've seen reports that indexing a DataRow by a
DataColumn is the faster way to index.

Hope this helps
Jay

"scorpion53061" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Very urgent and I am very close but need a little help to get me over the
> edge........
>
> I need to write these columns to a html file with each row containing
these
> columns (seperated by breaks). Currently this code write all columns to a
> single row which works but is not what the boss wants. If anyone has
> suggestions how you would do a report like this I very much would like to
> hear them as well. vb.net windows app.
>
> sw.WriteLine("<html>")
> sw.WriteLine("<head>")
> sw.WriteLine("</head>")
> sw.WriteLine("<body>")
> Dim i, r, c As Integer
> For i = 0 To dshistory1.Tables.Count - 1
> sw.WriteLine("<center>")
> sw.WriteLine("<table border=1 width='100%'
> style='border-collapse: collapse' cellspacing='3'
> bordercolorlight='#000000'>")
> sw.WriteLine("<tr>")
> sw.WriteLine("<b>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<td>{0}</td>",
> dshistory1.Tables(i).Columns(c).ColumnName)
> Next
> sw.WriteLine("</b>")
> sw.WriteLine("</td>")
> sw.WriteLine("</tr>")
> For r = 0 To dshistory1.Tables(i).Rows.Count - 1
>
> sw.WriteLine("<tr>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<td>{0}</td>",
> dshistory1.Tables(i).Rows(r).Item(c))
> Next
> sw.WriteLine("</tr>")
> Next
>
> sw.WriteLine("</table>")
> sw.WriteLine("</center>")
> 'sw.WriteLine("</hr>")
> Next
> sw.WriteLine("</body>")
> sw.WriteLine("</html>")
> sw.Close()
>
> What I am trying to do is this(and make it all fit in a landscape print
> situation

>
> Row 1 is the title ("Sales Report" - not in loop)
>
> (Row 1)
> CUSTNO CUSTNAME SLSMNO
> (Row 2 is the values in the columns above)
>
> (Row 3)
> LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES JULSALES
> JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
> (row 4 is the values in the columns above)
>
> etc on and so forth with the profit, orders, arbal, and GM columns.
>
> These are the names of the columns in the table.
>
> <xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
> <xs:element name="CUSTNAME" type="xs:string" minOccurs="0"
/>
> <xs:element name="SLSMN" type="xs:int" minOccurs="0" />
> <xs:element name="LYTMSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="LYTMPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="LYTMGM" type="xs:double" minOccurs="0" />
> <xs:element name="LYTMORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="LYTMARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="LYTDSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="LYTDPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="LYTDGM" type="xs:double" minOccurs="0" />
> <xs:element name="LYTDORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="LYTDARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="JANSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="JANGM" type="xs:double" minOccurs="0" />
> <xs:element name="JANPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="JANORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JANARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="FEBSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="FEBPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="FEBGM" type="xs:double" minOccurs="0" />
> <xs:element name="FEBORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="FEBARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="MARSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="MARPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="MARGM" type="xs:double" minOccurs="0" />
> <xs:element name="MARORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="MARARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="APRSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="APRPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="APRGM" type="xs:double" minOccurs="0" />
> <xs:element name="APRORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="APRARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="MAYSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="MAYPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="MAYGM" type="xs:double" minOccurs="0" />
> <xs:element name="MAYORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="MAYARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="JUNESALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="JUNEPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="JUNEGM" type="xs:double" minOccurs="0" />
> <xs:element name="JUNEORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JUNEARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="JULYSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="JULYPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="JULYGM" type="xs:double" minOccurs="0" />
> <xs:element name="JULYORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JULYARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="AUGSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="AUGPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="AUGGM" type="xs:double" minOccurs="0" />
> <xs:element name="AUGORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="AUGARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="SEPTSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="SEPTPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="SEPTGM" type="xs:double" minOccurs="0" />
> <xs:element name="SEPTORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="SEPTARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="OCTSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="OCTPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="OCTGM" type="xs:double" minOccurs="0" />
> <xs:element name="OCTORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="OCTARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="NOVSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="NOVPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="NOVGM" type="xs:double" minOccurs="0" />
> <xs:element name="NOVORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="NOVARBAL" type="xs:double" minOccurs="0"
/>
> <xs:element name="DECSALES" type="xs:double" minOccurs="0"
/>
> <xs:element name="DECPROFIT" type="xs:double" minOccurs="0"
/>
> <xs:element name="DECGM" type="xs:double" minOccurs="0" />
> <xs:element name="DECORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="DECARBAL" type="xs:double" minOccurs="0"
/>
>
>

's Computer Specifications

The way I have it currently written all the values appear straight across as
if in an excel spreadsheet. What I am tring to do is group all the values
that go together (LYTDSALES, JANSALES, FEBSALES etc)

in one line and then the values in the next line then in the next line:
(LYTDPROFIT, JANPROFIT, FEBPROFIT etc)
then the values from those columns.

I hope I am making sense.

"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Scorpion,
> I don't see any real problems. I can run your code 'as is' and the output
> appears mostly as you describe it. When the HTML is viewed in design mode
or
> the browser, in source view there is only one line, but 'who cares' is
html
> source. (if you care, then change the Write to WriteLine...)
>
> Maybe I am reading your description wrong.
>
> Do you want:
>
> c1 c2 c3 c4
> v1 v2 v3 v4
>
> Or do you want:
>
> c1 v1
> c2 v2
> c3 v4
> c4 v4
>
> Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.
>
> I did notice a couple of potential problems:
>
> Is sw a StreamWriter? Why aren't you using a System.Web.UI.HtmlTextWriter
> instead? Although this is a windows app, you can reference the System.Web
> assembly and still use the HtmlTextWriter. This way you are better assured
> of having well formed HTML. Granted a StreamWriter is easier...
>
> When you write the column names, you have an extra /td on the end of the
> row.
>
> > sw.WriteLine("</b>")
> > sw.WriteLine("</td>") <- ???
> > sw.WriteLine("</tr>")
>
> Rather than then use <b> around the headers, consider using <th> for each
> header cell.
>
> > sw.WriteLine("<tr>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<th>{0}</th>",
> > dshistory1.Tables(i).Columns(c).ColumnName)
> > Next
> > sw.WriteLine("</tr>")
>
> This also allows you to control how it is displayed via a CSS file.
>
> Rather than use For To, consider a For Each loop, IMO it cleans up the
code,
> even more so in VS.NET 2003
>
> ' VS.NET 2003 syntax
> ' (some stuff omitted for clarity)
> For Each table As DataTable In dshistory.Tables
> For Each column As DataColumn In table.Columns
> sw.Write("<th>{0}</th>", column.ColumnName)
> Next
> For Each row As DataRow In table.Rows
> For Each column As DataColumn In table.Columns
> sw.Write("<td>{0}</td>", row.Item(column))
> Next
> Next
> Next
>
> For VS.NET 2002 you will need to use some dims:
> Dim table As DataTable
> Dim column As DataColumn
> Dim row As DataRow
>
> Which has the added benefit of causing the program to run quicker, as you
> are not dereferencing 3 or 4 objects to get at a value, you are accessing
> the object directly. And I've seen reports that indexing a DataRow by a
> DataColumn is the faster way to index.
>
> Hope this helps
> Jay
>
> "scorpion53061" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Very urgent and I am very close but need a little help to get me over
the
> > edge........
> >
> > I need to write these columns to a html file with each row containing
> these
> > columns (seperated by breaks). Currently this code write all columns to
a
> > single row which works but is not what the boss wants. If anyone has
> > suggestions how you would do a report like this I very much would like
to
> > hear them as well. vb.net windows app.
> >
> > sw.WriteLine("<html>")
> > sw.WriteLine("<head>")
> > sw.WriteLine("</head>")
> > sw.WriteLine("<body>")
> > Dim i, r, c As Integer
> > For i = 0 To dshistory1.Tables.Count - 1
> > sw.WriteLine("<center>")
> > sw.WriteLine("<table border=1 width='100%'
> > style='border-collapse: collapse' cellspacing='3'
> > bordercolorlight='#000000'>")
> > sw.WriteLine("<tr>")
> > sw.WriteLine("<b>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<td>{0}</td>",
> > dshistory1.Tables(i).Columns(c).ColumnName)
> > Next
> > sw.WriteLine("</b>")
> > sw.WriteLine("</td>")
> > sw.WriteLine("</tr>")
> > For r = 0 To dshistory1.Tables(i).Rows.Count - 1
> >
> > sw.WriteLine("<tr>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<td>{0}</td>",
> > dshistory1.Tables(i).Rows(r).Item(c))
> > Next
> > sw.WriteLine("</tr>")
> > Next
> >
> > sw.WriteLine("</table>")
> > sw.WriteLine("</center>")
> > 'sw.WriteLine("</hr>")
> > Next
> > sw.WriteLine("</body>")
> > sw.WriteLine("</html>")
> > sw.Close()
> >
> > What I am trying to do is this(and make it all fit in a landscape print
> > situation

> >
> > Row 1 is the title ("Sales Report" - not in loop)
> >
> > (Row 1)
> > CUSTNO CUSTNAME SLSMNO
> > (Row 2 is the values in the columns above)
> >
> > (Row 3)
> > LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES
JULSALES
> > JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
> > (row 4 is the values in the columns above)
> >
> > etc on and so forth with the profit, orders, arbal, and GM columns.
> >
> > These are the names of the columns in the table.
> >
> > <xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
> > <xs:element name="CUSTNAME" type="xs:string" minOccurs="0"
> />
> > <xs:element name="SLSMN" type="xs:int" minOccurs="0" />
> > <xs:element name="LYTMSALES" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="LYTMPROFIT" type="xs:double"
minOccurs="0"
> > />
> > <xs:element name="LYTMGM" type="xs:double" minOccurs="0"
/>
> > <xs:element name="LYTMORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="LYTMARBAL" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="LYTDSALES" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="LYTDPROFIT" type="xs:double"
minOccurs="0"
> > />
> > <xs:element name="LYTDGM" type="xs:double" minOccurs="0"
/>
> > <xs:element name="LYTDORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="LYTDARBAL" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="JANSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="JANGM" type="xs:double" minOccurs="0" />
> > <xs:element name="JANPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="JANORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="JANARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="FEBSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="FEBPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="FEBGM" type="xs:double" minOccurs="0" />
> > <xs:element name="FEBORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="FEBARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="MARSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="MARPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="MARGM" type="xs:double" minOccurs="0" />
> > <xs:element name="MARORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="MARARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="APRSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="APRPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="APRGM" type="xs:double" minOccurs="0" />
> > <xs:element name="APRORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="APRARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="MAYSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="MAYPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="MAYGM" type="xs:double" minOccurs="0" />
> > <xs:element name="MAYORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="MAYARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="JUNESALES" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="JUNEPROFIT" type="xs:double"
minOccurs="0"
> > />
> > <xs:element name="JUNEGM" type="xs:double" minOccurs="0"
/>
> > <xs:element name="JUNEORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="JUNEARBAL" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="JULYSALES" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="JULYPROFIT" type="xs:double"
minOccurs="0"
> > />
> > <xs:element name="JULYGM" type="xs:double" minOccurs="0"
/>
> > <xs:element name="JULYORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="JULYARBAL" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="AUGSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="AUGPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="AUGGM" type="xs:double" minOccurs="0" />
> > <xs:element name="AUGORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="AUGARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="SEPTSALES" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="SEPTPROFIT" type="xs:double"
minOccurs="0"
> > />
> > <xs:element name="SEPTGM" type="xs:double" minOccurs="0"
/>
> > <xs:element name="SEPTORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="SEPTARBAL" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="OCTSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="OCTPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="OCTGM" type="xs:double" minOccurs="0" />
> > <xs:element name="OCTORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="OCTARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="NOVSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="NOVPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="NOVGM" type="xs:double" minOccurs="0" />
> > <xs:element name="NOVORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="NOVARBAL" type="xs:double" minOccurs="0"
> />
> > <xs:element name="DECSALES" type="xs:double" minOccurs="0"
> />
> > <xs:element name="DECPROFIT" type="xs:double"
minOccurs="0"
> />
> > <xs:element name="DECGM" type="xs:double" minOccurs="0" />
> > <xs:element name="DECORDERS" type="xs:int" minOccurs="0"
/>
> > <xs:element name="DECARBAL" type="xs:double" minOccurs="0"
> />
> >
> >
>
>

's Computer Specifications

Dear Scorpion,

Your data table is wide. Very wide. Loadsacolumns. But that's no
problem. :-)

Your current solution is to dump every column out in an html table
that's wide. Very, very wide. Now that IS a problem, lol. Your Boss doesn't
want to have to play scrolly-scrolly. <:-( And why should he?

We're going to solve your problem in a series of steps. I'm not going to
give you the final solution immediately - I'm doing this off the top of my
head. You must do it step by step to make sure that you understand it and
that it works. Ok?

Here's what I want you to do:

Take all the code out of your outer loop and put it in a subroutine
which takes a Table as its parameter. Take all the column heading code out
and put that in its own routine. And the same for the values.

Replace dshistory1.Tables(i) with dtTable, the parameter. This is both
for efficiency and for clarity of code. Think about how often you've
repeated dshistory1.Tables(i). Cringe!! Get into the habit of spotting such
repetition and using extra variables instead.

This will give you:

Sub DoReport()
For i = 0 To dshistory1.Tables.Count - 1
Output_Table (dshistory1.Tables(i))
Next
End Sub

Sub Output_Table (DataTable dtTable)
sw.WriteLine("<center>")
: : :
Output_ColumnHeadings (dtTable)
Output_Values (dtTable)
sw.WriteLine("</table>")
: : :
End Sub

Sub Output_ColumnHeadings (DataTable dtTable)
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
: : :
End Sub

Sub Output_Values (DataTable dtTable)
For r = 0 To dtTable.Rows.Count - 1
sw.WriteLine("<tr>")
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next
End Sub

This will give you exactly the same as you already have. But it's
clearer and more efficient. More importantly, it gives us the foundation for
the next step. You can fix any errors that I've made.
Now, go to it!!

Regards,
Fergus.

================================
To the tune of Rawhide:

Scrollin' scrollin' scrollin,
Get that web page scrollin'
Scrollin' scrollin' scrollin,
Re-PORT.

's Computer Specifications

Like this?

Ready for step 2...

'sw publicly declared as streamwriter
Sub DoReport()
For i = 0 To Dshistory1.Tables.Count - 1 'i,r,c publicy declared as
integer
dtTable = Dshistory1.Tables(i) 'publicly delcared dtTable
Output_Table(Dshistory1.Tables(i))
Next
End Sub

Sub Output_Table(ByVal dtTable)
sw.WriteLine("<center>")

Output_ColumnHeadings(dtTable)
Output_Values(dtTable)
sw.WriteLine("</table>")

End Sub

Sub Output_ColumnHeadings(ByVal dtTable)
Dim c As Integer
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
Next
sw.WriteLine("</b>")

End Sub

Sub Output_Values(ByVal dtTable)
Dim r As Integer
For r = 0 To dtTable.Rows.Count - 1
sw.WriteLine("<tr>")
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next
End Sub

"Fergus Cooney" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear Scorpion,
>
> Your data table is wide. Very wide. Loadsacolumns. But that's no
> problem. :-)
>
> Your current solution is to dump every column out in an html table
> that's wide. Very, very wide. Now that IS a problem, lol. Your Boss
doesn't
> want to have to play scrolly-scrolly. <:-( And why should he?
>
> We're going to solve your problem in a series of steps. I'm not going
to
> give you the final solution immediately - I'm doing this off the top of my
> head. You must do it step by step to make sure that you understand it and
> that it works. Ok?
>
> Here's what I want you to do:
>
> Take all the code out of your outer loop and put it in a subroutine
> which takes a Table as its parameter. Take all the column heading code out
> and put that in its own routine. And the same for the values.
>
> Replace dshistory1.Tables(i) with dtTable, the parameter. This is both
> for efficiency and for clarity of code. Think about how often you've
> repeated dshistory1.Tables(i). Cringe!! Get into the habit of spotting
such
> repetition and using extra variables instead.
>
> This will give you:
>
>
> Sub DoReport()
> For i = 0 To dshistory1.Tables.Count - 1
> Output_Table (dshistory1.Tables(i))
> Next
> End Sub
>
> Sub Output_Table (DataTable dtTable)
> sw.WriteLine("<center>")
> : : :
> Output_ColumnHeadings (dtTable)
> Output_Values (dtTable)
> sw.WriteLine("</table>")
> : : :
> End Sub
>
> Sub Output_ColumnHeadings (DataTable dtTable)
> For c = 0 To dtTable.Columns.Count - 1
> sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
> Next
> sw.WriteLine("</b>")
> : : :
> End Sub
>
> Sub Output_Values (DataTable dtTable)
> For r = 0 To dtTable.Rows.Count - 1
> sw.WriteLine("<tr>")
> For c = 0 To dtTable.Columns.Count - 1
> sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
> Next
> sw.WriteLine("</tr>")
> Next
> End Sub
>
>
> This will give you exactly the same as you already have. But it's
> clearer and more efficient. More importantly, it gives us the foundation
for
> the next step. You can fix any errors that I've made.
> Now, go to it!!
>
> Regards,
> Fergus.
>
> ================================
> To the tune of Rawhide:
>
> Scrollin' scrollin' scrollin,
> Get that web page scrollin'
> Scrollin' scrollin' scrollin,
> Re-PORT.
>
>
>
>
>

's Computer Specifications

Scorpion,
Then you will need to use multiple loops, or put a 'control break' in the
middle of the loop. Or even better don't use a loop.

Something like (pseudo code)

write begin table row
write LYTDSALES
write JANSALES
write FEBSALES
write end table row

write begin table row
write LYTDPROFIT
write JANPROFIT
write FEBPROFIT
write end table row

If LYTDSALES, JANSALES, FEBSALES, LYTDPROFIT, JANPROFIT, & FEBPROFIT are in
the same row in the table, that sounds like poor design. I would have TYPE,
LYTD, JAN, FEB in a row, where TYPE would an enumeration with values of
SALES, PROFIT, ORDERS.

Then the Data Set would look similar to :
TYPE - LYTD - JAN - FEB
'SALES' - 100 - 10 - 90
' PROFIT' - 200 - 20 - 180

If you cannot change the layout of the Dataset, then I would suggest not
using a loop at all, instead explicitly refer to each field building the
output (the pseudo code above).

Hope this helps
Jay

"scorpion53061" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The way I have it currently written all the values appear straight across
as
> if in an excel spreadsheet. What I am tring to do is group all the values
> that go together (LYTDSALES, JANSALES, FEBSALES etc)
>
> in one line and then the values in the next line then in the next line:
> (LYTDPROFIT, JANPROFIT, FEBPROFIT etc)
> then the values from those columns.
>
> I hope I am making sense.
>
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
message
> news:(E-Mail Removed)...
> > Scorpion,
> > I don't see any real problems. I can run your code 'as is' and the
output
> > appears mostly as you describe it. When the HTML is viewed in design
mode
> or
> > the browser, in source view there is only one line, but 'who cares' is
> html
> > source. (if you care, then change the Write to WriteLine...)
> >
> > Maybe I am reading your description wrong.
> >
> > Do you want:
> >
> > c1 c2 c3 c4
> > v1 v2 v3 v4
> >
> > Or do you want:
> >
> > c1 v1
> > c2 v2
> > c3 v4
> > c4 v4
> >
> > Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.
> >
> > I did notice a couple of potential problems:
> >
> > Is sw a StreamWriter? Why aren't you using a
System.Web.UI.HtmlTextWriter
> > instead? Although this is a windows app, you can reference the
System.Web
> > assembly and still use the HtmlTextWriter. This way you are better
assured
> > of having well formed HTML. Granted a StreamWriter is easier...
> >
> > When you write the column names, you have an extra /td on the end of the
> > row.
> >
> > > sw.WriteLine("</b>")
> > > sw.WriteLine("</td>") <- ???
> > > sw.WriteLine("</tr>")
> >
> > Rather than then use <b> around the headers, consider using <th> for
each
> > header cell.
> >
> > > sw.WriteLine("<tr>")
> > > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > > sw.Write("<th>{0}</th>",
> > > dshistory1.Tables(i).Columns(c).ColumnName)
> > > Next
> > > sw.WriteLine("</tr>")
> >
> > This also allows you to control how it is displayed via a CSS file.
> >
> > Rather than use For To, consider a For Each loop, IMO it cleans up the
> code,
> > even more so in VS.NET 2003
> >
> > ' VS.NET 2003 syntax
> > ' (some stuff omitted for clarity)
> > For Each table As DataTable In dshistory.Tables
> > For Each column As DataColumn In table.Columns
> > sw.Write("<th>{0}</th>", column.ColumnName)
> > Next
> > For Each row As DataRow In table.Rows
> > For Each column As DataColumn In table.Columns
> > sw.Write("<td>{0}</td>", row.Item(column))
> > Next
> > Next
> > Next
> >
> > For VS.NET 2002 you will need to use some dims:
> > Dim table As DataTable
> > Dim column As DataColumn
> > Dim row As DataRow
> >
> > Which has the added benefit of causing the program to run quicker, as
you
> > are not dereferencing 3 or 4 objects to get at a value, you are
accessing
> > the object directly. And I've seen reports that indexing a DataRow by a
> > DataColumn is the faster way to index.
> >
> > Hope this helps
> > Jay
> >
<<snip>>

's Computer Specifications

Hi again Scorpion,

Ok, you've now got the same output from code with a better structure. At
least I hope you do, you'll learn it better that way.

The next step is to deal with the sub-tables.

First off, loose ends - Add code in DoReport() for the report heading,
and in Output_Table() for the CUSTNO CUSTNAME SLSMNO row. That will use
columns 0, 1 and 2 of your Table. The actual data starts in column 3.

Now the rest of the columns are in groups of 5:
LYTM: SALES, PROFIT, GM, ORDERS, ARBAL,
LYTD: SALES, PROFIT, GM, ORDERS, ARBAL,
JAN: SALES, PROFIT, GM, ORDERS, ARBAL,
Etc..

So the index for SALES will be 3, 8, 13, etc. For PROFIT it'll be 4, 9,
14, etc. And the pattern continues in an obvious way.

This means that to access all SALES items, you'd have a loop such as
For c = 3 To dtTable.Columns.Count - 1 Step 5
: : :
Next

Now having a 3 and 5 in a loop is next to meaningless compared to having
variable names. The 3 is the starting index of the values. The 5 is the gap
between values. Choose some names - decent length. I'm going to use SIOV and
GBV in my examples. Don't use these in your code - they're terrible names.
I'm using them to force you to think. :-)

So your column headings loop becomes
For c = SIOV To dtTable.Columns.Count - 1 Step GBV
: : :
Next
And likewise for the values.

The next step is to make SIOV and GBV parameters of the subroutines. The
subroutines will just do what they're told. The caller, Output_Table(), is
the one that knows what they should do.

So you'll have
Sub Output_ColumnHeadings (dtTable As DataTable, SIOV As Integer,
GBV As Integer)
And likewise for the values.

Now Output_Table() is going to have to do its thing with each of the
sub-tables. This means that SIOV is going to be different for each Table. So
Output_Table() will need SIOV and GBV to be parameters. And DoReport() is
going to have to tell Output_Table() what values to use.

Perhaps the next step will be obvious to you now? In DoReport() you want
to call OutputTable() once for each sub-table. Which means a loop covering
all 5.

[Note that the number 5 has appeared again. This time it's the number of
tables. You could therefore use the same variable name, GBV. Notice how,
with my name, the idea of a gap-between-values makes no sense compared with
number-of-tables. This is something that occurs often, whereby the same
value "changes" it's use. It warrants a different and appropriate name in
each context. This point can get as subtle as you want. Think about it
later. :-)]

Ok, so OutputReport() now gets a secondary loop around Output_Table().
It also needs to pass correct values for SIOV and GBV.

NumT = 5 'Suitable comment required.
GBV = 5 'Suitable comment required.
SIOV = 3 'Suitable comment required.
For i = 0 To dshistory1.Tables.Count - 1
For ST = 1 To NumT
Output_Table (dshistory1.Tables(i), SIOV + ST - 1, GBV)
Next
Next

Again, I'll leave it to you to make proper code out of all this. Come
back to me with your progress. And tell me what the deadline is, too - I
anticipate that we haven't quite finished the job yet. I expect issues to do
with CUSTNO and CUSTNAME!

Regards,
Fergus.

's Computer Specifications

The deadline is Monday at 10am when I am suppose to demonstrate it for my
boss.

He assigned it last Wednesday....

WIll take some time to work through this.

"Fergus Cooney" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi again Scorpion,
>
> Ok, you've now got the same output from code with a better structure.
At
> least I hope you do, you'll learn it better that way.
>
> The next step is to deal with the sub-tables.
>
> First off, loose ends - Add code in DoReport() for the report heading,
> and in Output_Table() for the CUSTNO CUSTNAME SLSMNO row. That will use
> columns 0, 1 and 2 of your Table. The actual data starts in column 3.
>
> Now the rest of the columns are in groups of 5:
> LYTM: SALES, PROFIT, GM, ORDERS, ARBAL,
> LYTD: SALES, PROFIT, GM, ORDERS, ARBAL,
> JAN: SALES, PROFIT, GM, ORDERS, ARBAL,
> Etc..
>
> So the index for SALES will be 3, 8, 13, etc. For PROFIT it'll be 4,
9,
> 14, etc. And the pattern continues in an obvious way.
>
> This means that to access all SALES items, you'd have a loop such as
> For c = 3 To dtTable.Columns.Count - 1 Step 5
> : : :
> Next
>
> Now having a 3 and 5 in a loop is next to meaningless compared to
having
> variable names. The 3 is the starting index of the values. The 5 is the
gap
> between values. Choose some names - decent length. I'm going to use SIOV
and
> GBV in my examples. Don't use these in your code - they're terrible names.
> I'm using them to force you to think. :-)
>
> So your column headings loop becomes
> For c = SIOV To dtTable.Columns.Count - 1 Step GBV
> : : :
> Next
> And likewise for the values.
>
> The next step is to make SIOV and GBV parameters of the subroutines.
The
> subroutines will just do what they're told. The caller, Output_Table(), is
> the one that knows what they should do.
>
> So you'll have
> Sub Output_ColumnHeadings (dtTable As DataTable, SIOV As Integer,
> GBV As Integer)
> And likewise for the values.
>
> Now Output_Table() is going to have to do its thing with each of the
> sub-tables. This means that SIOV is going to be different for each Table.
So
> Output_Table() will need SIOV and GBV to be parameters. And DoReport() is
> going to have to tell Output_Table() what values to use.
>
> Perhaps the next step will be obvious to you now? In DoReport() you
want
> to call OutputTable() once for each sub-table. Which means a loop covering
> all 5.
>
> [Note that the number 5 has appeared again. This time it's the number
of
> tables. You could therefore use the same variable name, GBV. Notice how,
> with my name, the idea of a gap-between-values makes no sense compared
with
> number-of-tables. This is something that occurs often, whereby the same
> value "changes" it's use. It warrants a different and appropriate name in
> each context. This point can get as subtle as you want. Think about it
> later. :-)]
>
> Ok, so OutputReport() now gets a secondary loop around Output_Table().
> It also needs to pass correct values for SIOV and GBV.
>
> NumT = 5 'Suitable comment required.
> GBV = 5 'Suitable comment required.
> SIOV = 3 'Suitable comment required.
> For i = 0 To dshistory1.Tables.Count - 1
> For ST = 1 To NumT
> Output_Table (dshistory1.Tables(i), SIOV + ST - 1, GBV)
> Next
> Next
>
> Again, I'll leave it to you to make proper code out of all this. Come
> back to me with your progress. And tell me what the deadline is, too - I
> anticipate that we haven't quite finished the job yet. I expect issues to
do
> with CUSTNO and CUSTNAME!
>
> Regards,
> Fergus.
>
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sum values in columns based on values in named range	Mikael Andersson	Microsoft Excel Worksheet Functions	10	12th Nov 2008 09:37 AM
Excel Compare values in columns & display missing values in a new	=?Utf-8?B?Y3BldHRh?=	Microsoft Excel Misc	1	2nd Apr 2005 05:51 AM
Summing only values in visible columns, not in hidden columns	=?Utf-8?B?Qlc=?=	Microsoft Excel Misc	1	2nd Nov 2004 06:23 PM
Re: URGENT HELP: Pick out unique values from repeated values in one ce	NH	Microsoft Excel Worksheet Functions	2	13th Sep 2004 06:30 PM
urgent! how to compare values in two columns...	R Green	Microsoft Excel Worksheet Functions	2	6th Jul 2004 03:19 PM