de.h2b.java.lib.office.ExcelWriter Maven / Gradle / Ivy
The newest version!
/*
PA-Toolbox -- Predictive Analytics Java Toolbox
Copyright 2014-2016 Hans-Hermann Bode
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
package de.h2b.java.lib.office;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author h2b
*
*/
public class ExcelWriter {
/**
* Enumerates Excel file format constants.
*
*/
public enum FileFormat {
/**
* HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
*/
HSSF,
/**
* XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
*/
XSSF
}
private FileFormat fileFormat = FileFormat.XSSF;
private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
private String dateFormat = DEFAULT_DATE_FORMAT;
private CellStyle dateStyle, wrapStyle;
private int maxColumnWidth = Integer.MAX_VALUE;
private final File file;
private final boolean appending;
private final Map>>> tableMap =
new LinkedHashMap>>>();
//preserve insertion order
/**
* Always creates a new file.
*
* @param file
*/
public ExcelWriter(File file) {
this(file, false);
}
/**
* In appending mode, the file must already exist and contain valid Excel
* data; in non-appending mode, creates a new file.
*
* @param file
* @param appending
*/
public ExcelWriter(File file, boolean appending) {
super();
this.file = file;
this.appending = appending;
}
/**
* Adds the named table with the given content to the spreadsheet.
* If a table with that name already exists, it will be overridden.
*
* The order by which tables are added is preserved in the output file.
*
* @param tableName
* @param content
*/
public void put(String tableName, List>> content) {
tableMap.put(tableName, content);
}
/**
* Writes the spreadsheet to the file specified by the constructor call.
*
* This method may be called more than once. In this case the following
* applies:If in appending mode, all tables added since the last write are
* written, so that already written data are not appended again; if not in
* appending mode, simply all tables added so far are written, thereby
* overwriting all data written before.
*
* @throws IOException
* @throws OfficeFormatException when the file has invalid format (appending mode only)
*/
public void write() throws IOException, OfficeFormatException {
Workbook workbook;
InputStream inputStream = null;
if (appending) {
//we have to open the workbook by an input stream rather than by a file directly,
//since later it is witten to an output stream created from the same file;
//disregarding these facts will result in a crash when closing the output stream
inputStream = new FileInputStream(file);
try {
workbook = WorkbookFactory.create(inputStream);
} catch (InvalidFormatException e) {
//get rid of POI-specific extension
throw new OfficeFormatException(e);
}
} else {
switch (fileFormat) {
case HSSF:
workbook = new HSSFWorkbook();
break;
case XSSF:
workbook = new XSSFWorkbook();
break;
default:
throw new AssertionError(fileFormat);
}
}
CreationHelper helper = workbook.getCreationHelper();
dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(helper.createDataFormat().getFormat(dateFormat));
wrapStyle = workbook.createCellStyle();
wrapStyle.setWrapText(true);
for (String tableName : tableMap.keySet()) {
Sheet sheet;
int startRowIdx;
if (appending && (sheet=workbook.getSheet(tableName))!=null) {
startRowIdx = sheet.getLastRowNum()+1;
} else {
sheet = workbook.createSheet(tableName);
startRowIdx = 0;
}
generateSheet(sheet, startRowIdx, tableMap.get(tableName));
}
if (inputStream!=null) {
inputStream.close();
}
OutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
outputStream.close();
workbook.close();
if (appending) {
//prevent from appending the same data again
tableMap.clear();
}
}
private void generateSheet(Sheet sheet, int startRowIdx, List>> content) {
if (content==null) {
return;
}
int rowcount = startRowIdx;
for (List> list : content) {
Row row = sheet.createRow(rowcount++);
int cellcount = 0;
for (TableElement element : list) {
Cell cell = row.createCell(cellcount++);
Object value = element.get();
if (value instanceof Boolean) {
cell.setCellValue((Boolean)value);
} else if (value instanceof Number) {
cell.setCellValue(((Number)value).doubleValue());
} else if (value instanceof String) {
cell.setCellValue((String)value);
adjustWidth(cell, ((String)value).length());
} else if (value instanceof Date) {
cell.setCellValue((Date)value);
cell.setCellStyle(dateStyle);
adjustWidth(cell, dateFormat.length());
} else if (value instanceof Calendar) {
cell.setCellValue((Calendar)value);
cell.setCellStyle(dateStyle);
adjustWidth(cell, dateFormat.length());
} else {
throw new AssertionError(value);
}
}
}
}
private void adjustWidth(Cell cell, int length) {
if (length>maxColumnWidth) {
length = maxColumnWidth;
cell.setCellStyle(wrapStyle);
short height = (short) (20*12*(maxColumnWidth/length+1)); //by rule of thumb
if (height>cell.getRow().getHeight()) {
cell.getRow().setHeight(height);
}
}
int cellWidth = 256*(length+2); //allow for some additional space
Sheet sheet = cell.getSheet();
int columnIndex = cell.getColumnIndex();
int currentWidth = sheet.getColumnWidth(columnIndex);
if (cellWidth>currentWidth) {
sheet.setColumnWidth(columnIndex, cellWidth);
}
}
/**
* The format returned will only be used if not in appending mode.
*
* @return the fileFormat
*/
public FileFormat getFileFormat() {
return fileFormat;
}
/**
* Will be ignored if in appending mode.
*
* @param fileFormat the fileFormat to set
*/
public void setFileFormat(FileFormat format) {
this.fileFormat = format;
}
/**
* @return the dateFormat
*/
public String getDateFormat() {
return dateFormat;
}
/**
* The string must be appropriate for
* {@link java.text.DateFormat#parse(String)}.
*
* The default value is {@value #DEFAULT_DATE_FORMAT}.
*
* @param dateFormat the dateFormat to set
*/
public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
/**
* The width measures approximately in number of characters to hold.
*
* @return the maxColumnWidth
*/
public int getMaxColumnWidth() {
return maxColumnWidth;
}
/**
* The width measures approximately in number of characters to hold.
*
* @param maxColumnWidth the maxColumnWidth to set
*/
public void setMaxColumnWidth(int maxColumnWidth) {
this.maxColumnWidth = maxColumnWidth;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy