private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
private void loadData() {
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
if (i % 2 == 0) {
Row rowID = sheet.getRow(i);
Row rowName = sheet.getRow(i + 1);
for (int j = 0; j < 15; j++) {
Cell cellID = rowID.getCell(j);
Cell cellName = rowName.getCell(j);
String name = WorkBookUtil.getCellValue(cellName);
String cellValue = WorkBookUtil.getCellValue(cellID);
if (name == null || name.equalsIgnoreCase("")) {
continue;
}
int parseInt = Integer.parseInt(cellValue);
if (catalogUsers.containsValue(name)) {
repeatUsers.put(parseInt, name);
}
if (cellName.getCellStyle().getFillPattern() == CellStyle.SOLID_FOREGROUND) {
colorUsers.put(parseInt, name);
}
catalogUsers.put(parseInt, name);
}
}
}
}
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
/**
* 取对应单元格类型的值
*
* @param c 列数
* @return 单元格的值
*/
private String getCellValue(Cell c) {
String o = null;
switch (c.getCellType()) {
case Cell.CELL_TYPE_BLANK:
o = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case CELL_TYPE_FORMULA:
o = String.valueOf(c.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式
SimpleDateFormat sdf;
if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = c.getDateCellValue();
o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date);
} else if (c.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = c.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
o = sdf.format(date);
}
break;
case Cell.CELL_TYPE_STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
/*
* Cannot keep existing formats when using CellBlock.
* Take a file with existing formats and write it back.
*/
public static void issue22() throws InvalidFormatException, IOException {
System.out.println("Testing issue22 =================================");
FileInputStream in = new FileInputStream(new File("../resources/issue22.xlsx"));
Workbook wb = WorkbookFactory.create(in);
Sheet sheet0 = wb.getSheetAt(0);
Sheet sheet1 = wb.getSheetAt(1);
Sheet sheet2 = wb.getSheetAt(2);
Cell c00_before = sheet1.getRow(0).getCell(0);
CellStyle cs_before = c00_before.getCellStyle();
System.out.println(
"Background color before creation is: " + cs_before.getFillBackgroundColor());
System.out.println("Cell style is: " + cs_before.toString());
double[] data = {1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0};
System.out.println("On Sheet 1 ....");
// need to create=true, because sheet is empty!
RCellBlock cb0 = new RCellBlock(sheet0, 0, 0, 3, 3, true);
cb0.setMatrixData(0, 2, 0, 2, data, true, null);
System.out.println("On Sheet 2 ....");
RCellBlock cb1 = new RCellBlock(sheet1, 0, 0, 3, 3, false);
Cell c00 = cb1.getCell(0, 0);
CellStyle cs = c00.getCellStyle();
System.out.println("Background color after creation is: " + cs.getFillBackgroundColor());
System.out.println("Cell style is: " + cs.toString());
// good news, that the BackgroundColor in the CellBlock is maintained!
cb1.setMatrixData(0, 2, 0, 2, data, true, null);
System.out.println("On Sheet 3 ....");
RCellBlock cb2 = new RCellBlock(sheet2, 0, 0, 3, 3, false);
cb2.setMatrixData(0, 2, 0, 2, data, true, null);
FileOutputStream out = new FileOutputStream("/tmp/issue22_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue22_out.xlsx");
}
@Test
public void testReadCell() throws Exception {
when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
final HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
final Date currentDate = new Date();
cellStyle.setDataFormat(
hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm"));
when(mockCell.getCellStyle()).thenReturn(cellStyle);
when(mockCell.getDateCellValue()).thenReturn(currentDate);
final Calendar calendar = calendarConverter.readCell(mockCell);
assertNotNull(calendar);
assertEquals(currentDate.getTime(), calendar.getTimeInMillis());
}
public void readUnchangableCell(Row row, int cellIdx, FormDisplayCell tableCell, DataDto dto) {
Cell cell = row.getCell(cellIdx);
if (null == cell) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " cell is null.");
}
if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC
&& cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " cell is is not numeric.");
}
double editedValue = cell.getNumericCellValue();
if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) {
// Percent
editedValue = editedValue * 100;
}
String editedValueString = formatter.formatValueToAtLeastDP(dto, editedValue);
if (!editedValueString.equals(dto.getValue()) && !editedValueString.isEmpty()) {
// value has changed so log a warning
metaData.addWarning(
row.getRowNum(),
cellIdx,
dto.getValue(),
editedValueString,
"This is a " + tableCell.getCellType().name() + " cell. It cannot be changed.");
}
}
public void printStyles(Workbook wb) {
if (DEFAULTS_CLASS_CSS == null) {
DEFAULTS_CLASS_CSS = getDefaultsClassCss();
}
out.format(DEFAULTS_CLASS_CSS);
Set<CellStyle> seen = new HashSet<CellStyle>();
sheet = wb.getSheetAt(sheetNum);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row = rows.next();
for (Cell cell : row) {
CellStyle style = cell.getCellStyle();
if (!seen.contains(style)) {
printStyle(style);
seen.add(style);
}
}
}
}
private void contentProcess(
Sheet tempSheet, Sheet newSheet, int contentRow, ConfirmationRecord data) throws Exception {
int tempRowNum = -1;
if (data.getAction() == null) {
tempRowNum = 7;
} else if (data.getAction().getParamKey().equals("CONFIRMATION_LOG_ACTION_ACCEPT")) {
tempRowNum = 5;
} else {
tempRowNum = 6;
}
Row tempRow = tempSheet.getRow(tempRowNum);
Row newRow = newSheet.createRow(contentRow);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
Cell tCell = tempRow.getCell(c);
Cell cell = newRow.createCell(c, tCell.getCellType());
setValue(cell, data);
cell.setCellStyle(tCell.getCellStyle());
cell.getRow().getSheet().setColumnWidth(c, tempRow.getSheet().getColumnWidth(c));
}
}
@Override
public void renderCell(Element element, int rowIndex, int columnIndex) {
Cell cell = sheet.getRow(rowIndex).createCell(columnIndex);
Double numericValue;
if (isDateCell(element)) {
DateFormat df = new SimpleDateFormat(getDateCellFormat(element));
try {
cell.setCellValue(df.parse(getElementText(element)));
} catch (ParseException pex) {
System.out.println("Invalid Usage");
}
} else if ((numericValue = getNumericValue(element)) != null) {
cell.setCellValue(numericValue);
} else {
cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING);
cell.setCellValue(getElementText(element));
}
Style style = styleMapper.getStyleForElement(element);
cell.setCellStyle(styleGenerator.getStyle(cell, style));
if (isDateCell(element)) {
CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
cell.getCellStyle()
.setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element)));
}
String commentText;
if ((commentText = getCellCommentText(element)) != null) {
ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element));
}
if (definesFreezePane(element)) {
sheet.createFreezePane(columnIndex, rowIndex);
}
}
private java.util.Date getDateValue(Cell cell) {
java.util.Date dtValue = null;
try {
dtValue = cell.getDateCellValue();
} catch (Exception ex) {
// ignore
}
String fmt = cell.getCellStyle().getDataFormatString();
double dv = cell.getNumericCellValue();
if (dtValue == null) {
dtValue = getJavaDate(dv);
}
if (dtValue != null) {
if (isTimestampFormat(fmt)) {
return new java.sql.Timestamp(dtValue.getTime());
} else {
return new java.sql.Date(dtValue.getTime());
}
}
return null;
}
public static void exportExcelForMap(
Workbook workBook, List<Map<String, Object>> dataList, OutputStream outputStream) {
try {
Sheet sheet = workBook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(lastRowNum);
int rowNum = row.getLastCellNum();
List<String> rowNames = new ArrayList<String>();
List<CellStyle> styles = new ArrayList<CellStyle>();
for (int i = 0; i < rowNum; i++) {
Cell cell = row.getCell(i);
rowNames.add(cell.getStringCellValue());
styles.add(cell.getCellStyle());
}
if (dataList != null && dataList.size() > 0) {
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> rowData = dataList.get(i);
rowData.put("NUM", lastRowNum + i);
createRow(sheet, rowData, rowNames, styles, lastRowNum + i);
rowData = null;
}
} else {
sheet.removeRow(row);
}
workBook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
throw new RuntimeException("导出文件发生异常");
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException("关闭导出文件流发生异常");
}
}
}
}
public void generate(
List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
throws IOException {
for (int i = 0; i < sheetNames.size(); i++) {
// Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
// Create row object
XSSFRow row;
// This data needs to be written (Object[])
Map<Integer, String[]> contentMap = new TreeMap<>();
contentMap.put(1, headersList.get(i));
for (int j = 0; j < contents.get(i).size(); j++) {
contentMap.put(j + 2, contents.get(i).get(j));
}
// Iterate over data and write to sheet
Set<Integer> keyid = contentMap.keySet();
int rowid = 0;
for (Integer key : keyid) {
row = spreadsheet.createRow(rowid++);
String[] objectArr = contentMap.get(key);
int cellid = 0;
for (String obj : objectArr) {
Cell cell = row.createCell(cellid++);
CellStyle style = cell.getCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);
cell.setCellValue(obj);
}
}
// for (int c = 0; c < headersList.get(i).length; c++) {
// spreadsheet.autoSizeColumn(c);
// }
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
out.close();
}
}
/**
* 导出时间适配器
*
* @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);
}
static int getColorVall(Cell cellVall) throws SQLException {
if (cellVall != null) {
short color = cellVall.getCellStyle().getFillForegroundColor();
switch (color) {
case 64:
return 1;
case 10:
return 3;
case 17:
return 5;
case 56:
return 7;
case 12:
return 7;
case 13:
return 7;
case 57:
return 8;
case 30:
return 8;
case 51:
return 9;
case 52:
return 9;
case 53:
return 9;
case 36:
return 10;
default:
return 1;
}
}
return 0;
}
private CellStyle cloneStyle(Cell cell) {
CellStyle newStyle = spreadsheet.getWorkbook().createCellStyle();
newStyle.cloneStyleFrom(cell.getCellStyle());
return newStyle;
}
/**
* 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;
}
public String readDataCell(Row row, int cellIdx, DataDto dto, CellType cellType) {
Cell cell = row.getCell(cellIdx);
if (null == cell) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " cell is null.");
return null;
}
String editedValueString = null;
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
try {
double editedValue = cell.getNumericCellValue();
if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) {
// Percent
editedValue = editedValue * 100;
}
editedValueString = formatter.formatValueToAtLeastDP(dto, editedValue);
} catch (IllegalStateException e) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " cell is not numeric. CELL TYPE is "
+ cell.getCellType()
+ ". Cannot read cell.");
metaData.addWarning(
row.getRowNum(),
cellIdx,
" current value: "
+ dto.getValue()
+ " cell is not numeric. CELL TYPE is "
+ cell.getCellType()
+ ". Cannot read cell.");
}
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
RichTextString editedValue = cell.getRichStringCellValue();
if (editedValue.getString().isEmpty()) {
editedValueString = "";
} else {
if (isNumberInString(editedValue.getString())) {
editedValueString = editedValue.getString();
} else {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " data '"
+ editedValue.getString()
+ "' is not numeric. CELL TYPE is "
+ cell.getCellType());
}
}
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
editedValueString = "";
} else {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getValue()
+ " cell is not numeric. CELL TYPE is "
+ cell.getCellType());
}
return editedValueString;
}
/** Adds in a Row to the given Sheet */
public Row addRow(
Workbook wb,
SheetToAdd sheetToAdd,
RowToAdd rowToAdd,
int rowIndex,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
// Create a new row and copy over style attributes from the row to add
Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
Row rowToClone = rowToAdd.getRowToClone();
try {
CellStyle rowStyle = rowToClone.getRowStyle();
if (rowStyle != null) {
newRow.setRowStyle(rowStyle);
}
} catch (Exception e) {
// No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
// rowStyle. Mysteries of POI
}
newRow.setHeight(rowToClone.getHeight());
// Iterate across all of the cells in the row, and configure all those that need to be
// added/cloned
List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();
int totalCells = rowToClone.getPhysicalNumberOfCells();
int cellsFound = 0;
for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
Cell currentCell = rowToClone.getCell(cellNum);
log.debug("Handling cell: " + currentCell);
if (currentCell != null) {
cellsFound++;
}
// If we find that the cell that we are on is a repeating cell, then add the appropriate
// number of cells to clone
String repeatingColumnProperty =
getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
if (repeatingColumnProperty != null) {
String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
int numCellsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numCellsToRepeat; i++) {
Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
if (repeatNum == 1 && cell != null && cell != currentCell) {
cellsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
rowToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
cellsToAdd.add(new CellToAdd(cell, newReplacements));
log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
}
}
cellNum += numCellsToRepeat;
} else {
cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
log.debug("Adding " + currentCell);
}
}
// Now, go through all of the collected cells, and add them back in
ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
for (int i = 0; i < cellsToAdd.size(); i++) {
CellToAdd cellToAdd = cellsToAdd.get(i);
Cell newCell = newRow.createCell(i);
Cell cellToClone = cellToAdd.getCellToClone();
if (cellToClone != null) {
String contents = ExcelUtil.getCellContentsAsString(cellToClone);
newCell.setCellStyle(cellToClone.getCellStyle());
try {
newCell.setCellFormula(cellToClone.getCellFormula());
} catch (Exception e) {
// Do nothing here. I don't know why POI throw exceptions here when the cell is not a
// formula, but this suppresses them...
}
int numFormattings =
sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
for (int n = 0; n < numFormattings; n++) {
ConditionalFormatting f =
sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
for (CellRangeAddress add : f.getFormattingRanges()) {
if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
&& add.getLastRow() == rowToClone.getRowNum()) {
if (add.getFirstColumn() == cellToClone.getColumnIndex()
&& add.getLastColumn() == cellToClone.getColumnIndex()) {
ConditionalFormattingRule[] rules =
new ConditionalFormattingRule[f.getNumberOfRules()];
for (int j = 0; j < f.getNumberOfRules(); j++) {
rules[j] = f.getRule(j);
}
CellRangeAddress[] cellRange = new CellRangeAddress[1];
cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
sheetToAdd
.getSheet()
.getSheetConditionalFormatting()
.addConditionalFormatting(cellRange, rules);
}
}
}
}
if (ObjectUtil.notNull(contents)) {
Object newContents =
EvaluationUtil.evaluateExpression(
contents, cellToAdd.getReplacementData(), prefix, suffix);
ExcelUtil.setCellContents(styleHelper, newCell, newContents);
}
}
}
return newRow;
}
