List of usage examples for org.apache.poi.ss.usermodel Sheet getColumnWidth
int getColumnWidth(int columnIndex);
Character width is defined as the maximum digit width of the numbers From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java License:Apache License From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java License:Apache License From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java From source file:jgnash.convert.exportantur.ssf.AccountExport.java License:Open Source License From source file:jgnash.convert.exports.ssf.AccountExport.java License:Open Source License From source file:jgnash.engine.budget.BudgetResultsExport.java License:Open Source License From source file:lucee.runtime.poi.Excel.java License:Open Source License From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java License:Open Source License From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java License:Open Source License From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java License:Open Source License0, 1, 2, ... Usage
/**
* Finalise la cration de la feuille de calcul, notamment en demandant le
* redimensionnement automatique des colonnes.
* //from w ww . ja v a 2 s . c o m
* @param sheet feuilles de calcul
* @param columnInfos map contenant l'en-tte et les informations d'une colonne
* @param landscapePrintSetup dfinit si la feuille est imprime en paysage ou non
*/
protected void finalizeSheet(Sheet sheet, RangeMap<Integer, ColumnInformation> columnInfos,
boolean landscapePrintSetup) {
for (Map.Entry<Range<Integer>, ColumnInformation> entry : columnInfos.asMapOfRanges().entrySet()) {
ColumnInformation columnInformation = entry.getValue();
Range<Integer> range = entry.getKey();
int beginIndex = range.lowerEndpoint();
int endIndex = range.upperEndpoint();
// Dtermination de la taille maximum de cette colonne
int maxColumnWidth;
if (columnInformation.getColumnMaxWidth() != -1) {
maxColumnWidth = columnInformation.getColumnMaxWidth();
} else {
maxColumnWidth = ABSOLUTE_MAX_COLUMN_WIDTH;
}
// Dtermination de la taille souhaite pour la colonne
if (columnInformation.getColumnWidth() != -1) {
// On force la taille des colonnes en fonction de la columnInformation
int columnWidth = columnInformation.getColumnWidth();
columnWidth = Math.min(columnWidth, maxColumnWidth);
// On prend en compte le fait que la "colonne" peut s'tendre en fait sur plusieurs colonnes (fusion de cellules au niveau du header)
int columnSpan = endIndex - beginIndex + 1;
columnWidth = columnWidth / columnSpan;
// On redimmensionne les colonnes
for (int columnIndex = beginIndex; columnIndex <= endIndex; ++columnIndex) {
sheet.setColumnWidth(columnIndex, columnWidth);
}
} else {
// On redimmensionne les colonnes une une en prennant leur taille actuelle et en les augmentant un petit peu
for (int columnIndex = beginIndex; columnIndex <= endIndex; ++columnIndex) {
sheet.autoSizeColumn(columnIndex);
int columnWidth = (int) (sheet.getColumnWidth(beginIndex) * COLUMN_RESIZE_RATIO);
columnWidth = Math.min(columnWidth, maxColumnWidth);
sheet.setColumnWidth(columnIndex, columnWidth);
}
}
}
finalizeSheet(sheet, landscapePrintSetup);
}
/**
* Redimensionne les colonnes qui contiennent des rgions fusionnes.
* //from w w w . j a v a 2s . c om
* Dans POI, les rgions fusionnes ne sont pas prises en compte dans le autoSizeColumn.
* Quand on fusionne des cellules sur une mme colonne, on corrige la taille de cette colonne si ncessaire.
*
* @param sheet feuille de calcul
* @param columns map contenant l'en-tte et les informations d'une colonne
*/
protected void resizeMergedColumns(Sheet sheet, Collection<ColumnInformation> columns) {
if (sheet.getNumMergedRegions() > 0) {
List<ColumnInformation> columnsInfo = new ArrayList<ColumnInformation>(columns);
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
if (mergedRegion.getFirstColumn() == mergedRegion.getLastColumn()) {
int columnIndex = mergedRegion.getFirstColumn();
String headerText = getColumnLabel(columnsInfo.get(columnIndex).getHeaderKey());
int headerSize = (int) (headerText.length() * 300 * COLUMN_RESIZE_RATIO);
if (sheet.getColumnWidth(columnIndex) < headerSize) {
sheet.setColumnWidth(columnIndex, headerSize);
}
}
}
}
}
/**
* Given a sheet, this method deletes a column from a sheet and moves
* all the columns to the right of it to the left one cell.
*
* Note, this method will not update any formula references.
*
* @param sheet//from www . j a v a 2 s . c om
* @param column
*/
private static void deleteColumn(Sheet sheet, int columnToDelete) {
int maxColumn = 0;
for (int iii = 0; iii < sheet.getLastRowNum() + 1; iii++) {
Row row = sheet.getRow(iii);
// if no row exists here; then nothing to do; next!
if (row == null) {
continue;
}
// if the row doesn't have this many columns then we are good; next!
int lastColumn = row.getLastCellNum();
if (lastColumn > maxColumn) {
maxColumn = lastColumn;
}
if (lastColumn < columnToDelete) {
continue;
}
for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
Cell oldCell = row.getCell(x - 1);
if (oldCell != null) {
row.removeCell(oldCell);
}
Cell nextCell = row.getCell(x);
if (nextCell != null) {
Cell newCell = row.createCell(x - 1, nextCell.getCellType());
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for (int ccc = 0; ccc < maxColumn; ccc++) {
sheet.setColumnWidth(ccc, sheet.getColumnWidth(ccc + 1));
}
}
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.setBold(true);/* w w w .ja va 2 s.co m*/
// create header cell styles
final CellStyle headerStyle = wb.createCellStyle();
// Set the other cell style and formatting
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
DataFormat df_header = wb.createDataFormat();
headerStyle.setDataFormat(df_header.getFormat("text"));
headerStyle.setFont(headerFont);
headerStyle.setAlignment(HorizontalAlignment.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(HorizontalAlignment.RIGHT);
final DecimalFormat format = (DecimalFormat) NumericFormats
.getFullCommodityFormat(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, CellType.STRING);
c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
c.setCellStyle(dateStyle);
// timestamp
c = r.createCell(++col, CellType.STRING);
c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
c.setCellStyle(timestampStyle);
// number
c = r.createCell(++col, CellType.STRING);
c.setCellValue(transaction.getNumber());
c.setCellStyle(textStyle);
// payee
c = r.createCell(++col, CellType.STRING);
c.setCellValue(transaction.getPayee());
c.setCellStyle(textStyle);
// memo
c = r.createCell(++col, CellType.STRING);
c.setCellValue(transaction.getMemo());
c.setCellStyle(textStyle);
// account
c = r.createCell(++col, CellType.STRING);
c.setCellValue(getAccountColumnValue(transaction, account));
c.setCellStyle(textStyle);
// clr, strip any zero width spaces
c = r.createCell(++col, CellType.STRING);
c.setCellValue(transaction.getReconciled(account).toString().replaceAll(ZERO_WIDTH_SPACE, ""));
c.setCellStyle(textStyle);
final BigDecimal amount = transaction.getAmount(account);
// increase
c = r.createCell(++col, CellType.NUMERIC);
if (amount.signum() >= 0) {
c.setCellValue(amount.doubleValue());
}
c.setCellStyle(amountStyle);
// decrease
c = r.createCell(++col, CellType.NUMERIC);
if (amount.signum() < 0) {
c.setCellValue(amount.abs().doubleValue());
}
c.setCellStyle(amountStyle);
// balance
c = r.createCell(++col, CellType.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);
}
}
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);// www . ja v a 2 s. c o m
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);
// 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 FileOutputStream out = new FileOutputStream(filename)) {
wb.write(out);
} catch (final Exception e) {
Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
} catch (IOException e) {
Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
}
public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {
Resource rb = Resource.get();
Workbook wb;//ww w . j a va 2s .c om
String extension = FileUtils.getFileExtension(file.getAbsolutePath());
if (extension.equals("xlsx")) {
wb = new XSSFWorkbook();
} else {
wb = new HSSFWorkbook();
}
CreationHelper createHelper = wb.getCreationHelper();
// create a new sheet
Sheet s = wb.createSheet(model.getBudget().getName());
// create header cell styles
CellStyle headerStyle = wb.createCellStyle();
// create 2 fonts objects
Font amountFont = wb.createFont();
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);
DataFormat df = wb.createDataFormat();
// 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);
headerStyle.setDataFormat(df.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.Change")));
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());
Collections.sort(accounts);
// create account rows
for (Account account : accounts) {
CellStyle amountStyle = wb.createCellStyle();
amountStyle.setFont(amountFont);
DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().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);
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 (AccountGroup group : model.getAccountGroupList()) {
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);
DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
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);
}
// Save
String filename = file.getAbsolutePath();
if (wb instanceof XSSFWorkbook) {
filename = FileUtils.stripFileExtension(filename) + ".xlsx";
} else {
filename = FileUtils.stripFileExtension(filename) + ".xls";
}
try (FileOutputStream out = new FileOutputStream(filename)) {
wb.write(out);
} catch (Exception e) {
Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
}
}
private static void _expandColumnWidth(Sheet sheet, String value, int columnNumber) {
int colwidth = sheet.getColumnWidth(columnNumber);
int len = (int) ((value.length() * 8) / 0.05D);
if (colwidth < len)
sheet.setColumnWidth(columnNumber, len + 1);
}
private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map,
AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException {
Workbook wb = new XSSFWorkbook();
Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity);
for (Service service : mapServiceListeArticleDemande.keySet()) {
Sheet sheet = wb
.createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt());
sheet.protectSheet(configService.getMotDePasseVerrouExcel());
construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet);
remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet);
// Taille automatique des colonnes selon le contenu
for (int i = 0; i < 20; i++) {
sheet.autoSizeColumn(i);//from www .j a v a 2 s .c om
sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768);
}
}
// Ecriture dans le fichier
File result = File.createTempFile(nomFichierSansExtension, ".xlsx");
FileOutputStream fileStream = new FileOutputStream(result);
wb.write(fileStream);
return result;
}
@Override
public void exportCatalogue(Catalogue catalogue) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Feuil1");
construitEnteteExportCatalogue(wb, sheet);
int i = 1;/*from w w w . ja v a 2 s . c om*/
for (Famille famille : catalogue.getListeFamille()) {
for (SousFamille sousFamille : famille.getListeSousFamille()) {
for (ArticleCatalogue articleCatalogue : sousFamille.getListeArticleCatalogue()) {
remplitLigneArticleExportCatalogue(wb, sheet, articleCatalogue, i++);
}
}
}
// Taille automatique des colonnes selon le contenu
for (int colonne = 0; colonne <= 12; colonne++) {
sheet.autoSizeColumn(colonne);
sheet.setColumnWidth(colonne, sheet.getColumnWidth(colonne) + 768);
}
// Ecriture dans le fichier
File result = File.createTempFile("Export Catalogue", ".xlsx");
FileOutputStream fileStream = new FileOutputStream(result);
wb.write(fileStream);
downloadService.downloadToUser(result,
"Export Catalogue " + AppockUtil.replaceAllSpecialCharacter(catalogue.getLibelle()) + ".xlsx");
result.delete();
}
/**
* Given a sheet, this method deletes a column from a sheet and moves
* all the columns to the right of it to the left one cell.
* //from w w w . j a va 2 s . c om
* Note, this method will not update any formula references.
*
* @param sheet
* @param column
*/
public static void deleteColumn(Sheet sheet, int columnToDelete) {
int maxColumn = 0;
for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
Row row = sheet.getRow(r);
// if no row exists here; then nothing to do; next!
if (row == null)
continue;
int lastColumn = row.getLastCellNum();
if (lastColumn > maxColumn)
maxColumn = lastColumn;
// if the row doesn't have this many columns then we are good; next!
if (lastColumn < columnToDelete)
continue;
for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
Cell oldCell = row.getCell(x - 1);
if (oldCell != null)
row.removeCell(oldCell);
Cell nextCell = row.getCell(x);
if (nextCell != null) {
Cell newCell = row.createCell(x - 1, nextCell.getCellType());
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for (int c = 0; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}