We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

About HtmlSaveOptions.setExportGridLines and cell's border

Hi Aspose team


We are working on Excel files’ HTML conversion.
Here is our code for conversion test:

Workbook book = new Workbook(“custom/input/xlsx/20150109_1.xlsx”);
ByteArrayOutputStream baos = new ByteArrayOutputStream();

this.fixCellStyle(book);

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
if (i != 1) {
book.getWorksheets().get(i).setVisible(false);
}
}
HtmlSaveOptions saveOps = new HtmlSaveOptions();
saveOps.setExportHiddenWorksheet(false);
//saveOps.setExportGridLines(true);
final Map<String, byte[]> resourceMap = new HashMap<String, byte[]>();
saveOps.setStreamProvider(new IStreamProvider() {

@Override
public void initStream(StreamProviderOptions arg0) throws Exception {
arg0.setStream(new ByteArrayOutputStream());
}

@Override
public void closeStream(StreamProviderOptions arg0)
throws Exception {
System.out.println(arg0.getDefaultPath());
OutputStream stream = arg0.getStream();
if (stream instanceof ByteArrayOutputStream) {
ByteArrayOutputStream bb = (ByteArrayOutputStream) stream;
IOUtils.write(bb.toByteArray(), new FileOutputStream(
“custom/output/xlsx/” + arg0.getDefaultPath()));
}
String fileName = arg0.getDefaultPath().substring(
arg0.getDefaultPath().lastIndexOf(’/’) + 1);
resourceMap.put(fileName, ((ByteArrayOutputStream) arg0
.getStream()).toByteArray());
}
});
book.save(baos, saveOps);
IOUtils.write(baos.toByteArray(), new FileOutputStream(
“custom/output/xlsx/stream.html”));

fixCellStyle method:

protected void fixCellStyle(Workbook book) {

StyleFlag styleFlagGrid = new StyleFlag();
styleFlagGrid.setBorders(true);

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
int maxRow = sheet.getCells().getMaxRow();
int maxCol = sheet.getCells().getMaxColumn();

for (int datai = 0; datai <= maxRow; datai++) {

for (int datay = 0; datay <= maxCol; datay++) {

Cell dataCell = sheet.getCells().get(datai, datay);
Style dataStyle = dataCell.getStyle();

if (dataStyle.getBorders()
.getByBorderType(BorderType.LEFT_BORDER)
.getLineStyle() == 0) {
dataStyle.setBorder(BorderType.LEFT_BORDER,
CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
}
if (dataStyle.getBorders()
.getByBorderType(BorderType.RIGHT_BORDER)
.getLineStyle() == 0) {
dataStyle.setBorder(BorderType.RIGHT_BORDER,
CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
}
if (dataStyle.getBorders()
.getByBorderType(BorderType.BOTTOM_BORDER)
.getLineStyle() == 0) {
dataStyle.setBorder(BorderType.BOTTOM_BORDER,
CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
}
if (dataStyle.getBorders()
.getByBorderType(BorderType.TOP_BORDER)
.getLineStyle() == 0) {
dataStyle.setBorder(BorderType.TOP_BORDER,
CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
}
dataCell.setStyle(dataStyle, styleFlagGrid);
dataCell.getStyle().setNumber(49);
}
}
}
}

There are 2 problems occurs in a Excel file, Is there any option or method to fix these?

1. HtmlSaveOptions.setExportGridLines did not work, so there would not be gray grid lines(like Excel files shows in MS Office) in the HTML conversion result.

2. Because of problem #1, we took back the method we used to generate gray grid lines. However there were some border’s style changed, which looks a little different from the original Excel file.

I have uploaded the Excel file, HTML conversion result, and comparison images.
Please check the attachment, and Thank you for the help :slight_smile:

Best,
Craig

Hi Craig,


Thank you for sharing the updated code and sample spreadsheet.

I have evaluated the complete scenario on my side while using the latest version of Aspose.Cells for Java 8.9.2.7, and I am able to notice the following problems.

  1. Grid Lines seem to overlap the cell borders in a few places (please check attached snapshot). I have used my own sample to replicate this problem and have logged it as CELLSJAVA-41979.
  2. Grid Lines aren’t rendering in the HTML. I have used your sample spreadsheet and code by commenting the call to fixCellStyle method and rendering the spreadsheet to HTML with HtmlSaveOptions.ExportGridLines. I have noticed that Grid Lines haven’t rendered at all. I have logged this incident as CELLSJAVA-41980 for further investigation.

Please note, if you opt to use both fixCellStyle method & ExportGridLines property, the result shows the problem as mentioned in point 1) above, that is; Grid Lines tend to overlap the cell borders on some places.

Please let me know if I have missed anything during the tests. Moreover, it is always appropriate to split the complex scenario in different threads for better visibility and tracking of individual issues.

Hi Barbar.Raza


Thanks for your information.

Let me split this scenario as 2 thread

1. HtmlSaveOptions.setExportGridLines does not render grid lines to HTML.
2. With our custom method fixCellStyle, which adds the gray grid lines and ignores the cell’s borders style already exists, some borders did changed its style to grid lines

I want to leave this thread for problem #2, and open another thread for problem #1

Hi Craig,


Thank you for your cooperation in this regard. I have attached the appropriate ticket to your new thread for tracking purposes.

Hi again,


This is to update you regarding the ticket CELLSJAVA-41979. Please note, the browsers have specific rules when borders are merged, and we cannot alter those rules therefore we will not be able to fix the problem related to the border overlapping with the grid lines.

Thank you for the understanding.,

The issues you have found earlier (filed as CELLSJAVA-41980) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

Hi Babar.Raza


I tried setExportGridLines(true) with Cells 17.1.0, and the grid lines did show up.
Thanks for fixing this.

However with setExportGridLines(true), there are still some lines are overlapped by the generated grid lines, as you can see in the image of the attachments.

Is this like CELLSJAVA-41979 , the behavior of the browsers? Or this can also be improved?

Hi Craig,


Thank you for the feedback.

Yes, you are correct. The overlapping of cell borders with the grid lines is caused due to the same reason as shared in my previous response. Unfortunately, we cannot fix this scenario on your side due to the rendering rules of web browsers, however, you can control it on your end by not making the grid lines visible during the conversion of spreadsheets to HTML format, instead, you can use the cells borders to simulate the grid lines.

Hi Babar.Raza

We take a look about the classes and styles in the result.

For the td rendered correctly, its class is “x39”, which can make the color of the grid line exactly right as the origin file.
However, as for td rendered incorrectly, its class is “x40”, which make the wrong result.
So why using “x40” for this td instead “x39”, though these cells might have the same style in the original Excel file?

result.zip (4.9 KB)
class_x40.JPG (74.2 KB)
class_x39.JPG (72.1 KB)

@craig.w.su

Thanks for considering Aspose APIs.

We have checked your 1.html and 2.html but we could partially understand your issue. To make us understand this issue precisely, please provide us a simplest Excel file which could replicate your issue with some simplest code.

For example, please check the attached Excel file and the following code and its generated HTML (download link given below). Please provide us something similar. You can definitely add more code if it is really necessary. It will help us replicate and fix your issue quickly.

Thanks for your cooperation in this regard and have a good day.

Java

Workbook wb = new Workbook(dirPath + "sampleExcel.xlsx");
wb.save(dirPath + "out.html");

Sample Excel File Output HTML.zip (14.3 KB)

Hi Shakeel

It seem like that the attachment from old forum is gone.

With the code snippet from the top of this topic, we convert a Excel file to HTML file with part of grid lines rendered incorrectly.
20150109_1.xlsx.zip (7.3 KB)

Please see the comparison image in the attachment.
comparison.JPG (125.0 KB)

We took a look at the result HTML files, and then we found there might be something wrong about the classes in the result.
Please see the HTML/CSS class problem mentioned above.

Craig

@craig.w.su

Thanks for your help in diagnosing this issue.

There are two issues attached with this thread both are closed now. We will create a new ticket for your current issue.

We have found some problems in grid lines. When we converted your Excel file to HTML using Microsoft Excel 2016, both sheets (i.e. Adv, TODO Check) were right. But when we converted them to HTML using Aspose.Cells, we found issues in “TODO Check” sheet.

Please first download and try the most recent version because it is necessary, as we fix issues in most recent version only.

Here are the screenshots. First one is OK but second one is not OK.

First Screenshot - OK

Second Screenshot - KO

This is the sample code we used for testing.

Java

Workbook wb = new Workbook(dirPath + "20150109_1.xlsx");
wb.save(dirPath + "ByCells" + CellsHelper.getVersion() + ".html");

We have also attached the HTML files generated by Microsoft Excel 2016 as well as Aspose.Cells for Java

Html-generated-by-Excel-2016.zip (7.4 KB)
Html-generated-by-Aspose.Cells.zip (8.6 KB)

Regarding your sample code, please provide your code in the form of Java files which we could run at our end. Because the code you provided is now unformatted and jumbled up and in parts as well as, it is not runnable. Please make sure, when you provide us your runnable sample code, you remove all of your business logic and any other non-Aspose.Cells dependencies.

Hi Shakeel

Could you please provide other way to get 17.6.7?
I can’t access Dropbox from my office, due to our IT restriction.

Craig

@craig.w.su

I have uploaded the 17.6.7 on another file sharing web site. Please download it and let us know if it is OK in China.

Here is download link.

Hi Shakeel

Can you upload to the forum? I can’t access this service either.
We banned most of the online storage service, due to our IT policy in my company.
(Nothing to do with China, and I am not in China)

@craig.w.su,

Please download all these parts. Remove .remove.zip extension and place all of them in some folder and then extract its content using WinRAR tool. Thank you.

Aspose Cells 17.6.7 for Java - In Parts.part1.rar.remove.zip (2 MB)
Aspose Cells 17.6.7 for Java - In Parts.part2.rar.remove.zip (2 MB)
Aspose Cells 17.6.7 for Java - In Parts.part3.rar.remove.zip (2 MB)
Aspose Cells 17.6.7 for Java - In Parts.part4.rar.remove.zip (93.9 KB)

Hi Shakeel

I got HTTP 500 when getting the attachment you provide.
Is this happening at your side?

500.JPG (61.9 KB)

@craig.w.su,

I sent attachment to your email but it gave me the following error message.

@shakeel.faiz

Thank you very much for the help.
I used so many steps to get 17.6.7 from the link you provided above, and then sent it to my PC :’(

Then I need some time to try it, thanks again.

Craig

Hi @shakeel.faiz

I tried with 17.6.7 and found that the problem still exists.

This code snippet of my test case should be runnable:

@Test
public void asposeSaveStream() throws Exception {

String fileName = “20150109_1.xlsx”;

Workbook book = new Workbook(“custom/input/xlsx/” + fileName);

String dirName = fileName;
File file = new File(“custom/output/xlsx/” + dirName);
file.mkdirs();

fixCellStyle(book);
for (int page = 0; page < book.getWorksheets().getCount(); page++) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();

  book.getWorksheets().get(page).setVisible(true);
  for (int i = 0; i < book.getWorksheets().getCount(); i++) {
  	// only one page for test
  	if (i != page) {
  		book.getWorksheets().get(i).setVisible(false);
  	}
  }
  
  
  HtmlSaveOptions saveOps = new HtmlSaveOptions();
  saveOps.setClearData(false);
  saveOps.setCreateDirectory(false);
  saveOps.setExportActiveWorksheetOnly(false);
  saveOps.setExportHiddenWorksheet(false);
  saveOps.setParseHtmlTagInCell(true);
  saveOps.setEncoding(Encoding.getUTF8());
  saveOps.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
  saveOps.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
  saveOps.setExportImagesAsBase64(true);
  //saveOps.setExportGridLines(true);
  saveOps.setStreamProvider(new IStreamProvider() {
  	@Override
  	public void initStream(StreamProviderOptions arg0) throws Exception {
  		arg0.setStream(new ByteArrayOutputStream());
  	}
  	@Override
  	public void closeStream(StreamProviderOptions arg0) throws Exception {
  		System.out.println(arg0.getDefaultPath());
  		OutputStream stream = arg0.getStream();
  		if (stream instanceof ByteArrayOutputStream) {
  			ByteArrayOutputStream bb = (ByteArrayOutputStream) stream;
  			IOUtils.write(bb.toByteArray(),
  					new FileOutputStream("custom/output/xlsx/" + arg0.getDefaultPath()));
  		}
  	}
  });
  book.save(baos, saveOps);
  IOUtils.write(baos.toByteArray(),
  		new FileOutputStream("custom/output/xlsx/" + dirName + "/" + (page + 1) + ".html"));

}
}

protected void fixCellStyle(Workbook book) {

StyleFlag styleFlagGrid = new StyleFlag();
styleFlagGrid.setBorders(true);

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
int maxRow = sheet.getCells().getMaxRow();
int maxCol = sheet.getCells().getMaxColumn();

  for (int datai = 0; datai <= maxRow; datai++) {
  	for (int datay = 0; datay <= maxCol; datay++) {
  		Cell dataCell = sheet.getCells().get(datai, datay);
  		Style dataStyle = dataCell.getStyle();
  		if (dataStyle.getBorders().getByBorderType(BorderType.LEFT_BORDER).getLineStyle() == 0) {
  			dataStyle.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.fromArgb(204, 204, 204));
  		}
  		if (dataStyle.getBorders().getByBorderType(BorderType.RIGHT_BORDER).getLineStyle() == 0) {
  			dataStyle.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN,
  					Color.fromArgb(204, 204, 204));
  		}
  		if (dataStyle.getBorders().getByBorderType(BorderType.BOTTOM_BORDER).getLineStyle() == 0) {
  			dataStyle.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN,
  					Color.fromArgb(204, 204, 204));
  		}
  		if (dataStyle.getBorders().getByBorderType(BorderType.TOP_BORDER).getLineStyle() == 0) {
  			dataStyle.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.fromArgb(204, 204, 204));
  		}
  		dataCell.setStyle(dataStyle, styleFlagGrid);
  		dataCell.getStyle().setNumber(49);
  	}
  }

}
}