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:com.nc.common.utils.ExcelUtil.java License:Open Source License From source file:com.qihang.winter.poi.util.PoiSheetUtility.java License:Apache License From source file:com.upbest.utils.AddDimensionedImage.java License:Apache License From source file:com.upbest.utils.AddDimensionedImage.java License:Apache License From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java License:Open Source License From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java License:Open Source License From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer.java License:Open Source License From source file:demo.poi.AddDimensionedImage.java License:Apache License From source file:eu.esdihumboldt.hale.io.xls.writer.XLSAlignmentMappingWriter.java License:Open Source License From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java License:Apache License0, 1, 2, ... Usage
/**
* <pre>// w ww.ja v a 2 s. c om
* 1. : POI UTIL
* 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>>
* </pre>
*
* @method Name : createWorkBook
* @param workbook, sheetName, list, colNames
* @return Workbook
* @throws Exception
*
*/
@SuppressWarnings("deprecation")
public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list,
List<Map<String, Object>> colNames) throws Exception {
Row row;
Cell cell;
/* ? ? START */
/* Workbook workbook = new Workbook(); */
CellStyle titleStyle = workbook.createCellStyle();
CellStyle cellStyle = workbook.createCellStyle();
CellStyle contentStyle = workbook.createCellStyle();
CellStyle contentStyle_2 = workbook.createCellStyle();
/* ? */
/* ? ? */
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 13);
titleFont.setFontName("? ");
/* ? */
Font colNameFont = workbook.createFont();
colNameFont.setFontHeightInPoints((short) 10);
colNameFont.setFontName("? ");
/* ? */
Font contentFont = workbook.createFont();
/* ? ? ? */
titleStyle.setFont(titleFont);
/* ? / ? ? */
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//?
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setFont(colNameFont);
/* ? / ? */
contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */
contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setFont(contentFont);
/* ? / ? */
contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */
contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
contentStyle_2.setFont(contentFont);
/* ? */
Sheet sheet = workbook.createSheet(sheetName);
/* ?? */
int sheet1_row = 0;
List<Map<String, Object>> colList = colNames;
/* ? START */
row = sheet.createRow(sheet1_row);
for (int i = 0; i < colList.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(String.valueOf(colList.get(i).get("item" + i)));
cell.setCellStyle(cellStyle);
}
sheet1_row++;
/* ? END */
for (Map<String, Object> map : list) {
row = sheet.createRow(sheet1_row);
for (int j = 0; j < colList.size(); j++) {
cell = row.createCell(j);
cell.setCellStyle(contentStyle);
if (null != map.get(colList.get(j).get("item" + j))) {
cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j))));
} else {
cell.setCellValue("");
}
/* ? ?? */
sheet.autoSizeColumn((short) j);
sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312);
}
sheet1_row++;
}
return workbook;
}
/**
* 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 v a 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;
// 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 = 0; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}
/**
* Determines whether the sheets columns should be re-sized to accomodate
* the image, adjusts the columns width if necessary and creates then
* returns a ClientAnchorDetail object that facilitates construction of
* an ClientAnchor that will fix the image on the sheet and establish
* it's size./* ww w. java 2s . c om*/
*
* @param sheet A reference to the sheet that will 'contain' the image.
* @param colNumber A primtive int that contains the index number of a
* column on the sheet.
* @param reqImageWidthMM A primitive double that contains the required
* width of the image in millimetres
* @param resizeBehaviour A primitive int whose value will indicate how the
* width of the column should be adjusted if the
* required width of the image is greater than the
* width of the column.
* @return An instance of the ClientAnchorDetail class that will contain
* the index number of the column containing the cell whose top
* left hand corner also defines the top left hand corner of the
* image, the index number column containing the cell whose top
* left hand corner also defines the bottom right hand corner of
* the image and an inset that determines how far the right hand
* edge of the image can protrude into the next column - expressed
* as a specific number of coordinate positions.
*/
private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber, double reqImageWidthMM,
int resizeBehaviour) {
double colWidthMM = 0.0D;
double colCoordinatesPerMM = 0.0D;
int pictureWidthCoordinates = 0;
ClientAnchorDetail colClientAnchorDetail = null;
// Get the colum's width in millimetres
colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber));
// Check that the column's width will accomodate the image at the
// required dimension. If the width of the column is LESS than the
// required width of the image, decide how the application should
// respond - resize the column or overlay the image across one or more
// columns.
if (colWidthMM < reqImageWidthMM) {
// Should the column's width simply be expanded?
if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)
|| (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) {
// Set the width of the column by converting the required image
// width from millimetres into Excel's column width units.
sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM));
// To make the image occupy the full width of the column, convert
// the required width of the image into co-ordinates. This value
// will become the inset for the ClientAnchorDetail class that
// is then instantiated.
if (sheet instanceof HSSFSheet) {
colWidthMM = reqImageWidthMM;
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
} else {
pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM;
}
colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
}
// If the user has chosen to overlay both rows and columns or just
// to expand ONLY the size of the rows, then calculate how to lay
// the image out across one or more columns.
else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)
|| (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) {
colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM);
}
}
// If the column is wider than the image.
else {
if (sheet instanceof HSSFSheet) {
// Mow many co-ordinate positions are there per millimetre?
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
// Given the width of the image, what should be it's co-ordinate?
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
} else {
pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM;
}
colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
}
return (colClientAnchorDetail);
}
/**
* If the image is to overlie more than one column, calculations need to be
* performed to determine how many columns and whether the image will
* overlie just a part of one column in order to be presented at the
* required size./*from w w w . ja va2 s. co m*/
*
* @param sheet The sheet that will 'contain' the image.
* @param startingColumn A primitive int whose value is the index of the
* column that contains the cell whose top left hand
* corner should be aligned with the top left hand
* corner of the image.
* @param reqImageWidthMM A primitive double whose value will indicate the
* required width of the image in millimetres.
* @return An instance of the ClientAnchorDetail class that will contain
* the index number of the column containing the cell whose top
* left hand corner also defines the top left hand corner of the
* image, the index number column containing the cell whose top
* left hand corner also defines the bottom right hand corner of
* the image and an inset that determines how far the right hand
* edge of the image can protrude into the next column - expressed
* as a specific number of coordinate positions.
*/
private ClientAnchorDetail calculateColumnLocation(Sheet sheet, int startingColumn, double reqImageWidthMM) {
ClientAnchorDetail anchorDetail = null;
double totalWidthMM = 0.0D;
double colWidthMM = 0.0D;
double overlapMM = 0.0D;
double coordinatePositionsPerMM = 0.0D;
int toColumn = startingColumn;
int inset = 0;
// Calculate how many columns the image will have to
// span in order to be presented at the required size.
while (totalWidthMM < reqImageWidthMM) {
colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) (sheet.getColumnWidth(toColumn)));
// Note use of the cell border width constant. Testing with an image
// declared to fit exactly into one column demonstrated that it's
// width was greater than the width of the column the POI returned.
// Further, this difference was a constant value that I am assuming
// related to the cell's borders. Either way, that difference needs
// to be allowed for in this calculation.
totalWidthMM += (colWidthMM + ConvertImageUnits.CELL_BORDER_WIDTH_MILLIMETRES);
toColumn++;
}
// De-crement by one the last column value.
toColumn--;
// Highly unlikely that this will be true but, if the width of a series
// of columns is exactly equal to the required width of the image, then
// simply build a ClientAnchorDetail object with an inset equal to the
// total number of co-ordinate positions available in a column, a
// from column co-ordinate (top left hand corner) equal to the value
// of the startingColumn parameter and a to column co-ordinate equal
// to the toColumn variable.
//
// Convert both values to ints to perform the test.
if ((int) totalWidthMM == (int) reqImageWidthMM) {
// A problem could occur if the image is sized to fit into one or
// more columns. If that occurs, the value in the toColumn variable
// will be in error. To overcome this, there are two options, to
// ibcrement the toColumn variable's value by one or to pass the
// total number of co-ordinate positions to the third paramater
// of the ClientAnchorDetail constructor. For no sepcific reason,
// the latter option is used below.
if (sheet instanceof HSSFSheet) {
anchorDetail = new ClientAnchorDetail(startingColumn, toColumn,
ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS);
} else {
anchorDetail = new ClientAnchorDetail(startingColumn, toColumn,
(int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM);
}
}
// In this case, the image will overlap part of another column and it is
// necessary to calculate just how much - this will become the inset
// for the ClientAnchorDetail object.
else {
// Firstly, claculate how much of the image should overlap into
// the next column.
overlapMM = reqImageWidthMM - (totalWidthMM - colWidthMM);
// When the required size is very close indded to the column size,
// the calcaulation above can produce a negative value. To prevent
// problems occuring in later caculations, this is simply removed
// be setting the overlapMM value to zero.
if (overlapMM < 0) {
overlapMM = 0.0D;
}
if (sheet instanceof HSSFSheet) {
// Next, from the columns width, calculate how many co-ordinate
// positons there are per millimetre
coordinatePositionsPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
// From this figure, determine how many co-ordinat positions to
// inset the left hand or bottom edge of the image.
inset = (int) (coordinatePositionsPerMM * overlapMM);
} else {
inset = (int) overlapMM * AddDimensionedImage.EMU_PER_MM;
}
// Now create the ClientAnchorDetail object, setting the from and to
// columns and the inset.
anchorDetail = new ClientAnchorDetail(startingColumn, toColumn, inset);
}
return (anchorDetail);
}
/**
* Sets the column to automatically adjust the column width to fit the
* largest cell content within the column. This is a POI feature, and is
* meant to be called after all the data for the target column has been
* written. See {@link Sheet#autoSizeColumn(int)}.
* <p>//from ww w .jav a 2 s.co m
* This does not take into account cells that have custom Vaadin components
* inside them.
*
* @param columnIndex
* Index of the target column, 0-based
*/
public void autofitColumn(int columnIndex) {
final Sheet activeSheet = getActiveSheet();
activeSheet.autoSizeColumn(columnIndex);
getState().colW[columnIndex] = AbstractExcelUtils
.getColumnWidthInPx(activeSheet.getColumnWidth(columnIndex));
getCellValueManager().clearCacheForColumn(columnIndex + 1);
getCellValueManager().loadCellData(firstRow, columnIndex + 1, lastRow, columnIndex + 1);
if (hasSheetOverlays()) {
reloadImageSizesFromPOI = true;
loadOrUpdateOverlays();
}
}
/**
* Calculate size-related values for the sheet. Includes row and column
* counts, actual row heights and column widths, and hidden row and column
* indexes./*from w w w.jav a 2s. co m*/
*
* @param spreadsheet
* @param sheet
*/
static void calculateSheetSizes(final Spreadsheet spreadsheet, final Sheet sheet) {
// Always have at least the default amount of rows
int rows = sheet.getLastRowNum() + 1;
if (rows < spreadsheet.getDefaultRowCount()) {
rows = spreadsheet.getDefaultRowCount();
}
spreadsheet.getState().rows = rows;
final float[] rowHeights = new float[rows];
int cols = 0;
int tempRowIndex = -1;
final ArrayList<Integer> hiddenRowIndexes = new ArrayList<Integer>();
for (Row row : sheet) {
int rIndex = row.getRowNum();
// set the empty rows to have the default row width
while (++tempRowIndex != rIndex) {
rowHeights[tempRowIndex] = spreadsheet.getState().defRowH;
}
if (row.getZeroHeight()) {
rowHeights[rIndex] = 0.0F;
hiddenRowIndexes.add(rIndex + 1);
} else {
rowHeights[rIndex] = row.getHeightInPoints();
}
int c = row.getLastCellNum();
if (c > cols) {
cols = c;
}
}
if (rows > sheet.getLastRowNum() + 1) {
float defaultRowHeightInPoints = sheet.getDefaultRowHeightInPoints();
int lastRowNum = sheet.getLastRowNum();
// if sheet is empty, also set height for 'last row' (index
// zero)
if (lastRowNum == 0) {
rowHeights[0] = defaultRowHeightInPoints;
}
// set default height for the rest
for (int i = lastRowNum + 1; i < rows; i++) {
rowHeights[i] = defaultRowHeightInPoints;
}
}
spreadsheet.getState().hiddenRowIndexes = hiddenRowIndexes;
spreadsheet.getState().rowH = rowHeights;
// Always have at least the default amount of columns
if (cols < spreadsheet.getDefaultColumnCount()) {
cols = spreadsheet.getDefaultColumnCount();
}
spreadsheet.getState().cols = cols;
final int[] colWidths = new int[cols];
final ArrayList<Integer> hiddenColumnIndexes = new ArrayList<Integer>();
for (int i = 0; i < cols; i++) {
if (sheet.isColumnHidden(i)) {
colWidths[i] = 0;
hiddenColumnIndexes.add(i + 1);
} else {
colWidths[i] = ExcelToHtmlUtils.getColumnWidthInPx(sheet.getColumnWidth(i));
}
}
spreadsheet.getState().hiddenColumnIndexes = hiddenColumnIndexes;
spreadsheet.getState().colW = colWidths;
}
/**{@inheritDoc}**/
@Override//from w w w . j av a 2 s . c om
public void transform(List<?> sourceList, OutputStream out, TypeOfBuildingBlock typeOfBuildingBlock) {
ColumnStructure[] columns = tableStructure.getColumns();
try {
Workbook workbook = createWorkbook();
Sheet sheet = workbook.createSheet();
configSheetName(sheet, typeOfBuildingBlock);
Map<IteraExcelStyle, CellStyle> createdStyles = ExcelStylesCreator.createStyles(workbook);
CellStyle headerStyle = createdStyles.get(IteraExcelStyle.HEADER);
CellStyle dataStyle = createdStyles.get(IteraExcelStyle.DATA);
CellStyle dataDateStyle = createdStyles.get(IteraExcelStyle.DATA_DATE);
// Create cell style for numbers
CellStyle numCellStyle = workbook.createCellStyle();
numCellStyle.cloneStyleFrom(dataStyle);
short numFormatIndex = workbook.createDataFormat().getFormat("0.00");
numCellStyle.setDataFormat(numFormatIndex);
Row headerRow = sheet.createRow(0);
int nextCol = 0;
for (ColumnStructure columnStructure : columns) {
Cell headerCell = headerRow.createCell(nextCol);
headerCell.setCellValue(columnStructure.getColumnHeader());
headerCell.setCellStyle(headerStyle);
nextCol++;
}
int nextRow = 1;
for (Object obj : sourceList) {
if (obj instanceof BuildingBlock) {
BuildingBlock bb = (BuildingBlock) obj;
// skip virutal root element
if (bb instanceof AbstractHierarchicalEntity<?>) {
AbstractHierarchicalEntity<?> hierarchicalEntity = (AbstractHierarchicalEntity<?>) bb;
if (hierarchicalEntity.isTopLevelElement()) {
continue;
}
}
Row row = sheet.createRow(nextRow);
nextCol = 0;
for (ColumnStructure columnStructure : columns) {
Cell cell = row.createCell(nextCol);
Object resolvedValue = columnStructure.resolveValue(bb);
if (resolvedValue instanceof Date) {
cell.setCellStyle(dataDateStyle);
cell.setCellValue((Date) resolvedValue);
} else if (resolvedValue instanceof Number) {
cell.setCellStyle(numCellStyle);
double doubleValue = ((Number) resolvedValue).doubleValue();
cell.setCellValue(doubleValue);
} else {
cell.setCellStyle(dataStyle);
cell.setCellValue(String.valueOf(resolvedValue));
}
++nextCol;
}
++nextRow;
}
}
// auto format
nextCol = 0;
for (int col = 0; col < columns.length; col++) {
sheet.autoSizeColumn(col);
int columnCharWidth = sheet.getColumnWidth(col) / 256;
if (columnCharWidth > MAX_COLUM_CHAR_WIDTH) {
sheet.setColumnWidth(col, MAX_COLUM_CHAR_WIDTH * 256);
}
}
workbook.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Determines whether the sheets columns should be re-sized to accomodate
* the image, adjusts the columns width if necessary and creates then
* returns a ClientAnchorDetail object that facilitates construction of an
* ClientAnchor that will fix the image on the sheet and establish it's
* size.//from w ww .j a v a 2 s . co m
*
* @param sheet
* A reference to the sheet that will 'contain' the image.
* @param colNumber
* A primtive int that contains the index number of a column on
* the sheet.
* @param reqImageWidthMM
* A primitive double that contains the required width of the
* image in millimetres
* @param resizeBehaviour
* A primitive int whose value will indicate how the width of the
* column should be adjusted if the required width of the image
* is greater than the width of the column.
* @return An instance of the ClientAnchorDetail class that will contain the
* index number of the column containing the cell whose top left
* hand corner also defines the top left hand corner of the image,
* the index number column containing the cell whose top left hand
* corner also defines the bottom right hand corner of the image and
* an inset that determines how far the right hand edge of the image
* can protrude into the next column - expressed as a specific
* number of coordinate positions.
*/
private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber, double reqImageWidthMM,
int resizeBehaviour) {
double colWidthMM = 0.0D;
double colCoordinatesPerMM = 0.0D;
int pictureWidthCoordinates = 0;
ClientAnchorDetail colClientAnchorDetail = null;
// Get the colum's width in millimetres
colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber));
// Check that the column's width will accomodate the image at the
// required dimension. If the width of the column is LESS than the
// required width of the image, decide how the application should
// respond - resize the column or overlay the image across one or more
// columns.
if (colWidthMM < reqImageWidthMM) {
// Should the column's width simply be expanded?
if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)
|| (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) {
// Set the width of the column by converting the required image
// width from millimetres into Excel's column width units.
sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM));
// To make the image occupy the full width of the column,
// convert
// the required width of the image into co-ordinates. This value
// will become the inset for the ClientAnchorDetail class that
// is then instantiated.
if (sheet instanceof HSSFSheet) {
colWidthMM = reqImageWidthMM;
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
} else {
pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM;
}
colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
}
// If the user has chosen to overlay both rows and columns or just
// to expand ONLY the size of the rows, then calculate how to lay
// the image out across one or more columns.
else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)
|| (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) {
colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM);
}
}
// If the column is wider than the image.
else {
if (sheet instanceof HSSFSheet) {
// Mow many co-ordinate positions are there per millimetre?
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
// Given the width of the image, what should be it's
// co-ordinate?
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
} else {
pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM;
}
colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
}
return (colClientAnchorDetail);
}
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter)
throws IOProviderConfigurationException, IOException {
super.execute(progress, reporter);
Workbook workbook;/*from ww w .j a v a2 s. c om*/
// write xls file
if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
workbook = new HSSFWorkbook();
}
// write xlsx file
else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
workbook = new XSSFWorkbook();
} else {
reporter.error(new IOMessageImpl("Content type is invalid!", null));
reporter.setSuccess(false);
return reporter;
}
Sheet sheet = workbook.createSheet();
workbook.setSheetName(0, "Mapping table");
Row row = null;
Cell cell = null;
// create cell style of the header
CellStyle headerStyle = XLSCellStyles.getHeaderStyle(workbook);
// create cell style
CellStyle cellStyle = XLSCellStyles.getNormalStyle(workbook, false);
// create highlight style for type cells
CellStyle highlightStyle = XLSCellStyles.getHighlightedStyle(workbook, false);
// create disabled style
CellStyle disabledStyle = XLSCellStyles.getNormalStyle(workbook, true);
// create disabled highlight style
CellStyle disabledTypeStyle = XLSCellStyles.getHighlightedStyle(workbook, true);
List<Map<CellType, CellInformation>> mapping = getMappingList();
// determine if cells are organized by type cell
boolean byTypeCell = isByTypeCell();
int rownum = 0;
// write header
row = sheet.createRow(rownum++);
for (int i = 0; i < getMappingHeader().size(); i++) {
cell = row.createCell(i);
cell.setCellValue(getMappingHeader().get(i));
cell.setCellStyle(headerStyle);
}
// write all mappings
for (Map<CellType, CellInformation> entry : mapping) {
boolean disabled = false;
if (getParameter(TRANSFORMATION_AND_DISABLED_FOR).as(Boolean.class)) {
List<String> transformationDisabled = entry.get(CellType.TRANSFORMATION_AND_DISABLED).getText();
disabled = !transformationDisabled.isEmpty()
&& !transformationDisabled.contains(TransformationMode.active.displayName());
}
// create a row
row = sheet.createRow(rownum);
CellStyle rowStyle = cellStyle;
String targetProp = getCellValue(entry, CellType.TARGET_PROPERTIES);
boolean isTypeCell = targetProp == null || targetProp.isEmpty();
if (isTypeCell && byTypeCell) {
// organized by type cells and this is a type cell
if (disabled) {
// disabled type cell
rowStyle = disabledTypeStyle;
} else {
// normal type cell
rowStyle = highlightStyle;
}
} else if (disabled) {
// disabled property cell
rowStyle = disabledStyle;
}
List<CellType> celltypes = getCellTypes();
for (int i = 0; i < celltypes.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(getCellValue(entry, celltypes.get(i)));
cell.setCellStyle(rowStyle);
}
rownum++;
}
// could be integrated in configuration page
// int maxColWidth = calculateWidth(getParameter(MAX_COLUMN_WIDTH).as(Integer.class));
int maxColWidth = calculateWidth(maxWidth);
// autosize all columns
for (int i = 0; i < getMappingHeader().size(); i++) {
sheet.autoSizeColumn(i);
if (sheet.getColumnWidth(i) > maxColWidth)
sheet.setColumnWidth(i, maxColWidth);
}
// write file
FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
workbook.write(out);
out.close();
reporter.setSuccess(true);
return reporter;
}
/**
* Finalise la cration de la feuille de calcul, notamment en demandant le
* redimensionnement automatique des colonnes.
* // ww w .j av a 2s. co m
* @param sheet feuilles de calcul
* @param headers en-ttes
* @param landscapePrintSetup dfinit si la feuille est imprime en paysage ou non
*/
protected void finalizeSheet(Sheet sheet, List<String> headers, boolean landscapePrintSetup) {
int nbColumns = headers.size();
for (int i = 0; i < nbColumns; i++) {
sheet.autoSizeColumn(i);
int columnWidth = (int) (sheet.getColumnWidth(i) * COLUMN_RESIZE_RATIO);
sheet.setColumnWidth(i,
columnWidth < ABSOLUTE_MAX_COLUMN_WIDTH ? columnWidth : ABSOLUTE_MAX_COLUMN_WIDTH);
}
finalizeSheet(sheet, landscapePrintSetup);
}