EPPLUS 用法

获得授权：ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

先上实例：

            FileInfo newFile = new FileInfo(@"d:\test.xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();
                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