Python Forum
Python - Merge existing cells of Excel file created with xlsxwriter
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python - Merge existing cells of Excel file created with xlsxwriter
#1
Hello,
I'm writing a Python script which creates an Excel file. The worksheet that causes me problems is the one with a calendar of each month. I would like to have the week number above the days of each week. I already generated that, but I would like to merge the week number cells.

Does anyone know if there is a fonction for that ?

Here is the code that generates the worksheet.
import xlsxwriter
import calendar 
import datetime
import time
import locale
import pandas as pd


def calendrier(year,mnth):
    cal= calendar.Calendar()
    temp=cal.monthdatescalendar(year, mnth)
    datesMois=[]
    for w in temp:
        for d in w:
            if d.month==mnth:
                datesMois.append(d)
    
    locale.setlocale(locale.LC_ALL, "")
    nomMois=datetime.datetime.strptime(str(mnth), "%m").strftime("%B")
    
    #locale.setlocale(locale.getdefaultlocale())
       
    return datesMois,nomMois


annee=2021
for m in range(1,13):
    exec("dates"+str(m)+",nom"+str(m)+"=calendrier(annee,m)")
    
workbook = xlsxwriter.Workbook('essai.xlsx')

merge_format = workbook.add_format({   # format jours de la semaine
    'align': 'center',
    'bold': 1,
    'valign': 'vcenter'})
date_format=workbook.add_format({      # format date
    'align': 'center',
    'bold': 1,
    'num_format':'dd/mm/yy',
    'valign': 'vcenter'})
date_formatTDB=workbook.add_format({   # format Date dans le tableau de bord (format minimaliste)
    'align': 'center',
    'num_format':'dd',
    'valign': 'vcenter'})


# ajout de la feuille "Tableau de bord"
tdb=workbook.add_worksheet('Tableau de bord')


l=1
for m in range(1,13):
    # dd=1
    # ld=[]
    # exec("for d in dates"+str(m)+":\n\tif d.strftime('%a')=='dim.':\n\t\tld.append(dd+1)\n\tdd+=1")
    # print(ld)
    # exec("tdb.merge_range('B"+str(l)+":Z"+str(l)+"', nom"+str(m)+", merge_format)")
    # exec("tdb.merge_range(l,ld[0],l,ld[1],dates"+str(m)+"[ld[1]].strftime('%V'),merge_format)")
    exec("for cmp in range(1,len(dates"+str(m)+")+1):\n\ttdb.write(l,cmp, dates"+str(m)+"[cmp-1].strftime('%V'), date_formatTDB)")
    # exec("for j in range(1,len(ld)-1):\n\tprint(j)\n\ttdb.merge_range(l,ld[j],l,ld[j+1],dates"+str(m)+"[ld[j]].strftime('%V'),merge_format)")
    exec("for cmp in range(1,len(dates"+str(m)+")+1):\n\ttdb.write(l+1,cmp, dates"+str(m)+"[cmp-1].strftime('%a'), date_formatTDB)")
    exec("for cmp in range(1,len(dates"+str(m)+")+1):\n\ttdb.write(l+2,cmp, dates"+str(m)+"[cmp-1], date_formatTDB)")
    l+=22
    
    
workbook.close()
Thanks in advance,
Manon
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Pandas keep existing format of Excel AlphaInc 2 883 Jan-11-2024, 03:44 AM
Last Post: plonkarchivist
  Data Sorting and filtering(From an Excel File) PY_ALM 0 1,012 Jan-09-2023, 08:14 PM
Last Post: PY_ALM
  Creating more than one excel File at once malvarez1976 0 1,792 Dec-15-2020, 02:04 AM
Last Post: malvarez1976
  Python pandas merge with or conditional Lafayette 0 2,148 May-07-2020, 07:34 PM
Last Post: Lafayette
  Convert Excel to .txt - Need each excel row to be separate .txt file cowboykevin05 2 4,732 Jan-03-2020, 06:29 PM
Last Post: Larz60+
  [split] Converting excel file to txt file unexceptionalhobby 2 4,312 Oct-16-2019, 06:34 PM
Last Post: unexceptionalhobby
  Read exel with merged cells and write to another excel SriMekala 0 2,938 Aug-10-2019, 07:14 AM
Last Post: SriMekala
  how read and write merged cells in excel SriMekala 1 15,063 Aug-07-2019, 11:27 PM
Last Post: scidam
  Python write result of VAR to excel file wissam1974 8 8,523 Jul-13-2019, 01:09 PM
Last Post: wissam1974
  How to add a dataframe to an existing excel file wendysling 2 28,054 May-09-2019, 07:00 PM
Last Post: wendysling

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020