/// <summary>
/// 合併儲存格
/// </summary>
/// <param name="writableSheet"> WritableSheet </param>
/// <param name="usedCells"> 已使用區域紀錄物件 </param>
/// <param name="startCol"> 開始的 column index (行) </param>
/// <param name="startRow"> 開始的 row index (列) </param>
/// <param name="colspan"> colspan </param>
/// <param name="rowspan"> rowspan </param>
/// <param name="maxWidthMap"> 最大欄寬紀錄物件 </param>
/// <param name="width"> 欄寬 </param>
private void merageCell(WritableSheet writableSheet, Dictionary <int, HashSet <string> > usedCells, int startCol,
int startRow, int colspan, int rowspan, Dictionary <string, int> maxWidthMap, int width)
{
int endCol = startCol;
if (colspan > 1)
{
endCol += colspan - 1;
}
int endRow = startRow;
if (rowspan > 1)
{
endRow += rowspan - 1;
}
// 合併儲存格
writableSheet.mergeCells(startCol, startRow, endCol, endRow);
// 記錄使用區域
setUsed(usedCells, startCol, startRow, endCol, endRow);
// 記錄欄寬
for (int colNum = startCol; colNum <= endCol; colNum++)
{
setMaxWidth(maxWidthMap, width, colNum);
}
}
/**
* Initializes the range when this hyperlink is added to the sheet
*
* @param s the sheet containing this hyperlink
*/
public void initialize(WritableSheet s)
{
sheet = s;
range = new SheetRangeImpl(s,
firstColumn, firstRow,
lastColumn, lastRow);
}
private int EnterWriteDetail(WritableSheet writableSheet, DetailInfo detailInfo, int targetRowIndex,
IList <ColumnDataSet> columnDataSetList, Dictionary <int, HashSet <string> > usedCells,
Dictionary <string, int> maxWidthMap)
{
// 取得欄位設定欄位
List <ColumnInfo> columnInfoList = detailInfo.ColumnInfoList;
// 無資料跳出
if (ExcelStringUtil.IsEmpty(detailInfo.ColumnInfoList) || ExcelStringUtil.IsEmpty(columnDataSetList) || columnDataSetList.Count == 0)
{
return(targetRowIndex);
}
// 計算 rowspan
foreach (ColumnDataSet columnDataSet in columnDataSetList)
{
countRowspan(columnInfoList, columnDataSet);
}
// excel 欄位輸出
foreach (ColumnDataSet columnDataSet in columnDataSetList)
{
targetRowIndex = writeDetail(
writableSheet,
columnInfoList,
columnDataSet,
targetRowIndex,
usedCells,
maxWidthMap);
}
return(targetRowIndex);
}
/**
* Copies a hyperlink read in from a writable sheet.
* Used when copying writable sheets
*
* @param hl the hyperlink from the read spreadsheet
*/
private void copyWritableHyperlink(Hyperlink hl, WritableSheet s)
{
HyperlinkRecord h = (HyperlinkRecord)hl;
firstRow = h.firstRow;
lastRow = h.lastRow;
firstColumn = h.firstColumn;
lastColumn = h.lastColumn;
if (h.url != null)
{
try
{
url = new Uri(h.url.ToString());
}
catch (UriFormatException e)
{
// should never get a malformed url as a result url.ToString()
Assert.verify(false);
}
}
if (h.file != null)
{
file = new System.IO.FileInfo(h.file.FullName);
}
location = h.location;
contents = h.contents;
linkType = h.linkType;
modified = true;
sheet = s;
range = new SheetRangeImpl(s, firstColumn, firstRow, lastColumn, lastRow);
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param desc the cell contents for this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
*/
public WritableHyperlink(int col, int row,
string desc,
WritableSheet sheet,
int destcol, int destrow)
: this(col, row, col, row, desc, sheet, destcol, destrow, destcol, destrow)
{
}
/**
* Sets the location of the cells to be linked to within this workbook
*
* @param desc the label describing the link
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public virtual void setLocation(string desc, WritableSheet sheet, int destcol, int destrow, int lastdestcol, int lastdestrow)
{
linkType = workbookLink;
url = null;
file = null;
modified = true;
contents = desc;
setLocation(sheet, destcol, destrow, lastdestcol, lastdestrow);
if (sheet == null)
{
// hyperlink has not been added to the sheet yet, so simply return
return;
}
// Change the label on the sheet
WritableCell wc = sheet.getWritableCell(firstColumn, firstRow);
Assert.verify(wc.getType() == CellType.LABEL);
Label l = (Label)wc;
l.setString(desc);
}
public WritableSheetCopier(WritableSheet f, WritableSheet t)
{
fromSheet = (WritableSheetImpl)f;
toSheet = (WritableSheetImpl)t;
workbookSettings = toSheet.getWorkbook().getSettings();
chartOnly = false;
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param lastcol the last column which activates this hyperlink
* @param lastrow the last row which activates this hyperlink
* @param desc the cell contents for this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public WritableHyperlink(int col, int row,
int lastcol, int lastrow,
string desc,
WritableSheet sheet,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
: base(col, row, lastcol, lastrow, desc, sheet, destcol, destrow, lastdestcol, lastdestrow)
{
}
/**
* Constructs an empty row which has the specified row number
*
* @param rn the row number of this row
*/
public RowRecord(int rn, WritableSheet ws)
: base(Type.ROW)
{
rowNumber = rn;
cells = new CellValue[0];
numColumns = 0;
rowHeight = defaultHeightIndicator;
collapsed = false;
matchesDefFontHeight = true;
sheet = ws;
}
/**
* Constructs this object from the readable spreadsheet
*
* @param hl the hyperlink from the read spreadsheet
*/
protected HyperlinkRecord(Hyperlink h, WritableSheet s)
: base(CSharpJExcel.Jxl.Biff.Type.HLINK)
{
if (h is CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord)
{
copyReadHyperlink(h, s);
}
else
{
copyWritableHyperlink(h, s);
}
}
/// <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);
}
/**
* Initializes the location from the data passed in
*
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
private void setLocation(WritableSheet sheet,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
{
StringBuilder sb = new StringBuilder();
sb.Append('\'');
if (sheet.getName().IndexOf('\'') == -1)
{
sb.Append(sheet.getName());
}
else
{
// sb.append(sheet.getName().replaceAll("'", "''"));
// Can't use replaceAll as it is only 1.4 compatible, so have to
// do this the tedious way
string sheetName = sheet.getName();
int pos = 0;
int nextPos = sheetName.IndexOf('\'', pos);
while (nextPos != -1 && pos < sheetName.Length)
{
sb.Append(sheetName.Substring(pos, nextPos));
sb.Append("''");
pos = nextPos + 1;
nextPos = sheetName.IndexOf('\'', pos);
}
sb.Append(sheetName.Substring(pos));
}
sb.Append('\'');
sb.Append('!');
lastdestcol = System.Math.Max(destcol, lastdestcol);
lastdestrow = System.Math.Max(destrow, lastdestrow);
CellReferenceHelper.getCellReference(destcol, destrow, sb);
sb.Append(':');
CellReferenceHelper.getCellReference(lastdestcol, lastdestrow, sb);
location = sb.ToString();
}
public byte[] Render(Rdl.Render.GenericRender report, bool renderAll)
{
_report = report;
MemoryStream ms = new MemoryStream();
report.SetSizes(renderAll);
_workbook = Workbook.createWorkbook(ms);
//_workbook = Workbook.createWorkbook(new System.IO.FileInfo(@"c:\foo.xls"));
_ws = _workbook.createSheet("Sheet 1", 0);
RecurseBuildRowsCols(report.BodyContainer, 0, 0, renderAll);
_rows.Add(0);
_rows.Sort(delegate(decimal d1, decimal d2) { return(decimal.Compare(d1, d2)); });
_cols.Add(0);
_cols.Sort(delegate(decimal d1, decimal d2) { return(decimal.Compare(d1, d2)); });
for (int i = 1; i < _rows.Count; i++)
{
_ws.setRowView(i - 1, (int)((_rows[i] - _rows[i - 1]) * _lineHeight));
}
for (int i = 1; i < _cols.Count; i++)
{
_ws.setColumnView(i - 1, (int)((_cols[i] - _cols[i - 1]) * _colWidth));
}
formats = new WritableCellFormat[report.StyleList.Count];
for (int i = 0; i < report.StyleList.Count; i++)
{
if (report.StyleList[i] is TextStyle)
{
formats[i] = GetWritableFormat((TextStyle)report.StyleList[i]);
}
}
RecurseRender(report.BodyContainer, 0, 0, renderAll);
_workbook.write();
_workbook.close();
//BIFF8Writer.WriteWorkbookToStream(_workbook, ms);
return(ms.ToArray());
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param lastcol the last column which activates this hyperlink
* @param lastrow the last row which activates this hyperlink
* @param desc the contents of the cell which describe this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
protected HyperlinkRecord(int col, int row,
int lastcol, int lastrow,
string desc,
WritableSheet s,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
: base(CSharpJExcel.Jxl.Biff.Type.HLINK)
{
firstColumn = col;
firstRow = row;
lastColumn = System.Math.Max(firstColumn, lastcol);
lastRow = System.Math.Max(firstRow, lastrow);
setLocation(s, destcol, destrow, lastdestcol, lastdestrow);
contents = desc;
linkType = workbookLink;
modified = true;
}
/**
* Copies a hyperlink read in from a read only sheet
*/
private void copyReadHyperlink(Hyperlink h, WritableSheet s)
{
CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord hl = (CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord)h;
data = hl.getRecord().getData();
sheet = s;
// Populate this hyperlink with the copied data
firstRow = hl.getRow();
firstColumn = hl.getColumn();
lastRow = hl.getLastRow();
lastColumn = hl.getLastColumn();
range = new SheetRangeImpl(s,
firstColumn, firstRow,
lastColumn, lastRow);
linkType = unknown;
if (hl.isFile())
{
linkType = fileLink;
file = hl.getFile();
}
else if (hl.isURL())
{
linkType = urlLink;
url = hl.getURL();
}
else if (hl.isLocation())
{
linkType = workbookLink;
location = hl.getLocation();
}
modified = false;
}
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);
}
// ===========================================================================
// 功能區
// ===========================================================================
/// <summary>
/// 產生Excel
/// </summary>
/// <param name="exportConfigInfo"> </param>
/// <param name="exportDataSet"> </param>
/// <param name="outString"></param>
public virtual void export(ExportConfigInfo exportConfigInfo, ExportDataSet exportDataSet, Stream outString)
{
configInfo = exportConfigInfo;
try
{
// =========================================================
// 建立 Workbook
// =========================================================
WritableWorkbook writableWorkbook = Workbook.createWorkbook(outString);
for (int sheetlIndex = 0; sheetlIndex < exportConfigInfo.SheetList.Count; sheetlIndex++)
{
// =====================================================
// 建立 sheet
// =====================================================
// 取得 sheetlInfo 設定
SheetlInfo sheetlInfo = exportConfigInfo.SheetList[sheetlIndex];
// 取得 sheetName
string sheetName = (ExcelStringUtil.IsEmpty(sheetlInfo.SheetName))
? "Sheet" + (sheetlIndex + 1)
: sheetlInfo.SheetName;
// 建立 sheet
WritableSheet writableSheet = writableWorkbook.createSheet(sheetName, sheetlIndex);
// 版面設定
// setPageSetup Parameters:
// p - the page orientation
// ps - the paper size
// hm - the header margin, in inches
// fm - the footer margin, in inches
writableSheet.setPageSetup(PageOrientation.LANDSCAPE, exportConfigInfo.PaperSize, 0, 0);
writableSheet.getSettings().setLeftMargin(0);
writableSheet.getSettings().setRightMargin(0);
// =====================================================
// 處理前準備
// =====================================================
// 列指標
int targetRowIndex = 0;
// 紀錄已使用的儲存格 (cell)
var usedCells = new Dictionary <int, HashSet <string> >();
// 紀錄欄(column)的最大欄寬
var maxWidthMap = new Dictionary <string, int>();
// =====================================================
// 資訊
// =====================================================
foreach (var entry in sheetlInfo.PartInfoMap)
{
if (entry.Value == null)
{
return;
}
// 內容為 context
if (entry.Key.StartsWith(Constant.ELEMENT_CONTEXT))
{
//取得 context 設定檔設定資料
var contextInfo = (ContextInfo)entry.Value;
//取得 匯出資料
Dictionary <string, object> dataMap = exportDataSet.getContext(contextInfo.DataId);
targetRowIndex = WriteContext(
writableSheet,
contextInfo,
targetRowIndex,
dataMap,
usedCells,
maxWidthMap);
}
//內容為 detail
if (entry.Key.StartsWith(Constant.ELEMENT_DETAIL))
{
//取得 context 設定檔設定資料
var detailInfo = (DetailInfo)entry.Value;
//取得 匯出資料
var columnDataSetList = exportDataSet.getDetail(detailInfo.DataId);
targetRowIndex = EnterWriteDetail(
writableSheet,
detailInfo,
targetRowIndex,
columnDataSetList,
usedCells,
maxWidthMap);
}
}
// =====================================================
// 設定欄寬
// =====================================================
// 取得最大欄位 index
int maxColIndex = maxWidthMap[KEY_MAX_COL];
for (int colIndex = 0; colIndex <= maxColIndex; colIndex++)
{
// 取得欄寬
int colWidth = 0;
//取得 MAP 中的值 (tr、td 設定)
if (maxWidthMap.ContainsKey(colIndex + ""))
{
colWidth = maxWidthMap[colIndex + ""];
}
//若 tr td 未設定時，取 style 設定
if (colWidth == 0)
{
colWidth = Convert.ToInt32(ExcelStringUtil.SafeTrim(exportConfigInfo.StyleInfo.Width, "0"));
}
// 以上都未設定時使用預設值
//if (colWidth == 0)
//{
// colWidth = Convert.ToInt32(Constant.DEFAULT_WIDTH);
//}
if (colWidth > 0)
{
writableSheet.setColumnView(colIndex, colWidth);
}
}
}
writableWorkbook.write();
writableWorkbook.close();
}
catch (Exception e)
{
throw new ExcelOperateException("EXCEL 檔案匯出處理錯誤! \r\n" + e.Message + "\r\n" + e.StackTrace);
}
}
/**
* 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());
}
/**
* Add new named area to this workbook with the given information.
*
* @param name name to be created.
* @param sheet sheet containing the name
* @param firstCol first column this name refers to.
* @param firstRow first row this name refers to.
* @param lastCol last column this name refers to.
* @param lastRow last row this name refers to.
*/
public abstract void addNameArea(string name,
WritableSheet sheet,
int firstCol,
int firstRow,
int lastCol,
int lastRow);
/**
* Constructs this object from the readable spreadsheet
*
* @param hl the hyperlink from the read spreadsheet
*/
protected HyperlinkRecord(Hyperlink h, WritableSheet s)
: base(CSharpJExcel.Jxl.Biff.Type.HLINK)
{
if (h is CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord)
copyReadHyperlink(h, s);
else
copyWritableHyperlink(h, s);
}
/**
* Sets the location of the cells to be linked to within this workbook
*
* @param desc the label describing the link
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public override void setLocation(string desc, WritableSheet sheet, int destcol, int destrow, int lastdestcol, int lastdestrow)
{
base.setLocation(desc, sheet, destcol, destrow, lastdestcol, lastdestrow);
}
/**
* Write out the images
*/
private void writeImageSheet(WritableSheet ws)
{
Label l = new Label(0, 0, "Weald & Downland Open Air Museum, Sussex");
ws.addCell(l);
WritableImage wi = new WritableImage
(0, 3, 5, 7, new FileInfo("resources/wealdanddownland.png"));
ws.addImage(wi);
l = new Label(0, 12, "Merchant Adventurers Hall, York");
ws.addCell(l);
wi = new WritableImage(5, 12, 4, 10,
new FileInfo("resources/merchantadventurers.png"));
ws.addImage(wi);
// An unsupported file type
/*
wi = new WritableImage(0, 60, 5, 5, new File("resources/somefile.gif"));
ws.addImage(wi);
*/
}
/**
* Add new named area to this workbook with the given information.
*
* @param name name to be created.
* @param sheet sheet containing the name
* @param firstCol first column this name refers to.
* @param firstRow first row this name refers to.
* @param lastCol last column this name refers to.
* @param lastRow last row this name refers to.
*/
public override void addNameArea(string name,WritableSheet sheet,int firstCol,int firstRow,int lastCol,int lastRow)
{
addNameArea(name, sheet, firstCol, firstRow, lastCol, lastRow, true);
}
/**
* Adds cells to the specified sheet which test the various date formats
*
* @param s
*/
private void writeDateFormatSheet(WritableSheet s)
{
WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
wrappedText.setWrap(true);
s.setColumnView(0, 20);
s.setColumnView(2, 20);
s.setColumnView(3, 20);
s.setColumnView(4, 20);
s.getSettings().setFitWidth(2);
s.getSettings().setFitHeight(2);
// TODO: CML - fix this with DateTime support
//Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
//c.set(1975, 4, 31, 15, 21, 45);
//c.set(Calendar.MILLISECOND, 660);
//Date date = c.getTime();
//c.set(1900, 0, 1, 0, 0, 0);
//c.set(Calendar.MILLISECOND, 0);
//Date date2 = c.getTime();
//c.set(1970, 0, 1, 0, 0, 0);
//Date date3 = c.getTime();
//c.set(1918, 10, 11, 11, 0, 0);
//Date date4 = c.getTime();
//c.set(1900, 0, 2, 0, 0, 0);
//Date date5 = c.getTime();
//c.set(1901, 0, 1, 0, 0, 0);
//Date date6 = c.getTime();
//c.set(1900, 4, 31, 0, 0, 0);
//Date date7 = c.getTime();
//c.set(1900, 1, 1, 0, 0, 0);
//Date date8 = c.getTime();
//c.set(1900, 0, 31, 0, 0, 0);
//Date date9 = c.getTime();
//c.set(1900, 2, 1, 0, 0, 0);
//Date date10 = c.getTime();
//c.set(1900, 1, 27, 0, 0, 0);
//Date date11 = c.getTime();
//c.set(1900, 1, 28, 0, 0, 0);
//Date date12 = c.getTime();
//c.set(1980, 5, 31, 12, 0, 0);
//Date date13 = c.getTime();
//c.set(1066, 9, 14, 0, 0, 0);
//Date date14 = c.getTime();
//// Built in date formats
//SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
//sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
//Label l = new Label(0, 0, "All dates are " + sdf.format(date), wrappedText);
//s.addCell(l);
//l = new Label(0, 1, "Built in formats",
// wrappedText);
//s.addCell(l);
//l = new Label(2, 1, "Custom formats");
//s.addCell(l);
DateTime date = DateTime.UtcNow;
WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
ExcelDateTime dt = new ExcelDateTime(0, 2, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT2);
dt = new ExcelDateTime(0, 3, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT3);
dt = new ExcelDateTime(0, 4, date, cf1);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT4);
dt = new ExcelDateTime(0, 5, date, cf1);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT5);
dt = new ExcelDateTime(0, 6, date, cf1);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT6);
dt = new ExcelDateTime(0, 7, date, cf1);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT7);
dt = new ExcelDateTime(0, 8, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT8);
dt = new ExcelDateTime(0, 9, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT9);
dt = new ExcelDateTime(0, 10, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT10);
dt = new ExcelDateTime(0, 11, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT11);
dt = new ExcelDateTime(0, 12, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
cf1 = new WritableCellFormat(DateFormats.FORMAT12);
dt = new ExcelDateTime(0, 13, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
// Custom formats
DateFormat df = new DateFormat("dd MM yyyy");
cf1 = new WritableCellFormat(df);
Label l = new Label(2, 2, "dd MM yyyy");
s.addCell(l);
dt = new ExcelDateTime(3, 2, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("dd MMM yyyy");
cf1 = new WritableCellFormat(df);
l = new Label(2, 3, "dd MMM yyyy");
s.addCell(l);
dt = new ExcelDateTime(3, 3, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("hh:mm");
cf1 = new WritableCellFormat(df);
l = new Label(2, 4, "hh:mm");
s.addCell(l);
dt = new ExcelDateTime(3, 4, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("hh:mm:ss");
cf1 = new WritableCellFormat(df);
l = new Label(2, 5, "hh:mm:ss");
s.addCell(l);
dt = new ExcelDateTime(3, 5, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("H:mm:ss a");
cf1 = new WritableCellFormat(df);
l = new Label(2, 5, "H:mm:ss a");
s.addCell(l);
dt = new ExcelDateTime(3, 5, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
dt = new ExcelDateTime(4, 5, date, cf1, ExcelDateTime.GMT);
// dt = new ExcelDateTime(4, 5, date13, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("mm:ss.SSS");
cf1 = new WritableCellFormat(df);
l = new Label(2, 6, "mm:ss.SSS");
s.addCell(l);
dt = new ExcelDateTime(3, 6, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
df = new DateFormat("hh:mm:ss a");
cf1 = new WritableCellFormat(df);
l = new Label(2, 7, "hh:mm:ss a");
s.addCell(l);
// dt = new ExcelDateTime(4, 7, date13, cf1, ExcelDateTime.GMT);
dt = new ExcelDateTime(4, 7, date, cf1, ExcelDateTime.GMT);
s.addCell(dt);
// Check out the zero date ie. 1 Jan 1900
// l = new Label(0, 16, "Zero date " + sdf.format(date2),wrappedText);
// s.addCell(l);
cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(0, 17, date2, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
// Check out the zero date + 1 ie. 2 Jan 1900
//l = new Label(3, 16, "Zero date + 1 " + sdf.format(date5),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 17, date5, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out the 1 Jan 1901
//l = new Label(3, 19, sdf.format(date6),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 20, date6, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out the 31 May 1900
//l = new Label(3, 22, sdf.format(date7),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 23, date7, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 1 Feb 1900
//l = new Label(3, 25, sdf.format(date8),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 26, date8, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 31 Jan 1900
//l = new Label(3, 28, sdf.format(date9),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 29, date9, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 31 Jan 1900
//l = new Label(3, 28, sdf.format(date9),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 29, date9, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 1 Mar 1900
//l = new Label(3, 31, sdf.format(date10),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 32, date10, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 27 Feb 1900
//l = new Label(3, 34, sdf.format(date11),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 35, date11, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out 28 Feb 1900
//l = new Label(3, 37, sdf.format(date12),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(3, 38, date12, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out the zero date ie. 1 Jan 1970
//l = new Label(0, 19, "Zero UTC date " + sdf.format(date3),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(0, 20, date3, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out the WWI armistice day ie. 11 am, Nov 11, 1918
//l = new Label(0, 22, "Armistice date " + sdf.format(date4),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT9);
//dt = new ExcelDateTime(0, 23, date4, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
//// Check out the Battle of Hastings date Oct 14th, 1066
//l = new Label(0, 25, "Battle of Hastings " + sdf.format(date14),
// wrappedText);
//s.addCell(l);
//cf1 = new WritableCellFormat(DateFormats.FORMAT2);
//dt = new ExcelDateTime(0, 26, date14, cf1, ExcelDateTime.GMT);
//s.addCell(dt);
}
/**
* Test out the formula parser
*/
private void writeFormulaSheet(WritableSheet ws)
{
// Add some cells to manipulate
Number nc = new Number(0, 0, 15);
ws.addCell(nc);
nc = new Number(0, 1, 16);
ws.addCell(nc);
nc = new Number(0, 2, 10);
ws.addCell(nc);
nc = new Number(0, 3, 12);
ws.addCell(nc);
ws.setColumnView(2, 20);
WritableCellFormat wcf = new WritableCellFormat();
wcf.setAlignment(Alignment.RIGHT);
wcf.setWrap(true);
CellView cv = new CellView();
cv.setSize(25 * 256);
cv.setFormat(wcf);
ws.setColumnView(3, cv);
// Add in the formulas
Formula f = null;
Label l = null;
f = new Formula(2, 0, "A1+A2");
ws.addCell(f);
l = new Label(3, 0, "a1+a2");
ws.addCell(l);
f = new Formula(2, 1, "A2 * 3");
ws.addCell(f);
l = new Label(3, 1, "A2 * 3");
ws.addCell(l);
f = new Formula(2, 2, "A2+A1/2.5");
ws.addCell(f);
l = new Label(3, 2, "A2+A1/2.5");
ws.addCell(l);
f = new Formula(2, 3, "3+(a1+a2)/2.5");
ws.addCell(f);
l = new Label(3, 3, "3+(a1+a2)/2.5");
ws.addCell(l);
f = new Formula(2, 4, "(a1+a2)/2.5");
ws.addCell(f);
l = new Label(3, 4, "(a1+a2)/2.5");
ws.addCell(l);
f = new Formula(2, 5, "15+((a1+a2)/2.5)*17");
ws.addCell(f);
l = new Label(3, 5, "15+((a1+a2)/2.5)*17");
ws.addCell(l);
f = new Formula(2, 6, "SUM(a1:a4)");
ws.addCell(f);
l = new Label(3, 6, "SUM(a1:a4)");
ws.addCell(l);
f = new Formula(2, 7, "SUM(a1:a4)/4");
ws.addCell(f);
l = new Label(3, 7, "SUM(a1:a4)/4");
ws.addCell(l);
f = new Formula(2, 8, "AVERAGE(A1:A4)");
ws.addCell(f);
l = new Label(3, 8, "AVERAGE(a1:a4)");
ws.addCell(l);
f = new Formula(2, 9, "MIN(5,4,1,2,3)");
ws.addCell(f);
l = new Label(3, 9, "MIN(5,4,1,2,3)");
ws.addCell(l);
f = new Formula(2, 10, "ROUND(3.14159265, 3)");
ws.addCell(f);
l = new Label(3, 10, "ROUND(3.14159265, 3)");
ws.addCell(l);
f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
ws.addCell(f);
l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
ws.addCell(l);
f = new Formula(2, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
ws.addCell(f);
l = new Label(3, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
ws.addCell(l);
f = new Formula(2, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
ws.addCell(f);
l = new Label(3, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
ws.addCell(l);
f = new Formula(2, 14, "IF(A3<=10, \"<= 10\")");
ws.addCell(f);
l = new Label(3, 14, "IF(A3<=10, \"<= 10\")");
ws.addCell(l);
f = new Formula(2, 15, "SUM(1,2,3,4,5)");
ws.addCell(f);
l = new Label(3, 15, "SUM(1,2,3,4,5)");
ws.addCell(l);
f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
ws.addCell(f);
l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
ws.addCell(l);
f = new Formula(2, 17, "3*4+5");
ws.addCell(f);
l = new Label(3, 17, "3*4+5");
ws.addCell(l);
f = new Formula(2, 18, "\"Plain text formula\"");
ws.addCell(f);
l = new Label(3, 18, "Plain text formula");
ws.addCell(l);
f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
ws.addCell(f);
l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
ws.addCell(l);
f = new Formula(2, 20, "2*-(a1+a2)");
ws.addCell(f);
l = new Label(3, 20, "2*-(a1+a2)");
ws.addCell(l);
f = new Formula(2, 21, "'Number Formats'!B1/2");
ws.addCell(f);
l = new Label(3, 21, "'Number Formats'!B1/2");
ws.addCell(l);
f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
ws.addCell(f);
l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
ws.addCell(l);
f = new Formula(2, 23, "RAND()");
ws.addCell(f);
l = new Label(3, 23, "RAND()");
ws.addCell(l);
StringBuilder buf = new StringBuilder();
buf.Append("'");
buf.Append(workbook.getSheet(0).getName());
buf.Append("'!");
buf.Append(CellReferenceHelper.getCellReference(9, 18));
buf.Append("*25");
f = new Formula(2, 24, buf.ToString());
ws.addCell(f);
l = new Label(3, 24, buf.ToString());
ws.addCell(l);
wcf = new WritableCellFormat(DateFormats.DEFAULT);
f = new Formula(2, 25, "NOW()", wcf);
ws.addCell(f);
l = new Label(3, 25, "NOW()");
ws.addCell(l);
f = new Formula(2, 26, "$A$2+A3");
ws.addCell(f);
l = new Label(3, 26, "$A$2+A3");
ws.addCell(l);
f = new Formula(2, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
ws.addCell(f);
l = new Label(3, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
ws.addCell(l);
f = new Formula(2, 28, "SUM(A1,A2,A3,A4)");
ws.addCell(f);
l = new Label(3, 28, "SUM(A1,A2,A3,A4)");
ws.addCell(l);
l = new Label(1, 29, "a1");
ws.addCell(l);
f = new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
ws.addCell(f);
l = new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)");
ws.addCell(l);
f = new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")");
ws.addCell(f);
l = new Label(3, 30, "COUNTIF(A1:A4, \">=12\")");
ws.addCell(l);
f = new Formula(2, 31, "MAX($A$1:$A$4)");
ws.addCell(f);
l = new Label(3, 31, "MAX($A$1:$A$4)");
ws.addCell(l);
f = new Formula(2, 32, "OR(A1,TRUE)");
ws.addCell(f);
l = new Label(3, 32, "OR(A1,TRUE)");
ws.addCell(l);
f = new Formula(2, 33, "ROWS(A1:C14)");
ws.addCell(f);
l = new Label(3, 33, "ROWS(A1:C14)");
ws.addCell(l);
f = new Formula(2, 34, "COUNTBLANK(A1:C14)");
ws.addCell(f);
l = new Label(3, 34, "COUNTBLANK(A1:C14)");
ws.addCell(l);
f = new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
ws.addCell(f);
l = new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
ws.addCell(l);
f = new Formula(2, 36,
"HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\", \"Long hyperlink\")");
ws.addCell(f);
f = new Formula(2, 37, "1234567+2699");
ws.addCell(f);
l = new Label(3, 37, "1234567+2699");
ws.addCell(l);
f = new Formula(2, 38, "IF(ISERROR(G25/G29),0,-1)");
ws.addCell(f);
l = new Label(3, 38, "IF(ISERROR(G25/G29),0,-1)");
ws.addCell(l);
f = new Formula(2, 39, "SEARCH(\"C\",D40)");
ws.addCell(f);
l = new Label(3, 39, "SEARCH(\"C\",D40)");
ws.addCell(l);
f = new Formula(2, 40, "#REF!");
ws.addCell(f);
l = new Label(3, 40, "#REF!");
ws.addCell(l);
nc = new Number(1, 41, 79);
ws.addCell(nc);
f = new Formula(2, 41, "--B42");
ws.addCell(f);
l = new Label(3, 41, "--B42");
ws.addCell(l);
f = new Formula(2, 42, "CHOOSE(3,A1,A2,A3,A4");
ws.addCell(f);
l = new Label(3, 42, "CHOOSE(3,A1,A2,A3,A4");
ws.addCell(l);
f = new Formula(2, 43, "A4-A3-A2");
ws.addCell(f);
l = new Label(3, 43, "A4-A3-A2");
ws.addCell(l);
f = new Formula(2, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
ws.addCell(f);
l = new Label(3, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
ws.addCell(l);
nc = new Number(1, 45, 17);
ws.addCell(nc);
f = new Formula(2, 45, "formulavalue+5");
ws.addCell(f);
l = new Label(3, 45, "formulavalue+5");
ws.addCell(l);
// Errors
/*
f = new Formula(2, 25, "PLOP(15)"); // unknown function
ws.addCell(f);
f = new Formula(2, 26, "SUM(15,3"); // unmatched parentheses
ws.addCell(f);
f = new Formula(2, 27, "SUM15,3)"); // missing opening parentheses
ws.addCell(f);
f = new Formula(2, 28, "ROUND(3.14159)"); // missing args
ws.addCell(f);
f = new Formula(2, 29, "NONSHEET!A1"); // sheet not found
ws.addCell(f);
*/
}
/**
* Writes out a sheet containing the various numerical formats
*
* @param s
*/
private void writeNumberFormatSheet(WritableSheet s)
{
WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
wrappedText.setWrap(true);
s.setColumnView(0, 20);
s.setColumnView(4, 20);
s.setColumnView(5, 20);
s.setColumnView(6, 20);
// Floats
Label l = new Label(0, 0, "+/- Pi - default format", wrappedText);
s.addCell(l);
Number n = new Number(1, 0, 3.1415926535);
s.addCell(n);
n = new Number(2, 0, -3.1415926535);
s.addCell(n);
l = new Label(0, 1, "+/- Pi - integer format", wrappedText);
s.addCell(l);
WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
n = new Number(1, 1, 3.1415926535, cf1);
s.addCell(n);
n = new Number(2, 1, -3.1415926535, cf1);
s.addCell(n);
l = new Label(0, 2, "+/- Pi - float 2dps", wrappedText);
s.addCell(l);
WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
n = new Number(1, 2, 3.1415926535, cf2);
s.addCell(n);
n = new Number(2, 2, -3.1415926535, cf2);
s.addCell(n);
l = new Label(0, 3, "+/- Pi - custom 3dps",
wrappedText);
s.addCell(l);
NumberFormat dp3 = new NumberFormat("#.###");
WritableCellFormat dp3cell = new WritableCellFormat(dp3);
n = new Number(1, 3, 3.1415926535, dp3cell);
s.addCell(n);
n = new Number(2, 3, -3.1415926535, dp3cell);
s.addCell(n);
l = new Label(0, 4, "+/- Pi - custom &3.14",
wrappedText);
s.addCell(l);
NumberFormat pounddp2 = new NumberFormat("&#.00");
WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
n = new Number(1, 4, 3.1415926535, pounddp2cell);
s.addCell(n);
n = new Number(2, 4, -3.1415926535, pounddp2cell);
s.addCell(n);
l = new Label(0, 5, "+/- Pi - custom Text #.### Text",
wrappedText);
s.addCell(l);
NumberFormat textdp4 = new NumberFormat("Text#.####Text");
WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
n = new Number(1, 5, 3.1415926535, textdp4cell);
s.addCell(n);
n = new Number(2, 5, -3.1415926535, textdp4cell);
s.addCell(n);
// Integers
l = new Label(4, 0, "+/- Bilko default format");
s.addCell(l);
n = new Number(5, 0, 15042699);
s.addCell(n);
n = new Number(6, 0, -15042699);
s.addCell(n);
l = new Label(4, 1, "+/- Bilko float format");
s.addCell(l);
WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
n = new Number(5, 1, 15042699, cfi1);
s.addCell(n);
n = new Number(6, 1, -15042699, cfi1);
s.addCell(n);
l = new Label(4, 2, "+/- Thousands separator");
s.addCell(l);
WritableCellFormat cfi2 = new WritableCellFormat
(NumberFormats.THOUSANDS_INTEGER);
n = new Number(5, 2, 15042699, cfi2);
s.addCell(n);
n = new Number(6, 2, -15042699, cfi2);
s.addCell(n);
l = new Label(4, 3, "+/- Accounting red - added 0.01");
s.addCell(l);
WritableCellFormat cfi3 = new WritableCellFormat
(NumberFormats.ACCOUNTING_RED_FLOAT);
n = new Number(5, 3, 15042699.01, cfi3);
s.addCell(n);
n = new Number(6, 3, -15042699.01, cfi3);
s.addCell(n);
l = new Label(4, 4, "+/- Percent");
s.addCell(l);
WritableCellFormat cfi4 = new WritableCellFormat
(NumberFormats.PERCENT_INTEGER);
n = new Number(5, 4, 15042699, cfi4);
s.addCell(n);
n = new Number(6, 4, -15042699, cfi4);
s.addCell(n);
l = new Label(4, 5, "+/- Exponential - 2dps");
s.addCell(l);
WritableCellFormat cfi5 = new WritableCellFormat
(NumberFormats.EXPONENTIAL);
n = new Number(5, 5, 15042699, cfi5);
s.addCell(n);
n = new Number(6, 5, -15042699, cfi5);
s.addCell(n);
l = new Label(4, 6, "+/- Custom exponentional - 3dps", wrappedText);
s.addCell(l);
NumberFormat edp3 = new NumberFormat("0.000E0");
WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
n = new Number(5, 6, 15042699, edp3Cell);
s.addCell(n);
n = new Number(6, 6, -15042699, edp3Cell);
s.addCell(n);
l = new Label(4, 7, "Custom neg brackets", wrappedText);
s.addCell(l);
NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
n = new Number(5, 7, 15042699, negbrackscell);
s.addCell(n);
n = new Number(6, 7, -15042699, negbrackscell);
s.addCell(n);
l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
s.addCell(l);
NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
n = new Number(5, 8, 15042699, negbrackscell2);
s.addCell(n);
n = new Number(6, 8, -15042699, negbrackscell2);
s.addCell(n);
l = new Label(4, 9, "Custom percent", wrappedText);
s.addCell(l);
NumberFormat cuspercent = new NumberFormat("0.0%");
WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
n = new Number(5, 9, 3.14159265, cuspercentf);
s.addCell(n);
// Booleans
l = new Label(0, 10, "Boolean - TRUE");
s.addCell(l);
CSharpJExcel.Jxl.Write.Boolean b = new CSharpJExcel.Jxl.Write.Boolean(1, 10, true);
s.addCell(b);
l = new Label(0, 11, "Boolean - FALSE");
s.addCell(l);
b = new CSharpJExcel.Jxl.Write.Boolean(1, 11, false);
s.addCell(b);
l = new Label(0, 12, "A hidden cell->");
s.addCell(l);
n = new Number(1, 12, 17, WritableWorkbook.HIDDEN_STYLE);
s.addCell(n);
// Currencies
l = new Label(4, 19, "Currency formats");
s.addCell(l);
l = new Label(4, 21, "UK Pound");
s.addCell(l);
NumberFormat poundCurrency = new NumberFormat(NumberFormat.CURRENCY_POUND + " #,###.00",
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat poundFormat = new WritableCellFormat(poundCurrency);
n = new Number(5, 21, 12345, poundFormat);
s.addCell(n);
l = new Label(4, 22, "Euro 1");
s.addCell(l);
NumberFormat euroPrefixCurrency =
new NumberFormat(NumberFormat.CURRENCY_EURO_PREFIX + " #,###.00",
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat euroPrefixFormat =
new WritableCellFormat(euroPrefixCurrency);
n = new Number(5, 22, 12345, euroPrefixFormat);
s.addCell(n);
l = new Label(4, 23, "Euro 2");
s.addCell(l);
NumberFormat euroSuffixCurrency =
new NumberFormat("#,###.00" + NumberFormat.CURRENCY_EURO_SUFFIX,
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat euroSuffixFormat =
new WritableCellFormat(euroSuffixCurrency);
n = new Number(5, 23, 12345, euroSuffixFormat);
s.addCell(n);
l = new Label(4, 24, "Dollar");
s.addCell(l);
NumberFormat dollarCurrency =
new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " #,###.00",
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat dollarFormat =
new WritableCellFormat(dollarCurrency);
n = new Number(5, 24, 12345, dollarFormat);
s.addCell(n);
l = new Label(4, 25, "Japanese Yen");
s.addCell(l);
NumberFormat japaneseYenCurrency =
new NumberFormat(NumberFormat.CURRENCY_JAPANESE_YEN + " #,###.00",
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat japaneseYenFormat =
new WritableCellFormat(japaneseYenCurrency);
n = new Number(5, 25, 12345, japaneseYenFormat);
s.addCell(n);
l = new Label(4, 30, "Fraction formats");
s.addCell(l);
l = new Label(4, 32, "One digit fraction format", wrappedText);
s.addCell(l);
WritableCellFormat fraction1digitformat =
new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
n = new Number(5, 32, 3.18279, fraction1digitformat);
s.addCell(n);
l = new Label(4, 33, "Two digit fraction format", wrappedText);
s.addCell(l);
WritableCellFormat fraction2digitformat =
new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
n = new Number(5, 33, 3.18279, fraction2digitformat);
s.addCell(n);
l = new Label(4, 34, "Three digit fraction format (improper)", wrappedText);
s.addCell(l);
NumberFormat fraction3digit1 =
new NumberFormat(NumberFormat.FRACTION_THREE_DIGITS,
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat fraction3digitformat1 =
new WritableCellFormat(fraction3digit1);
n = new Number(5, 34, 3.18927, fraction3digitformat1);
s.addCell(n);
l = new Label(4, 35, "Three digit fraction format (proper)", wrappedText);
s.addCell(l);
NumberFormat fraction3digit2 =
new NumberFormat("# " + NumberFormat.FRACTION_THREE_DIGITS,
NumberFormat.COMPLEX_FORMAT);
WritableCellFormat fraction3digitformat2 =
new WritableCellFormat(fraction3digit2);
n = new Number(5, 35, 3.18927, fraction3digitformat2);
s.addCell(n);
// Lots of numbers
for (int row = 0; row < 100; row++)
{
for (int col = 8; col < 108; col++)
{
n = new Number(col, row, col + row);
s.addCell(n);
}
}
// Lots of numbers
for (int row = 101; row < 3000; row++)
{
for (int col = 0; col < 25; col++)
{
n = new Number(col, row, col + row);
s.addCell(n);
}
}
}
/**
* Write out loads of labels, in order to test the shared string table
*/
private void writeLabelsSheet(WritableSheet ws)
{
ws.getSettings().setProtected(true);
ws.getSettings().setPassword("jxl");
ws.getSettings().setVerticalFreeze(5);
ws.getSettings().setDefaultRowHeight(25 * 20);
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
wf.setItalic(true);
WritableCellFormat wcf = new WritableCellFormat(wf);
CellView cv = new CellView();
cv.setSize(25 * 256);
cv.setFormat(wcf);
ws.setColumnView(0, cv);
ws.setColumnView(1, 15);
for (int i = 0; i < 61; i++)
{
Label l1 = new Label(0, i, "Common Label");
Label l2 = new Label(1, i, "Distinct label number " + i);
ws.addCell(l1);
ws.addCell(l2);
}
// Frig this test record - it appears exactly on the boundary of an SST
// continue record
Label l3 = new Label(0, 61, "Common Label", wcf);
Label l4 = new Label(1, 61, "1-1234567890", wcf);
Label l5 = new Label(2, 61, "2-1234567890", wcf);
ws.addCell(l3);
ws.addCell(l4);
ws.addCell(l5);
for (int i = 62; i < 200; i++)
{
Label l1 = new Label(0, i, "Common Label");
Label l2 = new Label(1, i, "Distinct label number " + i);
ws.addCell(l1);
ws.addCell(l2);
}
// Add in a last label which doesn't take the jxl.common.format
wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
wf.setColour(Colour.RED);
WritableCellFormat wcf2 = new WritableCellFormat(wf);
wcf2.setWrap(true);
Label l = new Label(0, 205, "Different format", wcf2);
ws.addCell(l);
// Add some labels to column 5 for autosizing
Label l6 = new Label(5, 2, "A column for autosizing", wcf2);
ws.addCell(l6);
l6 = new Label(5, 4, "Another label, longer this time and " +
"in a different font");
ws.addCell(l6);
CellView cf = new CellView();
cf.setAutosize(true);
ws.setColumnView(5, cf);
}
/**
* Adds cells to the specified sheet which test the various border
* styles
*
* @param s
*/
private void writeBordersSheet(WritableSheet s)
{
s.getSettings().setProtected(true);
s.setColumnView(1, 15);
s.setColumnView(2, 15);
s.setColumnView(4, 15);
WritableCellFormat thickLeft = new WritableCellFormat();
thickLeft.setBorder(Border.LEFT, BorderLineStyle.THICK);
Label lr = new Label(1, 0, "Thick left", thickLeft);
s.addCell(lr);
WritableCellFormat dashedRight = new WritableCellFormat();
dashedRight.setBorder(Border.RIGHT, BorderLineStyle.DASHED);
lr = new Label(2, 0, "Dashed right", dashedRight);
s.addCell(lr);
WritableCellFormat doubleTop = new WritableCellFormat();
doubleTop.setBorder(Border.TOP, BorderLineStyle.DOUBLE);
lr = new Label(1, 2, "Double top", doubleTop);
s.addCell(lr);
WritableCellFormat hairBottom = new WritableCellFormat();
hairBottom.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
lr = new Label(2, 2, "Hair bottom", hairBottom);
s.addCell(lr);
WritableCellFormat allThin = new WritableCellFormat();
allThin.setBorder(Border.ALL, BorderLineStyle.THIN);
lr = new Label(4, 2, "All thin", allThin);
s.addCell(lr);
WritableCellFormat twoBorders = new WritableCellFormat();
twoBorders.setBorder(Border.TOP, BorderLineStyle.THICK);
twoBorders.setBorder(Border.LEFT, BorderLineStyle.THICK);
lr = new Label(6, 2, "Two borders", twoBorders);
s.addCell(lr);
// Create a cell in the middle of nowhere (out of the grow region)
lr = new Label(20, 20, "Dislocated cell - after a page break");
s.addCell(lr);
// Set the orientation and the margins
s.getSettings().setPaperSize(PaperSize.A3);
s.getSettings().setOrientation(PageOrientation.LANDSCAPE);
s.getSettings().setPageOrder(PageOrder.DOWN_THEN_RIGHT);
s.getSettings().setHeaderMargin(2);
s.getSettings().setFooterMargin(2);
s.getSettings().setTopMargin(3);
s.getSettings().setBottomMargin(3);
// Add a header and footera
HeaderFooter header = new HeaderFooter();
header.getCentre().append("Page Header");
s.getSettings().setHeader(header);
HeaderFooter footer = new HeaderFooter();
footer.getRight().append("page ");
footer.getRight().appendPageNumber();
s.getSettings().setFooter(footer);
// Add a page break and insert a couple of rows
s.addRowPageBreak(18);
s.insertRow(17);
s.insertRow(17);
s.removeRow(17);
// Add a page break off the screen
s.addRowPageBreak(30);
// Add a hidden column
lr = new Label(10, 1, "Hidden column");
s.addCell(lr);
lr = new Label(3, 8, "Hidden row");
s.addCell(lr);
s.setRowView(8, true);
WritableCellFormat allThickRed = new WritableCellFormat();
allThickRed.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.RED);
lr = new Label(1, 5, "All thick red", allThickRed);
s.addCell(lr);
WritableCellFormat topBottomBlue = new WritableCellFormat();
topBottomBlue.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLUE);
topBottomBlue.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
lr = new Label(4, 5, "Top and bottom blue", topBottomBlue);
s.addCell(lr);
}
/**
* Copies a hyperlink read in from a read only sheet
*/
private void copyReadHyperlink(Hyperlink h, WritableSheet s)
{
CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord hl = (CSharpJExcel.Jxl.Read.Biff.HyperlinkRecord)h;
data = hl.getRecord().getData();
sheet = s;
// Populate this hyperlink with the copied data
firstRow = hl.getRow();
firstColumn = hl.getColumn();
lastRow = hl.getLastRow();
lastColumn = hl.getLastColumn();
range = new SheetRangeImpl(s,
firstColumn, firstRow,
lastColumn, lastRow);
linkType = unknown;
if (hl.isFile())
{
linkType = fileLink;
file = hl.getFile();
}
else if (hl.isURL())
{
linkType = urlLink;
url = hl.getURL();
}
else if (hl.isLocation())
{
linkType = workbookLink;
location = hl.getLocation();
}
modified = false;
}
/**
* Sets the location of the cells to be linked to within this workbook
*
* @param desc the label describing the link
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public virtual void setLocation(string desc,WritableSheet sheet, int destcol, int destrow, int lastdestcol, int lastdestrow)
{
linkType = workbookLink;
url = null;
file = null;
modified = true;
contents = desc;
setLocation(sheet, destcol, destrow, lastdestcol, lastdestrow);
if (sheet == null)
{
// hyperlink has not been added to the sheet yet, so simply return
return;
}
// Change the label on the sheet
WritableCell wc = sheet.getWritableCell(firstColumn, firstRow);
Assert.verify(wc.getType() == CellType.LABEL);
Label l = (Label)wc;
l.setString(desc);
}
/**
* Gets the sheets within this workbook. Use of this method for
* large worksheets can cause performance problems.
*
* @return an array of the individual sheets
*/
public override WritableSheet[] getSheets()
{
WritableSheet[] sheetArray = new WritableSheet[getNumberOfSheets()];
for (int i = 0; i < getNumberOfSheets(); i++)
{
sheetArray[i] = getSheet(i);
}
return sheetArray;
}
/**
* Initializes the range when this hyperlink is added to the sheet
*
* @param s the sheet containing this hyperlink
*/
public void initialize(WritableSheet s)
{
sheet = s;
range = new SheetRangeImpl(s,
firstColumn, firstRow,
lastColumn, lastRow);
}
/**
* Add new named area to this workbook with the given information.
*
* @param name name to be created.
* @param sheet sheet containing the name
* @param firstCol first column this name refers to.
* @param firstRow first row this name refers to.
* @param lastCol last column this name refers to.
* @param lastRow last row this name refers to.
* @param firstCol2 first column this name refers to.
* @param firstRow2 first row this name refers to.
* @param lastCol2 last column this name refers to.
* @param lastRow2 last row this name refers to.
* @param global TRUE if this is a global name, FALSE if this is tied to
* the sheet
*/
public void addNameArea(BuiltInName name,
WritableSheet sheet,
int firstCol,
int firstRow,
int lastCol,
int lastRow,
int firstCol2,
int firstRow2,
int lastCol2,
int lastRow2,
bool global)
{
if (names == null)
names = new ArrayList();
int index = getInternalSheetIndex(sheet.getName());
int externalSheetIndex = getExternalSheetIndex(sheet.getName());
// Create a new name record.
NameRecord nr = new NameRecord(name,
index,
externalSheetIndex,
firstRow2, lastRow2,
firstCol2, lastCol2,
firstRow, lastRow,
firstCol, lastCol,
global);
// Add new name to name array.
names.Add(nr);
// Add new name to name hash table.
string key = name.getName() == null ? NULLKEY : name.getName();
nameRecords.Add(key, nr);
}
/**
* Constructs an empty row which has the specified row number
*
* @param rn the row number of this row
*/
public RowRecord(int rn, WritableSheet ws)
: base(Type.ROW)
{
rowNumber = rn;
cells = new CellValue[0];
numColumns = 0;
rowHeight = defaultHeightIndicator;
collapsed = false;
matchesDefFontHeight = true;
sheet = ws;
}
/**
* Initializes the location from the data passed in
*
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
private void setLocation(WritableSheet sheet,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
{
StringBuilder sb = new StringBuilder();
sb.Append('\'');
if (sheet.getName().IndexOf('\'') == -1)
{
sb.Append(sheet.getName());
}
else
{
// sb.append(sheet.getName().replaceAll("'", "''"));
// Can't use replaceAll as it is only 1.4 compatible, so have to
// do this the tedious way
string sheetName = sheet.getName();
int pos = 0;
int nextPos = sheetName.IndexOf('\'', pos);
while (nextPos != -1 && pos < sheetName.Length)
{
sb.Append(sheetName.Substring(pos, nextPos));
sb.Append("''");
pos = nextPos + 1;
nextPos = sheetName.IndexOf('\'', pos);
}
sb.Append(sheetName.Substring(pos));
}
sb.Append('\'');
sb.Append('!');
lastdestcol = System.Math.Max(destcol, lastdestcol);
lastdestrow = System.Math.Max(destrow, lastdestrow);
CellReferenceHelper.getCellReference(destcol, destrow, sb);
sb.Append(':');
CellReferenceHelper.getCellReference(lastdestcol, lastdestrow, sb);
location = sb.ToString();
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param desc the cell contents for this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
*/
public WritableHyperlink(int col, int row,
string desc,
WritableSheet sheet,
int destcol, int destrow)
: this(col, row, col, row, desc, sheet, destcol, destrow, destcol, destrow)
{
}
/**
* Constructor used internally by the worksheet when making a copy
* of worksheet
*
* @param h the hyperlink being read in
* @param ws the writable sheet containing the hyperlink
*/
public WritableHyperlink(Hyperlink h, WritableSheet ws)
: base(h, ws)
{
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param lastcol the last column which activates this hyperlink
* @param lastrow the last row which activates this hyperlink
* @param desc the cell contents for this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public WritableHyperlink(int col, int row,
int lastcol, int lastrow,
string desc,
WritableSheet sheet,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
: base(col, row, lastcol, lastrow, desc,sheet, destcol, destrow, lastdestcol, lastdestrow)
{
}
/**
* Constructor
*/
public MergedCells(WritableSheet ws)
{
ranges = new ArrayList();
sheet = ws;
}
/**
* Sets the location of the cells to be linked to within this workbook
*
* @param desc the label describing the link
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
public override void setLocation(string desc, WritableSheet sheet, int destcol, int destrow, int lastdestcol, int lastdestrow)
{
base.setLocation(desc, sheet, destcol, destrow, lastdestcol, lastdestrow);
}
/**
* Constructor
*/
public MergedCells(WritableSheet ws)
{
ranges = new ArrayList();
sheet = ws;
}
/**
* Constructor used internally by the worksheet when making a copy
* of worksheet
*
* @param h the hyperlink being read in
* @param ws the writable sheet containing the hyperlink
*/
public WritableHyperlink(Hyperlink h, WritableSheet ws)
: base(h, ws)
{
}
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);
}
/**
* Constructs a hyperlink to some cells within this workbook
*
* @param col the column containing this hyperlink
* @param row the row containing this hyperlink
* @param lastcol the last column which activates this hyperlink
* @param lastrow the last row which activates this hyperlink
* @param desc the contents of the cell which describe this hyperlink
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
protected HyperlinkRecord(int col, int row,
int lastcol, int lastrow,
string desc,
WritableSheet s,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
: base(CSharpJExcel.Jxl.Biff.Type.HLINK)
{
firstColumn = col;
firstRow = row;
lastColumn = System.Math.Max(firstColumn, lastcol);
lastRow = System.Math.Max(firstRow, lastrow);
setLocation(s, destcol, destrow, lastdestcol, lastdestrow);
contents = desc;
linkType = workbookLink;
modified = true;
}
/**
* Adds cells to the specified sheet which test the various label formatting
* styles, such as different fonts, different sizes and bold, underline etc.
*
* @param s1
*/
private void writeLabelFormatSheet(WritableSheet s1)
{
s1.setColumnView(0, 60);
Label lr = new Label(0, 0, "Arial Fonts");
s1.addCell(lr);
lr = new Label(1, 0, "10pt");
s1.addCell(lr);
lr = new Label(2, 0, "Normal");
s1.addCell(lr);
lr = new Label(3, 0, "12pt");
s1.addCell(lr);
WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
arial12format.setWrap(true);
lr = new Label(4, 0, "Normal", arial12format);
s1.addCell(lr);
WritableFont arial10ptBold = new WritableFont
(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat arial10BoldFormat = new WritableCellFormat
(arial10ptBold);
lr = new Label(2, 2, "BOLD", arial10BoldFormat);
s1.addCell(lr);
WritableFont arial12ptBold = new WritableFont
(WritableFont.ARIAL, 12, WritableFont.BOLD);
WritableCellFormat arial12BoldFormat = new WritableCellFormat
(arial12ptBold);
lr = new Label(4, 2, "BOLD", arial12BoldFormat);
s1.addCell(lr);
WritableFont arial10ptItalic = new WritableFont
(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
WritableCellFormat arial10ItalicFormat = new WritableCellFormat
(arial10ptItalic);
lr = new Label(2, 4, "Italic", arial10ItalicFormat);
s1.addCell(lr);
WritableFont arial12ptItalic = new WritableFont
(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
(arial12ptItalic);
lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
s1.addCell(lr);
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
WritableCellFormat times10format = new WritableCellFormat(times10pt);
lr = new Label(0, 7, "Times Fonts", times10format);
s1.addCell(lr);
lr = new Label(1, 7, "10pt", times10format);
s1.addCell(lr);
lr = new Label(2, 7, "Normal", times10format);
s1.addCell(lr);
lr = new Label(3, 7, "12pt", times10format);
s1.addCell(lr);
WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
WritableCellFormat times12format = new WritableCellFormat(times12pt);
lr = new Label(4, 7, "Normal", times12format);
s1.addCell(lr);
WritableFont times10ptBold = new WritableFont
(WritableFont.TIMES, 10, WritableFont.BOLD);
WritableCellFormat times10BoldFormat = new WritableCellFormat
(times10ptBold);
lr = new Label(2, 9, "BOLD", times10BoldFormat);
s1.addCell(lr);
WritableFont times12ptBold = new WritableFont
(WritableFont.TIMES, 12, WritableFont.BOLD);
WritableCellFormat times12BoldFormat = new WritableCellFormat
(times12ptBold);
lr = new Label(4, 9, "BOLD", times12BoldFormat);
s1.addCell(lr);
// The underline styles
s1.setColumnView(6, 22);
s1.setColumnView(7, 22);
s1.setColumnView(8, 22);
s1.setColumnView(9, 22);
lr = new Label(0, 11, "Underlining");
s1.addCell(lr);
WritableFont arial10ptUnderline = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
WritableCellFormat arialUnderline = new WritableCellFormat
(arial10ptUnderline);
lr = new Label(6, 11, "Underline", arialUnderline);
s1.addCell(lr);
WritableFont arial10ptDoubleUnderline = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.DOUBLE);
WritableCellFormat arialDoubleUnderline = new WritableCellFormat
(arial10ptDoubleUnderline);
lr = new Label(7, 11, "Double Underline", arialDoubleUnderline);
s1.addCell(lr);
WritableFont arial10ptSingleAcc = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE_ACCOUNTING);
WritableCellFormat arialSingleAcc = new WritableCellFormat
(arial10ptSingleAcc);
lr = new Label(8, 11, "Single Accounting Underline", arialSingleAcc);
s1.addCell(lr);
WritableFont arial10ptDoubleAcc = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.DOUBLE_ACCOUNTING);
WritableCellFormat arialDoubleAcc = new WritableCellFormat
(arial10ptDoubleAcc);
lr = new Label(9, 11, "Double Accounting Underline", arialDoubleAcc);
s1.addCell(lr);
WritableFont times14ptBoldUnderline = new WritableFont
(WritableFont.TIMES,
14,
WritableFont.BOLD,
false,
UnderlineStyle.SINGLE);
WritableCellFormat timesBoldUnderline = new WritableCellFormat
(times14ptBoldUnderline);
lr = new Label(6, 12, "Times 14 Bold Underline", timesBoldUnderline);
s1.addCell(lr);
WritableFont arial18ptBoldItalicUnderline = new WritableFont
(WritableFont.ARIAL,
18,
WritableFont.BOLD,
true,
UnderlineStyle.SINGLE);
WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
(arial18ptBoldItalicUnderline);
lr = new Label(6, 13, "Arial 18 Bold Italic Underline",
arialBoldItalicUnderline);
s1.addCell(lr);
lr = new Label(0, 15, "Script styles");
s1.addCell(lr);
WritableFont superscript = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK,
ScriptStyle.SUPERSCRIPT);
WritableCellFormat superscriptFormat = new WritableCellFormat
(superscript);
lr = new Label(1, 15, "superscript", superscriptFormat);
s1.addCell(lr);
WritableFont subscript = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK,
ScriptStyle.SUBSCRIPT);
WritableCellFormat subscriptFormat = new WritableCellFormat
(subscript);
lr = new Label(2, 15, "subscript", subscriptFormat);
s1.addCell(lr);
lr = new Label(0, 17, "Colours");
s1.addCell(lr);
WritableFont red = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.RED);
WritableCellFormat redFormat = new WritableCellFormat(red);
lr = new Label(2, 17, "Red", redFormat);
s1.addCell(lr);
WritableFont blue = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLUE);
WritableCellFormat blueFormat = new WritableCellFormat(blue);
lr = new Label(2, 18, "Blue", blueFormat);
s1.addCell(lr);
WritableFont lime = new WritableFont(WritableFont.ARIAL);
lime.setColour(Colour.LIME);
WritableCellFormat limeFormat = new WritableCellFormat(lime);
limeFormat.setWrap(true);
lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
s1.addCell(lr);
WritableCellFormat greyBackground = new WritableCellFormat();
greyBackground.setWrap(true);
greyBackground.setBackground(Colour.GRAY_50);
lr = new Label(2, 19, "Grey background", greyBackground);
s1.addCell(lr);
WritableFont yellow = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.YELLOW);
WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
yellowOnBlue.setWrap(true);
yellowOnBlue.setBackground(Colour.BLUE);
lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
s1.addCell(lr);
WritableCellFormat yellowOnBlack = new WritableCellFormat(yellow);
yellowOnBlack.setWrap(true);
yellowOnBlack.setBackground(Colour.PALETTE_BLACK);
lr = new Label(3, 20, "Black background, yellow foreground",
yellowOnBlack);
s1.addCell(lr);
lr = new Label(0, 22, "Null label");
s1.addCell(lr);
lr = new Label(2, 22, null);
s1.addCell(lr);
lr = new Label(0, 24,
"A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" +
"A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" +
"And sat and drank coffee an talked for an hour\012",
arial12format);
s1.addCell(lr);
WritableCellFormat vertical = new WritableCellFormat();
vertical.setOrientation(Orientation.VERTICAL);
lr = new Label(0, 26, "Vertical orientation", vertical);
s1.addCell(lr);
WritableCellFormat plus_90 = new WritableCellFormat();
plus_90.setOrientation(Orientation.PLUS_90);
lr = new Label(1, 26, "Plus 90", plus_90);
s1.addCell(lr);
WritableCellFormat minus_90 = new WritableCellFormat();
minus_90.setOrientation(Orientation.MINUS_90);
lr = new Label(2, 26, "Minus 90", minus_90);
s1.addCell(lr);
lr = new Label(0, 28, "Modified row height");
s1.addCell(lr);
s1.setRowView(28, 24 * 20);
lr = new Label(0, 29, "Collapsed row");
s1.addCell(lr);
s1.setRowView(29, true);
// Write hyperlinks
Label l;
try
{
l = new Label(0, 30, "Hyperlink to home page");
s1.addCell(l);
Uri url = new Uri("http://www.andykhan.com/jexcelapi");
WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
s1.addHyperlink(wh);
// The below hyperlink clashes with above
WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
s1.addHyperlink(wh2);
l = new Label(4, 2, "File hyperlink to documentation");
s1.addCell(l);
FileInfo file = new FileInfo("../jexcelapi/docs/index.html");
wh = new WritableHyperlink(0, 32, 8, 32, file, "JExcelApi Documentation");
s1.addHyperlink(wh);
// Add a hyperlink to another cell on this sheet
wh = new WritableHyperlink(0, 34, 8, 34,
"Link to another cell",
s1,
0, 180, 1, 181);
s1.addHyperlink(wh);
file = new FileInfo("\\\\localhost\\file.txt");
wh = new WritableHyperlink(0, 36, 8, 36, file);
s1.addHyperlink(wh);
// Add a very long hyperlink
url = new Uri("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086" +
"/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
wh = new WritableHyperlink(0, 38, 0, 38, url);
s1.addHyperlink(wh);
}
catch (Exception e)
{
Console.WriteLine(e);
}
// Write out some merged cells
l = new Label(5, 35, "Merged cells", timesBoldUnderline);
s1.mergeCells(5, 35, 8, 37);
s1.addCell(l);
l = new Label(5, 38, "More merged cells");
s1.addCell(l);
Range r = s1.mergeCells(5, 38, 8, 41);
s1.insertRow(40);
s1.removeRow(39);
s1.unmergeCells(r);
// Merge cells and centre across them
WritableCellFormat wcf = new WritableCellFormat();
wcf.setAlignment(Alignment.CENTRE);
l = new Label(5, 42, "Centred across merged cells", wcf);
s1.addCell(l);
s1.mergeCells(5, 42, 10, 42);
wcf = new WritableCellFormat();
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf.setBackground(Colour.GRAY_25);
l = new Label(3, 44, "Merged with border", wcf);
s1.addCell(l);
s1.mergeCells(3, 44, 4, 46);
// Clash some ranges - the second range will not be added
// Also merge some cells with two data items in the - the second data
// item will not be merged
/*
l = new Label(5, 16, "merged cells");
s1.addCell(l);
Label l5 = new Label(7, 17, "this label won't appear");
s1.addCell(l5);
s1.mergeCells(5, 16, 8, 18);
s1.mergeCells(5, 19, 6, 24);
s1.mergeCells(6, 18, 10, 19);
*/
WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
l = new Label(0, 49, "Courier fonts", courier10pt);
s1.addCell(l);
WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
s1.addCell(l);
WritableFont.FontName wingdingsFont =
WritableFont.createFont("Wingdings 2");
WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
WritableCellFormat wingdings210pt = new WritableCellFormat
(wingdings210ptFont);
l = new Label(0, 51, "Bespoke Windgdings 2", wingdings210pt);
s1.addCell(l);
WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
shrinkToFit.setShrinkToFit(true);
l = new Label(3, 53, "Shrunk to fit", shrinkToFit);
s1.addCell(l);
l = new Label(3, 55, "Some long wrapped text in a merged cell",
arial12format);
s1.addCell(l);
s1.mergeCells(3, 55, 4, 55);
l = new Label(0, 57, "A cell with a comment");
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("the cell comment");
l.setCellFeatures(cellFeatures);
s1.addCell(l);
l = new Label(0, 59,
"A cell with a long comment");
cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("a very long cell comment indeed that won't " +
"fit inside a standard comment box, so a " +
"larger comment box is used instead",
5, 6);
l.setCellFeatures(cellFeatures);
s1.addCell(l);
WritableCellFormat indented = new WritableCellFormat(times12pt);
indented.setIndentation(4);
l = new Label(0, 61, "Some indented text", indented);
s1.addCell(l);
l = new Label(0, 63, "Data validation: list");
s1.addCell(l);
Blank b = new Blank(1, 63);
cellFeatures = new WritableCellFeatures();
ArrayList al = new ArrayList();
al.Add("bagpuss");
al.Add("clangers");
al.Add("ivor the engine");
al.Add("noggin the nog");
cellFeatures.setDataValidationList(al);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
l = new Label(0, 64, "Data validation: number > 4.5");
s1.addCell(l);
b = new Blank(1, 64);
cellFeatures = new WritableCellFeatures();
cellFeatures.setNumberValidation(4.5, WritableCellFeatures.GREATER_THAN);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
l = new Label(0, 65, "Data validation: named range");
s1.addCell(l);
l = new Label(4, 65, "tiger");
s1.addCell(l);
l = new Label(5, 65, "sword");
s1.addCell(l);
l = new Label(6, 65, "honour");
s1.addCell(l);
l = new Label(7, 65, "company");
s1.addCell(l);
l = new Label(8, 65, "victory");
s1.addCell(l);
l = new Label(9, 65, "fortress");
s1.addCell(l);
b = new Blank(1, 65);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationRange("validation_range");
b.setCellFeatures(cellFeatures);
s1.addCell(b);
// Set the row grouping
s1.setRowGroup(39, 45, false);
// s1.setRowGroup(72, 74, true);
l = new Label(0, 66, "Block of cells B67-F71 with data validation");
s1.addCell(l);
al = new ArrayList();
al.Add("Achilles");
al.Add("Agamemnon");
al.Add("Hector");
al.Add("Odysseus");
al.Add("Patroclus");
al.Add("Nestor");
b = new Blank(1, 66);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationList(al);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
s1.applySharedDataValidation(b, 4, 4);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationRange("");
l = new Label(0, 71, "Read only cell using empty data validation");
l.setCellFeatures(cellFeatures);
s1.addCell(l);
// Set the row grouping
s1.setRowGroup(39, 45, false);
// s1.setRowGroup(72, 74, true);
}
/**
* Copies a hyperlink read in from a writable sheet.
* Used when copying writable sheets
*
* @param hl the hyperlink from the read spreadsheet
*/
private void copyWritableHyperlink(Hyperlink hl, WritableSheet s)
{
HyperlinkRecord h = (HyperlinkRecord)hl;
firstRow = h.firstRow;
lastRow = h.lastRow;
firstColumn = h.firstColumn;
lastColumn = h.lastColumn;
if (h.url != null)
{
try
{
url = new Uri(h.url.ToString());
}
catch (UriFormatException e)
{
// should never get a malformed url as a result url.ToString()
Assert.verify(false);
}
}
if (h.file != null)
file = new System.IO.FileInfo(h.file.FullName);
location = h.location;
contents = h.contents;
linkType = h.linkType;
modified = true;
sheet = s;
range = new SheetRangeImpl(s,firstColumn, firstRow,lastColumn, lastRow);
}
/**
* Add new named area to this workbook with the given information.
*
* @param name name to be created.
* @param sheet sheet containing the name
* @param firstCol first column this name refers to.
* @param firstRow first row this name refers to.
* @param lastCol last column this name refers to.
* @param lastRow last row this name refers to.
*/
public abstract void addNameArea(string name,
WritableSheet sheet,
int firstCol,
int firstRow,
int lastCol,
int lastRow);
public SheetCopier(Sheet f, WritableSheet t)
{
fromSheet = (SheetImpl)f;
toSheet = (WritableSheetImpl)t;
workbookSettings = toSheet.getWorkbook().getSettings();
chartOnly = false;
}
