Python Workbook.add_format方法代码示例

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def write_cell_errors(xlsxname, header, lines, cells_to_mark):
    workbook  = Workbook(join(SPREADSHEETS_DIR, xlsxname))
    cell_fmt  = workbook.add_format()
    cell_fmt.set_bg_color('cyan')
    worksheet = workbook.add_worksheet()
    write_sheet(worksheet, cell_fmt, header, lines, cells_to_mark)
    workbook.close()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def write_mismatch(filename, etalon, tocomp, mismatch):
    workbook  = Workbook(join(SPREADSHEETS_DIR, get_filebase(filename)+'.xlsx'))
    cell_fmt  = workbook.add_format()
    cell_fmt.set_bg_color('cyan')
    worksheet = workbook.add_worksheet(name='test')
    write_sheet(worksheet, cell_fmt, *tocomp, cells_to_mark=mismatch)
    worksheet = workbook.add_worksheet(name='etalon')
    write_sheet(worksheet, cell_fmt, *etalon)
    workbook.close()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def dispatch(dataset):
    buf = StringIO()
    wb = Workbook(buf,
                  {'constant_memory': True,
                   'in_memory': True,
                   'default_date_format': 'yyyy/mm/dd hh:mm:ss'}
                 )
    format_ = wb.add_format(FORMAT)

    # dataset metadata
    ws = wb.add_worksheet('Global attributes')
    write_metadata(ws, dataset, 0, 0, format_)

    # 1D grids
    for grid in [g for g in walk(dataset, GridType) if len(g.shape) == 1]:
        logger.debug('Grid {}'.format(grid.name))
        ws = wb.add_worksheet(grid.name)

        # headers
        ws.write(0, 0, grid.dimensions[0], format_)
        ws.write(0, 1, grid.name, format_)

        # data
        for j, data in enumerate(grid.data):
            for i, value in enumerate(numpy.asarray(data)):
                ws.write(i+1, 1-j, value)

        # add var metadata
        write_metadata(ws, grid, 0, 2, format_)

    # sequences
    for seq in walk(dataset, SequenceType):
        logger.debug('Sequence {}'.format(seq.name))

        ws = wb.add_worksheet(seq.name)

        # add header across the first row
        for j, var_ in enumerate(seq.keys()):
            ws.write(0, j, var_, format_)

        # add data in the subsequent rows
        for i, row in enumerate(seq.data):
            for j, value in enumerate(row):
                ws.write(i+1, j, value)

        # add var metadata in columns to the right of the data
        n = 0
        j = len(seq.keys())+1
        for child in seq.children():
            logger.debug("Child {}".format(child.name))
            ws.merge_range(n, j, n, j+1, child.name, format_)
            n = write_metadata(ws, child, n+1, j, format_)+1

    wb.close()
    return buf

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
    def genera_e_salva(self, nome='File.xlsx', scadenza=None, ordina_fogli=True, save_to_memory=False):
        """
        Genera il file e lo salva su database.
        :param nome: Il nome del file da allegare (opzionale, default 'File.xlsx').
        :param scadenza: Scadenza del file. Domani.
        :return:
        """

        generatore = GeneratoreNomeFile('allegati/')
        zname = generatore(self, nome)

        self.prepara_cartelle(MEDIA_ROOT + zname)

        if save_to_memory:
            workbook = Workbook(self.output)
        else:
            workbook = Workbook(MEDIA_ROOT + zname)

        bold = workbook.add_format({'bold': True})

        # Per ogni foglio
        for foglio in [x for x in self.fogli]:

            # Aggiunge il foglio
            worksheet = workbook.add_worksheet(foglio.nome)

            # Aggiunge l'intestazione
            for col, testo in enumerate(foglio.intestazione):
                worksheet.write(0, col, str(testo), bold)

            # Aggiunge il contenuto
            for riga, colonne in enumerate(foglio.contenuto):
                riga += 1  # Indice shiftato per intestazione
                for colonna, testo in enumerate(colonne):
                    if isinstance(testo, datetime):
                        testo = testo.strftime("%d/%m/%Y %H:%M")
                    if isinstance(testo, date):
                        testo = testo.strftime("%d/%m/%Y")
                    if testo == ", ":  # Rimuove campi ', '
                        testo = ""
                    worksheet.write(riga, colonna, str(testo))

        if ordina_fogli:
            workbook.worksheets_objs.sort(key=lambda x: x.name)

        workbook.close()

        if save_to_memory:
            self.output.seek(0)
        else:
            self.file = zname
            self.nome = nome
            self.scadenza = scadenza or domani()
            self.save()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def generar_layout(datos, salida):
    wb = Workbook(salida)
    ws = wb.add_worksheet('Layout')
    encabezados = '''
Nombre
Inicio
Longitud
Tipo de dato capturado
    '''.split('\n')[1:-1]

    # Escribir encabezados
    col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper()
    col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper()
    format_enc = wb.add_format({'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg})
    format_ren1 = wb.add_format({'border': 1})
    format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2})
    for col, encabezado in enumerate(encabezados):
        ws.write(0, col, encabezado, format_enc)

    # Escribir datos del diccionario    
    for renglon, registro in enumerate(datos, 1):
        formato = format_ren1 if renglon % 2 == 0 else format_ren2
        # Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap')
        ws.write(renglon, 0, registro.etiqueta, formato)
        ws.write(renglon, 1, int(registro.inicio), formato)
        ws.write(renglon, 2, int(registro.longitud), formato)
        ws.write(renglon, 3, registro.tipo_cap, formato)


    # Aplicando formato a la hoja
    ws.freeze_panes(1, 0)
    ws.autofilter('A1:D1')
    ws.set_column(0, 0, 55)
    ws.set_column(1, 1, 8)
    ws.set_column(2, 2, 10)
    ws.set_column(3, 3, 25)
    ws.hide_gridlines(2)
    wb.close()
    click.launch(salida)

    print("Layout generado")

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def medicamentos_topOrganizacionesPorPedidoExcel(request):
    datos = request.session['estadistica']['excel']['datos']
    medicamento = request.session['estadistica']['excel']['medicamento']
    excel = io.BytesIO()
    workbook = Workbook(excel, {'in_memory': True})
    worksheet = workbook.add_worksheet()
    titulo = workbook.add_format({
        'font_name':'Arial',
        'font_size': 12,
        'font_color': 'navy',
        'bold': True
    })
    encabezado = workbook.add_format({
        'font_name': 'Arial',
        'bold': True
    })
    alignLeft = workbook.add_format({
        'align': 'left',
    })
    worksheet.write('A1:B1', 'Organizaciones mas demandantes del medicamento '+medicamento+' (por pedido)', titulo)

    worksheet.set_column('B:B', 40)
    worksheet.set_column('C:C', 20)
    worksheet.write('A2', '#', encabezado)
    worksheet.write('B2', 'Organizacion', encabezado)
    worksheet.write('C2', 'Cantidad', encabezado)
    fila = 2
    tope = len(datos)
    for i in range(0, tope):
        worksheet.write(fila, 0, i + 1, alignLeft)
        worksheet.write(fila, 1, datos[i]['organizacion'], alignLeft)
        worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft)
        fila += 1
    workbook.close()

    excel.seek(0)

    response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response['Content-Disposition'] = "attachment; filename=OrganizacionesMasDemandantesDeMedicamentoPorPedido.xlsx"
    return response

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def pedidosAlaboratorio_topLabConMasSolicitudesPedidosExcel(request):
    datos = request.session['estadistica']['excel']
    excel = io.BytesIO()
    workbook = Workbook(excel, {'in_memory': True})
    worksheet = workbook.add_worksheet()
    titulo = workbook.add_format({
        'font_name':'Arial',
        'font_size': 12,
        'font_color': 'navy',
        'bold': True
    })
    encabezado = workbook.add_format({
        'font_name':'Arial',
        'bold': True
    })
    alignLeft = workbook.add_format({
        'align':'left',
    })
    worksheet.write('A1:B1', 'Laboratorios con mas solicitudes (por medicamento)', titulo)

    worksheet.set_column('B:B', 40)
    worksheet.set_column('C:C', 20)
    worksheet.write('A2', '#', encabezado)
    worksheet.write('B2', 'Laboratorio', encabezado)
    worksheet.write('C2', 'Cantidad', encabezado)
    fila = 2
    tope = len(datos)
    for i in range(0, tope):
        worksheet.write(fila, 0, i + 1, alignLeft)
        worksheet.write(fila, 1, datos[i]['laboratorio'], alignLeft)
        worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft)
        fila += 1
    workbook.close()

    excel.seek(0)

    response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response['Content-Disposition'] = "attachment; filename=LaboratoriosConMasSolicitudesDeMedicamentos.xlsx"
    return response

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def wr_xlsx(fout_xlsx, xlsx_data, **kws):
    """Write a spreadsheet into a xlsx file."""
    if xlsx_data:
        from xlsxwriter import Workbook
        workbook = Workbook(fout_xlsx)
        worksheet = workbook.add_worksheet()
        # Cell formatting
        fmt_hdr = workbook.add_format({'top':1, 'bottom':1, 'left':0, 'right':0, 'bold':True})
        fmt_txt = workbook.add_format({'border':0})
        flds = xlsx_data[0]._fields
        if 'fld2col_widths' in kws:
            _set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds)
        # Print header
        hdrs = flds if 'hdrs' not in kws else kws['hdrs']
        for col_idx, hdr in enumerate(hdrs):
            worksheet.write(0, col_idx, hdr, fmt_hdr)
        row_data_start = 1
        # Print data
        row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt, **kws)
        workbook.close()
        sys.stdout.write("  {:>5} items WROTE: {}\n".format(row_idx-row_data_start, fout_xlsx))
    else:
        sys.stdout.write("      0 items. NOT WRITING {}\n".format(fout_xlsx))

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
    def toExcel(self, outFileName):
        """
        Write the distribution fitter results to an Excel spreadsheet
        :param outFileName: output spreadsheet name
        :type  outFileName: str
        :return: Nothing
        :rtype:  None
        """
        workbook = Workbook(outFileName, {'constant_memory': True})
        workbook.use_zip64() # allow large size Excels just in case

        wks    = workbook.add_worksheet('Distribution Fitting')
        hdrFmt = workbook.add_format({'bold'      : True,
                                      'underline' : True,
                                      'align'     : 'center'})
        resultFormats = [workbook.add_format({'num_format' : fmtStr}) \
                             for fmtStr in ['0.000000', '0.0000%']]

        row = 0
        wks.set_column(0, 0, 11)
        wks.set_column(1, 1,  8,   resultFormats[0])
        wks.set_column(2, 2, 10.6, resultFormats[1])
        for col, headerName in enumerate(self.getHeaderList()):
            wks.write_string(row, col, headerName, hdrFmt)

        for distrName, (results, params) in self.result.iteritems():
            row += 1
            col = 0
            wks.write_string(row, col, distrName)
            for col, (result, outFormat) in \
                     enumerate(itertools.izip(results, resultFormats), col+1):
                wks.write_number(row, col, result, outFormat)
            for col, paramValue in enumerate(params, col+1):
                wks.write_number(row, col, paramValue)

        workbook.close()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
	def get_query_result_excel(self, sql, excelFile, header='True'):
		'''
			Build an excel file from result set of the sql command \n
			Example: get query result excel		select * from customer		test.xlsx
		'''
		
		cur = self._dbconnection.cursor()
		cur.execute(sql + ';')
		rows = cur.fetchall()
		print(cur.description)
		
		workbook = Workbook(excelFile)
		sheet = workbook.add_worksheet()
		format = workbook.add_format({'bold': True})
		
		if header.lower() == 'true':
			for i, val in enumerate([column[0] for column in cur.description]): sheet.write(0, i, self._f(val), format)	#Write header column
		for r, row in enumerate(rows):
			for c, s in enumerate(row): sheet.write(r+1, c, self._f(s)) # Write table data
		workbook.close()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def xlsx_generate(file_name, sheets):
    log.info(' -> %s', file_name)
    with open(file_name, 'w') as fh:
        workbook = Workbook(fh)
        header_format = workbook.add_format({
            'bold': True,
            'border': 1
        })

        for title, rows in sheets:
            if not len(rows):
                continue
            headers = get_headers(rows)
            if not len(headers):
                continue

            worksheet = workbook.add_worksheet(title)
            worksheet.strings_to_numbers = False
            worksheet.strings_to_urls = False
            worksheet.strings_to_formulas = False

            row_idx = 0
            headers = None
            for row in rows:
                if headers is None:
                    headers = row.keys()
                    for c, cell in enumerate(headers):
                        worksheet.write(row_idx, c, cell, header_format)
                    row_idx += 1

                col_idx = 0
                for cell in headers:
                    worksheet.write(row_idx, col_idx, row.get(cell))
                    col_idx += 1
                row_idx += 1

            worksheet.freeze_panes(1, 0)
        workbook.close()

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def generate_change_template(request):
    stream = BytesIO()
    book = Workbook(stream)
    bold = book.add_format({'bold': True})
    # create sheet for user input
    sheet = book.add_worksheet('Updates')
    # set headers
    sheet.write('A1', 'category (like destID)', bold)
    sheet.write('B1', 'title (like input cat)', bold)
    sheet.write('C1', 'url (like output cat)', bold)
    rows = 400
    # no idead
    sheet.data_validation(1, 1, rows, 1, {
        'validate': 'list', 
        'source': 'Valid_Categories'
    })

    # set width
    sheet.set_column('A:A', 25)
    sheet.set_column('B:B', 25)
    sheet.set_column('C:C', 25)

    # Create sheet containing data for user validations
    # sheet = book.add_worksheet('Validations')
    # sheet.write('A1', 'Categories', bold)
    categories = Category.objects.all()
    for i, cat in enumerate(categories):
        sheet.write(i + 1, 0, cat.slug)
    # book.define_name('Valid_Categories', '=Validations!$A$2:$A${}'.format(1 + categories.count()))
    # sheet.set_column('A:A', 10)
    # sheet.set_column('B:B', 2)

    book.close()
    stream.seek(0)
    response = HttpResponse(stream,
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachement; filename="page_add_template.xlsx"'
    return response

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def make_xlsx_response(sheets, basename):
    output = StringIO()
    workbook = Workbook(output)
    header_format = workbook.add_format({
        'bold': True,
        'border': 1
    })

    for title, rows in sheets.items():
        if not len(rows):
            continue

        worksheet = workbook.add_worksheet(title)

        row_idx = 0
        headers = None
        for row in rows:
            if headers is None:
                headers = row.keys()
                for c, cell in enumerate(headers):
                    worksheet.write(row_idx, c, cell, header_format)
                row_idx += 1

            col_idx = 0
            for cell in headers:
                worksheet.write(row_idx, col_idx, row.get(cell))
                col_idx += 1
            row_idx += 1

        worksheet.freeze_panes(1, 0)

    workbook.close()
    output.seek(0)
    fn = basename + '.xlsx'
    headers = {'Content-Disposition': 'attachment; filename=%s' % fn}
    return Response(output, mimetype=XLSX_MIME, headers=headers)

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
def testYieldCurveFwdsGeneration():

    # ==========================================================================
    # A_Auto: USD_IRS_LIBOR_3M

    # Test based on Auto Zero rates
    # zeroRates=[0.00233531255143132,
    #            0.00233531255143297,
    #            0.00233531255143352,
    #            0.00233531255143333,
    #            0.00297455023730465,
    #            0.00319723236979883,
    #            0.00330765830399476,
    #            0.00691586090809845,
    #            0.01085698247648050,
    #            0.01394073189902450,
    #            0.01633917521905550,
    #            0.02000659612723410,
    #            0.02346660204636610,
    #            0.02685361531988290,
    #            0.02854781579990930,
    #            0.02975553109452430]

    # Test based on Manual Zero rates
    zeroRates = [
                0.00236843676021893,
                0.00236821394576394,
                0.00236797582662841,
                0.00236773777132554,
                0.00290042675432344,
                0.00308558288679761,
                0.00330592988104989,
                0.00691281016439806,
                0.01085462048320750,
                0.01393891008113780,
                0.01633775879077160,
                0.02000467532244330,
                0.02346505198474200,
                0.02685243199737300,
                0.02854687951971270,
                0.02975479160795850
    ]

    yieldCurve = FYC.yieldCurveFactory(times=ZERO_TIMES,
                                       zeros=zeroRates,
                                       type='linearFwd')

    runTime = dt.strptime('20141128.230000', dtFmtLog)
    runDate = runTime.date() # same as outDates

    # make sure the first out date is the end of the following month
    nextDate = Cal.NextEndOfMonth(runDate)
    startDate = nextDate if nextDate.month == runDate.month else runDate

    dfltFwdDates = SortedList(unfoldGen(func     = Cal.NextEndOfMonth,
                                        initArg  = startDate,
                                        numSteps = NUMBER_FWD_MONTHS))

    cal = Cal()

    fwdDates = [[cal.AddSplitTenor(baseDate  = baseDate,
                                    tenorSize = x[0],
                                    tenorUnit = x[1])
                  for x in [[0,'D']] + TENORS]
                      for baseDate in dfltFwdDates]

    fwdTimes = [cal.DateTimesToFloats(dateTimes = fwdDateList,
                                       baseDate = runDate)
                for fwdDateList in fwdDates]


    outFwds = np.empty((len(fwdTimes[0])-1,NUMBER_FWD_MONTHS))

    for idx, fwdTimesAtDate in enumerate(fwdTimes):
        outFwds[:,idx] \
            = yieldCurve.getFwds(
                    times    = fwdTimesAtDate[1:],
                    baseTime = fwdTimesAtDate[0])

    rateTable =  np.column_stack((zeroRates, outFwds))
    # outFwds = np.maximum(0., outFwds)

    workbook = Workbook('tests/test_YieldCurve.xlsx',{'constant_memory': True})

    headerFormat     = workbook.add_format({'bold'     : True,
                                            'font_name': 'Arial',
                                            'font_size': 8})

    rateFormat       = workbook.add_format({'num_format': '0.000%',
                                            'font_name' : 'Arial',
                                            'font_size' : 8,
                                            'border'    : 1})

    stringFormat     = workbook.add_format({'font_name' : 'Arial',
                                            'font_size' : 8,
                                            'border'    : 1})

    dateFormat       = workbook.add_format({'bold'      : False,
                                            'num_format': 'mm/dd/yyyy',
                                            'font_name' : 'Arial',
#.........这里部分代码省略.........

# 需要导入模块: from xlsxwriter import Workbook [as 别名]
# 或者: from xlsxwriter.Workbook import add_format [as 别名]
    def process(self, files):
        # Get the filename from the already-open file handle
        filename = self.fout.name

        # Close the existing file as the XlsxWriter library handles that for us
        self.fout.close()

        # Create the new workbook
        workbook = Workbook(filename)

        # Define some common styles/ formats
        bold = workbook.add_format({
            'bold': 1,
        })
        
        # Create the main worksheet where all the raw data is shown
        main_sheet = workbook.add_worksheet('Raw Data')

        # Write the header
        main_sheet.write(0, 0, 'IP', bold) # The IP field can be either ip_str or ipv6 so we treat it differently
        main_sheet.set_column(0, 0, 20)
        
        row = 0
        col = 1
        for field in self.fields:
            name = self.field_names.get(field, field.capitalize())
            main_sheet.write(row, col, name, bold)
            col += 1
        row += 1

        total = 0
        ports = defaultdict(int)
        for banner in iterate_files(files):
            try:
                # Build the list that contains all the relevant values
                data = []
                for field in self.fields:
                    value = self.banner_field(banner, field)
                    data.append(value)
                
                # Write those values to the main workbook
                # Starting off w/ the special "IP" property
                main_sheet.write_string(row, 0, get_ip(banner))
                col = 1

                for value in data:
                    main_sheet.write(row, col, value)
                    col += 1
                row += 1
            except Exception:
                pass
            
            # Aggregate summary information
            total += 1
            ports[banner['port']] += 1
        
        summary_sheet = workbook.add_worksheet('Summary')
        summary_sheet.write(0, 0, 'Total', bold)
        summary_sheet.write(0, 1, total)

        # Ports Distribution
        summary_sheet.write(0, 3, 'Ports Distribution', bold)
        row = 1
        col = 3
        for key, value in sorted(ports.items(), reverse=True, key=lambda kv: (kv[1], kv[0])):
            summary_sheet.write(row, col, key)
            summary_sheet.write(row, col + 1, value)
            row += 1