daily pastebin goal
78%
SHARE
TWEET

mysqldb

wtgeographer Jan 28th, 2017 89 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # Name:        genTable.py
  2. # Purpose:     xlsxwriter experiments
  3. #
  4. # Author:      Noah
  5. #
  6. # Created:     01/19/2017
  7. # Copyright:   (c) Noah 2017
  8. # Licence:     <your licence>
  9. #-------------------------------------------------------------------------------
  10. #   You will need to install these and add to PATH,
  11. #       install xlsxwriter via  pip (ex. pip install xlsxwriter)
  12. #           pip comes with python 2.7.9
  13. #               install MySQLdb via MySQL-python-1.2.3.win-amd64-py2.7.msi
  14. import xlsxwriter
  15. import MySQLdb
  16. import datetime
  17. from string import ascii_lowercase
  18.  
  19.  
  20. # Server connection parameters
  21. #/////////////////////////////////////////////////////////
  22. user = '######' # your username
  23. passwd = '####' # your password
  24. host = '###' # your host
  25. db = '###' # database where your table is stored
  26. table = '###' # table you want to save
  27.  
  28.  
  29. # Connect to db and create cursor
  30. #/////////////////////////////////////////////////////////
  31. con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
  32. cursor = con.cursor()
  33. query = "SELECT DISTINCT column FROM table;"
  34. cursor.execute(query)
  35.  
  36.  
  37.  
  38. # workbook properties...
  39. #/////////////////////////////////////////////////////////
  40.  
  41. workbook = xlsxwriter.Workbook('xxx.xlsx')
  42.  
  43. workbook.set_properties({
  44.     'title':    'FLS Employee Time',
  45.     'subject':  'Employee Time',
  46.     'author':   'Noah Huntington',
  47.     'manager':  '',
  48.     'company':  'GDI, Inc.',
  49.     'category': '',
  50.     'keywords': 'employee, hours',
  51.     'comments': 'Created with Python and XlsxWriter'})
  52.  
  53. # Add the VBA project binary.
  54. #workbook.add_vba_project('vbaProject.bin')
  55.  
  56.  
  57. #//////////////////////////////////////
  58. #format headers
  59. columnHeader = workbook.add_format()
  60. columnHeader.set_align('left')
  61. columnHeader.set_bold()
  62. columnHeader.set_underline()
  63. # Prepare formats.
  64. center = workbook.add_format()
  65. center.set_align('center')
  66. #declare format variables
  67. totals = workbook.add_format()
  68. totals.set_bold()
  69. totals.set_align('left')
  70. #////////////////////////////
  71. ##errors = workbook.add_format()
  72. ##errors.set_pattern(1)  # This is optional when using a solid fill.
  73. ##errors.set_bg_color('red')
  74. ##errors.set_num_format('0')
  75. ##errors.set_align('center')
  76. #//////////////////////////
  77. ##errors_hrs = workbook.add_format()
  78. ##errors_hrs.set_pattern(1)  # This is optional when using a solid fill.
  79. ##errors_hrs.set_bg_color('red')
  80. ##errors_hrs.set_num_format('[h]:mm')
  81. #//////////////////////////
  82. highlight = workbook.add_format()
  83. highlight.set_bg_color('#ff9600')
  84. #//////////////////////////
  85. highlightTime = workbook.add_format()
  86. highlightTime.set_num_format('mm/dd/yy hh:mm:ss')
  87. highlightTime.set_bg_color('#ff9600')
  88. #///////////////////////////
  89. align_left = workbook.add_format({'align': 'left'})
  90. align_right = workbook.add_format({'align': 'right'})
  91. #////////////////////////////
  92. integer = workbook.add_format({'num_format': '0', 'align': 'right'})
  93. text = workbook.add_format({'num_format': '@', 'align': 'right'})
  94. time = workbook.add_format({'num_format': 'hh:mm', 'align': 'right'})
  95. date_time = workbook.add_format({'num_format': 'dd/mm/yy hh:mm:ss', 'align': 'right'})
  96. date = workbook.add_format({'num_format': 'mm/dd/yyyy', 'align': 'right'})
  97. sum_hours = workbook.add_format({'num_format':'[h]:mm', 'align': 'center'})
  98. sum_days = workbook.add_format({'num_format': '0', 'align': 'center'})
  99.  
  100.  
  101. # Create key-value pairs for headers
  102. headerDict = {'A':'id',
  103.                 'B':'work_date',
  104.                 'C':'name',
  105.                 'D':'crew_type',
  106.                 'E':'add_1',
  107.                 'F':'add_2',
  108.                 'G':'jobnumber',
  109.                 'H':'st_time',
  110.                 'I':'end_time',
  111.                 'J':'st_mileage',
  112.                 'K':'end_mileage',
  113.                 'L':'work_type',
  114.                 'M':'gps',
  115.                 'N':'per_diem',
  116.                 'O':'atv_no',
  117.                 'P':'date',
  118.                 'Q':'comments',
  119.                 'R':'sum_time',
  120.                 'S':'sum_mileage',
  121.                 'T':'bill_type'}
  122.  
  123.  
  124. def statistics(job):
  125.         ##########################################################
  126.         #/////////////////////////////////////////////////////////
  127.         # create and populate statistics page per job number
  128.         #/////////////////////////////////////////////////////////
  129.         ##########################################################
  130.  
  131.         #////////////////////////////////////////
  132.         # setup header
  133.         sheet = job[0]+'!'
  134.         last4 = "x"+job[0][-4:]
  135.         worksheet1 = workbook.add_worksheet(last4 +' Totals')
  136.         worksheet1.set_margins(left=.5,right=.5,top=1.3)
  137.         worksheet1.set_tab_color('#b8b4ae')
  138.         worksheet1.protect()
  139.         headerString = '&L&[Picture]&C&20&"Arial"'+job[0]+'&R&[Picture]'
  140.         headerString.center
  141.         worksheet1.set_header(headerString, {'image_left':'C:/Time App/img/xcel_energy.jpg','image_right':'C:/Time App/img/fls_logo.jpg'})
  142.         footerString = '&L&D' + '&R&T'
  143.         worksheet1.set_footer(footerString)
  144.  
  145.         # labels for ROW
  146.         worksheet1.write('A1','Totals:', columnHeader)
  147.         worksheet1.write_string('A2','ROW', align_left)
  148.  
  149.         # headers
  150.         worksheet1.write('B3','Hours:', columnHeader)
  151.         worksheet1.write('D3','Per Diem:', columnHeader)
  152.         worksheet1.write('F3','Other:', columnHeader)
  153.  
  154.         # labels for totals
  155.         worksheet1.write('A20','Totals:', columnHeader)
  156.         worksheet1.write_string('A21','TLINE', align_left)
  157.  
  158.         # headers
  159.         worksheet1.write('B22','Hours:', columnHeader)
  160.         worksheet1.write('D22','Per Diem:', columnHeader)
  161.         worksheet1.write('F22','Other:', columnHeader)
  162.  
  163.         # Page setup
  164.         #//////////////////////////////////////////////
  165.         worksheet1.set_portrait()
  166.         worksheet1.set_page_view()
  167.         worksheet1.set_paper(1)  # US Letter
  168.         worksheet1.center_horizontally()
  169.  
  170.         # Set the column width.
  171.         #///////////////////////////////////////////////
  172.         worksheet1.set_column('A:A', 8)
  173.         worksheet1.set_column('B:B', 18)
  174.         worksheet1.set_column('C:C', 10)
  175.         worksheet1.set_column('D:D', 15)
  176.         worksheet1.set_column('E:E', 10)#, center)
  177.         worksheet1.set_column('F:F', 18)
  178.         worksheet1.set_column('G:G', 10)#,center)
  179.  
  180.         #////////////////////////////////////////////////////////////////
  181.         # Setup ROW table
  182.         #////////////////////////////////////////////////////////////////
  183.  
  184.         #Column Hours
  185.         #////////////////////////////////////////////////
  186.  
  187.         # 1 Man Hours
  188.         worksheet1.write('B4','1 Man Hrs:', align_left)
  189.         worksheet1.write('C4','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "1man"),--('+sheet+'T6:T1000 = "ROW"))', sum_hours)
  190.  
  191.         #2 Man Hours
  192.         worksheet1.write('B5','2 Man Hrs:', align_left)
  193.         worksheet1.write('C5','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "2man"),--('+sheet+'T6:T1000 = "ROW"))*2', sum_hours)
  194.  
  195.         #3 Man Hours
  196.         worksheet1.write('B6','3 Man Hrs:', align_left)
  197.         worksheet1.write('C6','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "3man"),--('+sheet+'T6:T1000 = "ROW"))*3', sum_hours)
  198.  
  199.         #admin
  200.         worksheet1.write('B7','Admin:', align_left)
  201.         worksheet1.write('C7','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "admin"),--('+sheet+'T6:T1000 = "ROW"))', sum_hours)
  202.  
  203.         #coordinator
  204.         worksheet1.write('B8','Field  Coord. Hrs:', align_left)
  205.         worksheet1.write('C8','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "coordinator"),--('+sheet+'T6:T1000 = "ROW"))', sum_hours)
  206.  
  207.         #research
  208.         worksheet1.write('B9','Research:', align_left)
  209.         worksheet1.write('C9','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "research"),--('+sheet+'T6:T1000 = "ROW"))', sum_hours)
  210.  
  211.         #sit
  212.         worksheet1.write('B10','SIT:', align_left)
  213.         worksheet1.write('C10','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "sit"),--('+sheet+'T6:T1000 = "ROW"))', sum_hours)
  214.  
  215.         ...
  216.  
  217.         #Totals
  218.         worksheet1.write('B17','Total Hours:', totals)
  219.         worksheet1.write('C17','=sum(C4:C15)', sum_hours)
  220.  
  221.  
  222.         #Column Per Diem
  223.         #////////////////////////////////////////////////////
  224.  
  225.         #1 Man Per Diem
  226.         worksheet1.write('D4','1 Man Per Diem:', align_left)
  227.         worksheet1.write('E4','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "1man"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  228.  
  229.         #2 Man Per Diem
  230.         worksheet1.write('D5','2 Man Per Diem:', align_left)
  231.         worksheet1.write('E5','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "2man"),--('+sheet+'T6:T1000 = "ROW"))*2', sum_days)
  232.  
  233.         #3 Man Per Diem
  234.         worksheet1.write('D6','3 Man Per Diem:', align_left)
  235.         worksheet1.write('E6','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "3man"),--('+sheet+'T6:T1000 = "ROW"))*3', sum_days)
  236.  
  237.         #admin Per Diem
  238.         worksheet1.write('D7','Admin:', align_left)
  239.         worksheet1.write('E7','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "admin"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  240.  
  241.         #columnHeader coordinator Per Diem
  242.         worksheet1.write('D8','Field Coord.:', align_left)
  243.         worksheet1.write('E8','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "coordinator"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  244.  
  245.         #Research Per Diem
  246.         worksheet1.write('D9','Research:', align_left)
  247.         worksheet1.write('E9','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "research"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  248.  
  249.         #SIT Per Diem
  250.         worksheet1.write('D10','SIT:', align_left)
  251.         worksheet1.write('E10','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "sit"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  252.  
  253.         #RPLS Per Diem
  254.         worksheet1.write('D11','RPLS:', align_left)
  255.         worksheet1.write('E11','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "rpls"),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  256.  
  257.         #Total Per Diem
  258.         worksheet1.write('D13','Total Days:', totals)
  259.         worksheet1.write('E13','=SUM(E4:E11)', sum_days)
  260.  
  261.  
  262.         # Column Other
  263.         #////////////////////////////////////////////////////////////////
  264.  
  265.         #days  GPS
  266.         worksheet1.write('F4','Days GPS:', align_left)
  267.         worksheet1.write('G4','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'M6,ROW('+sheet+'M6:M1000)-ROW('+sheet+'M6),,1)),--('+sheet+'M6:M1000 =  1),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  268.  
  269.         # days atv
  270.         worksheet1.write('F5','Days ATV:', align_left)
  271.         worksheet1.write('G5','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'O6,ROW('+sheet+'O6:O1000)-ROW('+sheet+'O6),,1)),--('+sheet+'O6:O1000 = 1),--('+sheet+'T6:T1000 = "ROW"))', sum_days)
  272.  
  273.         #mileage
  274.         worksheet1.write('F6','Mileage:', align_left)
  275.         worksheet1.write('G6','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'S2,ROW('+sheet+'S6:S1000)-ROW('+sheet+'S2)--('+sheet+'S6:S1000>0)--('+sheet+'T6:T1000="ROW"),,1)))', sum_days)
  276.  
  277.  
  278.  
  279.         #################################################################
  280.         #////////////////////////////////////////////////////////////////
  281.         # Setup TLINE table
  282.         #////////////////////////////////////////////////////////////////
  283.         #################################################################
  284.  
  285.         #////////////////////////////////////////////////
  286.         #Column Hours
  287.  
  288.         # 1 Man Hours
  289.         worksheet1.write('B23','1 Man Hrs:', align_left)
  290.         worksheet1.write('C23','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "1man"),--('+sheet+'T6:T1000 = "TLINE"))', sum_hours)
  291.  
  292.         #2 Man Hours
  293.         worksheet1.write('B24','2 Man Hrs:', align_left)
  294.         worksheet1.write('C24','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "2man"),--('+sheet+'T6:T1000 = "TLINE"))*2', sum_hours)
  295.  
  296.         #3 Man Hours
  297.         worksheet1.write('B25','3 Man Hrs:', align_left)
  298.         worksheet1.write('C25','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "3man"),--('+sheet+'T6:T1000 = "TLINE"))*3', sum_hours)
  299.  
  300.         #admin
  301.         worksheet1.write('B26','Admin:', align_left)
  302.         worksheet1.write('C26','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "admin"),--('+sheet+'T6:T1000 = "TLINE"))', sum_hours)
  303.  
  304.         #hours columnHeader coordinator
  305.         worksheet1.write('B27','Field  Coord. Hrs:', align_left)
  306.         worksheet1.write('C27','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "coordinator"),--('+sheet+'T6:T1000 = "TLINE"))', sum_hours)
  307.  
  308.         #research
  309.         worksheet1.write('B28','Research:', align_left)
  310.         worksheet1.write('C28','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "reserach"),--('+sheet+'T6:T1000 = "TLINE"))', sum_hours)
  311.  
  312.         #sit
  313.         worksheet1.write('B29','SIT:', align_left)
  314.         worksheet1.write('C29','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "sit"),--('+sheet+'T6:T1000 = "TLINE"))', sum_hours)
  315.        
  316.         ...
  317.  
  318.         #Totals
  319.         worksheet1.write('B36','Total Hours:', totals)
  320.         worksheet1.write('C36','=sum(C23:C35)', sum_hours)
  321.  
  322.  
  323.         #////////////////////////////////////////////////////
  324.         #Column Per Diem
  325.  
  326.         #1 Man Per Diem
  327.         worksheet1.write('D23','1 Man Per Diem:', align_left)
  328.         worksheet1.write('E23','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "1man"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  329.  
  330.         #2 Man Per Diem
  331.         worksheet1.write('D24','2 Man Per Diem:', align_left)
  332.         worksheet1.write('E24','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "2man"),--('+sheet+'T6:T1000 = "TLINE"))*2', sum_days)
  333.  
  334.         #3 Man Per Diem
  335.         worksheet1.write('D25','3 Man Per Diem:', align_left)
  336.         worksheet1.write('E25','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "3man"),--('+sheet+'T6:T1000 = "TLINE"))*3', sum_days)
  337.  
  338.         #admin Per Diem
  339.         worksheet1.write('D26','Admin:', align_left)
  340.         worksheet1.write('E26','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "admin"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  341.  
  342.         #columnHeader coordinator Per Diem
  343.         worksheet1.write('D27','Field Coord.:', align_left)
  344.         worksheet1.write('E27','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "coordinator"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  345.  
  346.         #Research Per Diem
  347.         worksheet1.write('D28','Research:', align_left)
  348.         worksheet1.write('E28','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "research"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  349.  
  350.         #SIT Per Diem
  351.         worksheet1.write('D29','SIT:', align_left)
  352.         worksheet1.write('E29','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "sit"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  353.  
  354.         #RPLS Per Diem
  355.         worksheet1.write('D30','RPLS:', align_left)
  356.         worksheet1.write('E30','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'R6,ROW('+sheet+'R6:R1000)-ROW('+sheet+'R6),,1)),--('+sheet+'D6:D1000 = "rpls"),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  357.  
  358.         #Total Per Diem
  359.         worksheet1.write('D32','Total Days:', totals)
  360.         worksheet1.write('E32','=SUM(E23:E29)', sum_days)
  361.  
  362.  
  363.         #///////////////////////////////////////////////////////
  364.         # Column Other
  365.  
  366.         #days  GPS
  367.         worksheet1.write('F23','Days GPS:', align_left)
  368.         worksheet1.write('G23','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'M6,ROW('+sheet+'M6:M1000)-ROW('+sheet+'M6),,1)),--('+sheet+'M6:M1000 =  1),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  369.  
  370.         # days atv
  371.         worksheet1.write('F24','Days ATV:', align_left)
  372.         worksheet1.write('G24','=SUMPRODUCT(SUBTOTAL(2,OFFSET('+sheet+'O6,ROW('+sheet+'O6:O1000)-ROW('+sheet+'O6),,1)),--('+sheet+'O6:O1000 = 0),--('+sheet+'T6:T1000 = "TLINE"))', sum_days)
  373.  
  374.         #mileage
  375.         worksheet1.write('F25','Mileage:', align_left)
  376.         worksheet1.write('G25','=SUMPRODUCT(SUBTOTAL(109,OFFSET('+sheet+'S2,ROW('+sheet+'S6:S1000)-ROW('+sheet+'S2)--('+sheet+'S6:S1000>0)--('+sheet+'T6:T1000="TLINE"),,1)))', sum_days)
  377.  
  378.  
  379. def records(job):
  380.  
  381.         # create and populate records page per job number
  382.         #/////////////////////////////////////////////////
  383.  
  384.         worksheet2 = workbook.add_worksheet(job[0])
  385.  
  386.         # Set tab colors
  387.         worksheet2.set_tab_color('#ff9600')
  388.  
  389.  
  390.         # autofilters
  391.         #
  392.         worksheet2.autofilter('A5:T1000')
  393.  
  394.         # header/instructions
  395.         timeStamp = datetime.datetime.now()
  396.         timeMessage = 'Current as of: '
  397.         worksheet2.write('A1',timeMessage, highlight)
  398.         worksheet2.write('B1',timeStamp, highlightTime)
  399.  
  400.  
  401.         # Set the column width.
  402.         worksheet2.set_column('A:A', 12)
  403.         worksheet2.set_column('B:B', 20)
  404.         worksheet2.set_column('C:C', 18, columnHeader)
  405.         worksheet2.set_column('D:D', 15)
  406.         worksheet2.set_column('E:E', 15)
  407.         worksheet2.set_column('F:F', 15)
  408.         worksheet2.set_column('G:G', 10)
  409.         worksheet2.set_column('H:H', 6)
  410.         worksheet2.set_column('I:I', 6)
  411.         worksheet2.set_column('J:J', 8)
  412.         worksheet2.set_column('K:K', 6)
  413.         worksheet2.set_column('L:L', 12)
  414.         worksheet2.set_column('M:M', 6)
  415.         worksheet2.set_column('N:N', 6)
  416.         worksheet2.set_column('O:O', 6)
  417.         worksheet2.set_column('P:P', 18)
  418.  
  419.         # write headers to cells
  420.         for key, value in headerDict.items():
  421.                 worksheet2.write(key + str(5), value, columnHeader)
  422.  
  423.         #query job number
  424.         sql = "SELECT * FROM table WHERE column = '%s'" % (job)
  425.  
  426.         try:
  427.          cursor.execute(sql)
  428.          results = cursor.fetchall()
  429.  
  430.          row = 6;
  431.          for item in results:
  432.             worksheet2.write('A' + str(row), item[0], integer)
  433.             worksheet2.write('B' + str(row), item[1], date)
  434.             worksheet2.write('C' + str(row), item[2], text)
  435.             worksheet2.write('D' + str(row), item[3], text)
  436.             worksheet2.write('E' + str(row), item[4], text)
  437.             worksheet2.write('F' + str(row), item[5], text)
  438.             worksheet2.write('G' + str(row), item[6], text)
  439.             worksheet2.write('H' + str(row), item[7], time)
  440.             worksheet2.write('I' + str(row), item[8], time)
  441.             worksheet2.write('J' + str(row), item[9], integer)
  442.             worksheet2.write('K' + str(row), item[10], integer)
  443.             worksheet2.write('L' + str(row), item[11], text)
  444.             worksheet2.write('M' + str(row), item[12], integer)
  445.             worksheet2.write('N' + str(row), item[13], integer)
  446.             worksheet2.write('O' + str(row), item[14], integer)
  447.             worksheet2.write('P' + str(row), item[15], date_time)
  448.             worksheet2.write('Q' + str(row), item[16], text)
  449.             worksheet2.write('R' + str(row), "=I:I-H:H", time)
  450.             worksheet2.write('S' + str(row), "=K:K-J:J", integer)
  451.             worksheet2.write('T' + str(row),'=IF(OR(L6:L1000="Boundary", L6:L1000="Research"), "ROW","TLINE")',text)
  452.             row += 1
  453.  
  454.         except:
  455.            print "Error: unable to fetch data"
  456.  
  457. def main():
  458.  
  459.     for job in cursor.fetchall():
  460.         statistics(job)
  461.         records(job)
  462.  
  463.     workbook.close()
  464.     con.close()
  465.  
  466. if __name__ == '__main__':
  467.     main()
RAW Paste Data
Top