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:org.bbreak.excella.core.util.PoiUtil.java License:Open Source License From source file:org.bbreak.excella.reports.ReportsTestUtil.java License:Open Source License From source file:org.bbreak.excella.reports.tag.ColRepeatParamParser.java License:Open Source License From source file:org.bbreak.excella.reports.util.ReportsUtil.java License:Open Source License From source file:org.bonitasoft.connectors.excel.AddDimensionedImage.java License:Apache License From source file:org.bonitasoft.connectors.excel.AddDimensionedImage.java License:Apache License From source file:org.efaps.esjp.common.file.FileUtil_Base.java License:Apache License From source file:org.h819.commons.file.excel.poi.examples.AddDimensionedImage.java License:Apache License From source file:org.joeffice.spreadsheet.SheetComponent.java License:Apache License From source file:org.netxilia.impexp.impl.ExcelImportService.java License:Open Source License0, 1, 2, ... Usage
/**
* ?/*from w w w . jav a 2 s. c o m*/
*
* @param fromSheet
* @param rangeAddress
* @param toSheet
* @param toRowNum
* @param toColumnNum
* @param clearFromRange
*/
public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum,
int toColumnNum, boolean clearFromRange) {
if (fromSheet == null || rangeAddress == null || toSheet == null) {
return;
}
int fromRowIndex = rangeAddress.getFirstRow();
int fromColumnIndex = rangeAddress.getFirstColumn();
int rowNumOffset = toRowNum - fromRowIndex;
int columnNumOffset = toColumnNum - fromColumnIndex;
//
CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset,
rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset,
rangeAddress.getLastColumn() + columnNumOffset);
Workbook fromWorkbook = fromSheet.getWorkbook();
Sheet baseSheet = fromSheet;
Sheet tmpSheet = null;
// ?????
if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) {
// ?
tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME);
if (tmpSheet == null) {
tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME);
}
baseSheet = tmpSheet;
int lastColNum = getLastColNum(fromSheet);
for (int i = 0; i <= lastColNum; i++) {
tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(),
false);
// ?
if (clearFromRange) {
clearRange(fromSheet, rangeAddress);
}
}
// ????
Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress);
// ???
clearRange(toSheet, toAddress);
// ???
for (CellRangeAddress mergeAddress : targetCellSet) {
toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset,
mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset,
mergeAddress.getLastColumn() + columnNumOffset));
}
for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) {
//
Row fromRow = baseSheet.getRow(i);
if (fromRow == null) {
continue;
}
Row row = toSheet.getRow(i + rowNumOffset);
if (row == null) {
row = toSheet.createRow(i + rowNumOffset);
row.setHeight((short) 0);
}
// ??????
int fromRowHeight = fromRow.getHeight();
int toRowHeight = row.getHeight();
if (toRowHeight < fromRowHeight) {
row.setHeight(fromRow.getHeight());
}
ColumnHelper columnHelper = null;
if (toSheet instanceof XSSFSheet) {
XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook()
.getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet));
CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
columnHelper = new ColumnHelper(ctWorksheet);
}
for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) {
Cell fromCell = fromRow.getCell(j);
if (fromCell == null) {
continue;
}
int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns();
if (toSheet instanceof XSSFSheet) {
maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns();
}
if (j + columnNumOffset >= maxColumn) {
break;
}
Cell cell = row.getCell(j + columnNumOffset);
if (cell == null) {
cell = row.createCell(j + columnNumOffset);
if (toSheet instanceof XSSFSheet) {
// XSSF??????????
CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false);
if (col == null || !col.isSetWidth()) {
toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j));
}
}
}
// ?
copyCell(fromCell, cell);
// ??????
int fromColumnWidth = baseSheet.getColumnWidth(j);
int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset);
if (toColumnWidth < fromColumnWidth) {
toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j));
}
}
}
if (tmpSheet != null) {
//
fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet));
} else if (clearFromRange) {
// ????
clearRange(fromSheet, rangeAddress);
}
}
/**
* /*from w w w . j av a 2 s. com*/
*
* @param expected
* @param actual
* @param isActCopyOfExp ??????true
* @throws ReportsCheckException
*/
public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp)
throws ReportsCheckException {
List<CheckMessage> errors = new ArrayList<CheckMessage>();
Workbook expectedWorkbook = expected.getWorkbook();
Workbook actualWorkbook = actual.getWorkbook();
if (log.isDebugEnabled()) {
log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
+ "] check start!");
}
// ----------------------
// ????
// ----------------------
// ??
String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected));
String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual));
if (!isActCopyOfExp) {
if (!eSheetName.equals(aSheetName)) {
errors.add(new CheckMessage("??", eSheetName, aSheetName));
}
}
// ?
String ePrintSetupString = getPrintSetupString(expected.getPrintSetup());
String aPrintSetupString = getPrintSetupString(actual.getPrintSetup());
if (!ePrintSetupString.equals(aPrintSetupString)) {
errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString));
}
// ?
String eHeaderString = getHeaderString(expected.getHeader());
String aHeaderString = getHeaderString(actual.getHeader());
if (!eHeaderString.equals(aHeaderString)) {
errors.add(new CheckMessage("", eHeaderString, aHeaderString));
}
String eFooterString = getFooterString(expected.getFooter());
String aFooterString = getFooterString(actual.getFooter());
if (!eFooterString.equals(aFooterString)) {
errors.add(new CheckMessage("", eFooterString, aFooterString));
}
//
String eBreaksString = getBreaksString(expected);
String aBreaksString = getBreaksString(actual);
log.debug(eBreaksString + "/" + aBreaksString);
if (!eBreaksString.equals(aBreaksString)) {
errors.add(new CheckMessage("", eBreaksString, aBreaksString));
}
// ?
String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected));
String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual));
if (expectedPrintArea != null || actualPrintArea != null) {
// ????????Null?????????????
// if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) {
// errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea));
// }
if (!isActCopyOfExp) {
if (expectedPrintArea == null || actualPrintArea == null
|| !expectedPrintArea.equals(actualPrintArea)) {
errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea));
}
}
}
// (?)
String ePaneInformationString = getPaneInformationString(expected.getPaneInformation());
String aPaneInformationString = getPaneInformationString(actual.getPaneInformation());
if (!ePaneInformationString.equals(aPaneInformationString)) {
errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea));
}
// ??????
// ?????
// ?????
//
//
if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) {
errors.add(new CheckMessage("",
String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines())));
}
// ?
if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) {
errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()),
String.valueOf(actual.isDisplayRowColHeadings())));
}
// ?
if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) {
errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()),
String.valueOf(actual.isDisplayFormulas())));
}
// ??
if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) {
errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()),
String.valueOf(actual.getNumMergedRegions())));
}
for (int i = 0; i < actual.getNumMergedRegions(); i++) {
CellRangeAddress actualAddress = null;
if (expected instanceof HSSFSheet) {
actualAddress = ((HSSFSheet) actual).getMergedRegion(i);
} else if (expected instanceof XSSFSheet) {
actualAddress = ((XSSFSheet) actual).getMergedRegion(i);
}
StringBuffer expectedAdressBuffer = new StringBuffer();
boolean equalAddress = false;
for (int j = 0; j < expected.getNumMergedRegions(); j++) {
CellRangeAddress expectedAddress = null;
if (expected instanceof HSSFSheet) {
expectedAddress = ((HSSFSheet) expected).getMergedRegion(j);
} else if (expected instanceof XSSFSheet) {
expectedAddress = ((XSSFSheet) expected).getMergedRegion(j);
}
if (expectedAddress.toString().equals(actualAddress.toString())) {
equalAddress = true;
break;
}
CellReference crA = new CellReference(expectedAddress.getFirstRow(),
expectedAddress.getFirstColumn());
CellReference crB = new CellReference(expectedAddress.getLastRow(),
expectedAddress.getLastColumn());
expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]");
}
if (!equalAddress) {
errors.add(new CheckMessage("??", expectedAdressBuffer.toString(),
actualAddress.toString()));
}
}
int maxColumnNum = -1;
if (expected instanceof HSSFSheet) {
maxColumnNum = HSSF_MAX_COLUMN_NUMBER;
} else if (expected instanceof XSSFSheet) {
maxColumnNum = XSSF_MAX_COLUMN_NUMBER;
}
for (int i = 0; i < maxColumnNum; i++) {
try {
//
checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(),
actual.getColumnStyle(i));
} catch (ReportsCheckException e) {
CheckMessage checkMessage = e.getCheckMessages().iterator().next();
checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage());
errors.add(checkMessage);
}
//
if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) {
errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)),
String.valueOf(actual.getColumnWidth(i))));
}
}
// ???
if (expected.getLastRowNum() != actual.getLastRowNum()) {
// ??????
if (expected.getLastRowNum() < actual.getLastRowNum()) {
int lastRowIndex = -1;
if (expected instanceof HSSFSheet) {
lastRowIndex = 0;
}
Iterator<Row> rowIterator = actual.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// ?????
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellTypeEnum() != CellType.BLANK) {
lastRowIndex = row.getRowNum();
break;
}
}
}
if (expected.getLastRowNum() != lastRowIndex) {
errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
String.valueOf(lastRowIndex)));
}
} else {
errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
String.valueOf(actual.getLastRowNum())));
}
}
if (errors.isEmpty()) {
for (int i = 0; i <= expected.getLastRowNum(); i++) {
try {
checkRow(expected.getRow(i), actual.getRow(i));
} catch (ReportsCheckException e) {
errors.addAll(e.getCheckMessages());
}
}
}
if (!errors.isEmpty()) {
if (log.isErrorEnabled()) {
for (CheckMessage message : errors) {
log.error("?[" + message.getMessage() + "]");
log.error(":" + message.getExpected());
log.error(":" + message.getActual());
}
}
throw new ReportsCheckException(errors);
}
if (log.isDebugEnabled()) {
log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
+ "] check end.");
}
}
@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {
Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());
// ?/*from w ww . j a v a 2 s .c om*/
checkParam(paramDef, tagCell);
String tag = tagCell.getStringCellValue();
ReportsParserInfo info = (ReportsParserInfo) data;
ParamInfo paramInfo = info.getParamInfo();
ParsedReportInfo parsedReportInfo = new ParsedReportInfo();
// ??
Object[] paramValues = null;
try {
// ???
String replaceParam = paramDef.get(PARAM_VALUE);
// ?
Integer repeatNum = null;
if (paramDef.containsKey(PARAM_REPEAT_NUM)) {
repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM));
}
// ??
Integer minRepeatNum = null;
if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) {
minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM));
}
// ?
boolean sheetLink = false;
if (paramDef.containsKey(PARAM_SHEET_LINK)) {
sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK));
}
//
String propertyName = null;
if (paramDef.containsKey(PARAM_PROPERTY)) {
propertyName = paramDef.get(PARAM_PROPERTY);
}
// ???
boolean hideDuplicate = false;
if (paramDef.containsKey(PARAM_DUPLICATE)) {
hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE));
}
//
if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) {
// ??
paramValues = ReportsUtil.getSheetNames(info.getReportBook()).toArray();
} else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) {
//
paramValues = ReportsUtil
.getSheetValues(info.getReportBook(), propertyName, info.getReportParsers()).toArray();
} else {
// ???
if (paramInfo != null) {
paramValues = getParamData(paramInfo, replaceParam);
}
}
if (paramValues == null || paramValues.length == 0) {
// ?
paramValues = new Object[] { null };
}
// ?
if (hideDuplicate && paramValues.length > 1) {
List<Object> paramValuesList = new ArrayList<Object>();
for (int i = 0; i <= paramValues.length - 1; i++) {
// ?????
if (!paramValuesList.contains(paramValues[i])) {
paramValuesList.add(paramValues[i]);
} else {
paramValuesList.add(null);
}
}
paramValues = paramValuesList.toArray();
}
// ?
int shiftNum = paramValues.length;
// ?
int paramLength = paramValues.length;
// ???????
if (minRepeatNum != null && shiftNum < minRepeatNum) {
Object[] tmpValues = new Object[minRepeatNum];
System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length);
paramValues = tmpValues;
shiftNum = paramValues.length;
paramLength = paramValues.length;
}
// ???
int defaultFromCellRowIndex = tagCell.getRowIndex();
// ???
int defaultFromCellColIndex = tagCell.getColumnIndex();
// ??
int unitColSize = 1;
// ???
List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>();
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress targetAddress = sheet.getMergedRegion(i);
maegedAddresses.add(targetAddress);
}
// ???
if (maegedAddresses.size() > 0) {
// ?????????????
for (CellRangeAddress curMergedAdress : maegedAddresses) {
if (defaultFromCellColIndex == curMergedAdress.getFirstColumn()
&& defaultFromCellRowIndex == curMergedAdress.getFirstRow()) {
// ????????????
// ??????
unitColSize = curMergedAdress.getLastColumn() - curMergedAdress.getFirstColumn() + 1;
// ??????
shiftNum = shiftNum * unitColSize;
}
}
}
// ?
if (repeatNum != null && repeatNum < shiftNum) {
// ??????
// ????????????????
// ?(repeatNum)??(unitColSize)??
shiftNum = repeatNum * unitColSize;
// ??????
paramLength = repeatNum;
}
// ???
// ?????
tagCell = new CellClone(tagCell);
List<Cell> cellList = new ArrayList<Cell>();
int defaultToOverCellColIndex = tagCell.getColumnIndex() + unitColSize;
for (int i = defaultFromCellColIndex; i < defaultToOverCellColIndex; i++) {
Row targetCellRow = sheet.getRow(tagCell.getRowIndex());
cellList.add(new CellClone(targetCellRow.getCell(i)));
}
// ?
if (shiftNum > 1) {
// ?(????????)
int shiftColSize = tagCell.getColumnIndex() + shiftNum - unitColSize - 1;
// ???
CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), tagCell.getRowIndex(),
tagCell.getColumnIndex(), shiftColSize);
PoiUtil.insertRangeRight(sheet, rangeAddress);
//
int tagCellWidth = sheet.getColumnWidth(tagCell.getColumnIndex());
for (int i = tagCell.getColumnIndex() + 1; i <= shiftColSize; i++) {
int colWidth = sheet.getColumnWidth(i);
if (colWidth < tagCellWidth) {
// ?? ???????
// ??????
sheet.setColumnWidth(i, tagCellWidth);
}
}
}
// ???
Workbook workbook = sheet.getWorkbook();
String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet));
// ??
List<String> sheetNames = ReportsUtil.getSheetNames(info.getReportBook());
// ?
List<Object> resultValues = new ArrayList<Object>();
// ??(beforeValue)
Object beforeValue = null;
// ?
int valueIndex = -1;
// ?????
for (int colIndex = 0; colIndex < shiftNum; colIndex++) {
// ??
Row row = sheet.getRow(tagCell.getRowIndex());
if (row == null) {
// ????
// ?
// (??)??????????
// ??null???(RowCreate?)???????????
row = sheet.createRow(tagCell.getRowIndex());
}
// ??
Cell cell = row.getCell(tagCell.getColumnIndex() + colIndex);
if (cell == null) {
cell = row.createCell(tagCell.getColumnIndex() + colIndex);
}
// ????(null)
Object value = null;
// ??????
// ??0???(?????????)???????
int cellIndex = colIndex % unitColSize;
// ?????????
boolean skipCol = false;
if (cellIndex != 0) {
skipCol = true;
} else {
valueIndex++;
}
//
// ?
PoiUtil.copyCell(cellList.get(cellIndex), cell);
// ?
Object currentValue = paramValues[valueIndex];
// ??=true???????????
boolean duplicateValue = false;
if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) {
// ???
duplicateValue = true;
}
if (!skipCol && !(hideDuplicate && duplicateValue)) {
// ??=true
// ??????????????
value = currentValue;
}
if (log.isDebugEnabled()) {
log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
+ cell.getColumnIndex() + ")] " + tag + " " + value);
}
PoiUtil.setCellValue(cell, value);
resultValues.add(value);
// ?
if (sheetLink) {
if (!skipCol && valueIndex < sheetNames.size()) {
PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT,
"'" + sheetNames.get(valueIndex) + "'!A1");
if (log.isDebugEnabled()) {
log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
+ cell.getColumnIndex() + ")] Hyperlink " + "'"
+ sheetNames.get(valueIndex) + "'!A1");
}
}
}
// ??
// ??????????????????
if (!skipCol && unitColSize > 1 && paramLength > valueIndex + 1) {
CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(),
cell.getColumnIndex(), cell.getColumnIndex() + unitColSize - 1);
sheet.addMergedRegion(rangeAddress);
// ????????????
beforeValue = value;
}
// ???????
if (unitColSize == 1) {
beforeValue = value;
}
}
parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex());
// ??
parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex() + unitColSize - 1);
parsedReportInfo.setRowIndex(tagCell.getRowIndex());
parsedReportInfo.setColumnIndex(tagCell.getColumnIndex() + shiftNum - 1);
parsedReportInfo.setParsedObject(resultValues);
if (log.isDebugEnabled()) {
log.debug(parsedReportInfo);
}
return parsedReportInfo;
} catch (Exception e) {
throw new ParseException(tagCell, e);
}
}
/**
* ??????int[?]?????// www . ja v a 2 s . co m
*
* @param sheet ??
* @param bStartColIndex ?
* @param bEndColIndex ?
* @return ??
*/
public static int[] getColumnWidth(Sheet sheet, int bStartColIndex, int bEndColIndex) {
int[] columnWidth = new int[bEndColIndex - bStartColIndex + 1];
int colIdx = 0;
for (int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) {
columnWidth[colIdx] = sheet.getColumnWidth(bColIndex);
colIdx++;
}
return columnWidth;
}
/**
* 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 . j a v a 2s .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 primtive double that contains the required
* width of the image in millimetres
* @param resizeBehaviour A primitve 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 co-ordinate 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.
colWidthMM = reqImageWidthMM;
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
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 {
// 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);
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./* w w w . j a v a2s. c o 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 co-ordinate 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.
anchorDetail = new ClientAnchorDetail(startingColumn, toColumn,
ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS);
}
// 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;
}
// 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);
// Now create the ClientAnchorDetail object, setting the from and to
// columns and the inset.
anchorDetail = new ClientAnchorDetail(startingColumn, toColumn, inset);
}
return (anchorDetail);
}
/**
* Copy sheets.// w w w. j a v a2 s.c o m
*
* @param _newSheet the new sheet
* @param _sheet the sheet
* @param _copyStyle the copy style
*/
protected void copySheets(final Sheet _newSheet, final Sheet _sheet, final boolean _copyStyle) {
int maxColumnNum = 0;
final Map<Integer, CellStyle> styleMap = _copyStyle ? new HashMap<>() : null;
for (int i = _sheet.getFirstRowNum(); i <= _sheet.getLastRowNum(); i++) {
final Row srcRow = _sheet.getRow(i);
final Row destRow = _newSheet.createRow(i);
if (srcRow != null) {
copyRow(_sheet, _newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
_newSheet.setColumnWidth(i, _sheet.getColumnWidth(i));
}
}
/**
* 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 av a 2s.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 primtive double that contains the required width of the
* image in millimetres
* @param resizeBehaviour
* A primitve 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 co-ordinate 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.
colWidthMM = reqImageWidthMM;
colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM);
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 {
// 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);
colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
}
return (colClientAnchorDetail);
}
public JTable createTable(Sheet sheet) {
SheetTableModel sheetTableModel = new SheetTableModel(sheet);
JTable table = new SheetTable(sheetTableModel);
table.setDefaultRenderer(Cell.class, new CellRenderer());
TableCellEditor editor = new org.joeffice.spreadsheet.cell.CellEditor();
table.setDefaultEditor(Cell.class, editor);
int columnsCount = sheetTableModel.getColumnCount();
for (int i = 0; i < columnsCount; i++) {
TableColumn tableColumn = table.getColumnModel().getColumn(i);
tableColumn.setCellRenderer(new CellRenderer());
tableColumn.setCellEditor(editor);
int widthUnits = sheet.getColumnWidth(i);
tableColumn.setPreferredWidth(widthUnitsToPixel(widthUnits));
}//from w w w . j a v a2 s. c o m
int rowCount = sheetTableModel.getRowCount();
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row != null) {
int cellHeight = (int) Math.ceil(sheet.getRow(rowIndex).getHeightInPoints());
cellHeight += CELL_HEIGHT_MARGINS;
table.setRowHeight(rowIndex, cellHeight);
}
}
table.setAutoscrolls(true);
table.setFillsViewportHeight(true);
JLabel tableHeader = (JLabel) table.getTableHeader().getDefaultRenderer();
tableHeader.setHorizontalAlignment(SwingConstants.CENTER);
table.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
table.setCellSelectionEnabled(true);
TableColumnAdjuster tca = new TableColumnAdjuster(table, 20);
if (sheet.getDefaultColumnWidth() == -1) {
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
tca.setOnlyAdjustLarger(true);
tca.setLeaveEmptyAsIs(true);
tca.adjustColumns();
}
table.setTransferHandler(new TableTransferHandler());
table.setDragEnabled(true);
table.setDropMode(DropMode.ON_OR_INSERT);
Action cutAction = new ClipboardAction(DefaultEditorKit.cutAction);
Action copyAction = new ClipboardAction(DefaultEditorKit.copyAction);
Action pasteAction = new ClipboardAction(DefaultEditorKit.pasteAction);
table.getActionMap().put(DefaultEditorKit.cutAction, cutAction);
table.getActionMap().put(DefaultEditorKit.copyAction, copyAction);
table.getActionMap().put(DefaultEditorKit.pasteAction, pasteAction);
//table.setIntercellSpacing(new Dimension(0, 0));
table.putClientProperty("print.printable", Boolean.TRUE);
Rectangle lastDataCellBounds = table.getCellRect(sheet.getLastRowNum(), sheetTableModel.getLastColumnNum(),
true);
table.putClientProperty("print.size", new Dimension(lastDataCellBounds.x + lastDataCellBounds.width,
lastDataCellBounds.y + lastDataCellBounds.height));
new SheetListener(table);
if (!sheet.isDisplayGridlines()) {
table.setShowGrid(false);
}
return table;
}
@Override
public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName,
InputStream is, IProcessingConsole console) throws ImportException {
List<SheetFullName> sheetNames = new ArrayList<SheetFullName>();
try {/*from w w w. j a va 2s. co m*/
log.info("Starting import:" + workbookName);
Workbook poiWorkbook = new HSSFWorkbook(is);
IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName);
log.info("Read POI");
NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver(
styleService.getStyleDefinitions(workbookName));
HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette();
for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) {
Sheet poiSheet = poiWorkbook.getSheetAt(s);
SheetFullName sheetName = new SheetFullName(workbookName,
getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
ISheet nxSheet = null;
BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder();
try {
List<CellReference> refreshCells = new ArrayList<CellReference>();
for (Row poiRow : poiSheet) {
if (poiRow.getRowNum() % 100 == 0) {
log.info("importing row #" + poiRow.getRowNum());
}
for (Cell poiCell : poiRow) {
if (nxSheet == null) {
// lazy creation
while (true) {
try {
nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(),
SheetType.normal);
nxSheet.setRefreshEnabled(false);
break;
} catch (AlreadyExistsException e) {
// may happen is simultaneous imports take place
sheetName = new SheetFullName(workbookName,
getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
}
}
}
CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(),
poiCell.getColumnIndex());
try {
ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver);
if (cmd != null) {
cellCommandBuilder.command(cmd);
}
if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
refreshCells.add(ref);
}
} catch (Exception e) {
if (console != null) {
console.println("Could import cell " + ref + ":" + poiCell + ":" + e);
}
log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e);
}
}
if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) {
nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
cellCommandBuilder = new BlockCellCommandBuilder();
}
}
if (nxSheet == null) {
// empty sheet
continue;
}
if (!cellCommandBuilder.isEmpty()) {
nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
}
// add the columns after as is not very clear how to get the number of cols in poi
for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) {
int width = 50;
try {
width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c));
nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width));
} catch (NullPointerException ex) {
// ignore it
// NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998)
// defaultColumnWidth can be null !?
}
CellStyle poiStyle = poiSheet.getColumnStyle(c);
if (poiStyle == null) {
continue;
}
Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);
if (styles != null) {
nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles));
}
}
// merge
List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions());
for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) {
CellRangeAddress poiSpan = poiSheet.getMergedRegion(i);
spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(),
poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn()));
}
nxSheet.sendCommand(SheetCommands.spans(spans));
// refresh all the cells now
nxSheet.setRefreshEnabled(true);
nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false));
} finally {
if (nxSheet != null) {
sheetNames.add(sheetName);
}
}
}
} catch (IOException e) {
throw new ImportException(null, "Cannot open workbook:" + e, e);
} catch (StorageException e) {
throw new ImportException(null, "Error storing sheet:" + e, e);
} catch (NotFoundException e) {
throw new ImportException(null, "Cannot find workbook:" + e, e);
} catch (NetxiliaResourceException e) {
throw new ImportException(null, e.getMessage(), e);
} catch (NetxiliaBusinessException e) {
throw new ImportException(null, e.getMessage(), e);
}
return sheetNames;
}