def __create_borders():
    border = Border()
    border.border_style = Border.BORDER_THIN

    b = {}

    top_borders = Borders()
    top_borders.left = border
    top_borders.right= border
    top_borders.top = border

    bottom_borders = Borders()
    bottom_borders.bottom=border
    bottom_borders.left=border
    bottom_borders.right=border

    side_borders =  Borders()
    side_borders.right = border
    side_borders.left = border

    borders  = Borders()
    borders.left = border
    borders.top=border
    borders.right=border
    borders.bottom=border


    b['full'] = borders
    b['top'] = top_borders
    b['bottom'] = bottom_borders
    b['side'] = side_borders
    return b
Пример #2
0
  def FlgCell(self,wsRow,wsCol):
    pyws = self.pyws
    c = pyws.cell(row=wsRow,column=wsCol)

    red = openpyxl.styles.colors.RED
    side   = Side(style='medium',color=red)
    border = Border(left=side,right=side,top=side,bottom=side)
    c.border = border.copy()
Пример #3
0
def SetCell(ws,wsRow,wsCol,val,fmt):

  align  = None
  fill   = None
  numFmt = None
  border = None

  c = ws.cell(row=wsRow,column=wsCol)

  for i in fmt:
    #logging.debug(i)

    if (i == 'hAlign'): 
      if (not align): align = Alignment()
      align.horizontal = alignType[fmt[i]]

    if (i == 'vAlign'): 
      if (not align): align = Alignment()
      align.vertical   = alignType[fmt[i]]

    if (i == 'wrap'):
      if (not align): align = Alignment()
      if (fmt[i] == '1'):
        align.wrap_text = 1
      else:
        align.wrap_text = 0

    if (i == 'border'): 
      if (not align): align = Alignment()
      side = Side(style='thin')
      border = Border(left=side,right=side,top=side,bottom=side)

    if (i == 'fill'): 
      color = ColorTable[fmt[i]]
      fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid')

    if (i == 'orient'): 
      pass

    if (i == 'bg'):
      fill = PatternFill(start_color='FFEE1111',end_color='FFEE1111',fill_type='solid')

    if (i == 'numFmt'):
      numFmt = fmt[i]

  if (align):
    c.alignment = align.copy()

  if (border):
    c.border = border.copy()

  if (fill):
    c.fill = fill.copy()

  if (numFmt):
    c.number_format = numFmt

  c.value = val
Пример #4
0
def FlgCell(ws,wsRow,wsCol):

  c = ws.cell(row=wsRow,column=wsCol)

  red = openpyxl.styles.colors.RED
  side   = Side(style='medium',color=red)
  border = Border(left=side,right=side,top=side,bottom=side)
  #style  = Style(border=border)
  #cell.style = style
  c.border = border.copy()
Пример #5
0
def set_allborder(ws, cell_range):
    rows = list(ws.iter_rows(cell_range))
    side = Side(border_style='thin', color="FF000000")
    for pos_y, cells in enumerate(rows):
        for pos_x, cell in enumerate(cells):
            border = Border(
                left=cell.border.left,
                right=cell.border.right,
                top=cell.border.top,
                bottom=cell.border.bottom
            )
            border.left = side
            border.right = side
            border.top = side
            border.bottom = side
            cell.border = border
Пример #6
0
def compile_info(plot_objects, field=None):
    """
    :plot_objects: List containing selected ObsPlot objects
    :return: .xlsx containing a colored field map of the passed plots.
    """
    wb = Workbook()
    if field:
        field_set = [field]
    else:
        field_set = get_field_object_set(plot_objects)
    for field in field_set:
        field_plots = get_plots_in_field(plot_objects, field)
        worksheet = wb.create_sheet()
        worksheet.title = field.field_name
        experiment_current = field_plots[0].experiment
        experiment_colors = iter(COLOR_SET)
        cell_color = experiment_colors.next()

        for plot in field_plots:
            if plot.experiment != experiment_current:
                experiment_current = plot.experiment
                # Cycles through three experiment colors so the spreadsheet doesn't look incredibly dull
                try:
                    cell_color = experiment_colors.next()
                except StopIteration:
                    experiment_colors = iter(COLOR_SET)
                    cell_color = experiment_colors.next()

            cell_fill = PatternFill(start_color=cell_color, end_color=cell_color, fill_type="solid")
            side = Side(border_style="thin", color="003300")
            for coordinate in plot.get_coordinates():
                worksheet[coordinate] = plot.plot_id
                worksheet[coordinate].fill = cell_fill
                border = Border(bottom=worksheet[coordinate].border.bottom)
                if check_row_even(coordinate):  # Slips in a border every 2 rows
                    border.bottom = side
                    worksheet[coordinate].border = border

        domain = get_plot_domains(field_plots)

        add_axes(worksheet, domain, field_plots)

    wb.remove_sheet(wb.get_sheet_by_name("Sheet"))

    return wb
Пример #7
0
  def SetCellFmt(self,cell,hAlign,vAlign,fmt,value):
    if (align == 'C'):
      align = Alignment(horizontal='center',vertical='center')
    elif (align == 'L'):
      align = Alignment(horizontal='left',vertical='center')
    elif (align == 'R'):
      align = Alignment(horizontal='right',vertical='center')
    else:
      align = Alignment(horizontal='right',vertical='center')

    side   = Side(style='thin')
    border = Border(left=side,right=side,top=side,bottom=side)

    if (fmt == 'F'):
      fmt = '0.00'
      cell.number_format = fmt
    cell.alignment     = align.copy()
    cell.border        = border.copy()
    cell.value = value
Пример #8
0
def set_border(ws, cell_range):
    rows = list(ws.iter_rows(cell_range))
    side = Side(border_style='thin', color="FF000000")
    max_y = len(rows) - 1  # index of the last row
    for pos_y, cells in enumerate(rows):
        max_x = len(cells) - 1  # index of the last cell
        for pos_x, cell in enumerate(cells):
            border = Border(
                left=cell.border.left,
                right=cell.border.right,
                top=cell.border.top,
                bottom=cell.border.bottom
            )
            if pos_x == 0:
                border.left = side
            if pos_x == max_x:
                border.right = side
            if pos_y == 0:
                border.top = side
            if pos_y == max_y:
                border.bottom = side
            # set new border only if it's one of the edge cells
            if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
                cell.border = border
Пример #9
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):

    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102

    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    # img = Image("myems.png")
    img.width = img.width * 1.06
    img.height = img.height * 1.06
    ws.add_image(img, 'B1')

    # Title
    ws.row_dimensions[3].height = 60

    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local[:
                                              10] + "__" + reporting_end_datetime_local[:
                                                                                        10]
    ws.merge_cells("G3:H3")

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename
    #################################################
    # First: 能耗分析
    # 6: title
    # 7: table title
    # 8~10 table_data
    # Total: 5 rows
    # if has not energy data: set low height for rows
    #################################################
    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True
    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_energy_data_flag = False

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name + ' 能耗分析'

        category = reporting_period_data['names']
        ca_len = len(category)

        ws.row_dimensions[7].height = 60
        ws['B7'].fill = table_fill
        ws['B7'].border = f_border

        ws['B8'].font = title_font
        ws['B8'].alignment = c_c_alignment
        ws['B8'] = '能耗'
        ws['B8'].border = f_border

        ws['B9'].font = title_font
        ws['B9'].alignment = c_c_alignment
        ws['B9'] = '环比'
        ws['B9'].border = f_border

        col = ''

        for i in range(0, ca_len):
            col = chr(ord('C') + i)
            row = '7'
            cell = col + row
            ws[col + '7'].fill = table_fill
            ws[col + '7'].font = name_font
            ws[col + '7'].alignment = c_c_alignment
            ws[col + '7'] = reporting_period_data['names'][
                i] + " (" + reporting_period_data['units'][i] + ")"
            ws[col + '7'].border = f_border

            ws[col + '8'].font = name_font
            ws[col + '8'].alignment = c_c_alignment
            ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
            ws[col + '8'].border = f_border

            ws[col + '9'].font = name_font
            ws[col + '9'].alignment = c_c_alignment
            ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
                if reporting_period_data['increment_rates'][i] is not None else "-"
            ws[col + '9'].border = f_border

        # TCE TCO2E
        end_col = col
        # TCE
        tce_col = chr(ord(end_col) + 1)
        ws[tce_col + '7'].fill = table_fill
        ws[tce_col + '7'].font = name_font
        ws[tce_col + '7'].alignment = c_c_alignment
        ws[tce_col + '7'] = "吨标准煤 (TCE)"
        ws[tce_col + '7'].border = f_border

        ws[tce_col + '8'].font = name_font
        ws[tce_col + '8'].alignment = c_c_alignment
        ws[tce_col + '8'] = round(
            reporting_period_data['total_in_kgce'] / 1000, 2)
        ws[tce_col + '8'].border = f_border

        ws[tce_col + '9'].font = name_font
        ws[tce_col + '9'].alignment = c_c_alignment
        ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
            if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
        ws[tce_col + '9'].border = f_border

        # TCO2E
        tco2e_col = chr(ord(end_col) + 2)
        ws[tco2e_col + '7'].fill = table_fill
        ws[tco2e_col + '7'].font = name_font
        ws[tco2e_col + '7'].alignment = c_c_alignment
        ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
        ws[tco2e_col + '7'].border = f_border

        ws[tco2e_col + '8'].font = name_font
        ws[tco2e_col + '8'].alignment = c_c_alignment
        ws[tco2e_col + '8'] = round(
            reporting_period_data['total_in_kgco2e'] / 1000, 2)
        ws[tco2e_col + '8'].border = f_border

        ws[tco2e_col + '9'].font = name_font
        ws[tco2e_col + '9'].alignment = c_c_alignment
        ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
            if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
        ws[tco2e_col + '9'].border = f_border
    else:
        for i in range(6, 9 + 1):
            ws.row_dimensions[i].height = 0.1
    #################################################
    # Second: 分时电耗
    # 12: title
    # 13: table title
    # 14~17 table_data
    # Total: 6 rows
    ################################################
    has_ele_peak_flag = True
    if "toppeaks" not in reporting_period_data.keys() or \
            reporting_period_data['toppeaks'] is None or \
            len(reporting_period_data['toppeaks']) == 0:
        has_ele_peak_flag = False

    if has_ele_peak_flag:
        ws['B12'].font = title_font
        ws['B12'] = name + ' 分时电耗'

        ws.row_dimensions[13].height = 60
        ws['B13'].fill = table_fill
        ws['B13'].font = name_font
        ws['B13'].alignment = c_c_alignment
        ws['B13'].border = f_border

        ws['C13'].fill = table_fill
        ws['C13'].font = name_font
        ws['C13'].alignment = c_c_alignment
        ws['C13'].border = f_border
        ws['C13'] = '分时电耗'

        ws['B14'].font = title_font
        ws['B14'].alignment = c_c_alignment
        ws['B14'] = '尖'
        ws['B14'].border = f_border

        ws['C14'].font = title_font
        ws['C14'].alignment = c_c_alignment
        ws['C14'].border = f_border
        ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)

        ws['B15'].font = title_font
        ws['B15'].alignment = c_c_alignment
        ws['B15'] = '峰'
        ws['B15'].border = f_border

        ws['C15'].font = title_font
        ws['C15'].alignment = c_c_alignment
        ws['C15'].border = f_border
        ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)

        ws['B16'].font = title_font
        ws['B16'].alignment = c_c_alignment
        ws['B16'] = '平'
        ws['B16'].border = f_border

        ws['C16'].font = title_font
        ws['C16'].alignment = c_c_alignment
        ws['C16'].border = f_border
        ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)

        ws['B17'].font = title_font
        ws['B17'].alignment = c_c_alignment
        ws['B17'] = '谷'
        ws['B17'].border = f_border

        ws['C17'].font = title_font
        ws['C17'].alignment = c_c_alignment
        ws['C17'].border = f_border
        ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)

        pie = PieChart()
        pie.title = name + ' 分时电耗'
        labels = Reference(ws, min_col=2, min_row=14, max_row=17)
        pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 7.25  # cm 1.05*5 1.05cm = 30 pt
        pie.width = 9
        # pie.title = "Pies sold by category"
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False  # 标签显示
        s1.dLbls.showVal = True  # 数量显示
        s1.dLbls.showPercent = True  # 百分比显示
        # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100

        ws.add_chart(pie, "D13")

    else:
        for i in range(12, 18 + 1):
            ws.row_dimensions[i].height = 0.1
        # end_row 10
        # start_row 12
    ################################################
    # Third: 子空间能耗
    # 19: title
    # 20: table title
    # 21~24 table_data
    # Total: 6 rows
    ################################################
    has_child_flag = True
    # Judge if the space has child space, if not, delete it.
    if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
            len(report['child_space']["energy_category_names"]) == 0 \
            or 'child_space_names_array' not in report['child_space'].keys() \
            or report['child_space']['energy_category_names'] is None \
            or len(report['child_space']['child_space_names_array']) == 0 \
            or len(report['child_space']['child_space_nchild_space_names_arrayames_array'][0]) == 0:

        has_child_flag = False

    current_row_number = 19

    if has_child_flag:
        child = report['child_space']
        child_spaces = child['child_space_names_array'][0]
        child_subtotals = child['subtotals_array'][0]

        ws['B19'].font = title_font
        ws['B19'] = name + ' 子空间能耗'

        ws.row_dimensions[20].height = 60
        ws['B20'].fill = table_fill
        ws['B20'].border = f_border
        ca_len = len(child['energy_category_names'])

        table_start_row_number = 20

        for i in range(0, ca_len):
            row = chr(ord('C') + i)
            ws[row + '20'].fill = table_fill
            ws[row + '20'].font = title_font
            ws[row + '20'].alignment = c_c_alignment
            ws[row + '20'].border = f_border
            ws[row + '20'] = child['energy_category_names'][i] + ' (' + child[
                'units'][i] + ')'

        space_len = len(child['child_space_names_array'][0])

        for i in range(0, space_len):
            row = str(i + 21)

            ws['B' + row].font = name_font
            ws['B' + row].alignment = c_c_alignment
            ws['B' + row] = child['child_space_names_array'][0][i]
            ws['B' + row].border = f_border

            for j in range(0, ca_len):
                col = chr(ord('C') + j)
                ws[col + row].font = name_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = round(child['subtotals_array'][j][i], 2)
                ws[col + row].border = f_border

        table_end_row_number = 20 + space_len
        chart_start_row_number = 20 + space_len + 1

        for i in range(0, ca_len):
            # pie
            # 25~30: pie
            pie = PieChart()
            pie.title = ws.cell(column=3 + i, row=table_start_row_number).value
            labels = Reference(ws,
                               min_col=2,
                               min_row=table_start_row_number + 1,
                               max_row=table_end_row_number)
            pie_data = Reference(ws,
                                 min_col=3 + i,
                                 min_row=table_start_row_number,
                                 max_row=table_end_row_number)
            pie.add_data(pie_data, titles_from_data=True)
            pie.set_categories(labels)
            pie.height = 6.6  # cm 1.05*5 1.05cm = 30 pt
            pie.width = 8
            # pie.title = "Pies sold by category"
            s1 = pie.series[0]
            s1.dLbls = DataLabelList()
            s1.dLbls.showCatName = False  # 标签显示
            s1.dLbls.showVal = True  # 数量显示
            s1.dLbls.showPercent = True  # 百分比显示
            # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100
            chart_cell = ''
            if i % 2 == 0:
                chart_cell = 'B' + str(chart_start_row_number)
            else:
                chart_cell = 'E' + str(chart_start_row_number)
                chart_start_row_number += 5
            # ws.add_chart(pie, chart_cell)
            # chart_col = chr(ord('B') + 2 * j)
            # chart_cell = chart_col + '25'
            ws.add_chart(pie, chart_cell)

        current_row_number = chart_start_row_number

        if ca_len % 2 == 1:
            current_row_number += 5

        current_row_number += 1

    ################################################
    # Fourth: 能耗详情
    # current_row_number: title
    # current_row_number+1 ~ current_row_number+1+ca_len*6-1: line
    # current_row_number+1+ca_len*6: table title
    # current_row_number+1+ca_len*6~: table_data
    ################################################
    reporting_period_data = report['reporting_period']
    times = reporting_period_data['timestamps']
    has_detail_data_flag = True
    ca_len = len(report['reporting_period']['names'])
    real_timestamps_len = timestamps_data_not_equal_0(
        report['parameters']['timestamps'])
    table_row = current_row_number + ca_len * 6 + real_timestamps_len * 7 + 2
    chart_start_row_number = current_row_number + 1
    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_detail_data_flag = False

    if has_detail_data_flag:
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 详细数据'

        ws.row_dimensions[table_row].height = 60
        ws['B' + str(table_row)].fill = table_fill
        ws['B' + str(table_row)].font = title_font
        ws['B' + str(table_row)].border = f_border
        ws['B' + str(table_row)].alignment = c_c_alignment
        ws['B' + str(table_row)] = '日期时间'
        time = times[0]
        has_data = False
        max_row = 0
        if len(time) > 0:
            has_data = True
            max_row = table_row + len(time)
            print("max_row", max_row)

        if has_data:
            for i in range(0, len(time)):
                col = 'B'
                row = str(table_row + 1 + i)
                # col = chr(ord('B') + i)
                ws[col + row].font = title_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = time[i]
                ws[col + row].border = f_border

            for i in range(0, ca_len):
                # 38 title
                col = chr(ord('C') + i)

                ws[col + str(table_row)].fill = table_fill
                ws[col + str(table_row)].font = title_font
                ws[col + str(table_row)].alignment = c_c_alignment
                ws[col + str(table_row)] = reporting_period_data['names'][i] + \
                    " (" + reporting_period_data['units'][i] + ")"
                ws[col + str(table_row)].border = f_border

                # 39 data
                time = times[i]
                time_len = len(time)

                for j in range(0, time_len):
                    row = str(table_row + 1 + j)
                    # col = chr(ord('B') + i)
                    ws[col + row].font = title_font
                    ws[col + row].alignment = c_c_alignment
                    ws[col + row] = round(
                        reporting_period_data['values'][i][j], 2)
                    ws[col + row].border = f_border

            current_row_number = table_row + 1 + len(times[0])

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '小计'

            for i in range(0, ca_len):
                col = chr(ord('C') + i)
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)].border = f_border
                ws[col + str(current_row_number)] = round(
                    reporting_period_data['subtotals'][i], 2)

                # line
                # 39~: line
                line = LineChart()
                line.title = '报告期消耗 - ' + ws.cell(column=3 + i,
                                                  row=table_row).value
                labels = Reference(ws,
                                   min_col=2,
                                   min_row=table_row + 1,
                                   max_row=max_row)
                line_data = Reference(ws,
                                      min_col=3 + i,
                                      min_row=table_row,
                                      max_row=max_row)  # openpyxl bug
                line.add_data(line_data, titles_from_data=True)
                line.set_categories(labels)
                line_data = line.series[0]
                line_data.marker.symbol = "circle"
                line_data.smooth = True
                line.x_axis.crosses = 'min'
                line.height = 8.25  # cm 1.05*5 1.05cm = 30 pt
                line.width = 24
                # pie.title = "Pies sold by category"
                line.dLbls = DataLabelList()
                line.dLbls.dLblPos = 't'
                # line.dLbls.showCatName = True  # label show
                line.dLbls.showVal = True  # val show
                line.dLbls.showPercent = True  # percent show
                # s1 = CharacterProperties(sz=1800)     # font size *100
                chart_col = 'B'
                chart_cell = chart_col + str(chart_start_row_number + 6 * i)
                ws.add_chart(line, chart_cell)

    current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6
    ##########################################
    has_parameters_names_and_timestamps_and_values_data = True
    if 'parameters' not in report.keys() or \
            report['parameters'] is None or \
            'names' not in report['parameters'].keys() or \
            report['parameters']['names'] is None or \
            len(report['parameters']['names']) == 0 or \
            'timestamps' not in report['parameters'].keys() or \
            report['parameters']['timestamps'] is None or \
            len(report['parameters']['timestamps']) == 0 or \
            'values' not in report['parameters'].keys() or \
            report['parameters']['values'] is None or \
            len(report['parameters']['values']) == 0 or \
            timestamps_data_all_equal_0(report['parameters']['timestamps']):

        has_parameters_names_and_timestamps_and_values_data = False
    if has_parameters_names_and_timestamps_and_values_data:

        ###############################
        # new worksheet
        ###############################

        parameters_data = report['parameters']
        parameters_names_len = len(parameters_data['names'])

        parameters_ws = wb.create_sheet('相关参数')

        parameters_timestamps_data_max_len = \
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))

        # Row height
        parameters_ws.row_dimensions[1].height = 102
        for i in range(2, 7 + 1):
            parameters_ws.row_dimensions[i].height = 42

        for i in range(8, parameters_timestamps_data_max_len + 10):
            parameters_ws.row_dimensions[i].height = 60

        # Col width
        parameters_ws.column_dimensions['A'].width = 1.5

        parameters_ws.column_dimensions['B'].width = 25.0

        for i in range(3, 12 + parameters_names_len * 3):
            parameters_ws.column_dimensions[format_cell.get_column_letter(
                i)].width = 15.0

        # Img
        img = Image("excelexporters/myems.png")
        img.width = img.width * 0.85
        img.height = img.height * 0.85
        # img = Image("myems.png")
        parameters_ws.add_image(img, 'B1')

        # Title
        parameters_ws.row_dimensions[3].height = 60

        parameters_ws['B3'].font = name_font
        parameters_ws['B3'].alignment = b_r_alignment
        parameters_ws['B3'] = 'Name:'
        parameters_ws['C3'].border = b_border
        parameters_ws['C3'].alignment = b_c_alignment
        parameters_ws['C3'].font = name_font
        parameters_ws['C3'] = name

        parameters_ws['D3'].font = name_font
        parameters_ws['D3'].alignment = b_r_alignment
        parameters_ws['D3'] = 'Period:'
        parameters_ws['E3'].border = b_border
        parameters_ws['E3'].alignment = b_c_alignment
        parameters_ws['E3'].font = name_font
        parameters_ws['E3'] = period_type

        parameters_ws['F3'].font = name_font
        parameters_ws['F3'].alignment = b_r_alignment
        parameters_ws['F3'] = 'Date:'
        parameters_ws['G3'].border = b_border
        parameters_ws['G3'].alignment = b_c_alignment
        parameters_ws['G3'].font = name_font
        parameters_ws[
            'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
        parameters_ws.merge_cells("G3:H3")

        parameters_ws_current_row_number = 6

        parameters_ws['B' +
                      str(parameters_ws_current_row_number)].font = title_font
        parameters_ws['B' +
                      str(parameters_ws_current_row_number)] = name + ' 相关参数'

        parameters_ws_current_row_number += 1

        parameters_table_start_row_number = parameters_ws_current_row_number

        parameters_ws.row_dimensions[
            parameters_ws_current_row_number].height = 80

        parameters_ws_current_row_number += 1

        table_current_col_number = 'B'

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].fill = table_fill
            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].border = f_border

            col = chr(ord(table_current_col_number) + 1)

            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].border = f_border
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].font = name_font
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].alignment = c_c_alignment
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)] = parameters_data['names'][i]

            table_current_row_number = parameters_ws_current_row_number

            for j, value in enumerate(list(parameters_data['timestamps'][i])):
                col = table_current_col_number

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = value

                col = chr(ord(col) + 1)

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = round(
                    parameters_data['values'][i][j], 2)

                table_current_row_number += 1

            table_current_col_number = chr(ord(table_current_col_number) + 3)

        ########################################################
        # parameters chart and parameters table
        ########################################################

        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
        ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'

        current_sheet_parameters_row_number += 1

        chart_start_row_number = current_sheet_parameters_row_number

        col_index = 0

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            line = LineChart()
            data_col = 3 + col_index * 3
            labels_col = 2 + col_index * 3
            col_index += 1
            line.title = '相关参数 - ' + \
                         parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
            labels = Reference(parameters_ws,
                               min_col=labels_col,
                               min_row=parameters_table_start_row_number + 1,
                               max_row=(len(parameters_data['timestamps'][i]) +
                                        parameters_table_start_row_number))
            line_data = Reference(
                parameters_ws,
                min_col=data_col,
                min_row=parameters_table_start_row_number,
                max_row=(len(parameters_data['timestamps'][i]) +
                         parameters_table_start_row_number))
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = False
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

        current_sheet_parameters_row_number = chart_start_row_number

        current_sheet_parameters_row_number += 1
    ##########################################
    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #10
0
def generate_excel(report, space_name, reporting_start_datetime_local, reporting_end_datetime_local):

    wb = Workbook()
    ws = wb.active
    ws.title = "StoreBatch"

    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 5 + 1):
        ws.row_dimensions[i].height = 42

    for i in range(6, len(report['stores']) + 15):
        ws.row_dimensions[i].height = 60

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Arial', size=15, bold=True)
    title_font = Font(name='Arial', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000')
                      )
    b_border = Border(
        bottom=Side(border_style='medium', color='00000000'),
    )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    ws.add_image(img, 'A1')

    # Title
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Space:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'] = space_name

    ws['B4'].alignment = b_r_alignment
    ws['B4'] = 'Reporting Start Datetime:'
    ws['C4'].border = b_border
    ws['C4'].alignment = b_c_alignment
    ws['C4'] = reporting_start_datetime_local

    ws['B5'].alignment = b_r_alignment
    ws['B5'] = 'Reporting End Datetime:'
    ws['C5'].border = b_border
    ws['C5'].alignment = b_c_alignment
    ws['C5'] = reporting_end_datetime_local

    # Title
    ws['B6'].border = f_border
    ws['B6'].font = name_font
    ws['B6'].alignment = c_c_alignment
    ws['B6'].fill = table_fill
    ws['B6'] = 'ID'

    ws['C6'].border = f_border
    ws['C6'].font = name_font
    ws['C6'].alignment = c_c_alignment
    ws['C6'].fill = table_fill
    ws['C6'] = 'Name'

    ws['D6'].border = f_border
    ws['D6'].alignment = c_c_alignment
    ws['D6'].font = name_font
    ws['D6'].fill = table_fill
    ws['D6'] = 'Space'

    ca_len = len(report['energycategories'])

    for i in range(0, ca_len):
        col = chr(ord('E') + i)
        ws[col + '6'].fill = table_fill
        ws[col + '6'].font = name_font
        ws[col + '6'].alignment = c_c_alignment
        ws[col + '6'] = report['energycategories'][i]['name'] + \
            " (" + report['energycategories'][i]['unit_of_measure'] + ")"
        ws[col + '6'].border = f_border

    current_row_number = 7
    for i in range(0, len(report['stores'])):
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)] = report['stores'][i]['id']

        ws['C' + str(current_row_number)].font = title_font
        ws['C' + str(current_row_number)].border = f_border
        ws['C' + str(current_row_number)].alignment = c_c_alignment
        ws['C' + str(current_row_number)] = report['stores'][i]['store_name']

        ws['D' + str(current_row_number)].font = title_font
        ws['D' + str(current_row_number)].border = f_border
        ws['D' + str(current_row_number)].alignment = c_c_alignment
        ws['D' + str(current_row_number)] = report['stores'][i]['space_name']

        ca_len = len(report['stores'][i]['values'])
        for j in range(0, ca_len):
            col = chr(ord('E') + j)
            ws[col + str(current_row_number)].font = data_font
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)] = round(report['stores'][i]['values'][j], 2)

        current_row_number += 1

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #11
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):
    wb = Workbook()
    ws = wb.active
    ws.title = "StoreStatistics"

    # Row height
    ws.row_dimensions[1].height = 102

    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

    # Col width
    ws.column_dimensions['A'].width = 1.5
    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Arial', size=15, bold=True)
    title_font = Font(name='Arial', size=15, bold=True)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    ws.add_image(img, 'A1')

    # Title
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'] = name

    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'] = period_type

    ws['B4'].alignment = b_r_alignment
    ws['B4'] = 'Reporting Start Datetime:'
    ws['C4'].border = b_border
    ws['C4'].alignment = b_c_alignment
    ws['C4'] = reporting_start_datetime_local

    ws['D4'].alignment = b_r_alignment
    ws['D4'] = 'Reporting End Datetime:'
    ws['E4'].border = b_border
    ws['E4'].alignment = b_c_alignment
    ws['E4'] = reporting_end_datetime_local

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename
    ####################################################################################################################
    # First: Statistics
    # 6: title
    # 7: table title
    # 8~ca_len table_data
    ####################################################################################################################
    reporting_period_data = report['reporting_period']

    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)
        return filename

    ws['B6'].font = title_font
    ws['B6'] = name + ' ' + 'Statistics'

    category = reporting_period_data['names']

    # table_title
    ws['B7'].fill = table_fill
    ws['B7'].font = title_font
    ws['B7'].alignment = c_c_alignment
    ws['B7'] = 'Reporting Period'
    ws['B7'].border = f_border

    ws['C7'].font = title_font
    ws['C7'].alignment = c_c_alignment
    ws['C7'] = 'Arithmetic Mean'
    ws['C7'].border = f_border

    ws['D7'].font = title_font
    ws['D7'].alignment = c_c_alignment
    ws['D7'] = 'Median (Middle Value)'
    ws['D7'].border = f_border

    ws['E7'].font = title_font
    ws['E7'].alignment = c_c_alignment
    ws['E7'] = 'Minimum Value'
    ws['E7'].border = f_border

    ws['F7'].font = title_font
    ws['F7'].alignment = c_c_alignment
    ws['F7'] = 'Maximum Value'
    ws['F7'].border = f_border

    ws['G7'].font = title_font
    ws['G7'].alignment = c_c_alignment
    ws['G7'] = 'Sample Standard Deviation'
    ws['G7'].border = f_border

    ws['H7'].font = title_font
    ws['H7'].alignment = c_c_alignment
    ws['H7'] = 'Sample Variance'
    ws['H7'].border = f_border

    # table_data

    for i, value in enumerate(category):
        row = i * 2 + 8
        ws['B' + str(row)].font = name_font
        ws['B' + str(row)].alignment = c_c_alignment
        ws['B' + str(row)] = reporting_period_data['names'][
            i] + " (" + reporting_period_data['units'][i] + " )"
        ws['B' + str(row)].border = f_border

        ws['B' + str(row + 1)].font = name_font
        ws['B' + str(row + 1)].alignment = c_c_alignment
        ws['B' + str(row + 1)] = 'Increment Rate'
        ws['B' + str(row + 1)].border = f_border

        ws['C' + str(row)].font = name_font
        ws['C' + str(row)].alignment = c_c_alignment
        ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \
            if reporting_period_data['means'][i] is not None else ''
        ws['C' + str(row)].border = f_border
        ws['C' + str(row)].number_format = '0.00'

        ws['C' + str(row + 1)].font = name_font
        ws['C' + str(row + 1)].alignment = c_c_alignment
        ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['means_increment_rate'][i] is not None else '0.00%'
        ws['C' + str(row + 1)].border = f_border

        ws['D' + str(row)].font = name_font
        ws['D' + str(row)].alignment = c_c_alignment
        ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \
            if reporting_period_data['medians'][i] is not None else ''
        ws['D' + str(row)].border = f_border
        ws['D' + str(row)].number_format = '0.00'

        ws['D' + str(row + 1)].font = name_font
        ws['D' + str(row + 1)].alignment = c_c_alignment
        ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%'
        ws['D' + str(row + 1)].border = f_border

        ws['E' + str(row)].font = name_font
        ws['E' + str(row)].alignment = c_c_alignment
        ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \
            if reporting_period_data['minimums'][i] is not None else ''
        ws['E' + str(row)].border = f_border
        ws['E' + str(row)].number_format = '0.00'

        ws['E' + str(row + 1)].font = name_font
        ws['E' + str(row + 1)].alignment = c_c_alignment
        ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%'
        ws['E' + str(row + 1)].border = f_border

        ws['F' + str(row)].font = name_font
        ws['F' + str(row)].alignment = c_c_alignment
        ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
            if reporting_period_data['maximums'][i] is not None else ''
        ws['F' + str(row)].border = f_border
        ws['F' + str(row)].number_format = '0.00'

        ws['F' + str(row + 1)].font = name_font
        ws['F' + str(row + 1)].alignment = c_c_alignment
        ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%'
        ws['F' + str(row + 1)].border = f_border

        ws['G' + str(row)].font = name_font
        ws['G' + str(row)].alignment = c_c_alignment
        ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \
            if reporting_period_data['stdevs'][i] is not None else ''
        ws['G' + str(row)].border = f_border
        ws['G' + str(row)].number_format = '0.00'

        ws['G' + str(row + 1)].font = name_font
        ws['G' + str(row + 1)].alignment = c_c_alignment
        ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%'
        ws['G' + str(row + 1)].border = f_border

        ws['H' + str(row)].font = name_font
        ws['H' + str(row)].alignment = c_c_alignment
        ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \
            if reporting_period_data['variances'][i] is not None else ''
        ws['H' + str(row)].border = f_border
        ws['H' + str(row)].number_format = '0.00'

        ws['H' + str(row + 1)].font = name_font
        ws['H' + str(row + 1)].alignment = c_c_alignment
        ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \
            if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%'
        ws['H' + str(row + 1)].border = f_border
    ####################################################################################################################
    # Second: Reporting Period Consumption
    # 9 + ca_len * 2: title
    # 10 + ca_len * 2: table title
    # per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len :  table_data
    ####################################################################################################################

    names = reporting_period_data['names']
    ca_len = len(names)

    per_unit_area_start_row_number = 9 + ca_len * 2

    ws['B' + str(per_unit_area_start_row_number)].font = title_font
    ws['B' + str(
        per_unit_area_start_row_number)] = name + ' ' + 'Per Unit Area' + str(
            report['store']['area']) + 'M²'

    category = reporting_period_data['names']

    # table_title
    ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill
    ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['B' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['B' + str(per_unit_area_start_row_number + 1)] = 'Reporting Period'
    ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['C' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['C' + str(per_unit_area_start_row_number + 1)] = 'Arithmetic Mean'
    ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['D' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['D' + str(per_unit_area_start_row_number + 1)] = 'Median (Middle Value)'
    ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['E' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['E' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['E' + str(per_unit_area_start_row_number + 1)] = 'Minimum Value'
    ws['E' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['F' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['F' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['F' + str(per_unit_area_start_row_number + 1)] = 'Maximum Value'
    ws['F' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['G' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['G' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['G' +
       str(per_unit_area_start_row_number + 1)] = 'Sample Standard Deviation'
    ws['G' + str(per_unit_area_start_row_number + 1)].border = f_border

    ws['H' + str(per_unit_area_start_row_number + 1)].font = title_font
    ws['H' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
    ws['H' + str(per_unit_area_start_row_number + 1)] = 'Sample Variance'
    ws['H' + str(per_unit_area_start_row_number + 1)].border = f_border

    # table_data

    for i, value in enumerate(category):
        row_data = per_unit_area_start_row_number + 2 + i
        ws['B' + str(row_data)].font = name_font
        ws['B' + str(row_data)].alignment = c_c_alignment
        ws['B' + str(row_data)] = reporting_period_data['names'][
            i] + " (" + reporting_period_data['units'][i] + "/M²)"
        ws['B' + str(row_data)].border = f_border

        ws['C' + str(row_data)].font = name_font
        ws['C' + str(row_data)].alignment = c_c_alignment
        if reporting_period_data['means_per_unit_area'][i] \
                or reporting_period_data['means_per_unit_area'][i] == 0:
            ws['C' + str(row_data)] = round(
                reporting_period_data['means_per_unit_area'][i], 2)
        ws['C' + str(row_data)].border = f_border
        ws['C' + str(row_data)].number_format = '0.00'

        ws['D' + str(row_data)].font = name_font
        ws['D' + str(row_data)].alignment = c_c_alignment
        if reporting_period_data['medians_per_unit_area'][i] \
                or reporting_period_data['medians_per_unit_area'][i] == 0:
            ws['D' + str(row_data)] = round(
                reporting_period_data['medians_per_unit_area'][i], 2)
        ws['D' + str(row_data)].border = f_border
        ws['D' + str(row_data)].number_format = '0.00'

        ws['E' + str(row_data)].font = name_font
        ws['E' + str(row_data)].alignment = c_c_alignment
        if reporting_period_data['minimums_per_unit_area'][i] \
                or reporting_period_data['minimums_per_unit_area'][i] == 0:
            ws['E' + str(row_data)] = round(
                reporting_period_data['minimums_per_unit_area'][i], 2)
        ws['E' + str(row_data)].border = f_border
        ws['E' + str(row_data)].number_format = '0.00'

        ws['F' + str(row_data)].font = name_font
        ws['F' + str(row_data)].alignment = c_c_alignment
        if reporting_period_data['maximums_per_unit_area'][i] \
                or reporting_period_data['maximums_per_unit_area'][i] == 0:
            ws['F' + str(row_data)] = round(
                reporting_period_data['maximums_per_unit_area'][i], 2)
        ws['F' + str(row_data)].border = f_border
        ws['F' + str(row_data)].number_format = '0.00'

        ws['G' + str(row_data)].font = name_font
        ws['G' + str(row_data)].alignment = c_c_alignment
        if (reporting_period_data['stdevs_per_unit_area'][i]) \
                or reporting_period_data['stdevs_per_unit_area'][i] == 0:
            ws['G' + str(row_data)] = round(
                reporting_period_data['stdevs_per_unit_area'][i], 2)
        ws['G' + str(row_data)].border = f_border
        ws['G' + str(row_data)].number_format = '0.00'

        ws['H' + str(row_data)].font = name_font
        ws['H' + str(row_data)].alignment = c_c_alignment
        if reporting_period_data['variances_per_unit_area'][i] \
                or reporting_period_data['variances_per_unit_area'][i] == 0:
            ws['H' + str(row_data)] = round(
                reporting_period_data['variances_per_unit_area'][i], 2)
        ws['H' + str(row_data)].border = f_border
        ws['H' + str(row_data)].number_format = '0.00'

    ####################################################################################################################
    # Third: Detailed Data
    # detailed_start_row_number~ detailed_start_row_number+time_len: line
    # detailed_start_row_number+1: table title
    # i + analysis_end_row_number + 2 + 6 * ca_len~: table_data
    ####################################################################################################################
    has_timestamps_flag = True
    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_timestamps_flag = False

    timestamps = reporting_period_data['timestamps'][0]
    names = reporting_period_data['names']
    ca_len = len(names)
    time_len = len(timestamps)
    parameters_names_len = len(report['parameters']['names'])
    parameters_parameters_datas_len = 0
    analysis_end_row_number = 12 + 3 * ca_len
    current_row_number = analysis_end_row_number
    values = reporting_period_data['values']
    if has_timestamps_flag:
        for i in range(0, parameters_names_len):
            if len(report['parameters']['timestamps'][i]) == 0:
                continue
            parameters_parameters_datas_len += 1
        detail_data_table_start_row_number = current_row_number + (
            ca_len + parameters_parameters_datas_len) * 6 + 2

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data'
        # table_title
        ws['B' + str(detail_data_table_start_row_number)].fill = table_fill
        ws['B' + str(detail_data_table_start_row_number)].font = name_font
        ws['B' +
           str(detail_data_table_start_row_number)].alignment = c_c_alignment
        ws['B' + str(detail_data_table_start_row_number)] = 'Datetime'
        ws['B' + str(detail_data_table_start_row_number)].border = f_border

        current_row_number += 1

        for i in range(0, ca_len):
            col = chr(ord('C') + i)

            ws[col + str(detail_data_table_start_row_number)].font = name_font
            ws[col + str(
                detail_data_table_start_row_number)].alignment = c_c_alignment
            ws[col + str(detail_data_table_start_row_number)] = \
                names[i] + " - (" + reporting_period_data['units'][i] + ")"
            ws[col + str(detail_data_table_start_row_number)].border = f_border
        # table_date
        for i in range(0, time_len):
            rows = i + detail_data_table_start_row_number + 1

            ws['B' + str(rows)].font = name_font
            ws['B' + str(rows)].alignment = c_c_alignment
            ws['B' + str(rows)] = timestamps[i]
            ws['B' + str(rows)].border = f_border

            for index in range(0, ca_len):
                col = chr(ord('C') + index)

                ws[col + str(rows)].font = name_font
                ws[col + str(rows)].alignment = c_c_alignment
                ws[col + str(rows)] = round(values[index][i], 2)
                ws[col + str(rows)].number_format = '0.00'
                ws[col + str(rows)].border = f_border

        # Subtotal
        row_subtotals = detail_data_table_start_row_number + 1 + time_len
        ws['B' + str(row_subtotals)].font = name_font
        ws['B' + str(row_subtotals)].alignment = c_c_alignment
        ws['B' + str(row_subtotals)] = 'Subtotal'
        ws['B' + str(row_subtotals)].border = f_border

        for i in range(0, ca_len):
            col = chr(ord('C') + i)

            ws[col + str(row_subtotals)].font = name_font
            ws[col + str(row_subtotals)].alignment = c_c_alignment
            ws[col + str(row_subtotals)] = round(
                reporting_period_data['subtotals'][i], 2)
            ws[col + str(row_subtotals)].border = f_border
            ws[col + str(row_subtotals)].number_format = '0.00'

    ####################################################################################################################
    # third: LineChart
    # LineChart requires data from the detailed data table in the Excel file
    # so print the detailed data table first and then print LineChart
    ####################################################################################################################
        for i in range(0, ca_len):
            line = LineChart()
            line.title = "Reporting Period Consumption" + " - " + names[i] + \
                "(" + reporting_period_data['units'][i] + ")"
            line.style = 10
            line.x_axis.majorTickMark = 'in'
            line.y_axis.majorTickMark = 'in'
            line.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = True
            times = Reference(ws,
                              min_col=2,
                              min_row=detail_data_table_start_row_number + 1,
                              max_row=detail_data_table_start_row_number + 1 +
                              time_len)
            line_data = Reference(ws,
                                  min_col=3 + i,
                                  min_row=detail_data_table_start_row_number,
                                  max_row=detail_data_table_start_row_number +
                                  time_len)
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(times)
            ser = line.series[0]
            ser.marker.symbol = "diamond"
            ser.marker.size = 5
            ws.add_chart(line, 'B' + str(current_row_number + 6 * i))

    ####################################################################################################################
    has_parameters_names_and_timestamps_and_values_data = True

    ca_len = len(report['reporting_period']['names'])
    current_sheet_parameters_row_number = current_row_number + ca_len * 6
    if 'parameters' not in report.keys() or \
            report['parameters'] is None or \
            'names' not in report['parameters'].keys() or \
            report['parameters']['names'] is None or \
            len(report['parameters']['names']) == 0 or \
            'timestamps' not in report['parameters'].keys() or \
            report['parameters']['timestamps'] is None or \
            len(report['parameters']['timestamps']) == 0 or \
            'values' not in report['parameters'].keys() or \
            report['parameters']['values'] is None or \
            len(report['parameters']['values']) == 0 or \
            timestamps_data_all_equal_0(report['parameters']['timestamps']):
        has_parameters_names_and_timestamps_and_values_data = False
    if has_parameters_names_and_timestamps_and_values_data:

        ################################################################################################################
        # new worksheet
        ################################################################################################################

        parameters_data = report['parameters']

        parameters_names_len = len(parameters_data['names'])

        file_name = ws.title
        parameters_ws = wb.create_sheet(file_name + 'Parameters')

        parameters_timestamps_data_max_len = \
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))

        # Row height
        parameters_ws.row_dimensions[1].height = 102
        for i in range(2, 7 + 1):
            parameters_ws.row_dimensions[i].height = 42

        for i in range(8, parameters_timestamps_data_max_len + 10):
            parameters_ws.row_dimensions[i].height = 60

        # Col width
        parameters_ws.column_dimensions['A'].width = 1.5

        parameters_ws.column_dimensions['B'].width = 25.0

        for i in range(3, 12 + parameters_names_len * 3):
            parameters_ws.column_dimensions[format_cell.get_column_letter(
                i)].width = 15.0

        # Img
        img = Image("excelexporters/myems.png")
        parameters_ws.add_image(img, 'A1')

        # Title
        parameters_ws['B3'].alignment = b_r_alignment
        parameters_ws['B3'] = 'Name:'
        parameters_ws['C3'].border = b_border
        parameters_ws['C3'].alignment = b_c_alignment
        parameters_ws['C3'] = name

        parameters_ws['D3'].alignment = b_r_alignment
        parameters_ws['D3'] = 'Period:'
        parameters_ws['E3'].border = b_border
        parameters_ws['E3'].alignment = b_c_alignment
        parameters_ws['E3'] = period_type

        parameters_ws['B4'].alignment = b_r_alignment
        parameters_ws['B4'] = 'Reporting Start Datetime:'
        parameters_ws['C4'].border = b_border
        parameters_ws['C4'].alignment = b_c_alignment
        parameters_ws['C4'] = reporting_start_datetime_local

        parameters_ws['D4'].alignment = b_r_alignment
        parameters_ws['D4'] = 'Reporting End Datetime:'
        parameters_ws['E4'].border = b_border
        parameters_ws['E4'].alignment = b_c_alignment
        parameters_ws['E4'] = reporting_end_datetime_local

        parameters_ws_current_row_number = 6

        parameters_ws['B' +
                      str(parameters_ws_current_row_number)].font = title_font
        parameters_ws[
            'B' +
            str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters'

        parameters_ws_current_row_number += 1

        parameters_table_start_row_number = parameters_ws_current_row_number

        parameters_ws.row_dimensions[
            parameters_ws_current_row_number].height = 80

        parameters_ws_current_row_number += 1

        table_current_col_number = 2

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            col = format_cell.get_column_letter(table_current_col_number)

            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].border = f_border

            col = format_cell.get_column_letter(table_current_col_number + 1)

            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].border = f_border
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].font = name_font
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].alignment = c_c_alignment
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)] = parameters_data['names'][i]

            table_current_row_number = parameters_ws_current_row_number

            for j, value in enumerate(list(parameters_data['timestamps'][i])):
                col = format_cell.get_column_letter(table_current_col_number)

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = value

                col = format_cell.get_column_letter(table_current_col_number +
                                                    1)

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = round(
                    parameters_data['values'][i][j], 2)

                table_current_row_number += 1

            table_current_col_number = table_current_col_number + 3

        ################################################################################################################
        # parameters chart and parameters table
        ################################################################################################################

        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
        ws['B' + str(
            current_sheet_parameters_row_number)] = name + ' ' + 'Parameters'

        current_sheet_parameters_row_number += 1

        chart_start_row_number = current_sheet_parameters_row_number

        col_index = 0

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            line = LineChart()
            data_col = 3 + col_index * 3
            labels_col = 2 + col_index * 3
            col_index += 1
            line.title = 'Parameters - ' + \
                         parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
            labels = Reference(parameters_ws,
                               min_col=labels_col,
                               min_row=parameters_table_start_row_number + 1,
                               max_row=(len(parameters_data['timestamps'][i]) +
                                        parameters_table_start_row_number))
            line_data = Reference(
                parameters_ws,
                min_col=data_col,
                min_row=parameters_table_start_row_number,
                max_row=(len(parameters_data['timestamps'][i]) +
                         parameters_table_start_row_number))
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = False
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

        current_sheet_parameters_row_number = chart_start_row_number

        current_sheet_parameters_row_number += 1

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #12
0
def add_style_edi(edi_dataframe, PATH):

    wb = load_workbook(PATH)
    ws = wb['Export EDI']

    nb_rows, nb_columns = edi_dataframe.shape
    header_columns = [get_column_letter(col_idx) for col_idx in list(range(1, nb_columns + 2))]
    medium = Side(border_style='thin', color='FF000000')
    medium_borders = Border(top=medium, left=medium, right=medium, bottom=medium)
    font_head_1 = Font(size=10, name='Arial', bold=True)
    font_head = Font(size=20, name='Arial')
    alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True)
    ws.row_dimensions[1].height=30
    ws.freeze_panes = ws["C4"]

    # Color 
    red = PatternFill(fill_type='solid', start_color='FF0000', end_color='FF0000')
    orange_light = PatternFill(fill_type='solid', start_color='FED8B1', end_color='FED8B1') 
    orange_dark = PatternFill(fill_type='solid', start_color='FF8C00', end_color='FF8C00') 
    orange = PatternFill(fill_type='solid', start_color='FFA500', end_color='FFA500') 
    blue = PatternFill(fill_type='solid', start_color='87CEFA', end_color='87CEFA') 
    blue_light = PatternFill(fill_type='solid', start_color='B0E0E6', end_color='B0E0E6') 
    yellow_light = PatternFill(fill_type='solid', start_color='FFFFE0', end_color='FFFFE0') 
    yellow = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00') 
    green_light = PatternFill(fill_type='solid', start_color='90EE90', end_color='90EE90') 


    for letter in [get_column_letter(col_idx) for col_idx in range(1, nb_columns+1)]:
        for number in range(1, nb_rows+4):
            ws[letter+str(number)].border = medium_borders
            ws[letter+str(number)].alignment = alignment_center
    

    ## Head merge and precision
    for index, letter in enumerate(header_columns[:12]):
        ws[letter+"1"].font = font_head_1
        ws[letter+"1"].value = ws[letter+"3"].value
        if index < 8 :
            ws[letter+"1"].fill = orange_light
        elif index < 9 :
            ws[letter+"1"].fill = orange_dark
        else :
            ws[letter+"1"].fill = orange


        ws.merge_cells(letter+"1:"+letter+"3")
        
    ws["M1"].value = "Encagement"
    ws["M1"].font = font_head
    ws["M1"].fill = blue
    ws.merge_cells("M1:AL1")
    
    for letter in ["M","N","O","P"]:
        ws[letter+"2"].font = font_head_1
        ws[letter+"2"].value = ws[letter+"3"].value
        ws[letter+"2"].fill = blue
        ws.merge_cells(letter+"2:"+letter+"3")
    
    ws["Q2"].value = "Mesure sur site"
    ws["Q2"].font = font_head_1
    ws["Q2"].fill = blue
    ws.merge_cells("Q2:U2")

    ws["V2"].value = "Mesure Environnementale"
    ws["V2"].font = font_head_1
    ws["V2"].fill = blue
    ws.merge_cells("V2:AL2")
   
    ws["AM1"].value = "Prélèvement"
    ws["AM1"].font = font_head
    ws["AM1"].fill = blue
    ws.merge_cells("AM1:BL1")
    
    for letter in ["AM","AN","AO","AP"]:
        ws[letter+"2"].value = ws[letter+"3"].value
        ws[letter+"2"].font = font_head_1
        ws[letter+"2"].fill = blue
        ws.merge_cells(letter+"2:"+letter+"3")
    
    ws["AQ2"].value = "Mesure sur site"
    ws["AQ2"].font = font_head_1
    ws["AQ2"].fill = blue    
    ws.merge_cells("AQ2:AU2")

    ws["AV2"].value = "Mesure Environnementale"
    ws["AV2"].font = font_head_1
    ws["AV2"].fill = blue    
    ws.merge_cells("AV2:BL2")
    
    for letter in ["BM","BN"] :
        ws[letter+"1"].font = font_head
        ws[letter+"1"].value = ws[letter+"3"].value
        ws[letter+"1"].fill = red   
        ws.merge_cells(letter+"1:"+letter+"3")
    for letter in ["BO","BP","BQ","BR","BS","BT","BU","BV"] :
        ws[letter+"2"].font = font_head_1
        ws[letter+"2"].value = ws[letter+"3"].value
        ws[letter+"2"].fill = blue   
        ws.merge_cells(letter+"2:"+letter+"3")

    ws["BO1"].value = "Echantillonage"
    ws["BO1"].font = font_head
    ws["BO1"].fill = blue
    ws.merge_cells("BO1:BT1")
    ws["BU1"].value = "Après Lyophilisation"
    ws["BU1"].font = font_head
    ws["BU1"].fill = blue
    ws.merge_cells("BU1:BV1")

    # column width
    ws.column_dimensions["A"].width = 10
    ws.column_dimensions["B"].width = 12
    ws.column_dimensions["C"].width = 12
    ws.column_dimensions["D"].width = 12
    ws.column_dimensions["E"].width = 32
    ws.column_dimensions["F"].width = 32
    ws.column_dimensions["G"].width = 85
    ws.column_dimensions["I"].width = 20
    ws.column_dimensions["J"].width = 15
    ws.column_dimensions["K"].width = 15
    ws.column_dimensions["L"].width = 15

    for letter in header_columns[12:64]:
        ws.column_dimensions[letter].width = 15
        ws[letter + "3"].fill = blue
    ws.column_dimensions["BM"].width = 20
    for letter in header_columns[65:74]:
        ws.column_dimensions[letter].width = 30
        ws[letter + "3"].fill = blue
    # for letter in ["W","X","Y","Z","AA","AL","AM","AN","AO","AP","AQ"]:
    #     ws.column_dimensions[letter].width = 20
    #     if letter != "AQ":
    #         ws[letter + "3"].fill = blue
    ws.column_dimensions["BN"].width = 85

    for letter in ["H","N","AN"]:
        ws.column_dimensions[letter].width = 0.01
    # for letter in ["AS","AT","AU","AV","AW","AX"]:
    #     ws.column_dimensions[letter].width = 30

    not_validated_fill = PatternFill(fill_type='solid', start_color='FF0000', end_color='FF0000')
    not_validated_font = Font(size=10, name='Arial', bold=True, color="FFFFFF")

    for row in range(4,nb_rows+4):
        for index, letter in enumerate(header_columns):
            if letter == "A":
                ws[letter + str(row)].number_format="MMM-YY"
            if letter in ["J","K","L"]:
                ws[letter + str(row)].fill = yellow
            if letter in ["D","M","AM"]:
                ws[letter + str(row)].fill = red
            if letter in ["I","O","P","AO","AP","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV"]:
                ws[letter + str(row)].fill = blue_light
            if letter in ["Q","R","S","T","U","AQ","AR","AS","AT","AU"]:
                ws[letter + str(row)].fill = green_light
            if index in range(21,38) or index in range(47,64):
                ws[letter + str(row)].fill = yellow_light
        if ws["BT"+str(row)].value :
            if ws["BT"+str(row)].value < 3000:
                ws["B"+str(row)].font= not_validated_font
                ws["B"+str(row)].fill= not_validated_fill
                ws["BT"+str(row)].font= not_validated_font
                ws["BT"+str(row)].fill= not_validated_fill
        if ws["BQ"+str(row)].value :
            if ws["BQ"+str(row)].value < 500:
                ws["B"+str(row)].font= not_validated_font
                ws["B"+str(row)].fill= not_validated_fill
                ws["BQ"+str(row)].font= not_validated_font
                ws["BQ"+str(row)].fill= not_validated_fill
        if ws["BM"+str(row)].value=="0%":
            ws["B"+str(row)].font= not_validated_font
            ws["B"+str(row)].fill= not_validated_fill
            ws["BM"+str(row)].font= not_validated_font
            ws["BM"+str(row)].fill= not_validated_fill
            
    




    wb.save(PATH)
    wb.close()
Пример #13
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):
    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 118
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 30

    # Col width
    ws.column_dimensions['A'].width = 1.5

    for i in range(ord('B'), ord('I')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    # Img
    img = Image("excelexporters/myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws.merge_cells("G3:J3")
    for i in range(ord('G'), ord('K')):
        ws[chr(i) + '3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local

    if "reporting_period" not in report.keys() or \
            "difference_values" not in report['reporting_period'].keys() or \
            len(report['reporting_period']['difference_values']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename
    #################################################

    has_difference_values_data_flag = True
    if 'difference_values' not in report['reporting_period'].keys() or len(
            report['reporting_period']['difference_values']) == 0:
        has_difference_values_data_flag = False

    current_row_number = 6

    if has_difference_values_data_flag:
        reporting_period_data = report['reporting_period']
        category = report['meter']['energy_category_name']

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 报告期'

        current_row_number += 1

        ws['B' + str(current_row_number)].fill = table_fill
        if not isinstance(category, list):
            ws['C' + str(current_row_number)].fill = table_fill
            ws['C' + str(current_row_number)].font = name_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = report['meter'][
                'energy_category_name'] + " (" + report['meter'][
                    'unit_of_measure'] + ")"

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '总表消耗'

            ws['C' + str(current_row_number)].font = name_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = round(
                reporting_period_data['master_meter_consumption_in_category'],
                2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '分表消耗'

            ws['C' + str(current_row_number)].font = name_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = round(
                reporting_period_data['submeters_consumption_in_category'], 2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '差值'

            ws['C' + str(current_row_number)].font = name_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = round(
                reporting_period_data['difference_in_category'], 2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '差值百分比'

            ws['C' + str(current_row_number)].font = name_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = str(
                round(reporting_period_data['percentage_difference'] * 100,
                      2)) + '%'

            current_row_number += 2

            time = reporting_period_data['timestamps']
            has_time_data_flag = False
            if time is not None and len(time) > 0:
                has_time_data_flag = True

            if has_time_data_flag:

                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)] = name + ' 详细数据'

                current_row_number += 1
                chart_start_number = current_row_number
                current_row_number = current_row_number + 5
                table_start_number = current_row_number

                ws['B' + str(current_row_number)].fill = table_fill
                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)].border = f_border
                ws['B' + str(current_row_number)].alignment = c_c_alignment
                ws['B' + str(current_row_number)] = '日期时间'

                ws['C' + str(current_row_number)].fill = table_fill
                ws['C' + str(current_row_number)].font = title_font
                ws['C' + str(current_row_number)].border = f_border
                ws['C' + str(current_row_number)].alignment = c_c_alignment
                ws['C' + str(current_row_number)] = report['meter'][
                    'energy_category_name'] + " (" + report['meter'][
                        'unit_of_measure'] + ")"

                current_row_number += 1

                for i in range(0, len(time)):
                    ws['B' + str(current_row_number)].font = title_font
                    ws['B' + str(current_row_number)].border = f_border
                    ws['B' + str(current_row_number)].alignment = c_c_alignment
                    ws['B' + str(current_row_number)] = time[i]

                    ws['C' + str(current_row_number)].font = title_font
                    ws['C' + str(current_row_number)].border = f_border
                    ws['C' + str(current_row_number)].alignment = c_c_alignment
                    ws['C' + str(current_row_number)] = round(
                        reporting_period_data['difference_values'][i], 2)

                    current_row_number += 1

                table_end_number = current_row_number - 1

                bar = BarChart()
                labels = Reference(ws,
                                   min_col=2,
                                   min_row=table_start_number + 1,
                                   max_row=table_end_number)
                bar_data = Reference(ws,
                                     min_col=3,
                                     min_row=table_start_number,
                                     max_row=table_end_number)
                bar.add_data(bar_data, titles_from_data=True)
                bar.set_categories(labels)
                bar.height = 5.25
                bar.width = len(time)
                bar.dLbls = DataLabelList()
                bar.dLbls.showVal = True  # 数量显示
                ws.add_chart(bar, "B" + str(chart_start_number))

        else:
            pass

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #14
0
    ws = wb.active

    ws.title = "Main Sheet"

    headers = ["Sl No", "City", "Temparature", "Update"]

    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 17

    #create a style
    heading_style = NamedStyle(name="heading_style")
    heading_style.font = Font(color=colors.BLACK, size=14, bold=True)
    heading_style.alignment = Alignment(horizontal="center", vertical="center")
    heading_style.fill = PatternFill("solid", fgColor="FFA500")
    bd = Side("thin", color="000000")
    heading_style.border = Border(top=bd, right=bd, bottom=bd, left=bd)

    header_index = 0
    for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=4):

        for cell in row:
            cell.value = headers[header_index]
            cell.style = heading_style
            header_index += 1
    #write serial numbers
    _city_no = 1
    for column in ws.iter_cols(min_col=1,
                               max_col=1,
                               min_row=2,
                               max_row=len(cities) + 1):
        for cell in column:
Пример #15
0
        cell.border = cell.border + top
    for cell in rows[-1]:
        cell.border = cell.border + bottom

    for row in rows:
        l = row[0]
        r = row[-1]
        l.border = l.border + left
        r.border = r.border + right
        if fill:
            for c in row:
                c.fill = fill


wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"
my_cell = ws['B6']
my_cell.value = "My Cell"
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border = Border(top=double, left=thin, right=thin, bottom=double)
fill = PatternFill("solid", fgColor="DDDDDD")
fill = GradientFill(stop=("000000", "FFFFFF"))
font = Font(b=True, color="FF0000")
al = Alignment(horizontal="center", vertical="center")

style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al)
style_range(ws, 'B6:B6', border=border, alignment=al)
wb.save("styled.xlsx")
Пример #16
0
conn_eur.close()
conn_sib.close()

# Объединяем массивы данных по сверхбалансу и приводим дату в номальный вид
df_SverhBal = df_SverhBal_eur.append(df_SverhBal_sib)
df_SverhBal['ДАТА'] = df_SverhBal['ДАТА'].astype('str')
df_Fact_DV['ДАТА'] = df_Fact_DV['ДАТА'].astype('str')

# Экспортируем выгруженные данные в xlsx
df_SverhBal.to_excel(path_sverhbal, index=False)
df_Fact_DV.to_excel(path_fact_DV, index=False)

# Создаем шаблоны стилей
border = Border(left=Side(border_style='thin', color='FF000000'),
                right=Side(border_style='thin', color='FF000000'),
                top=Side(border_style='thin', color='FF000000'),
                bottom=Side(border_style='thin', color='FF000000'))
align_head = Alignment(horizontal='center',
                       vertical='center',
                       text_rotation=0,
                       wrap_text=True,
                       shrink_to_fit=True,
                       indent=0)
align_cell = Alignment(horizontal='right',
                       vertical='center',
                       text_rotation=0,
                       wrap_text=False,
                       shrink_to_fit=False,
                       indent=0)

Пример #17
0
#fill_type 有如下的方式 一般纯色填充使用 solid, 其他样式自行尝试
#{'lightGrid', 'gray0625', 'lightTrellis', 'lightDown', 'lightVertical',
#'darkTrellis', 'darkHorizontal', 'darkVertical', 'darkGrid', 'darkGray',
#'solid', 'darkUp', 'lightGray', 'mediumGray', 'darkDown', 'lightHorizontal', 'lightUp', 'gray125'}


def PatternFill_modify(start, end, patternFill):
    for tuples in sheet[start + ":" + end]:
        for cells in tuples:
            cells.fill = patternFill


#PatternFill_modify("A3","A3",patternObj)

borderObj = Border(left=Side(border_style='thin', color='00ff00'),
                   right=Side(border_style='dashDot', color='00ff00'),
                   top=Side(border_style='double', color='00ff00'),
                   bottom=Side(border_style='hair', color='00ff00'))

#border_style样式很多:‘dashDot’,‘dashDotDot’,‘dashed’,‘dotted’,‘double’,‘hair’,‘medium’,
#‘mediumDashDot’,‘mediumDashDotDot’,‘mediumDashed’,‘slantDashDot’,‘thick’,‘thin’


def border_modify(start, end, border):
    for tuples in sheet[start + ":" + end]:
        for cells in tuples:
            cells.border = border


#border_modify("A3","B6",borderObj)

alignmentObj = Alignment(horizontal='left', vertical='top', wrap_text=True)
Пример #18
0
font = Font(size=12)
BUG_GRADE = ["阻塞缺陷", "一般缺陷", "显示缺陷", "建议缺陷"]
fill = PatternFill("solid", fgColor="00B050")
fill1 = PatternFill("solid", fgColor="FF0000")
fill2 = PatternFill("solid", fgColor="FFC000")
fill3 = PatternFill("solid", fgColor="FFFF00")
fill4 = PatternFill("solid", fgColor="A6A6A6")
column_width = 15
alignment = Alignment(horizontal='center', vertical='center')
now_date = datetime.datetime.now().date()
border = Border(left=Side(style='medium', color='A6A6A6'),
                right=Side(style='medium', color='A6A6A6'),
                top=Side(style='medium', color='A6A6A6'),
                bottom=Side(style='medium', color='A6A6A6'),
                diagonal=Side(style='medium', color='A6A6A6'),
                diagonal_direction=0,
                outline=Side(style='medium', color='A6A6A6'),
                vertical=Side(style='medium', color='A6A6A6'),
                horizontal=Side(style='medium', color='A6A6A6'))

world_farm = {2: '运营客服', 10: '世界农场', 14: '苗叔运营后台', 15: '苗叔用户端'}


def create_xlsx(file_name):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = 'test_report'
    wb.save(filename=file_name)
    print("新建Excel:" + file_name + "成功")
Пример #19
0
class Content(object):
    """通过project实例创建目录"""

    # 设置公用样式
    title_font = Font(name='宋体', size=24, bold=True)
    header_font = Font(name='仿宋_GB2312', size=14, bold=True)
    normal_font = Font(name='仿宋_GB2312', size=14)
    header_border = Border(bottom=Side(style='medium'))
    normal_border = Border(bottom=Side(style='thin', color='80969696'))
    ctr_alignment = Alignment(horizontal='center',
                              vertical='center',
                              wrap_text=True)
    left_alignment = Alignment(horizontal='left',
                               vertical='center',
                               wrap_text=True,
                               indent=1)
    margin = PageMargins()

    def __init__(self, project):
        self.project = project
        self.wb = Workbook()
        self.ws_lob = None
        self.ws_tech = None
        self.ws_qual = None
        self.ws_eco = None
        self.ws_com = None

    def create_all(self):
        """生成目录总方法"""
        self.create_qual()
        self.create_com()
        self.create_eco()
        self.create_tech()
        self.create_lob()
        self.wb.save('目录—{}.xlsx'.format(self.project.name))

    def create_lob(self):
        """创建投标函目录"""
        self.ws_lob = self.wb.create_sheet('投标函', 0)
        col_titles = ['序号', '内容', '页码']
        content = [['一', '投标函'], ['二', '法定代表人身份证明书'], ['三', '法定代表人授权书'],
                   ['四', '守法廉政承诺书'], ['五', '企业内控承诺'], ['六', '投标保证金银行保函']]
        col_width = [10, 60, 10]
        col_num = 3
        row_num = 8

        # 初始化表格
        for i in range(row_num):
            for j in range(col_num):
                cell_now = self.ws_lob.cell(row=i + 1, column=j + 1)
                self.ws_lob.row_dimensions[i + 1].height = 45  # 修改行高
                if i > 0:
                    if i == 1:
                        cell_now.font = Content.header_font
                        cell_now.alignment = Content.ctr_alignment
                        cell_now.border = Content.header_border
                        cell_now.value = col_titles[j]
                    else:
                        cell_now.font = Content.normal_font
                        if j == 1:
                            cell_now.alignment = Content.left_alignment
                            cell_now.value = content[i - 2][1]
                        else:
                            cell_now.alignment = Content.ctr_alignment
                            if j == 0:
                                cell_now.value = content[i - 2][0]
                            elif j == 2:
                                cell_now.value = i - 1
                        if i != row_num - 1:
                            cell_now.border = Content.normal_border
        letters = string.ascii_uppercase
        for i in range(col_num):  # 修改列宽
            self.ws_lob.column_dimensions[letters[i]].width = col_width[i]

        # 填写抬头
        self.ws_lob.merge_cells('A1:C1')
        header = self.ws_lob['A1']
        header.font = Content.title_font
        header.alignment = Content.ctr_alignment
        header.value = '目  录'
        self.ws_lob.row_dimensions[1].height = 50

        # 打印设置
        self.ws_lob.print_options.horizontalCentered = True
        self.ws_lob.print_area = 'A1:C9'
        self.ws_lob.page_setup.fitToWidth = 1
        self.ws_lob.page_margins = Content.margin

    def create_tech(self):
        """创建技术标目录"""
        self.ws_tech = self.wb.create_sheet('技术标', 0)
        col_titles = ['序号', '内容', '页码']
        # 存放固定内容
        content = [
            '技术偏离表', '物资选型部分', '供货清单(一)中各项物资选型一览表', '供货清单(一)中各项物资相关资料', '包装方案',
            '运输相关文件', '物资自检验收方案', '物资第三方检验相关文件', '对外实施工作主体落实承诺书',
            '物资生产企业三体系认证相关资料', '物资节能产品认证相关资料', '物资环境标志产品认证相关资料'
        ]
        # 存放中文序号
        num = [
            '一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '十一', '十二', '十三'
        ]
        col_width = [10, 60, 10]
        col_num = 3

        # 确定行数
        com_num = len(self.project.commodities)
        row_num = com_num + 14
        if self.project.is_cc:
            row_num += 1
            content.insert(9, '来华培训方案及相关材料')
        if self.project.is_qa:
            row_num += 1
            content.insert(9, '售后服务方案及相关材料')
        if self.project.is_tech:
            row_num += 1
            content.insert(9, '技术服务方案及相关材料')

        # 创建专用样式
        third_alignment = Alignment(horizontal='left',
                                    vertical='center',
                                    wrap_text=True,
                                    indent=3)
        third_font = Font(name='仿宋_GB2312', size=12)

        # 填写抬头
        self.ws_tech.merge_cells('A1:C1')
        header = self.ws_tech['A1']
        header.font = Content.title_font
        header.alignment = Content.ctr_alignment
        header.value = '目  录'
        self.ws_tech.row_dimensions[1].height = 50

        # 初始化表格,双循环扫描先行后列扫描表格
        for i in range(1, row_num):
            for j in range(col_num):
                cell_now = self.ws_tech.cell(row=i + 1, column=j + 1)
                self.ws_tech.row_dimensions[i + 1].height = 30  # 修改行高
                # 判断行数来确定应用的字体和样式
                if i == 1:  # 表头行样式填写
                    cell_now.font = Content.header_font
                    cell_now.alignment = Content.ctr_alignment
                    cell_now.border = Content.header_border
                    cell_now.value = col_titles[j]
                elif 1 < i < 4:  # 头两行
                    cell_now.font = Content.normal_font
                    cell_now.border = Content.normal_border
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - 2]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                        if j == 0:
                            cell_now.value = num[i - 2]
                elif i == 4 or i == 5:  # 3、4行
                    cell_now.font = Content.normal_font
                    cell_now.border = Content.normal_border
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - 2]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                elif 5 < i < com_num + 6:  # 填写物资名称
                    cell_now.font = third_font
                    cell_now.border = Content.normal_border
                    if j == 1:
                        cell_now.alignment = third_alignment
                        cell_now.value = '{}、{}'.format(
                            i - 5, self.project.commodities[i - 5][0])
                    else:
                        cell_now.alignment = Content.ctr_alignment
                else:  # 其余的一起填写
                    cell_now.font = Content.normal_font
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - com_num - 2]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                        if j == 0:
                            cell_now.value = num[i - com_num - 4]
                    if i != row_num - 1:
                        cell_now.border = Content.normal_border
        # for i in (9, 11):  # 修改两处格式
        #     self.ws_tech.cell(row=com_num + i, column=2).font = third_font
        #     self.ws_tech.cell(
        #         row=com_num + i,
        #         column=2).alignment = third_alignment
        letters = string.ascii_uppercase
        for i in range(col_num):  # 修改列宽
            self.ws_tech.column_dimensions[letters[i]].width = col_width[i]

        # 打印设置
        self.ws_tech.print_options.horizontalCentered = True
        self.ws_tech.print_area = 'A1:C{}'.format(row_num)
        self.ws_tech.page_setup.fitToWidth = 1
        self.ws_tech.page_margins = PageMargins(top=0.5,
                                                bottom=0.5,
                                                header=0.1,
                                                footer=0.1)

    def create_eco(self):
        self.ws_eco = self.wb.create_sheet('经济标', 0)
        col_titles = ['序号', '内容', '页码']
        content = ['投标报价总表', '物资对内分项报价表', '《供货清单(一)》中各项物资增值税退抵税额表']
        col_width = [10, 60, 10]
        num = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十']
        col_num = 3

        # 确定行数
        row_num = 5
        if len(self.project.qc) == 0:
            row_num += 1
            content.insert(3, '非法检物资检验一览表')
        else:
            if len(self.project.qc) == len(self.project.commodities):
                row_num += 1
                content.insert(3, '法检物资检验一览表')
            else:
                row_num += 2
                content.insert(3, '非法检物资检验一览表')
                content.insert(3, '法检物资检验一览表')
        if self.project.is_cc:
            row_num += 1
            content.insert(3, '来华培训费报价表')
        if self.project.is_tech:
            row_num += 1
            content.insert(3, '技术服务费报价表')

        # 初始化表格
        for i in range(1, row_num):
            for j in range(col_num):
                cell_now = self.ws_eco.cell(row=i + 1, column=j + 1)
                self.ws_eco.row_dimensions[i + 1].height = 45  # 修改行高
                # 判断行数来确定应用的字体和样式
                if i == 1:  # 表头行样式填写
                    cell_now.font = Content.header_font
                    cell_now.alignment = Content.ctr_alignment
                    cell_now.border = Content.header_border
                    cell_now.value = col_titles[j]
                else:  # 其余的一起填写
                    cell_now.font = Content.normal_font
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - 2]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                    if i != row_num - 1:
                        cell_now.border = Content.normal_border
        letters = string.ascii_uppercase
        for i in range(col_num):  # 修改列宽
            self.ws_eco.column_dimensions[letters[i]].width = col_width[i]

        # 填写序号
        # self.ws_eco['A3'] = '经济标部分'
        # self.ws_eco['A3'].font = Content.header_font
        # if not self.project.is_lowprice:
        #     self.ws_eco_com['A{}'.format(row_num - 4)] = '商务标部分'
        #     self.ws_eco_com['A{}'.format(
        #         row_num - 4)].font = Content.header_font

        # 填写序号
        for i in range(3, row_num + 1):
            self.ws_eco['A{}'.format(i)] = num[i - 3]

        # 合并小标题
        # self.ws_eco.merge_cells('A3:C3')
        # if not self.project.is_lowprice:
        #     self.ws_eco.merge_cells('A{0}:C{0}'.format(row_num - 4))

        # 填写抬头
        self.ws_eco.merge_cells('A1:C1')
        header = self.ws_eco['A1']
        header.font = Content.title_font
        header.alignment = Content.ctr_alignment
        header.value = '目  录'
        self.ws_eco.row_dimensions[1].height = 50

        # 打印设置
        self.ws_eco.print_options.horizontalCentered = True
        self.ws_eco.print_area = 'A1:C{}'.format(row_num)
        self.ws_eco.page_setup.fitToWidth = 1
        # self.ws_eco.page_margins = PageMargins(
        #     top=0.5, bottom=0.5, header=0.1, footer=0.1)

    def create_com(self):
        self.ws_com = self.wb.create_sheet('商务标', 0)
        col_titles = ['序号', '内容', '页码']
        content = [['一', '同类物资出口业绩一览表及报关单'], ['二', '向受援国出口货物业绩一览表及报关单']]
        col_width = [10, 60, 10]
        col_num = 3
        row_num = 4

        # # 创建专用样式
        # special_alignment = Alignment(
        #     horizontal='left',
        #     vertical='center',
        #     wrap_text=True,
        #     indent=0)
        # special_font = Font(name='仿宋_GB2312', size=12)

        # 初始化表格
        for i in range(1, row_num):
            for j in range(col_num):
                cell_now = self.ws_com.cell(row=i + 1, column=j + 1)
                self.ws_com.row_dimensions[i + 1].height = 45  # 修改行高
                # 判断行数来确定应用的字体和样式
                if i == 1:  # 表头行样式填写
                    cell_now.font = Content.header_font
                    cell_now.alignment = Content.ctr_alignment
                    cell_now.border = Content.header_border
                    cell_now.value = col_titles[j]
                else:  # 其余
                    cell_now.font = Content.normal_font
                    if i != row_num - 1:
                        cell_now.border = Content.normal_border
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - 2][1]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                        if j == 0:
                            cell_now.value = content[i - 2][0]

        letters = string.ascii_uppercase
        for i in range(col_num):  # 修改列宽
            self.ws_com.column_dimensions[letters[i]].width = col_width[i]

        # 填写抬头
        self.ws_com.merge_cells('A1:C1')
        header = self.ws_com['A1']
        header.font = Content.title_font
        header.alignment = Content.ctr_alignment
        header.value = '目  录'
        self.ws_com.row_dimensions[1].height = 50

        # 打印设置
        self.ws_com.print_options.horizontalCentered = True
        self.ws_com.print_area = 'A1:C{}'.format(row_num)
        self.ws_com.page_setup.fitToWidth = 1
        self.ws_com.page_margins = PageMargins(top=0.5,
                                               bottom=0.5,
                                               header=0.1,
                                               footer=0.1)

    def create_qual(self):
        self.ws_qual = self.wb.create_sheet('资格后审', 0)
        col_titles = ['序号', '内容', '页码']
        content = [['一', '资格后审申请函'], ['二', '证明文件']]
        content2 = [
            '投标人的法人营业执照(复印件)和援外物资项目实施企业资格证明文件(复印件)', '法定代表人证明书和授权书(复印件)',
            '无重大违法记录的声明函', '财务审计报告(复印件)', '依法缴纳社会保障资金的证明和税收的证明(复印件)',
            '特殊物资经营资格、资质许可证明文件(复印件)', '关联企业声明', '其它'
        ]
        col_width = [10, 60, 10]
        col_num = 3
        row_num = 12

        # 创建专用样式
        special_alignment = Alignment(horizontal='left',
                                      vertical='center',
                                      wrap_text=True,
                                      indent=0)
        special_font = Font(name='仿宋_GB2312', size=12)

        # 初始化表格
        for i in range(1, row_num):
            for j in range(col_num):
                cell_now = self.ws_qual.cell(row=i + 1, column=j + 1)
                self.ws_qual.row_dimensions[i + 1].height = 45  # 修改行高
                # 判断行数来确定应用的字体和样式
                if i == 1:  # 表头行样式填写
                    cell_now.font = Content.header_font
                    cell_now.alignment = Content.ctr_alignment
                    cell_now.border = Content.header_border
                    cell_now.value = col_titles[j]
                elif 1 < i < 4:  # 头两行
                    cell_now.font = Content.normal_font
                    cell_now.border = Content.normal_border
                    if j == 1:
                        cell_now.alignment = Content.left_alignment
                        cell_now.value = content[i - 2][1]
                    else:
                        cell_now.alignment = Content.ctr_alignment
                        if j == 0:
                            cell_now.value = content[i - 2][0]
                else:  # 其余的一起填写
                    cell_now.font = special_font
                    if j == 1:
                        cell_now.alignment = special_alignment
                        cell_now.value = '{}、{}'.format(i - 3, content2[i - 4])
                    else:
                        cell_now.alignment = Content.ctr_alignment
                    if i != row_num - 1:
                        cell_now.border = Content.normal_border
        letters = string.ascii_uppercase
        for i in range(col_num):  # 修改列宽
            self.ws_qual.column_dimensions[letters[i]].width = col_width[i]

        # 填写抬头
        self.ws_qual.merge_cells('A1:C1')
        header = self.ws_qual['A1']
        header.font = Content.title_font
        header.alignment = Content.ctr_alignment
        header.value = '目  录'
        self.ws_qual.row_dimensions[1].height = 50

        # 打印设置
        self.ws_qual.print_options.horizontalCentered = True
        self.ws_qual.print_area = 'A1:C{}'.format(row_num)
        self.ws_qual.page_setup.fitToWidth = 1
        self.ws_qual.page_margins = PageMargins(top=0.5,
                                                bottom=0.5,
                                                header=0.1,
                                                footer=0.1)
Пример #20
0
def apply_borders_and_colour(worksheet, heading_colour, fill_colour):
    """ Apply Borders and Colour

    Applies borders and coluring to the parsed openPyXl worksheet.

    :param worksheet:       openPyXl worksheet.
    :param heading_colour:  String describing the hex code of the colour used to fill spreadsheet headings.
    :param fill_colour:     String describing the hex code of the colour used to fill spreadsheet body.
    :return:
    """

    height = worksheet.max_row
    width = worksheet.max_column

    for style_col in range(width):
        column = get_column_value(style_col)
        style_cell_name = "{}{}".format(column, 1)
        worksheet[style_cell_name].font = Font(size=11,
                                               bold=True,
                                               color='FF424242')

    for style_col in range(width):
        column = get_column_value(style_col)
        for style_row in range(height):
            style_cell_name = "{}{}".format(column, style_row + 1)
            style_cell = worksheet[style_cell_name]

            if style_row != 0:
                if style_row % 2 == 0:
                    style_cell.fill = PatternFill("solid", fgColor=fill_colour)
            else:
                style_cell.fill = PatternFill("solid", fgColor=heading_colour)

            if style_row == 0 and style_col == 0:
                style_cell.border = Border(left=Side(border_style="thick",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           top=Side(border_style="thick",
                                                    color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_row == 0 and style_col == width - 1:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thick",
                                                      color='FF000000'),
                                           top=Side(border_style="thick",
                                                    color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_row == 0:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           top=Side(border_style="thick",
                                                    color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_row == height - 1 and style_col == 0:
                style_cell.border = Border(left=Side(border_style="thick",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_row == height - 1 and style_col == width - 1:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thick",
                                                      color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_row == height - 1:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           bottom=Side(border_style="thick",
                                                       color='FF000000'))
            elif style_col == width - 1:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thick",
                                                      color='FF000000'),
                                           bottom=Side(border_style="dashed",
                                                       color='FF000000'))
            elif style_col == 0:
                style_cell.border = Border(left=Side(border_style="thick",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           bottom=Side(border_style="dashed",
                                                       color='FF000000'))
            else:
                style_cell.border = Border(left=Side(border_style="thin",
                                                     color='FF000000'),
                                           right=Side(border_style="thin",
                                                      color='FF000000'),
                                           bottom=Side(border_style="dashed",
                                                       color='FF000000'))
    logger.info("Borders and colour successfully applied to Excel file.")
Пример #21
0
s['U4'].value = '距离退休月数'
s['V4'].value = '公司内部工龄(月数)'
s['W4'].value = '退休日期'


for i in range(5, 31):#根据人数设置
    age_function(i)




#设置单元格格式
font1 = Font(name='黑体',size=24)
font2 = Font(size=12)
border2 = Border(left=Side('thin'),
                right=Side('thin'),
                top=Side('thin'),
                bottom=Side('thin'))
alignment = Alignment(horizontal='center', vertical='center')



for cells in s['R4:w30']:#设置边框
    for cell in cells:
        cell.border = border2
        cell.alignment = alignment



#按当前日期保存
wb.save('d:\\age\\age'+str(arrow.now().format('YYYY-MM-DD'))+ '.xlsx')
Пример #22
0
    def gerar_pla(self, data, dir):
        wb = Workbook()
        ws1 = wb.active  # work sheet
        ws1.title = "Controle de Gasto"

        listDesc = []
        listValor = []
        listData = []
        listPag = []
        listValorPag = []
        listDev = []
        listStatus = []

        ws1["A1"] = 'Descrição'
        ws1["B1"] = 'Valor'
        ws1["C1"] = 'Data de Vencimento'
        ws1["D1"] = 'Data de Pagamento'
        ws1["E1"] = 'Valor que foi pago'
        ws1["F1"] = 'Devendo'
        ws1["G1"] = 'Status'

        conection = ConexaoBD.cur

        Double_border = Border(left=Side(border_style='dashed',
                                         color='FF000000'),
                               right=Side(border_style='dashed',
                                          color='FF000000'),
                               top=Side(border_style='double',
                                        color='FF000000'),
                               bottom=Side(border_style='double',
                                           color='FF000000'))

        #Laços de interação para pegar as informações do banco e transformar em uma planilha
        for i in conection.execute(
                f"select desc from finance WHERE dataVenc LIKE '%{data}'"
        ).fetchall():
            listDesc.append(i[0])
            ws1.cell(column=1, row=len(listDesc) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"SELECT valor  FROM finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listValor.append(i[0])
            ws1.cell(column=2, row=len(listValor) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"SELECT dataVenc FROM finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listData.append(i[0])
            ws1.cell(column=3, row=len(listData) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"SELECT dataPag FROM finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listPag.append(i[0])
            ws1.cell(column=4, row=len(listPag) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"SELECT valorPag FROM finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listValorPag.append(i[0])
            ws1.cell(column=5, row=len(listValorPag) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"select devendo from finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listDev.append(i[0])
            ws1.cell(column=6, row=len(listDev) + 1,
                     value=i[0]).border = Double_border

        for i in conection.execute(
                f"select status from finance WHERE dataVenc LIKE '%{data}' "
        ).fetchall():
            listStatus.append(i[0])
            ws1.cell(column=7, row=len(listStatus) + 1,
                     value=i[0]).border = Double_border

        for i in range(2, 51):
            ws1[f"F{i}"] = f'=SUM(B{i}-E{i})'

        #Céluta total da Dívida
        ws1["H12"] = 'TOTAL DE GASTO DESTE MÊS: '
        ws1["I12"] = '=SUM(B2:B50)'

        #Célula para mostrar o quanto falta pagar
        ws1["H13"] = 'VALOR QUE RESTA À PAGAR: '
        ws1["I13"] = '=SUM(F2:F50)'

        ft_a = Font(name='Times New Roman',
                    color=colors.BLACK,
                    bold=True,
                    size=12)

        a1 = ws1['A1']
        b1 = ws1['B1']
        c1 = ws1['C1']
        d1 = ws1['D1']
        e1 = ws1['E1']
        f1 = ws1['F1']
        g1 = ws1['G1']
        h12 = ws1['H12']
        h13 = ws1['H13']
        h14 = ws1['H14']
        a1.font = ft_a
        b1.font = ft_a
        c1.font = ft_a
        d1.font = ft_a
        e1.font = ft_a
        f1.font = ft_a
        g1.font = ft_a
        h12.font = ft_a
        h13.font = ft_a
        h14.font = ft_a

        wb.save(f'{dir}')
Пример #23
0
    def get(self, request, *args, **kwargs):

        today = datetime.datetime.now()
        today = today.strftime('%Y-%m-%d')

        query = TiempoMuertonDet.objects.all()
        wb = Workbook()

        ws = wb.active
        ws.tittle = 'Tiempos Muertos'

        #Establer el nombre del archivo
        nombre_archivo = str(today) + "Reporte Tiempos Muertosw.xlsx"
        ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B1'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['B1'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B1'].font = Font(name='calibri', size=12, bold=True)
        ws['B1'] = 'Mar Bran S.A. de C.V.'

        ws.merge_cells('B1:F1')

        ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B2'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['B2'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B2'].font = Font(name='calibri', size=12, bold=True)
        ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'

        ws.merge_cells('B2:F2')
        ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['B3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B3'].font = Font(name='calibri', size=12, bold=True)
        ws['B3'] = 'Reporte de Tiempos Muertos'

        ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['G3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['G3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['G3'].font = Font(name='calibri', size=12, bold=True)
        ws['G3'] = 'FECHA'

        ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['H3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['H3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['H3'].font = Font(name='calibri', size=12, bold=True)
        ws['H3'] = today

        ws.merge_cells('B3:F3')

        ws.row_dimensions[1].height = 20
        ws.row_dimensions[2].height = 20
        ws.row_dimensions[3].height = 20

        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 20
        ws.column_dimensions['E'].width = 30
        ws.column_dimensions['F'].width = 20
        ws.column_dimensions['G'].width = 60
        ws.column_dimensions['H'].width = 60
        ws.column_dimensions['G'].width = 20
        ws.column_dimensions['J'].width = 60

        ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['B6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['B6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['B6'].font = Font(name='calibri', size=11, bold=True)
        ws['B6'] = 'Fecha'

        ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['C6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['C6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['C6'].font = Font(name='calibri', size=11, bold=True)
        ws['C6'] = 'Planta'

        ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['D6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['D6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['D6'].font = Font(name='calibri', size=11, bold=True)
        ws['D6'] = 'Línea'

        ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['E6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['E6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['E6'].font = Font(name='calibri', size=11, bold=True)
        ws['E6'] = 'Supervisor'

        ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['F6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['F6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['F6'].font = Font(name='calibri', size=11, bold=True)
        ws['F6'] = 'Turno'

        ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['G6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['G6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['G6'].font = Font(name='calibri', size=11, bold=True)
        ws['G6'] = 'Categoría'

        ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['H6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['H6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['H6'].font = Font(name='calibri', size=11, bold=True)
        ws['H6'] = 'Causa'

        ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['I6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['I6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['I6'].font = Font(name='calibri', size=11, bold=True)
        ws['I6'] = 'Tiempo (min)'

        ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['J6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['J6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['J6'].font = Font(name='calibri', size=11, bold=True)
        ws['J6'] = 'Obs'

        controlador = 7
        for q in query:

            causa = q.causa
            query3 = CausaTM.objects.filter(descripcion=causa).first()

            categoria = query3.categoriaTM
            ws.cell(row=controlador,
                    column=7).alignment = Alignment(horizontal='center',
                                                    vertical='center')
            ws.cell(row=controlador,
                    column=7).border = Border(left=Side(border_style='thin'),
                                              right=Side(border_style='thin'),
                                              top=Side(border_style='thin'),
                                              bottom=Side(border_style='thin'))
            ws.cell(row=controlador, column=7).font = Font(name='calibri',
                                                           size=11,
                                                           bold=True)
            ws.cell(row=controlador, column=7).value = str(categoria)

            ws.cell(row=controlador,
                    column=8).alignment = Alignment(horizontal='center',
                                                    vertical='center')
            ws.cell(row=controlador,
                    column=8).border = Border(left=Side(border_style='thin'),
                                              right=Side(border_style='thin'),
                                              top=Side(border_style='thin'),
                                              bottom=Side(border_style='thin'))
            ws.cell(row=controlador, column=8).font = Font(name='calibri',
                                                           size=11,
                                                           bold=True)
            ws.cell(row=controlador, column=8).value = str(q.causa)

            ws.cell(row=controlador,
                    column=9).alignment = Alignment(horizontal='center',
                                                    vertical='center')
            ws.cell(row=controlador,
                    column=9).border = Border(left=Side(border_style='thin'),
                                              right=Side(border_style='thin'),
                                              top=Side(border_style='thin'),
                                              bottom=Side(border_style='thin'))
            ws.cell(row=controlador, column=9).font = Font(name='calibri',
                                                           size=11,
                                                           bold=True)
            ws.cell(row=controlador, column=9).value = q.cantidad

            ws.cell(row=controlador,
                    column=10).alignment = Alignment(horizontal='center',
                                                     vertical='center')
            ws.cell(row=controlador, column=10).border = Border(
                left=Side(border_style='thin'),
                right=Side(border_style='thin'),
                top=Side(border_style='thin'),
                bottom=Side(border_style='thin'))
            ws.cell(row=controlador, column=10).font = Font(name='calibri',
                                                            size=11,
                                                            bold=True)
            ws.cell(row=controlador, column=10).value = q.obs

            id_enc = q.tiempo_muerto_id

            query2 = TiempoMuertoEnc.objects.filter(id=id_enc)

            for x in query2:

                ws.cell(row=controlador,
                        column=2).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=2).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=2).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=2).value = x.fecha_produccion

                ws.cell(row=controlador,
                        column=3).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=3).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=3).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=3).value = str(x.planta)

                ws.cell(row=controlador,
                        column=4).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=4).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=4).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=4).value = str(x.linea)

                ws.cell(row=controlador,
                        column=5).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=5).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=5).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=5).value = str(x.supervisor)

                ws.cell(row=controlador,
                        column=6).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=6).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=6).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=6).value = str(x.turno)

            controlador += 1

        response = HttpResponse(content_type='application/ms-excel')
        contenido = "attachment; filename = {0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response
Пример #24
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):
    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

    # for i in range(2, 37 + 1):
    #     ws.row_dimensions[i].height = 30
    #
    # for i in range(38, 90 + 1):
    #     ws.row_dimensions[i].height = 30

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    img.width = img.width * 0.85
    img.height = img.height * 0.85
    # img = Image("myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws.row_dimensions[3].height = 60

    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
    ws.merge_cells("G3:H3")

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename

    #################################################

    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True
    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_energy_data_flag = False

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name + ' 报告期成本'

        category = reporting_period_data['names']
        ca_len = len(category)

        ws.row_dimensions[7].height = 60
        ws['B7'].fill = table_fill
        ws['B7'].border = f_border

        ws['B8'].font = title_font
        ws['B8'].alignment = c_c_alignment
        ws['B8'] = '成本'
        ws['B8'].border = f_border

        ws['B9'].font = title_font
        ws['B9'].alignment = c_c_alignment
        ws['B9'] = '环比'
        ws['B9'].border = f_border

        col = 'B'

        for i in range(0, ca_len):
            col = chr(ord('C') + i)
            ws[col + '7'].fill = table_fill
            ws[col + '7'].font = name_font
            ws[col + '7'].alignment = c_c_alignment
            ws[col + '7'] = reporting_period_data['names'][
                i] + " (" + reporting_period_data['units'][i] + ")"
            ws[col + '7'].border = f_border

            ws[col + '8'].font = name_font
            ws[col + '8'].alignment = c_c_alignment
            ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
            ws[col + '8'].border = f_border

            ws[col + '9'].font = name_font
            ws[col + '9'].alignment = c_c_alignment
            ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
                if reporting_period_data['increment_rates'][i] is not None else "-"
            ws[col + '9'].border = f_border

        end_col = chr(ord(col) + 1)
        ws[end_col + '7'].fill = table_fill
        ws[end_col + '7'].font = name_font
        ws[end_col + '7'].alignment = c_c_alignment
        ws[end_col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
        ws[end_col + '7'].border = f_border

        ws[end_col + '8'].font = name_font
        ws[end_col + '8'].alignment = c_c_alignment
        ws[end_col + '8'] = round(reporting_period_data['total'], 2)
        ws[end_col + '8'].border = f_border

        ws[end_col + '9'].font = name_font
        ws[end_col + '9'].alignment = c_c_alignment
        ws[end_col + '9'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
            if reporting_period_data['total_increment_rate'] is not None else "-"
        ws[end_col + '9'].border = f_border

    else:
        for i in range(6, 9 + 1):
            ws.row_dimensions[i].height = 0.1

    #################################################

    has_ele_peak_flag = True
    if "toppeaks" not in reporting_period_data.keys() or \
            reporting_period_data['toppeaks'] is None or \
            len(reporting_period_data['toppeaks']) == 0:
        has_ele_peak_flag = False

    if has_ele_peak_flag:
        ws['B12'].font = title_font
        ws['B12'] = name + '分时用电成本'

        ws['B13'].fill = table_fill
        ws['B13'].font = name_font
        ws['B13'].alignment = c_c_alignment
        ws['B13'].border = f_border

        ws['C13'].fill = table_fill
        ws['C13'].font = name_font
        ws['C13'].alignment = c_c_alignment
        ws['C13'].border = f_border
        ws['C13'] = '分时用电成本'

        ws['D13'].fill = table_fill
        ws['D13'].font = name_font
        ws['D13'].alignment = c_c_alignment
        ws['D13'].border = f_border
        ws['D13'] = '分时用电成本占比'

        costsum = round(reporting_period_data['toppeaks'][0], 2) + round(reporting_period_data['onpeaks'][0], 2) + \
            round(reporting_period_data['midpeaks'][0], 2) + round(reporting_period_data['offpeaks'][0], 2)

        ws['B14'].font = title_font
        ws['B14'].alignment = c_c_alignment
        ws['B14'] = '尖'
        ws['B14'].border = f_border

        ws['C14'].font = title_font
        ws['C14'].alignment = c_c_alignment
        ws['C14'].border = f_border
        ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)

        ws['D14'].font = title_font
        ws['D14'].alignment = c_c_alignment
        ws['D14'].border = f_border
        ws['D14'] = '{:.2%}'.format(round(reporting_period_data['toppeaks'][0], 2) / costsum) \
            if costsum is not None and costsum != Decimal(0.0) else " "

        ws['B15'].font = title_font
        ws['B15'].alignment = c_c_alignment
        ws['B15'] = '峰'
        ws['B15'].border = f_border

        ws['C15'].font = title_font
        ws['C15'].alignment = c_c_alignment
        ws['C15'].border = f_border
        ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)

        ws['D15'].font = title_font
        ws['D15'].alignment = c_c_alignment
        ws['D15'].border = f_border
        ws['D15'] = '{:.2%}'.format(round(reporting_period_data['onpeaks'][0], 2) / costsum) \
            if costsum is not None and costsum != Decimal(0.0) else " "

        ws['B16'].font = title_font
        ws['B16'].alignment = c_c_alignment
        ws['B16'] = '平'
        ws['B16'].border = f_border

        ws['C16'].font = title_font
        ws['C16'].alignment = c_c_alignment
        ws['C16'].border = f_border
        ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)

        ws['D16'].font = title_font
        ws['D16'].alignment = c_c_alignment
        ws['D16'].border = f_border
        ws['D16'] = '{:.2%}'.format(round(reporting_period_data['midpeaks'][0], 2) / costsum) \
            if costsum is not None and costsum != Decimal(0.0) else " "

        ws['B17'].font = title_font
        ws['B17'].alignment = c_c_alignment
        ws['B17'] = '谷'
        ws['B17'].border = f_border

        ws['C17'].font = title_font
        ws['C17'].alignment = c_c_alignment
        ws['C17'].border = f_border
        ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)

        ws['D17'].font = title_font
        ws['D17'].alignment = c_c_alignment
        ws['D17'].border = f_border
        ws['D17'] = '{:.2%}'.format(round(reporting_period_data['offpeaks'][0], 2) / costsum) \
            if costsum is not None and costsum != Decimal(0.0) else " "

        pie = PieChart()
        pie.title = name + '分时用电成本'
        labels = Reference(ws, min_col=2, min_row=14, max_row=17)
        pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 6.6
        pie.width = 9
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False
        s1.dLbls.showVal = True
        s1.dLbls.showPercent = True
        ws.add_chart(pie, "E13")

    else:
        for i in range(12, 18 + 1):
            ws.row_dimensions[i].height = 0.1

    ################################################

    current_row_number = 19

    has_subtotals_data_flag = True
    if "subtotals" not in reporting_period_data.keys() or \
            reporting_period_data['subtotals'] is None or \
            len(reporting_period_data['subtotals']) == 0:
        has_subtotals_data_flag = False

    if has_subtotals_data_flag:
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 成本占比'

        current_row_number += 1

        table_start_row_number = current_row_number

        ws['B' + str(current_row_number)].fill = table_fill
        ws['B' + str(current_row_number)].font = name_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border

        ws['C' + str(current_row_number)].fill = table_fill
        ws['C' + str(current_row_number)].font = name_font
        ws['C' + str(current_row_number)].alignment = c_c_alignment
        ws['C' + str(current_row_number)].border = f_border
        ws['C' + str(current_row_number)] = '成本'

        ws['D' + str(current_row_number)].fill = table_fill
        ws['D' + str(current_row_number)].font = name_font
        ws['D' + str(current_row_number)].alignment = c_c_alignment
        ws['D' + str(current_row_number)].border = f_border
        ws['D' + str(current_row_number)] = '成本占比'

        current_row_number += 1

        ca_len = len(reporting_period_data['names'])
        costsum = Decimal(0.0)
        for i in range(0, ca_len):
            costsum = round(reporting_period_data['subtotals'][i], 2) + costsum
        for i in range(0, ca_len):
            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' +
               str(current_row_number)] = reporting_period_data['names'][i]
            ws['B' + str(current_row_number)].border = f_border

            ws['C' + str(current_row_number)].font = title_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = round(
                reporting_period_data['subtotals'][i], 2)

            ws['D' + str(current_row_number)].font = title_font
            ws['D' + str(current_row_number)].alignment = c_c_alignment
            ws['D' + str(current_row_number)].border = f_border
            ws['D' + str(current_row_number)] = '{:.2%}'.format(round(
                reporting_period_data['subtotals'][i], 2) / costsum) if costsum is not None and costsum != Decimal(0.0)\
                else " "
            current_row_number += 1

        table_end_row_number = current_row_number - 1

        pie = PieChart()
        pie.title = name + ' 成本占比'
        labels = Reference(ws,
                           min_col=2,
                           min_row=table_start_row_number + 1,
                           max_row=table_end_row_number)
        pie_data = Reference(ws,
                             min_col=3,
                             min_row=table_start_row_number,
                             max_row=table_end_row_number)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 6.6
        pie.width = 9
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False
        s1.dLbls.showVal = True
        s1.dLbls.showPercent = True
        table_cell = 'E' + str(table_start_row_number)
        ws.add_chart(pie, table_cell)

        if ca_len < 4:
            current_row_number = current_row_number - ca_len + 4

    else:
        for i in range(21, 29 + 1):
            current_row_number = 30
            ws.row_dimensions[i].height = 0.1

    ###############################################

    current_row_number += 1

    has_detail_data_flag = True

    table_start_draw_flag = current_row_number + 1

    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_detail_data_flag = False

    if has_detail_data_flag:
        reporting_period_data = report['reporting_period']
        times = reporting_period_data['timestamps']
        ca_len = len(report['reporting_period']['names'])
        real_timestamps_len = timestamps_data_not_equal_0(
            report['parameters']['timestamps'])
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 详细数据'

        table_start_row_number = (current_row_number +
                                  1) + ca_len * 6 + real_timestamps_len * 7
        current_row_number = table_start_row_number

        time = times[0]
        has_data = False

        if len(time) > 0:
            has_data = True

        if has_data:

            ws.row_dimensions[current_row_number].height = 60
            ws['B' + str(current_row_number)].fill = table_fill
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)] = '日期时间'

            col = 'B'

            for i in range(0, ca_len):
                col = chr(ord('C') + i)

                ws[col + str(current_row_number)].fill = table_fill
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)] = \
                    reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
                ws[col + str(current_row_number)].border = f_border

            end_col = chr(ord(col) + 1)

            ws[end_col + str(current_row_number)].fill = table_fill
            ws[end_col + str(current_row_number)].font = title_font
            ws[end_col + str(current_row_number)].alignment = c_c_alignment
            ws[end_col +
               str(current_row_number
                   )] = "总计 (" + reporting_period_data['total_unit'] + ")"
            ws[end_col + str(current_row_number)].border = f_border

            current_row_number += 1

            for i in range(0, len(time)):
                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)].alignment = c_c_alignment
                ws['B' + str(current_row_number)] = time[i]
                ws['B' + str(current_row_number)].border = f_border

                col = 'B'

                every_day_total = Decimal(0.0)

                for j in range(0, ca_len):
                    col = chr(ord('C') + j)

                    ws[col + str(current_row_number)].font = title_font
                    ws[col + str(current_row_number)].alignment = c_c_alignment
                    value = round(reporting_period_data['values'][j][i], 2)
                    every_day_total += value
                    ws[col + str(current_row_number)] = value
                    ws[col + str(current_row_number)].border = f_border

                end_col = chr(ord(col) + 1)
                ws[end_col + str(current_row_number)].font = title_font
                ws[end_col + str(current_row_number)].alignment = c_c_alignment
                ws[end_col + str(current_row_number)] = round(
                    every_day_total, 2)
                ws[end_col + str(current_row_number)].border = f_border

                current_row_number += 1

            table_end_row_number = current_row_number - 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)] = '小计'
            ws['B' + str(current_row_number)].border = f_border

            col = 'B'

            for i in range(0, ca_len):
                col = chr(ord('C') + i)
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)] = round(
                    reporting_period_data['subtotals'][i], 2)
                ws[col + str(current_row_number)].border = f_border

                # line
                line = LineChart()
                line.title = '报告期成本 - ' + ws.cell(
                    column=3 + i, row=table_start_row_number).value
                labels = Reference(ws,
                                   min_col=2,
                                   min_row=table_start_row_number + 1,
                                   max_row=table_end_row_number)
                line_data = Reference(ws,
                                      min_col=3 + i,
                                      min_row=table_start_row_number,
                                      max_row=table_end_row_number)
                line.add_data(line_data, titles_from_data=True)
                line.set_categories(labels)
                line_data = line.series[0]
                line_data.marker.symbol = "circle"
                line_data.smooth = True
                line.x_axis.crosses = 'min'
                line.height = 8.25
                line.width = 24
                line.dLbls = DataLabelList()
                line.dLbls.dLblPos = 't'
                line.dLbls.showVal = True
                line.dLbls.showPercent = False
                chart_col = 'B'
                chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
                ws.add_chart(line, chart_cell)

            end_col = chr(ord(col) + 1)
            ws[end_col + str(current_row_number)].font = title_font
            ws[end_col + str(current_row_number)].alignment = c_c_alignment
            ws[end_col + str(current_row_number)] = round(
                reporting_period_data['total'], 2)
            ws[end_col + str(current_row_number)].border = f_border

            current_row_number += 1

    else:
        for i in range(30, 69 + 1):
            current_row_number = 70
            ws.row_dimensions[i].height = 0.1

    #####################################

    has_associated_equipment_flag = True

    if "associated_equipment" not in report.keys() or \
            "energy_category_names" not in report['associated_equipment'].keys() or \
            len(report['associated_equipment']["energy_category_names"]) == 0 \
            or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
            or report['associated_equipment']['associated_equipment_names_array'] is None \
            or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
            or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
        has_associated_equipment_flag = False

    if has_associated_equipment_flag:
        associated_equipment = report['associated_equipment']
        current_row_number += 1

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 相关设备数据'

        current_row_number += 1

        ws.row_dimensions[current_row_number].height = 60
        ws['B' + str(current_row_number)].fill = table_fill
        ws['B' + str(current_row_number)].font = name_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)] = '相关设备'
        ca_len = len(associated_equipment['energy_category_names'])

        for i in range(0, ca_len):
            col = chr(ord('C') + i)
            ws[col + str(current_row_number)].fill = table_fill
            ws[col + str(current_row_number)].font = name_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = \
                reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"

        end_col = chr(ord('B') + ca_len + 1)
        ws[end_col + str(current_row_number)].fill = table_fill
        ws[end_col + str(current_row_number)].font = name_font
        ws[end_col + str(current_row_number)].alignment = c_c_alignment
        ws[end_col + str(current_row_number)].border = f_border
        ws[end_col +
           str(current_row_number
               )] = "总计" + " (" + reporting_period_data['units'][i] + ")"

        associated_equipment_len = len(
            associated_equipment['associated_equipment_names_array'][0])

        for i in range(0, associated_equipment_len):
            current_row_number += 1
            row = str(current_row_number)
            value = Decimal(0.0)

            ws['B' + row].font = title_font
            ws['B' + row].alignment = c_c_alignment
            ws['B' + row] = associated_equipment[
                'associated_equipment_names_array'][0][i]
            ws['B' + row].border = f_border

            for j in range(0, ca_len):
                col = chr(ord('C') + j)
                ws[col + row].font = title_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = round(
                    associated_equipment['subtotals_array'][j][i], 2)
                value += round(associated_equipment['subtotals_array'][j][i],
                               2)
                ws[col + row].border = f_border

            end_col = chr(ord(col) + 1)
            ws[end_col + row].font = title_font
            ws[end_col + row].alignment = c_c_alignment
            ws[end_col + row] = round(value, 2)
            ws[end_col + row].border = f_border

    ##########################################
    current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6 + 1
    has_parameters_names_and_timestamps_and_values_data = True
    if 'parameters' not in report.keys() or \
            report['parameters'] is None or \
            'names' not in report['parameters'].keys() or \
            report['parameters']['names'] is None or \
            len(report['parameters']['names']) == 0 or \
            'timestamps' not in report['parameters'].keys() or \
            report['parameters']['timestamps'] is None or \
            len(report['parameters']['timestamps']) == 0 or \
            'values' not in report['parameters'].keys() or \
            report['parameters']['values'] is None or \
            len(report['parameters']['values']) == 0 or \
            timestamps_data_all_equal_0(report['parameters']['timestamps']):
        has_parameters_names_and_timestamps_and_values_data = False
    if has_parameters_names_and_timestamps_and_values_data:

        ###############################
        # new worksheet
        ###############################

        parameters_data = report['parameters']
        parameters_names_len = len(parameters_data['names'])

        parameters_ws = wb.create_sheet('相关参数')

        parameters_timestamps_data_max_len = \
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))

        # Row height
        parameters_ws.row_dimensions[1].height = 102
        for i in range(2, 7 + 1):
            parameters_ws.row_dimensions[i].height = 42

        for i in range(8, parameters_timestamps_data_max_len + 10):
            parameters_ws.row_dimensions[i].height = 60

        # Col width
        parameters_ws.column_dimensions['A'].width = 1.5

        parameters_ws.column_dimensions['B'].width = 25.0

        for i in range(3, 12 + parameters_names_len * 3):
            parameters_ws.column_dimensions[format_cell.get_column_letter(
                i)].width = 15.0

        # Img
        img = Image("excelexporters/myems.png")
        img.width = img.width * 0.85
        img.height = img.height * 0.85
        # img = Image("myems.png")
        parameters_ws.add_image(img, 'B1')

        # Title
        parameters_ws.row_dimensions[3].height = 60

        parameters_ws['B3'].font = name_font
        parameters_ws['B3'].alignment = b_r_alignment
        parameters_ws['B3'] = 'Name:'
        parameters_ws['C3'].border = b_border
        parameters_ws['C3'].alignment = b_c_alignment
        parameters_ws['C3'].font = name_font
        parameters_ws['C3'] = name

        parameters_ws['D3'].font = name_font
        parameters_ws['D3'].alignment = b_r_alignment
        parameters_ws['D3'] = 'Period:'
        parameters_ws['E3'].border = b_border
        parameters_ws['E3'].alignment = b_c_alignment
        parameters_ws['E3'].font = name_font
        parameters_ws['E3'] = period_type

        parameters_ws['F3'].font = name_font
        parameters_ws['F3'].alignment = b_r_alignment
        parameters_ws['F3'] = 'Date:'
        parameters_ws['G3'].border = b_border
        parameters_ws['G3'].alignment = b_c_alignment
        parameters_ws['G3'].font = name_font
        parameters_ws[
            'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
        parameters_ws.merge_cells("G3:H3")

        parameters_ws_current_row_number = 6

        parameters_ws['B' +
                      str(parameters_ws_current_row_number)].font = title_font
        parameters_ws['B' +
                      str(parameters_ws_current_row_number)] = name + ' 相关参数'

        parameters_ws_current_row_number += 1

        parameters_table_start_row_number = parameters_ws_current_row_number

        parameters_ws.row_dimensions[
            parameters_ws_current_row_number].height = 80

        parameters_ws_current_row_number += 1

        table_current_col_number = 'B'

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].fill = table_fill
            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].border = f_border

            col = decimal_to_column(
                column_to_decimal(table_current_col_number) + 1)

            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].border = f_border
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].font = name_font
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].alignment = c_c_alignment
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)] = parameters_data['names'][i]

            table_current_row_number = parameters_ws_current_row_number

            for j, value in enumerate(list(parameters_data['timestamps'][i])):
                col = table_current_col_number

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = value

                col = decimal_to_column(column_to_decimal(col) + 1)

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = round(
                    parameters_data['values'][i][j], 2)

                table_current_row_number += 1

            table_current_col_number = decimal_to_column(
                column_to_decimal(table_current_col_number) + 3)

        ########################################################
        # parameters chart and parameters table
        ########################################################

        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
        ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'

        current_sheet_parameters_row_number += 1

        chart_start_row_number = current_sheet_parameters_row_number

        col_index = 0

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            line = LineChart()
            data_col = 3 + col_index * 3
            labels_col = 2 + col_index * 3
            col_index += 1
            line.title = '相关参数 - ' + \
                         parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
            labels = Reference(parameters_ws,
                               min_col=labels_col,
                               min_row=parameters_table_start_row_number + 1,
                               max_row=(len(parameters_data['timestamps'][i]) +
                                        parameters_table_start_row_number))
            line_data = Reference(
                parameters_ws,
                min_col=data_col,
                min_row=parameters_table_start_row_number,
                max_row=(len(parameters_data['timestamps'][i]) +
                         parameters_table_start_row_number))
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = False
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

        current_sheet_parameters_row_number = chart_start_row_number

        current_sheet_parameters_row_number += 1
    ##########################################
    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #25
0
    def get(self, request, *args, **kwargs):

        today = datetime.datetime.now()
        today = today.strftime('%Y-%m-%d')

        query = ProduccionDet.objects.all()
        wb = Workbook()

        ws = wb.active
        ws.tittle = 'Producción Embolsados'

        #Establer el nombre del archivo
        nombre_archivo = str(today) + "Reporte Producción Embolslados.xlsx"
        ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B1'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['B1'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B1'].font = Font(name='calibri', size=12, bold=True)
        ws['B1'] = 'Mar Bran S.A. de C.V.'

        ws.merge_cells('B1:F1')
        ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B2'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))

        ws['B2'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B2'].font = Font(name='calibri', size=12, bold=True)
        ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'
        ws.merge_cells('B2:F2')
        ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['B3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['B3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['B3'].font = Font(name='calibri', size=12, bold=True)
        ws['B3'] = 'Reporte de Producción Embolsados'

        ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['G3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['G3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['G3'].font = Font(name='calibri', size=12, bold=True)
        ws['G3'] = 'FECHA'

        ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
        ws['H3'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['H3'].fill = PatternFill(start_color='66FFCC',
                                    end_color='66FFCC',
                                    fill_type='solid')
        ws['H3'].font = Font(name='calibri', size=12, bold=True)
        ws['H3'] = today

        ws.merge_cells('B3:F3')

        ws.row_dimensions[1].height = 20
        ws.row_dimensions[2].height = 20
        ws.row_dimensions[3].height = 20

        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 20
        ws.column_dimensions['E'].width = 30
        ws.column_dimensions['F'].width = 20
        ws.column_dimensions['G'].width = 60
        ws.column_dimensions['H'].width = 60
        ws.column_dimensions['G'].width = 20
        ws.column_dimensions['J'].width = 60

        ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['B6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['B6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['B6'].font = Font(name='calibri', size=11, bold=True)
        ws['B6'] = 'Fecha'

        ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['C6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['C6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['C6'].font = Font(name='calibri', size=11, bold=True)
        ws['C6'] = 'Planta'

        ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['D6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['D6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['D6'].font = Font(name='calibri', size=11, bold=True)
        ws['D6'] = 'Línea'

        ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['E6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['E6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['E6'].font = Font(name='calibri', size=11, bold=True)
        ws['E6'] = 'Supervisor'

        ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['F6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['F6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['F6'].font = Font(name='calibri', size=11, bold=True)
        ws['F6'] = 'Turno'

        ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['G6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['G6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['G6'].font = Font(name='calibri', size=11, bold=True)
        ws['G6'] = 'Plantilla'

        ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['H6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['H6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['H6'].font = Font(name='calibri', size=11, bold=True)
        ws['H6'] = 'Proc./term.'

        ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['I6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['I6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['I6'].font = Font(name='calibri', size=11, bold=True)
        ws['I6'] = 'Producto'

        ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['J6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['J6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['J6'].font = Font(name='calibri', size=11, bold=True)
        ws['J6'] = 'Peso (Lbs)'

        ws['K6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['K6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['K6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['K6'].font = Font(name='calibri', size=11, bold=True)
        ws['K6'] = 'Cantidad (cajas)'

        ws['L6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['L6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['L6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['L6'].font = Font(name='calibri', size=11, bold=True)
        ws['L6'] = 'Resto (lbs)'

        ws['M6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['M6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['M6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['M6'].font = Font(name='calibri', size=11, bold=True)
        ws['M6'] = 'Producción (lbs)'

        ws['N6'].alignment = Alignment(horizontal='center', vertical='center')
        ws['N6'].border = Border(left=Side(border_style='thin'),
                                 right=Side(border_style='thin'),
                                 top=Side(border_style='thin'),
                                 bottom=Side(border_style='thin'))
        ws['N6'].fill = PatternFill(start_color='66CFCC',
                                    end_color='66CFCC',
                                    fill_type='solid')
        ws['N6'].font = Font(name='calibri', size=11, bold=True)
        ws['N6'] = 'Merma(%)'

        controlador = 7
        for q in query:

            id_enc = q.produccion_id
            query2 = ProduccionEnc.objects.filter(id=id_enc)
            for x in query2:

                ws.cell(row=controlador,
                        column=2).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=2).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=2).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=2).value = x.fecha_produccion

                ws.cell(row=controlador,
                        column=3).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=3).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=3).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=3).value = str(x.planta)

                ws.cell(row=controlador,
                        column=4).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=4).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=4).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=4).value = str(x.linea)

                ws.cell(row=controlador,
                        column=5).alignment = Alignment(horizontal='center',
                                                        vertical='center')
                ws.cell(row=controlador, column=5).border = Border(
                    left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
                ws.cell(row=controlador, column=5).font = Font(name='calibri',
                                                               size=11,
                                                               bold=True)
                ws.cell(row=controlador, column=5).value = str(x.supervisor)

        response = HttpResponse(content_type='application/ms-excel')
        contenido = "attachment; filename = {0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response
Пример #26
0
    def create_excel(self):

        #创建新excel,加入格式字体,填充,颜色,预置名称等

        letter = ['A', 'C', 'D', 'E', 'F', 'G', 'H', 'I']

        self.excel_name = self.excel_addr + '\\' + self.report_time + '-Report.xlsx'
        wb = Workbook()
        ws = wb['Sheet']
        ws.title = u'自动化测试报告'
        ws = wb.create_sheet(u'用例执行情况')

        #编辑第一个工作表格信息
        ws = wb[u'自动化测试报告']
        white = Font(size=12, bold=False, name=u'等线', color="FFFFFF")  #白色字体
        black = Font(size=12, bold=False, name=u'等线', color="000000")  #黑色字体
        #合并单元格
        ws.merge_cells('A1:I1')
        ws.merge_cells('A2:I2')
        ws.merge_cells('A5:I5')
        ws.merge_cells('A6:D6')
        ws.merge_cells('F6:I6')
        ws.merge_cells('A4:B4')
        for i in range(7, 51):
            ws.merge_cells('B%d:C%d' % (i, i))
        #更改行高和列宽
        ws.column_dimensions['A'].width = 5
        ws.column_dimensions['B'].width = 15
        ws.column_dimensions['C'].width = 18
        ws.column_dimensions['D'].width = 25
        ws.column_dimensions['E'].width = 15
        ws.column_dimensions['F'].width = 20
        ws.column_dimensions['G'].width = 15
        ws.column_dimensions['H'].width = 25
        ws.column_dimensions['I'].width = 25
        for i in range(1, 51):
            ws.row_dimensions[i].height = 15
        #添加表格线条,
        thin = Side(border_style="thin", color="000000")
        border = Border(left=thin, right=thin, top=thin, bottom=thin)
        for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
            for j in range(1, 51):
                ws[i + str(j)].border = border
                alignment = Alignment(horizontal="center",
                                      vertical="center",
                                      wrap_text=True)  #所有表格居中
                ws[i + str(j)].alignment = alignment
                ws[i + str(j)].font = black
        #添加表格颜色
        ws['A1'].fill = PatternFill(patternType='solid', start_color='333399')
        ws['A2'].fill = PatternFill(patternType='solid', start_color='99CC00')
        for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
            ws[i + str(3)].fill = PatternFill(patternType='solid',
                                              start_color='0066CC')
            ws[i + str(3)].font = white
        ws['C6'].fill = PatternFill(patternType='solid', start_color='0066CC')
        for i in ['A', 'B', 'D']:
            ws[i + str(7)].fill = PatternFill(patternType='solid',
                                              start_color='0066CC')
        ws['A6'].fill = PatternFill(patternType='solid', start_color='99CC00')
        ws['E6'].fill = PatternFill(patternType='solid', start_color='FF6600')
        ws['E7'].fill = PatternFill(patternType='solid', start_color='FF9900')
        ws['F6'].fill = PatternFill(patternType='solid', start_color='FFCC99')
        ws['F7'].fill = PatternFill(patternType='solid', start_color='FFCC00')
        for i in ['F', 'G', 'H', 'I']:
            ws[i + str(7)].fill = PatternFill(patternType='solid',
                                              start_color='0066CC')
        #写入字符
        ws['A7'].font = ws['B7'].font = ws['D7'].font = ws['A1'].font = white
        ws['A1'] = u'自动化测试报告'
        ws['F6'].font = ws['E6'].font = ws['E7'].font = ws['A6'].font = ws[
            'A2'].font = black
        ws['F7'].font = ws['G7'].font = ws['H7'].font = ws['I7'].font = white
        ws['A2'] = u'自动化测试用例总计'
        ws['A6'] = u'测试模块列表'
        ws['E6'] = u'是否执行'
        ws['E7'] = u'执行标识符'
        ws['F6'] = u'执行情况'
        ws['A7'] = u'NO.'
        ws['B7'] = u'模块'
        ws['D7'] = u'自动化测试用例数量'
        ws['F7'] = u'执行时间'
        ws['G7'] = u'总计执行'
        ws['H7'] = u'通过数目'
        ws['I7'] = u'失败数目'
        title1 = [
            u'自动化测试项目', u'自动化测试用例数量', u'自动化测试总计执行', 'Pass', 'Fail', u'测试用时',
            u'开始时间', u'结束时间'
        ]
        ws.merge_cells('A3:B3')
        for i in range(len(letter)):
            ws[letter[i] + str(3)] = title1[i]

        #编辑第二个表格信息
        ws1 = wb[u'用例执行情况']
        #更改单元格宽度
        for i in range(1, 1000):
            ws1.row_dimensions[i].height = 40  #更改行高为40
        for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
            for j in range(1, 1000):
                ws1[i + str(j)].border = border
                ws1[i + str(j)].font = black
                alignment = Alignment(horizontal="center",
                                      vertical="center",
                                      wrap_text=True)  #所有表格居中
                ws1[i + str(j)].alignment = alignment

        ws1.column_dimensions['A'].width = 25
        ws1.column_dimensions['B'].width = 40
        ws1.column_dimensions['C'].width = 13
        ws1.column_dimensions['D'].width = 35
        ws1.column_dimensions['E'].width = 25
        ws1.column_dimensions['F'].width = 40
        ws1.column_dimensions['G'].width = 15
        ws1.column_dimensions['H'].width = 100
        ws1.column_dimensions['I'].width = 25
        ws1.column_dimensions['J'].width = 25
        #合并单元格A1。添加颜色
        ws1.merge_cells('A1:J1')
        ws1['A1'].fill = PatternFill(patternType='solid', start_color='0066CC')
        ws1['A1'].alignment = alignment
        ws1['A1'].font = white
        ws1['A1'] = u'自动化测试用例执行情况'
        #添加A2-G2格式和字体
        x1 = [
            'Suite Name', 'Case Name', 'Tags', u'输入数据', u'实际结果', u'预期结果',
            'Status', 'Message', 'Start-Time', 'End-Time'
        ]
        x2 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
        for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
            ws1[i + str(2)].fill = PatternFill(patternType='solid',
                                               start_color='C0C0C0')  #设置字体
        ws1['A2'] = x1[0]
        ws1['B2'] = x1[1]
        ws1['C2'] = x1[2]
        ws1['D2'] = x1[3]
        ws1['E2'] = x1[4]
        ws1['F2'] = x1[5]
        ws1['G2'] = x1[6]
        ws1['H2'] = x1[7]
        ws1['I2'] = x1[8]
        ws1['J2'] = x1[9]

        #wb.save(addr)#保存excel文件
        return wb  #excel对象
Пример #27
0
def Ping(sheet,sheet_title,ping_ip,num,file_name):

    #workbook = Workbook()
    #sheet = workbook.active

    #workbook = Workbook()
    #sheet = workbook.worksheets[int(num)]
    #sheet = workbook.active 
    sheet = workbook.create_sheet(sheet_title,int(num))
    
    


 
    #wb = Workbook()

    #sheet = workbook.create_sheet("Sheet_A",int(num))
    #sheet.title = sheet_title

    #sheet = workbook.create_sheet("Sheet_B", 1)
    #sheet.title = sheet_title

    #wb.save(filename = 'sample_book.xlsx')

    sheet["A1"] = "Management IP"
    sheet["B1"] = "Hostname"
    sheet["C1"] = "GBN ID"
    sheet["D1"] = "Model"
    sheet["E1"] = "Ping Status"
    sheet["F1"] = "Success Rate (%)"
    sheet["G1"] = "LAN Subnet"
    sheet["H1"] = "LAN Status"
    sheet["I1"] = "Date/Time"



    username="******"
    password="******"

    def Date_time(start_row,start_column):
                
        dt_object = datetime.now()

        sheet.cell(row=start_row, column=start_column).value = dt_object

        return

    def Background_color(row,color,id1,id2):
        fill = PatternFill(start_color=color, fill_type = "solid")


        for cell in sheet[row][id1:id2]:
            cell.fill = fill


        
        return 

    Background_color(1,"009999FF",0,9)

    with open(file_name) as f:
        endpointIPs = f.readlines()

    start_row = 2
    start_column = 1

    for endpoint in endpointIPs:
        host = endpoint.strip()
        print(host)
        print ('Connecting to device: ' + host)
        ios_device = {
            'device_type': 'cisco_ios',
            'ip': host,
            'username': username,
            'password': password
        }

        sheet.cell(row=start_row, column=start_column).value = host
        start_column += 1
        print(start_column)
        print(start_row)

        try:

                    
            remote_conn = ConnectHandler(**ios_device)

            #sheet.cell(row=start_row, column=start_column).value = host
            #start_column += 1
            #print(start_column)
            #print(start_row)

            GBN_ID= remote_conn.send_command("dis current-configuration | i sysname ")
            print (GBN_ID)

            hostname = GBN_ID.split(" ")[2]
            print(hostname)

            sheet.cell(row=start_row, column=start_column).value = hostname
            start_column += 1
            
            print(hostname[0:8])
            sheet.cell(row=start_row, column=start_column).value = hostname[0:8]
            start_column += 1

            sheet.cell(row=start_row, column=start_column).value = hostname[21:25]
            start_column += 1
            
            output= remote_conn.send_command("dis ip int brief ")
            print (output)
        

            
            match = re.compile(r'(10)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)')
            match1 = re.compile(r'(10)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)/(\d\d)\s*(up|down|\*down)')
            
            
            matches = match.finditer(output)
            matches1 = match1.finditer(output)
            

            
            #print(matches1)

            # matches = pattern.finditer(urls)
            List=[]
            for match in matches:
               #print(match)
               List.append(match[0])
            print(List[0])

            List1=[]
            for match1 in matches1:
                #print(match2)
                List1.append(match1[0])
            print(List1)

            #if List1.__contains__("up"):
                

            
            status=[]
            for i in List1:
                status.append(i.split(' '))
            #print(status)


     

            
            #print("Interface is Up")
            for item in List:
                print(item)
                output1= remote_conn.send_command("ping -a %s %s" % (item,ping_ip) )
                print("ping -a %s %s" % (item,ping_ip))
                print (output1)

                

                match2 = re.compile(r'(0|25|50|75|100)\.(00\%)')

                matches2 = match2.finditer(output1)

                List2 = []
                for match2 in matches2:
                    # print(match)
                    List2.append(match2[0])
                    #print(match2[0])
                    print(List2)
                    print("advit")

                
               
                for id in List2:

                    
                    if id == "0.00%":
                        sheet.cell(row=start_row, column=start_column).value = "Pingable"

                        Background_color(start_row,"00008000",4,5)
                        
                        start_column += 1

                        id1 = 100
                                                
                        #sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
                        sheet.cell(row=start_row, column=start_column).value = id1
                        #print(start_column)

                        #Background_color(start_row,"00008000",7,8)

                        start_column += 1
                        print(start_column)
                        print(start_row)
                                            


                    elif id == "25.00%":
                        sheet.cell(row=start_row, column=start_column).value = "Pingable"

                        Background_color(start_row,"00008000",4,5)
                        
                        start_column += 1

                        id1 = 75
                                                
                        #sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
                        sheet.cell(row=start_row, column=start_column).value = id1
                        #print(start_column)
                        
                        #start_row += 1
                        start_column += 1
                        print(start_column)
                        print(start_row)

                    elif id == "50.00%":
                        sheet.cell(row=start_row, column=start_column).value = "Pingable"

                        Background_color(start_row,"00008000",4,5)
                        
                        start_column += 1

                        id1 = 50
                                                
                        #sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
                        sheet.cell(row=start_row, column=start_column).value = id1
                        #print(start_column)
                        
                        #start_row += 1
                        start_column += 1
                        print(start_column)
                        print(start_row)


                    elif id == "75.00%":
                        sheet.cell(row=start_row, column=start_column).value = "Pingable"

                        Background_color(start_row,"00008000",4,5)
                        
                        start_column += 1

                        id1 = 25
                                                
                        #sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
                        sheet.cell(row=start_row, column=start_column).value = id1
                        #print(start_column)
                        
                        #start_row += 1
                        start_column += 1
                        print(start_column)
                        print(start_row)
                        

                    else:
                        sheet.cell(row=start_row, column=start_column).value = "Not Pingable"

                        Background_color(start_row,"00FF0000",4,5)
                        
                        start_column += 1
                        sheet.cell(row=start_row, column=start_column).value = "Unsuccessfull"

                        #print(start_column)
                        
                        #start_row += 1
                        start_column += 1
                        print(start_column)
                        print(start_row)
                            

                                          
            

           

            print("============================================================\n\n")

            
            

            for ip in List:
                Lan = i.split(" ")
                print(Lan)

                sheet.cell(row=start_row, column=start_column).value = Lan[0]
            
                         
                #start_row += 1
                start_column += 1
                print(start_column)
                print(start_row)

                if Lan.__contains__("up"):
                    sheet.cell(row=start_row, column=start_column).value = "UP"

                    Background_color(start_row,"00008000",7,8)

                    start_column += 1
                    
                    #start_row += 1
                    #start_column -= 7
                    

                else:
                    sheet.cell(row=start_row, column=start_column).value = "Down"

                    Background_color(start_row,"00FF0000",7,8)

                    start_column += 1
                    #start_row += 1
                    #start_column -= 7

        

            

            Date_time(start_row,start_column)
            
            start_row += 1
            start_column -= 8

                    

        except (AuthenticationException):
            print ('Authentication failure: ' + host)
            sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
            sheet.cell(row=start_row, column=start_column).value = "Authentication failure"
            

            Background_color(start_row,"00800000",1,2)
            
            start_column += 1     

            
            Date_time(start_row,9)

           
            start_row += 1
            start_column -= 2
            print(start_column)
            print(start_row)

            print("============================================================\n\n")
                   
            continue
       
        except (EOFError):
            print ('End of file while attempting device ' + host)
            sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
            sheet.cell(row=start_row, column=start_column).value = "End of file while attempting device"
            
            Background_color(start_row,"00800000",1,2)
            
            start_column += 1     

            
            Date_time(start_row,9)

           
            start_row += 1
            start_column -= 2
            
            print(start_column)
            print(start_row)
            
            print("============================================================\n\n")
            continue
       
        except:
            print ('Device is not reachable')
            sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
            
            sheet.cell(row=start_row, column=start_column).value = "Device is not reachable"

            Background_color(start_row,"00FFFF00",1,2)

            start_column += 1     

            
            Date_time(start_row,9)

           
            start_row += 1
            start_column -= 2
            
            print(start_column)
            print(start_row)
            print("============================================================\n\n")
            continue


    # Create a few styles
    font = Font(bold=True,
                    size=11,
                    color="00000000")

    alignment=Alignment(horizontal='center',
                              vertical='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)

    border = Border(left=Side(border_style="thin",
                                     color='FF000000'),
                            right=Side(border_style="thin",
                                      color='FF000000'),
                            top=Side(border_style="thin",
                                     color='FF000000'),
                            bottom=Side(border_style="thin",
                                        color='FF000000'),
                            diagonal=Side(border_style="thin",
                                          color='FF000000'),
                            diagonal_direction=0,
                            outline=Side(border_style="thin",
                                         color='FF000000'),
                            vertical=Side(border_style="thin",
                                          color='FF000000'),
                            horizontal=Side(border_style="thin",
                                           color='FF000000')
                           )

    #fill = PatternFill(start_color="00008080", end_color="00008080", fill_type = "solid")

    for cell in sheet[1:1]:
        cell.font = font

            
    print(start_row)
    for i in range(1,start_row):
        for cell in sheet[i]:
            cell.alignment = alignment
            cell.border = border
            #cell.fill = fill

        

    #sheet.title = sheet_title
    print(workbook.sheetnames)
    workbook.save(filename="GBN_Ping.xlsx")

    return
    def get (self, request, *args, **kwargs):

        query = TiempoMuertonDet.objects.all()
        wb = Workbook()

        ws = wb.active
        ws.tittle='Tiempos Muertos'


        #Establer el nombre del archivo
        nombre_archivo = "Reporte Tiempos Muertosw.xlsx"
        ws['B1'].alignment= Alignment(horizontal='left', vertical='center')
        ws['B1'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))

        ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
        ws['B1'].font = Font(name='calibri', size=12, bold=True)
        ws['B1']='Company'

        ws.merge_cells('B1:F1')

        ws['B2'].alignment= Alignment(horizontal='left', vertical='center')
        ws['B2'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))

        ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
        ws['B2'].font = Font(name='calibri', size=12, bold=True)
        ws['B2']='Department'

        ws.merge_cells('B2:F2')
        ws['B3'].alignment= Alignment(horizontal='left', vertical='center')
        ws['B3'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))

        ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
        ws['B3'].font = Font(name='calibri', size=12, bold=True)
        ws['B3']='Reporte de Tiempos Muertos'

        ws.merge_cells('B3:F3')

        ws.row_dimensions[1].height=20
        ws.row_dimensions[2].height=20
        ws.row_dimensions[3].height=20

        ws.column_dimensions['B'].width=20
        ws.column_dimensions['C'].width=20
        ws.column_dimensions['D'].width=20
        ws.column_dimensions['E'].width=20


        ws['B6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['B6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['B6'].font = Font(name='calibri', size=11, bold=True)
        ws['B6']='Fecha'


        ws['C6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['C6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['C6'].font = Font(name='calibri', size=11, bold=True)
        ws['C6']='Planta'

        ws['D6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['D6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['D6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['D6'].font = Font(name='calibri', size=11, bold=True)
        ws['D6']='Línea'

        ws['E6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['E6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['E6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['E6'].font = Font(name='calibri', size=11, bold=True)
        ws['E6']='supervisor'

        ws['F6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['F6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['F6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['F6'].font = Font(name='calibri', size=11, bold=True)
        ws['F6']='Turno'

        ws['G6'].alignment= Alignment(horizontal='center', vertical='center')
        ws['G6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws['G6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws['G6'].font = Font(name='calibri', size=11, bold=True)
        ws['G6']='Causa'

        controlador = 7
        for q in query:

            ws.cell(row=controlador,column=7).alignment= Alignment(horizontal='center', vertical='center')
            ws.cell(row=controlador,column=7).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
            ws.cell(row=controlador,column=7).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
            ws.cell(row=controlador,column=7).font = Font(name='calibri', size=11, bold=True)
            ws.cell(row=controlador,column=7).value=str(q.causa)

            ws.cell(row=controlador,column=8).alignment= Alignment(horizontal='center', vertical='center')
            ws.cell(row=controlador,column=8).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
            ws.cell(row=controlador,column=8).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
            ws.cell(row=controlador,column=8).font = Font(name='calibri', size=11, bold=True)
            ws.cell(row=controlador,column=8).value=q.cantidad

            ws.cell(row=controlador,column=8).alignment= Alignment(horizontal='center', vertical='center')
            ws.cell(row=controlador,column=8).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
            ws.cell(row=controlador,column=8).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
            ws.cell(row=controlador,column=8).font = Font(name='calibri', size=11, bold=True)
            ws.cell(row=controlador,column=8).value=q.obs

            id_enc= q.tiempo_muerto_id


            query2 = TiempoMuertoEnc.objects.filter(id=id_enc)

            for x in query2:

                ws.cell(row=controlador,column=2).alignment= Alignment(horizontal='center', vertical='center')
                ws.cell(row=controlador,column=2).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                    top=Side(border_style='thin'), bottom=Side(border_style='thin'))
                ws.cell(row=controlador,column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
                ws.cell(row=controlador,column=2).font = Font(name='calibri', size=11, bold=True)
                ws.cell(row=controlador,column=2).value=x.fecha_produccion
                
                ws.cell(row=controlador,column=3).alignment= Alignment(horizontal='center', vertical='center')
                ws.cell(row=controlador,column=3).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
                ws.cell(row=controlador,column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
                ws.cell(row=controlador,column=3).font = Font(name='calibri', size=11, bold=True)
                ws.cell(row=controlador,column=3).value=str(x.planta)

                ws.cell(row=controlador,column=4).alignment= Alignment(horizontal='center', vertical='center')
                ws.cell(row=controlador,column=4).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
                ws.cell(row=controlador,column=4).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
                ws.cell(row=controlador,column=4).font = Font(name='calibri', size=11, bold=True)
                ws.cell(row=controlador,column=4).value=str(x.linea)

                ws.cell(row=controlador,column=5).alignment= Alignment(horizontal='center', vertical='center')
                ws.cell(row=controlador,column=5).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
                ws.cell(row=controlador,column=5).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
                ws.cell(row=controlador,column=5).font = Font(name='calibri', size=11, bold=True)
                ws.cell(row=controlador,column=5).value=str(x.supervisor)

                ws.cell(row=controlador,column=6).alignment= Alignment(horizontal='center', vertical='center')
                ws.cell(row=controlador,column=6).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                                top=Side(border_style='thin'), bottom=Side(border_style='thin'))
                ws.cell(row=controlador,column=6).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
                ws.cell(row=controlador,column=6).font = Font(name='calibri', size=11, bold=True)
                ws.cell(row=controlador,column=6).value=str(x.turno)

            controlador +=1

        response = HttpResponse(content_type='application/ms-excel')
        contenido = "attachment; filename = {0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response
Пример #29
0
from openpyxl.styles import PatternFill, Alignment, Side, Border

# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')

# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')

# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式,有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式,有左边
content_border = Border(left=side)

# 设置文件夹路径
path = './各部门利润表汇总/'
# 返回当前目录下所有文件名
files = os.listdir(path)

# 循环文件名列表
for file in files:
    # 拼接文件路径
    file_path = path + file
    # 打开工作簿
    wb = load_workbook(file_path)
    # 打开工作表
Пример #30
0
from PyQt5.QtWidgets import QFileDialog, QDialog
from PyQt5 import QtCore
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00, FORMAT_CURRENCY_USD_SIMPLE
from StatisticsGenerator import StatisticsGenerator

FONT = Font(name='Calibri', size=12, bold=True)
FILL = PatternFill(fgColor='eeeeee')
DOUBLE_LINE = Side(border_style="double", color="000000")
BORDER = Border(bottom=DOUBLE_LINE)
VERT_BORDER = Border(right=DOUBLE_LINE)


class ExcelReportGenerator:
    def __init__(self, data_store):
        self._data_store = data_store
        self._statistics_generator = StatisticsGenerator.create_with_data(
            self._data_store)
        self._workbook: Workbook = None

    def generate(self):
        dialog = QFileDialog()
        dialog.setDefaultSuffix('xlsx')
        path, ok = QFileDialog.getSaveFileName(None,
                                               "Select Directory",
                                               directory="Report.xlsx",
                                               filter="Excel (*.xlsx)")
        print(path)

        if path != "":
Пример #31
0
  def SetFormat(self,row,col,fmt):
    pyws   = self.pyws
    font   = None
    color  = None
    align  = None
    fill   = None
    numFmt = None
    border = None

    c = pyws.cell(row=row,column=col)

    #-------------------------------------------------------------------------
    for i in fmt:
      if (i == 'hAlign'): 
        if (not align): align = Alignment()
        align.horizontal = alignType[fmt[i]]
      elif (i == 'vAlign'): 
        if (not align): align = Alignment()
        align.vertical   = alignType[fmt[i]]
      elif (i == 'tAlign'): 
        if (not align): align = Alignment()
        align.text_rotation = fmt[i]
      elif (i == 'wrap'): 
        if (not align): align = Alignment()
        align.wrap_text = fmt[i]

      elif (i == 'font'):
        name = 'Calibri'
        bold = False
        size = 11
        dict = fmt[i]
        if ('emph' in dict):
          if (dict['emph'] == 'B'):
            bold = True
        if ('size' in dict):
          size = dict['size']
        if (not font):
            font = Font(name=name,size=size,bold=bold)

      elif (i == 'border'):
        dict = fmt[i]
        color = None
        style = None
        if ('Color' in dict):
          color = ColorTable[dict['Color']]
        else:
          color = ColorTable['Black']
        if ('Style' in dict):
          color = dict['Style']
        if (c.border.top.style == None):
          tSide = Side(color=color)
        else:
          tSide = c.border.top.copy()
        if (c.border.bottom.style == None):
          bSide = Side(color=color)
        else:
          bSide = c.border.bottom.copy()
        if (c.border.left.style == None):
          lSide = Side(color=color)
        else:
          lSide = c.border.left.copy()
        if (c.border.right.style == None):
          rSide = Side(color=color)
        else:
          rSide = c.border.right.copy()

        if ((len(dict) ==1) and ('A' in dict)):
          tSide.style = dict['A']
          bSide.style = dict['A']
          lSide.style = dict['A']
          rSide.style = dict['A']
        else:
          for j in dict:
            if (j == 'T'):
              tSide.style = dict[j]
            if (j == 'B'):
              bSide.style = dict[j]
            if (j == 'L'):
              lSide.style = dict[j]
            if (j == 'R'):
              rSide.style = dict[j]

        border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide)

      elif (i == 'fill'): 
        color = ColorTable[fmt[i]]
        fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid')

      elif (i == 'orient'): 
        pass

      elif (i == 'numFmt'):
        numFmt = fmt[i]

    #-------------------------------------------------------------------------
    if (font):
      c.font = font.copy()

    if (align):
      c.alignment = align.copy()

    if (border):
      c.border = border.copy()

    if (fill):
      c.fill = fill.copy()

    if (numFmt):
      c.number_format = numFmt
Пример #32
0
write_wb = Workbook()

# 이름이 있는 시트를 생성
write_ws = write_wb.create_sheet('Sheet1')

# Sheet1에다 입력
write_ws = write_wb.active

grayFill = PatternFill(start_color='c0c0c0',
                       end_color='c0c0c0',
                       fill_type='solid')
box = Border(left=Side(border_style="thin", color='FF000000'),
             right=Side(border_style="thin", color='FF000000'),
             top=Side(border_style="thin", color='FF000000'),
             bottom=Side(border_style="thin", color='FF000000'),
             diagonal=Side(border_style="thin", color='FF000000'),
             diagonal_direction=0,
             outline=Side(border_style="thin", color='FF000000'),
             vertical=Side(border_style="thin", color='FF000000'),
             horizontal=Side(border_style="thin", color='FF000000'))

# Oracle 서버와 연결(Connection 맺기)
conn = cx_Oracle.connect(
    '[User_name]/[Password]@[Server IP]:[Port]/[Service Name]')
cursor = conn.cursor()  # cursor 객체 얻어오기
cursor.execute("""
SELECT 
  A.OWNER, A.table_name, B.comments, A.NUM_ROWS
FROM 
  all_tables A, all_tab_comments B
WHERE 
Пример #33
0
 def flagCell(self,cell):
   side   = Side(style='medium',color=RED)
   border = Border(left=side,right=side,top=side,bottom=side)
   #style  = Style(border=border)
   #cell.style = style
   cell.border = border.copy()
Пример #34
0
def generate_excel(report,
                   name,
                   reporting_start_datetime_local,
                   reporting_end_datetime_local,
                   period_type):

    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102

    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42
    #
    # for i in range(2, 37 + 1):
    #     ws.row_dimensions[i].height = 30
    #
    # for i in range(38, 90 + 1):
    #     ws.row_dimensions[i].height = 30

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000')
                      )
    b_border = Border(
        bottom=Side(border_style='medium', color='00000000'),
    )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    img.width = img.width * 0.85
    img.height = img.height * 0.85
    # img = Image("myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws.row_dimensions[3].height = 60

    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
    ws.merge_cells("G3:H3")

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename
    #################################################
    # First: 能耗分析
    # 6: title
    # 7: table title
    # 8~10 table_data
    # Total: 5 rows
    # if has not energy data: set low height for rows
    #################################################
    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True
    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_energy_data_flag = False

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name+' 能耗分析'

        category = reporting_period_data['names']
        ca_len = len(category)

        ws.row_dimensions[7].height = 60
        ws['B7'].fill = table_fill
        ws['B7'].border = f_border

        ws['B8'].font = title_font
        ws['B8'].alignment = c_c_alignment
        ws['B8'] = '能耗'
        ws['B8'].border = f_border

        ws['B9'].font = title_font
        ws['B9'].alignment = c_c_alignment
        ws['B9'] = '单位面积能耗'
        ws['B9'].border = f_border

        ws['B10'].font = title_font
        ws['B10'].alignment = c_c_alignment
        ws['B10'] = '环比'
        ws['B10'].border = f_border

        col = ''

        for i in range(0, ca_len):
            col = chr(ord('C') + i)
            row = '7'
            cell = col + row
            ws[col + '7'].fill = table_fill
            ws[col + '7'].font = name_font
            ws[col + '7'].alignment = c_c_alignment
            ws[col + '7'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
            ws[col + '7'].border = f_border

            ws[col + '8'].font = name_font
            ws[col + '8'].alignment = c_c_alignment
            ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
            ws[col + '8'].border = f_border

            ws[col + '9'].font = name_font
            ws[col + '9'].alignment = c_c_alignment
            ws[col + '9'] = round(reporting_period_data['subtotals_per_unit_area'][i], 2)
            ws[col + '9'].border = f_border

            ws[col + '10'].font = name_font
            ws[col + '10'].alignment = c_c_alignment
            ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
                if reporting_period_data['increment_rates'][i] is not None else "-"
            ws[col + '10'].border = f_border

        # TCE TCO2E
        end_col = col
        # TCE
        tce_col = chr(ord(end_col) + 1)
        ws[tce_col + '7'].fill = table_fill
        ws[tce_col + '7'].font = name_font
        ws[tce_col + '7'].alignment = c_c_alignment
        ws[tce_col + '7'] = "吨标准煤 (TCE)"
        ws[tce_col + '7'].border = f_border

        ws[tce_col + '8'].font = name_font
        ws[tce_col + '8'].alignment = c_c_alignment
        ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'] / 1000, 2)
        ws[tce_col + '8'].border = f_border

        ws[tce_col + '9'].font = name_font
        ws[tce_col + '9'].alignment = c_c_alignment
        ws[tce_col + '9'] = round(reporting_period_data['total_in_kgce_per_unit_area'] / 1000, 2)
        ws[tce_col + '9'].border = f_border

        ws[tce_col + '10'].font = name_font
        ws[tce_col + '10'].alignment = c_c_alignment
        ws[tce_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
            if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
        ws[tce_col + '10'].border = f_border

        # TCO2E
        tco2e_col = chr(ord(end_col) + 2)
        ws[tco2e_col + '7'].fill = table_fill
        ws[tco2e_col + '7'].font = name_font
        ws[tco2e_col + '7'].alignment = c_c_alignment
        ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
        ws[tco2e_col + '7'].border = f_border

        ws[tco2e_col + '8'].font = name_font
        ws[tco2e_col + '8'].alignment = c_c_alignment
        ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'] / 1000, 2)
        ws[tco2e_col + '8'].border = f_border

        ws[tco2e_col + '9'].font = name_font
        ws[tco2e_col + '9'].alignment = c_c_alignment
        ws[tco2e_col + '9'] = round(reporting_period_data['total_in_kgco2e_per_unit_area'] / 1000, 2)
        ws[tco2e_col + '9'].border = f_border

        ws[tco2e_col + '10'].font = name_font
        ws[tco2e_col + '10'].alignment = c_c_alignment
        ws[tco2e_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
            if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
        ws[tco2e_col + '10'].border = f_border
    else:
        for i in range(6, 10 + 1):
            ws.row_dimensions[i].height = 0.1
    #################################################
    # Second: 分时电耗
    # 12: title
    # 13: table title
    # 14~17 table_data
    # Total: 6 rows
    ################################################
    has_ele_peak_flag = True
    if "toppeaks" not in reporting_period_data.keys() or \
            reporting_period_data['toppeaks'] is None or \
            len(reporting_period_data['toppeaks']) == 0:
        has_ele_peak_flag = False

    if has_ele_peak_flag:
        ws['B12'].font = title_font
        ws['B12'] = name+' 分时电耗'

        ws.row_dimensions[13].height = 60
        ws['B13'].fill = table_fill
        ws['B13'].font = name_font
        ws['B13'].alignment = c_c_alignment
        ws['B13'].border = f_border

        ws['C13'].fill = table_fill
        ws['C13'].font = name_font
        ws['C13'].alignment = c_c_alignment
        ws['C13'].border = f_border
        ws['C13'] = '分时电耗'

        ws['B14'].font = title_font
        ws['B14'].alignment = c_c_alignment
        ws['B14'] = '尖'
        ws['B14'].border = f_border

        ws['C14'].font = title_font
        ws['C14'].alignment = c_c_alignment
        ws['C14'].border = f_border
        ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)

        ws['B15'].font = title_font
        ws['B15'].alignment = c_c_alignment
        ws['B15'] = '峰'
        ws['B15'].border = f_border

        ws['C15'].font = title_font
        ws['C15'].alignment = c_c_alignment
        ws['C15'].border = f_border
        ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)

        ws['B16'].font = title_font
        ws['B16'].alignment = c_c_alignment
        ws['B16'] = '平'
        ws['B16'].border = f_border

        ws['C16'].font = title_font
        ws['C16'].alignment = c_c_alignment
        ws['C16'].border = f_border
        ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)

        ws['B17'].font = title_font
        ws['B17'].alignment = c_c_alignment
        ws['B17'] = '谷'
        ws['B17'].border = f_border

        ws['C17'].font = title_font
        ws['C17'].alignment = c_c_alignment
        ws['C17'].border = f_border
        ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)

        pie = PieChart()
        pie.title = name+' 分时电耗'
        labels = Reference(ws, min_col=2, min_row=14, max_row=17)
        pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 7.25  # cm 1.05*5 1.05cm = 30 pt
        pie.width = 9
        # pie.title = "Pies sold by category"
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False  # 标签显示
        s1.dLbls.showVal = True  # 数量显示
        s1.dLbls.showPercent = True  # 百分比显示
        # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100

        ws.add_chart(pie, "D13")

    else:
        for i in range(12, 18 + 1):
            ws.row_dimensions[i].height = 0.1
        # end_row 10
        # start_row 12
    ################################################
    # Third: 子空间能耗
    # 19: title
    # 20: table title
    # 21~24 table_data
    # Total: 6 rows
    ################################################
    has_child_flag = True
    # Judge if the space has child space, if not, delete it.
    if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
            len(report['child_space']["energy_category_names"]) == 0:
        has_child_flag = False

    if has_child_flag:
        child = report['child_space']
        child_spaces = child['child_space_names_array'][0]
        child_subtotals = child['subtotals_array'][0]

        ws['B19'].font = title_font
        ws['B19'] = name+' 子空间能耗'

        ws.row_dimensions[20].height = 60
        ws['B20'].fill = table_fill
        ws['B20'].border = f_border
        ca_len = len(child['energy_category_names'])

        for i in range(0, ca_len):
            row = chr(ord('C') + i)
            ws[row + '20'].fill = table_fill
            ws[row + '20'].font = title_font
            ws[row + '20'].alignment = c_c_alignment
            ws[row + '20'].border = f_border
            ws[row + '20'] = child['energy_category_names'][i] + ' (' + child['units'][i] + ')'

        space_len = len(child['child_space_names_array'][0])
        for i in range(0, space_len):
            row = str(i + 21)

            ws['B' + row].font = name_font
            ws['B' + row].alignment = c_c_alignment
            ws['B' + row] = child['child_space_names_array'][0][i]
            ws['B' + row].border = f_border

            for j in range(0, ca_len):
                col = chr(ord('C') + j)
                ws[col + row].font = name_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = round(child['subtotals_array'][j][i], 2)
                ws[col + row].border = f_border
                # pie
                # 25~30: pie
                pie = PieChart()
                pie.title = ws.cell(column=3 + j, row=20).value
                labels = Reference(ws, min_col=2, min_row=21, max_row=24)
                pie_data = Reference(ws, min_col=3 + j, min_row=20, max_row=24)
                pie.add_data(pie_data, titles_from_data=True)
                pie.set_categories(labels)
                pie.height = 6.6  # cm 1.05*5 1.05cm = 30 pt
                pie.width = 8
                # pie.title = "Pies sold by category"
                s1 = pie.series[0]
                s1.dLbls = DataLabelList()
                s1.dLbls.showCatName = True  # 标签显示
                s1.dLbls.showVal = True  # 数量显示
                s1.dLbls.showPercent = True  # 百分比显示
                # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100
                chart_col = chr(ord('B') + 2 * j)
                chart_cell = chart_col + '25'
                ws.add_chart(pie, chart_cell)
    else:
        for i in range(19, 36 + 1):
            ws.row_dimensions[i].height = 0.1
    for i in range(30, 35 + 1):
        ws.row_dimensions[i].height = 0.1
    ################################################
    # Fourth: 能耗详情
    # 37: title
    # 38~ 38+ca_len*6-1: line
    # 38+ca_len*6: table title
    # 38+ca_len*6~: table_data
    ################################################
    reporting_period_data = report['reporting_period']
    times = reporting_period_data['timestamps']
    has_detail_data_flag = True
    ca_len = len(report['reporting_period']['names'])
    table_row = 38 + ca_len*6
    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_detail_data_flag = False

    if has_detail_data_flag:
        ws['B37'].font = title_font
        ws['B37'] = name+' 能耗详情'

        ws.row_dimensions[table_row].height = 60
        ws['B'+str(table_row)].fill = table_fill
        ws['B' + str(table_row)].font = title_font
        ws['B'+str(table_row)].border = f_border
        ws['B'+str(table_row)].alignment = c_c_alignment
        ws['B'+str(table_row)] = '时间'
        time = times[0]
        has_data = False
        max_row = 0
        if len(time) > 0:
            has_data = True
            max_row = table_row + len(time)
            print("max_row", max_row)

        if has_data:
            for i in range(0, len(time)):
                col = 'B'
                row = str(table_row+1 + i)
                # col = chr(ord('B') + i)
                ws[col + row].font = title_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = time[i]
                ws[col + row].border = f_border

            for i in range(0, ca_len):
                # 38 title
                col = chr(ord('C') + i)

                ws[col + str(table_row)].fill = table_fill
                ws[col + str(table_row)].font = title_font
                ws[col + str(table_row)].alignment = c_c_alignment
                ws[col + str(table_row)] = reporting_period_data['names'][i] + \
                    " (" + reporting_period_data['units'][i] + ")"
                ws[col + str(table_row)].border = f_border

                # 39 data
                time = times[i]
                time_len = len(time)

                for j in range(0, time_len):
                    row = str(table_row+1 + j)
                    # col = chr(ord('B') + i)
                    ws[col + row].font = title_font
                    ws[col + row].alignment = c_c_alignment
                    ws[col + row] = round(reporting_period_data['values'][i][j], 2)
                    ws[col + row].border = f_border
                # line
                # 39~: line
                line = LineChart()
                line.title = '报告期消耗 - '
                labels = Reference(ws, min_col=2, min_row=table_row+1, max_row=max_row + 1)
                line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row + 1)  # openpyxl bug
                line.add_data(line_data, titles_from_data=True)
                line.set_categories(labels)
                line_data = line.series[0]
                line_data.marker.symbol = "circle"
                line_data.smooth = False
                line.height = 8.25  # cm 1.05*5 1.05cm = 30 pt
                line.width = 24
                # pie.title = "Pies sold by category"
                line.dLbls = DataLabelList()
                # line.dLbls.showCatName = True  # label show
                line.dLbls.showVal = True  # val show
                line.dLbls.showPercent = True  # percent show
                # s1 = CharacterProperties(sz=1800)     # font size *100
                chart_col = 'B'
                chart_cell = chart_col + str(38 + 6*i)
                ws.add_chart(line, chart_cell)
    else:
        for i in range(37, 69 + 1):
            ws.row_dimensions[i].height = 0.1

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
# The following are the default values

>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> font = Font(name='Calibri',
...                 size=11,
...                 bold=False,
...                 italic=False,
...                 vertAlign=None,
...                 underline='none',
...                 strike=False,
...                 color='FF000000')
>>> fill = PatternFill(fill_type=None,
...                 start_color='FFFFFFFF',
...                 end_color='FF000000')
>>> border = Border(left=Side(border_style=None,
...                           color='FF000000'),
...                 right=Side(border_style=None,
...                            color='FF000000'),
...                 top=Side(border_style=None,
...                          color='FF000000'),
...                 bottom=Side(border_style=None,
...                             color='FF000000'),
...                 diagonal=Side(border_style=None,
...                               color='FF000000'),
...                 diagonal_direction=0,
...                 outline=Side(border_style=None,
...                              color='FF000000'),
...                 vertical=Side(border_style=None,
...                               color='FF000000'),
...                 horizontal=Side(border_style=None,
...                                color='FF000000')
Пример #36
0
               bold=False,
               italic=False,
               vertAlign=None,
               underline='none',
               strike=False,
               color='9C0006')
fillGreen = PatternFill("solid", fgColor="1F497D")
greenFont = Font(name='Calibri',
                 size=11,
                 bold=True,
                 italic=False,
                 vertAlign=None,
                 underline='none',
                 strike=False,
                 color='FFFFFF')
border = Border(top=thin, left=thin, right=thin, bottom=thin)
'''
top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)
'''

# BIM Style
def BIMStyle(cell, Fill, Font):
    cell.fill = Fill
    cell.font = Font

# CATEGORY IN WORKSETS CONTROL
def FormattingCategory(workSheets):
    wsSix = workSheets
Пример #37
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):

    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

    # for i in range(2, 37 + 1):
    #     ws.row_dimensions[i].height = 30
    #
    # for i in range(38, 90 + 1):
    #     ws.row_dimensions[i].height = 30

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    img.width = img.width * 0.85
    img.height = img.height * 0.85
    # img = Image("myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws.row_dimensions[3].height = 60

    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
    ws.merge_cells("G3:H3")

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename

    #################################################

    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True
    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_energy_data_flag = False

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name + ' 报告期成本'

        category = reporting_period_data['names']
        ca_len = len(category)

        ws.row_dimensions[7].height = 60
        ws['B7'].fill = table_fill
        ws['B7'].border = f_border

        ws['B8'].font = title_font
        ws['B8'].alignment = c_c_alignment
        ws['B8'] = '成本'
        ws['B8'].border = f_border

        ws['B9'].font = title_font
        ws['B9'].alignment = c_c_alignment
        ws['B9'] = '单位面积能耗'
        ws['B9'].border = f_border

        ws['B10'].font = title_font
        ws['B10'].alignment = c_c_alignment
        ws['B10'] = '环比'
        ws['B10'].border = f_border

        col = 'B'

        for i in range(0, ca_len):
            col = chr(ord('C') + i)
            ws[col + '7'].fill = table_fill
            ws[col + '7'].font = name_font
            ws[col + '7'].alignment = c_c_alignment
            ws[col + '7'] = reporting_period_data['names'][
                i] + " (" + reporting_period_data['units'][i] + ")"
            ws[col + '7'].border = f_border

            ws[col + '8'].font = name_font
            ws[col + '8'].alignment = c_c_alignment
            ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
            ws[col + '8'].border = f_border

            ws[col + '9'].font = name_font
            ws[col + '9'].alignment = c_c_alignment
            ws[col + '9'] = round(
                reporting_period_data['subtotals_per_unit_area'][i], 2)
            ws[col + '9'].border = f_border

            ws[col + '10'].font = name_font
            ws[col + '10'].alignment = c_c_alignment
            ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
                if reporting_period_data['increment_rates'][i] is not None else "-"
            ws[col + '10'].border = f_border

        end_col = chr(ord(col) + 1)
        ws[end_col + '7'].fill = table_fill
        ws[end_col + '7'].font = name_font
        ws[end_col + '7'].alignment = c_c_alignment
        ws[end_col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
        ws[end_col + '7'].border = f_border

        ws[end_col + '8'].font = name_font
        ws[end_col + '8'].alignment = c_c_alignment
        ws[end_col + '8'] = round(reporting_period_data['total'], 2)
        ws[end_col + '8'].border = f_border

        ws[end_col + '9'].font = name_font
        ws[end_col + '9'].alignment = c_c_alignment
        ws[end_col + '9'] = round(reporting_period_data['total_per_unit_area'],
                                  2)
        ws[end_col + '9'].border = f_border

        ws[end_col + '10'].font = name_font
        ws[end_col + '10'].alignment = c_c_alignment
        ws[end_col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
            if reporting_period_data['total_increment_rate'] is not None else "-"
        ws[end_col + '10'].border = f_border

    else:
        for i in range(6, 10 + 1):
            ws.row_dimensions[i].height = 0.1

    #################################################

    has_ele_peak_flag = True
    if "toppeaks" not in reporting_period_data.keys() or \
            reporting_period_data['toppeaks'] is None or \
            len(reporting_period_data['toppeaks']) == 0:
        has_ele_peak_flag = False

    if has_ele_peak_flag:
        ws['B12'].font = title_font
        ws['B12'] = name + ' 分时电耗'

        ws['B13'].fill = table_fill
        ws['B13'].font = name_font
        ws['B13'].alignment = c_c_alignment
        ws['B13'].border = f_border

        ws['C13'].fill = table_fill
        ws['C13'].font = name_font
        ws['C13'].alignment = c_c_alignment
        ws['C13'].border = f_border
        ws['C13'] = '分时电耗'

        ws['B14'].font = title_font
        ws['B14'].alignment = c_c_alignment
        ws['B14'] = '尖'
        ws['B14'].border = f_border

        ws['C14'].font = title_font
        ws['C14'].alignment = c_c_alignment
        ws['C14'].border = f_border
        ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)

        ws['B15'].font = title_font
        ws['B15'].alignment = c_c_alignment
        ws['B15'] = '峰'
        ws['B15'].border = f_border

        ws['C15'].font = title_font
        ws['C15'].alignment = c_c_alignment
        ws['C15'].border = f_border
        ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)

        ws['B16'].font = title_font
        ws['B16'].alignment = c_c_alignment
        ws['B16'] = '平'
        ws['B16'].border = f_border

        ws['C16'].font = title_font
        ws['C16'].alignment = c_c_alignment
        ws['C16'].border = f_border
        ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)

        ws['B17'].font = title_font
        ws['B17'].alignment = c_c_alignment
        ws['B17'] = '谷'
        ws['B17'].border = f_border

        ws['C17'].font = title_font
        ws['C17'].alignment = c_c_alignment
        ws['C17'].border = f_border
        ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)

        pie = PieChart()
        pie.title = name + ' 分时电耗'
        labels = Reference(ws, min_col=2, min_row=14, max_row=17)
        pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 6.6
        pie.width = 9
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False
        s1.dLbls.showVal = True
        s1.dLbls.showPercent = True
        ws.add_chart(pie, "D13")

    else:
        for i in range(12, 18 + 1):
            ws.row_dimensions[i].height = 0.1

    ################################################

    current_row_number = 19

    has_subtotals_data_flag = True
    if "subtotals" not in reporting_period_data.keys() or \
            reporting_period_data['subtotals'] is None or \
            len(reporting_period_data['subtotals']) == 0:
        has_subtotals_data_flag = False

    if has_subtotals_data_flag:
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 成本占比'

        current_row_number += 1

        table_start_row_number = current_row_number

        ws['B' + str(current_row_number)].fill = table_fill
        ws['B' + str(current_row_number)].font = name_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border

        ws['C' + str(current_row_number)].fill = table_fill
        ws['C' + str(current_row_number)].font = name_font
        ws['C' + str(current_row_number)].alignment = c_c_alignment
        ws['C' + str(current_row_number)].border = f_border
        ws['C' + str(current_row_number)] = '成本占比'

        current_row_number += 1

        ca_len = len(reporting_period_data['names'])

        for i in range(0, ca_len):
            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' +
               str(current_row_number)] = reporting_period_data['names'][i]
            ws['B' + str(current_row_number)].border = f_border

            ws['C' + str(current_row_number)].font = title_font
            ws['C' + str(current_row_number)].alignment = c_c_alignment
            ws['C' + str(current_row_number)].border = f_border
            ws['C' + str(current_row_number)] = round(
                reporting_period_data['subtotals'][i], 2)

            current_row_number += 1

        table_end_row_number = current_row_number - 1

        pie = PieChart()
        pie.title = name + ' 成本占比'
        labels = Reference(ws,
                           min_col=2,
                           min_row=table_start_row_number + 1,
                           max_row=table_end_row_number)
        pie_data = Reference(ws,
                             min_col=3,
                             min_row=table_start_row_number,
                             max_row=table_end_row_number)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 6.6
        pie.width = 9
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False
        s1.dLbls.showVal = True
        s1.dLbls.showPercent = True
        table_cell = 'D' + str(table_start_row_number)
        ws.add_chart(pie, table_cell)

        if ca_len < 4:
            current_row_number = current_row_number - ca_len + 4

    else:
        for i in range(21, 29 + 1):
            current_row_number = 30
            ws.row_dimensions[i].height = 0.1

    ###############################################

    current_row_number += 1

    has_detail_data_flag = True

    table_start_draw_flag = current_row_number + 1

    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_detail_data_flag = False

    if has_detail_data_flag:
        reporting_period_data = report['reporting_period']
        times = reporting_period_data['timestamps']
        ca_len = len(report['reporting_period']['names'])

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 详细数据'

        table_start_row_number = (current_row_number + 1) + ca_len * 6
        current_row_number = table_start_row_number

        time = times[0]
        has_data = False

        if len(time) > 0:
            has_data = True

        if has_data:

            ws.row_dimensions[current_row_number].height = 60
            ws['B' + str(current_row_number)].fill = table_fill
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)] = '日期时间'

            col = 'B'

            for i in range(0, ca_len):
                col = chr(ord('C') + i)

                ws[col + str(current_row_number)].fill = table_fill
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
                    " (" + reporting_period_data['units'][i] + ")"
                ws[col + str(current_row_number)].border = f_border

            end_col = chr(ord(col) + 1)

            ws[end_col + str(current_row_number)].fill = table_fill
            ws[end_col + str(current_row_number)].font = title_font
            ws[end_col + str(current_row_number)].alignment = c_c_alignment
            ws[end_col +
               str(current_row_number
                   )] = "总计 (" + reporting_period_data['total_unit'] + ")"
            ws[end_col + str(current_row_number)].border = f_border

            current_row_number += 1

            for i in range(0, len(time)):
                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)].alignment = c_c_alignment
                ws['B' + str(current_row_number)] = time[i]
                ws['B' + str(current_row_number)].border = f_border

                col = 'B'

                every_day_total = 0

                for j in range(0, ca_len):
                    col = chr(ord('C') + j)

                    ws[col + str(current_row_number)].font = title_font
                    ws[col + str(current_row_number)].alignment = c_c_alignment
                    value = round(reporting_period_data['values'][j][i], 2)
                    every_day_total += value
                    ws[col + str(current_row_number)] = value
                    ws[col + str(current_row_number)].border = f_border

                end_col = chr(ord(col) + 1)
                ws[end_col + str(current_row_number)].font = title_font
                ws[end_col + str(current_row_number)].alignment = c_c_alignment
                ws[end_col + str(current_row_number)] = round(
                    every_day_total, 2)
                ws[end_col + str(current_row_number)].border = f_border

                current_row_number += 1

            table_end_row_number = current_row_number - 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)] = '小计'
            ws['B' + str(current_row_number)].border = f_border

            col = 'B'

            for i in range(0, ca_len):
                col = chr(ord('C') + i)
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)] = round(
                    reporting_period_data['subtotals'][i], 2)
                ws[col + str(current_row_number)].border = f_border

                # line
                line = LineChart()
                line.title = '报告期成本 - ' + ws.cell(
                    column=3 + i, row=table_start_row_number).value
                labels = Reference(ws,
                                   min_col=2,
                                   min_row=table_start_row_number + 1,
                                   max_row=table_end_row_number)
                line_data = Reference(ws,
                                      min_col=3 + i,
                                      min_row=table_start_row_number,
                                      max_row=table_end_row_number)
                line.add_data(line_data, titles_from_data=True)
                line.set_categories(labels)
                line_data = line.series[0]
                line_data.marker.symbol = "circle"
                line_data.smooth = True
                line.x_axis.crosses = 'min'
                line.height = 8.25
                line.width = 24
                line.dLbls = DataLabelList()
                line.dLbls.dLblPos = 't'
                line.dLbls.showVal = True
                line.dLbls.showPercent = False
                chart_col = 'B'
                chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
                ws.add_chart(line, chart_cell)

            end_col = chr(ord(col) + 1)
            ws[end_col + str(current_row_number)].font = title_font
            ws[end_col + str(current_row_number)].alignment = c_c_alignment
            ws[end_col + str(current_row_number)] = round(
                reporting_period_data['total'], 2)
            ws[end_col + str(current_row_number)].border = f_border

            current_row_number += 1

    else:
        for i in range(30, 69 + 1):
            current_row_number = 70
            ws.row_dimensions[i].height = 0.1

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #38
0
    def render_xlsx(self, outfd, data):
        BoldStyle = Style(font=Font(name='Calibri',
                                    size=11,
                                    bold=True,
                                    italic=False,
                                    vertAlign=None,
                                    underline='none',
                                    strike=False,
                                    color='FFFFFFFF'),
                          fill=PatternFill(fill_type="solid",
                                           start_color='FF000000',
                                           end_color='FF000000'))
        RedStyle = Style(font=Font(name='Calibri',
                                   size=11,
                                   bold=False,
                                   italic=False,
                                   vertAlign=None,
                                   underline='none',
                                   strike=False,
                                   color='FF000000'),
                         border=Border(left=Side(border_style="thick",
                                                 color='FF000000'),
                                       right=Side(border_style="thick",
                                                  color='FF000000'),
                                       top=Side(border_style="thick",
                                                color='FF000000'),
                                       bottom=Side(border_style="thick",
                                                   color='FF000000'),
                                       diagonal=Side(border_style="thick",
                                                     color='FF000000'),
                                       diagonal_direction=0,
                                       outline=Side(border_style="thick",
                                                    color='FF000000'),
                                       vertical=Side(border_style="thick",
                                                     color='FF000000'),
                                       horizontal=Side(border_style="thick",
                                                       color='FF000000')),
                         fill=PatternFill(start_color='FFFF0000',
                                          end_color='FFFF0000',
                                          fill_type='solid'))
        GreenStyle = Style(font=Font(name='Calibri',
                                     size=11,
                                     bold=False,
                                     italic=False,
                                     vertAlign=None,
                                     underline='none',
                                     strike=False,
                                     color='FF000000'),
                           fill=PatternFill(start_color="FF00FF00",
                                            end_color="FF00FF00",
                                            fill_type="solid"))

        wb = Workbook(optimized_write=True)
        ws = wb.create_sheet()
        ws.title = "Psxview Output"
        ws.append([
            "Offset (P)", "Name", "PID", "pslist", "psscan", "thrdproc",
            "pspcid", "csrss", "session", "deskthrd", "Exit Time"
        ])
        total = 1
        for offset, process, ps_sources in data:
            incsrss = ps_sources['csrss'].has_key(offset)
            insession = ps_sources['session'].has_key(offset)
            indesktop = ps_sources['deskthrd'].has_key(offset)
            inpspcid = ps_sources['pspcid'].has_key(offset)
            inpslist = ps_sources['pslist'].has_key(offset)
            inthread = ps_sources['thrdproc'].has_key(offset)

            if self._config.APPLY_RULES:
                if not incsrss:
                    if str(process.ImageFileName).lower() in [
                            "system", "smss.exe", "csrss.exe"
                    ]:
                        incsrss = "Okay"
                    elif process.ExitTime > 0:
                        incsrss = "Okay"
                if not insession:
                    if str(process.ImageFileName).lower() in [
                            "system", "smss.exe"
                    ]:
                        insession = "Okay"
                    elif process.ExitTime > 0:
                        insession = "Okay"
                if not indesktop:
                    if str(process.ImageFileName).lower() in [
                            "system", "smss.exe"
                    ]:
                        indesktop = "Okay"
                    elif process.ExitTime > 0:
                        indesktop = "Okay"
                if not inpspcid:
                    if process.ExitTime > 0:
                        inpspcid = "Okay"
                if not inpslist:
                    if process.ExitTime > 0:
                        inpslist = "Okay"
                if not inthread:
                    if process.ExitTime > 0:
                        inthread = "Okay"

            ws.append([
                hex(offset),
                str(
                    utils.remove_unprintable(str(process.ImageFileName))
                    or ""),
                str(process.UniqueProcessId),
                str(inpslist),
                str(ps_sources['psscan'].has_key(offset)),
                str(inthread),
                str(inpspcid),
                str(incsrss),
                str(insession),
                str(indesktop),
                str(process.ExitTime or '')
            ])
            total += 1
        wb.save(filename=self._config.OUTPUT_FILE)

        wb = load_workbook(filename=self._config.OUTPUT_FILE)
        ws = wb.get_sheet_by_name(name="Psxview Output")
        for col in xrange(1, 12):
            ws.cell("{0}{1}".format(get_column_letter(col),
                                    1)).style = BoldStyle
        for row in xrange(2, total + 1):
            for col in xrange(4, 11):
                if ws.cell("{0}{1}".format(get_column_letter(col),
                                           row)).value == "False":
                    ws.cell("{0}{1}".format(get_column_letter(col),
                                            row)).style = RedStyle
                else:
                    ws.cell("{0}{1}".format(get_column_letter(col),
                                            row)).style = GreenStyle
        wb.save(filename=self._config.OUTPUT_FILE)
Пример #39
0
def generate_excel(report, name, reporting_start_datetime_local,
                   reporting_end_datetime_local, period_type):
    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

    # Col width
    ws.column_dimensions['A'].width = 1.5

    ws.column_dimensions['B'].width = 25.0

    for i in range(ord('C'), ord('L')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000'))
    b_border = Border(bottom=Side(border_style='medium', color='00000000'), )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    # Img
    img = Image("excelexporters/myems.png")
    img.width = img.width * 0.85
    img.height = img.height * 0.85
    # img = Image("myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws.row_dimensions[3].height = 60

    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
    ws.merge_cells("G3:H3")

    if "reporting_period" not in report.keys() or \
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'
        wb.save(filename)

        return filename

    ##################################

    current_row_number = 6

    reporting_period_data = report['reporting_period']

    has_names_data_flag = True

    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_names_data_flag = False

    if has_names_data_flag:
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 报告期产出'

        current_row_number += 1

        category = reporting_period_data['names']
        ca_len = len(category)

        ws.row_dimensions[current_row_number].height = 60
        ws['B' + str(current_row_number)].fill = table_fill
        ws['B' + str(current_row_number)].border = f_border

        col = 'C'

        for i in range(0, ca_len):
            ws[col + str(current_row_number)].fill = table_fill
            ws[col + str(current_row_number)].font = name_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = \
                reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"

            col = chr(ord(col) + 1)

        current_row_number += 1

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)] = '产出'

        col = 'C'

        for i in range(0, ca_len):
            ws[col + str(current_row_number)].font = name_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = round(
                reporting_period_data['subtotals'][i], 2)

            col = chr(ord(col) + 1)

        current_row_number += 1

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)] = '环比'

        col = 'C'

        for i in range(0, ca_len):
            ws[col + str(current_row_number)].font = name_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = str(
                round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
                if reporting_period_data['increment_rates'][i] is not None else '-'

            col = chr(ord(col) + 1)

        current_row_number += 2

    ####################################

    has_values_data = True
    has_timestamps_data = True

    if 'values' not in reporting_period_data.keys() or \
            reporting_period_data['values'] is None or \
            len(reporting_period_data['values']) == 0:
        has_values_data = False

    if 'timestamps' not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0 or \
            len(reporting_period_data['timestamps'][0]) == 0:
        has_timestamps_data = False

    if has_values_data and has_timestamps_data:
        ca_len = len(reporting_period_data['names'])
        time = reporting_period_data['timestamps'][0]

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 详细数据'

        current_row_number += 1

        chart_start_row_number = current_row_number
        real_timestamps_len = timestamps_data_not_equal_0(
            report['parameters']['timestamps'])
        current_row_number += ca_len * 6 + real_timestamps_len * 7 + 2
        table_start_row_number = current_row_number

        ws.row_dimensions[current_row_number].height = 60
        ws['B' + str(current_row_number)].fill = table_fill
        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)] = '日期时间'

        col = 'C'

        for i in range(0, ca_len):
            ws[col + str(current_row_number)].fill = table_fill
            ws[col + str(current_row_number)].font = title_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = \
                reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
            col = chr(ord(col) + 1)

        current_row_number += 1

        for i in range(0, len(time)):
            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = time[i]

            col = 'C'
            for j in range(0, ca_len):
                ws[col + str(current_row_number)].font = title_font
                ws[col + str(current_row_number)].alignment = c_c_alignment
                ws[col + str(current_row_number)].border = f_border
                ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
                    if reporting_period_data['values'][j][i] is not None else 0.00
                col = chr(ord(col) + 1)

            current_row_number += 1

        table_end_row_number = current_row_number - 1

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)].alignment = c_c_alignment
        ws['B' + str(current_row_number)].border = f_border
        ws['B' + str(current_row_number)] = '小计'

        col = 'C'

        for i in range(0, ca_len):
            ws[col + str(current_row_number)].font = title_font
            ws[col + str(current_row_number)].alignment = c_c_alignment
            ws[col + str(current_row_number)].border = f_border
            ws[col + str(current_row_number)] = round(
                reporting_period_data['subtotals'][i], 2)
            col = chr(ord(col) + 1)

        current_row_number += 2

        format_time_width_number = 1.0
        min_len_number = 1.0
        min_width_number = 11.0  # format_time_width_number * min_len_number + 4 and min_width_number > 11.0

        if period_type == 'hourly':
            format_time_width_number = 4.0
            min_len_number = 2
            min_width_number = 12.0
        elif period_type == 'daily':
            format_time_width_number = 2.5
            min_len_number = 4
            min_width_number = 14.0
        elif period_type == 'monthly':
            format_time_width_number = 2.1
            min_len_number = 4
            min_width_number = 12.4
        elif period_type == 'yearly':
            format_time_width_number = 1.5
            min_len_number = 5
            min_width_number = 11.5

        for i in range(0, ca_len):
            line = LineChart()
            line.title = '报告期产出 - ' + \
                         reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
            labels = Reference(ws,
                               min_col=2,
                               min_row=table_start_row_number + 1,
                               max_row=table_end_row_number)
            line_data = Reference(ws,
                                  min_col=3 + i,
                                  min_row=table_start_row_number,
                                  max_row=table_end_row_number)
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = format_time_width_number * len(time) if len(
                time) > min_len_number else min_width_number
            if line.width > 24:
                line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = True
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

    ####################################################
    current_sheet_parameters_row_number = chart_start_row_number + 1
    has_parameters_names_and_timestamps_and_values_data = True
    if 'parameters' not in report.keys() or \
            report['parameters'] is None or \
            'names' not in report['parameters'].keys() or \
            report['parameters']['names'] is None or \
            len(report['parameters']['names']) == 0 or \
            'timestamps' not in report['parameters'].keys() or \
            report['parameters']['timestamps'] is None or \
            len(report['parameters']['timestamps']) == 0 or \
            'values' not in report['parameters'].keys() or \
            report['parameters']['values'] is None or \
            len(report['parameters']['values']) == 0 or \
            timestamps_data_all_equal_0(report['parameters']['timestamps']):
        has_parameters_names_and_timestamps_and_values_data = False
    if has_parameters_names_and_timestamps_and_values_data:

        ###############################
        # new worksheet
        ###############################

        parameters_data = report['parameters']
        parameters_names_len = len(parameters_data['names'])

        parameters_ws = wb.create_sheet('相关参数')

        parameters_timestamps_data_max_len = \
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))

        # Row height
        parameters_ws.row_dimensions[1].height = 102
        for i in range(2, 7 + 1):
            parameters_ws.row_dimensions[i].height = 42

        for i in range(8, parameters_timestamps_data_max_len + 10):
            parameters_ws.row_dimensions[i].height = 60

        # Col width
        parameters_ws.column_dimensions['A'].width = 1.5

        parameters_ws.column_dimensions['B'].width = 25.0

        for i in range(3, 12 + parameters_names_len * 3):
            parameters_ws.column_dimensions[format_cell.get_column_letter(
                i)].width = 15.0

        # Img
        img = Image("excelexporters/myems.png")
        img.width = img.width * 0.85
        img.height = img.height * 0.85
        # img = Image("myems.png")
        parameters_ws.add_image(img, 'B1')

        # Title
        parameters_ws.row_dimensions[3].height = 60

        parameters_ws['B3'].font = name_font
        parameters_ws['B3'].alignment = b_r_alignment
        parameters_ws['B3'] = 'Name:'
        parameters_ws['C3'].border = b_border
        parameters_ws['C3'].alignment = b_c_alignment
        parameters_ws['C3'].font = name_font
        parameters_ws['C3'] = name

        parameters_ws['D3'].font = name_font
        parameters_ws['D3'].alignment = b_r_alignment
        parameters_ws['D3'] = 'Period:'
        parameters_ws['E3'].border = b_border
        parameters_ws['E3'].alignment = b_c_alignment
        parameters_ws['E3'].font = name_font
        parameters_ws['E3'] = period_type

        parameters_ws['F3'].font = name_font
        parameters_ws['F3'].alignment = b_r_alignment
        parameters_ws['F3'] = 'Date:'
        parameters_ws['G3'].border = b_border
        parameters_ws['G3'].alignment = b_c_alignment
        parameters_ws['G3'].font = name_font
        parameters_ws[
            'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
        parameters_ws.merge_cells("G3:H3")

        parameters_ws_current_row_number = 6

        parameters_ws['B' +
                      str(parameters_ws_current_row_number)].font = title_font
        parameters_ws['B' +
                      str(parameters_ws_current_row_number)] = name + ' 相关参数'

        parameters_ws_current_row_number += 1

        parameters_table_start_row_number = parameters_ws_current_row_number

        parameters_ws.row_dimensions[
            parameters_ws_current_row_number].height = 80

        parameters_ws_current_row_number += 1

        table_current_col_number = 'B'

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].fill = table_fill
            parameters_ws[table_current_col_number +
                          str(parameters_ws_current_row_number -
                              1)].border = f_border

            col = chr(ord(table_current_col_number) + 1)

            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].border = f_border
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].font = name_font
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)].alignment = c_c_alignment
            parameters_ws[col + str(parameters_ws_current_row_number -
                                    1)] = parameters_data['names'][i]

            table_current_row_number = parameters_ws_current_row_number

            for j, value in enumerate(list(parameters_data['timestamps'][i])):
                col = table_current_col_number

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = value

                col = chr(ord(col) + 1)

                parameters_ws[col +
                              str(table_current_row_number)].border = f_border
                parameters_ws[col +
                              str(table_current_row_number)].font = title_font
                parameters_ws[
                    col +
                    str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = round(
                    parameters_data['values'][i][j], 2)

                table_current_row_number += 1

            table_current_col_number = chr(ord(table_current_col_number) + 3)

        ########################################################
        # parameters chart and parameters table
        ########################################################

        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
        ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'

        current_sheet_parameters_row_number += 1

        chart_start_row_number = current_sheet_parameters_row_number

        col_index = 0

        for i in range(0, parameters_names_len):

            if len(parameters_data['timestamps'][i]) == 0:
                continue

            line = LineChart()
            data_col = 3 + col_index * 3
            labels_col = 2 + col_index * 3
            col_index += 1
            line.title = '相关参数 - ' + \
                         parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
            labels = Reference(parameters_ws,
                               min_col=labels_col,
                               min_row=parameters_table_start_row_number + 1,
                               max_row=(len(parameters_data['timestamps'][i]) +
                                        parameters_table_start_row_number))
            line_data = Reference(
                parameters_ws,
                min_col=data_col,
                min_row=parameters_table_start_row_number,
                max_row=(len(parameters_data['timestamps'][i]) +
                         parameters_table_start_row_number))
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = False
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

        current_sheet_parameters_row_number = chart_start_row_number

        current_sheet_parameters_row_number += 1
    ####################################################
    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename
Пример #40
0
 def parse_borders(self):
     """Read in the boarders"""
     borders = self.root.findall('{%s}borders/{%s}border' % (SHEET_MAIN_NS, SHEET_MAIN_NS))
     for border_node in borders:
         yield Border.from_tree(border_node)
def build_consolidated_bs(wb, dict_db, date_end):
    wb_cur = wb
    date_prior_ye = (date_end +
                     relativedelta.relativedelta(months=-date_end.month))

    gl_data = retrieve_bs_data(dict_db)
    assets = sorted(set([x[1] for x in gl_data if x[0] == 'Asset']))
    liabilities = sorted(set([x[1] for x in gl_data if x[0] == 'Liability']))
    equities = sorted(set([x[1] for x in gl_data if x[0] == 'Equity']))

    ws_cur = wb_cur.create_sheet('BS_RAC')
    c1 = ws_cur.cell(row=1, column=1, value='Balance Sheet - RAC')
    c1.font = Font(bold='true')
    c1.alignment = Alignment(horizontal='center')

    ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=3)

    ws_cur.cell(row=3,
                column=2,
                value=datetime.strftime(date_end, '%B %d, %Y'))
    ws_cur.cell(row=3,
                column=3,
                value=datetime.strftime(date_prior_ye, '%B %d, %Y'))

    for c in range(2, 4):
        c1 = ws_cur.cell(row=3, column=c)
        c1.fill = PatternFill(start_color='1e90ff',
                              end_color='1e90ff',
                              fill_type='solid')
        c1.font = Font(bold='true', color='f8f8ff')
        c1.alignment = Alignment(horizontal='center')

    r_next = 4
    c1 = ws_cur.cell(row=r_next, column=1, value='ASSETS:')
    c1.font = Font(bold='true')

    r_next += 1

    #Border Style
    top_border = Border(top=Side(style='thin'))

    #Assets
    r = 0

    for a in assets:
        ws_cur.cell(row=r_next + r, column=1, value=a)
        cur_period_bal = sum([
            x[3] for x in gl_data
            if x[0] == 'Asset' and x[1] == a and x[2] == date_end
        ])
        ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
        py_end_bal = sum([
            x[3] for x in gl_data
            if x[0] == 'Asset' and x[1] == a and x[2] == date_prior_ye
        ])
        ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
        r += 1

    r_next = r_next + r
    r_asset = r_next

    c1 = ws_cur.cell(row=r_next, column=1, value='Total Assets')
    c1.font = Font(bold='true')

    formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(assets),
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(assets),
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    #Liabilities
    r_next += 2
    c1 = ws_cur.cell(row=r_next, column=1, value='LIABILITIES AND EQUITY:')
    c1.font = Font(bold='true')

    r_next += 1
    r = 0

    for liab in liabilities:
        ws_cur.cell(row=r_next + r, column=1, value=liab)
        cur_period_bal = -sum([
            x[3] for x in gl_data
            if x[0] == 'Liability' and x[1] == liab and x[2] == date_end
        ])
        ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
        py_end_bal = -sum([
            x[3] for x in gl_data
            if x[0] == 'Liability' and x[1] == liab and x[2] == date_prior_ye
        ])
        ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
        r += 1

    r_next = r_next + r
    r_liability = r_next
    c1 = ws_cur.cell(row=r_next, column=1, value='Total Liabilities')
    c1.font = Font(bold='true')

    formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(liabilities),
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(liabilities),
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    #Equity
    r_next += 2
    r = 0

    for e in equities:
        ws_cur.cell(row=r_next + r, column=1, value=e)
        cur_period_bal = -sum([
            x[3] for x in gl_data
            if x[0] == 'Equity' and x[1] == e and x[2] == date_end
        ])
        ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
        py_end_bal = -sum([
            x[3] for x in gl_data
            if x[0] == 'Equity' and x[1] == e and x[2] == date_prior_ye
        ])
        ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
        r += 1

    #Net Income
    r_next = r_next + r
    r_net_income = r_next
    r_retained_earning = r_net_income - 1
    ws_cur.cell(row=r_next, column=1, value='Net Income')
    cur_period_bal = sum([
        x[3] for x in gl_data
        if x[0] == 'Net Income' and x[1] == 'Net Income' and x[2] == date_end
    ])
    ws_cur.cell(row=r_next, column=2, value=cur_period_bal)
    py_end_bal = sum([
        x[3] for x in gl_data if x[0] == 'Net Income' and x[1] == 'Net Income'
        and x[2] == date_prior_ye
    ])
    ws_cur.cell(row=r_next, column=3, value=py_end_bal)

    r_next += 1
    r_equity = r_next
    c1 = ws_cur.cell(row=r_next, column=1, value='Total Equity')
    c1.font = Font(bold='true')

    formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(equities) - 1,
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(equities) - 1,
                                          r2=r_next - 1)
    c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
    c1.font = Font(bold='true')
    c1.border = top_border

    #Total Liabilities and Equity
    r_next += 2
    c1 = ws_cur.cell(row=r_next,
                     column=1,
                     value='Total Liabilities and Equity')
    c1.font = Font(bold='true')

    formula1 = '=B{r1}+B{r2}'.format(r1=r_liability, r2=r_equity)
    c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
    c1.font = Font(bold='true')

    formula1 = '=C{r1}+C{r2}'.format(r1=r_liability, r2=r_equity)
    c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
    c1.font = Font(bold='true')

    r_next += 2
    c1 = ws_cur.cell(row=r_next, column=1, value='Accounting Equation')
    c1.font = Font(bold='true')

    formula1 = '=B{r1}-B{r2}'.format(r1=r_asset, r2=r_next - 2)
    c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
    c1.font = Font(bold='true')

    formula1 = '=C{r1}-C{r2}'.format(r1=r_asset, r2=r_next - 2)
    c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
    c1.font = Font(bold='true')

    #Reclass non YTD Net Income to Retained Earnings---------------------------
    net_income_cur = ws_cur.cell(row=r_net_income, column=2).value
    net_income_cur_ytd = sum([
        x[3] for x in gl_data if x[0] == 'Net Income YTD'
        and x[1] == 'Net Income YTD' and x[2] == date_end
    ])
    net_income_remainder = net_income_cur - net_income_cur_ytd
    retained_earning_cur = ws_cur.cell(row=r_retained_earning, column=2).value
    retained_earning_new = retained_earning_cur + net_income_remainder
    ws_cur.cell(row=r_retained_earning, column=2, value=retained_earning_new)
    ws_cur.cell(row=r_net_income, column=2, value=net_income_cur_ytd)

    net_income_pye = ws_cur.cell(row=r_net_income, column=3).value
    net_income_py_ytd = sum([
        x[3] for x in gl_data if x[0] == 'Net Income YTD'
        and x[1] == 'Net Income YTD' and x[2] == date_prior_ye
    ])
    net_income_remainder = net_income_pye - net_income_py_ytd
    retained_earning_pye = ws_cur.cell(row=r_retained_earning, column=3).value
    retained_earning_new = retained_earning_pye + net_income_remainder
    ws_cur.cell(row=r_retained_earning, column=3, value=retained_earning_new)
    ws_cur.cell(row=r_net_income, column=3, value=net_income_py_ytd)
    #-------------------------------------------------------------------------

    #Format Cells
    format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'

    for r in range(5, ws_cur.max_row + 1):
        for c in range(2, 4):
            ws_cur.cell(row=r, column=c).number_format = format_number

    #Column widths
    ws_cur.column_dimensions['A'].width = 44
    ws_cur.column_dimensions['B'].width = 20
    ws_cur.column_dimensions['C'].width = 20

    #Page Setup
    ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
    ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
    ws_cur.page_setup.fitToPage = True
    ws_cur.page_setup.fitToHeight = False
    ws_cur.page_setup.fitToWidth = 1
    ws_cur.print_options.horizontalCentered = True
    ws_cur.add_print_title(3)
    ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)

    #Freeze Panes
    c1 = ws_cur.cell(row=4, column=1)
    ws_cur.freeze_panes = c1

    return wb_cur