Exemplo n.º 1
0
        public void GenerateExcelSheet_Achievements(string PlayerFullName, string PlayerEmailAddress, string LeagueName, List <PlayerAchievementsExt> ListOfPlayerAchievements, ref XSSFWorkbook workbook)
        {
            Dictionary <string, int> SheetColumns = new Dictionary <string, int>();

            int ColumnNumber = 0;

            //Create new Excel sheet
            var sheet = workbook.CreateSheet("Achievements");

            //Create a header row
            var headerRow = sheet.CreateRow(0);

            SheetColumns.Add("Title", 35);
            SheetColumns.Add("Description", 35);
            SheetColumns.Add("Points", 35);

            CreateExcelFile_NPOI.AddHeaderColumns(ref SheetColumns, ref ColumnNumber, ref sheet, ref headerRow);

            //Create Header Font BOLD
            headerRow.GetCell(0).CellStyle         =
                headerRow.GetCell(1).CellStyle     =
                    headerRow.GetCell(2).CellStyle = CreateExcelFile_NPOI.CellStyle(ref workbook, true, true, null, null, null, null, null, null, null);

            //Fix the Header Row
            CreateExcelFile_NPOI.FixHeader(ref sheet);

            var DateOnly_Format = CreateExcelFile_NPOI.SetCellStyle_DateOnly(ref workbook);

            int rowNumber = 1;

            foreach (var item in ListOfPlayerAchievements)
            {
                var row = sheet.CreateRow(rowNumber++);

                row.CreateCell(0).SetCellValue(item.Title);
                row.CreateCell(1).SetCellValue(item.Description);
                row.CreateCell(2).SetCellValue(item.Points);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("---------------");
            }
            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Name: " + PlayerFullName);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Email Address: " + PlayerEmailAddress);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("League Name: " + LeagueName);
            }
        }
        public ActionResult ExportToExcel()
        {
            PlayersRepository modelRepo = new PlayersRepository();
            var players = modelRepo.ReadAll();

            Dictionary <string, int> SheetColumns = new Dictionary <string, int>();

            int ColumnNumber = 0;
            //Create new Excel workbook
            var workbook = new XSSFWorkbook();

            //Create new Excel sheet
            var sheet = workbook.CreateSheet("MVFF Players");

            //Create a header row
            var headerRow = sheet.CreateRow(0);

            SheetColumns.Add("First Name", 15);
            SheetColumns.Add("Last Name", 15);
            SheetColumns.Add("Email Address", 30);
            SheetColumns.Add("DOB", 15);
            SheetColumns.Add("Reg. Date", 15);
            SheetColumns.Add("League", 25);
            SheetColumns.Add("Weight", 15);
            SheetColumns.Add("Height", 18);
            SheetColumns.Add("How active?", 40);
            SheetColumns.Add("BMI", 15);
            SheetColumns.Add("Body Fat (%)", 15);
            SheetColumns.Add("Active", 15);
            SheetColumns.Add("Work Phone", 20);
            SheetColumns.Add("Landline", 15);
            SheetColumns.Add("Mobile", 15);
            //SheetColumns.Add("Address Line 1", 20);
            //SheetColumns.Add("Address Line 2", 20);
            //SheetColumns.Add("Country", 15);
            //SheetColumns.Add("City", 15);
            SheetColumns.Add("Post Code", 15);
            SheetColumns.Add("Notes", 30);
            SheetColumns.Add("How Hear about MANvFAT?", 80);

            CreateExcelFile_NPOI.AddHeaderColumns(ref SheetColumns, ref ColumnNumber, ref sheet, ref headerRow);

            ////Create Header Font BOLD
            headerRow.GetCell(0).CellStyle                                                                      =
                headerRow.GetCell(1).CellStyle                                                                  =
                    headerRow.GetCell(2).CellStyle                                                              =
                        headerRow.GetCell(3).CellStyle                                                          =
                            headerRow.GetCell(4).CellStyle                                                      =
                                headerRow.GetCell(5).CellStyle                                                  =
                                    headerRow.GetCell(6).CellStyle                                              =
                                        headerRow.GetCell(7).CellStyle                                          =
                                            headerRow.GetCell(8).CellStyle                                      =
                                                headerRow.GetCell(9).CellStyle                                  =
                                                    headerRow.GetCell(10).CellStyle                             =
                                                        headerRow.GetCell(11).CellStyle                         =
                                                            headerRow.GetCell(12).CellStyle                     =
                                                                headerRow.GetCell(13).CellStyle                 =
                                                                    headerRow.GetCell(14).CellStyle             =
                                                                        headerRow.GetCell(15).CellStyle         =
                                                                            headerRow.GetCell(16).CellStyle     =
                                                                                headerRow.GetCell(17).CellStyle = CreateExcelFile_NPOI.CellStyle(ref workbook, true, true, false, null, null, null, null, null, null);

            //Fix the Header Row
            CreateExcelFile_NPOI.FixHeader(ref sheet);

            var Currency_Format = CreateExcelFile_NPOI.SetCellStyle_Currency(ref workbook, "£", 2);
            var Number_Format   = CreateExcelFile_NPOI.SetCellStyle_Currency(ref workbook, "", 2);
            var DateTime_Format = CreateExcelFile_NPOI.SetCellStyle_DateTime(ref workbook, true);
            var DateOnly_Format = CreateExcelFile_NPOI.SetCellStyle_DateOnly(ref workbook);

            NumberFormatInfo provider = new NumberFormatInfo();

            provider.NumberDecimalSeparator = ".";
            provider.NumberGroupSeparator   = ",";
            provider.NumberDecimalDigits    = 2;
            provider.NumberGroupSizes       = new int[] { 2 };

            int rowNumber = 1;

            //Populate the sheet with values from the grid data
            foreach (PlayersExt p in players)
            {
                var row = sheet.CreateRow(rowNumber++);

                row.CreateCell(1).SetCellValue(p.FirstName);
                row.CreateCell(2).SetCellValue(p.LastName);
                row.CreateCell(3).SetCellValue(p.EmailAddress);
                row.CreateCell(4).SetCellValue(p.DOB.HasValue ? p.DOB.Value.ToString("dd/MM/yyyy") : "");
                row.CreateCell(5).SetCellValue(p.RegistrationDate.ToString("dd/MM/yyyy"));
                row.CreateCell(6).SetCellValue(Convert.ToDouble(p.Weight, provider));
                row.CreateCell(7).SetCellValue(p.Height);
                row.CreateCell(8).SetCellValue("TODO");
                row.CreateCell(9).SetCellValue(Convert.ToDouble(p.BMI, provider));
                row.CreateCell(10).SetCellValue(Convert.ToDouble(p.BodyFat, provider));
                row.CreateCell(11).SetCellValue((p.Active ? "Yes" : "No"));
                row.CreateCell(12).SetCellValue((p.Featured ? "Yes" : "No"));
                row.CreateCell(13).SetCellValue(p.WorkPhone);
                row.CreateCell(14).SetCellValue(p.Landline);
                row.CreateCell(15).SetCellValue(p.Mobile);
                //row.CreateCell(18).SetCellValue(p.AddressLine1);
                //row.CreateCell(19).SetCellValue(p.AddressLine2);
                //row.CreateCell(20).SetCellValue(p.Country);
                //row.CreateCell(21).SetCellValue(p.City);
                row.CreateCell(16).SetCellValue(p.PostCode);
                row.CreateCell(17).SetCellValue(p.Notes);

                string HowHeardAboutMANvFAT = p.AdvertisementOther ? p.AdvertisementOtherDetails : p.Advertisement;
                row.CreateCell(24).SetCellValue(HowHeardAboutMANvFAT);

                row.GetCell(4).CellStyle = DateOnly_Format;
                row.GetCell(5).CellStyle = DateOnly_Format;

                row.GetCell(6).CellStyle              =
                    row.GetCell(7).CellStyle          =
                        row.GetCell(11).CellStyle     =
                            row.GetCell(12).CellStyle = Number_Format;

                row.GetCell(23).CellStyle = CreateExcelFile_NPOI.CellStyle(ref workbook, false, true, false, null, null, null, null, null, null);
            }

            MemoryStream output = new MemoryStream();

            workbook.Write(output);

            //Return the result to the end user
            return(File(output.ToArray(),                                                   //The binary data of the XLS file
                        "application/vnd.ms-excel",                                         //MIME type of Excel files
                        "Players(" + DateTime.Now.ToString("dd-MM-yyyy_HHmm") + ").xlsx")); //Suggested file name in the "Save as" dialogue which will be displayed to the end user
        }
Exemplo n.º 3
0
        public void GenerateExcelSheet_WeeklyActivities(string PlayerFullName, string PlayerEmailAddress, string LeagueName, List <WeeklyActivityCSV> ListOfWeeklyActivities, ref XSSFWorkbook workbook)
        {
            Dictionary <string, int> SheetColumns = new Dictionary <string, int>();

            int ColumnNumber = 0;

            //Create new Excel sheet
            var sheet = workbook.CreateSheet("Weekly Activity");

            //Create a header row
            var headerRow = sheet.CreateRow(0);

            SheetColumns.Add("Activity Date", 35);
            SheetColumns.Add("Activity", 35);
            SheetColumns.Add("Activity Length", 35);
            SheetColumns.Add("Completed", 35);

            CreateExcelFile_NPOI.AddHeaderColumns(ref SheetColumns, ref ColumnNumber, ref sheet, ref headerRow);

            //Create Header Font BOLD
            headerRow.GetCell(0).CellStyle             =
                headerRow.GetCell(1).CellStyle         =
                    headerRow.GetCell(2).CellStyle     =
                        headerRow.GetCell(3).CellStyle = CreateExcelFile_NPOI.CellStyle(ref workbook, true, true, null, null, null, null, null, null, null);

            //Fix the Header Row
            CreateExcelFile_NPOI.FixHeader(ref sheet);

            var DateOnly_Format = CreateExcelFile_NPOI.SetCellStyle_DateOnly(ref workbook);

            int rowNumber = 1;

            foreach (var item in ListOfWeeklyActivities)
            {
                var row = sheet.CreateRow(rowNumber++);

                row.CreateCell(0).SetCellValue(item.ActivityDate);
                row.CreateCell(1).SetCellValue(item.Activity);
                row.CreateCell(2).SetCellValue(item.ActivityTime);
                row.CreateCell(3).SetCellValue(item.Completed);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("---------------");
            }
            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Name: " + PlayerFullName);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Email Address: " + PlayerEmailAddress);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("League Name: " + LeagueName);
            }
        }
Exemplo n.º 4
0
        public void GenerateExcelSheet_DailyActivities(string PlayerFullName, string PlayerEmailAddress, string LeagueName, List <PlayerDailyActivityCSV> ListOfDailyActivities, ref XSSFWorkbook workbook)
        {
            Dictionary <string, int> SheetColumns = new Dictionary <string, int>();

            int ColumnNumber = 0;

            //Create new Excel sheet
            var sheet = workbook.CreateSheet("Daily Activity");

            //Create a header row
            var headerRow = sheet.CreateRow(0);

            SheetColumns.Add("Activity Date", 35);
            SheetColumns.Add("Breakfast", 35);
            SheetColumns.Add("Lunch", 35);
            SheetColumns.Add("Dinner", 35);
            SheetColumns.Add("Snacks", 35);
            SheetColumns.Add("Drink", 35);
            SheetColumns.Add("HowHealthy", 35);

            CreateExcelFile_NPOI.AddHeaderColumns(ref SheetColumns, ref ColumnNumber, ref sheet, ref headerRow);

            //Create Header Font BOLD
            headerRow.GetCell(0).CellStyle                         =
                headerRow.GetCell(1).CellStyle                     =
                    headerRow.GetCell(2).CellStyle                 =
                        headerRow.GetCell(3).CellStyle             =
                            headerRow.GetCell(4).CellStyle         =
                                headerRow.GetCell(5).CellStyle     =
                                    headerRow.GetCell(6).CellStyle = CreateExcelFile_NPOI.CellStyle(ref workbook, true, true, null, null, null, null, null, null, null);

            //Fix the Header Row
            CreateExcelFile_NPOI.FixHeader(ref sheet);

            var DateOnly_Format = CreateExcelFile_NPOI.SetCellStyle_DateOnly(ref workbook);

            int rowNumber = 1;

            foreach (var item in ListOfDailyActivities)
            {
                var row = sheet.CreateRow(rowNumber++);

                row.CreateCell(0).SetCellValue(item.ActivityDate);
                row.CreateCell(1).SetCellValue(item.Breakfast);
                row.CreateCell(2).SetCellValue(item.Lunch);

                row.CreateCell(3).SetCellValue(item.Dinner);
                row.CreateCell(4).SetCellValue(item.Snacks);
                row.CreateCell(5).SetCellValue(item.Drink);
                row.CreateCell(6).SetCellValue(item.HowHealthy.HasValue ? item.HowHealthy.Value.ToString() : "");
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("---------------");
            }
            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Name: " + PlayerFullName);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("Player Email Address: " + PlayerEmailAddress);
            }

            {
                var row = sheet.CreateRow(rowNumber++);
                row.CreateCell(0).SetCellValue("League Name: " + LeagueName);
            }
        }