API tools faq
paste
Advertisement
wtgeographer

mysqldb

wtgeographer
Jan 28th, 2017
393
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 20.87 KB | None | 0 0
raw download clone embed print report
  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()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!