0

I've been using openpyxl and xlsx for few months and have found while there is an option to change the default alignment format on xlsxwriter, there is none on openpyxl.

More details:

Xlsxwriter - creating an .xlsx with xlsxwriter, defining default alignment, saving, and opening again - the whole xlsx has the alignment as defined.

OpenPyXL - opening the same .xlsx above, whether defining alignment for each cell or not, saving and opening again - only cells with value keep the alignment meaning None cells and new cells I insert manually (out of range) doesn't have an alignment.

I would like to ask if anyone aware of a way on openpyxl (or even other libraries beside PyExcelerate I already checked) to keep the whole xlsx's alignment as configured.

P.s. I already search the whole stack overflow and google on default alignment on xlsx file but found no post regarding that issue

yahel
  • 1
  • How are you changing the default alignment with XlsxWriter? – jmcnamara Feb 04 '21 at 13:53
  • @jmcnamara workbook.formats[0].set_align('center') workbook.formats[0].set_align('vcenter') – yahel Feb 04 '21 at 20:51
  • That isn’t a documented, or even valid, way of changing the default format in XlsxWriter. Which is probably why OpenPyXL doesn’t read/maintain it. – jmcnamara Feb 04 '21 at 22:08
  • @jmcnamara That's true it isn't documented, BUT this method with XlsxWriter gives an automated result which is actually just the same as manually pressing ctrl+A on the sheet and align it all, which is valid overall, and OpenPyXL can't maintain it too. That said, my guess is OpenPyXL applies its own default style and default format for cells with no values or out of range of the original document. The default alignment of the whole document can't be change on OpenPyXL, hence why I'm looking for an alternative solution. – yahel Feb 05 '21 at 02:03
  • What's the motivation of setting the default vs centering every cell? If it's an option in excel it seems reasonable to add to pyexcelerate. – kevmo314 Feb 05 '21 at 02:17
  • @kevmo314 Changing the default (like XlsxWriter) result in the whole sheet being changed, including empty cells and cells out of range, meaning info added both manually through excel and auto through py doesnt need a restyle, you just enter the value. However, centering every cell makes the auto editing later longer with restyling each new cell you add to py, or defining font and alignment for each cell if you want to do it manually which take a lot of time. On that matter PyExcelerate is behind and unfortunately I read on previous posts they have won't add that kind of feature due to errors. – yahel Feb 05 '21 at 10:15
  • How would one set the default alignment in Excel? PyExcelerate is intended to be an exporting library, not really meant to be read again for future edits although we do weakly support that case. If the feature exists in Excel, we can add it to PyExcelerate. – kevmo314 Feb 05 '21 at 17:51
  • After some digging into excel's properties, I didn't manage to find a feature for that. However as I mentioned, manually clicking ctrl+A and aligning it do it for the whole sheet and redefining it as **sheet's** default. Somehow, XlsxWriter do have a feature which enables to change the default alignment for the whole **notebook**, which isn't even documented tho (mentioned it on 2nd comment). I don't know how they did it, but it appears its possible. – yahel Feb 06 '21 at 02:08
  • if PyExcelerate contains workbook's/sheet's properties I guss it can be redefined, so it will be possible to add it. I know only what documented tho, don't know what's going on the background – yahel Feb 06 '21 at 02:25

0 Answers0