Columns and Values (Urgent)

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

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" />
 
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
 
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>")

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>")
Click to expand...

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
 
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.
 
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.
 
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
 
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 said:
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.

mode
Click to expand...
<<snip>>
 
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.
 
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 said:
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.
Click to expand...
 
Hi Jay,

Lol, I thought of going down that route with Scorpion, but it's a whole
new language!

I wish I were able to give Scorpion "best practice" with what I'm
doing - certainly the solution that we're developing isn't how I'd do it
myself - but I suspect that Scorpion doesn't have our years.

And the keyword in his request is (Urgent) !! :-)

Best Wishes,
Fergus
 
I am not familar with XSLT. But can you help me learn enough to do what I
need to do here...

By the way I did not design the SQL table this came from. It is from another
application that is fed from a unix server.
 
Fergus,
You know, sometimes you need to say "What the F**k!" and suggest a totally
different approach. ;-)

(if you've seen the non-primetime version of Risky Business).

Later
Jay
 
Scorpion,
Why don't you send a peace of the HTML output in this group,
Only the lines between the <table><tr>and the second </tr>
Then I know what is the problem and Jay knows what is the problem.
(You can change all the numeric data before)
Cor
 
Back
Top