C# OfficeOpenXml.ExcelPackage类代码示例

    private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
      FileInfo newFile = new FileInfo(fileName);
      if (newFile.Exists) {
        newFile.Delete();
        newFile = new FileInfo(fileName);
      }
      var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);

      using (ExcelPackage package = new ExcelPackage(newFile)) {
        ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
        FormatModelSheet(modelWorksheet, solution, formulaParts);

        ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
        WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);

        ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
        WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);

        if (solution is IRegressionSolution) {
          ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
          WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);

          ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
          AddCharts(chartsWorksheet);
        }
        package.Workbook.Properties.Title = "Excel Export";
        package.Workbook.Properties.Author = "HEAL";
        package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";

        package.Save();
      }
    }

        /// <summary>
        /// Экспортирует массив данных в XLSX формат с учетом выбранной локали
        /// </summary>
        /// <param name="path">Путь к файлу, в который нужно сохранить данные</param>
        /// <param name="localisation">Локализация</param>
        /// <returns>Успешное завершение операции</returns>
        public override bool Export(String path, Localisation localisation)
        {
            try
            {
                if (!path.EndsWith(".xlsx"))
                    path += ".xlsx";

                log.Info(String.Format("Export to .xlsx file to: {0}", path));
                var timer = new Stopwatch();
                timer.Start();
                var file = new FileInfo(path);
                using (var pck = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    ws.Cells.AutoFitColumns();
                    pck.Save();
                }

                timer.Stop();
                log.Info(String.Format("Export complete! Elapsed time: {0} ms", timer.Elapsed.Milliseconds));
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Can't export to .xlsx file!", ex);
                return false;
            }
        }

 public void AverageCellReferences()
 {
     // In the case of cell references, Average DOES NOT parse and include numeric strings, date strings, bools, unparsable strings, etc.
     ExcelPackage package = new ExcelPackage();
     var worksheet = package.Workbook.Worksheets.Add("Test");
     ExcelRange range1 = worksheet.Cells[1, 1];
     range1.Formula = "\"1000\"";
     range1.Calculate();
     var range2 = worksheet.Cells[1, 2];
     range2.Value = 2000;
     var range3 = worksheet.Cells[1, 3];
     range3.Formula = $"\"{new DateTime(2013, 1, 5).ToString("d")}\"";
     range3.Calculate();
     var range4 = worksheet.Cells[1, 4];
     range4.Value = true;
     var range5 = worksheet.Cells[1, 5];
     range5.Value = new DateTime(2013, 1, 5);
     var range6 = worksheet.Cells[1, 6];
     range6.Value = "Test";
     Average average = new Average();
     var rangeInfo1 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 1, 1, 3);
     var rangeInfo2 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 4, 1, 4);
     var rangeInfo3 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 5, 1, 6);
     var context = ParsingContext.Create();
     var address = new OfficeOpenXml.FormulaParsing.ExcelUtilities.RangeAddress();
     address.FromRow = address.ToRow = address.FromCol = address.ToCol = 2;
     context.Scopes.NewScope(address);
     var result = average.Execute(new FunctionArgument[]
     {
         new FunctionArgument(rangeInfo1),
         new FunctionArgument(rangeInfo2),
         new FunctionArgument(rangeInfo3)
     }, context);
     Assert.AreEqual((2000 + new DateTime(2013, 1, 5).ToOADate()) / 2, result.Result);
 }

        /// <summary>
        /// 保存excel文件，覆盖相同文件名的文件
        /// </summary>
        public static void SaveExcel(string FileName, string sql, string SheetName)
        {

            FileInfo newFile = new FileInfo(FileName);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(FileName);
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                try
                {
                    ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);

                    IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                    ws.Cells["A1"].LoadFromDataReader(reader, true);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                package.Save();
            }
         
        }

        private static void VBASample2(DirectoryInfo outputDir)
        {
            //Open Sample 1 again
            ExcelPackage pck = new ExcelPackage(new FileInfo(outputDir.FullName + @"\sample1.xlsx"));
            //Create a vba project             
            pck.Workbook.CreateVBAProject();

            //Now add some code that creates a bubble chart...
            var sb = new StringBuilder();

            sb.AppendLine("Public Sub CreateBubbleChart()");
            sb.AppendLine("Dim co As ChartObject");
            sb.AppendLine("Set co = Inventory.ChartObjects.Add(10, 100, 400, 200)");
            sb.AppendLine("co.Chart.SetSourceData Source:=Range(\"'Inventory'!$B$1:$E$5\")");
            sb.AppendLine("co.Chart.ChartType = xlBubble3DEffect         'Add a bubblechart");
            sb.AppendLine("End Sub");

            //Create a new module and set the code
            var module = pck.Workbook.VbaProject.Modules.AddModule("EPPlusGeneratedCode");
            module.Code = sb.ToString();

            //Call the newly created sub from the workbook open event
            pck.Workbook.CodeModule.Code = "Private Sub Workbook_Open()\r\nCreateBubbleChart\r\nEnd Sub";

            //Optionally, Sign the code with your company certificate.
            /*            
            X509Store store = new X509Store(StoreLocation.CurrentUser);
            store.Open(OpenFlags.ReadOnly);
            pck.Workbook.VbaProject.Signature.Certificate = store.Certificates[0];
            */

            //And Save as xlsm
            pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-2.xlsm"));
        }

        public ListaHojasExcel(string pathExcel)
            : this()
        {
            var archivo = new FileInfo(pathExcel);
            Excel = new ExcelPackage(archivo);

            foreach (var hoja in Hojas)
            {
                var indice = hoja.Key;

                var hojaActual = Excel.Workbook.Worksheets[indice];
                var filaActual = 1;
                while (String.IsNullOrEmpty(hojaActual.GetValue<string>(filaActual, 2)))
                {
                    filaActual++;
                }
                filaActual+=3;
                while (!String.IsNullOrEmpty(hojaActual.GetValue<string>(filaActual, 2)))
                {

                    var oportunidad = Oportunidad.CrearOportunidad(indice);
                    oportunidad.CargarDatos(hojaActual, filaActual);
                    //if (oportunidad.Codigo == 13124) && otraOportunidad.Codigo == this.Codigo
                    //    Console.WriteLine("this");
                    hoja.Value.Add(oportunidad);
                    filaActual++;
                }
            }
        }

        private void CreateExcelFile(string fileName, IList<SqliteExcelFormat> data)
        {
            if (File.Exists(fileName)) File.Delete(fileName);
            using (var excel = new ExcelPackage(new FileInfo(fileName)))
            {
                var ws = excel.Workbook.Worksheets.Add("Sheet1");
                ws.Cells[1, 1].Value = "Vendor";
                ws.Cells[1, 2].Value = "Incomes";
                ws.Cells[1, 3].Value = "Expenses";
                ws.Cells[1, 4].Value = "Taxes";
                ws.Cells[1, 5].Value = "Financial Result";

                for (int i = 0; i < data.Count; i++)
                {
                    var tax = (data[i].TaxPercentage / 100) * data[i].Incomes;
                    ws.Cells[i + 2, 1].Value = data[i].Vendor;
                    ws.Cells[i + 2, 2].Value = data[i].Incomes;
                    ws.Cells[i + 2, 3].Value = data[i].Expenses;
                    ws.Cells[i + 2, 4].Value = Math.Round((decimal)tax, 2);
                    ws.Cells[i + 2, 5].Value = Math.Round((decimal)(data[i].Incomes - data[i].Expenses - tax), 2);
                }
                            
                ws.Column(1).AutoFit();
                ws.Column(2).AutoFit();
                ws.Column(3).AutoFit();
                ws.Column(4).AutoFit();
                ws.Column(5).AutoFit();
                excel.Save();
            }
        }

        public byte[] GetShirtsFile(List<GuestShirtGetDto> notOrderedShirts)
        {
            using (var pck = new ExcelPackage())
            {
                //Create the worksheet
                var ws = pck.Workbook.Worksheets.Add($"NotOrderedShirts-{DateTime.Today.ToShortDateString()}");

                if (notOrderedShirts.Count > 0)
                {
                    var properties = notOrderedShirts.First().GetType().GetProperties();

                    for (var i = 0; i < properties.Length; i++)
                    {
                        ws.Cells[1, i + 1].Value = properties[i].Name;
                    }

                    ws.Row(1).Style.Font.Bold = true;

                    for (var j = 0; j < notOrderedShirts.Count; j++)
                    {
                        var item = notOrderedShirts[j];
                        for (var i = 0; i < properties.Length; i++)
                        {
                            ws.Cells[j + 2, i + 1].Value = item.GetType().GetProperty(properties[i].Name).GetValue(item);
                        }
                    }
                }

                return pck.GetAsByteArray();
            }
        }

            /// <summary>
            /// constructor
            /// </summary>
            /// <param name="dt">To export DataTable</param>
            /// <param name="tableStyle">Styling for entire table</param>
            /// <param name="headerStyle">Styling for header</param>
            /// <param name="itemStyle">Styling for the individual cells</param>
            public ExcelFileResult(DataTable dt, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle)
                : base("application/ms-excel")
            {
                this.dt = dt;
                Title = "Attendance Report ";
                Footer = "Powered By: Hasib, IT Department";
                TitleExportDate = "Export Date: {0}";
                this.tableStyle = tableStyle;
                this.headerStyle = headerStyle;
                this.itemStyle = itemStyle;
                ExcelPackage EXPackage = new ExcelPackage();

                // provide defaults
                if (this.tableStyle == null)
                {
                    this.tableStyle = new TableStyle();
                    this.tableStyle.BorderStyle = BorderStyle.Solid;
                    this.tableStyle.BorderColor = Color.Black;
                    this.tableStyle.BorderWidth = Unit.Parse("2px");
                    //this.tableStyle.BackColor = Color.LightGray;
                    this.tableStyle.BackColor = Color.Azure;
                    //this.tableStyle.BackImageUrl = Path.GetFullPath("D:/HOP/BOK.jpg");
                    //exPackage.Workbook.Properties.Author = "Hasib";
                    //exPackage.Workbook.Properties.Comments = "HopLunIT";
                    //exPackage.Workbook.Properties.Title = "HopLun (Bangladesh) Ltd. Reports";
                }
                if (this.headerStyle == null)
                {
                    this.headerStyle = new TableItemStyle();
                    this.headerStyle.BackColor = Color.LightGray;
                }
            }

 public CMixExcel(Stream s)
 {
     if (IsUpperVer2003)
         _excelPackage = new ExcelPackage(s);
     else
         _HSSFWorkbook = new HSSFWorkbook(s);
 }

        public void WriteVBA()
        {
            var package = new ExcelPackage();
            package.Workbook.Worksheets.Add("Sheet1");
            package.Workbook.CreateVBAProject();
            package.Workbook.VbaProject.Modules["Sheet1"].Code += "\r\nPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\nMsgBox(\"Test of the VBA Feature!\")\r\nEnd Sub\r\n";
            package.Workbook.VbaProject.Modules["Sheet1"].Name = "Blad1";
            package.Workbook.CodeModule.Name = "DenHärArbetsboken";
            package.Workbook.Worksheets[1].Name = "FirstSheet";
            package.Workbook.CodeModule.Code += "\r\nPrivate Sub Workbook_Open()\r\nBlad1.Cells(1,1).Value = \"VBA test\"\r\nMsgBox \"VBA is running!\"\r\nEnd Sub";
            //X509Store store = new X509Store(StoreLocation.CurrentUser);
            //store.Open(OpenFlags.ReadOnly);
            //package.Workbook.VbaProject.Signature.Certificate = store.Certificates[11];

            var m = package.Workbook.VbaProject.Modules.AddModule("Module1");
            m.Code += "Public Sub Test(param1 as string)\r\n\r\nEnd sub\r\nPublic Function functest() As String\r\n\r\nEnd Function\r\n";
            var c = package.Workbook.VbaProject.Modules.AddClass("Class1", false);
            c.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub";
            var c2 = package.Workbook.VbaProject.Modules.AddClass("Class2", true);
            c2.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub";

            package.Workbook.VbaProject.Protection.SetPassword("EPPlus");
            package.SaveAs(new FileInfo(@"c:\temp\vbaWrite.xlsm"));

        }

        private static void LoadFile1(ExcelPackage package)
        {
            //Create the Worksheet
            var sheet = package.Workbook.Worksheets.Add("Csv1");

            //Create the format object to describe the text file
            var format = new ExcelTextFormat();
            format.TextQualifier = '"';
            format.SkipLinesBeginning = 2;
            format.SkipLinesEnd = 1;

            //Now read the file into the sheet. Start from cell A1. Create a table with style 27. First row contains the header.
            Console.WriteLine("Load the text file...");
            var range = sheet.Cells["A1"].LoadFromText(new FileInfo("..\\..\\csv\\Sample9-1.txt"), format, TableStyles.Medium27, true);

            Console.WriteLine("Format the table...");
            //Tables don't support custom styling at this stage(you can of course format the cells), but we can create a Namedstyle for a column...
            var dateStyle = package.Workbook.Styles.CreateNamedStyle("TableDate");
            dateStyle.Style.Numberformat.Format = "YYYY-MM";

            var numStyle = package.Workbook.Styles.CreateNamedStyle("TableNumber");
            numStyle.Style.Numberformat.Format = "#,##0.0";

            //Now format the table...
            var tbl = sheet.Tables[0];
            tbl.ShowTotal = true;
            tbl.Columns[0].TotalsRowLabel = "Total";
            tbl.Columns[0].DataCellStyleName = "TableDate";
            tbl.Columns[1].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[1].DataCellStyleName = "TableNumber";
            tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[2].DataCellStyleName = "TableNumber";
            tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[3].DataCellStyleName = "TableNumber";
            tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[4].DataCellStyleName = "TableNumber";
            tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[5].DataCellStyleName = "TableNumber";
            tbl.Columns[6].TotalsRowFunction = RowFunctions.Sum;
            tbl.Columns[6].DataCellStyleName = "TableNumber";

            Console.WriteLine("Create the chart...");
            //Now add a stacked areachart...
            var chart = sheet.Drawings.AddChart("chart1", eChartType.AreaStacked);
            chart.SetPosition(0, 630);
            chart.SetSize(800, 600);

            //Create one series for each column...
            for (int col = 1; col < 7; col++)
            {
                var ser = chart.Series.Add(range.Offset(1, col, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1));
                ser.HeaderAddress = range.Offset(0, col, 1, 1);
            }

            //Set the style to 27.
            chart.Style = eChartStyle.Style27;

            sheet.View.ShowGridLines = false;
            sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
        }

        private static ExcelPackage writeToSheet(ExcelPackage package, DataTable dt)
        {
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add(dt.TableName);
            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;
            for (int i = 1; i <= rows; i++)
            {
                DataRow dr = dt.Rows[i - 1];
                for (int j = 1; j <= cols; j++)
                {
                    //sheet.Cells[i, j].Style.Numberformat.Format = ((dynamic)dr[j - 1])["format"];
                    sheet.Cells[i, j].Value = ((dynamic)dr[j-1])["text"];
                    if (((dynamic)dr[j - 1])["isColor"] == true)
                    {
                        Color color = ColorTranslator.FromHtml("#"+((dynamic)dr[j - 1])["color"]);
                        sheet.Cells[i, j].Style.Font.Color.SetColor(color);
                    }
                }
            }

            ExcelRange r = sheet.Cells[1, 1, cols, rows];
            r.AutoFitColumns();

            return package;
        }

        public void WriteListToExcel(string fileName, SharepointList list)
        {
            var newFile = new FileInfo(fileName);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(fileName);
            }

            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");

                //Add the headers
                for (var i = 0; i < list.Columns.Count; i++)
                {
                    var column = list.Columns[i];
                    worksheet.Cells[1, i+1].Value = column.Name;
                }

                //Add the items
                for (var i = 0; i < list.Rows.Count; i++)
                {
                    var row = list.Rows[i];
                    for (var j = 0; j < row.Values.Count; j++)
                    {
                        var value = row.Values[j] != null ? row.Values[j].ToString() : row.Values[j];
                        worksheet.Cells[i+2, j+1].Value = value;
                    }
                }

                package.Save();
            }
        }

        public void AverateIfsShouldCaluclateResult()
        {
            using (var pck = new ExcelPackage())
            {
                var sheet = pck.Workbook.Worksheets.Add("test");
                sheet.Cells["F4"].Value = 1;
                sheet.Cells["F5"].Value = 2;
                sheet.Cells["F6"].Formula = "2 + 2";
                sheet.Cells["F7"].Value = 4;
                sheet.Cells["F8"].Value = 5;

                sheet.Cells["H4"].Value = 3;
                sheet.Cells["H5"].Value = 3;
                sheet.Cells["H6"].Formula = "2 + 2";
                sheet.Cells["H7"].Value = 4;
                sheet.Cells["H8"].Value = 5;

                sheet.Cells["I4"].Value = 2;
                sheet.Cells["I5"].Value = 3;
                sheet.Cells["I6"].Formula = "2 + 2";
                sheet.Cells["I7"].Value = 5;
                sheet.Cells["I8"].Value = 1;

                sheet.Cells["H9"].Formula = "AVERAGEIFS(F4:F8;H4:H8;\">3\";I4:I8;\"<5\")";
                sheet.Calculate();
                Assert.AreEqual(4.5d, sheet.Cells["H9"].Value);
            }
        }