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