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