C# ExcelPackage.GetAsByteArray方法代码示例

        public static Byte[] GenerateXLS(DashboardCollection datasource)
        {
            /* Call OpenOfficeXML need nuget package for epplus
               This can now be customize for each type of  excel export, also we can make a abstract calls for data dumps
               My want abastrct it out os multiple worksheets can be created.
            */

            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet 
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Dashboards");

                // Build Excel title
                BuildTitle(ws);
                //By Teachers
                BuildTeachersTable(ws, datasource.Teachers);
               
                //By Homerooms
                BuildHomeroomsTable(ws, datasource.Homerooms);
               
                //By Violations
                BuildViolationsTable(ws, datasource.OffenseTypes);

                return pck.GetAsByteArray();
            }
        }

        public NarrativeReport(IEnumerable<Area> areas, IEnumerable<Fund> funds)
        {
            Funds = funds;

            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Funding Request Report");

            #region Table Labels
            Row++;
            sheet = WriteTableLabels(sheet);
            #endregion

            #region Area Data
            foreach (Area area in areas)
            {
                sheet = WriteAreaData(sheet, area);
            }
            #endregion

            sheet = PerformFinalFormatting(sheet);

            this.BinaryData = package.GetAsByteArray();
            this.FileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            this.FileName = "FoundationPortal_" + System.DateTime.Now.ToShortDateString() + ".xlsx";
        }

        private void CreateTranslationsFile(List<IndicatorUpdate> indicators, string filename)
        {
            DataTable table = new DataTable();
            table.Columns.Add("Key");
            table.Columns.Add("English");
            table.Columns.Add("French");
            table.Columns.Add("Portuguese");
            table.Columns.Add("Bahasa");
            foreach (var ind in indicators)
            {
                var dr = table.NewRow();
                dr["Key"] = ind.Key;
                dr["English"] = ind.English;
                dr["French"] = ind.French;
                dr["Portuguese"] = ind.Portuguese;
                dr["Bahasa"] = ind.Bahasa;
                table.Rows.Add(dr);
            }

            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                ws.Cells["A1"].LoadFromDataTable(table, true);
                File.WriteAllBytes(filename, pck.GetAsByteArray());
            }
        }

        /// <summary>
        ///     Creates an Excel spreadsheet with a single worksheet for the supplied data.
        /// </summary>
        /// <param name="data">Each row of the spreadsheet will contain one item from the data collection.</param>
        /// <returns>An Excel spreadsheet as a byte array.</returns>
        public static byte[] Create(IEnumerable<object> data)
        {
            var package = new ExcelPackage();
            Spreadsheet.AddData(ref package, data);

            return package.GetAsByteArray();
        }

        public byte[] CreateNewStatusesReport(ObservableCollection<AccountsMainSet> accountsList)
        {
            byte[] resultPackage;
            using (ExcelPackage p = new ExcelPackage())
            {
                p.Workbook.Worksheets.Add("Report");
                var ws = p.Workbook.Worksheets[1];

                ws.Cells[1, 1].Value = @"Номер п\п";
                ws.Cells[1, 2].Value = "Объект";
                ws.Cells[1, 3].Value = "Поставщик";
                ws.Cells[1, 4].Value = "Номер счета";
                ws.Cells[1, 5].Value = "Дата счета";
                ws.Cells[1, 6].Value = "Сумма счета";
                ws.Cells[1, 1, 1, 6].Style.Font.Bold = true;
                var i = 1;
                foreach(var account in accountsList)
                {
                    ws.Cells[i + 1, 1].Value = i;
                    ws.Cells[i + 1, 2].Value = (account.AccountsStoreDetailsSets.Count > 1) ? "По списку" : ((account.AccountsStoreDetailsSets.Count == 0) ? "Не задан" : _storeService.GetStoreName(account.AccountsStoreDetailsSets.FirstOrDefault().AccountStore));
                    ws.Cells[i + 1, 3].Value = account.AccountCompany;
                    ws.Cells[i + 1, 4].Value = account.AccountNumber;
                    ws.Cells[i + 1, 5].Value = account.AccountDate.ToShortDateString();
                    ws.Cells[i + 1, 6].Value = account.AccountAmount;
                    i++;
                }
                for (int k = 1; k <= 6; k++)
                {
                    ws.Column(k).AutoFit(k);
                }
                resultPackage = p.GetAsByteArray();
                return resultPackage;
            }
            
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            ExcelPackage package = new ExcelPackage();
            EppTools tools = new EppTools();
            //
            string fileName = "WeeklyReport";
            string year = ddlYear.SelectedValue;
            int classid = Convert.ToInt32(ddlClass.SelectedItem.Value);

            DataTable table = Common.GetDailyReport(year, classid);
            //
            tools.GenerateDailyReport(table, ref package, fileName);
            //
            var ws = package.Workbook.Worksheets[fileName];
            //
            // HEADER TITLE FORMATING
            ws.Cells[2, 1].Value = "DAILY REPORT ";
            ws.Cells[3, 1].Value = "Year and Date ";
            ws.Cells[4, 1].Value = "Agent: " + GlobalAccess.Username;

            ws.Cells[2, 1, 4, 1].Style.Font.Bold = true;
            ws.Cells[2, 1, 4, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

            // SAVING EXCEL FILE
            Response.Clear();
            Response.BinaryWrite(package.GetAsByteArray());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=\"" + "Daily_Report.xlsx\"");

            Response.Flush();
            Response.End();

            package = null;
            ws = null;
        }

 public static byte[] GetBytes(this ExcelWorksheet worksheet)
 {
     using(ExcelPackage package = new ExcelPackage())
     {
         package.Workbook.Worksheets.Add(worksheet.Name, worksheet);
         return package.GetAsByteArray();
     }
 }

 public static byte[] ToExcelBytes(this IDataReader rd, string filename = null, bool useTable = false)
 {
     var dt = new DataTable();
     dt.Load(rd);
     var ep = new ExcelPackage();
     ep.AddSheet(dt, filename, useTable);
     return ep.GetAsByteArray();
 }

        /// <summary>
        /// Creates an Excel spreadsheet with a single worksheet for the supplied data.
        /// </summary>
        /// <param name="data">Each row of the spreadsheet will contain one item from the data collection.</param>
        /// <returns>An Excel spreadsheet as a byte array.</returns>
        public static byte[] Create(IEnumerable<object> data)
        {
            var package = new ExcelPackage();

            AddWorksheet(package, data);
            AddSpreadsheetLinks(package, new[] { data });

            return package.GetAsByteArray();
        }

        public static Byte[] GenerateXLS(List<Student> datasource)
        {
            /* Call OpenOfficeXML need nuget package for epplus
               This can now be customize for each type of  excel export, also we can make a abstract calls for data dumps
               My want abastrct it out os multiple worksheets can be created.
            */
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet 
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Students");

                //Set Header titles
                ws.Cells[1, 1].Value = "Student Id";
                ws.Cells[1, 2].Value = "First Name";
                ws.Cells[1, 3].Value = "Last Name";
                ws.Cells[1, 4].Value = "School Year";
                ws.Cells[1, 5].Value = "Home Room";
                ws.Cells[1, 6].Value = "Grade";
                ws.Cells[1, 7].Value = "Created By";
                ws.Cells[1, 8].Value = "Create Date";
                ws.Cells[1, 9].Value = "Last Updated By";
                ws.Cells[1, 10].Value = "Last Update Date";
                ws.Cells[1, 11].Value = "Deleted";

                //Get Data
                for (int i = 0; i < datasource.Count(); i++)
                {
                    ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).student_id;
                    ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).first_name;
                    ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).last_name;
                    ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).school_year_id;
                    ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).homeroom_id;
                    ws.Cells[i + 2, 6].Value = datasource.ElementAt(i).grade_id;
                    ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).create_contact_id;
                    ws.Cells[i + 2, 8].Value = datasource.ElementAt(i).create_dt;
                    ws.Cells[i + 2, 9].Value = datasource.ElementAt(i).last_update_contact_id;
                    ws.Cells[i + 2, 10].Value = datasource.ElementAt(i).last_update_dt;
                    ws.Cells[i + 2, 11].Value = datasource.ElementAt(i).is_deleted;

                }

                //Set Header style
                using (ExcelRange rng = ws.Cells[1,1,1,11])
                {
                    rng.AutoFilter = true;
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.Blue);
                    rng.Style.Font.Color.SetColor(Color.White);
                }

               
                return pck.GetAsByteArray();
            }
        }

        public static Response AsExcel(this IResponseFormatter response, IEnumerable<TimeRegistration> items)
        {
            using (var package = new ExcelPackage())
            {
                WriteSheet(package, items);
                var stream = new MemoryStream(package.GetAsByteArray());

                return response.FromStream(() => stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                              .WithHeader("content-disposition", string.Format("attachment;filename=Timeregistrations_all.xlsx"));
            }

        }

        private void SetHttpResponse(HttpResponseBase httpResponse, string fileNameWithoutExtension, ExcelPackage package)
        {
            httpResponse.ClearContent();
            httpResponse.Buffer = true;

            //Write it back to the client
            httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            httpResponse.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", fileNameWithoutExtension));
            httpResponse.BinaryWrite(package.GetAsByteArray());

            httpResponse.Flush();
            httpResponse.End();
        }

        public void ExportDataSetToExcelWithPlus(DataSet ds)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                HttpResponse Response = HttpContext.Current.Response;

                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ItemList");

                string[] cellNames = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

                DataTable tbl = ds.Tables[0];

                int cellRange = tbl.Columns.Count;

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(tbl, true);

                //Format the header for column 1-3
                using (ExcelRange rng = ws.Cells[cellNames[0] + "1:" + cellNames[cellRange - 1] + "1"])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);
                }

                ////Example how to Format Column 1 as numeric
                //using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
                //{
                //    col.Style.Numberformat.Format = "#,##0.00";
                //    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                //}

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=ItemDataList.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
                try {
                Response.End();
                 }catch (Exception ex)
                {
                    if (!(ex is System.Threading.ThreadAbortException))
                    {
                        //Log other errors here
                    }
                }

            }
        }

        public FileResult ExportOrdersToExcel(OrderExportQuery exportQuery)
        {
            using (var excelFile = new ExcelPackage())
            {
                AddOrders(excelFile, exportQuery);
                AddOrderLines(excelFile, exportQuery);

                byte[] data = excelFile.GetAsByteArray();

                return new FileContentResult(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    FileDownloadName = "mrcms-ecommerce-order-export.xlsx"
                };
            }
        }

        /// <summary>
        /// Generates the report.
        /// </summary>
        private static void GenerateReport()
        {
            var startTime = DateTime.Now.Second;
            using (ExcelPackage p = new ExcelPackage())
            {

                //set the workbook properties and add a default sheet in it
                SetWorkbookProperties(p);
                //Create a sheet
                ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
                DataTable dt = CreateDataTable(); //My Function which generates DataTable
                List<TestData> testData = BuildList();
                ws.InsertRow(1, testData.Count);
                ws.InsertColumn(1, 7);
                
                //Merging cells and create a center heading for out table
                ws.Cells[1, 1].Value = "Sample DataTable Export";
                ws.Cells[1, 1, 1, 6].Merge = true;
                ws.Cells[1, 1, 1, 6].Style.Font.Bold = true;
                ws.Cells[1, 1, 1, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                int rowIndex = 2;

                CreateHeader(ws, ref rowIndex);
                CreateData(ws, ref rowIndex, testData);
                CreateFooter(ws, ref rowIndex);

                AddComment(ws, 5, 10, "Zeeshan Umar's Comments", "Zeeshan Umar");

                ws.Cells.AutoFitColumns();
                //string path = Path.Combine(Path.GetDirectoryName(Path.GetDirectoryName(Application.StartupPath)), "Zeeshan Umar.jpg");
                //AddImage(ws, 10, 0, path);

                //AddCustomShape(ws, 10, 7, eShapeStyle.Ellipse, "Text inside Ellipse.");

                //Generate A File with Random name
                Byte[] bin = p.GetAsByteArray();
                string file = Guid.NewGuid().ToString() + ".xlsx";
                File.WriteAllBytes(@"C:\temp\" + file, bin);
                var endTime = DateTime.Now.Second;

                MessageBox.Show("All done it took " + (endTime - startTime) + " seconds for 25000 rows");
            }
        }