Auto-height about axlsx HOT 55 OPEN

Hello @randym, how one should approach a PR for this feature? what's to take into account?

from axlsx.

Re calculating height manually, see #125 (comment) for help in calculating text width

from axlsx.

Here's what I ended up implementing. Hopefully it can be of use to someone else in the future...

It loops over each column in a row, taking the cell value, splitting the string on line breaks (in case the content contains line breaks), then compares the length of each text_line to the column width to determine how many physical lines it spans in the sheet. Then add them together for each cell and take the cell with the most total text_lines and use the font size to translate that to a row height.

def infer_row_height(row)
  physical_lines = row.each_with_index.map do |cell, column_index|
    text = cell.value
    column_width = row.worksheet.column_info[column_index].width

    text_lines = text.to_s.lines # handle newlines entered by the user
    text_lines.map { |line| (string_width(line, row) / column_width.to_f).ceil }.sum
  end.max
  row.height = (physical_lines * FONT_SIZE) + LINE_PADDING # in my case 11, 5
end

# Copied directly from caxlsx: https://github.com/caxlsx/caxlsx/blob/ebd11df1c11e3fe3eb0870e4c43d7ff8771ffe0d/lib/axlsx/workbook/worksheet/cell.rb#L437
def string_width(string, row)
  font_scale = Style::FONT_SIZE / row.worksheet.workbook.font_scale_divisor
  (string.to_s.size + 3) * font_scale
end

In code for generating the workbook:

data.each { |d| sheet.add_row(d) }
sheet.column_widths(*columns.map { |c| c[:width] }) # must be set after adding data

# needs to run after column_widths is set
sheet.rows.each { |row| infer_row_height(row) }

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

Can't you accomplish this through word wrap?

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1 Is there any workaround for this in the meantime that people have come up with?

from axlsx.

You can just use word wrap or you can count the number of lines and multiply that by the +/- the font size

from axlsx.

could you do a gist for handling this? or is there a way to query the worksheet for the number of rows given a fontSize and a certrain string of text?

from axlsx.

Yeah, so I was using wordwrap but still had rows and words cut off.

Ended up writing a helper to do similar to what you suggest @jurriaan . Checked the cells autowidth value, then taking in to account that, any new lines, and a bit of a buffer for whitespace at the end of each line, assign a new row hight.
....
A bit (quite) flakey, but quick, and it will do the trick in the short term.

from axlsx.

+1

from axlsx.

+1

from axlsx.

@hofdesu can you share the helper re: calculating the height?

from axlsx.

@hofdesu yes, could you please show your helper?

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

+1

from axlsx.

For anyone wanting a cheap hack to get a rough estimate of the row height, here's what worked for me.

def set_row_height(row, text)
	column_width = row.worksheet.column_info[8].width # column 8 is where my data lives
	length_of_text = if text.nil?
					0
				   else
				 	text.length
				 end

	row_height = length_of_text / 52 * 14
	row.height = row_height
end

def get_line_item_data(line_item, line_item_type, sheet)
   line_item_data = [line_item.created_at.strftime("%m/%d/%Y"), line_item.id, line_item.item_index, line_item.quote_id, line_item.quote.project_name, line_item_type , line_item.hours, line_item.price, line_item.scope_description ]
   line_item_data
end

wb = xlsx_package.workbook
wb.add_worksheet(name: "Line Items") do |sheet|	
	sheet.add_row ["Created At", "Line Item Id", "Line Item Index", "Quote ID", "Project Name", "Line Item Type", "Hours", "Price", "Description", "Links......"]

	@line_items.each do |line_item|
		row = sheet.add_row(get_line_item_data(line_item, line_item_type, sheet), style: wrap_text)
		set_row_height(row, line_item.scope_description )
	end
end

Watch out - we've hard coded some values, and remember, the size of the text can change. Overall, it might give you some ideas on where to start. Is it robust? No. Is it pretty? It's just good enough.

from axlsx.

@benkoshy can you please clarify the magic numbers? I assume 14 is font size? How did you get the 52 coefficient? Is that supposed to be column_width?

from axlsx.

@jprince sorry mate i completely forgot. I hacked out a solution, but it probably does deserve a fuller treatment: some sort of PR in the main library.

from axlsx.

No worries. It would be a great option to be able to have in this library though.

from axlsx.