EPPLUS 用法

wenhongshen發表於2025-01-24

獲得授權：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

相關文章