C# ExcelPackage.SaveAs方法代码示例

        public byte[] Map(IEnumerable<Game> games)
        {
            var result = new MemoryStream();
            var pack = new ExcelPackage();
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add("games");

            CreateHeader(ws);

            int row = 2;
            foreach (Game game in games)
            {
                ws.Cells[row, 1].Value = game.Id.ToString();
                ws.Cells[row, 2].Value = game.Slot.StartDateTime.ToString("MM/dd/yyyy");
                ws.Cells[row, 3].Value = game.Slot.StartDateTime.ToString("hh:mm tt");
                ws.Cells[row, 4].Value = string.Format("U{0}", game.Age);
                ws.Cells[row, 5].Value = game.Level.ToString();
                ws.Cells[row, 6].Value = game.Gender;
                ws.Cells[row, 7].Value = game.Slot.Field.Description;
                ws.Cells[row, 8].Value = game.Team1 != null ? game.Team1.FullName : string.Empty;
                ws.Cells[row, 9].Value = game.Team2 != null ? game.Team2.FullName : string.Empty;
                ws.Cells[row, 10].Value = game.Activity;
                ws.Cells[row, 11].Value = "3";
                ws.Cells[row, 12].Value = string.Format("Refs needed: {0}", game.AreRefereesNeeded ? "Yes" : "No");
                ws.Cells[row, 13].Value = game.Notes;

                row++;
            }
            pack.SaveAs(result);

            return result.ToArray();
        }

        public ActionResult Generate(int CountryID, string Languaje_country, int? HospitalID, int? Year, int? WeekFrom, int? WeekTo)
        {
            try {
                var ms = new MemoryStream();
                var Languaje_country_ = db.Countries.Find(CountryID);

                using (var fs = System.IO.File.OpenRead(ConfigurationManager.AppSettings["FluIDTemplate"].Replace("{countryId}", CountryID.ToString()))) {
                    using (var excelPackage = new ExcelPackage(fs)) {
                        var excelWorkBook = excelPackage.Workbook;

                        AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FLUID_NATIONAL_VIRUSES", 6, 1,false);
                        AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FLUID_IRAG", 9, 4, false);
                        //AppendDataToExcel(CountryID, Languaje_country_.Language.ToString(), Year, HospitalID, WeekFrom, WeekTo, excelWorkBook, "FluidIragExport", 33, "Fallecidos IRAG");

                        excelPackage.SaveAs(ms);
                    }
                }

                ms.Position = 0;

                return new FileStreamResult(ms, "application/xlsx") {
                    FileDownloadName = "FluID.xlsx"
                };

                //ViewBag.Message = $"Report generated successfully!";
            } catch (Exception e)             {
                ViewBag.Message = "Reporte no pudo generarse, por favor ponganse en contacto con el administrador";
            }

            return View();
        }

        public Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
        {
            var stream = new MemoryStream();
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Report");
                worksheet.Cells["A1"].LoadFromCollection(_data, true);

                const string dateFormat = "dd/mm/yyyy";
                worksheet.Cells[1, 2, _data.Length + 1, 2].Style.Numberformat.Format = dateFormat;
                worksheet.Cells[1, 6, _data.Length + 1, 6].Style.Numberformat.Format = dateFormat;
                worksheet.Cells.AutoFitColumns();
                package.SaveAs(stream);
            }
            var responseMessage = new HttpResponseMessage(HttpStatusCode.OK)
            {Content = new ByteArrayContent(stream.GetBuffer())};

            responseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = _filename
            };
            responseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");

            return Task.FromResult(responseMessage);
        }

 public static byte[] SaveWorkbookToBytes(ExcelPackage package)
 {
     using (var stream = new MemoryStream()) {
         package.SaveAs(stream);
         return stream.ToArray();
     }
 }

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

        }

        public void ReadWriteEncrypt()
        {
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"Test\Drawing.xlsx"), true))
            {
                pck.Encryption.Password = "EPPlus";
                pck.Encryption.Algorithm = EncryptionAlgorithm.AES192;
                pck.Workbook.Protection.SetPassword("test");
                pck.Workbook.Protection.LockStructure = true;
                pck.Workbook.Protection.LockWindows = true;

                pck.SaveAs(new FileInfo(@"Test\DrawingEncr.xlsx"));
            }

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(_worksheetPath + @"\DrawingEncr.xlsx"), true, "EPPlus"))
            {
                pck.Encryption.IsEncrypted = false;
                pck.SaveAs(new FileInfo(_worksheetPath + @"\DrawingNotEncr.xlsx"));
            }

            FileStream fs = new FileStream(_worksheetPath + @"\DrawingEncr.xlsx", FileMode.Open, FileAccess.ReadWrite);
            using (ExcelPackage pck = new ExcelPackage(fs, "EPPlus"))
            {
                pck.Encryption.IsEncrypted = false;
                pck.SaveAs(new FileInfo(_worksheetPath + @"DrawingNotEncr.xlsx"));
            }
        }

        protected void ExportToExcel_Click(object sender, EventArgs e)
        {
            var products = GetProducts();
            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Products");
            var totalCols = products.Columns.Count;
            var totalRows = products.Rows.Count;

            for (var col = 1; col <= totalCols; col++)
            {
                workSheet.Cells[1, col].Value = products.Columns[col - 1].ColumnName;
            }
            for (var row = 1; row <= totalRows; row++)
            {
                for (var col = 0; col < totalCols; col++)
                {
                    workSheet.Cells[row + 1, col + 1].Value = products.Rows[row - 1][col];
                }
            }
            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename= BuyerDetails.xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }

        private void ExportWFTDAStatsReport()
        {
            using (ExcelPackage p = new ExcelPackage())
            {
                try
                {
                    p.Workbook.Properties.Author = "RDNation.com v" + ScoreboardConfig.SCOREBOARD_VERSION_NUMBER;
                    p.Workbook.Properties.Title = "WFTDA Report For " + GameViewModel.Instance.GameName;
                    Color tabPink = Color.FromArgb(255, 138, 255);
                    ExportIBRF(p);
                    ExportScores(p);
                    ExportPenalties(p);
                    ExportLineups(p);
                    ExportBoutSummary(p);
                    ExportPenaltySummary(p);

                }
                catch (Exception exception)
                {
                    ErrorViewModel.Save(exception, exception.GetType());
                }
                FileInfo fi = new FileInfo(this.fileName);
                p.SaveAs(fi);
            }
        }

        public static string RunSample11(DirectoryInfo outputDir)
        {
            //Create a Sample10 directory...
            if (!Directory.Exists(outputDir.FullName + @"\Sample11"))
            {
                outputDir.CreateSubdirectory("Sample11");
            }
            outputDir = new DirectoryInfo(outputDir + @"\Sample11");

            //create FileInfo object...
            FileInfo output = new FileInfo(outputDir.FullName + @"\Output.xlsx");
            if (output.Exists)
            {
                output.Delete();
                output = new FileInfo(outputDir.FullName + @"\Output.xlsx");
            }

            using (var package = new ExcelPackage(output))
            {
                AddIntegerValidation(package);
                AddListValidationFormula(package);
                AddListValidationValues(package);
                AddTimeValidation(package);
                AddDateTimeValidation(package);
                ReadExistingValidationsFromPackage(package);
                package.SaveAs(output);
            }
            return output.FullName;
        }

        protected void ImgExportToExcell_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {
            long RecordCount;
            var kids = KidsUser_DataProvider.GetGeustUser(out RecordCount,
                                                        Name: txtSearchName.Text,
                                                        Family: txtSearchFamily.Text,
                                                        MelliCode: txtSearchMelliCode.Text,
                                                        EmailAddress: txtSearchEmailAddress.Text,
                                                        MobileNumber: txtSearchMobileNumber.Text,
                                                        SelectDistinct: true,
                                                        PageSize: 100000
                                                        );

            string templatefilePath = Server.MapPath("~/AdminCP/Files/GeustUser/GeustUserReport.xlsx");
            FileInfo templateFileInfo = new FileInfo(templatefilePath);

            string NewfilePath = string.Format("~/AdminCP/Files/GeustUser/Temp/GeustUserReport_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
            FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));

            ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);

            ExcelWorksheet workSheetGeustUser = xlPackage.Workbook.Worksheets["GeustUser"];
            int i = 2;
            foreach (var user in kids)
            {
                FillExcellRow(user, workSheetGeustUser, i);
                i++;
            }
            xlPackage.SaveAs(NewfileInfo);
            xlPackage.Dispose();
            ClientRedirect(NewfilePath, 2000);
        }

        private void OpenAndExportWFTDAStatsReport()
        {
            try
            {

                FileInfo newFile = new FileInfo(openFileName);

                ExcelPackage p = new ExcelPackage(newFile);
                //Add the Content sheet

                if (GameViewModel.Instance != null)
                {
                    PopulateWftdaReport pop = new PopulateWftdaReport(p);
                    pop.PopulateReport();
                }

                FileInfo fi = new FileInfo(this.fileName);
                p.SaveAs(fi);
                System.Diagnostics.Process.Start(this.fileName);
            }
            catch (Exception exception)
            {
                ErrorViewModel.Save(exception, exception.GetType());
            }
        }

 public static void OutputExcel(HttpResponseBase response, string filename, ExcelPackage pck)
 {
     pck.SaveAs(response.OutputStream);
     response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
     response.AddHeader("content-disposition", "attachment;  filename=" + filename + ".xlsx");
     response.AddHeader("Content-Length", pck.Stream.Length.ToString());
 }

        public ActionResult Excel()
        {
            var tbl = CriaDataTableProdutos();

            IncluirNoDataTable("Arroz Tipo 1", "PCT", 12, 2, tbl);
            IncluirNoDataTable("Feijão São João", "PCT", 7, 3, tbl);
            IncluirNoDataTable("Macarrão Ferrari", "PCT", 8, (decimal) 2.3, tbl);

            using (var pacote = new ExcelPackage())
            {
                //Cria uma pasta de Trabalho, no arquivo do Excel
                var ws = pacote.Workbook.Worksheets.Add("Produtos");

                //Carrega os dados para a planilha, inicia na celula A1, a primeira linha é o descricao dos campos usado na tabela
                ws.Cells["A1"].LoadFromDataTable(tbl, true);

                //Formata a coluna para numerico, valor unitario (4)
                using (var col = ws.Cells[2, 4, 2 + tbl.Rows.Count, 4])
                {
                    col.Style.Numberformat.Format = "#,##0.00";
                    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                //Formata o cabeçalho, 4 celulas (a1:d1)
                using (var rng = ws.Cells["A1:F1"])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
                    rng.Style.Font.Color.SetColor(Color.White);
                }
                //Seta um texto para o Valor Total
                ws.Cells["F1"].Value = "Valor Total";

                //Formata a coluna do valor total, para numeric, e coloca uma formula
                for (var i = 2; i <= tbl.Rows.Count + 1; i++)
                {
                    ws.Cells["F" + i].Style.Numberformat.Format = "#,##0.00";
                    ws.Cells["F" + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    ws.Cells["F" + i].Formula = string.Format(" {0}*{1}", "D" + i, "E" + i);
                }

                //Formata o valor total
                var vTotal = tbl.Rows.Count + 2;
                ws.Cells["F" + vTotal].Style.Font.Bold = true;
                ws.Cells["F" + vTotal].Style.Fill.PatternType = ExcelFillStyle.Solid;
                ws.Cells["F" + vTotal].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
                ws.Cells["F" + vTotal].Style.Font.Color.SetColor(Color.White);
                ws.Cells["F" + vTotal].Style.Numberformat.Format = "#,##0.00";
                ws.Cells["F" + vTotal].Formula = string.Format("SUM({0},{1})", "F2", "F" + (vTotal - 1));

                //gera o arquivo para download
                var stream = new MemoryStream();
                pacote.SaveAs(stream);
                const string nomeDoArquivo = "RelatorioDeProdutos.xlsx";
                const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                stream.Position = 0;
                return File(stream, contentType, nomeDoArquivo);
            }
        }

 public void ReadBug12()
 {
     var package = new ExcelPackage(new FileInfo(@"c:\temp\bug.xlsx"));
     var ws = package.Workbook.Worksheets[1];
     ws.Cells["A1"].Value = 1;
     package.SaveAs(new FileInfo(@"c:\temp\bug2.xlsx"));
 }

        public void ReadStreamWithTemplateWorkSheet()
        {
            FileStream instream = new FileStream(@"Test\Worksheet.xlsx", FileMode.Open, FileAccess.Read);
            MemoryStream stream = new MemoryStream();
            using (ExcelPackage pck = new ExcelPackage(stream, instream))
            {
                var ws = pck.Workbook.Worksheets["Perf"];
                Assert.AreEqual(ws.Cells["H6"].Formula, "B5+B6");

               var wsHF=pck.Workbook.Worksheets["Header footer"];
               Assert.AreEqual(wsHF.HeaderFooter.firstFooter.LeftAlignedText, "First Left");
               Assert.AreEqual(wsHF.HeaderFooter.firstFooter.RightAlignedText, "First Right");

                Assert.AreEqual(wsHF.HeaderFooter.evenFooter.CenteredText, "even Centered");

               Assert.AreEqual(wsHF.HeaderFooter.oddFooter.LeftAlignedText, "odd Left");
               Assert.AreEqual(wsHF.HeaderFooter.oddFooter.CenteredText,"odd Centered");
               Assert.AreEqual(wsHF.HeaderFooter.oddFooter.RightAlignedText, "odd Right");

               foreach (var cell in pck.Workbook.Names["Data"])
               {
                   Assert.IsNotNull(cell);
               }

                pck.SaveAs(new FileInfo(@"Test\Worksheet2.xlsx"));

            }

            instream.Close();
        }