use of org.apache.poi.ss.usermodel.Font in project jgnash by ccavanaugh.
the class BudgetResultsExport method exportBudgetResultsModel.
/**
* Exports a {@code BudgetResultsModel} to a spreadsheet.
*
* @param file File to save to
* @param model Results model to export
* @return Error message
*/
public static String exportBudgetResultsModel(final Path file, final BudgetResultsModel model) {
String message = null;
final ResourceBundle rb = ResourceUtils.getBundle();
final String extension = FileUtils.getFileExtension(file.toString());
try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
final CreationHelper createHelper = wb.getCreationHelper();
// create a new sheet
final Sheet s = wb.createSheet(model.getBudget().getName());
// create header cell styles
final CellStyle headerStyle = wb.createCellStyle();
// create 2 fonts objects
final Font amountFont = wb.createFont();
final Font headerFont = wb.createFont();
amountFont.setFontHeightInPoints((short) 10);
amountFont.setColor(IndexedColors.BLACK.getIndex());
headerFont.setFontHeightInPoints((short) 11);
headerFont.setColor(IndexedColors.BLACK.getIndex());
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Set the other cell style and formatting
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
DataFormat df_header = wb.createDataFormat();
headerStyle.setDataFormat(df_header.getFormat("text"));
headerStyle.setFont(headerFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
int row = 0;
Row r = s.createRow(row);
// create period headers
for (int i = 0; i < model.getDescriptorList().size(); i++) {
Cell c = r.createCell(i * 3 + 1);
c.setCellValue(createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
c.setCellStyle(headerStyle);
s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
}
{
int col = model.getDescriptorList().size() * 3 + 1;
Cell c = r.createCell(col);
c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
c.setCellStyle(headerStyle);
s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
}
// create results header columns
row++;
r = s.createRow(row);
{
Cell c = r.createCell(0);
c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
c.setCellStyle(headerStyle);
for (int i = 0; i <= model.getDescriptorList().size(); i++) {
c = r.createCell(i * 3 + 1);
c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
c.setCellStyle(headerStyle);
c = r.createCell(i * 3 + 2);
c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Actual")));
c.setCellStyle(headerStyle);
c = r.createCell(i * 3 + 3);
c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
c.setCellStyle(headerStyle);
}
}
// must sort the accounts, otherwise child structure is not correct
List<Account> accounts = new ArrayList<>(model.getAccounts());
accounts.sort(Comparators.getAccountByTreePosition(Comparators.getAccountByCode()));
// create account rows
for (final Account account : accounts) {
final DataFormat df = wb.createDataFormat();
final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
final CellStyle amountStyle = wb.createCellStyle();
amountStyle.setFont(amountFont);
amountStyle.setDataFormat(df.getFormat(pattern));
// Sets cell indentation, only impacts display if users changes the cell formatting to be left aligned.
amountStyle.setIndention((short) (model.getDepth(account) * 2));
row++;
int col = 0;
r = s.createRow(row);
CellStyle cs = wb.createCellStyle();
cs.cloneStyleFrom(headerStyle);
cs.setAlignment(CellStyle.ALIGN_LEFT);
cs.setIndention((short) (model.getDepth(account) * 2));
Cell c = r.createCell(col);
c.setCellValue(createHelper.createRichTextString(account.getName()));
c.setCellStyle(cs);
List<CellReference> budgetedRefList = new ArrayList<>();
List<CellReference> changeRefList = new ArrayList<>();
List<CellReference> remainingRefList = new ArrayList<>();
for (int i = 0; i < model.getDescriptorList().size(); i++) {
BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(results.getBudgeted().doubleValue());
c.setCellStyle(amountStyle);
CellReference budgetedRef = new CellReference(row, col);
budgetedRefList.add(budgetedRef);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(results.getChange().doubleValue());
c.setCellStyle(amountStyle);
CellReference changeRef = new CellReference(row, col);
changeRefList.add(changeRef);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_FORMULA);
c.setCellStyle(amountStyle);
c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
CellReference remainingRef = new CellReference(row, col);
remainingRefList.add(remainingRef);
}
// add summary columns
addSummaryCell(r, ++col, budgetedRefList, amountStyle);
addSummaryCell(r, ++col, changeRefList, amountStyle);
addSummaryCell(r, ++col, remainingRefList, amountStyle);
}
// add group summary rows
for (final AccountGroup group : model.getAccountGroupList()) {
final DataFormat df = wb.createDataFormat();
final CellStyle amountStyle = wb.createCellStyle();
amountStyle.setFont(amountFont);
amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
amountStyle.setBorderTop(CellStyle.BORDER_THIN);
amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
amountStyle.setBorderRight(CellStyle.BORDER_THIN);
final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
final String pattern = format.toLocalizedPattern().replace("¤", model.getBaseCurrency().getPrefix());
amountStyle.setDataFormat(df.getFormat(pattern));
row++;
int col = 0;
r = s.createRow(row);
CellStyle cs = wb.createCellStyle();
cs.cloneStyleFrom(headerStyle);
cs.setAlignment(CellStyle.ALIGN_LEFT);
Cell c = r.createCell(col);
c.setCellValue(createHelper.createRichTextString(group.toString()));
c.setCellStyle(cs);
List<CellReference> budgetedRefList = new ArrayList<>();
List<CellReference> changeRefList = new ArrayList<>();
List<CellReference> remainingRefList = new ArrayList<>();
for (int i = 0; i < model.getDescriptorList().size(); i++) {
BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(results.getBudgeted().doubleValue());
c.setCellStyle(amountStyle);
CellReference budgetedRef = new CellReference(row, col);
budgetedRefList.add(budgetedRef);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(results.getChange().doubleValue());
c.setCellStyle(amountStyle);
CellReference changeRef = new CellReference(row, col);
changeRefList.add(changeRef);
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_FORMULA);
c.setCellStyle(amountStyle);
c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
CellReference remainingRef = new CellReference(row, col);
remainingRefList.add(remainingRef);
}
// add summary columns
addSummaryCell(r, ++col, budgetedRefList, amountStyle);
addSummaryCell(r, ++col, changeRefList, amountStyle);
addSummaryCell(r, ++col, remainingRefList, amountStyle);
}
// force evaluation
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateAll();
short columnCount = s.getRow(1).getLastCellNum();
// autosize all of the columns + 10 pixels
for (int i = 0; i <= columnCount; i++) {
s.autoSizeColumn(i);
s.setColumnWidth(i, s.getColumnWidth(i) + 10);
}
Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
// Save
String filename = file.toString();
if (wb instanceof XSSFWorkbook) {
filename = FileUtils.stripFileExtension(filename) + ".xlsx";
} else {
filename = FileUtils.stripFileExtension(filename) + ".xls";
}
try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
wb.write(out);
} catch (final Exception e) {
Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
message = e.getLocalizedMessage();
}
} catch (IOException e) {
Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
return message;
}
use of org.apache.poi.ss.usermodel.Font in project jgnash by ccavanaugh.
the class AccountExport method exportAccount.
public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) {
Objects.requireNonNull(account);
Objects.requireNonNull(startDate);
Objects.requireNonNull(endDate);
Objects.requireNonNull(file);
Objects.requireNonNull(columnNames);
final String extension = FileUtils.getFileExtension(file.getAbsolutePath());
try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
final CreationHelper createHelper = wb.getCreationHelper();
// create a new sheet
final Sheet s = wb.createSheet(account.getName());
// create 2 fonts objects
final Font defaultFont = wb.createFont();
final Font headerFont = wb.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setColor(IndexedColors.BLACK.getIndex());
headerFont.setFontHeightInPoints((short) 11);
headerFont.setColor(IndexedColors.BLACK.getIndex());
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
// create header cell styles
final CellStyle headerStyle = wb.createCellStyle();
// Set the other cell style and formatting
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
DataFormat df_header = wb.createDataFormat();
headerStyle.setDataFormat(df_header.getFormat("text"));
headerStyle.setFont(headerFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
final CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy"));
dateStyle.setFont(defaultFont);
final CellStyle timestampStyle = wb.createCellStyle();
timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS"));
timestampStyle.setFont(defaultFont);
final CellStyle textStyle = wb.createCellStyle();
textStyle.setFont(defaultFont);
final CellStyle amountStyle = wb.createCellStyle();
amountStyle.setFont(defaultFont);
amountStyle.setAlignment(CellStyle.ALIGN_RIGHT);
final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
final DataFormat df = wb.createDataFormat();
amountStyle.setDataFormat(df.getFormat(pattern));
// Create headers
int row = 0;
Row r = s.createRow(row);
for (int i = 0; i < columnNames.length; i++) {
Cell c = r.createCell(i);
c.setCellValue(createHelper.createRichTextString(columnNames[i]));
c.setCellStyle(headerStyle);
}
// Dump the transactions
for (final Transaction transaction : account.getTransactions(startDate, endDate)) {
r = s.createRow(++row);
int col = 0;
// date
Cell c = r.createCell(col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
c.setCellStyle(dateStyle);
// timestamp
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
c.setCellStyle(timestampStyle);
// number
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(transaction.getNumber());
c.setCellStyle(textStyle);
// payee
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(transaction.getPayee());
c.setCellStyle(textStyle);
// memo
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(transaction.getMemo());
c.setCellStyle(textStyle);
// account
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(getAccountColumnValue(transaction, account));
c.setCellStyle(textStyle);
// clr
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_STRING);
c.setCellValue(transaction.getReconciled(account).toString());
c.setCellStyle(textStyle);
final BigDecimal amount = transaction.getAmount(account);
// increase
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (amount.signum() >= 0) {
c.setCellValue(amount.doubleValue());
}
c.setCellStyle(amountStyle);
// decrease
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (amount.signum() < 0) {
c.setCellValue(amount.abs().doubleValue());
}
c.setCellStyle(amountStyle);
// balance
c = r.createCell(++col);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(account.getBalanceAt(transaction).doubleValue());
c.setCellStyle(amountStyle);
}
// autosize the column widths
final short columnCount = s.getRow(1).getLastCellNum();
// autosize all of the columns + 10 pixels
for (int i = 0; i <= columnCount; i++) {
s.autoSizeColumn(i);
s.setColumnWidth(i, s.getColumnWidth(i) + 10);
}
Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
// Save
final String filename;
if (wb instanceof XSSFWorkbook) {
filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx";
} else {
filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls";
}
try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
wb.write(out);
} catch (final Exception e) {
Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
} catch (final IOException e) {
Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
}
use of org.apache.poi.ss.usermodel.Font in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetCache method createBaseHeaderFont.
private Font createBaseHeaderFont() {
Font font = mWorkbook.createFont();
font.setBold(true);
return font;
}
use of org.apache.poi.ss.usermodel.Font in project poi by apache.
the class ToHtml method fontStyle.
private void fontStyle(CellStyle style) {
Font font = wb.getFontAt(style.getFontIndex());
if (font.getBold()) {
out.format(" font-weight: bold;%n");
}
if (font.getItalic()) {
out.format(" font-style: italic;%n");
}
int fontheight = font.getFontHeightInPoints();
if (fontheight == 9) {
//fix for stupid ol Windows
fontheight = 10;
}
out.format(" font-size: %dpt;%n", fontheight);
// Font color is handled with the other colors
}
use of org.apache.poi.ss.usermodel.Font in project poi by apache.
the class CellComments method main.
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook();
CreationHelper factory = wb.getCreationHelper();
Sheet sheet = wb.createSheet();
Cell cell1 = sheet.createRow(3).createCell(5);
cell1.setCellValue("F4");
Drawing<?> drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = factory.createClientAnchor();
Comment comment1 = drawing.createCellComment(anchor);
RichTextString str1 = factory.createRichTextString("Hello, World!");
comment1.setString(str1);
comment1.setAuthor("Apache POI");
cell1.setCellComment(comment1);
Cell cell2 = sheet.createRow(2).createCell(2);
cell2.setCellValue("C3");
Comment comment2 = drawing.createCellComment(anchor);
RichTextString str2 = factory.createRichTextString("XSSF can set cell comments");
//apply custom font to the text in the comment
Font font = wb.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBold(true);
font.setColor(IndexedColors.RED.getIndex());
str2.applyFont(font);
comment2.setString(str2);
comment2.setAuthor("Apache POI");
comment2.setAddress(new CellAddress("C3"));
String fname = "comments.xlsx";
FileOutputStream out = new FileOutputStream(fname);
wb.write(out);
out.close();
wb.close();
}
Aggregations