Missing Text after conversion to HTML

Hi Aspose team

We have a Excel file and have it converted it into HTML file format for cross-platform view with Aspose Cells 8.9.0.
And we found that some text were missing in the HTML result file.

For better viewing experience, we have some method to modify the Workbook:
protected void addEmptyColumn(Workbook book) {

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
int columns = sheet.getCells().getMaxDisplayRange()
.getColumnCount();
if (columns > 0) {
sheet.getCells().insertColumn(columns);
sheet.getCells()
.clearRange(
0,
columns,
sheet.getCells().getMaxDisplayRange()
.getRowCount() - 1, columns);
sheet.getCells().getColumns().get(columns).setWidth(8.5);
}
}
}

protected void addColRowHeader(Workbook book) {

for (int i = 0; i < book.getWorksheets().getCount(); i++) {

Worksheet sheet = book.getWorksheets().get(i);

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

sheet.getCells().insertColumn(0);
sheet.getCells().insertRow(0);

sheet.getCells().getRows().get(0).setHeight(15.75);
sheet.getCells().getColumns().get(0).setWidth(4.75);

int maxRow =
// sheet.getCells().getMaxRow();
sheet.getCells().getMaxDisplayRange().getRowCount();
int maxCol =
// sheet.getCells().getMaxColumn();
sheet.getCells().getMaxDisplayRange().getColumnCount();

for (int j = 1; j < maxRow; j++) {
sheet.getCells().get(j, 0).putValue(j);
}
for (int k = 1; k < maxCol; k++) {
sheet.getCells().get(0, k)
.putValue(CellsHelper.columnIndexToName(k - 1));
}
}
}

protected void addColRowHeaderStyle(Workbook book) throws Exception {

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);

StyleFlag styleFlagHeader = new StyleFlag();
styleFlagHeader.setAll(true);

Style headerStyle = book.createStyle();
headerStyle.setForegroundColor(Color.fromArgb(238, 238, 238));
headerStyle.setPattern(BackgroundType.SOLID);
headerStyle.getFont().setBold(true);
headerStyle.setVerticalAlignment(1);
headerStyle.setHorizontalAlignment(1);
headerStyle.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.BOTTOM_BORDER,
CellBorderType.THIN, Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));

int maxRow = sheet.getCells().getMaxRow();
int maxCol = sheet.getCells().getMaxColumn();

for (int k = 0; k <= maxRow; k++) {
sheet.getCells().get(k, 0)
.setStyle(headerStyle, styleFlagHeader);
}
for (int j = 0; j <= maxCol; j++) {
sheet.getCells().get(0, j)
.setStyle(headerStyle, styleFlagHeader);
}
}
}

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);
// set to Text
dataCell.getStyle().setNumber(49);
}
}
}
}









And Here is the code we used for test:
Workbook book = new Workbook(“custom/input/xlsx/TEST0123.xlsx.xlsx”);
this.addEmptyColumn(book);
this.addColRowHeader(book);
this.addColRowHeaderStyle(book);
this.fixCellStyle(book);
ByteArrayOutputStream baos = new ByteArrayOutputStream();

// for one sheet output only
for (int i = 0; i < book.getWorksheets().getCount(); i++) {
if (i != 0) {
book.getWorksheets().get(i).setVisible(false);
}
}
HtmlSaveOptions saveOps = new HtmlSaveOptions();
saveOps.setExportHiddenWorksheet(false);
saveOps.setClearData(false);
saveOps.setCreateDirectory(false);
saveOps.setExportActiveWorksheetOnly(false);
saveOps.setParseHtmlTagInCell(true);
saveOps.setEncoding(Encoding.getUTF8());
saveOps.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
saveOps.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
saveOps.setExportImagesAsBase64(false);

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”));

Is there any options to make the missing text appear in the result HTML again?

Please check the attachment. There should be the result, the origin Excel, and screen clips files in the 7z, Thank you for the viewing.

Best,
Craig

Hi Craig,


Thank you for sharing the samples.

First of all, the said problem is not caused due to the conversion of spreadsheet to HTML because it can be replicated if you save the resultant Workbook to spreadsheet format. That said, if you check your sample spreadsheet, you will notice there are a few hidden rows (13, 19 & 21) that is the reason your serial number is skipping these numbers as well. If you un-hide these rows before inserting the numbers (serial) in newly inserted column, you will not face this problem while converting to HTML. Please check the attached resultant spreadsheet as well as HTML in an archive and confirm if this is your expected result.

Java

protected static void addColRowHeader(Workbook book) {
for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
StyleFlag styleFlagGrid = new StyleFlag();
styleFlagGrid.setBorders(true);
sheet.getCells().insertColumn(0);
sheet.getCells().insertRow(0);
sheet.getCells().getRows().get(0).setHeight(15.75);
sheet.getCells().getColumns().get(0).setWidth(4.75);
int maxRow = sheet.getCells().getMaxDisplayRange().getRowCount();

sheet.getCells().unhideRows(0, maxRow, 15.75);

int maxCol = sheet.getCells().getMaxDisplayRange().getColumnCount();
for (int j = 1; j < maxRow; j++) {
sheet.getCells().get(j, 0).putValue(j);
}
for (int k = 1; k < maxCol; k++) {
sheet.getCells().get(0, k)
.putValue(CellsHelper.columnIndexToName(k - 1));
}
}
}

Hi Babar.Raza,


Thank you for the advise and the text appeared!

Further more, is there any way to make specific rows hidden again, just like the origin file, after this fix?

Hi Craig,


Yes, it is possible to re-hide the rows after you have injected new row & column headers. Before you un-hide the rows, please keep a track of rows which are originally hidden while using the Row.isHidden field. Once you have inserted new row and column, hide the rows again using the Cells.hideRow method. Please note, as you have inserted a new row at the top, you need to pass the row index to Cells.hideRow method such as (PreviouslyHiddenRowIndex + 1).

Please check following code that creates an ArrayList of hidden row indices before un-hiding all rows.

Java

ArrayList hiddenRows = new ArrayList();
Iterator rowsIterator = sheet.getCells().getRows().iterator();
while (rowsIterator.hasNext())
{
Row row = (Row)rowsIterator.next();
if(row.isHidden())
{
hiddenRows.add(row.getIndex());
}
}
sheet.getCells().unhideRows(0, sheet.getCells().getMaxDataRow(), 15.75);

Hi Babar.Raza


Thanks for your advice!

However, the text disappeared again.
Is there any way to make the result behavior exactly the same as the original Excel file? (The hidden rows remain hidden, and the text won’t disappear)

Here are the methods I apply:
protected void hideRows(Workbook book,
Map<Integer, List> hiddenRows) {
for(int i=0; i<book.getWorksheets().getCount();i++){
Worksheet sheet = book.getWorksheets().get(i);
for(Integer hiddenRow:hiddenRows.get(new Integer(i))){
sheet.getCells().hideRow(hiddenRow);
}
}
}

protected void addEmptyColumn(Workbook book) {

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
int columns = sheet.getCells().getMaxDisplayRange()
.getColumnCount();
if (columns > 0) {
sheet.getCells().insertColumn(columns);
sheet.getCells()
.clearRange(
0,
columns,
sheet.getCells().getMaxDisplayRange()
.getRowCount() - 1, columns);
sheet.getCells().getColumns().get(columns).setWidth(8.5);
}
}
}

protected Map<Integer,List> addColRowHeader(Workbook book) {

Map<Integer,List> hiddenRowResult = new HashMap<Integer,List>();
for (int i = 0; i < book.getWorksheets().getCount(); i++) {

Worksheet sheet = book.getWorksheets().get(i);

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

sheet.getCells().insertColumn(0);
sheet.getCells().insertRow(0);

sheet.getCells().getRows().get(0).setHeight(15.75);
sheet.getCells().getColumns().get(0).setWidth(4.75);

int maxRow =
// sheet.getCells().getMaxRow();
sheet.getCells().getMaxDisplayRange().getRowCount();
ArrayList hiddenRows = new ArrayList();
Iterator rowsIterator = sheet.getCells().getRows().iterator();
while (rowsIterator.hasNext())
{
Row row = (Row)rowsIterator.next();
if(row.isHidden())
{
hiddenRows.add(row.getIndex());
}
}
sheet.getCells().unhideRows(0, sheet.getCells().getMaxDataRow(), 15.75);
hiddenRowResult.put(i, hiddenRows);
int maxCol =
// sheet.getCells().getMaxColumn();
sheet.getCells().getMaxDisplayRange().getColumnCount();

for (int j = 1; j < maxRow; j++) {
sheet.getCells().get(j, 0).putValue(j);
}
for (int k = 1; k < maxCol; k++) {
sheet.getCells().get(0, k)
.putValue(CellsHelper.columnIndexToName(k - 1));
}
}
return hiddenRowResult;
}

protected void addColRowHeaderStyle(Workbook book) throws Exception {

for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);

StyleFlag styleFlagHeader = new StyleFlag();
styleFlagHeader.setAll(true);

Style headerStyle = book.createStyle();
headerStyle.setForegroundColor(Color.fromArgb(238, 238, 238));
headerStyle.setPattern(BackgroundType.SOLID);
headerStyle.getFont().setBold(true);
headerStyle.setVerticalAlignment(1);
headerStyle.setHorizontalAlignment(1);
headerStyle.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.BOTTOM_BORDER,
CellBorderType.THIN, Color.fromArgb(204, 204, 204));
headerStyle.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN,
Color.fromArgb(204, 204, 204));

int maxRow = sheet.getCells().getMaxRow();
int maxCol = sheet.getCells().getMaxColumn();

for (int k = 0; k <= maxRow; k++) {
sheet.getCells().get(k, 0)
.setStyle(headerStyle, styleFlagHeader);
}
for (int j = 0; j <= maxCol; j++) {
sheet.getCells().get(0, j)
.setStyle(headerStyle, styleFlagHeader);
}
}
}

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);
// set to Text
dataCell.getStyle().setNumber(49);
}
}
}
}


Here is the code I used for conversion:
Workbook book = new Workbook(“custom/input/xlsx/TEST0123.xlsx.xlsx”);
Map<Integer,List> hiddenRows;
this.addEmptyColumn(book);
hiddenRows = this.addColRowHeader(book);
this.addColRowHeaderStyle(book);
this.fixCellStyle(book);
this.hideRows(book, hiddenRows);
ByteArrayOutputStream baos = new ByteArrayOutputStream();

// for one sheet output only
for (int i = 0; i < book.getWorksheets().getCount(); i++) {
if (i != 0) {
book.getWorksheets().get(i).setVisible(false);
}
}
HtmlSaveOptions saveOps = new HtmlSaveOptions();
saveOps.setExportHiddenWorksheet(false);
saveOps.setClearData(false);
saveOps.setCreateDirectory(false);
saveOps.setExportActiveWorksheetOnly(false);
saveOps.setParseHtmlTagInCell(true);
saveOps.setEncoding(Encoding.getUTF8());
saveOps.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
saveOps.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
saveOps.setExportImagesAsBase64(false);

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”));

Best,
Craig

Hi Craig,

Thank you for writing back.

I have investigated the matter further while using the latest revision of Aspose.Cells for Java 8.9.0.7, and I am able to get the correct results in spreadsheet format (XLSX), however, conversion to HTML is still not working as expected, as the contents for cell A26 are missing when workbook is saved to HTML stream. I have raised this incident with product team under the ticket CELLSJAVA-41949 because the said behavior needs thorough investigation. Please spare us little time to properly analyze the case and revert back with updates in this regard.

We are sorry for the inconvenience caused.

@craig.w.su,

This is to inform you that we have fixed your issue. We will provide you the fixed version after performing QA and incorporating other enhancements and fixes.

The issues you have found earlier (filed as CELLSJAVA-41949) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi