[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;
}
}
