//表头制作
public void CreateHead(IWorkbook workBook, ISheet sheet, ICellStyle cellStyleHeadTitle, ExcelHeadAttribute eha)
{
IRow RowHeadTitle = sheet.CreateRow(eha.rowIndex);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(eha.firstRow, eha.lastRow, eha.firstCol, eha.lastCol));
RowHeadTitle.CreateCell(0).SetCellValue(eha.name);
/*****单元格样式 start******/
cellStyleHeadTitle.WrapText = true;
//高度
RowHeadTitle.HeightInPoints = eha.HeightInPoints; //2 * sheet.DefaultRowHeight / 10;
//字体设置,字体要调用CreateFont()
IFont fontheadTitle = workBook.CreateFont();
fontheadTitle.FontHeightInPoints = eha.fontSize;
//HSSFColor.OliveGreen.Black.Index
fontheadTitle.Color = eha.fontColor;
cellStyleHeadTitle.SetFont(fontheadTitle);
//这里调试出来的,样式一定要给到单元格才有效
RowHeadTitle.Cells[0].CellStyle = cellStyleHeadTitle;
}
private void DataWriteToSheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName, int mergeCellNum, string mergeCellContent)
{
ISheet sheet = book.CreateSheet(sheetName);
IRow header = null; int rowIndex = 0;
//表头
if (!string.IsNullOrEmpty(mergeCellContent) && mergeCellNum > 0)
{
var cellStyleHeadNotice = book.CreateCellStyle();
cellStyleHeadNotice.Alignment = HorizontalAlignment.Left; //居中显示
cellStyleHeadNotice.VerticalAlignment = VerticalAlignment.Top; //垂直居中
ExcelHeadAttribute ehanotice = new ExcelHeadAttribute()
{
rowIndex = 0,
firstRow = 0,
lastRow = 0,
firstCol = 0,
lastCol = mergeCellNum - 1,
fontColor = 10,
fontSize = 10,
HeightInPoints = 30,
name = mergeCellContent
};
CreateHead(book, sheet, cellStyleHeadNotice, ehanotice);
header = sheet.CreateRow(1);
rowIndex = 2;
}
else
{
header = sheet.CreateRow(0);
rowIndex = 1;
}
/*****单元格样式 start******/
var cellStyleHead = book.CreateCellStyle();
cellStyleHead.Alignment = HorizontalAlignment.Center; //居中显示
cellStyleHead.VerticalAlignment = VerticalAlignment.Top; //垂直居中
cellStyleHead.WrapText = true;
//高度
header.HeightInPoints = 50; //2 * sheet.DefaultRowHeight / 10;
//字体设置,字体要调用CreateFont()
IFont fonthead = book.CreateFont();
fonthead.FontHeightInPoints = 10;
fonthead.Color = HSSFColor.Red.Index;
cellStyleHead.SetFont(fonthead);
//这里调试出来的,样式一定要给到单元格才有效
//header.Cells[0].CellStyle = cellStyleHead;
/*****单元格样式 end******/
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
header.Cells[i].Row.HeightInPoints = 20;
//单元格样式
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index;
//设置单元格上下左右边框线
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//是否换行
cellStyle.WrapText = true;
header.Cells[i].CellStyle = cellStyle;
//字体大小
IFont cellfont = book.CreateFont();
cellfont.FontHeightInPoints = 14;
cellStyle.SetFont(cellfont);
sheet.SetColumnWidth(i, 20 * 256);
}
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}