newFile = new FileInfo(@"d:\test.xlsx");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//获得授权
using (ExcelPackage package = new ExcelPackage(newFile))
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");//创建worksheet
worksheet.Cells[1, 1].Value = "名称";
worksheet.Cells[1, 2].Value = "价格";
worksheet.Cells[1, 3].Value = "销量";
worksheet.Cells[2, 1].Value = "大米";
worksheet.Cells[2, 2].Value = 56;
worksheet.Cells[2, 3].Value = 100;
worksheet.Cells[3, 1].Value = "玉米";
worksheet.Cells[3, 2].Value = 45;
worksheet.Cells[3, 3].Value = 150;
worksheet.Cells[4, 1].Value = "小米";
worksheet.Cells[4, 2].Value = 38;
worksheet.Cells[4, 3].Value = 130;
worksheet.Cells[5, 1].Value = "糯米";
worksheet.Cells[5, 2].Value = 22;
worksheet.Cells[5, 3].Value = 200;
worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
worksheet.Cells[1, 4, 1, 5].Merge = true;//合并单元格
worksheet.Cells.Style.WrapText = true;//自动换行
worksheet.PrinterSettings.TopMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.HeaderMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.FooterMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.LeftMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.BottomMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.HorizontalCentered = true;
//垂直居中
worksheet.PrinterSettings.VerticalCentered = true;
//水平居中
worksheet.PrinterSettings.Orientation = eOrientation.Landscape;//横向打印
package.Save();//保存excel
worksheet.PrinterSettings.TopMargin = 0.1M / 2.54M;//设置打印边距
worksheet.PrinterSettings.RightMargin = 0.1M / 2.54M;
worksheet.PrinterSettings.LeftMargin = 0.1M / 2.54M;
worksheet.PrinterSettings.BottomMargin = 0.1M / 2.54M;//设置打印边距
worksheet.PrinterSettings.HorizontalCentered = true;
worksheet.PrinterSettings.Orientation = eOrientation.Landscape;//横向打印
//导出Excel
Response.Clear();
//Response.AddHeader("Content-Disposition", "attachment; filename=" + "file_" + DateTime.Now.ToString("yyyyMMddHHmmssms") + ".xlsx");
string sfilename = "file_" + DateTime.Now.ToString("yyyyMMddHHmmssms") + ".xlsx";
sfilename = HttpUtility.UrlEncode(sfilename, Encoding.UTF8);
Response.AddHeader("Content-Disposition", "attachment; filename=" + sfilename);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet";
Response.BinaryWrite(package.GetAsByteArray());
Response.Flush();
Response.End();
柱状填充颜色：
series1.Fill.Color=Color.Red;
线条填充颜色
series1.LineColor=Color.Red;
记号画线颜色:
series1.MarkLineColor=Color.Red;(好像是记不清）
worksheet.PrinterSettings.Orientation = eOrientation.Portrait;//打印
worksheet.PrinterSettings.PaperSize = ePaperSize.A4;//设置纸张样式
worksheet.PrinterSettings.FitToPage = true;//设置集中打印
worksheet.PrinterSettings.FitToWidth = 1;//把所有列在一页打出
worksheet.PrinterSettings.FitToHeight = 0;
worksheet.PrinterSettings.TopMargin = 1.00M / 2.54M;//设置打印边距
worksheet.PrinterSettings.RightMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.LeftMargin = 0.64M / 2.54M;
worksheet.PrinterSettings.BottomMargin = 1.00M / 2.54M;//设置打印边距
worksheet.PrinterSettings.Orientation = eOrientation.Landscape;//横向打印
int firstRow = worksheet.Dimension.End.Row + 1;//非常好用，但是不可用于第一行
worksheet.Column(index++).Width = 17；
worksheet.View.FreezePanes(2, 1);//冻结第一行
worksheet.Cells[rowNumber, 3].Style.Numberformat.Format = "yyyy-mm-dd";//日期
worksheet.Cells[rowNumber, 9].Style.Numberformat.Format = "0.00";//重量，如果需要加符号的，例如"$0.00"，即可
worksheet.Hidden = eWorkSheetHidden.Hidden;//隐藏sheet
worksheet.Column(1).Hidden = true;//隐藏某一列
worksheet.Row(1).Hidden = true;//隐藏某一行
合并单元格
worksheet.Cells[rowNumber + 1, 1, rowNumber + 1, 8].Merge = true;
获取单元格的值
string designNo = Convert.ToString(worksheet.Cells[row, 2].Value).Trim();
计算单元格公式
worksheet.Cells[rowNumber, 9].Formula = string.Format("=SUM(I2:I{0})", rowNumber);
单元格赋值
worksheet.SetValue(row,col,value);//这种赋值方法性能好一些
单元格边框
worksheet.Cells[rowNumber, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells.Style.Border.Left.Style= ExcelBorderStyle.Thin;
单元格背景颜色
worksheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(192, 192, 192));
worksheet.Cells.Style.WrapText= true;
worksheet.Cells[2, 18].Style.Font.Bold = true;
获取某一个区域
var rangeData= worksheet.Cells[fromRow, fromCol, toRow, toCol];
worksheet.Cells["A1:E4"].AutoFilter = true;//两种方法都可以
worksheet.Cells[1,1,1,1].AutoFilter = true;
作者：jerome6668
链接：https://www.jianshu.com/p/9d9806e0d4f5
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
https://www.cnblogs.com/rumeng/p/3785775.html
https://blog.csdn.net/q22200p/article/details/119115456
https://www.cnblogs.com/sczw-maqing/p/3365395.html
https://blog.csdn.net/BUBsky/article/details/88707620
分类
C#
、
EXCEL
、
学习足迹
worksheet.Cells[“A1”].Value = “X”;
worksheet.Cells[“B1”].Value = “Y”;
worksheet.Cells[“A2”].Value = 1;
worksheet.Cells[“B2”].Value = 10;
worksheet.Cells[“A3”].Value = 2;
worksheet.Cells[“B3”].Value = 20;
worksheet.Cells[“A4”].Value = 3;
worksheet.Cells[“B4”].Value = 30;
worksheet.Cells[“A5”].Value = 4;
worksheet.Cells[“B5”].Value = 40;
worksheet.Cells[“A6”].Value = 5;
worksheet.Cells[“B6”].Value = 50;
worksheet.Cells[“A7”].Value = 6;
worksheet.Cells[“B7”].Value = 60;
worksheet.Cells[“A8”].Value = 7;
worksheet.Cells[“B8”].Value = 70;
worksheet.Cells[“A9”].Value = 8;
worksheet.Cells[“B9”].Value = 80;
worksheet.Cells[“A10”].Value = 9;
worksheet.Cells[“B10”].Value = 90;
worksheet.Cells[“A11”].Value = 10;
worksheet.Cells[“B11”].Value = 100;
ExcelRange chartData = worksheet.Cells[“A1:B10”];
var rule = chartData.ConditionalFormatting.AddGreaterThan();
// 设置样式
// rule.Style.Font.Color.Color = Color.Red;
rule.Formula = “50”;
rule.Style.Fill.BackgroundColor.SetColor(Color.Green);
rule.Style.Font.Color.SetColor(Color.Orange);
本段代码的作用：导出Excel,将值大于50的背景颜色设为绿色，字体设为黄色·
