A regular spreadsheet (tab) in Excel can be made into a table by selecting the data and Cntl-T. (This gives each column tables header that allows filtering of values)
My spreadsheets are created using Apache-Poi and the SXSSF streaming interface, I have to use the streaming interface since the spreadsheet can be quite large. Is it possible to programmatically turn each sheet into a table
I came across XSSFTable class but not clear how to use it and whether it can be used in streaming mode.
package com.jthink.songkong.reports.spreadsheet;
import com.jthink.songkong.db.SongCache;
import com.jthink.songkong.text.SongFieldName;
import com.jthink.songkong.ui.MainWindow;
import com.jthink.songlayer.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.hibernate.Session;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by Paul on 09/03/2017.
*/
public class SpreadsheetReport
{
//Max Width we want to use in chars, whatever the data
public static final int MAX_COL_WIDTH = 100;
public static final int COL_WIDTH_MULTIPLIER = 256;
private static final int FLUSH_SIZE = 1000;
public static final int FONT_MARGIN_OF_ERROR = 5;
private String reportName;
private FileOutputStream fos;
private int rowCounter =0;
private SXSSFWorkbook workbook;
private CellStyle headerStyle;
private CellStyle fieldAddedStyle;
private CellStyle fieldChangedStyle;
private CellStyle fieldDeletedStyle;
private CellStyle fieldUnchangedStyle;
private List<Worksheet> worksheets;
private CreationHelper factory;
public SpreadsheetReport(String reportName) throws IOException
{
this.reportName =reportName;
//Create new File
fos = new FileOutputStream(reportName);
workbook = new SXSSFWorkbook(FLUSH_SIZE);
factory = workbook.getCreationHelper();
worksheets = new ArrayList<Worksheet>();
worksheets.add(0, new BasicWorksheet(workbook));
worksheets.add(1, new ReleaseWorksheet(workbook));
worksheets.add(2, new ClassicalWorksheet(workbook));
worksheets.add(3, new PeopleWorksheet(workbook));
worksheets.add(4, new SortWorksheet(workbook));
worksheets.add(5, new MusicBrainzWorksheet(workbook));
worksheets.add(6, new MusicBrainzWorkWorksheet(workbook));
worksheets.add(7, new AcousticBrainzWorksheet(workbook));
Font font = workbook.createFont();
font.setBold(true);
headerStyle = workbook.createCellStyle();
headerStyle.setFont(font);
headerStyle.setWrapText(true);
fieldAddedStyle = workbook.createCellStyle();
fieldAddedStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
fieldAddedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldAddedStyle.setWrapText(true);
fieldChangedStyle = workbook.createCellStyle();
fieldChangedStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
fieldChangedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldChangedStyle.setWrapText(true);
fieldDeletedStyle = workbook.createCellStyle();
fieldDeletedStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
fieldDeletedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldDeletedStyle.setWrapText(true);
fieldUnchangedStyle = workbook.createCellStyle();
fieldUnchangedStyle.setWrapText(true);
}
public void writeHeader() throws IOException
{
for(Worksheet next:worksheets)
{
Row r = next.getSheet().createRow(rowCounter);
for(int i=0;i <next.getMapping().size(); i++)
{
addHeaderCell(r, i, next.getMapping().get(i));
}
}
rowCounter++;
}
private void addHeaderCell(Row r, int spreadsheetIndex, SongFieldNameColumnWidth field)
{
Cell cell = r.createCell(spreadsheetIndex);
cell.setCellValue(field.getSongFieldName().getName());
cell.setCellStyle(headerStyle);
}
public void writeDatatoXlsFile(Session session, Song song, List<SongChanges> songChanges) throws IOException
{
for(Worksheet next:worksheets)
{
Row r = next.getSheet().createRow(rowCounter);
org.apache.poi.ss.usermodel.Cell c ;
c=r.createCell(0);
c.setCellValue(song.getFilename());
Map<SongFieldKey, SongChanges> fieldToChanges = new HashMap<SongFieldKey, SongChanges>();
for(SongChanges nextChange:songChanges)
{
fieldToChanges.put(nextChange.getField(), nextChange);
}
for (int i = 0; i < next.getMapping().size(); i++)
{
addFieldValue(session, next , r, i, next.getMapping().get(i), fieldToChanges.get(next.getMapping().get(i).getSongFieldName().getSongFieldKey()), song);
}
}
rowCounter++;
}
/**
* Show the old value as a comment/ttoltip
* @param r
* @param c
* @param sheet
* @param value
*/
private void addCellComment(Row r, Cell c, Worksheet sheet, String value)
{
String formattedValue = value.replace('\u0000', '\n');
int rowCount = value.split("\\\\u000").length;
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(c.getColumnIndex());
//Wider for filename column
if(c.getColumnIndex()==0)
{
anchor.setCol2(c.getColumnIndex()+5);
}
else
{
anchor.setCol2(c.getColumnIndex()+2);
}
anchor.setRow1(r.getRowNum());
anchor.setRow2(r.getRowNum()+rowCount);
// Create the comment and set the text+author
Drawing drawing = sheet.getDrawing();
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(formattedValue);
comment.setString(str);
c.setCellComment(comment);
}
private void addFieldValue(Session session, Worksheet sheet, Row r, int spreadsheetIndex, SongFieldNameColumnWidth sfncw, SongChanges fieldChanges, Song song)
{
SongFieldName songFieldName = sfncw.getSongFieldName();
String value = "";
org.apache.poi.ss.usermodel.Cell c ;
c=r.createCell(spreadsheetIndex);
//Chnanges have been made to this field
if(fieldChanges!=null)
{
SongChangeType changeType = fieldChanges.getType();
if(songFieldName==SongFieldName.FILENAME)
{
value = fieldChanges.getNewValue();
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
//addCellComment(r, c, sheet, song.getFilename());
}
else if(songFieldName==SongFieldName.COVER_ART)
{
if (changeType == SongChangeType.ADD )
{
CoverImage ci = SongCache.findCoverImageByDataKey(session, fieldChanges.getNewValue());
if(ci!=null)
{
value = ci.getWidth() + " x " + ci.getHeight();
c.setCellValue(value);
c.setCellStyle(fieldAddedStyle);
}
else
{
MainWindow.logger.severe("Unable to find coverimage for song:"+song.getRecNo()+":"+song.getFilename());
}
session.flush();
}
else if (changeType == SongChangeType.EDIT)
{
CoverImage ci = SongCache.findCoverImageByDataKey(session, fieldChanges.getNewValue());
if(ci!=null)
{
value = ci.getWidth() + " x " + ci.getHeight();
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
}
else
{
MainWindow.logger.severe("Unable to find coverimage for song:"+song.getRecNo()+":"+song.getFilename());
}
CoverImage ciOrig = SongCache.findCoverImageByDataKey(session, fieldChanges.getOriginalValue());
if(ciOrig!=null)
{
String valueOrig = ciOrig.getWidth() + " x " + ciOrig.getHeight();
//addCellComment(r, c, sheet, valueOrig);
}
session.flush();
}
else if (changeType == SongChangeType.NOCHANGE)
{
CoverArt coverart = song.getCoverArts().get(0);
value = coverart.getCoverImage().getWidth()+" x" + coverart.getCoverImage().getHeight();
c.setCellValue(value);
c.setCellStyle(fieldUnchangedStyle);
}
else if (changeType == SongChangeType.DELETE)
{
c.setCellStyle(fieldDeletedStyle);
}
}
else
{
if (changeType == SongChangeType.ADD)
{
value = fieldChanges.getNewValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldAddedStyle);
}
else if (changeType == SongChangeType.EDIT)
{
value = fieldChanges.getNewValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
//addCellComment(r, c, sheet, fieldChanges.getOriginalValue());
}
else if (changeType == SongChangeType.DELETE)
{
c.setCellStyle(fieldDeletedStyle);
}
else if (changeType == SongChangeType.NOCHANGE)
{
value = fieldChanges.getOriginalValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldUnchangedStyle);
//addCellComment(r, c, sheet, fieldChanges.getOriginalValue());
}
}
}
//Field is unchanged
//Note shoud not be called because we now log unchanged fields as well
else
{
if(songFieldName==SongFieldName.FILENAME)
{
value = song.getFilename();
c.setCellValue(value);
}
else if(songFieldName==SongFieldName.COVER_ART)
{
if(song.getCoverArts().size()>0)
{
CoverArt coverart = song.getCoverArts().get(0);
value = coverart.getCoverImage().getWidth()+" x" + coverart.getCoverImage().getHeight();
c.setCellValue(value);
}
}
else
{
value = song.getFieldValueOrEmptyString(songFieldName.getSongFieldKey()).replace('\u0000', '\n');
c.setCellStyle(fieldUnchangedStyle);
c.setCellValue(value);
}
}
sfncw.setColumnWidthFromValue(value);
}
public void finish() throws IOException
{
for (Worksheet next : worksheets)
{
SXSSFSheet sheet = (SXSSFSheet)next.getSheet();
for (int i = 0; i < next.getMapping().size(); i++)
{
int columnWidth = next.getMapping().get(i).getColumnWidthFromValue() + FONT_MARGIN_OF_ERROR;
columnWidth = columnWidth > MAX_COL_WIDTH ? MAX_COL_WIDTH : columnWidth;
sheet.setColumnWidth(i, columnWidth * COL_WIDTH_MULTIPLIER);
}
}
workbook.write(fos);
fos.close();
workbook.dispose();
}
}
