//表头制作 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()); } } }