Empty Cells ommited from DrawObjectEventHandler

Hello,

We’ve noticed that the draw event from the DrawObjectEventHandler class omits empty cells. It only captures cells whose value is not null.

For example, using the attached EmptyCells.xlsx workbook, the following code illustrates the issue:

try {
  final String fileName = [PATH] + "EmptyCells.xlsx";
  Workbook wb = new Workbook(fileName);

  // DrawObjectEventHandler
  final StringBuffer drawBuffer = new StringBuffer();
  drawBuffer.append("Start of DrawObjectEventHandler for ")
            .append(fileName).append("\n");
  DrawObjectEventHandler drawHandler = new DrawObjectEventHandler() {
    @Override
    public void draw(DrawObject drawObject, float x, float y, float width, float height) {
        if (drawObject.getType() == DrawObjectEnum.CELL) {
            Cell cell = drawObject.getCell();
            drawBuffer.append("Got Cell ").append(cell.getName()).append("\n");
        }
    }
  };

  final PdfSaveOptions pdfOptions = new PdfSaveOptions();
  pdfOptions.setOnePagePerSheet(false);
  pdfOptions.setDrawObjectEventHandler(drawHandler);

  String pdfOut = fileName.replace(".xlsx", ".pdf");
  wb.save(pdfOut, pdfOptions);
  System.out.println("Generated PDF file: " + pdfOut);

  Path drawOut = Paths.get(fileName.replace(".xlsx", "_drawInfo.txt"));
  Files.deleteIfExists(drawOut);
  Files.write(drawOut, drawBuffer.toString().getBytes());
  System.out.println("Generated draw output file: " + drawOut.toString());

} catch (Exception ex) {
  System.out.println("Unexpected EXCEPTION: \n" + ex.getMessage());
  ex.printStackTrace();
}

Running the above code produces the attached EmptyCells_drawInfo.txt file with the following text:

Start of DrawObjectEventHandler for C:\Carlos\Downloads\EmptyCells.xlsx
Got Cell A1
Got Cell C1
Got Cell E1
Got Cell F1
Got Cell A2
Got Cell B2
Got Cell C2
Got Cell E2
Got Cell A3
Got Cell C3
Got Cell F3
Got Cell A5
Got Cell B5
Got Cell C5
Got Cell E5
Got Cell F5
Got Cell F7

As you can see, all of the empty rows, columns and cells in the sheet are not included in the output.

Would it be possible to have an option for the DrawObjectEventHandler to invoke the draw method for empty cells?

Attachment: EmptyCells.zip (6.1 KB)

Thanks

@oraspose

Thanks for using Aspose APIs.

We are afraid, your issue cannot be fixed because these cells e.g. cell B1 is not only empty but also null and we do not init null cells, so you will not able to get such cells from DrawObjectEventHandler.

Thanks for your response Shakeel.

We need to be able to capture the coordinates of every cell from a worksheet as rendered in a page (for PDF conversions). Since empty cells are omitted from the Draw event - we are forced to calculate their coordinates.

The biggest challenge is when a page is scaled because of the various page-setup scaling options (ex. “fit to page”, scale to 90%, etc.). Unfortunately, the only way we know of to get the scale of a printed page is via the SheetRender.getPageScale API. However, this is highly inefficient because the DrawEvent is already rendering the worksheets, so using the SheetRenderer just to get the page scale is a very expensive operation.

Do you have any recommendations of a better way to get the page scaling or capture the coordinates of empty cells? Alternately, it would be really great if the DrawObject class would provide a getPageScale() API similar to the SheetRender.

Thanks.

@oraspose

Thanks for using Aspose APIs.

We have looked into your issue and found that even you get the page scale, you cannot calculate empty cell’s coordinates. Because the cell’s width and height is not simply Cells.getColumnWidthPixel() multiply PageScale or Cells.getRowHeight() multiply PageScale.

A workaround is to fill the empty cells in first row and first column and then cache the coordinates. After the rendering is finished, you can get every cell’s coordinates.

Sample Code - Java

try {
	  final String fileName = "d:\\download\\bugs\\tempbug\\" + "EmptyCells.xlsx";
	  Workbook wb = new Workbook(fileName);
	  
	  for(int sheetIndex = 0; sheetIndex < wb.getWorksheets().getCount(); sheetIndex++)
	  {
		  Worksheet sheet = wb.getWorksheets().get(sheetIndex);
		  Cells cells = sheet.getCells();
		  int endRow = cells.getMaxDataRow();
		  int endColumn = cells.getMaxDataColumn();
		  
		  //check first row and first column, fill empty cell.
		  //first row
		  for(int columnIndex = 0; columnIndex <= endColumn; columnIndex++)
		  {
			  Cell cell = cells.get(0, columnIndex);
			  String strValue = cell.getStringValue();
			  if(strValue == null || "".equals(strValue))
			  {
				  cell.putValue(" ");
			  }
		  }
		  
		  //first column
		  for(int rowIndex = 0; rowIndex <= endRow; rowIndex++)
		  {
			  Cell cell = cells.get(rowIndex, 0);
			  String strValue = cell.getStringValue();
			  if(strValue == null || "".equals(strValue))
			  {
				  cell.putValue(" ");
			  }
		  }
	  }
	  
	  //HashMap<SheetIndex, HashMap<columnIndex, x>
	  final HashMap<Integer, HashMap<Integer, Float>> xMaps = new HashMap<>();
	  //HashMap<SheetIndex, HashMap<rowIndex, y>
	  final HashMap<Integer, HashMap<Integer, Float>> yMaps = new HashMap<>();
	  
	  for(int sheetIndex = 0; sheetIndex < wb.getWorksheets().getCount(); sheetIndex++)
	  {
		  xMaps.put(sheetIndex, new HashMap<Integer, Float>());
		  yMaps.put(sheetIndex, new HashMap<Integer, Float>());
	  }

	  // DrawObjectEventHandler
	  final StringBuffer drawBuffer = new StringBuffer();
	  drawBuffer.append("Start of DrawObjectEventHandler for ")
				.append(fileName).append("\n");
	  DrawObjectEventHandler drawHandler = new DrawObjectEventHandler() {
		@Override
		public void draw(DrawObject drawObject, float x, float y, float width, float height) {
			if (drawObject.getType() == DrawObjectEnum.CELL) {
				Cell cell = drawObject.getCell();
				//System.out.println(String.format("%s: (%.2f,%.2f)", cell.getName(), x, y));
				//drawBuffer.append("Got Cell ").append(cell.getName()).append("\n");
				if(!xMaps.get(drawObject.getSheetIndex()).containsKey(cell.getColumn()))
				{
					xMaps.get(drawObject.getSheetIndex()).put(cell.getColumn(), x);
				}
				if(!yMaps.get(drawObject.getSheetIndex()).containsKey(cell.getRow()))
				{
					yMaps.get(drawObject.getSheetIndex()).put(cell.getRow(), y);
				}
			}
		}
	  };

	  final PdfSaveOptions pdfOptions = new PdfSaveOptions();
	  pdfOptions.setOnePagePerSheet(false);
	  pdfOptions.setDrawObjectEventHandler(drawHandler);

	  String pdfOut = fileName.replace(".xlsx", ".pdf");
	  wb.save(pdfOut, pdfOptions);
	  System.out.println("Generated PDF file: " + pdfOut);
	  
	  //take your Sheet1 for example, output every cell (x,y)
	  int sheetIndex = 0;
	  Worksheet sheet = wb.getWorksheets().get(sheetIndex);
	  Cells cells = sheet.getCells();
	  int endRow = cells.getMaxDataRow();
	  int endColumn = cells.getMaxDataColumn();
	  for(int rowIndex = 0; rowIndex <= endRow; rowIndex++)
	  {
		  float y = yMaps.get(0).get(rowIndex);
		  for(int columnIndex = 0; columnIndex <= endColumn; columnIndex++)
		  {
			  float x = xMaps.get(0).get(columnIndex);
			  drawBuffer.append(String.format("(%.2f,%.2f)", x,y));
			  drawBuffer.append('\t');
		  }
		  drawBuffer.append('\n');
	  }

	  Path drawOut = Paths.get(fileName.replace(".xlsx", "_drawInfo.txt"));
	  Files.deleteIfExists(drawOut);
	  Files.write(drawOut, drawBuffer.toString().getBytes());
	  System.out.println("Generated draw output file: " + drawOut.toString());

	} catch (Exception ex) {
	  System.out.println("Unexpected EXCEPTION: \n" + ex.getMessage());
	  ex.printStackTrace();
	}

Thank you for the response and workaround Shakeel.

We had previously explored a similar workaround as what you’ve suggested. However, we several hit issues and concerns:

  • Merged Cells: When there are merged cells in the first row/col you can’t use 'putValue' beyond the first merged cell (which is typically not already empty). Additionally, doesn’t the “Cells.get(row,col)” API return null when requesting a merged cell which is not the first cell in the merged range?
    So when merged cells exist in the first row/col, we won’t not be able to capture the correct coordinates on every row/col.
  • Overflowing Text Cells: As you know, when a cell’s text is too long to fit within the width of its column and the adjacent cell is empty - the text overflows into the next (empty) cell. The workaround presented would prohibit this from occurring (on the cells from the 1st row/col) since there would be no empty cells. In this case, the resulting PDF would not appear as the workbook author expected (i.e. the cell’s text would be truncated).
    Also note that per this thread it is not trivial to discover cells with overflowing text. Once again, we’re concerned about performance on large worksheets.
  • Formula or Formatting Dependencies: We also need to consider that some cells may be left empty by design. A worksheet can have formulas or conditional formatting which can change when the empty cells are not empty.

Ideally, we’d prefer not to manipulate the data in the worksheets as this can have unpredictable or unexpected consequences. If you know of any other workarounds, please do let us know.

Thanks again.

@oraspose

Another workaround is to calculate the width scale and height scale when output. Please see the following code.

Java

try {
	  final String fileName = "d:\\download\\bugs\\tempbug\\" + "EmptyCells.xlsx";
	  Workbook wb = new Workbook(fileName);
	  
	  //widthScale, heightScale
	  final float[] scale = new float[]{-1, -1};
	  
	  //HashMap<SheetIndex, HashMap<columnIndex, x>>
	  final HashMap<Integer, HashMap<Integer, Float>> xMaps = new HashMap<>();
	  //HashMap<SheetIndex, HashMap<rowIndex, y>>
	  final HashMap<Integer, HashMap<Integer, Float>> yMaps = new HashMap<>();
	  
	  for(int sheetIndex = 0; sheetIndex < wb.getWorksheets().getCount(); sheetIndex++)
	  {
		  xMaps.put(sheetIndex, new HashMap<Integer, Float>());
		  yMaps.put(sheetIndex, new HashMap<Integer, Float>());
	  }

	  // DrawObjectEventHandler
	  final StringBuffer drawBuffer = new StringBuffer();
	  drawBuffer.append("Start of DrawObjectEventHandler for ")
				.append(fileName).append("\n");
	  DrawObjectEventHandler drawHandler = new DrawObjectEventHandler() {
		@Override
		public void draw(DrawObject drawObject, float x, float y, float width, float height) {
			if (drawObject.getType() == DrawObjectEnum.CELL) {
				Cell cell = drawObject.getCell();
				System.out.println(String.format("%s: (%.2f,%.2f)", cell.getName(), x, y));
		            //drawBuffer.append("Got Cell ").append(cell.getName()).append("\n");
				if(!xMaps.get(drawObject.getSheetIndex()).containsKey(cell.getColumn()))
				{
					xMaps.get(drawObject.getSheetIndex()).put(cell.getColumn(), x);
				}
				if(!yMaps.get(drawObject.getSheetIndex()).containsKey(cell.getRow()))
				{
					yMaps.get(drawObject.getSheetIndex()).put(cell.getRow(), y);
				}
				
				//calculate width scale and height scale
				if(scale[0] < 0)
				{
					float originalWidth = (float)cell.getWorksheet().getCells().getColumnWidthInch(cell.getColumn()) * 72;
					scale[0] = width / originalWidth;
				}
				if(scale[1] < 0)
				{
					float originalHeight = (float)cell.getWorksheet().getCells().getRowHeightInch(cell.getRow()) * 72;
					scale[1] = height / originalHeight;
				}
			}
		}
	  };

	  final PdfSaveOptions pdfOptions = new PdfSaveOptions();
	  pdfOptions.setOnePagePerSheet(false);
	  pdfOptions.setDrawObjectEventHandler(drawHandler);

	  String pdfOut = fileName.replace(".xlsx", ".pdf");
	  wb.save(pdfOut, pdfOptions);
	  System.out.println("Generated PDF file: " + pdfOut);
	  
	  //take your Sheet1 for example, output every cell (x,y)
	  int sheetIndex = 0;
	  Worksheet sheet = wb.getWorksheets().get(sheetIndex);
	  HashMap<Integer, Float> xMap = xMaps.get(0);
	  HashMap<Integer, Float> yMap = yMaps.get(0);
	  Cells cells = sheet.getCells();
	  int startRow = 0;
	  int startColumn = 0;
	  int endRow = cells.getMaxDataRow();
	  int endColumn = cells.getMaxDataColumn();
	  
	  //no data in first row/column.
	  if(!xMap.containsKey(startColumn) || !yMap.containsKey(startRow))
	  {
		  throw new Exception("invalid data.");
	  }
	  
	  for(int rowIndex = startRow; rowIndex <= endRow; rowIndex++)
	  {
		  float y;
		  if(yMap.containsKey(rowIndex))
		  {
			  y = yMap.get(rowIndex);
		  }
		  else //calculate it if not exist.
		  {					  					  
			  y = yMap.get(rowIndex - 1);

			  float originalHeight = (float)cells.getRowHeightInch(rowIndex - 1) * 72;
			  y += originalHeight * scale[1];
			  yMap.put(rowIndex, y);
				  
			  System.out.println(String.format("Calculated row[%d]: %.2f", rowIndex, y));
		  }
						  
		  for(int columnIndex = startColumn; columnIndex <= endColumn; columnIndex++)
		  {
			  float x;
			  if(xMap.containsKey(columnIndex))
			  {
				  x = xMap.get(columnIndex);
			  }
			  else
			  {
				  x = xMap.get(columnIndex - 1);
				  
				  float originalWidth = (float)cells.getColumnWidthInch(columnIndex - 1) * 72;
				  x += originalWidth * scale[0];
				  xMap.put(columnIndex, x);
				  
				  System.out.println(String.format("Calculated column[%d]: %.2f", columnIndex, x));
			  }
			  drawBuffer.append(String.format("(%.2f,%.2f)", x,y));
			  drawBuffer.append('\t');
		  }
		  drawBuffer.append('\n');
	  }

	  Path drawOut = Paths.get(fileName.replace(".xlsx", "_drawInfo.txt"));
	  Files.deleteIfExists(drawOut);
	  Files.write(drawOut, drawBuffer.toString().getBytes());
	  System.out.println("Generated draw output file: " + drawOut.toString());

	} catch (Exception ex) {
	  System.out.println("Unexpected EXCEPTION: \n" + ex.getMessage());
	  ex.printStackTrace();
	}

Thank you again Shakeel!

Capturing the width/height scale within the draw event will probably work. We will try it out.

Just a minor point of clarification (for the benefit of other readers) - it is necessary to check that the current cell being processed is not merged. Otherwise, the scaling will be incorrect, since the ‘width’ and ‘height’ parameters take into account the total dimensions of the merged range (i.e. not just the dims of the cell being processed).

Lastly - you had previously stated that:

But in the new workaround, you do multiply the empty cells’ height/width by the scales captured in the draw event. Can we assume that the scales captured in the draw event are OK to use versus the PageScale?

Thanks again.

@oraspose

Thanks for using Aspose APIs.

To help you further and thoroughly, we have logged your comment in our database for more investigation. We will look into it and help you asap.

This issue has been logged as

  • CELLSJAVA-42641 - Empty Cells ommited from DrawObjectEventHandler

@oraspose

Image Link:
PageScale.png (84.1 KB)

Because the cell’s width and height is not simply Cells.getColumnWidthPixel() multiply PageScale or Cells.getRowHeight() multiply PageScale.

Here the “PageScale” means SheetRender.getPageScale(). It also means the scale in PageLayout in Microsoft Excel (Please see the attachment PageScale.png).