I want to enter a "Some text" at the background of the form that i will print.......
its like the "waterpaper" of the microsoft words........
I think you have heard of this.....
Is it possible to do this here?
Printable View
I want to enter a "Some text" at the background of the form that i will print.......
its like the "waterpaper" of the microsoft words........
I think you have heard of this.....
Is it possible to do this here?
Code:Imports System.Data
Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\waste\waste\db1.mdb"
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Table1", myConnection)
Dim ds As DataSet = New DataSet
DataGridView1.Columns(0).DataPropertyName = "Name"
DataGridView1.Columns(1).DataPropertyName = "Address"
DataGridView1.Columns(2).DataPropertyName = "Roll"
da.Fill(ds, "Table1")
Dim dt As New DataTable
da.Fill(dt)
DataGridView1.DataSource = dt
End Sub
Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Try
con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\waste\waste\db1.mdb")
con.Open()
cmd = New OleDbCommand("Insert into Table1 values ( ' " & TextBox1.Text.Trim() & " ' , ' " & TextBox2.Text.Trim() & " ',' " & TextBox3.Text.Trim() & " ' )", con)
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Sub
Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim dt As New DataGridView
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excel.ApplicationClass
Dim i As Integer = 1
Dim j As Integer
Try
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
With xlWorkSheet
xlApp.Range("A1:C1").Font.Bold = 15
.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
xlApp.Range("A1:C" & i).Borders.LineStyle = 1
xlApp.Range("A1:C" & i).HorizontalAlignment = 2
'xlApp.Range("A1:C" & i).Font.Bold = 15
xlWorkSheet.PrintOut()
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Catch ex As Exception
End Try
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class
You need to draw the text in the forms paint event using the relevant gray color
then how to add it in the above code?
I want to see the text in the printed form.......
vb Code:
Include this in ur code, and there will be 'Hello' drawn on the datagridview at position 50,50.
You can change the color to lightgray to get an water mark effect
sorry ashis.......
it did not work........
"Hello" appeared in the datagridview design part but it did nt appeared in the printed page..............
Somebody help me please
How and where are you doing the printing job
Just saving it in a directoy................After clicking the print button.....Why shoul i need to print it out?
I didnt get you can you brief a bitQuote:
Originally Posted by gautamshaw
open an excel page and try to print it........a form comes to save it.........you just save it in ur desired location and tats it.............
Ohh..
So you are exporting the data to excel and you want the water mark in execel sheet
Can you help me out...............
When you say you are printing it out do you mean printing it out, or do you mean saving it?
Your statements seem contradictory :
Aashish : "How and where are you doing the printing job"
gautumshaw : "Just saving it in a directoy................After clicking the print button.....Why shoul i need to print it out?"
Thats saving, not printing. The Print dialog is not used for saving files unless you use a special print driver to print to file rather than a physical printer. If thats what you mean then you should state that is what you are doing.
for your kind info paul,I did nt installed the print driver externllay......
i had a laptop.......everything ia automatically installed here okkkk.......
Now can you help me out in this?
I'm just trying to clarify what you are doing - saving or printing. Printing normally produces a physical sheet of paper, but you are talking about saving a file. I can only assuming you are printing to some kind of filewriter rather than a printer.
If you want to save a file rather than produce a printout then you shouldn't be using the print functionality.
either way it seems what you want to do is add a watermark to an excel spreadhseet. This is how you would do it in VBA :
This may work pretty much "out of the box" in vb.net or it may not - you will need to experiment.Code:xlSheet.Shapes.AddTextEffect(PresetTextEffect:=0, _
Text:="DRAFT", FontName:="Arial Black", FontSize:=36, _
FontBold:=False, FontItalic:=False, Left:=10, Top:=10).Select
Note I say "you" will need to experiment not "I will experiment and get back to you"- I am on a new laptop which I havent installed office on yet so I cant test it, and it will do you good to do a little testing of your own!
Can you give me a concept paul........
Just look at the area above the draft in the below figure:
Attachment 72072
Is it possible to use it.......i.e,do i need to move draft above the table or i shall move the table below the draft?
May be this is a nonsense question because in the below code:
Left and top are already set to 0.......but please give me the answer paul..........Code:xlWorkSheet.Shapes.AddTextEffect(PresetTextEffect:=3, _
Text:="DRAFT", FontName:="Arial Green", FontSize:=36, _
FontBold:=False, FontItalic:=False, Left:=0, Top:=0).Select()
I suspect your problem is that the space above the table isn't actually part of your spreadsheet, it is just the margin of the printed area.
I think that the cell with "Name" as the entry is cell A1, so that is position 0,0 as far as the positioning of the text is concerned.
If you want to create some space above the cells you will need to change the cell references you are using for pasting the datagrid contents into the spreadsheet, ie instead of putting them in cells "A1" to "C3" put it in (for example) "A3" to "C6", and leave the text where it is.
That said if you want a watermark, that traditionally appears behind the body of the page not above it. It sounds almost like you want a heading to the page, which is achieved in a totally different manner.
Attachment 72073Code:xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
With xlWorkSheet
xlApp.Range("A10:C13").Borders.LineStyle = 1
xlApp.Range("A10:C13").HorizontalAlignment = 2
xlApp.Range("A10:C13").Font.Bold = 15
.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
xlWorkSheet.Shapes.AddTextEffect(PresetTextEffect:=3, _
Text:="Goutam", FontName:="Arial Green", FontSize:=50, _
FontBold:=False, FontItalic:=False, Left:=100, Top:=0).Select()
I got control of the table cells paul.....
But the loop portion:
I cant mess with this Paul....Code:For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
giving some abnormal result if i test with dis.......
How to deal with this for loop paul........
Well if you are moving the grid down by 3 rows then I'm guessing that it would be appropriate to move the contents down by 3 as well.
I am in vain paul.....
Cant work with this part of the code.......Code:.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
OK lets look at your code...
It is your code... do you understand what each line does?Code:.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Roll"
Is it so Paul that suppose I am trying to print from a machine in which the Excel is not installed,then will the entire printing code work?
If i am not incorrect then,Code:.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Roll"
In the above code paul,the ("A1:C1") will contain the "Name",("A1:C2") will contain the "Address" and ("A1:C3") will contain the "Roll"
If you are trying to run on a machine without Excel installed your code won't run.
I'm wondering if you've thought this through at all...
If you are simply trying to print the contents of your datagrid then you are kinda going a long way around and introducing a dependency on Microsoft Excel being installed on all users' machines.
No that is not correct. For a start "A1:C1" is a range not a cell.Quote:
In the above code paul,the ("A1:C1") will contain the "Name",("A1:C2") will contain the "Address" and ("A1:C3") will contain the "Roll"
Then i am in great danger paul.....................
Tomorrow is the last date of submission...........
I think i am gone..........
Then how to do this paul if not in excel then?
I dont have any concept of printing.......
Except a bit that Manhit gave me tomorrow.........
OK If this is some kind of an assignment you can probably get away with relying on excel, if it was commercial software you would need to think very carefully about introducing a requirement for the user to have other 3rd party commercial software installed.
Those lines of code put "Name" in cell A1 (row 1, col 1), "Address" in A2 (row 1, col 2) and "Roll" in A3 (row 1, col 3).
So how would you change that so that those entries appeared in row 3?
Help me paul..........i need your help.......
will it be like this paul?Code:.Cells(3, 1).Value = "Name"
.Cells(3, 2).Value = "Address"
.Cells(3, 3).Value = "Roll"
Have you tried it, and did it work?
No paul it did nt work
the heading disappears.....Attachment 72075Code:.Cells(3, 1).Value = "Name"
.Cells(3, 2).Value = "Address"
.Cells(3, 3).Value = "Roll"
Here lies my entire code paul........Code:xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
With xlWorkSheet
xlApp.Range("A10:C13").Borders.LineStyle = 1
xlApp.Range("A10:C13").HorizontalAlignment = 2
xlApp.Range("A10:C13").Font.Bold = 15
.Cells(3, 1).Value = "Name"
.Cells(3, 2).Value = "Address"
.Cells(3, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
xlWorkSheet.Shapes.AddTextEffect(PresetTextEffect:=3, _
Text:="Goutam", FontName:="Arial Green", FontSize:=50, _
FontBold:=False, FontItalic:=False, Left:=150, Top:=0).Select()
OK right, first of all you have moved the grid down to start at row 10 so for your headings you also want to move them down by 10 not 3. I was using 3 as an example but seeing as you have moved the grid down by 10 you should do the same with the headings and data.
To do a simple test comment out all of those lines past the End With and run it. You should see the grid and the headings at the top row of the grid. The reason why it looks like it isn't working is because currently the data is going to overwrite your headings because you haven't moved the location of the data yet.
If that is working then you uncomment out the For...Next loop and get that to write it cells out in the right place by adding 10 to the row you are using inside the loop.
I did this paul.....the heading is now al right....Code:.Cells(10, 1).Value = "Name"
.Cells(10, 2).Value = "Address"
.Cells(10, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
Attachment 72077
But again the two for loops are not working good......
Code:For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
They aren't working because you havent changed the destination cells.
This :
is no different toCode:xlWorkSheet.Cells(i + 1, j + 1)
except for it is using variables to specify the row and column.Code:xlWorhSheet.Cells(1,1)
So if adding 10 to the row worked before, why not try adding 10 to the row in that code as well?
Thats why I said this :
Quote:
If that is working then you uncomment out the For...Next loop and get that to write it cells out in the right place by adding 10 to the row you are using inside the loop.
This did nt work.....Code:.Cells(10, 1).Value = "Name"
.Cells(10, 2).Value = "Address"
.Cells(10, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 10, j + 10) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
Attachment 72078
You aren't looking at and understanding what your code does. Thats why I'm trying to take you through it line by line and not just giving you the answer.
Look at this bit...
why are you adding 10 to the column as well? You only want to add 10 to the row. Do you not understand yet that the first number in the brackets is the row number, and then 2nd number is the column number??Code:xlWorkSheet.Cells(i + 10, j + 10)
Note though that in your code you are actually only adding 9 because you were already adding 1 in the initial code (i + 1) so adding 10 would be (i + 1 + 10) or (i + 11)
To save more heartache just try this :
Code:xlWorkSheet.Cells(i + 11, j + 1)
this worked for me sir.........Code:For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 10, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
this code is perfect for me sir.......Code:xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
With xlWorkSheet
xlApp.Range("A10:C13").Borders.LineStyle = 1
xlApp.Range("A10:C13").HorizontalAlignment = 2
xlApp.Range("A10:C10").Font.Bold = 15
.Cells(10, 1).Value = "Name"
.Cells(10, 2).Value = "Address"
.Cells(10, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 10, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
Now i want to print the same thing not getting dependent on the excel........Can you help me out of sir....
Your entire approach to printing is using Excel, if you want to print without being dependent on the presence of Excel you are going to have to go back and write it all from scratch.
Given that you have only today to complete your work I think that is beyond you.
truely speaking sir,
I referred a few books yesterday.........but i found all of them confusing.......
You just help me step by step sir....I might complete my work this whole night..............
I have got the confidence sir........all i need is your help..........
That said... if you really want to try it I'd make a backup copy of your code as it is, and then take a look at this.
Personally I'd probably just construct an HTML page with your table and load that up into the user's default browser to printout.
While I'm flattered that you have the confidence, unfortunately I cant sit here all day helping you out line by line (England are beating Australia in the cricket right now, and it's the final round of the Open golf - both of which are competing for my attention!)Quote:
Originally Posted by gautamshaw
i am ready sir..........I just need your guidance........
Please help me sir.....
I also love watching the ashes Sir...........
Dont you ve any code..........Which you can give to me.....
My honest opinion - take a look at the VB code in the example I linked to - you will need to modify it slightly to include your watermark, but that is probably the simplest way of doing it.
Note though that i haven't used that source code at all so I can't necessarily give you any great help with it.
I have probably got some example code of how to take a gridview and build the contents into an HTML table but its on my old laptop - I will see if I can dig it out.
ok sir then you just give me the link where i could find it........
One last confusion sir regarding the excel printing........
I tried to make the cells dynamically depending on the number of rows and columns present.......
I get bad output.....Code:xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
With xlWorkSheet
' xlApp.Range("A10:C13").Borders.LineStyle = 1
'xlApp.Range("A10:C13").HorizontalAlignment = 2
xlApp.Range("A10:C10").Font.Bold = 15
.Cells(10, 1).Value = "Name"
.Cells(10, 2).Value = "Address"
.Cells(10, 3).Value = "Roll"
End With
For i = 1 To DataGridView1.RowCount - 1
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 10, j + 1) = DataGridView1(j, i - 1).Value.ToString()
Next
Next
xlApp.Range("A10:C" & i).Borders.LineStyle = 1
xlApp.Range("A10:C" & i).HorizontalAlignment = 2
xlWorkSheet.Shapes.AddTextEffect(PresetTextEffect:=3, _
Text:="Goutam", FontName:="Arial Green", FontSize:=50, _
FontBold:=False, FontItalic:=False, Left:=150, Top:=0).Select()
'xlApp.Range("A1:C" & i).Font.Bold = 15
xlWorkSheet.PrintOut()
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Attachment 72079
Please dont get irritated sir......I need your help
You need to change this
because i is the number of rows in your grid, which in your example is 3, so the range you are looking at is A10:C3. You want it to go from A10 to C13, so it should beCode:xlApp.Range("A10:C" & i).Borders.LineStyle = 1
xlApp.Range("A10:C" & i).HorizontalAlignment = 2
Code:xlApp.Range("A10:C" & 10+i).Borders.LineStyle = 1
xlApp.Range("A10:C" & 10+ i).HorizontalAlignment = 2
Right... I've dug out my old code which takes a DataGridView control and creates a HTML document containing the table which can then be viewed and printed by your default web browser.
This is a stripped down version as the original had code for emulating formatting (like different coloured backgrounds in cells etc), but it should do what you want it to. You need to call this routine passing in your datagridview, and retrieve the filename returned and then fire the webbrowser to open the file.
Code:Private Function RenderListAsHTML(ByRef DataGrid As DataGridView) As String
Dim swrOutput As StreamWriter
Dim sFormat As String
Dim sPath As String
'establish a temporary filename
sPath = Path.GetTempFileName
swrOutput = New StreamWriter(sPath)
swrOutput.WriteLine("<head>")
swrOutput.WriteLine("<title>Your Title Here</title>")
swrOutput.WriteLine("</head>")
swrOutput.WriteLine("<style type='text/css'>")
swrOutput.WriteLine("th")
swrOutput.WriteLine("{")
swrOutput.WriteLine("font: 8pt verdana")
swrOutput.WriteLine("}")
swrOutput.WriteLine("tr")
swrOutput.WriteLine("{")
swrOutput.WriteLine("font: 8pt verdana")
swrOutput.WriteLine("}")
swrOutput.WriteLine("</style>")
swrOutput.WriteLine("<body>")
swrOutput.WriteLine("<table>")
'column headers
For Each dvgHeader As DataGridViewColumn In DataGrid.Columns
swrOutput.WriteLine("<th bgcolor='silver'>" & dvgHeader.HeaderText & "</th>")
Next
'actual data
For Each dvgRow As DataGridViewRow In DataGrid.Rows
If dvgRow.Visible Then
swrOutput.Write("<tr>")
For Each objCell As DataGridViewCell In dvgRow.Cells
swrOutput.WriteLine("<td>" & objCell.Value.ToString & "</td>")
Next
swrOutput.Write("</tr>")
End If
Next
swrOutput.WriteLine("</table>")
swrOutput.WriteLine("</body>")
swrOutput.Flush()
swrOutput.Close()
Return sPath
End Function
I am trying it out sir.......
You hust try to be online for a few minutes........
Its a request sir.........
I will reply soon after trying it.....
give me only 5 to 7 minutes...........
Please remain online sir for helping me........
NB - in order to incorporate your watermark you'll need to have a prepared image, and use
Code:swrOutput.WriteLine("<table style='background-image: url(""your_image.gif"")'>"
i am getting an error in this line sir......
It is telling that :Code:Dim swrOutput As StreamWriter
StreamWriter not defined.............
where and how to define it sir?
I think i need to add reference..........am i right sir?
You'll need to either add a line at the top of your form saying "Imports System.IO" or change the line to
Code:Dim swrOutput as System.IO.StreamWriter
one more error sir......
unused local variable:"sFormat"Code:Dim sFormat As String
Should I delete this line sir?
what to do?
Ah yeah - it should be a warning not an error (ie it won't stop your code from running), but you can just delete that line - it is a hangover from the code I removed handling specific formatting for rows.
should i call this function ?Code:You need to call this routine passing in your datagridview, and retrieve the filename returned and then fire the webbrowser to open the file.
in which event of gridview should i call it?Code:Private Function RenderListAsHTML(ByRef DataGrid As DataGridView) As String
My head is totally gone sir.........I am very tensed still..please help me sir.....
I presume you have a Print button on your form that calls your print routine... button3 by the looks of things.
In that button's click event you would call this routine. Note that you need to pass in the name of your gridview, and it will return the filename of the file that it has created.
From there you can either launch the user's default browser using process.start, or you can create a new webbrowser object and load the HTML file into it and print automatically if thats what you want.
I did this sir......Code:Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.RenderListAsHTML(DataGridView1)
End Sub