C# EPPlus導出EXCEL,並生成Chart表

一  在negut添加EPPlus.dll庫文件。

之前有寫過直接只用Microsoft.Office.Interop.Excel 導出EXCEL，並生成Chart表，非常耗時，所以找了個EPPlus控制項。

二 代碼部分

System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("序號", typeof(int));
            dt.Columns.Add("數據1", typeof(int));
            dt.Columns.Add("數據2", typeof(int));
            Random r = new Random();
            for (int i = 0; i < 20; i++)
            {
                if (i == 6 || i == 16) continue;
                dt.Rows.Add(i + 1, r.Next(50), r.Next(60));
            }
            ////新建一個 Excel 文件
            //string filePath = @"C:\Users\Lenovo\Desktop\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
            //FileStream fileStream = new FileStream(filePath, FileMode.Create);

            ////載入這個 Excel 文件
            //ExcelPackage package = new ExcelPackage(fileStream);

            //載入這個 Excel 文件
            ExcelPackage package = new ExcelPackage();

            // 添加一個 sheet 表
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("數據");
            //添加個chart表
            ExcelWorksheet shapesheet = package.Workbook.Worksheets.Add("Chart");
            shapesheet.View.ShowGridLines = false;//去掉sheet的網格線
            shapesheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
            shapesheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.SkyBlue);

            //worksheet.View.ShowGridLines = false;//去掉sheet的網格線

            #region 生成chart表
            ExcelChartSerie serie = null;
            ExcelChart chart = shapesheet.Drawings.AddChart("chart", eChartType.LineMarkers);
            //chart.Legend.Position = eLegendPosition.TopRight;
            chart.SetPosition(5, 5);
            chart.Legend.Add();
            chart.Title.Text = "測試";
            chart.ShowHiddenData = true;
            chart.SetSize(1000, 600);//設置圖表大小

            chart.XAxis.Title.Text = "CNC";
            chart.XAxis.Title.Font.Size = 10;
            
            chart.YAxis.Title.Text = "Value";
            chart.YAxis.Title.Font.Size = 10;

            #endregion


            int rowIndex = 1;   // 起始行為 1
            int colIndex = 1;   // 起始列為 1

            //設置列名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[rowIndex, colIndex + i].Value = dt.Columns[i].ColumnName;
                //字體
                worksheet.Cells[rowIndex, colIndex + i].Style.Font.Name = "Arial";
                //字體加粗
                worksheet.Cells[rowIndex, colIndex + i].Style.Font.Bold = true;
                //字體大小
                worksheet.Cells[rowIndex, colIndex + i].Style.Font.Size = 12;
                //自動調整列寬，也可以指定最小寬度和最大寬度
                worksheet.Column(colIndex + i).AutoFit();

                if (colIndex + i > 1)
                {
                    serie = chart.Series.Add(worksheet.Cells[2, colIndex + i, dt.Rows.Count + 1, colIndex + i], worksheet.Cells[2, 1, dt.Rows.Count + 1, 1]);
                    serie.HeaderAddress = worksheet.Cells[1, colIndex + i];
                }
            }

            // 跳過第一列列名
            rowIndex++;

            //寫入數據
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //worksheet.Cells[rowIndex + i, colIndex + j].Style.Numberformat.Format = "0.00";
                    worksheet.Cells[rowIndex + i, colIndex + j].Value = double.Parse(dt.Rows[i][j].ToString());
                }

                //自動調整行高
                worksheet.Row(rowIndex + i).CustomHeight = true;

               
            }

            //添加chart數據，chart.Series.Add()方法所需參數為：chart.Series.Add(X軸數據區,Y軸數據區)
            //serie = chart.Series.Add(worksheet.Cells[2, 2, dt.Rows.Count + 1, 2], worksheet.Cells[2, 1, dt.Rows.Count + 1, 1]);
            //serie.HeaderAddress = worksheet.Cells[1, 2];
          //chartSerie = chart.Series.Add(worksheet.Cells[row + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells["B1:M1"]);
          //chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//設置每條線的名稱



            //垂直居中
            worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //水平居中
            worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //單元格是否自動換行
            worksheet.Cells.Style.WrapText = false;
            //單元格自動適應大小
            worksheet.Cells.Style.ShrinkToFit = true;

            //合併單元格
            //worksheet.Cells[2, 1, 2, 2].Merge = true; 
            //worksheet.Cells[int fromRow, fromCol, int toRow,int toCol].Merge = true; 

            //凍結首行（行號，列號）
            worksheet.View.FreezePanes(2, 1);
            ////凍結1-2列
            //worksheet.View.FreezePanes(1, 3);



            //新建一個 Excel 文件
            string filePath = @"C:\Users\Lenovo\Desktop\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
            FileStream fileStream = new FileStream(filePath, FileMode.Create);
            package.SaveAs(fileStream);

            //package.Save();

            fileStream.Close();
            fileStream.Dispose();

            worksheet.Dispose();
            package.Dispose();
            GC.Collect();

三 效果