Hi Aspose team
We are using Aspose Cells 8.8.3 to convert Excel file to HTML format for cross-platform reading.
We found a Excel file ‘s background colors are different from the HTML conversion result.
Here are the code we used for conversion test:
Workbook book = new Workbook(“custom/input/xlsx/報告.xlsx”);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
//addEmptyColumn(book);
//addColRowHeader(book);
//addColRowHeaderStyle(book);
//fixCellStyle(book);
// for ony one page output
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);
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”));
In addition, we add some methods to add header and style before saving to HTML format to make the result more like it shows in MS Office Excel.
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);
dataCell.getStyle().setNumber(49);
}
}
}
}
Furthermore, we set the style for the headers we add, and they should be gray-like, but their colors and styles seems to be affected by the content.
In conclusion, here are 2 problems in the result of conversion to HTML:
1. Some of background colors are different from the origin Excel file.
2. The styles we want can not be set to some of headers we added.
Please help us to find how to solve these problems or any options we can use to fix these.
P.S. I uploaded comparison images, the Excel file, and the result of conversion to HTML format.
