private void SetStyleForRow()
{
#region 标题行
cellstyleForOneRow = (HSSFCellStyle)WorkBook.CreateCellStyle();
HSSFFont font = (HSSFFont)WorkBook.CreateFont();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 15;
cellstyleForOneRow.SetFont(font);
#endregion
#region 校验失败行
cellstyleForVerifyFailed = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifyFailed.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
cellstyleForVerifyFailed.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font1 = (HSSFFont)WorkBook.CreateFont();
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 12;
cellstyleForVerifyFailed.SetFont(font1);
#endregion
#region 校验成功行
cellstyleForVerifySuccess = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifySuccess.FillForegroundColor = HSSFColor.SKY_BLUE.index;
cellstyleForVerifySuccess.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font2 = (HSSFFont)WorkBook.CreateFont();
font2.Color = HSSFColor.BLACK.index;
font2.FontHeightInPoints = 12;
cellstyleForVerifySuccess.SetFont(font2);
#endregion
}
protected ICell ObterCelulaData(int numeroLinha, int numeroColuna, string nomeAba, bool borda, IndexedColors cor = null)
{
ICell celula = ObterCelula(numeroLinha, numeroColuna, nomeAba);
var estiloTemporario = (XSSFCellStyle)WorkBook.CreateCellStyle();
estiloTemporario = (XSSFCellStyle)EstiloCelulaCorpoData.Clone();
if (!borda)
{
estiloTemporario.BorderBottom = BorderStyle.None;
estiloTemporario.BorderTop = BorderStyle.None;
estiloTemporario.BorderLeft = BorderStyle.None;
estiloTemporario.BorderRight = BorderStyle.None;
}
if (cor != null)
{
estiloTemporario.FillForegroundColor = cor.Index;
estiloTemporario.FillPattern = FillPattern.SolidForeground;
}
celula.CellStyle = estiloTemporario;
return(celula);
}
private void CriarEstiloTitulo()
{
EstiloCelulaTitulo = (XSSFCellStyle)WorkBook.CreateCellStyle();
EstiloCelulaTitulo.Alignment = HorizontalAlignment.Center;
EstiloCelulaTitulo.VerticalAlignment = VerticalAlignment.Center;
EstiloCelulaTitulo.SetFont(FonteCabecalho);
EstiloCelulaTitulo.WrapText = true;
}
public void SetHeadersBold()
{
var font = WorkBook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Arial";
font.IsBold = true;
HeadersStyle = WorkBook.CreateCellStyle();
HeadersStyle.SetFont(font);
}
///// <summary>
///// 细线
///// </summary>
//private ICellStyle _styleThin = null;
//private ICellStyle StyleThin
//{
// //https://www.cnblogs.com/kingangWang/archive/2012/02/06/2339502.html
// ///
// get
// {
// if (_styleThin == null)
// {
// _styleThin = WorkBook.CreateCellStyle();
// _styleThin.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// //_titleStyle.BorderBottom = BorderStyle.Dashed;
// //_titleStyle.BorderLeft = BorderStyle.Dotted;
// //_titleStyle.BorderRight = BorderStyle.Medium;
// _styleThin.BorderTop = BorderStyle.Thin;
// //_titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
// //_titleStyle.FillPattern = FillPattern.SolidForeground;
// //NPOI.SS.UserModel.IFont _fontTitle = _workBook.CreateFont();
// //_fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
// //_fontTitle.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
// //_fontTitle.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
// //short s = (short)new NpoiStyle().Ss();
// //_titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;// NPOI.HSSF.Util.HSSFColor.Red.Index;
// //_titleStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;// NPOI.HSSF.Util.HSSFColor.Red.Index;
// //_titleStyle.WrapText = true;
// //_titleStyle.SetFont(_fontTitle);
// }
// return _styleThin;
// }
//}
/// <summary>
/// 获取单元线条样式
/// </summary>
public ICellStyle GetCellStyle(BorderStyle leftStyle, BorderStyle topStyle, BorderStyle rightStyle, BorderStyle bottomStyle)
{
ICellStyle cellStyle = WorkBook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//cellStyle.BorderLeft = leftStyle;
//cellStyle.BorderTop = topStyle;
//cellStyle.BorderRight = rightStyle;
//cellStyle.BorderBottom = bottomStyle;
return(cellStyle);
}
private void CriarEstiloCabecalho()
{
EstiloCelulaCabecalho = (XSSFCellStyle)WorkBook.CreateCellStyle();
EstiloCelulaCabecalho.Alignment = HorizontalAlignment.Center;
EstiloCelulaCabecalho.VerticalAlignment = VerticalAlignment.Center;
EstiloCelulaCabecalho.SetFont(FonteCabecalho);
EstiloCelulaCabecalho.WrapText = true;
EstiloCelulaCabecalho.BorderBottom = BorderStyle.Thin;
EstiloCelulaCabecalho.BorderTop = BorderStyle.Thin;
EstiloCelulaCabecalho.BorderLeft = BorderStyle.Thin;
EstiloCelulaCabecalho.BorderRight = BorderStyle.Thin;
}
private XSSFCellStyle CriarEstiloCelulaCorpo()
{
var estilo = (XSSFCellStyle)WorkBook.CreateCellStyle();
estilo.VerticalAlignment = VerticalAlignment.Center;
estilo.SetFillForegroundColor(new XSSFColor(Color.Transparent));
estilo.FillPattern = FillPattern.NoFill;
estilo.SetFont(FonteCelulaCorpo);
estilo.BorderBottom = BorderStyle.Thin;
estilo.BorderTop = BorderStyle.Thin;
estilo.BorderLeft = BorderStyle.Thin;
estilo.BorderRight = BorderStyle.Thin;
return(estilo);
}
protected override void WriteToExcelSpreadsheet()
{
var sheet = base.WorkBook.CreateSheet("Sample Form");
if (this.data == null || data.FirstOrDefault() == null)
{
return;
}
//Create a subject area merged all content columns in the first row.
var mainHeaderRange = new CellRangeAddress(0, 0, 0, 7);
sheet.AddMergedRegion(mainHeaderRange);
//Create the subject cell in the subject area and fill with subject value
//Since the first content row merged all columns, there is only one big cell in the first row.
string subject = GenerateSubject();
sheet.CreateRow(0).CreateCell(0).SetCellValue(subject);
//Create header row and header column.
var headRow = sheet.CreateRow(1);
int colIndex = 0;
headRow.CreateCell(colIndex++).SetCellValue("Time");
headRow.CreateCell(colIndex++).SetCellValue("Sunnday");
headRow.CreateCell(colIndex++).SetCellValue("Monday");
headRow.CreateCell(colIndex++).SetCellValue("Tuesday");
headRow.CreateCell(colIndex++).SetCellValue("Wednesday");
headRow.CreateCell(colIndex++).SetCellValue("Thursday");
headRow.CreateCell(colIndex++).SetCellValue("Friday");
headRow.CreateCell(colIndex).SetCellValue("Saturday");
int rowIndex = 2;
DateTime calendarStart = DateTime.Parse("8:00AM");
for (DateTime time = calendarStart; time <= DateTime.Parse("5:00PM"); time = time.AddMinutes(30))
{
sheet.CreateRow(rowIndex++).CreateCell(0).SetCellValue(time.ToString("HH:mm"));
}
var cellStyle = WorkBook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.WrapText = true;
rowIndex = 2;
colIndex = 1;
foreach (var calendarEvent in data)
{
int x = Convert.ToInt32((calendarEvent.StartTime - calendarStart).TotalMinutes / 30);
int y = (int)calendarEvent.Weekday;
if (calendarEvent.HalfHourCount > 1)
{
var eventRange = new CellRangeAddress(rowIndex + x, rowIndex + x + calendarEvent.HalfHourCount - 1, colIndex + y, colIndex + y);
sheet.AddMergedRegion(eventRange);
var cell = sheet.GetRow(rowIndex + x).CreateCell(colIndex + y);
if (cell != null)
{
cell.SetCellValue(calendarEvent.Memo);
cell.CellStyle = cellStyle;
}
}
}
}
public XSSFCellStyle CriarEstilo()
{
return((XSSFCellStyle)WorkBook.CreateCellStyle());
}
