Visual Basic

Re: make file excel(*.xls) from vb .NET 2003 [modified]

28-Nov-06 4:29

Neither was the question...

Dave Kreskowiak
Microsoft MVP - Visual Basic

Kevin Nicol28-Nov-06 1:42

Kevin Nicol

28-Nov-06 1:42

Here is a simple app that takes a dataset and writes a worksheet per data table in the set
Public Sub writeToExcel(ByVal source As DataSet)
Dim doc As New System.IO.StreamWriter("Excel.xls") 'use .xls even though its an xml file

Dim startExcelXML As String = ""

startExcelXML &= "<xml version>"
startExcelXML &= vbNewLine & "<Workbook " + "xmlns=""urn:schemas-microsoft-com:office:spreadsheet"""
startExcelXML &= vbNewLine & Microsoft.VisualBasic.Chr(10) & "" + " xmlns Blush | :O

=""urn:schemas-microsoft-com:office:office"""
startExcelXML &= vbNewLine & "xmlns:x=""urn:schemas- microsoft-com:office:" + "excel"""
startExcelXML &= vbNewLine & "xmlns:ss=""urn:schemas-microsoft-com:" + "office:spreadsheet"">"

'write the styles tags that format the data and cells properly
startExcelXML &= vbNewLine & "<Styles>"

startExcelXML &= vbNewLine & " <Style ss:ID=""Default"" ss:Name=""Normal"">"
startExcelXML &= vbNewLine & " <Alignment ss:Vertical=""Bottom""/>"
startExcelXML &= vbNewLine & " <Borders/>"
startExcelXML &= vbNewLine & " <Font/>"
startExcelXML &= vbNewLine & " <Interior/>"
startExcelXML &= vbNewLine & " <NumberFormat/>"
startExcelXML &= vbNewLine & " <Protection/>"
startExcelXML &= vbNewLine & " </Style>"

startExcelXML &= vbNewLine & "<Style ss:ID=""ColHeader"">"
startExcelXML &= vbNewLine & "<Alignment ss:Horizontal=""Center"" ss:Vertical=""Bottom""/>"
startExcelXML &= vbNewLine & "<Font x:Family=""Swiss"" ss:Size=""8"" ss:Bold=""1""/>"
startExcelXML &= vbNewLine & "<Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>"
startExcelXML &= vbNewLine & "</Style>"

startExcelXML &= vbNewLine & " <Style ss:ID=""Reg"">"
startExcelXML &= vbNewLine & " <Font " + "x:Family=""Swiss"" ss:Bold""0""/>"
startExcelXML &= vbNewLine & " </Style>"

startExcelXML &= vbNewLine & "</Styles>"

'write the header to the file
doc.WriteLine(startExcelXML)

'write one sheet per table
For Each tab As DataTable In source.Tables
Dim sheetname As String = "<Worksheet ss:Name="
sheetname &= Microsoft.VisualBasic.Chr(34)
sheetname &= tab.TableName.Trim
sheetname &= Microsoft.VisualBasic.Chr(34)
sheetname &= ">"
doc.WriteLine(sheetname)

doc.WriteLine("<Table>")

'write the column headers
doc.WriteLine("<Row>")
For Each col As DataColumn In tab.Columns
doc.Write("<Cell ss:StyleID=""ColHeader""><Data ss:Type=""String"">")
doc.Write(col.ColumnName)
doc.WriteLine("</Data></Cell>")
Next
doc.WriteLine("</Row>")

'write the table
For Each row As DataRow In tab.Rows
doc.WriteLine("<Row>")

'Dim i As Integer
For i As Integer = 0 To tab.Columns.Count - 1
doc.Write("<Cell ss:StyleID=""Reg""><Data ss:Type=""String"">")
doc.Write(row.Item(i))
doc.WriteLine("</Data></Cell>")
Next

doc.WriteLine("</Row>")
Next

doc.WriteLine("</Table>")
doc.WriteLine("</Worksheet>")
Next

doc.WriteLine("</Workbook>")
doc.Close()

End Sub

this can be opened in excel. You will have to add your own formating to make the sheet look like you want, to do that just make changes, save it and open it and see what it did to the tags. Then make your code do the same.

Hope this helps

Kevin

-- modified at 9:55 Tuesday 28th November, 2006

reading a block of data from a text file

charchabil0327-Nov-06 22:49

27-Nov-06 22:49

Hey guys
I m reading from a text file (that contains data like the following)
What I want is to simply get the data between the bold lines-each block alone
Any hint for that?

A1/EXT "BK82 LB73 21233" 105 061018 1804
EXTERNAL ALARM
COOLING ALARM
A2

A1/POWER "BK82 LB73 21233" 362 061019 0947
EXTERNAL ALARM
EDL FAILURE KAA
A1
A1/POWER "BK82 LB73 21233" 364 061019 0947
.....

thx

Regards
Ramy

Christian Graus27-Nov-06 23:49

Christian Graus

27-Nov-06 23:49

You need to read the whole file, and use a regex to find the lines that are in the format you want, assuming you can't rely on them being on the same lines.

Christian Graus - C++ MVP

'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert

charchabil0328-Nov-06 1:16

28-Nov-06 1:16

seems that i wasnt clear,
what i want is to read each block alone.(the sizes of the blocks are different)
i have hilighted the first line of each block in bold, in order to make myself clear..

Regards
Ramy

Christian Graus28-Nov-06 8:13

Christian Graus

28-Nov-06 8:13

Do you mean you want to read only part of a text file, not the whole thing ? You can't do that. There are APIs that can make it look like you can, but the only way to find the bits you want, is to read the whole file, throwing away the bits you don't want. The easiest way is to read in the whole file and search it, it's also the most efficient.

Christian Graus - C++ MVP

'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert

charchabil0328-Nov-06 9:33

28-Nov-06 9:33

seems i m expressing my self
if u take a look back to my first displayed msg:
what i want is : to read each block of that file alone ,and assign it to a string value (then insert it into a DB)

i have done the following-this is part of it

dim read as string<br />
while not sr.peerk=-1<br />
   checkstr =sr.readline<br />
read &=checkstr & vbnewline<br />
end while

the problem that i m facaing is that i cant retrieve each block alone
all the file is being concatanated,note : the block sizes are variant
the only way to separate them is by the presence of the double quote,after each presence of double quote:"read" should be cleared,and filled again (take a look at the first msg)
thanks

Regards
Ramy

Christian Graus28-Nov-06 11:47

Christian Graus

28-Nov-06 11:47

charchabil03 wrote:
the problem that i m facaing is that i cant retrieve each block alone

Isn't each block one line tho ?

charchabil03 wrote:
read &=checkstr & vbnewline

It would be quicker to just read the entire file, instead of parsing for lines. You can turn it into a string array with split, and work your way through from there, looking for your blocks. Assuming you don't want to filter the lines before adding them to the 'read' variable.

charchabil03 wrote:
the only way to separate them is by the presence of the double quote,after each presence of double quote:"read" should be cleared,and filled again (take a look at the first msg)

OK, I get it now. In that case, you just need to search checkstr for a ", right ? There's a Contains method on the string class.

Christian Graus - C++ MVP

'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert

Web Control

riyaz_786_p27-Nov-06 21:05

riyaz_786_p

27-Nov-06 21:05

Hi All
I have made one application which logs to a government website from my application.
For that purpose i have created a web explorer on my form and i have specified that particular URL name.
As soon as i run that form it opens that particular website

Now In that website there is 2 textboxes (user name and password) and 2 buttons (submit and cancel)
When I count no. of item by webcontrol1.controls.items.count() gives me 4
Is there a way to access these controls directly from my application by using webcontrol.controls.items(0).text = "XYZ"
something like this.......I am not able to get through with this

Also I can find a particular string in the document by
webcontrol1.document.outertext.contains("ABC") returns .T. , but how to select that particluar text automatically after finding it.

Please Help....Its Very Urgent......

Thanx In Advance

Riyaz Zujer Patanwala
I.T.Programmer
Al Jaber Energy Services
P.O.Box 47467
Abu Dhabi
U.A.E.

Stopping a service

nitin_ion27-Nov-06 20:50

nitin_ion

27-Nov-06 20:50

I am able to fetch services running on a remote system through WMI
Now i want to stop/Start a service.

I have not found any method in WMI to start or stop a service through WMI on remote system

Is there a way i can start and stop service on a remote system?

Re: Stopping a service

Dave Kreskowiak28-Nov-06 4:35

28-Nov-06 4:35

You have to call the StartService and StopService methods on the Win32_Service objects that your getting back from the remote machine.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Re: Stopping a service

nitin_ion28-Nov-06 16:34

nitin_ion

28-Nov-06 16:34

Thnx,

is there a list where i can find out all the methods WMI classes

Re: Stopping a service

Dave Kreskowiak29-Nov-06 2:44

Re: Regarding data binding

29-Nov-06 2:44

Of course, it's called MSDN. Docs on Win32_Service[^]

Dave Kreskowiak
Microsoft MVP - Visual Basic

vb wininet Problem

psiva198427-Nov-06 20:34

psiva1984

27-Nov-06 20:34

hi all,

when internet connection failed program hangs for a while
and gives a message(i wrote as when internet connection failed come out program even though it was hanging)
How can we know the internet connection failed.

can any one help.

[Message Deleted]

PREMSONBABY27-Nov-06 20:01

PREMSONBABY

27-Nov-06 20:01

[Message Deleted]

jsampsonPC28-Nov-06 4:08

jsampsonPC

28-Nov-06 4:08

If the following code shows you what properties and methods exist in BuddyItem when you test the member of X, then it's early-binding.

I didn't know that it would actually perform the typecast during design-time. Are you sure it's allowing you to access the inherited members through X at design-time?

Jonathan Sampson
www.SampsonResume.com

[Message Deleted]

PREMSONBABY28-Nov-06 5:06

PREMSONBABY

28-Nov-06 5:06

[Message Deleted]

Re: Regarding data binding

jsampsonPC28-Nov-06 5:08

jsampsonPC

28-Nov-06 5:08

http://visualbasic.about.com/od/usingvbnet/a/earlybind.htm

Jonathan Sampson
www.SampsonResume.com

how to insert data into datagrid dynamically usin insert command

tirumal123127-Nov-06 18:12

tirumal1231

27-Nov-06 18:12

cmd.CommandText = "insert into ssc(firstname,lastname,phno) values ('" & t1.Text & "','" & t2.Text & "'," & t3.Text & ")"
cmd.Connection = cn
cmd.ExecuteReader()
i am getting a syntax error like:

Server Error in '/Datagrid' Application.
--------------------------------------------------------------------------------

Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'.

please help me..

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

tirumal

Re: how to insert data into datagrid dynamically usin insert command

Guffa27-Nov-06 19:48

Guffa

27-Nov-06 19:48

Have you verified that the first two controls does not contain any characters that needs escaping, and that the third control contains a value that represents a number?

---
b { font-weight: normal; }

am i stupid beginner??i donnu how to write IF??

charchabil0327-Nov-06 11:53

Re: am i stupid beginner??i donnu how to write IF??

27-Nov-06 11:53

this is function that i m using and it returns an integer

Public Function getavailableswitch(ByVal checkstr As String) As Int16<br />
....<br />
 getavailableswitch = cmd.ExecuteScalar()<br />
end function

what i want is: to call the above function and
thus

Dim id As Integer<br />
If id = getavailableswitch(checkStr) <> 0 Then<br />
MsgBox(id)<br />
End If

i know it sounds very bad,
Note i want to do withing the "if" statement
1-the call statement
2-the test on retreived data from the function

thx for your help

Regards
Ramy

Dave Kreskowiak27-Nov-06 12:06

Re: am i stupid beginner??i donnu how to write IF??

27-Nov-06 12:06

You already asked this in another thread and got the answer that works. But, how you've written your IF statement is actually comparing the value in id to the value returned by getavailableswitch. You're not making an assignment and then comparing that to 0. Start by simplifying your code and do the assignment outside of the IF statement:

Dim id As Integer
id = getavailableswitch(checkStr)
If id <> 0 Then
    MsgBox(id)
End If

Also, your function should be changed to use a nice return type, instead of an Int16. You're not doing anything special to warrant using a smaller return type, so why bother...

Public Function getavailableswitch(ByVal checkstr As String) As Integer
    ....
    Return cmd.ExecuteScalar()
End Function

Dave Kreskowiak
Microsoft MVP - Visual Basic

charchabil0327-Nov-06 12:24

Re: am i stupid beginner??i donnu how to write IF??

27-Nov-06 12:24

i asked in other thread.coz i thought that ther person who already replied to me will be offline now,and i wanted a reply fast..
Any way thx

Regards
Ramy

Dave Kreskowiak27-Nov-06 16:55