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);
}
public void InsertText(string text, int row, int col)
{
IRow r = CellUtil.GetRow(row, currentSheet);
if (r == null)
{
r = currentSheet.CreateRow(row);
}
ICell cell = CellUtil.CreateCell(r, col, text, CreateStyle());
}
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;
}
}
/// <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 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();
}
/// <summary>
/// 获取行对象
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private IRow GetRow(int row)
{
return(CellUtil.GetRow(row, currentSheet));
}
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 IRow GetRow(int rowIndex, HSSFSheet sheet)
{
return((HSSFRow)CellUtil.GetRow(rowIndex, sheet));
}