[Test]//(expected=RuntimeException.class) public void SetCellStylePropertyWithInvalidValue() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); ICell c = r.CreateCell(0); // An invalid BorderStyle constant CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, 42); wb.Close(); }
void SetCell(IWorkbook workbook, ISheet sheet, int r, int tor, int c, int toc, dynamic value, IFont font, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); //var row = sheet.GetRow(r) == null ? sheet.CreateRow(r) : sheet.GetRow(r); var fff = sheet.GetType().GetField("_rows").GetValue(sheet); var row = (fff as SortedDictionary <int, SXSSFRow>).ContainsKey(r) ? sheet.GetRow(r) : sheet.CreateRow(r); if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.CreateCell(c); if (value != null) { cell.SetCellValue(value); } var style = workbook.CreateCellStyle(); //设置字体 style.SetFont(font); //设置边框 if (isBorder) { style.BorderLeft = borderStyle; style.BorderRight = borderStyle; style.BorderTop = borderStyle; style.BorderBottom = borderStyle; for (int i = rang.FirstRow; i <= rang.LastRow; i++) { var borderRow = CellUtil.GetRow(i, sheet); for (int j = rang.FirstColumn; j <= rang.LastColumn; j++) { var singleCell = CellUtil.GetCell(borderRow, (short)j); singleCell.CellStyle = style; } } } else { cell.CellStyle = style; } //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } }
public void TestBug54524() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("54524.xlsx"); ISheet sheet = workbook.GetSheetAt(0); sheet.ShiftRows(3, 5, -1); ICell cell = CellUtil.GetCell(sheet.GetRow(1), 0); Assert.AreEqual(1.0, cell.NumericCellValue); cell = CellUtil.GetCell(sheet.GetRow(2), 0); Assert.AreEqual("SUM(A2:A2)", cell.CellFormula); cell = CellUtil.GetCell(sheet.GetRow(3), 0); Assert.AreEqual("X", cell.StringCellValue); }
public void SetFontShouldNotCreateDuplicateStyle() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); ICell c = wb1.CreateSheet().CreateRow(1).CreateCell(1); IFont f = wb1.CreateFont(); CellUtil.SetFont(c, f); int num1 = wb1.NumCellStyles; CellUtil.SetFont(c, f); int num2 = wb1.NumCellStyles; Assert.AreEqual(num1, num2); wb1.Close(); }
/// <summary> /// 生成Excel的表头 /// </summary> /// <param name="sheet"></param> /// <param name="cols"></param> /// <param name="rowIndex"></param> /// <param name="colIndex"></param> /// <param name="style"></param> /// <returns></returns> private int MakeExcelHeader(ISheet sheet, IEnumerable <IGridColumn <TModel> > cols, int rowIndex, int colIndex, ICellStyle style) { var row = sheet.GetRow(rowIndex); if (row == null) { row = sheet.CreateRow(rowIndex); } int maxLevel = cols.Select(x => x.MaxLevel).Max(); //循环所有列 foreach (var col in cols) { //添加新单元格 var cell = row.CreateCell(colIndex); cell.CellStyle = style; cell.SetCellValue(col.Title); var bcount = col.BottomChildren.Count(); var rowspan = 0; if (rowIndex >= 0) { rowspan = maxLevel - col.MaxLevel; } var cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex + rowspan, colIndex, colIndex + bcount - 1); sheet.AddMergedRegion(cellRangeAddress); if (rowspan > 0 || bcount > 1) { cell.CellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle.VerticalAlignment = VerticalAlignment.Center; } for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++) { IRow r = CellUtil.GetRow(i, sheet); for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) { ICell c = CellUtil.GetCell(r, (short)j); c.CellStyle = style; } } if (col.Children != null && col.Children.Any()) { MakeExcelHeader(sheet, col.Children, rowIndex + rowspan + 1, colIndex, style); } colIndex += bcount; } return(maxLevel); }
public void SetCellStylePropertyBorderWithShortAndEnum() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); ICell c = r.CreateCell(0); // A valid BorderStyle constant, as a Short CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, (short)BorderStyle.DashDot); Assert.AreEqual(BorderStyle.DashDot, c.CellStyle.BorderBottom); // A valid BorderStyle constant, as an Enum CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_TOP, BorderStyle.MediumDashDot); Assert.AreEqual(BorderStyle.MediumDashDot, c.CellStyle.BorderTop); wb.Close(); }
public void GetRow() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); IRow row1 = sh.CreateRow(0); // Get row that already exists IRow r1 = CellUtil.GetRow(0, sh); Assert.IsNotNull(r1); Assert.AreSame(row1, r1, "An existing row should not be reCreated"); // Get row that does not exist yet Assert.IsNotNull(CellUtil.GetRow(1, sh)); wb.Close(); }
public void TestSetAlignment() { // Create initial cell with default style IWorkbook wkb = new HSSFWorkbook(); ISheet sheet = wkb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); // Create a new cell with default style ICell cell2 = row.CreateCell(1); // Init a new cell style cloned from the one of cell 0 cell2.CellStyle = wkb.CreateCellStyle(); cell2.CellStyle.CloneStyleFrom(cell.CellStyle); // At this time cell style index should be different Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index); // Set an arbitraty cell style property to differentiate the two styles cell.CellStyle.Alignment = HorizontalAlignment.Right; // Try to make the same change so that CellUtil will get existing style CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Right); // Check that cell style has properly been set to HorizontalAlignment.Right Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Right); // Check that cell style index are the same again Assert.AreEqual(cell.CellStyle.Index, cell2.CellStyle.Index); // Init a new cell style cloned from the one of cell 0 cell2.CellStyle = wkb.CreateCellStyle(); cell2.CellStyle.CloneStyleFrom(cell.CellStyle); // Set an arbitraty cell style property to differentiate the two styles cell.CellStyle.Alignment = HorizontalAlignment.Left; // Try to make different change so that CellUtil will get new style CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Center); // Check that cell style has alignement property set to HorizontalAlignment.Center Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Center); // Check that cell style index are different Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index); }
public void GetCell() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); IRow row = sh.CreateRow(0); ICell A1 = row.CreateCell(0); // Get cell that already exists ICell a1 = CellUtil.GetCell(row, 0); Assert.IsNotNull(a1); Assert.AreSame(A1, a1, "An existing cell should not be reCreated"); // Get cell that does not exist yet Assert.IsNotNull(CellUtil.GetCell(row, 1)); wb.Close(); }
public void SetFillForegroundColorBeforeFillBackgroundColorEnum() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); ICell A1 = wb1.CreateSheet().CreateRow(0).CreateCell(0); Dictionary <String, Object> properties = new Dictionary <String, Object>(); // FIXME: Use FillPattern.BRICKS enum properties.Add(CellUtil.FILL_PATTERN, FillPattern.Bricks); properties.Add(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.Blue.Index); properties.Add(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.Red.Index); CellUtil.SetCellStyleProperties(A1, properties); ICellStyle style = A1.CellStyle; // FIXME: Use FillPattern.BRICKS enum Assert.AreEqual(FillPattern.Bricks, style.FillPattern, "fill pattern"); Assert.AreEqual(IndexedColors.Blue, IndexedColors.FromInt(style.FillForegroundColor), "fill foreground color"); Assert.AreEqual(IndexedColors.Red, IndexedColors.FromInt(style.FillBackgroundColor), "fill background color"); }
public void UpdateColumn(ListViewColumn col, object handle, ListViewColumnChange change) { var column = (GridViewColumn)handle; column.CellTemplate = new DataTemplate { VisualTree = CellUtil.CreateBoundColumnTemplate(Context, Frontend, col.Views) }; if (col.HeaderView != null) { column.HeaderTemplate = new DataTemplate { VisualTree = CellUtil.CreateBoundCellRenderer(Context, Frontend, col.HeaderView) } } ; else { column.Header = col.Title; } }
private void copyDataAndFormat(ICell srcCell, ICell dstCell) { // コピー元のCellType(文字列とか数値とか)による分類 switch (srcCell.CellType) { // 文字列 case CellType.String: dstCell.SetCellValue(sanitize(srcCell.ToString())); break; // 数値・通貨 case CellType.Numeric: // 日付を含むことがある if (DateUtil.IsCellDateFormatted(srcCell)) { dstCell.SetCellValue(srcCell.DateCellValue); } else { dstCell.SetCellValue(srcCell.NumericCellValue); } break; // 真偽値 case CellType.Boolean: dstCell.SetCellValue(srcCell.BooleanCellValue); break; // そのほかは文字列型とみなす default: dstCell.SetCellValue(sanitize(srcCell.ToString())); break; } // CellTypeをコピー dstCell.SetCellType(srcCell.CellType); // スタイルをコピー var srcCellStyle = srcCell.CellStyle; CellUtil.SetCellStyleProperty(dstCell, CellUtil.DATA_FORMAT, srcCellStyle.DataFormat); }
public void SetFontFromDifferentWorkbook() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); IWorkbook wb2 = _testDataProvider.CreateWorkbook(); IFont font1 = wb1.CreateFont(); IFont font2 = wb2.CreateFont(); // do something to make font1 and font2 different // so they are not same or Equal. font1.IsItalic = true; ICell A1 = wb1.CreateSheet().CreateRow(0).CreateCell(0); // okay CellUtil.SetFont(A1, font1); // font belongs to different workbook try { CellUtil.SetFont(A1, font2); Assert.Fail("setFont not allowed if font belongs to a different workbook"); } catch (ArgumentException e) { if (e.Message.StartsWith("Font does not belong to this workbook")) { // expected } else { throw e; } } finally { wb1.Close(); wb2.Close(); } }
public object AddColumn(ListViewColumn col) { var column = new GridViewColumn(); column.CellTemplate = new DataTemplate { VisualTree = CellUtil.CreateBoundColumnTemplate(Context, Frontend, col.Views) }; if (col.HeaderView != null) { column.HeaderTemplate = new DataTemplate { VisualTree = CellUtil.CreateBoundCellRenderer(Context, Frontend, col.HeaderView) } } ; else { column.Header = col.Title; } this.view.Columns.Add(column); return(column); }
public void UpdateColumn(ListViewColumn column, object handle, ListViewColumnChange change) { var col = ((GridViewColumn)handle); switch (change) { case ListViewColumnChange.Title: col.Header = column.Title; break; case ListViewColumnChange.Cells: var cellTemplate = CellUtil.CreateBoundColumnTemplate(Context, this, column.Views); col.CellTemplate = new DataTemplate { VisualTree = cellTemplate }; int index = Tree.View.Columns.IndexOf(col); if (index == 0) { var dockFactory = CreateExpanderDock(); dockFactory.AppendChild(cellTemplate); col.CellTemplate.VisualTree = dockFactory; } MapColumn(column, col); break; case ListViewColumnChange.Alignment: var style = new Style(typeof(GridViewColumnHeader)); style.Setters.Add(new Setter(Control.HorizontalContentAlignmentProperty, Util.ToWpfHorizontalAlignment(column.Alignment))); col.HeaderContainerStyle = style; break; } }
public void SetCellStyleProperty() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); ICell c = r.CreateCell(0); // Add a border should create a new style int styCnt1 = wb.NumCellStyles; CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); int styCnt2 = wb.NumCellStyles; Assert.AreEqual(styCnt1 + 1, styCnt2); // Add same border to another cell, should not create another style c = r.CreateCell(1); CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); int styCnt3 = wb.NumCellStyles; Assert.AreEqual(styCnt2, styCnt3); wb.Close(); }
public void SetVerticalAlignmentEnum() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); IRow row = sh.CreateRow(0); ICell A1 = row.CreateCell(0); ICell B1 = row.CreateCell(1); // Assumptions Assert.AreEqual(A1.CellStyle, B1.CellStyle); // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call. // HSSFCellStyle wraps an underlying style record, and the underlying // style record is the same between multiple getCellStyle() calls. Assert.AreEqual(VerticalAlignment.Bottom, A1.CellStyle.VerticalAlignment); Assert.AreEqual(VerticalAlignment.Bottom, B1.CellStyle.VerticalAlignment); // get/set alignment modifies the cell's style CellUtil.SetVerticalAlignment(A1, VerticalAlignment.Top); Assert.AreEqual(VerticalAlignment.Top, A1.CellStyle.VerticalAlignment); // get/set alignment doesn't affect the style of cells with // the same style prior to modifying the style Assert.AreNotEqual(A1.CellStyle, B1.CellStyle); Assert.AreEqual(VerticalAlignment.Bottom, B1.CellStyle.VerticalAlignment); wb.Close(); }
public void CreateCell() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); IRow row = sh.CreateRow(0); ICellStyle style = wb.CreateCellStyle(); style.WrapText = (/*setter*/ true); // calling CreateCell on a non-existing cell should create a cell and Set the cell value and style. ICell F1 = CellUtil.CreateCell(row, 5, "Cell Value", style); Assert.AreSame(row.GetCell(5), F1); Assert.AreEqual("Cell Value", F1.StringCellValue); Assert.AreEqual(style, F1.CellStyle); // should be Assert.AreSame, but a new HSSFCellStyle is returned for each GetCellStyle() call. // HSSFCellStyle wraps an underlying style record, and the underlying // style record is the same between multiple GetCellStyle() calls. // calling CreateCell on an existing cell should return the existing cell and modify the cell value and style. ICell f1 = CellUtil.CreateCell(row, 5, "Overwritten cell value", null); Assert.AreSame(row.GetCell(5), f1); Assert.AreSame(F1, f1); Assert.AreEqual("Overwritten cell value", f1.StringCellValue); Assert.AreEqual("Overwritten cell value", F1.StringCellValue); Assert.AreEqual(style, f1.CellStyle, "cell style should be unChanged with CreateCell(..., null)"); Assert.AreEqual(style, F1.CellStyle, "cell style should be unChanged with CreateCell(..., null)"); // test CreateCell(row, column, value) (no CellStyle) f1 = CellUtil.CreateCell(row, 5, "Overwritten cell with default style"); Assert.AreSame(F1, f1); wb.Close(); }
/// <summary> /// 获取单元对象 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> private ICell GetCell(int row, int col) { IRow curRow = GetRow(row); return(CellUtil.GetCell(curRow, col)); }
public void TestBug55752() { IWorkbook wb = new XSSFWorkbook(); try { ISheet sheet = wb.CreateSheet("test"); for (int i = 0; i < 4; i++) { IRow row = sheet.CreateRow(i); for (int j = 0; j < 2; j++) { ICell cell = row.CreateCell(j); cell.CellStyle = (wb.CreateCellStyle()); } } // set content IRow row1 = sheet.GetRow(0); row1.GetCell(0).SetCellValue("AAA"); IRow row2 = sheet.GetRow(1); row2.GetCell(0).SetCellValue("BBB"); IRow row3 = sheet.GetRow(2); row3.GetCell(0).SetCellValue("CCC"); IRow row4 = sheet.GetRow(3); row4.GetCell(0).SetCellValue("DDD"); // merge cells CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1); sheet.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1); sheet.AddMergedRegion(range2); CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1); sheet.AddMergedRegion(range3); Assert.AreEqual(0, range3.FirstColumn); Assert.AreEqual(1, range3.LastColumn); Assert.AreEqual(2, range3.LastRow); CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1); sheet.AddMergedRegion(range4); // set border RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb); row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin; row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin; ICell cell0 = CellUtil.GetCell(row3, 0); CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); ICell cell1 = CellUtil.GetCell(row3, 1); CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb); // write to file Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite); try { wb.Write(stream); } finally { stream.Close(); } } finally { wb.Close(); } }
public static ICell CreateCell(IRow row, int column, String value, HSSFCellStyle style) { return((HSSFCell)CellUtil.CreateCell(row, column, value, style)); }
public ICell CopyCell(int sourceIndex, int targetIndex) { return(CellUtil.CopyCell(this, sourceIndex, targetIndex)); }
public static IRow GetRow(int rowIndex, HSSFSheet sheet) { return((HSSFRow)CellUtil.GetRow(rowIndex, sheet)); }
public static void SetFont(ICell cell, HSSFWorkbook workbook, HSSFFont font) { CellUtil.SetFont(cell, font); }
public static ICell GetCell(IRow row, int columnIndex) { return((HSSFCell)CellUtil.GetCell(row, columnIndex)); }
/// <summary> /// 获取行对象 /// </summary> /// <param name="row"></param> /// <returns></returns> private IRow GetRow(int row) { return(CellUtil.GetRow(row, currentSheet)); }
public ICell CopyCellTo(int targetIndex) { return(CellUtil.CopyCell(this.Row, this.ColumnIndex, targetIndex)); }
public static ICell TranslateUnicodeValues(ICell cell) { CellUtil.TranslateUnicodeValues(cell); return(cell); }
public static void SetCellStyleProperty(ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue) { CellUtil.SetCellStyleProperty(cell, propertyName, propertyValue); }
void SetCell(IWorkbook workbook, ISheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); var row = sheet.GetRow(r) == null?sheet.CreateRow(r) : sheet.GetRow(r); if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.CreateCell(c); if (value != null) { cell.SetCellValue(value); } var style = workbook.CreateCellStyle(); //设置颜色 if (color.HasValue) { style.FillForegroundColor = color.Value; style.FillPattern = FillPattern.SolidForeground; } //设置字体 var font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; style.SetFont(font); //设置边框 if (isBorder) { style.BorderLeft = borderStyle; style.BorderRight = borderStyle; style.BorderTop = borderStyle; style.BorderBottom = borderStyle; for (int i = rang.FirstRow; i <= rang.LastRow; i++) { var borderRow = CellUtil.GetRow(i, sheet); for (int j = rang.FirstColumn; j <= rang.LastColumn; j++) { var singleCell = CellUtil.GetCell(borderRow, (short)j); singleCell.CellStyle = style; } } } else { cell.CellStyle = style; } cell.CellStyle = style; //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } }