private void initHyperlinkStyle()
{
lock (this)
{
hyperlinkStyle = new WritableCellFormat(getHyperlinkFont(), NumberFormats.DEFAULT);
}
}
private void initDefaultDateFormat()
{
lock (this)
{
defaultDateFormat = new WritableCellFormat(DateFormats.DEFAULT);
}
}
/**
* Returns an initialized copy of the cell format
*
* @param cf the cell format to copy
* @return a deep copy of the cell format
*/
private WritableCellFormat copyCellFormat(CellFormat cf)
{
try
{
// just do a deep copy of the cell format for now. This will create
// a copy of the format and font also - in the future this may
// need to be sorted out
XFRecord xfr = (XFRecord)cf;
WritableCellFormat f = new WritableCellFormat(xfr);
formatRecords.addStyle(f);
// Maintain the local list of formats
int xfIndex = xfr.getXFIndex();
xfRecords.Add(xfIndex, f);
int fontIndex = xfr.getFontIndex();
fonts.Add(fontIndex, f.getFontIndex());
int formatIndex = xfr.getFormatRecord();
formats.Add(formatIndex, f.getFormatRecord());
return(f);
}
catch (NumFormatRecordsException e)
{
//logger.warn("Maximum number of format records exceeded. Using default format.");
return(WritableWorkbook.NORMAL_STYLE);
}
}
private void initHiddenStyle()
{
lock (this)
{
hiddenStyle = new WritableCellFormat(getArial10Pt(), new DateFormat(";;;"));
}
}
/**
* Performs a deep copy of the specified cell, handling the cell format
*
* @param cell the cell to copy
*/
private WritableCell deepCopyCell(Cell cell)
{
WritableCell c = shallowCopyCell(cell);
if (c == null)
{
return(c);
}
if (c is ReadFormulaRecord)
{
ReadFormulaRecord rfr = (ReadFormulaRecord)c;
bool crossSheetReference = !rfr.handleImportedCellReferences
(fromSheet.getWorkbook(),
fromSheet.getWorkbook(),
workbookSettings);
if (crossSheetReference)
{
try
{
//logger.warn("Formula " + rfr.getFormula() +
// " in cell " +
// CellReferenceHelper.getCellReference(cell.getColumn(),
// cell.getRow()) +
// " cannot be imported because it references another " +
// " sheet from the source workbook");
}
catch (FormulaException e)
{
//logger.warn("Formula in cell " +
// CellReferenceHelper.getCellReference(cell.getColumn(),
// cell.getRow()) +
// " cannot be imported: " + e.Message);
}
// Create a new error formula and add it instead
c = new Formula(cell.getColumn(), cell.getRow(), "\"ERROR\"");
}
}
// Copy the cell format
CellFormat cf = c.getCellFormat();
int index = ((XFRecord)cf).getXFIndex();
WritableCellFormat wcf = null;
if (!xfRecords.ContainsKey(index))
{
wcf = copyCellFormat(cf);
}
else
{
wcf = xfRecords[index];
}
c.setCellFormat(wcf);
return(c);
}
private void initNormalStyle()
{
lock (this)
{
normalStyle = new WritableCellFormat(getArial10Pt(), NumberFormats.DEFAULT);
normalStyle.setFont(getArial10Pt());
}
}
/**
* Constructor
*/
public Styles()
{
arial10pt = null;
hyperlinkFont = null;
normalStyle = null;
hyperlinkStyle = null;
hiddenStyle = null;
}
/// <summary>
/// add Cell
/// </summary>
/// <param name="writableSheet"> WritableSheet </param>
/// <param name="usedCells"> 已使用區域紀錄物件 </param>
/// <param name="colIndex"> column index </param>
/// <param name="rowIndex"> row index </param>
/// <param name="content"> 文字內容 </param>
/// <param name="cellFormat"> cell 格式設定物件 </param>
/// <param name="maxWidthMap"> 最大欄寬紀錄物件 </param>
/// <param name="width"> 欄寬 </param>
private void addCell(
WritableSheet writableSheet,
Dictionary <int, HashSet <string> > usedCells,
int colIndex,
int rowIndex,
string content,
WritableCellFormat cellFormat,
Dictionary <string, int> maxWidthMap,
int width)
{
// System.out.println(key + ": [" + rowIndex + "," + colIndex + "],[" + content + "]");
// 新增 cell
writableSheet.addCell(new Label(colIndex, rowIndex, content, cellFormat));
// 記錄使用區域
setUsed(usedCells, rowIndex, colIndex);
// 記錄欄寬
setMaxWidth(maxWidthMap, width, colIndex);
}
private WritableCellFormat GetWritableFormat(TextStyle ts)
{
WritableCellFormat cellFormat = new WritableCellFormat();
WritableFont font = new WritableFont(
new WritableFont.FontName(ts.FontFamily),
(int)ts.FontSize.points * _fontHeight,
BoldStyle(ts),
(ts.FontStyle == Engine.Style.FontStyleEnum.Italic),
(ts.TextDecoration == Engine.Style.TextDecorationEnum.Underline) ? UnderlineStyle.SINGLE : UnderlineStyle.NO_UNDERLINE,
(ts.Color == null) ? Colour.BLACK : getXlsColor(ts.Color, Colour.BLACK),
ScriptStyle.NORMAL_SCRIPT);
cellFormat.setFont(font);
cellFormat.setAlignment(ExcelAlignmentFromRdlAlignment(ts.TextAlign));
if (ts.BorderStyle != null && ts.BorderStyle != null)
{
BorderStyle bs = ts.BorderStyle;
BorderColor bc = ts.BorderColor;
cellFormat.setBorder(Border.TOP, ExcelBorderStyleFromRdlBorderStyle(bs.Top),
(bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Top, Colour.BLACK));
cellFormat.setBorder(Border.BOTTOM, ExcelBorderStyleFromRdlBorderStyle(bs.Bottom),
(bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Bottom, Colour.BLACK));
cellFormat.setBorder(Border.LEFT, ExcelBorderStyleFromRdlBorderStyle(bs.Left),
(bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Left, Colour.BLACK));
cellFormat.setBorder(Border.RIGHT, ExcelBorderStyleFromRdlBorderStyle(bs.Right),
(bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Right, Colour.BLACK));
}
if (ts.BackgroundColor != null)
{
cellFormat.setBackground(getXlsColor(ts.BackgroundColor, Colour.WHITE), Pattern.SOLID);
}
return(cellFormat);
}
/// <summary>
/// 設定 Cell 格式 (Detal時, 沒有 tr 和 td, 此時兩個參數傳入同一個物件, 不影響判斷)
/// </summary>
/// <param name="trInfo" />
/// <param name="tdInfo" />
private WritableCellFormat getCellFormat(AbstractStyleInfo trInfo, AbstractStyleInfo tdInfo)
{
//style 設定
StyleInfo styleInfo = ((ExportConfigInfo)configInfo).StyleInfo;
// 字體名稱
WritableFont.FontName font = styleInfo.Font;
if (tdInfo.Font != null)
{
font = tdInfo.Font;
}
else if (trInfo.Font != null)
{
font = trInfo.Font;
}
// 字體大小
int size = 0;
if (ExcelStringUtil.NotEmpty(tdInfo.Size))
{
size = Convert.ToInt32(ExcelStringUtil.SafeTrim(tdInfo.Size, "0"));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Size))
{
size = Convert.ToInt32(ExcelStringUtil.SafeTrim(trInfo.Size, "0"));
}
if (size == 0)
{
size = Convert.ToInt32(styleInfo.Size);
}
// 粗體
bool isBold = ("true".Equals(styleInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Bold))
{
isBold = ("true".Equals(tdInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
{
isBold = ("true".Equals(trInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
}
// 斜體
bool isItalic = ("true".Equals(styleInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Italic))
{
isItalic = ("true".Equals(tdInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
{
isItalic = ("true".Equals(trInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
}
// 底線
UnderlineStyle underlineStyle = styleInfo.Underline;
if (tdInfo.Underline != null)
{
underlineStyle = tdInfo.Underline;
}
else if (trInfo.Underline != null)
{
underlineStyle = trInfo.Underline;
}
// 字體顏色
Colour color = styleInfo.Color;
if (tdInfo.Color != null)
{
color = tdInfo.Color;
}
else if (trInfo.Color != null)
{
color = trInfo.Color;
}
// 水平位置
Alignment align = styleInfo.Align;
if (tdInfo.Align != null)
{
align = tdInfo.Align;
}
else if (trInfo.Align != null)
{
align = trInfo.Align;
}
// 垂直位置
VerticalAlignment valign = styleInfo.Valign;
if (tdInfo.Valign != null)
{
valign = tdInfo.Valign;
}
else if (trInfo.Valign != null)
{
valign = trInfo.Valign;
}
// 文字換行
bool isTextWrap = ("true".Equals(styleInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
if (ExcelStringUtil.NotEmpty(tdInfo.Wrap))
{
isTextWrap = ("true".Equals(tdInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
}
else if (ExcelStringUtil.NotEmpty(trInfo.Wrap))
{
isTextWrap = ("true".Equals(trInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
}
// 邊線位置
Border borderSide = styleInfo.BorderSide;
if (tdInfo.BorderSide != null)
{
borderSide = tdInfo.BorderSide;
}
else if (trInfo.BorderSide != null)
{
borderSide = trInfo.BorderSide;
}
// 邊線樣式
BorderLineStyle borderStyle = styleInfo.BorderStyle;
if (tdInfo.BorderStyle != null)
{
borderStyle = tdInfo.BorderStyle;
}
else if (trInfo.Valign != null)
{
borderStyle = trInfo.BorderStyle;
}
// 背景顏色
Colour background = styleInfo.Background;
if (tdInfo.Background != null)
{
background = tdInfo.Background;
}
else if (trInfo.Background != null)
{
background = trInfo.Background;
}
// 產生字型設定
var writableFont = new WritableFont(font, size, (isBold) ? WritableFont.BOLD : WritableFont.NO_BOLD,
isItalic, underlineStyle, color);
// 資料列cell格式
var writableCellFormat = new WritableCellFormat(writableFont);
// 水平置中
writableCellFormat.setAlignment(align);
// 垂直置中
writableCellFormat.setVerticalAlignment(valign);
// 換行
writableCellFormat.setWrap(isTextWrap);
// 背景顏色
writableCellFormat.setBackground(background);
// 邊線
writableCellFormat.setBorder(borderSide, borderStyle);
return(writableCellFormat);
}
private int WriteContext(
WritableSheet writableSheet,
ContextInfo contextInfo,
int targetRowIndex,
Dictionary <string, object> dataMap,
Dictionary <int, HashSet <string> > usedCells,
Dictionary <string, int> maxWidthMap)
{
// 無資料時跳出
if (contextInfo.TrInfoList == null)
{
return(targetRowIndex);
}
// 逐列處理
for (int row = 0; row < contextInfo.TrInfoList.Count; row++)
{
//取得 TrInfo
TrInfo trInfo = contextInfo.TrInfoList[row];
// col index 指標
int targetColIndex = 0;
for (int col = 0; col < trInfo.TdInfoList.Count; col++)
{
// 取得 TdInfo
TdInfo tdInfo = trInfo.TdInfoList[col];
// 取得欄位設定
WritableCellFormat cellFormat = getCellFormat(trInfo, tdInfo);
// 取得要放入 cell 的值
string content = perpareContent(tdInfo.Key, tdInfo.DefaultValue, tdInfo.FuncId, tdInfo.FuncParam,
dataMap);
// 取得寬度設定
int width = Convert.ToInt32(ExcelStringUtil.SafeTrim(tdInfo.Width, "0"));
// 取得還未使用的 column
targetColIndex = getUnUsedCol(usedCells, targetRowIndex, targetColIndex);
if (tdInfo.Colspan > 1 || tdInfo.Rowspan > 1)
{
// 合併儲存格
merageCell(writableSheet, usedCells, targetColIndex, targetRowIndex, tdInfo.Colspan,
tdInfo.Rowspan, maxWidthMap, width);
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, tdInfo.Key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat,
maxWidthMap, width);
// 移動 col 指標
if (tdInfo.Colspan > 0)
{
targetColIndex += tdInfo.Colspan;
}
else
{
targetColIndex++;
}
}
else
{
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, tdInfo.Key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat,
maxWidthMap, width);
// 移動 col 指標
targetColIndex++;
}
}
// 取得列高設定
//height of the row in 1/20ths of a point
int height = Convert.ToInt32(ExcelStringUtil.SafeTrim(trInfo.Height, "0")) * 20;
if (height > 0)
{
writableSheet.setRowView(targetRowIndex, height);
}
targetRowIndex++;
}
return(targetRowIndex);
}
private int writeDetail(WritableSheet writableSheet, IEnumerable <ColumnInfo> columnInfoList,
ColumnDataSet columnDataSet, int targetRowIndex, Dictionary <int, HashSet <string> > usedCells,
Dictionary <string, int> maxWidthMap)
{
int targetColIndex = 0;
int newTargetRowIndex = targetRowIndex;
foreach (ColumnInfo columnInfo in columnInfoList)
{
// 取得子欄位
List <ColumnDetailInfo> columnDetailInfoList = columnInfo.ColumnDetailInfoList;
// 為子欄位陣列時，進行遞迴處理
if (ExcelStringUtil.NotEmpty(columnDetailInfoList))
{
foreach (ColumnDetailInfo columnDetailInfo in columnDetailInfoList)
{
// 設定元素類別
string type = columnDetailInfo.Type;
// dataId
string dataId = columnDetailInfo.DataId;
// 欄位下的欄位
List <ColumnInfo> childColumnInfoList = columnDetailInfo.ColumnInfoList;
// ELEMENT_SINGLE
if (string.Equals(type, Constant.ELEMENT_SINGLE, StringComparison.OrdinalIgnoreCase))
{
// 遞迴處理
newTargetRowIndex = writeDetail(
writableSheet, childColumnInfoList,
columnDataSet.getSingle(dataId),
newTargetRowIndex, usedCells, maxWidthMap);
}
if (string.Equals(type, Constant.ELEMENT_ARRAY, StringComparison.OrdinalIgnoreCase))
{
// 取得 array 元素的資料集
List <ColumnDataSet> arrayDataList = columnDataSet.getArray(dataId);
// 逐筆處理
foreach (ColumnDataSet arrayColumnDataSet in arrayDataList)
{
// 遞迴處理
newTargetRowIndex = writeDetail(writableSheet, childColumnInfoList, arrayColumnDataSet,
newTargetRowIndex, usedCells, maxWidthMap);
}
}
}
continue;
}
// 取得 key
string key = columnInfo.Key;
// 取得欄位設定
WritableCellFormat cellFormat = getCellFormat(columnInfo, columnInfo);
// 取得要放入 cell 的值
string content = perpareContent(key, columnInfo.DefaultValue, columnInfo.FuncId, columnInfo.FuncParam,
columnDataSet.ColumnDataMap);
// 取得寬度設定
int width = Convert.ToInt32(ExcelStringUtil.SafeTrim(columnInfo.Width, "0"));
// 取得還未使用的 column
targetColIndex = getUnUsedCol(usedCells, targetRowIndex, targetColIndex);
// 取得 rowspan (之前已計算好)
if (!columnDataSet.ColumnDataMap.ContainsKey(KEY_COLUMN_COLSPAN_PERFIX + key))
{
throw new Exception("指定的索引鍵不在字典中 [" + key + "]");
}
var rowspan = (int)columnDataSet.ColumnDataMap[KEY_COLUMN_COLSPAN_PERFIX + key];
// colspan
int colspan = columnInfo.Colspan;
if (colspan > 1 || rowspan > 1)
{
// 合併儲存格
merageCell(writableSheet, usedCells, targetColIndex, targetRowIndex, colspan, rowspan, maxWidthMap,
width);
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap,
width);
// 移動 col 指標
if (colspan > 0)
{
targetColIndex += colspan;
}
else
{
targetColIndex++;
}
}
else
{
// addCell
//addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap, width, key);
addCell(writableSheet, usedCells, targetColIndex, targetRowIndex, content, cellFormat, maxWidthMap,
width);
// 移動 col 指標
targetColIndex++;
}
}
targetRowIndex++;
// newTargetRowIndex++;
return((targetRowIndex > newTargetRowIndex) ? targetRowIndex : newTargetRowIndex);
}
/**
* If the inputFile was the test spreadsheet, then it modifies certain fields
* of the writable copy
*
* @param w
*/
private void modify(WritableWorkbook w)
{
Console.WriteLine("Modifying...");
WritableSheet sheet = w.getSheet("modified");
WritableCell cell = null;
CellFormat cf = null;
Label l = null;
WritableCellFeatures wcf = null;
// Change the format of cell B4 to be emboldened
cell = sheet.getWritableCell(1, 3);
WritableFont bold = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.BOLD);
cf = new WritableCellFormat(bold);
cell.setCellFormat(cf);
// Change the format of cell B5 to be underlined
cell = sheet.getWritableCell(1, 4);
WritableFont underline = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
cf = new WritableCellFormat(underline);
cell.setCellFormat(cf);
// Change the point size of cell B6 to be 10 point
cell = sheet.getWritableCell(1, 5);
WritableFont tenpoint = new WritableFont(WritableFont.ARIAL, 10);
cf = new WritableCellFormat(tenpoint);
cell.setCellFormat(cf);
// Change the contents of cell B7 to read "Label - mod"
cell = sheet.getWritableCell(1, 6);
if (cell.getType() == CellType.LABEL)
{
Label lc = (Label)cell;
lc.setString(lc.getString() + " - mod");
}
// Change cell B10 to display 7 dps
cell = sheet.getWritableCell(1, 9);
NumberFormat sevendps = new NumberFormat("#.0000000");
cf = new WritableCellFormat(sevendps);
cell.setCellFormat(cf);
// Change cell B11 to display in the format 1e4
cell = sheet.getWritableCell(1, 10);
NumberFormat exp4 = new NumberFormat("0.####E0");
cf = new WritableCellFormat(exp4);
cell.setCellFormat(cf);
// Change cell B12 to be normal display
cell = sheet.getWritableCell(1, 11);
cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
// Change the contents of cell B13 to 42
cell = sheet.getWritableCell(1, 12);
if (cell.getType() == CellType.NUMBER)
{
Number n2 = (Number)cell;
n2.setValue(42);
}
// Add 0.1 to the contents of cell B14
cell = sheet.getWritableCell(1, 13);
if (cell.getType() == CellType.NUMBER)
{
Number n3 = (Number)cell;
n3.setValue(n3.getValue() + 0.1);
}
// Change the date format of cell B17 to be a custom format
cell = sheet.getWritableCell(1, 16);
DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
cf = new WritableCellFormat(df);
cell.setCellFormat(cf);
// Change the date format of cell B18 to be a standard format
cell = sheet.getWritableCell(1, 17);
cf = new WritableCellFormat(DateFormats.FORMAT9);
cell.setCellFormat(cf);
// Change the date in cell B19 to be 18 Feb 1998, 11:23:28
cell = sheet.getWritableCell(1, 18);
if (cell.getType() == CellType.DATE)
{
// TODO: fix this....
//DateTime dt = (DateTime)cell;
//Calendar cal = Calendar.getInstance();
//cal.set(1998, 1, 18, 11, 23, 28);
//Date d = cal.getTime();
//dt.setDate(d);
}
// Change the value in B23 to be 6.8. This should recalculate the
// formula
cell = sheet.getWritableCell(1, 22);
if (cell.getType() == CellType.NUMBER)
{
Number n1 = (Number)cell;
n1.setValue(6.8);
}
// Change the label in B30. This will have the effect of making
// the original string unreferenced
cell = sheet.getWritableCell(1, 29);
if (cell.getType() == CellType.LABEL)
{
l = (Label)cell;
l.setString("Modified string contents");
}
// Insert a new row (number 35)
sheet.insertRow(34);
// Delete row 38 (39 after row has been inserted)
sheet.removeRow(38);
// Insert a new column (J)
sheet.insertColumn(9);
// Remove a column (L - M after column has been inserted)
sheet.removeColumn(11);
// Remove row 44 (contains a hyperlink), and then insert an empty
// row just to keep the numbers consistent
sheet.removeRow(43);
sheet.insertRow(43);
// Modify the hyperlinks
WritableHyperlink[] hyperlinks = sheet.getWritableHyperlinks();
for (int i = 0; i < hyperlinks.Length; i++)
{
WritableHyperlink wh = hyperlinks[i];
if (wh.getColumn() == 1 && wh.getRow() == 39)
{
try
{
// Change the hyperlink that begins in cell B40 to be a different API
wh.setURL(new Uri("http://www.andykhan.com/jexcelapi/index.html"));
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
else if (wh.getColumn() == 1 && wh.getRow() == 40)
{
wh.setFile(new FileInfo("../jexcelapi/docs/overview-summary.html"));
}
else if (wh.getColumn() == 1 && wh.getRow() == 41)
{
wh.setFile(new FileInfo("d:/home/jexcelapi/docs/jxl/package-summary.html"));
}
else if (wh.getColumn() == 1 && wh.getRow() == 44)
{
// Remove the hyperlink at B45
sheet.removeHyperlink(wh);
}
}
// Change the background of cell F31 from blue to red
WritableCell c = sheet.getWritableCell(5, 30);
WritableCellFormat newFormat = new WritableCellFormat(c.getCellFormat());
newFormat.setBackground(Colour.RED);
c.setCellFormat(newFormat);
// Modify the contents of the merged cell
l = new Label(0, 49, "Modified merged cells");
sheet.addCell(l);
// Modify the chart data
Number n = (Number)sheet.getWritableCell(0, 70);
n.setValue(9);
n = (Number)sheet.getWritableCell(0, 71);
n.setValue(10);
n = (Number)sheet.getWritableCell(0, 73);
n.setValue(4);
// Add in a cross sheet formula
Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");
sheet.addCell(f);
// Add in a formula from the named cells
f = new Formula(1, 83, "value1+value2");
sheet.addCell(f);
// Add in a function formula using named cells
f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
sheet.addCell(f);
// Copy sheet 1 to sheet 3
// w.copySheet(0, "copy", 2);
// Use the cell deep copy feature
Label label = new Label(0, 88, "Some copied cells", cf);
sheet.addCell(label);
label = new Label(0, 89, "Number from B9");
sheet.addCell(label);
WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1, 89);
sheet.addCell(wc);
label = new Label(0, 90, "Label from B4 (modified format)");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 3).copyTo(1, 90);
sheet.addCell(wc);
label = new Label(0, 91, "Date from B17");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 16).copyTo(1, 91);
sheet.addCell(wc);
label = new Label(0, 92, "Boolean from E16");
sheet.addCell(label);
wc = sheet.getWritableCell(4, 15).copyTo(1, 92);
sheet.addCell(wc);
label = new Label(0, 93, "URL from B40");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 39).copyTo(1, 93);
sheet.addCell(wc);
// Add some numbers for the formula copy
for (int i = 0; i < 6; i++)
{
Number number = new Number(1, 94 + i, i + 1 + i / 8.0);
sheet.addCell(number);
}
label = new Label(0, 100, "Formula from B27");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 26).copyTo(1, 100);
sheet.addCell(wc);
label = new Label(0, 101, "A brand new formula");
sheet.addCell(label);
Formula formula = new Formula(1, 101, "SUM(B94:B96)");
sheet.addCell(formula);
label = new Label(0, 102, "A copy of it");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 101).copyTo(1, 102);
sheet.addCell(wc);
// Remove the second image from the sheet
WritableImage wi = sheet.getImage(1);
sheet.removeImage(wi);
wi = new WritableImage(1, 116, 2, 9, new FileInfo("resources/littlemoretonhall.png"));
sheet.addImage(wi);
// Add a list data validations
label = new Label(0, 151, "Added drop down validation");
sheet.addCell(label);
Blank b = new Blank(1, 151);
wcf = new WritableCellFeatures();
ArrayList al = new ArrayList();
al.Add("The Fellowship of the Ring");
al.Add("The Two Towers");
al.Add("The Return of the King");
wcf.setDataValidationList(al);
b.setCellFeatures(wcf);
sheet.addCell(b);
// Add a number data validation
label = new Label(0, 152, "Added number validation 2.718 < x < 3.142");
sheet.addCell(label);
b = new Blank(1, 152);
wcf = new WritableCellFeatures();
wcf.setNumberValidation(2.718, 3.142, WritableCellFeatures.BETWEEN);
b.setCellFeatures(wcf);
sheet.addCell(b);
// Modify the text in the first cell with a comment
cell = sheet.getWritableCell(0, 156);
l = (Label)cell;
l.setString("Label text modified");
cell = sheet.getWritableCell(0, 157);
wcf = cell.getWritableCellFeatures();
wcf.setComment("modified comment text");
cell = sheet.getWritableCell(0, 158);
wcf = cell.getWritableCellFeatures();
wcf.removeComment();
// Modify the validation contents of the row 173
cell = sheet.getWritableCell(0, 172);
wcf = cell.getWritableCellFeatures();
Range r = wcf.getSharedDataValidationRange();
Cell botright = r.getBottomRight();
sheet.removeSharedDataValidation(cell);
al = new ArrayList();
al.Add("Stanley Featherstonehaugh Ukridge");
al.Add("Major Plank");
al.Add("Earl of Ickenham");
al.Add("Sir Gregory Parsloe-Parsloe");
al.Add("Honoria Glossop");
al.Add("Stiffy Byng");
al.Add("Bingo Little");
wcf.setDataValidationList(al);
cell.setCellFeatures(wcf);
sheet.applySharedDataValidation(cell, botright.getColumn() - cell.getColumn(), 1); //botright.getRow() - cell.getRow());
}
/**
* Returns an initialized copy of the cell format
*
* @param cf the cell format to copy
* @return a deep copy of the cell format
*/
private WritableCellFormat copyCellFormat(CellFormat cf)
{
try
{
// just do a deep copy of the cell format for now. This will create
// a copy of the format and font also - in the future this may
// need to be sorted out
XFRecord xfr = (XFRecord)cf;
WritableCellFormat f = new WritableCellFormat(xfr);
formatRecords.addStyle(f);
// Maintain the local list of formats
int xfIndex = xfr.getXFIndex();
xfRecords.Add(xfIndex, f);
int fontIndex = xfr.getFontIndex();
fonts.Add(fontIndex, f.getFontIndex());
int formatIndex = xfr.getFormatRecord();
formats.Add(formatIndex, f.getFormatRecord());
return f;
}
catch (NumFormatRecordsException e)
{
//logger.warn("Maximum number of format records exceeded. Using default format.");
return WritableWorkbook.NORMAL_STYLE;
}
}
/**
* 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()
{
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.Count > 0)
{
int[] rb = new int[rowBreaks.Count];
for (int i = 0; i < rb.Length; i++)
{
rb[i] = (int)rowBreaks[i];
}
HorizontalPageBreaksRecord hpbr = new HorizontalPageBreaksRecord(rb);
outputFile.write(hpbr);
}
if (columnBreaks.Count > 0)
{
int[] rb = new int[columnBreaks.Count];
for (int i = 0; i < rb.Length; i++)
{
rb[i] = (int)columnBreaks[i];
}
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
foreach (ColumnInfoRecord cir in columnFormats)
{
// 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 = System.Math.Min(32, numRows - block * 32);
bool 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
foreach (WritableHyperlink hlr in hyperlinks)
{
outputFile.write(hlr);
}
if (buttonPropertySet != null)
{
outputFile.write(buttonPropertySet);
}
// Write out the data validations
if (dataValidation != null || validatedCells.Count > 0)
{
writeDataValidation();
}
// Write out the conditional formats
if (conditionalFormats != null && conditionalFormats.Count > 0)
{
foreach (ConditionalFormat cf in conditionalFormats)
{
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);
}
/**
* Imports a sheet from a different workbook, doing a deep copy
*/
public void importSheet()
{
xfRecords = new Dictionary <int, WritableCellFormat>();
fonts = new Dictionary <int, int>();
formats = new Dictionary <int, int>();
deepCopyCells();
// Copy the column info records
CSharpJExcel.Jxl.Read.Biff.ColumnInfoRecord[] readCirs = fromSheet.getColumnInfos();
for (int i = 0; i < readCirs.Length; i++)
{
CSharpJExcel.Jxl.Read.Biff.ColumnInfoRecord rcir = readCirs[i];
for (int j = rcir.getStartColumn(); j <= rcir.getEndColumn(); j++)
{
ColumnInfoRecord cir = new ColumnInfoRecord(rcir, j);
int xfIndex = cir.getXfIndex();
XFRecord cf = null;
if (!xfRecords.ContainsKey(xfIndex))
{
// TODO: CML -- what does THIS actually achieve unless it has side-effects?
CellFormat readFormat = fromSheet.getColumnView(j).getFormat();
WritableCellFormat wcf = copyCellFormat(readFormat);
}
else
{
cf = xfRecords[xfIndex];
}
cir.setCellFormat(cf);
cir.setHidden(rcir.getHidden());
columnFormats.Add(cir);
}
}
// Copy the hyperlinks
Hyperlink[] hls = fromSheet.getHyperlinks();
for (int i = 0; i < hls.Length; i++)
{
WritableHyperlink hr = new WritableHyperlink(hls[i], toSheet);
hyperlinks.Add(hr);
}
// Copy the merged cells
Range[] merged = fromSheet.getMergedCells();
for (int i = 0; i < merged.Length; i++)
{
mergedCells.add(new SheetRangeImpl((SheetRangeImpl)merged[i], toSheet));
}
// Copy the row properties
try
{
CSharpJExcel.Jxl.Read.Biff.RowRecord[] rowprops = fromSheet.getRowProperties();
for (int i = 0; i < rowprops.Length; i++)
{
RowRecord rr = toSheet.getRowRecord(rowprops[i].getRowNumber());
XFRecord format = null;
CSharpJExcel.Jxl.Read.Biff.RowRecord rowrec = rowprops[i];
if (rowrec.hasDefaultFormat())
{
if (!xfRecords.ContainsKey(rowrec.getXFIndex()))
{
int rownum = rowrec.getRowNumber();
CellFormat readFormat = fromSheet.getRowView(rownum).getFormat();
WritableCellFormat wcf = copyCellFormat(readFormat);
}
else
{
format = xfRecords[rowrec.getXFIndex()];
}
}
rr.setRowDetails(rowrec.getRowHeight(),
rowrec.matchesDefaultFontHeight(),
rowrec.isCollapsed(),
rowrec.getOutlineLevel(),
rowrec.getGroupStart(),
format);
numRows = System.Math.Max(numRows, rowprops[i].getRowNumber() + 1);
}
}
catch (RowsExceededException e)
{
// Handle the rows exceeded exception - this cannot occur since
// the sheet we are copying from will have a valid number of rows
Assert.verify(false);
}
// Copy the headers and footers
// sheetWriter.setHeader(new HeaderRecord(si.getHeader()));
// sheetWriter.setFooter(new FooterRecord(si.getFooter()));
// Copy the page breaks
int[] rowbreaks = fromSheet.getRowPageBreaks();
if (rowbreaks != null)
{
for (int i = 0; i < rowbreaks.Length; i++)
{
rowBreaks.Add(rowbreaks[i]);
}
}
int[] columnbreaks = fromSheet.getColumnPageBreaks();
if (columnbreaks != null)
{
for (int i = 0; i < columnbreaks.Length; i++)
{
columnBreaks.Add(columnbreaks[i]);
}
}
// Copy the charts
Chart[] fromCharts = fromSheet.getCharts();
if (fromCharts != null && fromCharts.Length > 0)
{
//logger.warn("Importing of charts is not supported");
/*
* sheetWriter.setCharts(fromSheet.getCharts());
* IndexMapping xfMapping = new IndexMapping(200);
* for (Iterator i = xfRecords.keySet().iterator(); i.hasNext();)
* {
* Integer key = (Integer) i.next();
* XFRecord xfmapping = (XFRecord) xfRecords[key);
* xfMapping.setMapping(key, xfmapping.getXFIndex());
* }
*
* IndexMapping fontMapping = new IndexMapping(200);
* for (Iterator i = fonts.keySet().iterator(); i.hasNext();)
* {
* Integer key = (Integer) i.next();
* Integer fontmap = (Integer) fonts[key);
* fontMapping.setMapping(key, fontmap);
* }
*
* IndexMapping formatMapping = new IndexMapping(200);
* for (Iterator i = formats.keySet().iterator(); i.hasNext();)
* {
* Integer key = (Integer) i.next();
* Integer formatmap = (Integer) formats[key);
* formatMapping.setMapping(key, formatmap);
* }
*
* // Now reuse the rationalization feature on each chart to
* // handle the new fonts
* for (int i = 0; i < fromCharts.Length ; i++)
* {
* fromCharts[i].rationalize(xfMapping, fontMapping, formatMapping);
* }
*/
}
// Copy the drawings
DrawingGroupObject[] dr = fromSheet.getDrawings();
// Make sure the destination workbook has a drawing group
// created in it
if (dr.Length > 0 && toSheet.getWorkbook().getDrawingGroup() == null)
{
toSheet.getWorkbook().createDrawingGroup();
}
for (int i = 0; i < dr.Length; i++)
{
if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Drawing)
{
WritableImage wi = new WritableImage
(dr[i].getX(), dr[i].getY(),
dr[i].getWidth(), dr[i].getHeight(),
dr[i].getImageData());
toSheet.getWorkbook().addDrawing(wi);
drawings.Add(wi);
images.Add(wi);
}
else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Comment)
{
CSharpJExcel.Jxl.Biff.Drawing.Comment c = new CSharpJExcel.Jxl.Biff.Drawing.Comment(dr[i],
toSheet.getWorkbook().getDrawingGroup(),
workbookSettings);
drawings.Add(c);
// Set up the reference on the cell value
CellValue cv = (CellValue)toSheet.getWritableCell(c.getColumn(), c.getRow());
Assert.verify(cv.getCellFeatures() != null);
cv.getWritableCellFeatures().setCommentDrawing(c);
}
else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Button)
{
CSharpJExcel.Jxl.Biff.Drawing.Button b = new CSharpJExcel.Jxl.Biff.Drawing.Button(dr[i],
toSheet.getWorkbook().getDrawingGroup(),
workbookSettings);
drawings.Add(b);
}
else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.ComboBox)
{
CSharpJExcel.Jxl.Biff.Drawing.ComboBox cb = new CSharpJExcel.Jxl.Biff.Drawing.ComboBox(dr[i],
toSheet.getWorkbook().getDrawingGroup(),
workbookSettings);
drawings.Add(cb);
}
}
// Copy the data validations
DataValidation rdv = fromSheet.getDataValidation();
if (rdv != null)
{
dataValidation = new DataValidation(rdv,
toSheet.getWorkbook(),
toSheet.getWorkbook(),
workbookSettings);
uint objid = dataValidation.getComboBoxObjectId();
if (objid != 0)
{
comboBox = (ComboBox)drawings[(int)objid];
}
}
// Copy the workspace options
sheetWriter.setWorkspaceOptions(fromSheet.getWorkspaceOptions());
// Set a flag to indicate if it contains a chart only
if (fromSheet.getSheetBof().isChart())
{
chartOnly = true;
sheetWriter.setChartOnly();
}
// Copy the environment specific print record
if (fromSheet.getPLS() != null)
{
if (fromSheet.getWorkbookBof().isBiff7())
{
//logger.warn("Cannot copy Biff7 print settings record - ignoring");
}
else
{
plsRecord = new PLSRecord(fromSheet.getPLS());
}
}
// Copy the button property set
if (fromSheet.getButtonPropertySet() != null)
{
buttonPropertySet = new ButtonPropertySetRecord
(fromSheet.getButtonPropertySet());
}
importNames();
// Copy the outline levels
maxRowOutlineLevel = fromSheet.getMaxRowOutlineLevel();
maxColumnOutlineLevel = fromSheet.getMaxColumnOutlineLevel();
}