Python Workbook.add_worksheet方法代碼示例

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [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_worksheet [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_worksheet [as 別名]
def dump_info_xlsx():
    from xlsxwriter import Workbook
    
    wb = Workbook(configuration['outputfile'] + '.xlsx')
    ws = wb.add_worksheet("CollatedInfo")
    
    ordered_list = 'Name,Matric,Team,MainLineCount,MainWordCount,TestLineCount,TestWordCount,DocsLineCount,DocsWordCount,MainFile,TestFile,DocsFile'.split(",")
    
    row = 0
    for header in ordered_list:
        col = ordered_list.index(header)
        ws.write(row, col, header)
        
    row = 1
    
    for matric in sorted(studentlist.keys()):
        for k , v in studentlist[matric].items():
            if k == 'MainFile':
                pass
            elif k == 'Main':
                pass
            elif k == 'TestFile':
                pass
            elif k == 'Test':
                pass
            col = ordered_list.index(k)
            ws.write(row, col, v)
        row += 1
        
    wb.close()

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
def exportExcelDirect(request, adict):
    try:
        l_parm = adict['ex_parm']
        l_parm['title']  # : '文檔標題', 第一行各列合並居中寫入title
        l_parm['cols']   # :['列名1','列名2','列名3'], //列標題
        l_parm['rows']   # :[      ['行1列1','行1列2','行1列3'],       #         ['行2列1','行2列2','行2列3'],
        import io
        from xlsxwriter import Workbook
        output = io.BytesIO()
        workbook = Workbook(output)
        worksheet = workbook.add_worksheet('export')
        worksheet.write('A1',l_parm['title'])
        # Text with formatting.              ..........  write( row, cols, content )
        for i in range(len(l_parm['cols'])):
            worksheet.write(1, i, l_parm['cols'][i])
        for i in range(len(l_parm['rows'] )):
            for j in range(len(l_parm['rows'][i])):
                worksheet.write(i + 2, j, l_parm['rows'][i][j])
        workbook.close()
        output.seek(0)
        response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    except Exception as e:
        ls_err = str(e.args)
        response = HttpResponse(str("導出excel失敗：" + ls_err), content_type="application/text")

    response['Content-Disposition'] = "attachment; filename=test.xlsx"
    return response

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [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_worksheet [as 別名]
def download_as_excel(data):
    output = StringIO.StringIO()
    wb = Workbook(output)
    ws = wb.add_worksheet("Sheet 1")
    first_row = 0
    for header in data['table_header']:

        col = data['table_header'].index(header) # we are keeping order.
        if header in ['order__'+each for each in OrderHeader._meta.get_all_field_names()]:
            header=header.split('__')[1]
            header=header.replace('_',' ')
            header=header.capitalize()
            print 'header----',header
        else:
            
            header=header.capitalize()
            header=header.replace('_',' ')
        ws.write(first_row,col,header) # we have written first row which is the header of worksheet also.
    srow=1

    for row in data['table_data']:

        for _key,_value in row.items():
            col = data['table_header'].index(_key)
            try:
                ws.write(srow,col,_value)
            except Exception, e:

                ws.write(srow,col, json.dumps(_value))

        srow += 1 #enter the next row

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
def verified(context, collaborator, test, outpath=None):
    """Export variants which have been verified for an institute
        and write them to an excel file.

    Args:
        collaborator(str): institute id
        test(bool): True if the function is called for testing purposes
        outpath(str): path to output file

    Returns:
        written_files(int): number of written or simulated files
    """
    written_files = 0
    collaborator = collaborator or 'cust000'
    LOG.info('Exporting verified variants for cust {}'.format(collaborator))

    adapter = context.obj['adapter']
    verified_vars = adapter.verified(institute_id=collaborator)
    LOG.info('FOUND {} verified variants for institute {}'.format(len(verified_vars), collaborator))


    if not verified_vars:
        LOG.warning('There are no verified variants for institute {} in database!'.format(collaborator))
        return None

    document_lines = export_verified_variants(verified_vars)

    today = datetime.datetime.now().strftime('%Y-%m-%d')
    document_name = '.'.join(['verified_variants', collaborator, today]) + '.xlsx'

    # If this was a test and lines are created return success
    if test and document_lines:
        written_files +=1
        LOG.info('Success. Verified variants file contains {} lines'.format(len(document_lines)))
        return written_files

    # create workbook and new sheet
    # set up outfolder
    if not outpath:
        outpath = str(os.getcwd())
    workbook = Workbook(os.path.join(outpath,document_name))
    Report_Sheet = workbook.add_worksheet()

    # Write the column header
    row = 0
    for col,field in enumerate(VERIFIED_VARIANTS_HEADER):
        Report_Sheet.write(row,col,field)

    # Write variant lines, after header (start at line 1)
    for row, line in enumerate(document_lines,1): # each line becomes a row in the document
        for col, field in enumerate(line): # each field in line becomes a cell
            Report_Sheet.write(row,col,field)
    workbook.close()

    if os.path.exists(os.path.join(outpath,document_name)):
        LOG.info('Success. Verified variants file of {} lines was written to disk'. format(len(document_lines)))
        written_files += 1

    return written_files

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
 def write_presentation(self, values, records, output):
     workbook = Workbook(output, {'constant_memory': True})
     worksheet = workbook.add_worksheet()
     for column, value in enumerate(values):
         worksheet.write_string(0, column, value)
     for row, record in enumerate(records):
         value_iterator = enumerate([unicode(record[v]) for v in values])
         for column, text in value_iterator:
             worksheet.write_string(row + 1, column, text)
     workbook.close()

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
 def _open_worksheet(xlsx_file):
     """
     Enable XlsxWriter Worksheet object to be opened, operated on, and
     then automatically closed within a `with` statement. A filename or
     stream object (such as a ``BytesIO`` instance) is expected as
     *xlsx_file*.
     """
     workbook = Workbook(xlsx_file, {"in_memory": True})
     worksheet = workbook.add_worksheet()
     yield workbook, worksheet
     workbook.close()

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [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_worksheet [as 別名]
    def from_nested_iters(cls, data):
        """Builds an XlrdSheetData from nested iterables.

        param data: is nested iterables representing data for each cell.
                    For example, `[('A1', 'B1'), ('A2', 'B2')]` represents two rows and two columns
                    of data.
        """
        workbook = Workbook(BytesIO())
        sheet = workbook.add_worksheet('test')

        for row, row_data in enumerate(data):
            for column, cell_data in enumerate(row_data):
                sheet.write(row, column, cell_data)

        return XlrdSheetData(xlsx_to_reader(workbook).sheet_by_index(0))

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
def mt_excel_files(store, case_obj, temp_excel_dir):
    """Collect MT variants and format line of a MT variant report
    to be exported in excel format

    Args:
        store(adapter.MongoAdapter)
        case_obj(models.Case)
        temp_excel_dir(os.Path): folder where the temp excel files are written to

    Returns:
        written_files(int): the number of files written to temp_excel_dir

    """
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    samples = case_obj.get('individuals')

    query = {'chrom':'MT'}
    mt_variants = list(store.variants(case_id=case_obj['_id'], query=query, nr_of_variants= -1, sort_key='position'))

    written_files = 0
    for sample in samples:
        sample_id = sample['individual_id']
        sample_lines = export_mt_variants(variants=mt_variants, sample_id=sample_id)

        # set up document name
        document_name = '.'.join([case_obj['display_name'], sample_id, today]) + '.xlsx'
        workbook = Workbook(os.path.join(temp_excel_dir,document_name))
        Report_Sheet = workbook.add_worksheet()

        # Write the column header
        row = 0
        for col,field in enumerate(MT_EXPORT_HEADER):
            Report_Sheet.write(row,col,field)

        # Write variant lines, after header (start at line 1)
        for row, line in enumerate(sample_lines,1): # each line becomes a row in the document
            for col, field in enumerate(line): # each field in line becomes a cell
                Report_Sheet.write(row,col,field)
        workbook.close()

        if os.path.exists(os.path.join(temp_excel_dir,document_name)):
            written_files += 1

    return written_files

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [as 別名]
def download_as_excel(data):
    output = StringIO.StringIO()
    wb = Workbook(output)
    ws = wb.add_worksheet("Sheet 1")
    first_row = 0
    for header in data['table_header']:
        col = data['table_header'].index(header) # we are keeping order.
        ws.write(first_row,col,header) # we have written first row which is the header of worksheet also.

    srow=1
    for row in data['table_data']:
        for _key,_value in row.items():
            col = data['table_header'].index(_key)
            try:
                ws.write(srow,col,_value)
            except Exception, e:
                ws.write(srow,col, json.dumps(_value))

        srow += 1 #enter the next row

# 需要導入模塊: from xlsxwriter import Workbook [as 別名]
# 或者: from xlsxwriter.Workbook import add_worksheet [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_worksheet [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")