@Override
public void close() {
try {
int nbS = wb.getNumberOfSheets();
for (int i = 0; i < nbS; i++) {
Sheet s = wb.getSheetAt(i);
if (s.getRow(0)
!= null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce
// cas pas de possibilité de traiter la mise en page de gros fichiers
for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
Cell c = s.getRow(0).getCell(j);
c.getSheet().autoSizeColumn(c.getColumnIndex());
}
Cell firstCell = s.getRow(0).getCell(0);
Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
s.setAutoFilter(
new CellRangeAddress(
firstCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getColumnIndex()));
}
}
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
cell.setCellValue(NumberUtils.format(fieldValue, 0));
cell.setCellStyle(cellStyle);
}
/**
* * Gets the formula content of a given cell from the POI-model as a stack of ptgs.The Ptgs are
* in reverse polish notation.
*
* @param poiCell The given cell from the POI-model.
* @return The contents of the given cell as a stack of ptgs.
*/
private Stack<Ptg> getPtgStackFor(org.apache.poi.ss.usermodel.Cell poiCell) {
// Parse POI-Formula to ptgs.
Ptg[] ptgArray =
FormulaParser.parse(
poiCell.getCellFormula(),
poiIO.formulaParsingWorkbook,
poiCell.getCellType(),
poiIO.poiWorkbook.getSheetIndex(poiCell.getSheet()));
// Create stack to keep the reverse polish notation and to ease the
// transformation.
Stack<Ptg> ptgs = new Stack<Ptg>();
for (Ptg ptg : ptgArray) {
ptgs.push(ptg);
}
return ptgs;
}
public static Object resolveCellValue(
Cell cell, String emptyMarker, String nullMarker, Converter<String, ?> stringPreprocessor) {
if (cell == null) return null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
double numericCellValue = cell.getNumericCellValue();
if (MathUtil.isIntegralValue(numericCellValue))
return ((Double) numericCellValue).longValue();
return numericCellValue;
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator =
cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
return convertString(cellValue, emptyMarker, stringPreprocessor);
case HSSFCell.CELL_TYPE_NUMERIC:
return cellValue.getNumberValue();
case Cell.CELL_TYPE_BOOLEAN:
return cellValue.getBooleanValue();
case HSSFCell.CELL_TYPE_BLANK:
case HSSFCell.CELL_TYPE_ERROR:
return null;
default:
throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
// CELL_TYPE_FORMULA is not supposed to be encountered here
}
default:
throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
}
}
public void outputNumericAdapter(
DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
StringBuilder format = new StringBuilder("0");
for (int i = 0; i < config.floatCount(); i++) {
if (i == 0) format.append(".");
format.append("0");
}
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
cell.setCellStyle(cellStyle);
}
/**
* {@inheritDoc}
*
* @see
* org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
* org.eclipse.emf.ecore.EStructuralFeature)
*/
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature)
throws EMFFormsConverterException {
String string;
try {
string = cell.getStringCellValue();
} catch (final IllegalStateException e) {
throw new EMFFormsConverterException(
String.format(
"Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
e);
}
if (string == null || string.length() == 0) {
return Collections.emptyList();
}
final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
final EDataType eDataType = eAttribute.getEAttributeType();
if (isDecimalNumber(eDataType.getInstanceClass())) {
string =
string.replace(
DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(),
'.');
}
final List<Object> result = new ArrayList<Object>();
final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
for (final String element : string.split(SEPARATOR)) {
try {
result.add(eFactory.createFromString(eDataType, element));
} // BEGIN SUPRESS CATCH EXCEPTION
catch (final RuntimeException ex) { // END SUPRESS CATCH EXCEPTION
throw new EMFFormsConverterException(
MessageFormat.format(
"The cell value {0} could not converted to a model value.", string)); // $NON-NLS-1$
}
}
return result;
}
/**
* 导出时间适配器
*
* @param fieldValue
* @param fieldName
* @return
* @throws AdapterException
*/
public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
Date date = null;
if (fieldValue == null) {
log.debug("fieldValue is null return");
cell.setCellValue("");
return;
} else if (fieldValue instanceof Date) {
log.debug("fieldValue instanceof Date ");
date = (Date) fieldValue;
} else if (fieldValue instanceof String) {
log.debug("fieldValue instanceof String ");
InputDateConfig config = dataBean.getInputConfig(fieldName);
try {
date = DateUtil.formatToDate((String) fieldValue, config.format());
} catch (ParseException e) {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
} else if (fieldValue instanceof Long) {
log.debug("fieldValue instanceof Long ");
date = new Date((Long) fieldValue);
} else {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
Workbook workbook = cell.getSheet().getWorkbook();
OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
CellStyle cellStyle = cell.getCellStyle();
if (cellStyle == null) cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
cell.setCellStyle(cellStyle);
cell.setCellValue(date);
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double getCellWidth(
Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
Sheet sheet = cell.getSheet();
Workbook wb = sheet.getWorkbook();
Row row = cell.getRow();
int column = cell.getColumnIndex();
int colspan = 1;
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i);
if (containsCell(region, row.getRowNum(), column)) {
if (!useMergedCells) {
// If we're not using merged cells, skip this one and move on to the next.
return -1;
}
cell = row.getCell(region.getFirstColumn());
colspan = 1 + region.getLastColumn() - region.getFirstColumn();
}
}
CellStyle style = cell.getCellStyle();
int cellType = cell.getCellType();
// for formula cells we compute the cell width for the cached formula result
if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();
Font font = wb.getFontAt(style.getFontIndex());
AttributedString str;
TextLayout layout;
double width = -1;
if (cellType == Cell.CELL_TYPE_STRING) {
RichTextString rt = cell.getRichStringCellValue();
String[] lines = rt.getString().split("\\n");
for (int i = 0; i < lines.length; i++) {
String txt = lines[i] + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
if (rt.numFormattingRuns() > 0) {
// TODO: support rich text fragments
}
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
} else {
String sval = null;
if (cellType == Cell.CELL_TYPE_NUMERIC) {
// Try to get it formatted to look the same as excel
try {
sval = formatter.formatCellValue(cell, dummyEvaluator);
} catch (Exception e) {
sval = String.valueOf(cell.getNumericCellValue());
}
} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
}
if (sval != null) {
String txt = sval + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
}
return width;
}
/**
* Discription:[写一个单元格]
*
* @param cell 单元格
* @param value 写入的值
* @param valueType 写入的值的类型
* @param dateFormat 日期格式，默认yyyy-MM-dd
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public void writeCell(Cell cell, Object value, String valueType, String dateFormat) {
if (cell == null || value == null) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
String cellValue = "";
if ("String".equals(valueType)) {
cellValue = value.toString();
} else if ("int".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("float".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("double".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("Number".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("BigDecimal".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("byte[]".equals(valueType)) {
// 有图片时，设置行高为60px;
cell.getRow().setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(
0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex());
anchor.setAnchorType(2);
// 声明一个画图的顶级管理器
cell.getSheet()
.createDrawingPatriarch()
.createPicture(
anchor,
cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
return;
} else if ("Date".equals(valueType)) {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
cellValue = sdf.format(value);
} else if ("boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else if ("Boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else {
cellValue = String.valueOf(value);
}
cell.setCellValue(cellValue);
return;
}
