Author: ABAYTHON

Tkinter Python Program to get details from Form and feeding to excel sheet

We are getting data via the form and feeding that to excel sheets. Let’s look into it with the below example:

import tkinter as tk
import xlwt

root = tk.Tk()

# setting the windows size
root.geometry("600x400")

# declaring string variable
# for storing details
cid_var = tk.StringVar()
cname_var = tk.StringVar()
caddr_var = tk.StringVar()
cstate_var = tk.StringVar()
ccity_var = tk.StringVar()
cmobile_var = tk.StringVar()
cgender_var = tk.StringVar()
cage_var = tk.StringVar()

# defining a function that will
# get the details and
# print them on the screen
def submit():
    cid = cid_var.get()
    cname = cname_var.get()
    caddr = caddr_var.get()
    cstate = cstate_var.get()
    ccity = ccity_var.get()
    cmobile = cmobile_var.get()
    cgender = cgender_var.get()
    cage = cage_var.get()

    workbook = xlwt.Workbook()

#Naming the sheet in the excel
    sheet = workbook.add_sheet("Contact Details")

    sheet.write(0, 0, 'Contact Details')
    sheet.write(1, 0, 'Contact ID')
    sheet.write(2, 0, 'Name')
    sheet.write(3, 0, 'Address')
    sheet.write(4, 0, 'State')
    sheet.write(5, 0, 'City')
    sheet.write(6, 0, 'Mobile No')
    sheet.write(7, 0, 'Gender')
    sheet.write(8, 0, 'Age')

    sheet.write(1, 1, cid)
    sheet.write(2, 1, cname)
    sheet.write(3, 1, caddr)
    sheet.write(4, 1, cstate)
    sheet.write(5, 1, ccity)
    sheet.write(6, 1, cmobile)
    sheet.write(7, 1, cgender)
    sheet.write(8, 1, cage)
    
#saving the data to excel
    workbook.save("ContactDetails.xls")

    cid_var.set("")
    cname_var.set("")
    caddr_var.set("")
    cstate_var.set("")
    ccity_var.set("")
    cmobile_var.set("")
    cgender_var.set("")
    cage_var.set("")

cid_label = tk.Label(root, text='Contact ID', font=('calibre', 10, 'bold'))
cname_label = tk.Label(root, text='Contact Name', font=('calibre', 10, 'bold'))
caddr_label = tk.Label(root, text='Contact Address', font=('calibre', 10, 'bold'))
cstate_label = tk.Label(root, text='State', font=('calibre', 10, 'bold'))
ccity_label = tk.Label(root, text='City', font=('calibre', 10, 'bold'))
cmobile_label = tk.Label(root, text='Mobile Number', font=('calibre', 10, 'bold'))
cgender_label = tk.Label(root, text='Gender', font=('calibre', 10, 'bold'))
cage_label = tk.Label(root, text='Age', font=('calibre', 10, 'bold'))

cid_entry = tk.Entry(root, textvariable=cid_var, font=('calibre', 10, 'normal'))
cname_entry = tk.Entry(root, textvariable=cname_var, font=('calibre', 10, 'normal'))
caddr_entry = tk.Entry(root, textvariable=caddr_var, font=('calibre', 10, 'normal'))
cstate_entry = tk.Entry(root, textvariable=cstate_var, font=('calibre', 10, 'normal'))
ccity_entry = tk.Entry(root, textvariable=ccity_var, font=('calibre', 10, 'normal'))
cmobile_entry = tk.Entry(root, textvariable=cmobile_var, font=('calibre', 10, 'normal'))
cgender_entry = tk.Entry(root, textvariable=cgender_var, font=('calibre', 10, 'normal'))
cage_entry = tk.Entry(root, textvariable=cage_var, font=('calibre', 10, 'normal'))

#submit button
sub_btn = tk.Button(root, text='Submit', command=submit)

# placing the label and entry in
# the required position using grid
# method
cid_label.grid(row=0, column=0)
cname_label.grid(row=1, column=0)
caddr_label.grid(row=2, column=0)
cstate_label.grid(row=3, column=0)
ccity_label.grid(row=4, column=0)
cmobile_label.grid(row=5, column=0)
cgender_label.grid(row=6, column=0)
cage_label.grid(row=7, column=0)

cid_entry.grid(row=0, column=1)
cname_entry.grid(row=1, column=1)
caddr_entry.grid(row=2, column=1)
cstate_entry.grid(row=3, column=1)
ccity_entry.grid(row=4, column=1)
cmobile_entry.grid(row=5, column=1)
cgender_entry.grid(row=6, column=1)
cage_entry.grid(row=7, column=1)

sub_btn.grid(row=8, column=1)

# performing an infinite loop
# for the window to display
root.mainloop()
Upon running the program, the form will be displayed
Feed the required form details
After clicking submit, a excel will be generated where in all the given form data is stored

How to use GROUP-BY clause in SAP ABAP

The values of a column can be grouped by using the GROUP BY statement. The GROUP BY clause must always come at the end of the ‘SELECT’ statement. It should always be after the ‘FROM’ clause and ‘WHERE’ clause.

Let us consider an example, where we group all the contact based on contact address.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

SELECT contact_name contact_address FROM ztt_db_table2 INTO TABLE lt_contact

GROUP BY contact_name contact_address HAVING contact_address = ‘Street 12’.

The columns mentioned after ‘SELECT’ keyword must match the columns mentioned after ‘GROUP BY’ keyword. In the above query, we have used additional constraint using the ‘HAVING’ keyword.

The results are grouped based on the columns contact_name and contact_address. So these column names are mentioned exactly after the ‘SELECT’ and ‘GROUP BY’ keyword. The additional constraint such as contact_address = ‘Street 12’ through HAVING option will help us to further filter the data records with street address equal to ‘Street 12’.

How to use ORDER BY in SELECT statement in SAP ABAP

The selected records can be sorted using the ‘ORDER BY’ clause. The data can be sorted based on the column mentioned after the ORDER BY clause. It can be sorted in ascending / increasing order or descending / decreasing order. It can be sorted based on more than one column. The priority of the sorting sequence is based on the order in which the column names are mentioned. For every column, it can be mentioned specifically if it needs to be sorted in ascending or descending order. By default, it is sorted in ascending order.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact ORDER BY contact_id ASCENDING.

How to use WHERE condition in SELECT statement in SAP ABAP

The ‘WHERE’ clause in SELECT query can be used to constrain the selected records from the Database table.

The following operators can be used in the ‘WHERE’ clause of SELECT query.

OperatorExplanation
EQ  /  =The data of operand1 must be equal to operand2.
NE   /   < >The data of operand1 must not be equal to operand2.  
LT   /   <The data of operand1 must be lesser than operand2.
GT / >The data of operand1 must be greater than operand2.
LE / <=The data of operand1 must be lesser than or equal to operand2.
GE / >=The data of operand1 must be greater than or equal to operand2.

We can also use AND, OR conditions in the ‘WHERE’ clause.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact WHERE contact_id = 102.

‘AND’ condition :

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact                                        WHERE contact_id = 102 AND contact_name = ‘Tom’.

‘LIKE’ condition:

When we are not sure about the exact value to be used in the WHERE clause, then we can use the LIKE option to narrow down the records.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact

WHERE contact_name LIKE ‘T_’.

The above ‘SELECT’ statement will select all the records with the contact_name starting with the letter ‘T’.

HOW TO USE OPEN-SQL SELECT STATEMENTS IN SAP ABAP

The data from the Database table can be read using SELECT statements. These data records are stored in Dataobjects such as variables, structure and Internal table.

The SELECT statement can be used in the following ways.

  • Reading a single line of record.
  • Reading more lines of record.
  • Reading more lines of record inside a loop.

Reading a single line of record:

              ‘SINGLE’ keyword is used in the select statement to read a single record from the Database table. The first line of record is selected in the Database table.

DATA ls_contact TYPE ztt_db_table2.

SELECT SINGLE * FROM ztt_db_table2 INTO ls_contact.

WRITE:/ ls_contact-contact_id.

Reading more lines of record:

              More than one lines of record can be read from the Database table using the addition ‘INTO’ or ‘APPENDING’. The Addition ‘INTO’ will overwrite the records in the internal table, if it already has some records. The Addition ‘APPENDING’ will not overwrite the records. It will hold the existing records in the internal table. It appends to these existing records. using the ‘SELCT DISTINCT *’, the Duplicate records can be ignored while writing into the Database table.

Using the Addition ‘UP TO’ … ‘ROWS’, the number of records selected can be constrained.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

DATA ls_contact TYPE ztt_db_table2.

SELECT * FROM ztt_db_table2 INTO TABLE lt_contact.

LOOP AT lt_contact INTO ls_contact.

              WRITE:/ ls_contact-contact_id.

ENDLOOP.

APPENDING:

SELECT * FROM ztt_db_table2 APPENDING TABLE lt_contact.

UPTO … ROWS…:

SELECT *FROM ztt_db_table2 INTO TABLE lt_contact UP TO 5 ROWS.

DISTINCT:

SELECT DISTINCT *FROM ztt_db_table2 INTO TABLE lt_contact.

Reading more lines of record inside a loop:

The lines of record can only be stored in a structure or variable. It cannot be stored inside an internal table. The ‘END SELECT’ keyword comes at the end. The line of record are processed between the ‘SELECT’ and ‘ENDSELECT’.

DATA ls_contact TYPE ztt_db_table2.

SELECT * FROM ztt_db_table2 INTO ls_contact.

              WRITE: / ls_contact-contact_id.

ENDSELECT.

The LOOP AT … ASSIGNING is preferred over the ‘SELECT… ENDSELECT’.

How to specify the column names in query.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

The below query can be used before ABAP version 7.40 SP05.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE ztt_db_table2.

After the release of ABAP version 7.40 SP05, the column names can be separated by comma. But the internal table must be specified with @.

SELECT contact_id, contact_name FROM ztt_db_table2 INTO TABLE @ztt_db_table2.

How to specify the target columns in ‘SELECT’ statement:

TYPE: BEGIN OF st_struct,

 contact_name TYPE ztt_db_table2-contact_name,

               contact_address TYPE ztt_db_table2-contact_address

END OF st_struct.

TYPES: tt_contact TYPE TABLE OF st_struct.

DATA pt_contact TYPE tt_contact.

SELECT * FROM ztt_db_table2 INTO CORRESPONDING FIELDS OF TABLE pt_contact.

When the components of the target internal table is different from the components of the database table, then the keyword ‘INTO CORRESPONDING FIELDS OF TABLE’ can be used. Through the `CORRESPONDING FIELDS OF´ keyword, the data records are written to the target internal table. The components of pt_contact are different from ztt_db_table2.

How to explicitly specify the Target columns in SELECT statement:

              The contents of the Database table can also be written to variables. This can be specifically used when the source and target column are different.

DATA lv_contact_id TYPE ztt_db_table2-contact_id.

DATA lv_contact_name TYPE ztt_db_table2-contact_name.

SELECT SINGLE contact_id contact_name FROM ztt_db_table2 INTO (lv_contact_id, lv_contact_name).

Five Important rules to improve the performance of Database access

  1. Output results must be minimum. Please try to use where, having constraints in the query to reduce the data records read from the Database table.
  2. Use SELECT colum1, column2 instead of SELECT *From. The performance will be better as only the important columns are selected in the query.
  3. Use Joins instead of writing multiple select statements. The performance will be better as each select statement is going to be costly.
  4. Try to use Indexes, primary keys in the SELECT query to read data records from the Database table.
  5. Try to use Buffer concept in Database access. So read the records only once and store it in buffer variable. Try to sort the data in the ABAP program instead of using ORDER BY in the SQL query.

5 Popular German Idioms Featuring Schwein 🐷

1.Schwein haben – to get lucky

Gestern hatten wir wirklich Schwein , beinahe hätten wir Unfall gehabt.

We got lucky yesterday. We almost had an accident.

2. Ich glaub mein Schwein pfeift. – Blow me down / Be suprised or shocked about what has been said.

3. Kein Schwein – No one / Nobody

Kein Schwein war da. – No one was there. Literally translates as No pig was there.

4. Wir haben zusammen noch keine Schweine gehütet. – Literally translates as We have not kept pigs together yet. Meaning You don’t know someone well enough to trust them

5. Den inneren Schweinehund überwinden. – Overcome your inner pig-dog

Meaning: If you overcome your inner pig-dogs, you manage to complete a difficult task.

If you are interested in other popular idioms in German please click the link below:

8 German Expressions you must know

What is the difference? – reden vs sprechen vs erzählen vs sagen

  1. reden – to talk

Ich muss mit meinem Lehrer reden, ich habe eine Frage.

I need to talk with my teacher, I have a question.

2. sprechen – to speak

Ich spreche Französisch und Englisch.

I speak French and English

3. erzählen – to narrate / to tell

Meine Mutter erzählte mir immer eine Gutenacht geschichte.

My mother always narrated me bedtime stories.

4. sagen – to say.

Ich sagte auf wiedersehen und verließ das Geschäft.

I said goodbye and left the shop.

Most Frequently used German Words with Two meanings

  1. die Taube vs der Taube

Here the only difference is with the article used. First one die Taube refers to the pigeon and the second one der Taube refers to a deaf person.

2.die Beeren vs die Bären

Here the articles are same but only spelling changes slightly. The pronunciation remains the same. The first one die Beeren is the plural form of die Beere which means berries and the second one die Bären is an animal bear.

3. das Pony vs der Pony

Here also the articles differ. The first one refers to baby horse and the second one refers to fringe hair style.

4. das Tor vs der Tor

Here the articles differ and the first one refers to a goal or a gate. For example ein Tor schießen – to score a goal and the second one der Tor refers to fool.

5. arm vs der Arm

The first one is an adjective and it refers to poor. The second one is a noun and it refers to a part in our body arm.

6. die Leiter vs der Leiter

This one differs in article and the first one refers to a ladder. Second one refers to leader or head.

7. bis vs der Biss

First one is a preposition which means until / upto / till. The second one is a noun which means bite. If you are a fan of twilight series in german version probably you can remember the caption biss zum Morgengrauen which means until dawn or bite at the break of dawn.

8. hast vs hasst.

The first one is second person singular present tense. Its infinitive verb is haben – to have. Second one is 2nd or 3rd person singular and its infinitive verb is hassen – to hate.

9. ist vs isst.

The first one is 3rd person singular and its infinitive verb is sein – to be. Second one is 2nd or 3rd person singular and its infinitive verb is essen – to eat.

10. die Seite vs die Saite.

The first one refers to page or side and the second one refers to string. Both words have the same pronunciation.

How to find the code location to change the column heading of an ALV List

  1. Determine the structure behind the ALV List by right clicking on the ALV List
  2. Determine the Table type for the structure using the Where used option in SAP ABAP
  3. Determin the corresponding Program for the Table type using the Where used option in SAP ABAP (Ctrl+Shift+F3)
  4. Click a particular program in the results list
  5. Click on the Display Object List Icon (Ctrl+Shift+F5)
  6. Click on the corresponding Includes or Function modules or a class in the Tree which appears on the left side of ABAP Editor
  7. Click on the Search glass on top below the menus.
  8. Enter the structure determined in step 1 and click the green check.
  9. Search the Field Catalog in the results list.
  10. Click on the Field Catalog in the Results List.
  11. Change the Column context of the Field Catalog structure.
  12. Save and activate the object