/** Writes out the data validations */
private void writeDataValidation() throws IOException {
if (dataValidation != null && validatedCells.size() == 0) {
// only data validations are those read in
dataValidation.write(outputFile);
return;
}
if (dataValidation == null && validatedCells.size() > 0) {
int comboBoxId =
sheet.getComboBox() != null
? sheet.getComboBox().getObjectId()
: DataValidation.DEFAULT_OBJECT_ID;
dataValidation =
new DataValidation(
comboBoxId, sheet.getWorkbook(), sheet.getWorkbook(), workbookSettings);
for (Iterator i = validatedCells.iterator(); i.hasNext(); ) {
CellValue cv = (CellValue) i.next();
CellFeatures cf = cv.getCellFeatures();
DataValiditySettingsRecord dvsr = new DataValiditySettingsRecord(cf.getDVParser());
dataValidation.add(dvsr);
}
dataValidation.write(outputFile);
return;
}
// Read and write validations
for (Iterator i = validatedCells.iterator(); i.hasNext(); ) {
CellValue cv = (CellValue) i.next();
CellFeatures cf = cv.getCellFeatures();
DataValiditySettingsRecord dvsr = new DataValiditySettingsRecord(cf.getDVParser());
dataValidation.add(dvsr);
}
dataValidation.write(outputFile);
return;
}
/**
* Check all the merged cells for borders. If the merge record has borders, then we need to rejig
* the cell formats to take account of this. This is called by the write method of the
* WritableWorkbookImpl, so that any new XFRecords that are created may be written out with the
* others
*/
void checkMergedBorders() {
Range[] mcells = mergedCells.getMergedCells();
ArrayList borderFormats = new ArrayList();
for (int mci = 0; mci < mcells.length; mci++) {
Range range = mcells[mci];
Cell topLeft = range.getTopLeft();
XFRecord tlformat = (XFRecord) topLeft.getCellFormat();
if (tlformat != null && tlformat.hasBorders() == true && !tlformat.isRead()) {
try {
CellXFRecord cf1 = new CellXFRecord(tlformat);
Cell bottomRight = range.getBottomRight();
cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf1.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf1.setBorder(
Border.TOP, tlformat.getBorderLine(Border.TOP), tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf1.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf1.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
int index = borderFormats.indexOf(cf1);
if (index != -1) {
cf1 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf1);
}
((WritableCell) topLeft).setCellFormat(cf1);
// Handle the bottom left corner
if (bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
if (bottomRight.getColumn() != topLeft.getColumn()) {
CellXFRecord cf2 = new CellXFRecord(tlformat);
cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf2.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf2.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
index = borderFormats.indexOf(cf2);
if (index != -1) {
cf2 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf2);
}
sheet.addCell(new Blank(topLeft.getColumn(), bottomRight.getRow(), cf2));
}
// Handle the cells down the left hand side (and along the
// right too, if necessary)
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf3 = new CellXFRecord(tlformat);
cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf3.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf3.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
index = borderFormats.indexOf(cf3);
if (index != -1) {
cf3 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf3);
}
sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
}
}
// Handle the top right corner
if (bottomRight.getColumn() > topLeft.getColumn()) {
if (bottomRight.getRow() != topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf6 = new CellXFRecord(tlformat);
cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf6.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf6.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
index = borderFormats.indexOf(cf6);
if (index != -1) {
cf6 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf6);
}
sheet.addCell(new Blank(bottomRight.getColumn(), topLeft.getRow(), cf6));
}
// Handle the cells along the right
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf7 = new CellXFRecord(tlformat);
cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf7.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
index = borderFormats.indexOf(cf7);
if (index != -1) {
cf7 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf7);
}
sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
}
// Handle the cells along the top, and along the bottom too
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf8 = new CellXFRecord(tlformat);
cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf8.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf8.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
index = borderFormats.indexOf(cf8);
if (index != -1) {
cf8 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf8);
}
sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
}
}
// Handle the bottom right corner
if (bottomRight.getColumn() > topLeft.getColumn()
|| bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf4 = new CellXFRecord(tlformat);
cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf4.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf4.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (bottomRight.getRow() == topLeft.getRow()) {
cf4.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
if (bottomRight.getColumn() == topLeft.getColumn()) {
cf4.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
}
index = borderFormats.indexOf(cf4);
if (index != -1) {
cf4 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf4);
}
sheet.addCell(new Blank(bottomRight.getColumn(), bottomRight.getRow(), cf4));
// Handle the cells along the bottom (and along the top
// as well, if appropriate)
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf5 = new CellXFRecord(tlformat);
cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf5.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (topLeft.getRow() == bottomRight.getRow()) {
cf5.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
index = borderFormats.indexOf(cf5);
if (index != -1) {
cf5 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf5);
}
sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
}
}
} catch (WriteException e) {
// just log e.toString(), not the whole stack trace
logger.warn(e.toString());
}
}
}
}
/**
* Writes out this sheet. First writes out the standard sheet information then writes out each row
* in turn. Once all the rows have been written out, it retrospectively adjusts the offset
* references in the file
*
* @exception IOException
*/
public void write() throws IOException {
Assert.verify(rows != null);
// This worksheet consists of just one chart, so write it and return
if (chartOnly) {
drawingWriter.write(outputFile);
return;
}
BOFRecord bof = new BOFRecord(BOFRecord.sheet);
outputFile.write(bof);
// Compute the number of blocks of 32 rows that will be needed
int numBlocks = numRows / 32;
if (numRows - numBlocks * 32 != 0) {
numBlocks++;
}
int indexPos = outputFile.getPos();
// Write the index record out now in order to serve as a place holder
// The bof passed in is the bof of the workbook, not this sheet
IndexRecord indexRecord = new IndexRecord(0, numRows, numBlocks);
outputFile.write(indexRecord);
if (settings.getAutomaticFormulaCalculation()) {
CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.automatic);
outputFile.write(cmr);
} else {
CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.manual);
outputFile.write(cmr);
}
CalcCountRecord ccr = new CalcCountRecord(0x64);
outputFile.write(ccr);
RefModeRecord rmr = new RefModeRecord();
outputFile.write(rmr);
IterationRecord itr = new IterationRecord(false);
outputFile.write(itr);
DeltaRecord dtr = new DeltaRecord(0.001);
outputFile.write(dtr);
SaveRecalcRecord srr = new SaveRecalcRecord(settings.getRecalculateFormulasBeforeSave());
outputFile.write(srr);
PrintHeadersRecord phr = new PrintHeadersRecord(settings.getPrintHeaders());
outputFile.write(phr);
PrintGridLinesRecord pglr = new PrintGridLinesRecord(settings.getPrintGridLines());
outputFile.write(pglr);
GridSetRecord gsr = new GridSetRecord(true);
outputFile.write(gsr);
GuttersRecord gutr = new GuttersRecord();
gutr.setMaxColumnOutline(maxColumnOutlineLevel + 1);
gutr.setMaxRowOutline(maxRowOutlineLevel + 1);
outputFile.write(gutr);
DefaultRowHeightRecord drhr =
new DefaultRowHeightRecord(
settings.getDefaultRowHeight(),
settings.getDefaultRowHeight() != SheetSettings.DEFAULT_DEFAULT_ROW_HEIGHT);
outputFile.write(drhr);
if (maxRowOutlineLevel > 0) {
workspaceOptions.setRowOutlines(true);
}
if (maxColumnOutlineLevel > 0) {
workspaceOptions.setColumnOutlines(true);
}
workspaceOptions.setFitToPages(settings.getFitToPages());
outputFile.write(workspaceOptions);
if (rowBreaks.size() > 0) {
int[] rb = new int[rowBreaks.size()];
for (int i = 0; i < rb.length; i++) {
rb[i] = ((Integer) rowBreaks.get(i)).intValue();
}
HorizontalPageBreaksRecord hpbr = new HorizontalPageBreaksRecord(rb);
outputFile.write(hpbr);
}
if (columnBreaks.size() > 0) {
int[] rb = new int[columnBreaks.size()];
for (int i = 0; i < rb.length; i++) {
rb[i] = ((Integer) columnBreaks.get(i)).intValue();
}
VerticalPageBreaksRecord hpbr = new VerticalPageBreaksRecord(rb);
outputFile.write(hpbr);
}
HeaderRecord header = new HeaderRecord(settings.getHeader().toString());
outputFile.write(header);
FooterRecord footer = new FooterRecord(settings.getFooter().toString());
outputFile.write(footer);
HorizontalCentreRecord hcr = new HorizontalCentreRecord(settings.isHorizontalCentre());
outputFile.write(hcr);
VerticalCentreRecord vcr = new VerticalCentreRecord(settings.isVerticalCentre());
outputFile.write(vcr);
// Write out the margins if they don't equal the default
if (settings.getLeftMargin() != settings.getDefaultWidthMargin()) {
MarginRecord mr = new LeftMarginRecord(settings.getLeftMargin());
outputFile.write(mr);
}
if (settings.getRightMargin() != settings.getDefaultWidthMargin()) {
MarginRecord mr = new RightMarginRecord(settings.getRightMargin());
outputFile.write(mr);
}
if (settings.getTopMargin() != settings.getDefaultHeightMargin()) {
MarginRecord mr = new TopMarginRecord(settings.getTopMargin());
outputFile.write(mr);
}
if (settings.getBottomMargin() != settings.getDefaultHeightMargin()) {
MarginRecord mr = new BottomMarginRecord(settings.getBottomMargin());
outputFile.write(mr);
}
if (plsRecord != null) {
outputFile.write(plsRecord);
}
SetupRecord setup = new SetupRecord(settings);
outputFile.write(setup);
if (settings.isProtected()) {
ProtectRecord pr = new ProtectRecord(settings.isProtected());
outputFile.write(pr);
ScenarioProtectRecord spr = new ScenarioProtectRecord(settings.isProtected());
outputFile.write(spr);
ObjectProtectRecord opr = new ObjectProtectRecord(settings.isProtected());
outputFile.write(opr);
if (settings.getPassword() != null) {
PasswordRecord pw = new PasswordRecord(settings.getPassword());
outputFile.write(pw);
} else if (settings.getPasswordHash() != 0) {
PasswordRecord pw = new PasswordRecord(settings.getPasswordHash());
outputFile.write(pw);
}
}
indexRecord.setDataStartPosition(outputFile.getPos());
DefaultColumnWidth dcw = new DefaultColumnWidth(settings.getDefaultColumnWidth());
outputFile.write(dcw);
// Get a handle to the normal styles
WritableCellFormat normalStyle = sheet.getWorkbook().getStyles().getNormalStyle();
WritableCellFormat defaultDateFormat = sheet.getWorkbook().getStyles().getDefaultDateFormat();
// Write out all the column formats
ColumnInfoRecord cir = null;
for (Iterator colit = columnFormats.iterator(); colit.hasNext(); ) {
cir = (ColumnInfoRecord) colit.next();
// Writing out the column info with index 0x100 causes excel to crash
if (cir.getColumn() < 0x100) {
outputFile.write(cir);
}
XFRecord xfr = cir.getCellFormat();
if (xfr != normalStyle && cir.getColumn() < 0x100) {
// Make this the format for every cell in the column
Cell[] cells = getColumn(cir.getColumn());
for (int i = 0; i < cells.length; i++) {
if (cells[i] != null
&& (cells[i].getCellFormat() == normalStyle
|| cells[i].getCellFormat() == defaultDateFormat)) {
// The cell has no overriding format specified, so
// set it to the column default
((WritableCell) cells[i]).setCellFormat(xfr);
}
}
}
}
// Write out the auto filter
if (autoFilter != null) {
autoFilter.write(outputFile);
}
DimensionRecord dr = new DimensionRecord(numRows, numCols);
outputFile.write(dr);
// Write out all the rows, in blocks of 32
for (int block = 0; block < numBlocks; block++) {
DBCellRecord dbcell = new DBCellRecord(outputFile.getPos());
int blockRows = Math.min(32, numRows - block * 32);
boolean firstRow = true;
// First write out all the row records
for (int i = block * 32; i < block * 32 + blockRows; i++) {
if (rows[i] != null) {
rows[i].write(outputFile);
if (firstRow) {
dbcell.setCellOffset(outputFile.getPos());
firstRow = false;
}
}
}
// Now write out all the cells
for (int i = block * 32; i < block * 32 + blockRows; i++) {
if (rows[i] != null) {
dbcell.addCellRowPosition(outputFile.getPos());
rows[i].writeCells(outputFile);
}
}
// Now set the current file position in the index record
indexRecord.addBlockPosition(outputFile.getPos());
// Set the position of the file pointer and write out the DBCell
// record
dbcell.setPosition(outputFile.getPos());
outputFile.write(dbcell);
}
// Do the drawings and charts if enabled
if (!workbookSettings.getDrawingsDisabled()) {
drawingWriter.write(outputFile);
}
Window2Record w2r = new Window2Record(settings);
outputFile.write(w2r);
// Handle the frozen panes
if (settings.getHorizontalFreeze() != 0 || settings.getVerticalFreeze() != 0) {
PaneRecord pr = new PaneRecord(settings.getHorizontalFreeze(), settings.getVerticalFreeze());
outputFile.write(pr);
// Handle the selection record. First, there will always be a top left
SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
outputFile.write(sr);
// Top right
if (settings.getHorizontalFreeze() != 0) {
sr = new SelectionRecord(SelectionRecord.upperRight, settings.getHorizontalFreeze(), 0);
outputFile.write(sr);
}
// Bottom left
if (settings.getVerticalFreeze() != 0) {
sr = new SelectionRecord(SelectionRecord.lowerLeft, 0, settings.getVerticalFreeze());
outputFile.write(sr);
}
// Bottom right
if (settings.getHorizontalFreeze() != 0 && settings.getVerticalFreeze() != 0) {
sr =
new SelectionRecord(
SelectionRecord.lowerRight,
settings.getHorizontalFreeze(),
settings.getVerticalFreeze());
outputFile.write(sr);
}
Weird1Record w1r = new Weird1Record();
outputFile.write(w1r);
} else {
// No frozen panes - just write out the selection record for the
// whole sheet
SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
outputFile.write(sr);
}
// Handle the zoom factor
if (settings.getZoomFactor() != 100) {
SCLRecord sclr = new SCLRecord(settings.getZoomFactor());
outputFile.write(sclr);
}
// Now write out all the merged cells
mergedCells.write(outputFile);
// Write out all the hyperlinks
Iterator hi = hyperlinks.iterator();
WritableHyperlink hlr = null;
while (hi.hasNext()) {
hlr = (WritableHyperlink) hi.next();
outputFile.write(hlr);
}
if (buttonPropertySet != null) {
outputFile.write(buttonPropertySet);
}
// Write out the data validations
if (dataValidation != null || validatedCells.size() > 0) {
writeDataValidation();
}
// Write out the conditional formats
if (conditionalFormats != null && conditionalFormats.size() > 0) {
for (Iterator i = conditionalFormats.iterator(); i.hasNext(); ) {
ConditionalFormat cf = (ConditionalFormat) i.next();
cf.write(outputFile);
}
}
EOFRecord eof = new EOFRecord();
outputFile.write(eof);
// Now the various cross reference offsets have been calculated,
// retrospectively set the values in the output file
outputFile.setData(indexRecord.getData(), indexPos + 4);
}