void SetCell(IWorkbook workbook, ISheet sheet, int r, int tor, int c, int toc, dynamic value, ICellStyle style)
{
var rang = new CellRangeAddress(r, tor, c, toc);
sheet.AddMergedRegion(rang);
var row = sheet.GetRow(r) == null?sheet.CreateRow(r) : sheet.GetRow(r);
var cell = row.CreateCell(c);
if (value != null)
{
cell.SetCellValue(value);
}
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;
}
}
cell.CellStyle = style;
}
private ICell GetCell(int sheetIndex, int rowIndex, int colIndex)
{
var sheet = npoi_workbook.GetSheetAt(sheetIndex);
var row = CellUtil.GetRow(rowIndex, sheet);
var cell = CellUtil.GetCell(row, colIndex);
return(cell);
}
private string CellAsString(IRow row, int col)
{
if (mSheet == null)
{
return(null);
}
ICell cell = CellUtil.GetCell(row, col);
return(cell.ToString());
}
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);
}
/// <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 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();
}
/// <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();
}
}
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;
}
}
public static ICell GetCell(IRow row, int columnIndex)
{
return((HSSFCell)CellUtil.GetCell(row, columnIndex));
}