![]() The whitespace is added to the string to provide some margin. Slant = ('italic' if alic else 'roman'),Īnd afterwards determine the cell width as cell_width = used_asure(cell_string+' ')/reference_asure('0') ![]() Weight = ('bold' if format.bold else 'normal'), That means, if you wrote something to your table using worksheet.write(row, col, cell_string, format), you can get the used font like this: used_font = (family = format.font_name, In order to do this for a cell from your Excel table, you need to take its format into account (it contains all the information on the used font). You can afterwards use its measure method to determine string widths in pixels, e.g. reference_font = (family='Calibri', size=11) In order to get a handle on the exact width of a string, you can use tkinter's ability to measure string lengths in pixels, depending on the font/size/weight/etc. I can not find a way to measure the width of the item that I want to insert into the cell. The column widths are given in multiples of the width of the '0' character in the font Calibri, size 11 (that's the Excel standard). That URL does not specify what the units are for the third argument to set_column. # Close the Pandas Excel writer and output the Excel file. # Get the xlsxwriter workbook and worksheet objects. Writer = pd.ExcelWriter('pandas_autofit.xlsx', engine='xlsxwriter')ĭf.to_excel(writer, sheet_name='Sheet1', index=False) # Create a Pandas Excel writer using XlsxWriter as the engine. # Create a Pandas dataframe from some data. ![]() Worksheet.write(0, 2, "Some longer text") # Write some worksheet data to demonstrate autofitting. XlsxWriter 3.0.6+ now supports a autofit() worksheet method: from xlsxwriter.workbook import Workbook values] + ) for col in lumns]įor i, width in enumerate(get_col_widths(dataframe)): # Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right # First we find the maximum length of the index column It shouldn't be too difficult to adapt this code for whatever data you are using. Then, it returns the maximum of all values and the column name for each of the remaining columns moving left to right. It first finds the maximum width of the index, which is always the left column for a pandas to excel rendered dataframe. Per example, I tend to use the code below when working with pandas dataframes and xlsxwriter. So, you can simulate autofit by setting each column to the max number of characters in that column. The with of 1 unit of the xlsxwriter columns is about equal to the width of one character. Select Case Sheet1.Range("O1").Value ' is this another sheet?ĪctiveSheet.ListObjects("tblPatients").Range.As a general rule, you want the width of the columns a bit larger than the size of the longest string in the column. If Len(targetCell.Value) > 60 Then targetCell.wrapText = True Cells.RowHeight = 15 ' set all rows to 15 height Set targetRange = Range(w.Cells(1, 7), w.Cells(Rows.Count, 7).End(xlUp)) 'lastRow = w. ' this is a quite bad and unstable idea, see below a better one I wonder if there is something obvious how I could achieve the same but in a more efficient way? Thanks. ![]() '\restore normal XL settings for application ' This checks value of "O1" - I store 1 there if a custom filter is onĪctiveSheet.ListObjects("tblPatients").Range.AutoFilter Field:=6ĪctiveSheet.ListObjects("tblPatients").Range.AutoFilter Field:=6, Criteria1:="=" Set targetRange = Range("G3:G" & lastRow) I base it on condition - if cell text is longer than 60 char, then wrap text and set row height. I wonder if there was a better way to set row height. I wrote a routine called through click of a button and it works, but is slow (takes a few seconds for a table with 200 entries and could become longer if we have a couple of thousand records). I have built a spreadsheet that at times requires to view "Notes" column so that all the text is visible, and sometimes so that all rows are the same height (15 works well).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |