c# EPPlus秘籍之Excel實現(xiàn)圖表導(dǎo)出

更新時間：2023年12月15日 10:40:28 作者：譚廣健

這篇文章主要為大家介紹了c# EPPlus秘籍之Excel實現(xiàn)圖表導(dǎo)出示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪

引言

之前受某大神推薦使用EPPlus作為excel之控件，簡直如獲一寶，大數(shù)據(jù)導(dǎo)出將不再麻煩，服務(wù)器也不必再安裝煩人的office控件.簡單易用更重要不用破解什么的。

大數(shù)據(jù)導(dǎo)出測試

今天測試了２點，一個是大數(shù)據(jù)導(dǎo)出測試５Ｗ簡直沒壓力。

代碼如下

　　　　　　FileInfo newFile = new FileInfo(@"c:\test1.xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(@"c:\test.xlsx");
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
                 for (int i = 1; i <= 50000; i++)
                 {
                     for (int j = 1; j <= 20; j++)
                     {
                         worksheet.Cells[i, j].Value = i + "|" + j;
                     }
                 }
                 package.Save();
                 MessageBox.Show("ok");
             }

于是再測試一下有什么特殊功能，發(fā)現(xiàn)有一個圖表導(dǎo)出功能也很不錯，分享代碼如下：

                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
                worksheet.Cells.Style.WrapText = true;
                worksheet.View.ShowGridLines = false;//去掉sheet的網(wǎng)格線
                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;
                using (ExcelRange range = worksheet.Cells[1, 1, 5, 3])
                {
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                using (ExcelRange range = worksheet.Cells[1, 1, 1, 3])
                {
                    range.Style.Font.Bold = true;
                    range.Style.Font.Color.SetColor(Color.White);
                    range.Style.Font.Name = "微軟雅黑";
                    range.Style.Font.Size = 12;
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
                }
                worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
                //eChartType中可以選擇圖表類型
                ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]);
                //設(shè)置圖表的x軸和y軸
                serie.HeaderAddress = worksheet.Cells[1, 3];//設(shè)置圖表的圖例
                chart.SetPosition(150, 10);//設(shè)置位置
                chart.SetSize(500, 300);//設(shè)置大小
                chart.Title.Text = "銷量走勢";//設(shè)置圖表的標(biāo)題
                chart.Title.Font.Color = Color.FromArgb(89, 89, 89);//設(shè)置標(biāo)題的顏色
                chart.Title.Font.Size = 15;//標(biāo)題的大小
                chart.Title.Font.Bold = true;//標(biāo)題的粗體
                chart.Style = eChartStyle.Style15;//設(shè)置圖表的樣式
                chart.Legend.Border.LineStyle = eLineStyle.Solid;
                chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);//設(shè)置圖例的樣式

效果

如下：

將圖片導(dǎo)入到excel

還有可以將圖片導(dǎo)入到excel中去，代碼如下：

ExcelPicture picture = worksheet.Drawings.AddPicture("logo", Image.FromFile(@"c:\QQ截圖20190403205944.jpg"));//插入圖片
picture.SetPosition(100, 100);//設(shè)置圖片的位置
picture.SetSize(100, 100);//設(shè)置圖片的大小

多個excel的權(quán)限

在最后弄多個excel的權(quán)限，加入后修改是需要口令的。

worksheet.Protection.IsProtected = true;//設(shè)置是否進行鎖定
worksheet.Protection.SetPassword("ABC");//設(shè)置密碼
worksheet.Protection.AllowAutoFilter = false;//下面是一些鎖定時權(quán)限的設(shè)置
worksheet.Protection.AllowDeleteColumns = false;
worksheet.Protection.AllowDeleteRows = false;
worksheet.Protection.AllowEditScenarios = false;
worksheet.Protection.AllowEditObject = false;
worksheet.Protection.AllowFormatCells = false;
worksheet.Protection.AllowFormatColumns = false;
worksheet.Protection.AllowFormatRows = false;
worksheet.Protection.AllowInsertColumns = false;
worksheet.Protection.AllowInsertHyperlinks = false;
worksheet.Protection.AllowInsertRows = false;
worksheet.Protection.AllowPivotTables = false;
worksheet.Protection.AllowSelectLockedCells = false;
worksheet.Protection.AllowSelectUnlockedCells = false;
worksheet.Protection.AllowSort = false;

加入excel的描述

還有一個秘籍，加入excel的描述：

epk.Workbook.Properties.Title = "excel的標(biāo)題";//設(shè)置excel的標(biāo)題 epk.Workbook.Properties.Author = "譚廣健";//作者 epk.Workbook.Properties.Comments = "這是備注";//備注 epk.Workbook.Properties.Company = "公司版權(quán)啥的";//公司

通過SQL語句直接導(dǎo)出Excel

 var db_data = ExecuteNonQuery("select * from SYS_LIST_SETTING", CommandType.Text);
     DataSet ds = new DataSet();
     ds.Tables.Add(db_data);
     DataSetToExcel(ds, @"c:\test1.xlsx");
     
 #region 讀數(shù)據(jù)庫
        public static DataTable ExecuteNonQuery(String cmdText, CommandType ct)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                    {
                        cmd.CommandType = ct;
                        using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            dt.Load(sdr);
                        }
                    }
                }
                catch (Exception e)
                {
                    System.Diagnostics.Debug.WriteLine(e.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    System.Diagnostics.Debug.WriteLine(dt.Rows.Count);
                }
            }
            return dt;
        }
 #endregion
 #region DataTable to Excel2007 
     private static void DataSetToExcel(DataSet dataSet, string filePath)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                foreach (DataTable dataTable in dataSet.Tables)
                {
                    ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(dataTable.TableName);
                    workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);
                }

                pck.SaveAs(new FileInfo(filePath));
            }
        }
 #endregion

以上就是c# EPPlus秘籍之Excel實現(xiàn)圖表導(dǎo)出的詳細內(nèi)容，更多關(guān)于c# EPPlus圖表導(dǎo)出Excel的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: