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();

三 效果