/// <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); }
/** * 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; }
/** * 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); }
/** * 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; }
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); }
/** * 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); }
public SheetCopier(Sheet f, WritableSheet t) { fromSheet = (SheetImpl)f; toSheet = (WritableSheetImpl)t; workbookSettings = toSheet.getWorkbook().getSettings(); chartOnly = false; }