codekingpro/portable-devtools

1import datetime
2
3import adodbapi
4
5try:
6    import adodbapi.is64bit as is64bit
7
8    is64 = is64bit.Python()
9except ImportError:
10    is64 = False  # in case the user has an old version of adodbapi
11if is64:
12    driver = "Microsoft.ACE.OLEDB.12.0"
13else:
14    driver = "Microsoft.Jet.OLEDB.4.0"
15filename = "xx.xls"  # file will be created if it does not exist
16extended = 'Extended Properties="Excel 8.0;Readonly=False;"'
17
18constr = "Provider=%s;Data Source=%s;%s" % (driver, filename, extended)
19
20conn = adodbapi.connect(constr)
21with conn:  # will auto commit if no errors
22    with conn.cursor() as crsr:
23        try:
24            crsr.execute("drop table SheetOne")
25        except:
26            pass  # just is case there is one already there
27
28        # create the sheet and the header row and set the types for the columns
29        crsr.execute(
30            "create table SheetOne (Name varchar, Rank varchar, SrvcNum integer, Weight float,  Birth date)"
31        )
32
33        sql = "INSERT INTO SheetOne (name, rank , srvcnum, weight, birth) values (?,?,?,?,?)"
34
35        data = ("Mike Murphy", "SSG", 123456789, 167.8, datetime.date(1922, 12, 27))
36        crsr.execute(sql, data)  # write the first row of data
37        crsr.execute(
38            sql, ["John Jones", "Pvt", 987654321, 140.0, datetime.date(1921, 7, 4)]
39        )  # another row of data
40conn.close()
41print("Created spreadsheet=%s worksheet=%s" % (filename, "SheetOne"))
42