Printing problem........Need some help

[gautamshaw]

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?

[gautamshaw]

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

[aashish_9601]

You need to draw the text in the forms paint event using the relevant gray color

[gautamshaw]

then how to add it in the above code?
I want to see the text in the printed form.......

[aashish_9601]

Private Sub DataGridView1_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles DataGridView1.Paint
        Dim f As New Font("Arial", 30, FontStyle.Regular)
        e.Graphics.DrawString("Hello", f, Brushes.Black, 50, 50)
    End Sub

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

[gautamshaw]

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

[aashish_9601]

How and where are you doing the printing job

[gautamshaw]

Just saving it in a directoy................After clicking the print button.....Why shoul i need to print it out?

[aashish_9601]

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 bit

[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.............

[aashish_9601]

Ohh..
So you are exporting the data to excel and you want the water mark in execel sheet

[gautamshaw]

Can you help me out...............

[keystone_paul]

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.

[gautamshaw]

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?

[keystone_paul]

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.

[keystone_paul]

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 :

      xlSheet.Shapes.AddTextEffect(PresetTextEffect:=0, _
      Text:="DRAFT", FontName:="Arial Black", FontSize:=36, _
      FontBold:=False, FontItalic:=False, Left:=10, Top:=10).Select

This may work pretty much "out of the box" in vb.net or it may not - you will need to experiment.

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!

[gautamshaw]

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:

xlWorkSheet.Shapes.AddTextEffect(PresetTextEffect:=3, _
      Text:="DRAFT", FontName:="Arial Green", FontSize:=36, _
      FontBold:=False, FontItalic:=False, Left:=0, Top:=0).Select()

Left and top are already set to 0.......but please give me the answer paul..........

[keystone_paul]

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.

[gautamshaw]

 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()

Attachment 72073

I got control of the table cells paul.....
But the loop portion:

 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

I cant mess with this Paul....
giving some abnormal result if i test with dis.......
How to deal with this for loop paul........

[keystone_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.

[gautamshaw]

I am in vain paul.....

.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

Cant work with this part of the code.......

[keystone_paul]

OK lets look at your code...

                .Cells(1, 1).Value = "Name"
                .Cells(1, 2).Value = "Address"
                .Cells(1, 3).Value = "Roll"

It is your code... do you understand what each line does?

[gautamshaw]

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?

[gautamshaw]

.Cells(1, 1).Value = "Name"
                .Cells(1, 2).Value = "Address"
                .Cells(1, 3).Value = "Roll"

If i am not incorrect then,
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"

[keystone_paul]

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.

[keystone_paul]

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"

No that is not correct. For a start "A1:C1" is a range not a cell.

[gautamshaw]

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

[keystone_paul]

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?

[gautamshaw]

Help me paul..........i need your help.......

[gautamshaw]

.Cells(3, 1).Value = "Name"
                .Cells(3, 2).Value = "Address"
                .Cells(3, 3).Value = "Roll"

will it be like this paul?

[keystone_paul]

Have you tried it, and did it work?

[gautamshaw]

No paul it did nt work
the heading disappears.....

 .Cells(3, 1).Value = "Name"
                .Cells(3, 2).Value = "Address"
                .Cells(3, 3).Value = "Roll"

Attachment 72075

[gautamshaw]

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()

Here lies my entire code paul........

[keystone_paul]

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.

[gautamshaw]

.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

I did this paul.....the heading is now al right....
Attachment 72077
But again the two for loops are not working good......

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

[keystone_paul]

They aren't working because you havent changed the destination cells.

This :

xlWorkSheet.Cells(i + 1, j + 1)

is no different to

xlWorhSheet.Cells(1,1)

except for it is using variables to specify the row and column.

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 :

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.

[gautamshaw]

 .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

This did nt work.....
Attachment 72078

[keystone_paul]

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

xlWorkSheet.Cells(i + 10, j + 10)

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??

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 :

xlWorkSheet.Cells(i + 11, j + 1)

[gautamshaw]

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 worked for me sir.........

 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

this code is perfect for me sir.......


Now i want to print the same thing not getting dependent on the excel........Can you help me out of sir....

[keystone_paul]

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.