[Source] C# EPPlus Bulk Style Cells

//1，赋值
worksheet. Cells[int row, int col]. Value = "xxx";
//或者
worksheet. Cells["A1"]. Value = "xxx";
//或者
worksheet. SetValue(row,col,value);

//2，单元格合并

//指定开始行，开始列，结束行，结束列
worksheet. Cells[fromRow, fromCol, toRow, toCol]. Merge = true;

//行合并
worksheet. Cells["A1:A5"]. Merge = true; Merge rows 1-5 of column A

//列合并
worksheet. Cells["A1:G1"]. Merge = true; Merge columns A-G in row 1

//3，样式

worksheet. Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; The level is centered, and the overall situation is overall
worksheet. Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; Vertical centering, global
worksheet. Cells.AutoFitColumns(); Big picture
worksheet. Cells.Style.WrapText = true; Wrap, global
worksheet.Cells.Style.Font.Name = "Song style"; Big picture

worksheet. Cells["A1"]. Style.VerticalAlignment = ExcelVerticalAlignment.Center; Centered vertically, targeting only specific cells

worksheet. Cells["A1:A5"]. Style.VerticalAlignment = ExcelVerticalAlignment.Center; Vertically centered, only for a range of cells

worksheet. Cells[1, 1]. Style.Font.Bold = true; The font is bold
worksheet. Cells[1, 1]. Style.Font.Color.SetColor(Color.White); Font color
worksheet. Cells[1, 1]. Style.Font.Size = 12; Font size


worksheet. Cells["A5"]. Style.TextRotation = 180; Content rotation

worksheet. Cells["P5"]. Style.SetTextVertical(); The text is arranged vertically


//调整行高
double rowHeight = 15;
worksheet. Row(1). Height = rowHeight;

//调整列宽
double columnWidth = 50;
worksheet. Column(1). Width = columnWidth;

//自动适应长宽

worksheet. Column(1). BestFit = true;

//公式计算


worksheet. Cells["D2:D5"]. Formula = "B2*C2"; This is the formula for multiplication, which means that the second column is multiplied by the value of the third column and assigned to the fourth column, which is relatively simple and straightforward
worksheet. Cells[6, 2, 6, 4]. Formula = string. Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 2, 5, 2). Address); This is an automatic summation method, and you need to understand the usage of subtotal yourself
    As for other formulas, you can try them yourself.

//设置单元格格式
worksheet. Cells[5, 3]. Style.Numberformat.Format = "#,##0.00"; This is to keep two decimal places
There are many more cell formatting, I won't list them one by one, basically the Epplus that can be implemented on excel can be implemented, you can go to the source code of Epplus to see.


//　设置单元格背景样式


worksheet. Cells[1, 1]. Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet. Cells[1, 1]. Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128)); Set the cell background color

//设置单元格边框，两种方法

worksheet. Cells[1, 1]. Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); Set all borders for cells
worksheet. Cells[1, 1]. Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Set the bottom border style and color of the cell separately (top, bottom, left, and right can be set separately)
worksheet. Cells[1, 1]. Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));
Set the row height and column width of the cells

worksheet. Cells.Style.ShrinkToFit = true; Cells automatically adapt to size
worksheet. Row(1). Height = 15; Set the row height
worksheet. Row(1). CustomHeight = true; Automatically adjust the line height
worksheet. Column(1). Width = 15; Set the column width

//4、设置sheet背景

worksheet. View.ShowGridLines = false; Remove the grid lines of the sheet
worksheet. Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet. Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray); Set the background color
worksheet. BackgroundImage.Image = Image.FromFile(@"firstbg.jpg"); Set a background image
//5、插入图片和形状

   Insert picture

ExcelPicture picture = worksheet. Drawings.AddPicture("logo", Image.FromFile(@"firstbg.jpg")); Insert picture
picture. SetPosition(100, 100); Set the position of the picture
picture. SetSize(100, 100); Set the size of the image

//插入形状

ExcelShape shape = worksheet. Drawings.AddShape("shape", eShapeStyle.Rect); Insert shape
shape. Font.Color = Color.Red; Set the font color of the shape
shape. Font.Size = 15; Font size
shape. Font.Bold = true; Font thickness
shape. Fill.Style = eFillStyle.NoFill; Sets the fill style of the shape
shape. Border.Fill.Style = eFillStyle.NoFill; Border style
shape. SetPosition(200, 300); Position of the shape
shape. SetSize(80, 30); The size of the shape
shape. Text = "test"; The content of the shape

//6、超链接

    Add a hyperlink to the image

ExcelPicture picture = worksheet. Drawings.AddPicture("logo", Image.FromFile(@"firstbg.jpg"), new ExcelHyperLink("http:\\www.baidu.com", UriKind.Relative));
//给单元格加超链接


worksheet. Cells[1, 1]. Hyperlink = new ExcelHyperLink("http:\\www.baidu.com", UriKind.Relative);
//7、隐藏sheet

worksheet. Hidden = eWorkSheetHidden.Hidden; Hide sheet
worksheet. Column(1). Hidden = true; Hide a column
worksheet. Row(1). Hidden = true; Hide a row
}