This code embeds a hyperlink into a cell of your choice of an Excel file.
/**
* @param pathName the path for the Excel file which you will update
* @param string the string you want to put in the cell, and if you put an empty string, it will default to
* "Empty String"
* @param hyperlink the hyperlink which will be embedded into the cell
* @param row the row number of the cell
* @param column the column number of the cell
* @param firstTime this is used in case you want the file to be wiped and start from the beginning
* @throws RuntimeException if the path can't be found or if an {@link IOException} is thrown for whatever reason
*/
public static void writeToCell(String pathName, String string, String hyperlink, int row, int column, boolean
firstTime) {
if (string.equals(""))
string = "Empty String";
if (!firstTime) {
try {
fileInputStream = new FileInputStream(new File(pathName));
} catch (FileNotFoundException e) {
throw new RuntimeException("File not found!", e);
}
try {
workbook = new XSSFWorkbook(fileInputStream);
sheet = workbook.getSheetAt(0);
} catch (IOException e) {
throw new RuntimeException("IOException thrown!", e);
}
} else {
workbook = new XSSFWorkbook();
sheet = workbook.createSheet();
}
CreationHelper createHelper = workbook.getCreationHelper();
XSSFCell cell;
try {
cell = sheet.getRow(row).getCell(column, XSSFRow.CREATE_NULL_AS_BLANK);
} catch (NullPointerException e) {
try {
cell = sheet.getRow(row).createCell(column);
} catch (NullPointerException npe) {
cell = sheet.createRow(row).createCell(column);
}
}
XSSFHyperlink url = (XSSFHyperlink) createHelper.createHyperlink(XSSFHyperlink.LINK_URL);
XSSFCellStyle hyperlinkStyle = workbook.createCellStyle();
XSSFFont hlink_font = workbook.createFont();
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hyperlinkStyle.setFont(hlink_font);
url.setAddress(hyperlink);
try {
cell.setHyperlink(url);
} catch (NullPointerException e) {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setHyperlink(url);
}
cell.setCellValue(string);
cell.setCellStyle(hyperlinkStyle);
try {
if (fileInputStream != null)
fileInputStream.close();
} catch (IOException e) {
throw new RuntimeException("IOException thrown!", e);
}
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(new File(pathName));
} catch (FileNotFoundException e) {
throw new RuntimeException("File not found!", e);
}
try {
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (IOException e) {
throw new RuntimeException("IOException thrown!", e);
}
}
public static HyperlinkWriter newInstance(String pathName) {
HyperlinkWriter writer = new HyperlinkWriter();
try {
fileInputStream = new FileInputStream(new File(pathName));
} catch (FileNotFoundException e) {
throw new RuntimeException("File not found!", e);
}
try {
workbook = new XSSFWorkbook(fileInputStream);
sheet = workbook.getSheetAt(0);
} catch (IOException e) {
throw new RuntimeException("IOException thrown!", e);
}
return writer;
}
I was wondering if there was a way to optimize this method in a way because I use this with a different class and I call upon this method multiple times but since I'm creating a FileInputStream and an XSSFWorkbook almost each time, I was wondering if there was a way to get around that and have the FileInputStream just add the bytes that it's missing, instead of reading the file from top to bottom almost each time the method is called. I should also mention that fileInputStream is static, so it won't be released from memory when the method ends (I think...)
1 Answer 1
Some thoughts:
Use methods! Break out complexities where you can to make it easier on readers of your code.
Avoid statics. There's no reason for it. Make a reusable class instead.
It also helps to rearrange the member variables so they're declared right before they're used.
You really shouldn't have your three static instance variables as static instance variables.
To my knowledge, no, POI does not support anything other than reading in the whole file, making your change, and then writing the whole file.
If you're going to make lots of these calls, create an object that holds the four relevant pieces of information (label, link, row, column), then create them and stick them in a queue. When you've got all of them, open the workbook once and add them all. Might not be worth the complexity - are you sure you have a real performance bottleneck, or are you guessing?
The exception handling needs work, and it only probably compiles, but this might get you pointed in a good direction. Note that it is not thread-safe! An interesting addition might be to make it implement AutoCloseable so that clients could use it in a try-with-resources block, and have it write automatically when they were done with it. Just add a close() method that calls flush, and a check in all public methods to make sure close() hasn't been called yet.
public final class ExcelFileEditor {
private final File file;
private final List<HyperlinkAddition> hyperlinkAdditions = new LinkedList<>();
public ExcelFileEditor(final File file) {
this.file = file;
}
public void setHyperlink(
final String label,
final String hyperlink,
final int row,
final int column)
throws IOException {
this.hyperlinkAdditions.add(new HyperlinkAddition(label, hyperlink, row, column));
}
public void flush()
throws IOException {
final XSSFWorkbook workbook = this.loadWorkbook();
for (final HyperlinkAddition hyperlinkAddition : this.hyperlinkAdditions) {
this.setHyperlink(
workbook,
hyperlinkAddition.label,
hyperlinkAddition.hyperlink,
hyperlinkAddition.rowIndex,
hyperlinkAddition.columnIndex);
}
this.hyperlinkAdditions.clear();
this.writeWorkbook(workbook);
}
private void setHyperlink(
final XSSFWorkbook workbook,
final String label,
final String hyperlink,
final int rowIndex,
final int columnIndex) {
final CreationHelper createHelper = workbook.getCreationHelper();
final XSSFHyperlink url = createHelper.createHyperlink(XSSFHyperlink.LINK_URL);
url.setAddress(hyperlink);
final XSSFFont hyperlinkFont = workbook.createFont();
hyperlinkFont.setColor(IndexedColors.BLUE.getIndex());
final XSSFCellStyle hyperlinkStyle = workbook.createCellStyle();
hyperlinkStyle.setFont(hyperlinkFont);
final XSSFSheet sheet = this.getSheet(workbook);
final XSSFCell cell = this.getCell(sheet, rowIndex, columnIndex);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setHyperlink(url);
cell.setCellValue(this.buildCellLabel(label));
cell.setCellStyle(hyperlinkStyle);
}
private XSSFWorkbook loadWorkbook()
throws IOException {
if (!this.file.exists()) {
return new XSSFWorkbook();
}
try (FileInputStream fis = new FileInputStream(this.file)) {
return new XSSFWorkbook(fis);
}
}
private XSSFSheet getSheet(final XSSFWorkbook workbook) {
final XSSFSheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return workbook.createSheet();
}
return sheet;
}
private XSSFCell getCell(final XSSFSheet sheet, final int rowIndex, final int columnIndex) {
final XSSFRow row = this.getRow(sheet, rowIndex);
final XSSFCell cell = row.getCell(columnIndex, XSSFRow.CREATE_NULL_AS_BLANK);
if (cell == null) {
return row.createCell(columnIndex);
}
return cell;
}
private XSSFRow getRow(final XSSFSheet sheet, final int rowIndex) {
final XSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
return sheet.createRow(rowIndex);
}
return row;
}
private String buildCellLabel(final String label) {
if (label.isEmpty()) {
return "Empty String";
}
return label;
}
private void writeWorkbook(final XSSFWorkbook workbook)
throws IOException {
final FileOutputStream fileOutputStream = null;
try (FileOutputStream fos = new FileOutputStream(this.file)) {
workbook.write(fos);
}
}
private static final class HyperlinkAddition {
public final String label;
public final String hyperlink;
public final int rowIndex;
public final int columnIndex;
public HyperlinkAddition(
final String label,
final String hyperlink,
final int rowIndex,
final int columnIndex) {
this.label = label;
this.hyperlink = hyperlink;
this.rowIndex = rowIndex;
this.columnIndex = columnIndex;
}
}
}