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:b01.officeLink.excel.ExcelRefiller.java License:Apache License From source file:br.com.tecsinapse.dataio.test.TableTest.java License:LGPL From source file:br.com.tecsinapse.dataio.test.TableTest.java License:LGPL From source file:cn.afterturn.easypoi.excel.html.css.impl.WidthCssConverImpl.java License:Apache License From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java License:Apache License From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java License:Open Source License From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java License:Open Source License From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java License:Apache License From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java License:Apache License From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.SummaryReportGenerator.java License:Apache License0, 1, 2, ... Usage
public void copyColumnWidths() {
Sheet srcSheet = getSourceSheet();
Sheet tarSheet = getTargetSheet();//from w w w . j a v a 2 s . com
for (int i = 1; i < 40; i++) {
int w = srcSheet.getColumnWidth(i);
tarSheet.setColumnWidth((short) (i - 1), w);
}
}
@Test
public void testAutoSizeColumn_ComprovaErroMetodoAutoSizeColumn_de_Sheet_QuandoUltimasLinhasSaoVazias() {
final int tamanhoIncorretoColuna = 236;
final int tamanhoCorretoColuna = 2048;
Table t = new Table();
//aps escrever certo nmero de linhas com o contedo vazio,
//a coluna no ajustada para o tamanho equivalente a linha
//com maior nmero de caracteres
for (int i = 1; i < 130; i++) {
t.addNewRow();//from w w w . ja va 2 s .c om
if (i < 20) {
t.add("Teste erro " + i);
} else {
t.add(" ");
}
}
Workbook wb = t.toWorkBook(new SXSSFWorkbook());
Sheet sheet = wb.getSheetAt(0);
// alterado o modo de validao devido a difernea de plataformas(Windows, Linux, Mac) esses valores podem mudar, porm devem respeitar o mnimo. Por isso usado assertTrue e no assertEquals
Assert.assertTrue(sheet.getColumnWidth(0) >= tamanhoIncorretoColuna);
Assert.assertTrue(sheet.getColumnWidth(1) >= tamanhoCorretoColuna);
}
@Test
public void testAutoSizeColumn_GeraTamanhoConformeMaiorQuantidadeCaracteresColuna() {
final int tamanhoUmCaracter = 256;
final int tamanhoDefaultColuna = 2048;
final int maiorNumeroCaracteresColuna = 13;
Table t = new Table() {
@Override/* www. ja v a 2s. c om*/
public boolean isAutoSizeColumnSheet() {
return false;
}
};
for (int i = 1; i < 130; i++) {
t.addNewRow();
if (i < 20) {
t.add("Teste erro " + i);
} else {
t.add(" ");
}
}
Workbook wb = t.toWorkBook(new SXSSFWorkbook());
Sheet sheet = wb.getSheetAt(0);
Assert.assertEquals(sheet.getColumnWidth(0), maiorNumeroCaracteresColuna * tamanhoUmCaracter);
Assert.assertEquals(sheet.getColumnWidth(1), tamanhoDefaultColuna);
}
@Override
public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) {
if (StringUtils.isNoneBlank(style.getWidth())) {
int width = (int) Math.round(PoiCssUtils.getInt(style.getWidth()) * 2048 / 8.43F);
Sheet sheet = cell.getSheet();
int colIndex = cell.getColumnIndex();
if (width > sheet.getColumnWidth(colIndex)) {
if (width > 255 * 256) {
width = 255 * 256;/*from ww w.java 2 s.c o m*/
}
sheet.setColumnWidth(colIndex, width);
}
}
}
/**
* 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.
* // ww w. j a v a2s .c o m
* Note, this method will not update any formula references.
*
* @param sheet
* @param columnToDelete
*/
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;
}
// 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 c = columnToDelete; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}
public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
assert wb != null;
assert reportFile != null;
//Precompute formula
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
for (Row r : sheet) {
for (Cell c : r) {
if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
try {
evaluator.evaluateFormulaCell(c);
} catch (Exception e) {
System.err.println(e);
}//from w w w . j a v a 2 s. c o m
}
}
}
}
File tmp = File.createTempFile("tmp_", ".xlsx");
try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
wb.write(out);
}
//Find page orientation
int maxColumnsGlobal = 0;
for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
Sheet sheet = wb.getSheetAt(sheetNo);
for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
Row row = rowIterator.next();
maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
}
}
Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);
PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
addHeader(writer, header);
pdfDocument.open();
//we have two columns in the Excel sheet, so we create a PDF table with two columns
//Note: There are ways to make this dynamic in nature, if you want to.
//Loop through sheets
for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
Sheet sheet = wb.getSheetAt(sheetNo);
//Loop through rows, to find number of columns
int minColumns = 1000;
int maxColumns = 0;
for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
Row row = rowIterator.next();
if (row.getFirstCellNum() >= 0)
minColumns = Math.min(minColumns, row.getFirstCellNum());
if (row.getLastCellNum() >= 0)
maxColumns = Math.max(maxColumns, row.getLastCellNum());
}
if (maxColumns == 0)
continue;
//Loop through first rows, to find relative width
float[] widths = new float[maxColumns];
int totalWidth = 0;
for (int c = 0; c < maxColumns; c++) {
int w = sheet.getColumnWidth(c);
widths[c] = w;
totalWidth += w;
}
for (int c = 0; c < maxColumns; c++) {
widths[c] /= totalWidth;
}
//Create new page and a new chapter with the sheet's name
if (sheetNo > 0)
pdfDocument.newPage();
Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);
PdfPTable pdfTable = null;
PdfPCell pdfCell = null;
boolean inTable = false;
//Loop through cells, to create the content
// boolean leftBorder = true;
// boolean[] topBorder = new boolean[maxColumns+1];
for (int r = 0; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
//Check if we exited a table (empty line)
if (row == null) {
if (pdfTable != null) {
addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
pdfTable = null;
}
inTable = false;
continue;
}
//Check if we start a table (>MIN_COL_IN_TABLE columns)
if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
inTable = true;
}
if (!inTable) {
//Process the data outside table, just add the text
boolean hasData = false;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
continue;
Chunk chunk = getChunk(wb, cell);
pdfSheet.add(chunk);
pdfSheet.add(new Chunk(" "));
hasData = true;
}
if (hasData)
pdfSheet.add(Chunk.NEWLINE);
} else {
//Process the data in table
if (pdfTable == null) {
//Create table
pdfTable = new PdfPTable(maxColumns);
pdfTable.setWidths(widths);
// topBorder = new boolean[maxColumns+1];
}
int cellNumber = minColumns;
// leftBorder = false;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
pdfCell = new PdfPCell();
pdfCell.setBorder(0);
pdfTable.addCell(pdfCell);
}
Chunk phrase = getChunk(wb, cell);
pdfCell = new PdfPCell(new Phrase(phrase));
pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
pdfCell.setPaddingLeft(1);
pdfCell.setHorizontalAlignment(
cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
: cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
? PdfPCell.ALIGN_RIGHT
: PdfPCell.ALIGN_LEFT);
pdfCell.setUseBorderPadding(false);
pdfCell.setUseVariableBorders(false);
pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
: cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
: cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
: cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
: ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
if (color != null)
pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
pdfTable.addCell(pdfCell);
cellNumber++;
}
for (; cellNumber < maxColumns; cellNumber++) {
pdfCell = new PdfPCell();
pdfCell.setBorder(0);
pdfTable.addCell(pdfCell);
}
}
//Custom code to add all images on the first sheet (works for reporting)
if (sheetNo == 0 && row.getRowNum() == 0) {
for (PictureData pd : wb.getAllPictures()) {
try {
Image pdfImg = Image.getInstance(pd.getData());
pdfImg.scaleToFit(
pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
- pageSize.getBorderWidthRight(),
pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
- pageSize.getBorderWidthBottom());
pdfSheet.add(pdfImg);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
if (pdfTable != null) {
addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
}
pdfDocument.add(pdfSheet);
}
pdfDocument.close();
}
@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
Class[] types = getTypes(table);
Workbook wb = new XSSFWorkbook();
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;/*from ww w . j a v a 2 s .co m*/
DataFormat df = wb.createDataFormat();
Font font = wb.createFont();
font.setFontName("Serif");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 15);
style = wb.createCellStyle();
style.setFont(font);
styles.put("title", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 10);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(font);
style.setWrapText(true);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("th", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setFont(font);
style.setWrapText(true);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setFont(font);
style.setWrapText(true);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td-border", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setFont(font);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td-double", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setFont(font);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td-right", style);
font = wb.createFont();
font.setFontName("Serif");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setFont(font);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td-bold", style);
font = wb.createFont();
font.setFontName("Serif");
font.setFontHeightInPoints((short) 9);
style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setFont(font);
style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
styles.put("td-date", style);
Sheet sheet = wb.createSheet();
sheet.setFitToPage(true);
Cell cell;
int maxRows = 0;
for (int r = 0; r < table.length; r++) {
Row row = sheet.createRow(r);
if (r == 0) {
row.setRowStyle(styles.get("th"));
}
int rows = 1;
for (int c = 0; c < table[r].length; c++) {
cell = row.createCell(c);
String s = table[r][c];
if (s == null)
continue;
rows = Math.max(rows, s.split("\n").length);
try {
if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
cell.setCellStyle(styles.get("th"));
cell.setCellValue(s);
} else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
if (r == 0 && c == 0) {
cell.setCellStyle(styles.get("td"));
} else {
cell.setCellStyle(styles.get("th"));
}
cell.setCellValue(s);
} else if (types[c] == Double.class) {
cell.setCellStyle(styles.get("td-double"));
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(s));
} else if (types[c] == String.class) {
cell.setCellStyle(
styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(s);
} else {
cell.setCellStyle(styles.get("td-right"));
cell.setCellValue(s);
}
} catch (Exception e) {
cell.setCellStyle(styles.get("td"));
cell.setCellValue(s);
}
}
maxRows = Math.max(maxRows, rows);
row.setHeightInPoints(rows * 16f);
}
// Add footer notes
if (footerData.size() > 0) {
Row row = sheet.createRow(table.length);
row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
row.getRowNum(), //last row (0-based)
0, //first column (0-based)
table[0].length - 1 //last column (0-based)
));
//for ( String data : footerData ) {
style = wb.createCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);
cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
//}
}
footerData.clear();
autoSizeColumns(sheet);
if (table.length > 0) {
for (int c = 0; c < table[0].length; c++) {
if (sheet.getColumnWidth(c) > 10000)
sheet.setColumnWidth(c, 3000);
}
}
if (exportMode == ExportMode.HEADERS_TOPLEFT) {
for (int r = 1; r < table.length; r++) {
sheet.getRow(r).setHeightInPoints(maxRows * 16f);
}
}
File reportFile = IOUtils.createTempFile("export_", ".xlsx");
FileOutputStream out = new FileOutputStream(reportFile);
wb.write(out);
wb.close();
out.close();
Desktop.getDesktop().open(reportFile);
}
void autosize(Sheet sheet, int lastColumnIndex) {
for (int i = 0; i <= lastColumnIndex; i++) {
try {/*from www. ja v a 2s. c o m*/
sheet.autoSizeColumn(i);
} catch (Exception e) {
// autosize depends on AWT stuff and can fail, but it should not be fatal
LOG.warn("autoSizeColumn(" + i + ") failed: " + e.getMessage());
}
int cw = sheet.getColumnWidth(i);
// increase width to accommodate drop-down arrow in the header
if (cw / 256 < 20) {
sheet.setColumnWidth(i, 256 * 12);
} else if (cw / 256 > 120) {
sheet.setColumnWidth(i, 256 * 120);
}
}
}
private void adjustColumnWidth(Sheet sheet, int lastColumn) {
assert sheet != null;
for (int i = 0; i <= lastColumn; i++) {
sheet.autoSizeColumn(i);/*from w w w . ja v a2s . com*/
int width = sheet.getColumnWidth(i);
if (width < MINIMUM_COLUMN_WIDTH) {
sheet.setColumnWidth(i, MINIMUM_COLUMN_WIDTH);
}
}
}
/**
* Creates Leveraging Sheet/* ww w . j a v a 2s . c o m*/
*/
private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
Map<String, ReportWordCount> p_wordCounts) throws Exception {
int rowLen = p_sheet.getPhysicalNumberOfRows();
int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells();
int wordTotalCol = colLen - 2;
int row = ROWNUMBER, column = colLen - 1;
int costCol;
Map<String, Double> p_ratesMap = null;
for (int r = 2; r < rowLen + ROWNUMBER; r++) {
Row theRow = getRow(p_sheet, r);
theRow.removeCell(getCell(theRow, column));
}
p_sheet.removeColumnBreak(column);
// Rates Columns
for (int dis = column - 1; column < colLen + dis - 2; column++) {
Cell cell_From = p_sheet.getRow(row).getCell(column - dis);
Cell cell_To = getCell(p_sheet.getRow(row), column);
cell_To.setCellValue(cell_From.getStringCellValue());
cell_To.setCellStyle(cell_From.getCellStyle());
p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis));
// Adds Rates for Match Type
for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) {
String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue();
String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue();
double rate = getRate(matchType, targetLocale, p_ratesMap);
addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook));
}
}
// Cost Columns Head
costCol = column;
p_sheet.setColumnWidth(column, 20 * 256);
Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++);
cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging"));
cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));
p_sheet.setColumnWidth(column, 20 * 256);
Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++);
cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging"));
cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));
p_sheet.setColumnWidth(column, 15 * 256);
Cell cell_Savings = getCell(getRow(p_sheet, row), column++);
cell_Savings.setCellValue(bundle.getString("lb_savings"));
cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook));
Cell cell_Percent = getCell(getRow(p_sheet, row), column++);
cell_Percent.setCellValue("%");
cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook));
// Cost Columns Data
for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) {
String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1));
String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*"
+ getColumnName(wordTotalCol + 5) + (row + 1);
String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1);
String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1);
Row theRow = getRow(p_sheet, row);
Cell cell_Leveraging = getCell(theRow, costCol);
cell_Leveraging.setCellFormula(leveragingForm);
cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook));
Cell cell_NoLeveraging = getCell(theRow, costCol + 1);
cell_NoLeveraging.setCellFormula(noLeveragingForm);
cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook));
Cell cell_Saving = getCell(theRow, costCol + 2);
cell_Saving.setCellFormula(savingForm);
cell_Saving.setCellStyle(getMoneyStyle(p_workbook));
Cell cell_PercentData = getCell(theRow, costCol + 3);
cell_PercentData.setCellFormula(percent);
cell_PercentData.setCellStyle(getPercentStyle(p_workbook));
}
if (rowLen > 1) {
row = rowLen + 1;
column = 1;
for (; column < colLen - 1; column++) {
Cell cell_Total = getCell(getRow(p_sheet, row), column);
cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));
}
for (; column < costCol; column++) {
Cell cell = getCell(getRow(p_sheet, row), column);
cell.setCellValue("");
cell.setCellStyle(getHeaderOrangeStyle(p_workbook));
}
// Summary Cost Columns
Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column);
cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook));
Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column);
cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook));
Cell cell_SumSaving = getCell(getRow(p_sheet, row), column);
cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook));
String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1);
Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column);
cell_AvgPercent.setCellFormula(percent);
cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook));
}
}