private static void BuildTitle(ExcelWorksheet ws)
        {
  
            ws.Cells[1, 5].Value = "Dashboards  To " + DateTime.Now.ToString("MM/dd/yyyy");
            ws.Cells[1, 5].Style.Font.Bold = true;

        }
        /// <summary>
        /// BuildTeachersTable
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="Teachers"></param>
        private static void BuildTeachersTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByTeacher> Teachers)
        {

            ws.Column(1).Width = 17.86;
            ws.Column(2).Width = 12.43;

            //Set Header titles
            ws.Cells[4, 1].Value = "Teachers";
            ws.Cells[4, 1].Style.Font.Bold = true;
            ws.Cells[5, 1].Value = "Teacher Name";
            ws.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 1].AutoFilter = true;
            ws.Cells[5, 2].Value = "Office Visits";
            ws.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 2].AutoFilter = true;

            //Get Data for Teachers       
            for (int i = 0; i < Teachers.Count(); i++)
            {
                ws.Cells[i + 6, 1].Value = Teachers.ElementAt(i).sent_by_contact_name;
                ws.Cells[i + 6, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                ws.Cells[i + 6, 2].Value = Teachers.ElementAt(i).total_visits;
                ws.Cells[i + 6, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            }

            //Set Header style
            using (ExcelRange rng = ws.Cells[4, 1, 5 + Teachers.Count(), 2])
            {
                rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);            
            }

        }
        private ExcelWorksheet PerformFinalFormatting(ExcelWorksheet sheet)
        {
            //Header
            sheet.HeaderFooter.FirstHeader.LeftAlignedText = "VIRGINIA TECH FOUNDATION INC.\n"
                + "UNRESTRICTED BUDGET\n" +
                "FY " + WebConfigurationManager.AppSettings["FiscalYear"].ToString();

            //Footer
            sheet.HeaderFooter.FirstFooter.CenteredText = System.DateTime.Now.ToShortDateString() +
                " Summary of VT Foundation Funding Request FY " +
                WebConfigurationManager.AppSettings["FiscalYear"].ToString();

            //Printing
            sheet.PrinterSettings.Orientation = eOrientation.Landscape;
            sheet.PrinterSettings.FitToPage = true;
            sheet.PrinterSettings.FitToWidth = 1;
            sheet.PrinterSettings.FitToHeight = 0;
            ExcelRange range_numberFormatting =
                sheet.Cells[1, NUM_COLUMNS - SUMMARY_DATA_COLUMNS + 1, 100, NUM_COLUMNS];

            //Cell styling
            range_numberFormatting.Style.Numberformat.Format = "_($* #,##0_);_($* (#,##0);_($* \"-\"_);_(@_)";

            sheet.Cells.AutoFitColumns();

            return sheet;
        }
Exemplo n.º 4
0
        private void ApplyDataToShopSignWorksheet(System.Data.DataTable dt, ExcelWorksheet worksheet)
        {
            int colIndex = 3;
            ExcelRange columnTemplate = worksheet.Cells["C1:C7"];
            ExcelRange pastedColumn = worksheet.Cells["D1"];//1, colIndex, 7, colIndex];
            //columnTemplate.Copy(pastedColumn);
            for (int i = 3; i < dt.Columns.Count; i++)
            {
                if (colIndex > 3)
                {
                    pastedColumn = worksheet.Cells[1, colIndex];//1, colIndex, 7, colIndex];
                    columnTemplate.Copy(pastedColumn);
                }

                pastedColumn[1, colIndex].Value = dt.Columns[i].ColumnName;
                pastedColumn[2, colIndex].Value = dt.Rows[0][i];
                pastedColumn[3, colIndex].Value = dt.Rows[1][i];
                pastedColumn[4, colIndex].Value = dt.Rows[2][i];
                pastedColumn[5, colIndex].Value = dt.Rows[3][i];
                pastedColumn[6, colIndex].Value = dt.Rows[4][i];
                pastedColumn[7, colIndex].Value = dt.Rows[5][i];

                colIndex++;

            }
            //Xoa cot template
        }
Exemplo n.º 5
0
		public CalculationDataDrawable(CalculationData data, string clientNick, int columnCount, ExcelWorksheet excel)
		{
			_data = data;
			_clientNick = clientNick;
			_excel = excel;
			_columnCount = columnCount;
		}
Exemplo n.º 6
0
 private static void AddWebpage(List<Webpage> webpages, int index, ExcelWorksheet wsItems)
 {
     var rowId = index + 2;
     var webpage = webpages[index];
     wsItems.Cells["A" + rowId].Value = webpage.UrlSegment;
     wsItems.Cells["A" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["B" + rowId].Value = webpage.Parent != null ? webpage.Parent.UrlSegment : String.Empty;
     wsItems.Cells["B" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["C" + rowId].Value = webpage.DocumentType;
     wsItems.Cells["D" + rowId].Value = webpage.Name;
     wsItems.Cells["D" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["E" + rowId].Value = webpage.BodyContent;
     wsItems.Cells["E" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
     wsItems.Cells["F" + rowId].Value = webpage.MetaTitle;
     wsItems.Cells["G" + rowId].Value = webpage.MetaDescription;
     wsItems.Cells["G" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
     wsItems.Cells["H" + rowId].Value = webpage.MetaKeywords;
     wsItems.Cells["I" + rowId].Value = string.Join(",", webpage.Tags.Select(tag => tag.Name));
     wsItems.Cells["I" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["J" + rowId].Value = webpage.RevealInNavigation;
     wsItems.Cells["K" + rowId].Value = webpage.DisplayOrder;
     wsItems.Cells["L" + rowId].Value = webpage.RequiresSSL;
     wsItems.Cells["M" + rowId].Value = webpage.PublishOn.HasValue
         ? webpage.PublishOn.Value.ToString("yyyy-MM-dd HH:mm:ss")
         : String.Empty;
     wsItems.Cells["N" + rowId].Value = string.Join(",", webpage.Urls.Select(history => history.UrlSegment));
     wsItems.Cells["N" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
 }
        public SheetWrapper(ExcelWorksheet sheet,bool revriteExising=false)
        {
            ColumnMap = new Dictionary<string, int>();
            Sheet = sheet;

            if (sheet.Dimension == null || revriteExising)
            {
                RowCursor = 2;
                return;
                
            }

            if (sheet.Dimension.Rows != 0)
            {
                RowCursor = sheet.Dimension.Rows + 1;
            }

            if (sheet.Dimension.Columns != 0)
            {
                for (int i = 1; i < sheet.Dimension.Columns; i++)
                {
                    ColumnMap.AddOrOvewrite(sheet.Cells[1, i].Value.ToString(), i);
                }
            }
        }
Exemplo n.º 8
0
 private void AddSteps(ExcelWorksheet xlWorkSheet, ITestAction testAction, Dictionary<string, string> replacements, ref int row)
 {
     var testStep = testAction as ITestStep;
     var group = testAction as ITestActionGroup;
     var sharedRef = testAction as ISharedStepReference;
     if (null != testStep)
     {
         CleanupText(xlWorkSheet.Cells[row, 3], testStep.Title.ToString(), replacements);
         CleanupText(xlWorkSheet.Cells[row, 5], testStep.ExpectedResult.ToString(), replacements);
     }
     else if (null != group)
     {
         foreach (var action in group.Actions)
         {
             AddSteps(xlWorkSheet, action, replacements, ref row);
         }
     }
     else if (null != sharedRef)
     {
         var step = sharedRef.FindSharedStep();
         foreach (var action in step.Actions)
         {
             AddSteps(xlWorkSheet, action, replacements, ref row);
         }
     }
     row++;
 }
Exemplo n.º 9
0
 internal ExcelNamedRange(string name,ExcelWorkbook wb, ExcelWorksheet nameSheet, int index) :
     base(wb, nameSheet, name, true)
 {
     Name = name;
     _sheet = nameSheet;
     Index = index;
 }
Exemplo n.º 10
0
        internal ExcelWriter(ExcelWorksheet excelWorksheet, int firstLine, int firstColumn,
            int entitiesCount)
        {
            if (excelWorksheet == null)
            {
                throw new ArgumentNullException("excelWorksheet");
            }
            if (firstLine < 1)
            {
                throw new ArgumentException("firstLine");
            }
            if (firstColumn < 1)
            {
                throw new ArgumentException("firstColumn");
            }

            _excelWorksheet = excelWorksheet;

            _lastGroupLine = firstLine;
            _curLine = firstLine;
            _firstLine = firstLine;
            _entityColumnsCount = entitiesCount;

            _tableColumn = firstColumn;
            _groupColumn = _tableColumn + 1;
            _rowColumn = _groupColumn + 1;
            _maxResultsColumn = _rowColumn + 1;
            _entitiesColumn = _maxResultsColumn + 1;
        }
Exemplo n.º 11
0
        protected int GetColumnIndexByName(ExcelWorksheet sheet, string columnName)
        {
            const int notFound = -1;
            if (sheet == null)
            {
                return notFound;
            }

            var columnIndex = 1;
            while (sheet.Cells[1, columnIndex].Value != null)
            {
                var value = sheet.Cells[1, columnIndex].Value.ToString();
                if (string.IsNullOrEmpty(value))
                {
                    columnIndex = notFound;
                    break;
                }

                if (string.Compare(value, columnName, StringComparison.InvariantCultureIgnoreCase) == 0)
                {
                    break;
                }

                columnIndex++;
            }

            return columnIndex;
        }
        private void SetSportaSheet(ExcelWorksheet worksheet)
        {
            ExcelHelper.SetHeader(
                worksheet,
                ExcelExportResources.CompetitionVolgnummer,
                ExcelExportResources.CompetitionIndex,
                ExcelExportResources.CompetitionLidnummer,
                ExcelExportResources.PlayerName,
                ExcelExportResources.PlayerRanking,
                ExcelExportResources.PlayerRankingValue
            );

            int i = 2;
            var rankingValueConverter = new KlassementValueConverter();
            foreach (var player in _players.Where(x => x.ClubIdSporta == Constants.OwnClubId).OrderBy(x => x.VolgnummerSporta))
            {
                worksheet.Cells[i, 1].Value = player.VolgnummerSporta;
                worksheet.Cells[i, 2].Value = player.IndexSporta;
                worksheet.Cells[i, 3].Value = player.LidNummerSporta;
                worksheet.Cells[i, 4].Value = player.Naam;
                worksheet.Cells[i, 5].Value = player.KlassementSporta;
                worksheet.Cells[i, 6].Value = rankingValueConverter.Sporta(player.KlassementSporta);

                i++;
            }
            worksheet.Cells.AutoFitColumns();
        }
Exemplo n.º 13
0
 private static void AutofitColumns(ExcelWorksheet wsItems)
 {
     wsItems.Cells["A:B"].AutoFitColumns();
     wsItems.Cells["D:D"].AutoFitColumns();
     wsItems.Cells["F:F"].AutoFitColumns();
     wsItems.Cells["I:AF"].AutoFitColumns();
 }
Exemplo n.º 14
0
 public void BaseInitialize()
 {
     var dir = AppDomain.CurrentDomain.BaseDirectory;
     var Package = new ExcelPackage(new FileInfo(Path.Combine(dir, "Workbooks", "FormulaTest.xlsx")));
     Worksheet = Package.Workbook.Worksheets["ValidateFormulas"];
     Package.Workbook.Calculate();
 }
Exemplo n.º 15
0
		/// <summary>
		/// Creates a new instance of the ExcelColumn class.  
		/// For internal use only!
		/// </summary>
		/// <param name="Worksheet"></param>
		/// <param name="col"></param>
		protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
        {
            _worksheet = Worksheet;
            _columnMin = col;
            _columnMax = col;
            _width = _worksheet.DefaultColWidth;
        }
Exemplo n.º 16
0
		/// <summary>
		/// Creates a new instance of the ExcelRow class. 
		/// For internal use only!
		/// </summary>
		/// <param name="Worksheet">The parent worksheet</param>
		/// <param name="row">The row number</param>
		protected internal ExcelRow(ExcelWorksheet Worksheet, int row)
		{
			_xlWorksheet = Worksheet;

			//  Search for the existing row
			_rowElement = (XmlElement) Worksheet.WorksheetXml.SelectSingleNode(string.Format("//d:sheetData/d:row[@r='{0}']", row), _xlWorksheet.NameSpaceManager);
			if (_rowElement == null)
			{
				// We didn't find the row, so add a new row element.
				// HOWEVER we MUST insert new row in the correct position - otherwise Excel 2007 will complain!!!
				_rowElement = Worksheet.WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
				_rowElement.SetAttribute("r", row.ToString());

				// now work out where to insert the new row
				XmlNode sheetDataNode = Worksheet.WorksheetXml.SelectSingleNode("//d:sheetData", _xlWorksheet.NameSpaceManager);
				if (sheetDataNode != null)
				{
					XmlNode followingRow = null;
					foreach (XmlNode currentRow in Worksheet.WorksheetXml.SelectNodes("//d:sheetData/d:row", _xlWorksheet.NameSpaceManager))
					{
						int rowFound = Convert.ToInt32(currentRow.Attributes.GetNamedItem("r").Value);
						if (rowFound > row)
						{
							followingRow = currentRow;
							break;
						}
					}
					if (followingRow == null)
						// no data rows exist, so just add row
						sheetDataNode.AppendChild(_rowElement);
					else
						sheetDataNode.InsertBefore(_rowElement, followingRow);
				}
			}
		}
Exemplo n.º 17
0
        private static void AddHeaderRow(ExcelWorksheet worksheet)
        {
            worksheet.Cells[1, 1].Value = "Game Name";
            worksheet.Cells[1, INDEX_OF_PLAYED_GAME_ID].Value = "Played Game Id";
            worksheet.Cells[1, INDEX_OF_DATE_PLAYED].Value = "Date Played (UTC)";
            worksheet.Cells[1, INDEX_OF_GAME_DEFINITION_ID].Value = "Game Id";
            worksheet.Cells[1, INDEX_OF_GAME_DEFINITION_NAME].Value = "Game Name";
            worksheet.Cells[1, INDEX_OF_BOARD_GAME_GEEK_OBJECT_ID].Value = "BoardGameGeek Object Id";
            worksheet.Cells[1, INDEX_OF_GAMING_GROUP_ID].Value = "Gaming Group Id";
            worksheet.Cells[1, INDEX_OF_GAMING_GROUP_NAME].Value = "Gaming Group Name";
            worksheet.Cells[1, INDEX_OF_DATE_RECORDED].Value = "Date Recorded (UTC)";
            worksheet.Cells[1, INDEX_OF_NOTES].Value = "Notes";
            worksheet.Cells[1, INDEX_OF_NUMBER_OF_PLAYERS].Value = "Number Of Players";
            worksheet.Cells[1, INDEX_OF_WINNING_PLAYER_IDS].Value = "Winning Player Ids";
            worksheet.Cells[1, INDEX_OF_WINNING_PLAYER_NAMES].Value = "Winning Player Names";

            using (var range = worksheet.Cells[1, 1, 1, 12])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(Color.Black);
                range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                range.Style.ShrinkToFit = false;
                range.AutoFitColumns();
            }
        }
Exemplo n.º 18
0
        public void AddGroundVehicleRowToSpreadsheet(GroundVehicle groundVehicle, ExcelWorksheet worksheet)
        {
            if (groundVehicle != null)
            {
                // Get the row we are up to
                int indexPosition = worksheet.Dimension.End.Row + 1;

                // Add values
                worksheet.Cells[$"A{indexPosition}"].Value = groundVehicle.Name;
                worksheet.Cells[$"B{indexPosition}"].Value = groundVehicle.Country;
                worksheet.Cells[$"C{indexPosition}"].Value = Enum.GetName(typeof(VehicleTypeEnum), groundVehicle.VehicleType);
                worksheet.Cells[$"D{indexPosition}"].Value = groundVehicle.Rank;
                worksheet.Cells[$"E{indexPosition}"].Value = groundVehicle.BattleRating;
                worksheet.Cells[$"F{indexPosition}"].Value = groundVehicle.Weight;
                worksheet.Cells[$"G{indexPosition}"].Value = groundVehicle.WeightUnit.Name;
                worksheet.Cells[$"H{indexPosition}"].Value = groundVehicle.EnginePower;
                worksheet.Cells[$"I{indexPosition}"].Value = groundVehicle.EnginePowerUnit.Name;
                worksheet.Cells[$"J{indexPosition}"].Value = groundVehicle.MaxSpeed;
                worksheet.Cells[$"K{indexPosition}"].Value = groundVehicle.MaxSpeedUnit.Name;
                worksheet.Cells[$"L{indexPosition}"].Value = groundVehicle.HullArmourThickness;
                worksheet.Cells[$"M{indexPosition}"].Value = groundVehicle.SuperstructureArmourThickness;
                worksheet.Cells[$"N{indexPosition}"].Value = groundVehicle.TimeForFreeRepair;
                worksheet.Cells[$"O{indexPosition}"].Value = groundVehicle.MaxRepairCost;
                worksheet.Cells[$"P{indexPosition}"].Value = groundVehicle.MaxRepairCostUnit.Name;
                worksheet.Cells[$"Q{indexPosition}"].Value = groundVehicle.PurchaseCost;
                worksheet.Cells[$"R{indexPosition}"].Value = groundVehicle.PurchaseCostUnit.Name;
                worksheet.Cells[$"S{indexPosition}"].Value = groundVehicle.LastModified;
            }
        }
Exemplo n.º 19
0
 public override ExcelCellAddress AddItemToWorksheet(ExcelWorksheet worksheet)
 {
     var cell = worksheet.Cells[Row + 1, Column + 1];
     cell.Style.Numberformat.Format = NumberFormat;
     cell.Value = Value;
     return cell.End;
 }
Exemplo n.º 20
0
 /// <summary>
 /// A named range
 /// </summary>
 /// <param name="name">The name</param>
 /// <param name="nameSheet">The sheet containing the name. null if its a global name</param>
 /// <param name="sheet">Sheet where the address points</param>
 /// <param name="address">The address</param>
 /// <param name="index">The index in the collection</param>
 public ExcelNamedRange(string name, ExcelWorksheet nameSheet , ExcelWorksheet sheet, string address, int index)
     : base(sheet, address)
 {
     Name = name;
     _sheet = nameSheet;
     Index = index;
 }
Exemplo n.º 21
0
 public void Apply(ExcelWorksheet ws, int rows)
 {
     for (int i = 0; i < _formats.Count; i++)
     {
         ws.Cells[_startRow, _startCol, _startRow + rows, _startCol + i].Style.Numberformat.Format = _formats[i];
     }
 }
Exemplo n.º 22
0
        /// <summary>
        /// Populate award objects from spreadsheet
        /// </summary>
        /// <param name="workSheet"></param>
        /// <param name="firstRowHeader"></param>
        /// <returns></returns>
        static IEnumerable<AcademyAward> PopulateAwards(ExcelWorksheet workSheet, bool firstRowHeader)
        {
            IList<AcademyAward> awards = new List<AcademyAward>();

            if (workSheet != null)
            {
                Dictionary<string, int> header = new Dictionary<string,int>();

                for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
                {
                    //Assume the first row is the header.  Then use the column match ups by name to determine the index.
                    //This will allow you to have the order of the columns change without any affect.
                    
                    if (rowIndex == 1 && firstRowHeader)
                    {
                        header = ExcelHelper.GetExcelHeader(workSheet, rowIndex);
                    }
                    else
                    {
                        awards.Add(new AcademyAward{
                            Year = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Year"),
                            Category = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Category"),
                            Nominee = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Nominee"),
                            AdditionalInfo = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "AdditionalInfo"),
                            Won = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Won?")
                        });

                    }
                }
            }

            return awards;
        }
Exemplo n.º 23
0
 /// <summary>
 /// Creates a new ExcelWorksheetView which provides access to all the view states of the worksheet.
 /// </summary>
 /// <param name="ns"></param>
 /// <param name="node"></param>
 /// <param name="xlWorksheet"></param>
 internal ExcelWorksheetView(XmlNamespaceManager ns, XmlNode node,  ExcelWorksheet xlWorksheet)
     : base(ns, node)
 {
     _worksheet = xlWorksheet;
     SchemaNodeOrder = new string[] { "sheetViews", "sheetView", "pane", "selection" };
     Panes = LoadPanes();
 }
Exemplo n.º 24
0
        public static ExcelRange GetLastFullEmptyRow(ExcelWorksheet sheet, params string[] columnsToCheck)
        {
            ExcelRange firstCellOfEmptyRow = sheet.Cells["A1"];
            List<ExcelRange> lastRows = new List<ExcelRange>();
            foreach (string column in columnsToCheck) {
                // Start at the top left corner
                var cell = sheet.Cells[column + "1"];

                // Get to last empty row
                while (!string.IsNullOrEmpty(cell.Text)) {
                    cell = cell.NextRow();
                }
                lastRows.Add(cell);
            }

            var lastEmptyCellColumn = GetRow(firstCellOfEmptyRow);
            foreach (ExcelRange row in lastRows) {
                var rowColumn = GetRow(row);
                if (rowColumn > lastEmptyCellColumn) {
                    lastEmptyCellColumn = rowColumn;
                }
            }

            return sheet.Cells["A" + lastEmptyCellColumn];
        }
        private List<UploadViewFileColumn> MakeColumnTypeList(int headerRow, ExcelWorksheet worksheet)
        {
            // this assumes that columns in the spreadsheet with blank header rows do
            // not contain relevant data

            List<UploadViewFileColumn> columnList = new List<UploadViewFileColumn>();

            for (int column = 1; column <= worksheet.Dimension.End.Column; column++)
            {
                if (worksheet.Cells[headerRow, column].Value != null)
                {
                    if (worksheet.Cells[headerRow, column].Value.ToString() != "")
                    {
                        UploadViewFileColumn homeViewColumn = new UploadViewFileColumn
                        {
                            ColumnName = worksheet.Cells[headerRow, column].Value.ToString().Trim(),
                            DataType = SetType(worksheet.Cells[headerRow + 1, column].Value)
                        };
                        if (homeViewColumn.ColumnName == "") homeViewColumn.ColumnName = "NONAME";
                        columnList.Add(homeViewColumn);
                    }
                }

            }

            return columnList;
        }
        private int FindHeaderRow(ExcelWorksheet worksheet)
        {
            // let's try to find the header row and then use the following row as the
            // template for the data types

            // let's assume the header row is the first row with more than half of its
            // cells not empty

            bool foundHeaderRow = false;
            int rowCounter = 1;

            while (!foundHeaderRow)
            {
                int notEmptyCells = 0;
                for (int column = 1; column < worksheet.Dimension.End.Column; column++)
                {
                    if (worksheet.Cells[rowCounter, column].Value != null)
                    {
                        if (worksheet.Cells[rowCounter, column].Value.ToString() != "") ++notEmptyCells;
                    }
                }

                if (((double)notEmptyCells / worksheet.Dimension.End.Column) > .5) foundHeaderRow = true;
                ++rowCounter;
            }

            //somehow we decide which one is the header row

            return (rowCounter - 1);
        }
Exemplo n.º 27
0
        private static void BuildHeader(ExcelWorksheet worksheet)
        {
            worksheet.Cells["A1"].Value = "Softuni OOP Course Results";
            using (ExcelRange r = worksheet.Cells["A1:M1"])
            {
                r.Merge = true;
                r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
                r.Style.Font.Color.SetColor(Color.White);
                r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
            }

            worksheet.Cells[2, 1].Value = "ID";
            worksheet.Cells[2, 2].Value = "First name";
            worksheet.Cells[2, 3].Value = "Last Name";
            worksheet.Cells[2, 4].Value = "Email";
            worksheet.Cells[2, 5].Value = "Gender";
            worksheet.Cells[2, 6].Value = "Student type";
            worksheet.Cells[2, 7].Value = "Exam result";
            worksheet.Cells[2, 8].Value = "Homework sent";
            worksheet.Cells[2, 9].Value = "Homework evaluated";
            worksheet.Cells[2, 10].Value = "Teamwork";
            worksheet.Cells[2, 11].Value = "Attendances";
            worksheet.Cells[2, 12].Value = "Bonus";
            worksheet.Cells[2, 13].Value = "Result";
        }
Exemplo n.º 28
0
        /// <summary>
        /// A named range
        /// </summary>
        /// <param name="name">The name</param>
        /// <param name="nameSheet">The sheet containing the name. null if its a global name</param>
        /// <param name="sheet">Sheet where the address points</param>
        /// <param name="address">The address</param>
        public ExcelNamedRange(string name, ExcelWorksheet nameSheet , ExcelWorksheet sheet, string address) :
            base(sheet, address)
        {
            Name = name;
            _sheet = nameSheet;

        }
        private void ExportTable(ExcelWorksheet workSheet, ReportTable table, ICollection<string> names)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }
            if (workSheet == null)
            {
                throw new ArgumentNullException("workSheet");
            }

            const int firstColumn = 2;
            var firstLine = workSheet.Dimension != null ? workSheet.Dimension.End.Row + 2 : 2;
            var xlWriter = new ExcelWriter(workSheet, firstLine, firstColumn, names.Count);

            xlWriter.PutTableHead(table.Name, names);

            ICollection<int> totalRating = null;
            foreach (var group in table.ReportGroups)
            {
                var groupResults = GenerateGroup(xlWriter, group);
                totalRating = totalRating == null ? groupResults : SummarizeResults(totalRating, groupResults);
            }

            xlWriter.PutTableResults(totalRating);
            xlWriter.SetGlobalStyles();
        }
Exemplo n.º 30
0
        public void Import(ExcelWorksheet sheet, IOrganizationService service)
        {
            var rowsCount = sheet.Dimension.Rows;

            for (var rowI = 1; rowI < rowsCount; rowI++)
            {
                var xml = new StringBuilder(string.Format("<LocLabel Id=\"{0}\"><Titles>", ZeroBasedSheet.Cell(sheet, rowI, 2).Value));

                var columnIndex = 3;

                while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
                {
                    xml.Append(string.Format("<Title description=\"{0}\" languagecode=\"{1}\"/>",
                                             ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value,
                                             int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString())));

                    columnIndex++;
                }

                xml.Append("</Titles></LocLabel>");

                var ribbonDiff = new Entity("ribbondiff") { Id = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString()) };
                ribbonDiff["rdx"] = xml.ToString();

                service.Update(ribbonDiff);
            }
        }
        private static int ExportSection(List <int> languages, ExcelWorksheet sectionSheet, int line,
                                         CrmFormSection crmFormSection)
        {
            var cell = 0;

            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Id.ToString("B");
            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Entity;
            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Form;
            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.FormUniqueId.ToString("B");
            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.FormId.ToString("B");
            ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Tab;

            foreach (var lcid in languages)
            {
                bool exists = crmFormSection.Names.ContainsKey(lcid);
                ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = exists
                    ? crmFormSection.Names.First(n => n.Key == lcid).Value
                    : string.Empty;
            }

            line++;
            return(line);
        }
Exemplo n.º 32
0
        private void InsertQuotationItemDetails(int count, OfficeOpenXml.ExcelWorksheet worksheet, List <QuotationItemDetailDto> quotationItemList, bool withDiscount)
        {
            var RowNumber = 29;

            for (int i = 0; i < count; i++)
            {
                if (i < quotationItemList.Count)
                {
                    worksheet.Cells[RowNumber, 1, RowNumber, 1].Value = quotationItemList[i].Style;
                    worksheet.Cells[RowNumber, 3, RowNumber, 3].Value = quotationItemList[i].Description;
                    worksheet.Cells[RowNumber, 4, RowNumber, 4].Value = quotationItemList[i].Quantity;
                    if (withDiscount)
                    {
                        worksheet.Cells[RowNumber, 7, RowNumber, 7].Value = quotationItemList[i].UnitPrice;
                    }
                    else
                    {
                        worksheet.Cells[RowNumber, 5, RowNumber, 5].Value = quotationItemList[i].UnitPrice;
                    }
                }
                RowNumber++;
            }

            var defaultItemrows = 26;

            if (defaultItemrows > quotationItemList.Count)
            {
                var deleteFrom = 56;
                worksheet.DeleteRow(deleteFrom, 973, true);
            }
            else
            {
                var numberOfRowsToDelete = 974 - (quotationItemList.Count - defaultItemrows);
                worksheet.DeleteRow(RowNumber, numberOfRowsToDelete, true);
            }
        }
Exemplo n.º 33
0
        public void PrepareFormSections(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
        {
            foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
            {
                var sectionId = row.Cells[0].Value.ToString();
                var formId    = new Guid(row.Cells[4].Value.ToString());

                var form = forms.FirstOrDefault(f => f.Id == formId);
                if (form == null)
                {
                    form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
                    forms.Add(form);
                }

                // Load formxml
                var formXml = form.GetAttributeValue <string>("formxml");
                var docXml  = new XmlDocument();
                docXml.LoadXml(formXml);

                var sectionNode =
                    docXml.DocumentElement.SelectSingleNode(
                        string.Format("tabs/tab/columns/column/sections/section[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", sectionId.ToLower()));
                if (sectionNode != null)
                {
                    var columnIndex = 6;
                    while (row.Cells[columnIndex].Value != null)
                    {
                        UpdateXmlNode(sectionNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
                                      row.Cells[columnIndex].Value.ToString());
                        columnIndex++;
                    }
                }

                form["formxml"] = docXml.OuterXml;
            }
        }
Exemplo n.º 34
0
        public void PrepareFormLabels(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
        {
            foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
            {
                var labelId = row.Cells[0].Value.ToString();
                var formId  = new Guid(row.Cells[4].Value.ToString());

                var form = forms.FirstOrDefault(f => f.Id == formId);
                if (form == null)
                {
                    form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
                    forms.Add(form);
                }

                // Load formxml
                var formXml = form.GetAttributeValue <string>("formxml");
                var docXml  = new XmlDocument();
                docXml.LoadXml(formXml);

                var cellNode =
                    docXml.DocumentElement.SelectSingleNode(
                        string.Format("tabs/tab/columns/column/sections/section/rows/row/cell[@id='{0}']", labelId));
                if (cellNode != null)
                {
                    var columnIndex = 8;
                    while (row.Cells[columnIndex].Value != null)
                    {
                        UpdateXmlNode(cellNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
                                      row.Cells[columnIndex].Value.ToString());
                        columnIndex++;
                    }
                }

                form["formxml"] = docXml.OuterXml;
            }
        }
Exemplo n.º 35
0
        public void ImportFormName(ExcelWorksheet sheet, IOrganizationService service)
        {
            var rowsCount  = sheet.Dimension.Rows;
            var cellsCount = sheet.Dimension.Columns;

            for (var rowI = 1; rowI < rowsCount; rowI++)
            {
                var currentFormId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString());

                var request = new SetLocLabelsRequest
                {
                    EntityMoniker = new EntityReference("systemform", currentFormId),
                    AttributeName = ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Name" ? "name" : "description"
                };

                var labels = new List <LocalizedLabel>();

                var columnIndex = 4;
                while (columnIndex < cellsCount)
                {
                    if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
                    {
                        var lcid  = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
                        var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();

                        labels.Add(new LocalizedLabel(label, lcid));
                    }

                    columnIndex++;
                }

                request.Labels = labels.ToArray();

                service.Execute(request);
            }
        }
Exemplo n.º 36
0
        public void PrepareFormTabs(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
        {
            var rowsCount = sheet.Dimension.Rows;

            for (var rowI = 1; rowI < rowsCount; rowI++)
            {
                var tabId  = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString();
                var formId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString());

                var form = forms.FirstOrDefault(f => f.Id == formId);
                if (form == null)
                {
                    form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
                    forms.Add(form);
                }

                // Load formxml
                var formXml = form.GetAttributeValue <string>("formxml");
                var docXml  = new XmlDocument();
                docXml.LoadXml(formXml);

                var tabNode = docXml.DocumentElement.SelectSingleNode(string.Format("tabs/tab[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", tabId.ToLower()));
                if (tabNode != null)
                {
                    var columnIndex = 4;
                    while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
                    {
                        UpdateXmlNode(tabNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
                                      ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString());
                        columnIndex++;
                    }
                }

                form["formxml"] = docXml.OuterXml;
            }
        }
Exemplo n.º 37
0
        private static int ExportField(List <int> languages, ExcelWorksheet labelSheet, int line,
                                       CrmFormLabel crmFormLabel)
        {
            var cell = 0;

            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Id.ToString("B");
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Form;
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.FormUniqueId.ToString("B");
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.FormId.ToString("B");
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Tab;
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Section;
            ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Attribute;

            foreach (var lcid in languages)
            {
                bool exists = crmFormLabel.Names.ContainsKey(lcid);
                ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = exists
                    ? crmFormLabel.Names.First(n => n.Key == lcid).Value
                    : string.Empty;
            }

            line++;
            return(line);
        }
Exemplo n.º 38
0
        public string ExportData(int?id)
        {
            string rootFolder = _hostingEnvironment.WebRootPath;

            string fileName = @"ExportServers.xlsx";

            FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));

            using (ExcelPackage package = new ExcelPackage(file))
            {
                //To be modified the way currentMonth is calculated
                var serverList = this.db.Servers.Where(s => s.Vulnerabilities.Any(v => v.VulnerabilityId == id))
                                 .Where(s => s.LastDetected.Year == currentYear && s.LastDetected.Month == currentMonth).ToList();

                var vulnTitle = this.db.Vulnerabilities.FirstOrDefault(v => v.Id == id).Name;



                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("AffectedServers");
                int            totalRows = serverList.Count();

                var titleRange   = worksheet.Cells[1, 1, 1, 11];
                var contentRange = worksheet.Cells[2, 1, totalRows, 11];
                titleRange.AutoFilter = true;
                titleRange.AutoFitColumns();
                worksheet.DefaultColWidth         = 20;
                titleRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
                titleRange.Style.Fill.BackgroundColor.SetColor(Color.Black);
                titleRange.Style.Font.Color.SetColor(Color.White);


                worksheet.Cells[1, 1].Value  = "IP";
                worksheet.Cells[1, 2].Value  = "System name";
                worksheet.Cells[1, 3].Value  = "System status";
                worksheet.Cells[1, 4].Value  = "System type";
                worksheet.Cells[1, 5].Value  = "OS Version";
                worksheet.Cells[1, 6].Value  = "Technical owner";
                worksheet.Cells[1, 7].Value  = "Downtime contact";
                worksheet.Cells[1, 8].Value  = "Last detected date";
                worksheet.Cells[1, 9].Value  = "Port";
                worksheet.Cells[1, 10].Value = "Notes";
                worksheet.Cells[1, 11].Value = "Vulnerability title";

                int i = 0;
                for (int row = 2; row <= totalRows + 1; row++)
                {
                    worksheet.Cells[row, 1].Value = serverList[i].Ip;
                    worksheet.Cells[row, 2].Value = serverList[i].SystemName;
                    worksheet.Cells[row, 3].Value = serverList[i].SystemStatus;
                    worksheet.Cells[row, 4].Value = serverList[i].SystemType;
                    worksheet.Cells[row, 5].Value = serverList[i].OSversion;
                    worksheet.Cells[row, 6].Value = serverList[i].TechnicalOwner;
                    worksheet.Cells[row, 7].Value = serverList[i].DowntimeContact;
                    //worksheet.Cells[row, 8].Value = String.Format("{0:MM/dd/yyyy HH:mm:ss}",serverList[i].LastDetected);
                    worksheet.Cells[row, 8].Value  = serverList[i].LastDetected.ToShortDateString();
                    worksheet.Cells[row, 9].Value  = serverList[i].Port;
                    worksheet.Cells[row, 10].Value = serverList[i].Notes;
                    worksheet.Cells[row, 11].Value = vulnTitle;

                    i++;
                }

                package.Save();
            }

            return(" Server list has been exported successfully");
        }
        private void BtnExcel_Click(object sender, RoutedEventArgs e)
        {
            List <GraduateDTO> _List = new List <GraduateDTO>();

            try
            {
                OfficeOpenXml.ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial; // giấy phép sử dụng thư viện bản thương mại

                OpenFileDialog _opendialog = new OpenFileDialog();
                _opendialog.Filter = "Excel Files|*.xls;*.xlsx";

                if (_opendialog.ShowDialog() == true)
                {
                    string _filepath = _opendialog.FileName;

                    var _package = new OfficeOpenXml.ExcelPackage(new FileInfo(_filepath));       // Mở file Excel

                    OfficeOpenXml.ExcelWorksheet _sheetCurrent = _package.Workbook.Worksheets[0]; // mở sheet (trang) 1 trong file excel

                    // duyệt tuần tự dòng thứ 2 tới dòng cuối của file
                    // duyệt file excel tương tự duyệt mảng 2 chiều
                    // chỉ số cột trong excel bắt đầu từ 1

                    for (int row = _sheetCurrent.Dimension.Start.Row + 1; row <= _sheetCurrent.Dimension.End.Row; row++)
                    {
                        try
                        {
                            int col = 1;

                            #region lấy ra tên ở vị trí dòng 2 cột 1, col++ sau khi thực hiện câu lệnh tăng cột lên 1 (toán tử hậu tố)

                            int _idmonhoc               = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _idsinhvien             = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _toanroirac             = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _giaitich               = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _xacsuatthongke         = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _kythuatlaptrinh        = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _mangmaytinh            = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _cosodulieu             = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _kynanggiaotiep         = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _laptrinhhuongdoituong  = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _cautrucdulieugiaithuat = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _hedieuhanh             = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _phantichthietke        = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _trituenhantao          = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _phanmemmanguonmo       = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _baotriphanmem          = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _thuongmaidientu        = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _doan1            = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _doan2            = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _doan3            = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _chungchingoaingu = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);
                            int _hocphantuchon    = Convert.ToInt32(_sheetCurrent.Cells[row, col++].Value);

                            #endregion

                            // tạo mới với các thuộc tính là các thuộc tính vừa đọc được trong file excel
                            AddSubjectDebt(_idsinhvien, _toanroirac, _giaitich, _xacsuatthongke, _kythuatlaptrinh, _mangmaytinh, _cosodulieu, _kynanggiaotiep, _laptrinhhuongdoituong, _cautrucdulieugiaithuat, _hedieuhanh,
                                           _phantichthietke, _trituenhantao, _phanmemmanguonmo, _baotriphanmem, _thuongmaidientu, _doan1, _doan2, _doan3, _chungchingoaingu, _hocphantuchon);
                        }

                        catch (Exception ex) { MessageBox.Show(ex.Message); }
                    }
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

            LoadData();
        }
Exemplo n.º 40
0
        public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
        {
            timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;

            // Start diagnostics timer
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);// DateTime.Now;//Datetime.Now;//DateTime.Now;//Datetime.Now;

            // Now gather and analyze data for the report
            SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
            requiredDataElements.NeedsSensorData            = true;
            requiredDataElements.NeedsPaymentData           = false;
            requiredDataElements.NeedsOverstayData          = false;
            requiredDataElements.NeedsEnforcementActionData = false;
            this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, _ReportParams);
            this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);

            OfficeOpenXml.ExcelWorksheet ws = null;

            using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
            {
                // Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
                // Create the worksheet
                ws = pck.Workbook.Worksheets.Add("Summary");

                // Render the standard report title lines
                rowIdx = 1; // Excel uses 1-based indexes
                colIdx = 1;
                RenderCommonReportTitle(ws, this._ReportName);

                // Render common report header for enforcement activity restriction filter, but only if its not for all activity
                if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
                {
                    rowIdx++;
                    colIdx = 1;
                    RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
                }

                // Render common report header for regulated hour restriction filter
                rowIdx++;
                colIdx = 1;
                RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);

                using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
                {
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;                 //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
                }

                rowIdx++;
                colIdx = 1;
                int hyperlinkstartRowIdx = rowIdx;

                if (_ReportParams.IncludeMeterSummary == true)
                {
                    RenderWorksheetHyperlink(ws, "Meter Occupancy", "Meter Occupancy summary");
                }
                if (_ReportParams.IncludeSpaceSummary == true)
                {
                    RenderWorksheetHyperlink(ws, "Space Occupancy", "Space Occupancy summary");
                }
                if (_ReportParams.IncludeAreaSummary == true)
                {
                    RenderWorksheetHyperlink(ws, "Area Occupancy", "Area Occupancy summary");
                }
                if (_ReportParams.IncludeDailySummary == true)
                {
                    RenderWorksheetHyperlink(ws, "Daily Occupancy", "Daily Occupancy summary");
                }
                if (_ReportParams.IncludeMonthlySummary == true)
                {
                    RenderWorksheetHyperlink(ws, "Monthly Occupancy", "Monthly Occupancy summary");
                }
                if (_ReportParams.IncludeDetailRecords == true)
                {
                    RenderWorksheetHyperlink(ws, "Details", "Occupancy details");
                }

                rowIdx++;
                rowIdx++;
                colIdx = 1;

                using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader])
                {
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
                }


                // Now start the report data summary header
                RenderOverallReportSummary(ws);

                //  --- END OF OVERALL SUMMARY WORKSHEET ---

                // Should we include a worksheet with Meter aggregates?
                if (_ReportParams.IncludeMeterSummary == true)
                {
                    RenderMeterSummaryWorksheet(pck, "Meter Occupancy");
                }

                // Should we include a worksheet with Space aggregates?
                if (_ReportParams.IncludeSpaceSummary == true)
                {
                    RenderSpaceSummaryWorksheet(pck, "Space Occupancy");
                }

                // Should we include a worksheet with Area aggregates?
                if (_ReportParams.IncludeAreaSummary == true)
                {
                    RenderAreaSummaryWorksheet(pck, "Area Occupancy");
                }

                // Should we include a worksheet with Daily aggregates?
                if (_ReportParams.IncludeDailySummary == true)
                {
                    RenderDailySummaryWorksheet(pck, "Daily Occupancy");
                }

                // Should we include a worksheet with Monthly aggregates?
                if (_ReportParams.IncludeDailySummary == true)
                {
                    RenderMonthlySummaryWorksheet(pck, "Monthly Occupancy");
                }


                // Should we include a Details worksheet?
                if (_ReportParams.IncludeDetailRecords == true)
                {
                    // Create the worksheet
                    ws = pck.Workbook.Worksheets.Add("Details");
                    int detailColumnCount = 8;

                    // Render the header row
                    rowIdx = 1; // Excel uses 1-based indexes
                    ws.SetValue(rowIdx, 1, "Space #");
                    ws.SetValue(rowIdx, 2, "Meter #");
                    ws.SetValue(rowIdx, 3, "Area #");
                    ws.SetValue(rowIdx, 4, "Area");
                    ws.SetValue(rowIdx, 5, "Event Start");
                    ws.SetValue(rowIdx, 6, "Event End");
                    ws.SetValue(rowIdx, 7, "Occupied?");
                    ws.SetValue(rowIdx, 8, "Duration");

                    // Format the header row
                    using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount])
                    {
                        rng.Style.Font.Bold        = true;
                        rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;                 //Set Pattern for the background to Solid
                        rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));  //Set color to dark blue
                        rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                    }

                    // Increment the row index, which will now be the 1st row of our data
                    rowIdx++;

                    #region Populate data for each record

                    foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport)
                    {
                        List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID);
                        foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent nextEvent in spaceRecs)
                        {
                            // Don't detail this item if its a "dummy" event
                            if (nextEvent.IsDummySensorEvent == true)
                            {
                                continue;
                            }

                            AreaAsset areaAsset = _ReportEngine.GetAreaAsset(spaceAsset.AreaID_PreferLibertyBeforeInternal);

                            // Output row values for data
                            ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
                            ws.SetValue(rowIdx, 2, spaceAsset.MeterID);

                            if (areaAsset != null)
                            {
                                ws.SetValue(rowIdx, 3, areaAsset.AreaID);
                                ws.SetValue(rowIdx, 4, areaAsset.AreaName);
                            }

                            ws.SetValue(rowIdx, 5, nextEvent.SensorEvent_Start);
                            ws.SetValue(rowIdx, 6, nextEvent.SensorEvent_End);

                            if (nextEvent.SensorEvent_IsOccupied == true)
                            {
                                ws.SetValue(rowIdx, 7, "Y");
                            }
                            else
                            {
                                ws.SetValue(rowIdx, 7, "N");
                            }

                            ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(nextEvent.SensorEvent_Duration));

                            // Increment the row index, which will now be the next row of our data
                            rowIdx++;

                            // Is there a child "repeat" event also?
                            if (nextEvent.RepeatSensorEvents != null)
                            {
                                foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in nextEvent.RepeatSensorEvents)
                                {
                                    ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
                                    ws.SetValue(rowIdx, 2, spaceAsset.MeterID);

                                    if (areaAsset != null)
                                    {
                                        ws.SetValue(rowIdx, 3, areaAsset.AreaID);
                                        ws.SetValue(rowIdx, 4, areaAsset.AreaName);
                                    }

                                    ws.SetValue(rowIdx, 5, repeatEvent.SensorEvent_Start);
                                    ws.SetValue(rowIdx, 6, repeatEvent.SensorEvent_End);

                                    if (repeatEvent.SensorEvent_IsOccupied == true)
                                    {
                                        ws.SetValue(rowIdx, 7, "Y");
                                    }
                                    else
                                    {
                                        ws.SetValue(rowIdx, 7, "N");
                                    }

                                    ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(repeatEvent.SensorEvent_Duration));

                                    // Increment the row index, which will now be the next row of our data
                                    rowIdx++;
                                }
                            }
                        }
                    }
                    #endregion

                    // We will add autofilters to our headers so user can sort the columns easier
                    using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount])
                    {
                        rng.AutoFilter = true;
                    }

                    // Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value)

                    ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
                    ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
                    ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);

                    ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
                    ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);

                    ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned
                    ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned

                    // And now lets size the columns
                    for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
                    {
                        using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
                        {
                            col.AutoFitColumns();
                        }
                    }
                }


                // All cells in spreadsheet are populated now, so render (save the file) to a memory stream
                byte[] bytes = pck.GetAsByteArray();
                ms.Write(bytes, 0, bytes.Length);
            }

            // Stop diagnostics timer
            sw.Stop();
            System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
        }
Exemplo n.º 41
0
        public string GenerarArchivoExcel_solicitud_cab(List <Solicitud_E> listDetalle, int id_usuario)
        {
            string Res       = "";
            int    _fila     = 4;
            string FileRuta  = "";
            string FileExcel = "";

            try
            {
                FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/Archivos/Excel/" + id_usuario + "_SolicitudResguardo.xlsx");
                string rutaServer = ConfigurationManager.AppSettings["Archivos"];

                FileExcel = rutaServer + "Excel/" + id_usuario + "_SolicitudResguardo.xlsx";

                FileInfo _file = new FileInfo(FileRuta);
                if (_file.Exists)
                {
                    _file.Delete();
                    _file = new FileInfo(FileRuta);
                }

                Thread.Sleep(1);

                using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
                {
                    Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("solicitudResguardo");
                    oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));


                    oWs.Cells[1, 1].Style.Font.Size = 24;   //letra tamaño  2
                    oWs.Cells[1, 1].Value           = "SOLICITUDES DE RESGUARDO";
                    oWs.Cells[1, 1, 1, 7].Merge     = true; // combinar celdaS

                    oWs.Cells[3, 1].Value = "NRO SOLICITUD";
                    oWs.Cells[3, 2].Value = "AREA ";
                    oWs.Cells[3, 3].Value = "SOLICITANTE";
                    oWs.Cells[3, 4].Value = "JEFE CUADRILLA";

                    oWs.Cells[3, 5].Value = "FECHA ASIGNACION";
                    oWs.Cells[3, 6].Value = "CANT. EFECTIVOS";
                    oWs.Cells[3, 7].Value = "ESTADOS";

                    foreach (var item in listDetalle)
                    {
                        oWs.Cells[_fila, 1].Value = item.nroSolicitud.ToString();
                        oWs.Cells[_fila, 2].Value = item.descripcionArea.ToString();
                        oWs.Cells[_fila, 3].Value = item.descripcionSolicitante.ToString();
                        oWs.Cells[_fila, 4].Value = item.descripcionJefeCuadrilla.ToString();

                        oWs.Cells[_fila, 5].Value = item.fechaAtencion.ToString();
                        oWs.Cells[_fila, 6].Value = item.cantidadEfectivos.ToString();
                        oWs.Cells[_fila, 7].Value = item.descripcionEstado.ToString();

                        _fila++;
                    }


                    oWs.Row(1).Style.Font.Bold           = true;
                    oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                    oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                    oWs.Row(3).Style.Font.Bold           = true;
                    oWs.Row(3).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                    oWs.Row(3).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                    for (int k = 1; k <= 7; k++)
                    {
                        oWs.Column(k).AutoFit();
                    }
                    oEx.Save();
                }

                Res = FileExcel;
            }
            catch (Exception)
            {
                throw;
            }
            return(Res);
        }
Exemplo n.º 42
0
        public string GenerarArchivoExcel_data_detallado(DataTable dt_detalles, string nombreFile, string nombreExcel)
        {
            string _ruta         = "";
            string Res           = "";
            int    _fila         = 2;
            string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];

            try
            {
                _ruta = HttpContext.Current.Server.MapPath("~/Temp/" + nombreFile);

                FileInfo _file = new FileInfo(_ruta);
                if (_file.Exists)
                {
                    _file.Delete();
                    _file = new FileInfo(_ruta);
                }

                using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
                {
                    Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add(nombreExcel);
                    oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
                    //for (int i = 1; i <= 8; i++)
                    //{
                    //    oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                    //}

                    oWs.Cells[1, 1].Value = "Año";
                    oWs.Cells[1, 2].Value = "Mes";
                    oWs.Cells[1, 3].Value = "Dia";
                    oWs.Cells[1, 4].Value = "Codigo";
                    oWs.Cells[1, 5].Value = "Operario";
                    oWs.Cells[1, 6].Value = "Total";


                    int ac = 0;
                    foreach (DataRow oBj in dt_detalles.Rows)
                    {
                        ac += 1;
                        //for (int j = 1; j <= 6; j++)
                        //{
                        //    oWs.Cells[_fila, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                        //}
                        oWs.Cells[_fila, 1].Value = oBj["anio"].ToString();
                        oWs.Cells[_fila, 2].Value = oBj["mes"].ToString();
                        oWs.Cells[_fila, 3].Value = oBj["dia"].ToString();

                        oWs.Cells[_fila, 4].Value = oBj["id_Operario_Lectura"].ToString();
                        oWs.Cells[_fila, 5].Value = oBj["operario"].ToString();

                        oWs.Cells[_fila, 6].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        oWs.Cells[_fila, 6].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear texto
                        oWs.Cells[_fila, 6].Style.Numberformat.Format = "#,##0.00";
                        oWs.Cells[_fila, 6].Value = Convert.ToDecimal(oBj["cant_reg"]);

                        _fila++;
                    }

                    oWs.Row(1).Style.Font.Bold           = true;
                    oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                    oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;
                    oWs.Column(1).Style.Font.Bold        = true;

                    for (int k = 1; k <= 6; k++)
                    {
                        oWs.Column(k).AutoFit();
                    }
                    oEx.Save();
                }

                Res = "1|" + ruta_descarga + nombreFile;
            }
            catch (Exception ex)
            {
                Res = "0|" + ex.Message;
            }
            return(Res);
        }
Exemplo n.º 43
0
 public override void Write(OfficeOpenXml.ExcelWorksheet ew, int row)
 {
     ew.SetValue(row, 1, ID);
     ew.SetValue(row, 2, OpenLevel);
     ew.SetValue(row, 3, Consume);
 }
    protected void btnDownloadSummary_Click(object sender, EventArgs e)
    {
        String ConnString = ConfigurationManager.ConnectionStrings["ConcordConnectionString_Report"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(ConnString))
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                using (SqlCommand command = new SqlCommand("DownloadSummaryReport", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@region", ddlRegion.SelectedValue);
                    if (!string.IsNullOrEmpty(ddlMarket.SelectedValue) && ddlMarket.SelectedValue != "-- Select --")
                    {
                        command.Parameters.AddWithValue("@market", ddlMarket.SelectedValue);
                    }
                    adapter.SelectCommand = command;
                    conn.Open();
                    adapter.Fill(dt);
                }
            }
            conn.Close();


            using (MemoryStream xlFileStream = new MemoryStream())
            {
                using (OfficeOpenXml.ExcelPackage xlPkg = new OfficeOpenXml.ExcelPackage())
                {
                    int    xlRowCnt  = 1;
                    int    xlColCnt  = 1;
                    string sheetName = "Download Intake Summary";

                    OfficeOpenXml.ExcelWorksheet xlWrkSht1 = xlPkg.Workbook.Worksheets.Add(sheetName);
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Region";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Market";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Requestor ";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Site_ID ";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Status";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Desktop_Analysis_Request_Date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Los_Upload_Date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Prelim_Design_Date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Requested_Date";

                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Completed_Date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Filed_date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Cleared_Date";

                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "BOM_Sent_to_Market_Date";
                    xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "FCC_601_Filed_Date";

                    xlWrkSht1.Select("A1:N1");
                    xlWrkSht1.SelectedRange.Style.Font.Bold        = true;
                    xlWrkSht1.SelectedRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    xlWrkSht1.SelectedRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#B4C6E7"));

                    if (dt != null)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            xlRowCnt++;
                            xlWrkSht1.Cells[xlRowCnt, 1].Value = Convert.ToString(dt.Rows[i]["Region"]);
                            xlWrkSht1.Cells[xlRowCnt, 2].Value = Convert.ToString(dt.Rows[i]["Market"]);
                            xlWrkSht1.Cells[xlRowCnt, 3].Value = Convert.ToString(dt.Rows[i]["Requester"]);
                            xlWrkSht1.Cells[xlRowCnt, 4].Value = Convert.ToString(dt.Rows[i]["Site_ID"]);
                            xlWrkSht1.Cells[xlRowCnt, 5].Value = Convert.ToString(dt.Rows[i]["Status"]);
                            xlWrkSht1.Cells[xlRowCnt, 6].Value = ((Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != null && Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Desktop_Analysis_Request_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 7].Value = ((Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != null && Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Los_Upload_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 8].Value = ((Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != null && Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Prelim_Design_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 9].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Requested_Date"]).ToString("MM/dd/yyyy") : string.Empty);

                            xlWrkSht1.Cells[xlRowCnt, 10].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Completed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 11].Value = ((Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Filed_date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 12].Value = ((Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Cleared_Date"]).ToString("MM/dd/yyyy") : string.Empty);


                            xlWrkSht1.Cells[xlRowCnt, 13].Value = ((Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != null && Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["BOM_Sent_to_Market_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                            xlWrkSht1.Cells[xlRowCnt, 14].Value = ((Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != null && Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["FCC_601_Filed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
                        }
                    }
                    for (int colCnt = 1; colCnt <= xlColCnt; colCnt++)
                    {
                        xlWrkSht1.Column(colCnt).AutoFit();
                    }
                    xlWrkSht1.Column(xlColCnt).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    xlWrkSht1.Select("A1:N" + xlRowCnt);
                    xlWrkSht1.SelectedRange.Style.Border.Top.Style    = ExcelBorderStyle.Thin;
                    xlWrkSht1.SelectedRange.Style.Border.Left.Style   = ExcelBorderStyle.Thin;
                    xlWrkSht1.SelectedRange.Style.Border.Right.Style  = ExcelBorderStyle.Thin;
                    xlWrkSht1.SelectedRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;



                    xlPkg.SaveAs(xlFileStream);
                    xlWrkSht1.Dispose();
                }


                Response.Clear();
                Response.Buffer      = true;
                Response.Charset     = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                xlFileStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
Exemplo n.º 45
0
 /// <summary>Get a worksheet by name.</summary>
 /// <param name="name">Name of the desired sheet.</param>
 /// <returns>A facade representing the sheet.</returns>
 public override ExcelSheet GetSheet(string name)
 {
     EPPlus.ExcelWorksheet native = this._package.Workbook.Worksheets[name];
     return(ResolveSheet(native));
 }
Exemplo n.º 46
0
 /// <summary>
 /// Default constructor
 /// </summary>
 /// <param name="sheet">The XLS sheet to wrap</param>
 public EPPlusExcelSheet(EPPlus.ExcelWorksheet sheet)
 {
     this._sheet = sheet;
 }
Exemplo n.º 47
0
        public async Task <byte[]> GenerateExportStaffAsync()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("StaffCode");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("MiddleName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("JobTitle");
            dt.Columns.Add("Office/Department");
            dt.Columns.Add("PhoneNumber");
            dt.Columns.Add("Email");
            dt.Columns.Add("DateOfBirth");
            dt.Columns.Add("Address");
            dt.Columns.Add("Country");
            dt.Columns.Add("State");
            dt.Columns.Add("StaffLimit");


            var structures = await(from a in _dataContext.cor_staff
                                   join b in _dataContext.Users on a.StaffId equals b.StaffId
                                   where a.Deleted == false
                                   orderby a.FirstName
                                   select new GODPAPIs.Contracts.Response.Admin.StaffObj
            {
                StaffId         = a.StaffId,
                StaffCode       = a.StaffCode,
                FirstName       = a.FirstName,
                LastName        = a.LastName,
                MiddleName      = a.MiddleName,
                JobTitle        = a.JobTitle,
                JobTitleName    = _dataContext.cor_jobtitles.FirstOrDefault(x => x.JobTitleId == a.JobTitle).Name,
                StaffOfficeName = _dataContext.cor_companystructure.FirstOrDefault(x => x.CompanyStructureId == a.StaffOfficeId).Name,
                PhoneNumber     = a.PhoneNumber,
                Email           = a.Email,
                Address         = a.Address,
                DateOfBirth     = a.DateOfBirth,
                Gender          = a.Gender,
                StateId         = a.StateId,
                StateName       = a.cor_state.StateName,
                CountryId       = a.CountryId,
                CountryName     = a.cor_country.CountryName,
                Active          = a.Active,
                UserName        = b.UserName,
                UserId          = b.Id,
                UserStatus      = b.Active
            }).ToListAsync();


            foreach (var kk in structures)
            {
                var row = dt.NewRow();
                row["StaffCode"]         = kk.StaffCode;
                row["FirstName"]         = kk.FirstName;
                row["LastName"]          = kk.LastName;
                row["MiddleName"]        = kk.MiddleName;
                row["Gender"]            = kk.Gender == "1" ? "Male" : "Female";
                row["JobTitle"]          = kk.JobTitleName;
                row["Office/Department"] = kk.StaffOfficeName;
                row["PhoneNumber"]       = kk.PhoneNumber;
                row["Email"]             = kk.Email;
                row["DateOfBirth"]       = kk.DateOfBirth;
                row["Address"]           = kk.Address;
                row["Country"]           = kk.CountryName;
                row["State"]             = kk.StateName;
                row["StaffLimit"]        = kk.StaffLimit;
                dt.Rows.Add(row);
            }
            Byte[] fileBytes = null;

            if (structures != null)
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
                {
                    OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add("StaffList");
                    ws.DefaultColWidth = 20;
                    ws.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.None);
                    fileBytes = pck.GetAsByteArray();
                }
            }
            return(fileBytes);
        }
Exemplo n.º 48
0
 public override void Parse(OfficeOpenXml.ExcelWorksheet worksheet, int currRow)
 {
     throw new NotImplementedException();
 }
Exemplo n.º 49
0
        public static ExcelRange Cell(this OfficeOpenXml.ExcelWorksheet worksheet, int rowNumber, String headerName)
        {
            int columnNumber = worksheet.ColumnHeaderNumber(headerName, true);

            return(worksheet.Cells[rowNumber, columnNumber]);
        }
Exemplo n.º 50
0
 public static int NextRowNumber(this OfficeOpenXml.ExcelWorksheet worksheet)
 {
     return(worksheet.MaxRowNumber() + 1);
 }
        public async Task <byte[]> Handle(GenerateExportStaffCommad request, CancellationToken cancellationToken)
        {
            try
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("StaffCode");
                dt.Columns.Add("FirstName");
                dt.Columns.Add("LastName");
                dt.Columns.Add("MiddleName");
                dt.Columns.Add("Gender");
                dt.Columns.Add("JobTitle");
                dt.Columns.Add("Office/Department");
                dt.Columns.Add("PhoneNumber");
                dt.Columns.Add("Email");
                dt.Columns.Add("DateOfBirth");
                dt.Columns.Add("Address");
                dt.Columns.Add("Country");
                dt.Columns.Add("State");
                dt.Columns.Add("StaffLimit");
                dt.Columns.Add("Access");
                dt.Columns.Add("Access Levels");
                dt.Columns.Add("Roles");
                dt.Columns.Add("User Name");


                var structures = await(from a in _dataContext.cor_staff
                                       join b in _dataContext.Users on a.StaffId equals b.StaffId
                                       where a.Deleted == false
                                       orderby a.FirstName
                                       select new GODPAPIs.Contracts.Response.Admin.StaffObj
                {
                    StaffId       = a.StaffId,
                    StaffCode     = a.StaffCode,
                    FirstName     = a.FirstName,
                    LastName      = a.LastName,
                    MiddleName    = a.MiddleName,
                    JobTitle      = a.JobTitle,
                    PhoneNumber   = a.PhoneNumber,
                    Email         = a.Email,
                    Address       = a.Address,
                    DateOfBirth   = a.DateOfBirth,
                    Gender        = a.Gender,
                    StateId       = a.StateId,
                    StateName     = a.cor_state.StateName,
                    CountryId     = a.CountryId,
                    Active        = a.Active,
                    UserName      = b.UserName,
                    UserId        = b.Id,
                    UserStatus    = b.Active,
                    AccessLevel   = a.AccessLevel,
                    StaffLimit    = a.StaffLimit,
                    AccessLevelId = a.AccessLevel ?? 0,
                    StaffOfficeId = a.StaffOfficeId,
                }).ToListAsync();

                foreach (var item  in structures)
                {
                    item.CountryName     = _dataContext.cor_country.FirstOrDefault(s => s.CountryId == item.CountryId)?.CountryName ?? string.Empty;
                    item.StateName       = _dataContext.cor_state.FirstOrDefault(s => s.StateId == item.StateId)?.StateName ?? string.Empty;
                    item.JobTitleName    = _dataContext.cor_jobtitles.FirstOrDefault(s => s.JobTitleId == item.JobTitle)?.Name;
                    item.StaffOfficeName = _dataContext.cor_companystructure.FirstOrDefault(a => a.CompanyStructureId == item.StaffOfficeId)?.Name;
                    var user = await _userManager.FindByEmailAsync(item.Email);

                    if (user != null)
                    {
                        var userlevels = _dataContext.cor_useraccess.Where(s => s.UserId == user.Id).Select(w => w.AccessLevelId).ToList();
                        item.UserAccessLevelsNames = string.Join(',', _dataContext.cor_companystructure.Where(w => userlevels.Contains(w.CompanyStructureId)).Select(w => w.Name).ToList());
                        item.ExcelUserRoleNames    = string.Join(",", await _userManager.GetRolesAsync(user));
                        item.AccessNames           = _dataContext.cor_companystructuredefinition.FirstOrDefault(s => s.StructureDefinitionId == item.AccessLevel)?.Definition;
                    }
                }

                foreach (var kk in structures)
                {
                    var row = dt.NewRow();
                    row["StaffCode"]         = kk.StaffCode;
                    row["FirstName"]         = kk.FirstName;
                    row["LastName"]          = kk.LastName;
                    row["MiddleName"]        = kk.MiddleName;
                    row["Gender"]            = kk.Gender == "1" ? "Male" : "Female";
                    row["JobTitle"]          = kk.JobTitleName;
                    row["Office/Department"] = kk.StaffOfficeName;
                    row["PhoneNumber"]       = kk.PhoneNumber;
                    row["Email"]             = kk.Email;
                    row["DateOfBirth"]       = kk.DateOfBirth;
                    row["Address"]           = kk.Address;
                    row["Country"]           = kk.CountryName;
                    row["State"]             = kk.StateName;
                    row["StaffLimit"]        = kk.StaffLimit;
                    row["Access"]            = kk.AccessNames;
                    row["Access Levels"]     = kk.UserAccessLevelsNames;
                    row["Roles"]             = kk.ExcelUserRoleNames;
                    row["User Name"]         = kk.UserName;
                    dt.Rows.Add(row);
                }
                Byte[] fileBytes = null;

                if (structures != null)
                {
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    using (OfficeOpenXml.ExcelPackage pck = new ExcelPackage())
                    {
                        OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add("StaffList");
                        ws.DefaultColWidth = 20;
                        ws.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.None);
                        fileBytes = pck.GetAsByteArray();
                    }
                }
                return(fileBytes);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemplo n.º 52
0
        public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
        {
            timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;

            // Start diagnostics timer
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);

            // Now gather and analyze data for the report
            SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
            requiredDataElements.NeedsSensorData            = true;
            requiredDataElements.NeedsPaymentData           = false;
            requiredDataElements.NeedsOverstayData          = true;
            requiredDataElements.NeedsEnforcementActionData = true;

            this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, this._ReportParams);
            this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);

            OfficeOpenXml.ExcelWorksheet ws = null;

            using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
            {
                // Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
                ws = pck.Workbook.Worksheets.Add("Details");

                // Render the standard report title lines
                rowIdx = 1; // Excel uses 1-based indexes
                colIdx = 1;
                RenderCommonReportTitle(ws, this._ReportName);

                // Render common report header for enforcement activity restriction filter, but only if its not for all activity
                if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
                {
                    rowIdx++;
                    colIdx = 1;
                    RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
                }

                // Render common report header for regulated hour restriction filter
                rowIdx++;
                colIdx = 1;
                RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);

                using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
                {
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;                 //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
                }

                rowIdx++;
                rowIdx++;
                colIdx = 1;

                int detailsStartRow   = rowIdx;
                int detailColumnCount = 29;

                // Render the header row
                ws.SetValue(rowIdx, 1, "Space #");
                ws.SetValue(rowIdx, 2, "Meter #");
                ws.SetValue(rowIdx, 3, "Area #");
                ws.SetValue(rowIdx, 4, "Area");
                ws.SetValue(rowIdx, 5, "Event Timestamp");
                ws.SetValue(rowIdx, 6, "Record Timestamp");
                ws.SetValue(rowIdx, 7, "Latency");
                ws.SetValue(rowIdx, 8, "Occupied");
                ws.SetValue(rowIdx, 9, "Vacant Duration");
                ws.SetValue(rowIdx, 10, "Time Arrived");
                ws.SetValue(rowIdx, 11, "Time Departed");
                ws.SetValue(rowIdx, 12, "Parked Duration");
                ws.SetValue(rowIdx, 13, "Max Stay Regulation");
                ws.SetValue(rowIdx, 14, "Overstay Violation");
                ws.SetValue(rowIdx, 15, "Overstay Duration");
                ws.SetValue(rowIdx, 16, "Overstay (0-15min)");
                ws.SetValue(rowIdx, 17, "Overstay (15-30min)");
                ws.SetValue(rowIdx, 18, "Overstay (30-60min)");
                ws.SetValue(rowIdx, 19, "Overstay (>60min)");
                ws.SetValue(rowIdx, 20, "Violation Actioned");
                ws.SetValue(rowIdx, 21, "Violation Issued");
                ws.SetValue(rowIdx, 22, "Violation Warning");
                ws.SetValue(rowIdx, 23, "Violation Not Issued");
                ws.SetValue(rowIdx, 24, "Violation Fault");
                ws.SetValue(rowIdx, 25, "Violation Missed");
                ws.SetValue(rowIdx, 26, "Capture Rate (0-15min)");
                ws.SetValue(rowIdx, 27, "Capture Rate (15-30min)");
                ws.SetValue(rowIdx, 28, "Capture Rate (30-60min)");
                ws.SetValue(rowIdx, 29, "Capture Rate (>60min)");

                // Format the header row
                using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 1, detailsStartRow, 4])
                {
                    rng.Style.Font.Bold        = true;
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;                 //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                }
                using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 5, detailsStartRow, 8])
                {
                    rng.Style.Font.Bold        = true;
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(36, 64, 98));
                    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                }
                using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 9, detailsStartRow, 13])
                {
                    rng.Style.Font.Bold        = true;
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 176, 80));
                    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                }
                using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 14, detailsStartRow, 29])
                {
                    rng.Style.Font.Bold        = true;
                    rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(150, 54, 52));
                    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                }

                // Increment the row index, which will now be the 1st row of our data
                rowIdx++;
                detailsStartRow = rowIdx;

                timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
                GroupStats baseTotalStats = this._ReportEngine.GetOverallStats(timeIsolation);
                ParkingAndOverstayGroupStats totalStats = new ParkingAndOverstayGroupStats(baseTotalStats);

                #region Populate data for each record
                foreach (AreaAsset areaAsset in this._ReportEngine.ReportDataModel.AreasIncludedInReport)
                {
                    GroupStats baseAreaStats = this._ReportEngine.GetAreaStats(areaAsset.AreaID, timeIsolation);
                    ParkingAndOverstayGroupStats areaStats = new ParkingAndOverstayGroupStats(baseAreaStats);

                    foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport)
                    {
                        // Skip record if its not applicable to the current area we are processing
                        if (spaceAsset.AreaID_PreferLibertyBeforeInternal != areaAsset.AreaID)
                        {
                            continue;
                        }

                        List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID);

                        TimeSpan previousVacantDuration = new TimeSpan(0);
                        SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent previousOccupiedEvent = null;
                        foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvents in spaceRecs)
                        {
                            ws.SetValue(rowIdx, 1, repEvents.BayInfo.SpaceID);
                            ws.SetValue(rowIdx, 2, repEvents.BayInfo.MeterID);
                            if (areaAsset != null)
                            {
                                ws.SetValue(rowIdx, 3, areaAsset.AreaID);
                                ws.SetValue(rowIdx, 4, areaAsset.AreaName);
                            }

                            ws.SetValue(rowIdx, 5, repEvents.SensorEvent_Start);
                            ws.SetValue(rowIdx, 6, repEvents.SensorEvent_RecCreationDateTime);
                            ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(repEvents.SensorEvent_Latency));
                            if (repEvents.SensorEvent_IsOccupied == true)
                            {
                                previousOccupiedEvent = repEvents;

                                ws.SetValue(rowIdx, 8, 1); // 1 for numeric version of "True"

                                if (previousVacantDuration.Ticks > 0)
                                {
                                    ws.SetValue(rowIdx, 9, FormatTimeSpanAsHoursMinutesAndSeconds(previousVacantDuration));
                                }

                                ws.SetValue(rowIdx, 10, repEvents.SensorEvent_Start);
                            }
                            else
                            {
                                ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False"
                                ws.SetValue(rowIdx, 11, repEvents.SensorEvent_Start);

                                if (previousOccupiedEvent != null)
                                {
                                    bool firstOverstay = true;

                                    if (previousOccupiedEvent.Overstays.Count == 0)
                                    {
                                        ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start);
                                        ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration));

                                        RegulatedHoursDetail ruleForEvent = GetRegulationRuleAtEventTime(repEvents);
                                        if (ruleForEvent != null)
                                        {
                                            StringBuilder sb = new StringBuilder();
                                            sb.Append(ruleForEvent.MaxStayMinutes.ToString());
                                            ws.SetValue(rowIdx, 13, sb.ToString());
                                        }

                                        ws.SetValue(rowIdx, 14, "N");
                                    }

                                    foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in previousOccupiedEvent.Overstays)
                                    {
                                        if (firstOverstay == false)
                                        {
                                            // Need to start new row and repeat the header info!
                                            rowIdx++;

                                            ws.SetValue(rowIdx, 1, previousOccupiedEvent.BayInfo.SpaceID);
                                            ws.SetValue(rowIdx, 2, previousOccupiedEvent.BayInfo.MeterID);
                                            if (areaAsset != null)
                                            {
                                                ws.SetValue(rowIdx, 3, areaAsset.AreaID);
                                                ws.SetValue(rowIdx, 4, areaAsset.AreaName);
                                            }

                                            ws.SetValue(rowIdx, 5, previousOccupiedEvent.SensorEvent_Start);
                                            ws.SetValue(rowIdx, 6, previousOccupiedEvent.SensorEvent_RecCreationDateTime);
                                            ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Latency));
                                            ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False"
                                            ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start);
                                        }

                                        ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration));
                                        if (overstay.OverstayBasedOnRuleDetail != null)
                                        {
                                            StringBuilder sb = new StringBuilder();
                                            sb.Append(overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString());
                                            ws.SetValue(rowIdx, 13, sb.ToString());
                                        }

                                        ws.SetValue(rowIdx, 14, "Y");
                                        ws.SetValue(rowIdx, 15, FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits));

                                        if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 15)
                                        {
                                            ws.SetValue(rowIdx, 16, "Y");
                                            areaStats.TotalOverstaysDuration0To15Mins++;
                                            totalStats.TotalOverstaysDuration0To15Mins++;
                                        }
                                        else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 30)
                                        {
                                            ws.SetValue(rowIdx, 17, "Y");
                                            areaStats.TotalOverstaysDuration15To30Mins++;
                                            totalStats.TotalOverstaysDuration15To30Mins++;
                                        }
                                        else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 60)
                                        {
                                            ws.SetValue(rowIdx, 18, "Y");
                                            areaStats.TotalOverstaysDuration30To60Mins++;
                                            totalStats.TotalOverstaysDuration30To60Mins++;
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 19, "Y");
                                            areaStats.TotalOverstaysDurationOver60Mins++;
                                            totalStats.TotalOverstaysDurationOver60Mins++;
                                        }


                                        if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken))
                                        {
                                            ws.SetValue(rowIdx, 20, "Y");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 20, "N");
                                        }


                                        if (string.Compare(overstay.EnforcementActionTaken, "Enforced", true) == 0)
                                        {
                                            ws.SetValue(rowIdx, 21, "Y");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 21, "N");
                                        }


                                        if (string.Compare(overstay.EnforcementActionTaken, "Cautioned", true) == 0)
                                        {
                                            ws.SetValue(rowIdx, 22, "Y");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 22, "N");
                                        }


                                        if (string.Compare(overstay.EnforcementActionTaken, "NotEnforced", true) == 0)
                                        {
                                            ws.SetValue(rowIdx, 23, "Y");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 23, "N");
                                        }


                                        if (string.Compare(overstay.EnforcementActionTaken, "Fault", true) == 0)
                                        {
                                            ws.SetValue(rowIdx, 24, "Y");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 24, "N");
                                        }


                                        if (string.IsNullOrEmpty(overstay.EnforcementActionTaken))
                                        {
                                            ws.SetValue(rowIdx, 25, "Y");

                                            ws.SetValue(rowIdx, 26, "N");
                                            ws.SetValue(rowIdx, 27, "N");
                                            ws.SetValue(rowIdx, 28, "N");
                                            ws.SetValue(rowIdx, 29, "N");
                                        }
                                        else
                                        {
                                            ws.SetValue(rowIdx, 25, "N");

                                            ws.SetValue(rowIdx, 26, "N");
                                            ws.SetValue(rowIdx, 27, "N");
                                            ws.SetValue(rowIdx, 28, "N");
                                            ws.SetValue(rowIdx, 29, "N");

                                            TimeSpan captureRate = (overstay.EnforcementActionTakenTimeStamp - overstay.StartOfOverstayViolation);
                                            if (captureRate.TotalMinutes < 15)
                                            {
                                                ws.SetValue(rowIdx, 26, "Y");
                                                areaStats.TotalOverstaysActioned0To15Mins++;
                                                totalStats.TotalOverstaysActioned0To15Mins++;
                                            }
                                            else if (captureRate.TotalMinutes < 30)
                                            {
                                                ws.SetValue(rowIdx, 27, "Y");
                                                areaStats.TotalOverstaysActioned15To30Mins++;
                                                totalStats.TotalOverstaysActioned15To30Mins++;
                                            }
                                            else if (captureRate.TotalMinutes < 60)
                                            {
                                                ws.SetValue(rowIdx, 28, "Y");
                                                areaStats.TotalOverstaysActioned30To60Mins++;
                                                totalStats.TotalOverstaysActioned30To60Mins++;
                                            }
                                            else
                                            {
                                                ws.SetValue(rowIdx, 29, "Y");
                                                areaStats.TotalOverstaysActionedOver60Mins++;
                                                totalStats.TotalOverstaysActionedOver60Mins++;
                                            }
                                        }

                                        // Set flag so we know we're no longer dealing with the first overstay of this occupied event
                                        firstOverstay = false;
                                    }
                                }
                            }

                            if (repEvents.SensorEvent_IsOccupied == false)
                            {
                                previousVacantDuration = new TimeSpan(repEvents.SensorEvent_Duration.Ticks);
                            }

                            // Increment the row index, which will now be the next row of our data
                            rowIdx++;
                        }
                    }

                    // Finish the area aggregations
                    areaStats.AggregateSelf();

                    colIdx = 1;
                    ws.SetValue(rowIdx, colIdx, "SUBTOTAL AREA");
                    MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4);
                    using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29])
                    {
                        rng.Style.Font.Bold = true;
                    }

                    ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(areaStats.AverageLatency));
                    ws.SetValue(rowIdx, 8, areaStats.ingress);
                    ws.SetValue(rowIdx, 9, areaStats.PercentVacantDuration.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 12, areaStats.PercentageOccupancy.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 14, areaStats.PercentOverstayedCount.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 15, areaStats.PercentageOverstayedDuration.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 16, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 17, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 18, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 19, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 20, areaStats.PercentOverstaysActioned.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 21, areaStats.PercentOverstaysIssued.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 22, areaStats.PercentOverstaysCautioned.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 23, areaStats.PercentOverstaysNotIssued.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 24, areaStats.PercentOverstaysFault.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 25, areaStats.PercentOverstaysMissed.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 26, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 27, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 28, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
                    ws.SetValue(rowIdx, 29, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");

                    rowIdx++;
                    rowIdx++;
                }

                // Finish the total aggregations
                totalStats.AggregateSelf();

                colIdx = 1;
                ws.SetValue(rowIdx, colIdx, "TOTAL");
                MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4);
                using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29])
                {
                    rng.Style.Font.Bold = true;
                }

                ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(totalStats.AverageLatency));
                ws.SetValue(rowIdx, 8, totalStats.ingress);
                ws.SetValue(rowIdx, 9, totalStats.PercentVacantDuration.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 12, totalStats.PercentageOccupancy.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 14, totalStats.PercentOverstayedCount.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 15, totalStats.PercentageOverstayedDuration.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 16, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 17, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 18, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 19, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 20, totalStats.PercentOverstaysActioned.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 21, totalStats.PercentOverstaysIssued.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 22, totalStats.PercentOverstaysCautioned.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 23, totalStats.PercentOverstaysNotIssued.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 24, totalStats.PercentOverstaysFault.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 25, totalStats.PercentOverstaysMissed.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 26, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 27, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 28, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
                ws.SetValue(rowIdx, 29, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");

                rowIdx++;
                rowIdx++;

                #endregion

                // AutoFilters aren't suitable for this report

                /*
                 * // We will add autofilters to our headers so user can sort the columns easier
                 * using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow - 1, 1, rowIdx, detailColumnCount])
                 * {
                 *  rng.AutoFilter = true;
                 * }
                 */

                ApplyNumberStyleToColumn(ws, 1, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
                ApplyNumberStyleToColumn(ws, 2, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
                ApplyNumberStyleToColumn(ws, 3, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
                ApplyNumberStyleToColumn(ws, 4, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Left);
                ApplyNumberStyleToColumn(ws, 5, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 6, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 7, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 8, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 9, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 10, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 11, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 12, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 13, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 14, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 15, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 16, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 17, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 18, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 19, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 20, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 21, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 22, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 23, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 24, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 25, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 26, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 27, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 28, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
                ApplyNumberStyleToColumn(ws, 29, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);

                // And now lets size the columns
                for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
                {
                    using (OfficeOpenXml.ExcelRange col = ws.Cells[detailsStartRow - 1, autoSizeColIdx, rowIdx, autoSizeColIdx])
                    {
                        col.AutoFitColumns();
                    }
                }

                // And finally we will freeze the header rows for nicer scrolling
                ws.View.FreezePanes(7, 1);

                // All cells in spreadsheet are populated now, so render (save the file) to a memory stream
                byte[] bytes = pck.GetAsByteArray();
                ms.Write(bytes, 0, bytes.Length);
            }

            // Stop diagnostics timer
            sw.Stop();
            System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
        }
        public string DescargaExcel(string fechaAsignacion, int TipoServicio)
        {
            int    _fila = 2;
            string _ruta;
            string nombreArchivo = "";
            string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
            var    usuario       = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;

            try
            {
                List <Cls_Entidad_Export_trabajos_lectura> _lista = new List <Cls_Entidad_Export_trabajos_lectura>();

                Cls_Negocio_Export_trabajos_lectura obj_negocio = new DSIGE.Negocio.Cls_Negocio_Export_trabajos_lectura();
                _lista = obj_negocio.Capa_Negocio_Get_ListaLecturas_Excel(fechaAsignacion, TipoServicio);

                if (_lista.Count == 0)
                {
                    return(_Serialize("0|No hay informacion para mostrar.", true));
                }

                if (TipoServicio == 1)
                {
                    nombreArchivo = "LECTURAS_EXPORTADO_" + usuario + ".xls";
                }
                else if (TipoServicio == 2)
                {
                    nombreArchivo = "RELECTURAS_EXPORTADO_" + usuario + ".xls";
                }
                else if (TipoServicio == 9)
                {
                    nombreArchivo = "RECLAMOS_EXPORTADO_" + usuario + ".xls";
                }

                _ruta = Path.Combine(Server.MapPath("~/Temp") + "\\" + nombreArchivo);

                FileInfo _file = new FileInfo(_ruta);
                if (_file.Exists)
                {
                    _file.Delete();
                    _file = new FileInfo(_ruta);
                }

                using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
                {
                    Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Importar");
                    oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
                    for (int i = 1; i <= 21; i++)
                    {
                        oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                        oWs.Cells[1, i].Style.Font.Size = 9;    //letra tamaño
                        oWs.Cells[1, i].Style.Font.Bold = true; //Letra negrita
                    }

                    oWs.Cells[1, 1].Value = "ITEM";
                    oWs.Cells[1, 2].Value = "NOBORAR";
                    oWs.Cells[1, 3].Value = "INSTALACIÓN";
                    oWs.Cells[1, 4].Value = "APARATO";

                    oWs.Cells[1, 5].Value = "TIPO CALLE";
                    oWs.Cells[1, 6].Value = "NOMBRE DE CALLE";
                    oWs.Cells[1, 7].Value = "ALTURA DE CALLE";
                    oWs.Cells[1, 8].Value = "NÚMERO DE EDIFICIO";
                    oWs.Cells[1, 9].Value = "NÚMERO DE DEPARTAMENTO";

                    oWs.Cells[1, 10].Value = "DETALLE CONSTRUCCIÓN (OBJETO DE CONEXIÓN)";
                    oWs.Cells[1, 11].Value = "CONJUNTO DE VIVIENDA (OBJETO DE CONEXIÓN)";
                    oWs.Cells[1, 12].Value = "MANZANA/LOTE";
                    oWs.Cells[1, 13].Value = "DISTRITO";

                    oWs.Cells[1, 14].Value = "CUENTA CONTRATO";
                    oWs.Cells[1, 15].Value = "SECUENCIA DE LECTURA";
                    oWs.Cells[1, 16].Value = "UNIDAD DE LECTURA";
                    oWs.Cells[1, 17].Value = "NÚMERO DE LECTURAS ESTIMADAS CONSECUTIVAS";

                    oWs.Cells[1, 18].Value = "EMPRESA LECTORA";
                    oWs.Cells[1, 19].Value = "NOTA 2 DE LA UBICACIÓN DEL APARATO";
                    oWs.Cells[1, 20].Value = "TECNICO";
                    oWs.Cells[1, 21].Value = "SECUENCIA";

                    int acu = 0;
                    foreach (Cls_Entidad_Export_trabajos_lectura oBj in _lista)
                    {
                        acu = acu + 1;

                        for (int i = 1; i <= 21; i++)
                        {
                            oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                        }

                        oWs.Cells[_fila, 1].Value = acu;
                        oWs.Cells[_fila, 2].Value = oBj.id_Lectura;
                        oWs.Cells[_fila, 3].Value = oBj.Instalacion;

                        //oWs.Cells[_fila, 4].Style.Numberformat.Format = "#,##0";
                        //oWs.Cells[_fila, 4].Value = Convert.ToDouble(oBj.Aparato);
                        oWs.Cells[_fila, 4].Value = oBj.Aparato;

                        oWs.Cells[_fila, 5].Value = oBj.Tipo_calle;
                        oWs.Cells[_fila, 6].Value = oBj.Nombre_Calle;
                        oWs.Cells[_fila, 7].Value = oBj.Altura_Calle;
                        oWs.Cells[_fila, 8].Value = oBj.Numero_Edificio;
                        oWs.Cells[_fila, 9].Value = oBj.Numero_Departamento;

                        oWs.Cells[_fila, 10].Value = oBj.Detalle_Construccion;
                        oWs.Cells[_fila, 11].Value = oBj.Conjunto_Vivienda;
                        oWs.Cells[_fila, 12].Value = oBj.Manzana_Lote;
                        oWs.Cells[_fila, 13].Value = oBj.Distrito;

                        oWs.Cells[_fila, 14].Value = oBj.Cuenta_contrato;
                        oWs.Cells[_fila, 15].Value = oBj.Secuencia_lectura;
                        oWs.Cells[_fila, 16].Value = oBj.Unidad_lectura;
                        oWs.Cells[_fila, 17].Value = oBj.Numero_lecturas_estimadas_consecutivas;
                        oWs.Cells[_fila, 18].Value = oBj.Empresa_Lectora;

                        oWs.Cells[_fila, 19].Value = oBj.Nota_2_ubicacion_aparato;
                        oWs.Cells[_fila, 20].Value = oBj.Tecnico;
                        oWs.Cells[_fila, 21].Value = oBj.Secuencia;

                        _fila++;
                    }

                    oWs.Row(1).Style.Font.Bold           = true;
                    oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                    oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                    oWs.Column(1).Style.Font.Bold = true;

                    for (int i = 1; i <= 21; i++)
                    {
                        oWs.Column(i).AutoFit();
                    }

                    oEx.Save();
                }


                return(_Serialize("1|" + ruta_descarga + nombreArchivo, true));
            }
            catch (Exception ex)
            {
                return(_Serialize("0|" + ex.Message, true));
            }
        }
        public ActionResult DescargaHistorico(string __a)
        {
            Int32  _fila = 2;
            String _servidor;
            String _ruta;

            if (__a.Length == 0)
            {
                return(View());
            }

            List <AsignaLecturaReLectura> _lista = MvcApplication._Deserialize <List <AsignaLecturaReLectura> >(__a);

            _servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
            _ruta     = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);

            FileInfo _file = new FileInfo(_ruta);

            if (_file.Exists)
            {
                _file.Delete();
                _file = new FileInfo(_ruta);
            }

            using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
            {
                Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Historico de Lecturas");

                oWs.Cells[1, 1].Value = "MEDIDOR";
                oWs.Cells[1, 2].Value = "FECHA DE LECTURA";
                oWs.Cells[1, 3].Value = "LECTURA";
                oWs.Cells[1, 4].Value = "OPERARIO";
                oWs.Cells[1, 5].Value = "OBSERVACION";
                oWs.Cells[1, 6].Value = "ESTADO";

                foreach (AsignaLecturaReLectura oBj in _lista)
                {
                    oWs.Cells[_fila, 1].Value = oBj.medidorLectura;
                    oWs.Cells[_fila, 2].Value = oBj.fecLectura;
                    oWs.Cells[_fila, 3].Value = (oBj.lectura);
                    oWs.Cells[_fila, 4].Value = (oBj.ope_nombre);
                    oWs.Cells[_fila, 5].Value = (oBj.obsLectura);
                    oWs.Cells[_fila, 6].Value = (oBj.Estado);

                    _fila++;
                }

                oWs.Row(1).Style.WrapText            = true;
                oWs.Row(1).Style.Font.Bold           = true;
                oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                oWs.Column(1).Style.Font.Bold = true;
                oWs.Column(1).AutoFit();
                oWs.Column(2).AutoFit();
                oWs.Column(3).AutoFit();
                oWs.Column(4).AutoFit();
                oWs.Column(5).AutoFit();
                oWs.Column(6).AutoFit();

                oEx.Save();
            }

            return(new ContentResult
            {
                Content = "{ \"__a\": \"" + _servidor + "\" }",
                ContentType = "application/json"
            });
        }
Exemplo n.º 55
0
 /// <summary>Get the sheet at the specific index.</summary>
 /// <param name="index">0-based index of the sheet.</param>
 /// <returns>A facade representing the sheet.</returns>
 public override ExcelSheet GetSheet(int index)
 {
     EPPlus.ExcelWorksheet native = this._package.Workbook.Worksheets[index + 1];
     return(ResolveSheet(native));
 }
Exemplo n.º 56
0
 public virtual void Write(OfficeOpenXml.ExcelWorksheet ew, int row)
 {
 }
Exemplo n.º 57
0
        public void ImportFormName(ExcelWorksheet sheet, IOrganizationService service)
        {
            var forms = new List <Tuple <int, Entity> >();

            //int lastColumnIndex = sheet.Rows[0].Cells.LastColumnIndex;

#if NO_GEMBOX
            var rowsCount = sheet.Dimension.Rows;
            for (var rowI = 1; rowI < rowsCount; rowI++)
            {
                var currentFormId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString());
                var columnIndex   = 4;

                while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
                {
                    var currentLcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
                    var formRecord  = forms.FirstOrDefault(t => t.Item1 == currentLcid && t.Item2.Id == currentFormId);
                    if (formRecord == null)
                    {
                        formRecord = new Tuple <int, Entity>(currentLcid, new Entity("systemform")
                        {
                            Id = currentFormId
                        });
                        forms.Add(formRecord);
                    }

                    if (ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Name")
                    {
                        formRecord.Item2["name"] = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
                    }
                    else if (ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Description")
                    {
                        formRecord.Item2["description"] = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
                    }

                    columnIndex++;
                }
            }
#else
            foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
            {
                var currentFormId = new Guid(row.Cells[1].Value.ToString());
                var columnIndex   = 4;
                //while (columnIndex <= lastColumnIndex)
                while (row.Cells[columnIndex].Value != null)
                {
                    var currentLcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
                    var formRecord  = forms.FirstOrDefault(t => t.Item1 == currentLcid && t.Item2.Id == currentFormId);
                    if (formRecord == null)
                    {
                        formRecord = new Tuple <int, Entity>(currentLcid, new Entity("systemform")
                        {
                            Id = currentFormId
                        });
                        forms.Add(formRecord);
                    }

                    if (row.Cells[3].Value.ToString() == "Name")
                    {
                        formRecord.Item2["name"] = row.Cells[columnIndex].Value.ToString();
                    }
                    else if (row.Cells[3].Value.ToString() == "Description")
                    {
                        formRecord.Item2["description"] = row.Cells[columnIndex].Value.ToString();
                    }

                    columnIndex++;
                }
            }
#endif
            // Retrieve current user language information
            var qe = new QueryExpression("usersettings")
            {
                ColumnSet = new ColumnSet("uilanguageid", "localeid"),
                Criteria  = new FilterExpression()
            };
            qe.Criteria.AddCondition("systemuserid", ConditionOperator.EqualUserId);
            var settings        = service.RetrieveMultiple(qe);
            var userSettingLcid = settings[0].GetAttributeValue <int>("uilanguageid");
            var currentSetting  = userSettingLcid;

            var languages = forms.Select(f => f.Item1).Distinct().ToList();
            foreach (var lcid in languages)
            {
                // Define correct user language for update
                if (userSettingLcid != lcid)
                {
                    settings[0]["localeid"]     = lcid;
                    settings[0]["uilanguageid"] = lcid;
                    service.Update(settings[0]);
                    currentSetting = lcid;
                }

                foreach (var form in forms.Where(f => f.Item1 == lcid))
                {
                    service.Update(form.Item2);
                }
            }

            // Reinit user language
            if (userSettingLcid != currentSetting)
            {
                settings[0]["localeid"]     = userSettingLcid;
                settings[0]["uilanguageid"] = userSettingLcid;
                service.Update(settings[0]);
            }
        }
        public ActionResult DescargaExcel(string __a)
        {
            Int32  _fila = 2;
            String _servidor;
            String _ruta;

            if (__a.Length == 0)
            {
                return(View());
            }

            List <LecturaHistorico> _lista = MvcApplication._Deserialize <List <LecturaHistorico> >(__a);

            _servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
            //_ruta = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);
            _ruta = Path.Combine(Server.MapPath("/Temp"), _servidor);

            FileInfo _file = new FileInfo(_ruta);

            if (_file.Exists)
            {
                _file.Delete();
                _file = new FileInfo(_ruta);
            }

            using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
            {
                Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Resumen de Lecturas");

                oWs.Cells[1, 1].Value = "OPERADOR";
                oWs.Cells[1, 2].Value = "TOTAL";
                oWs.Cells[1, 3].Value = "REALIZADOS";
                oWs.Cells[1, 4].Value = "CON FOTO";
                oWs.Cells[1, 5].Value = "PENDIENTES";
                oWs.Cells[1, 6].Value = "% AVANCE";
                oWs.Cells[1, 7].Value = "HORA INICIO DE TRABAJO";
                oWs.Cells[1, 8].Value = "HORA TERMINO DE TRABAJO";
                oWs.Cells[1, 9].Value = "HORAS TRABAJADAS";

                foreach (LecturaHistorico oBj in _lista)
                {
                    oWs.Cells[_fila, 1].Value = oBj.des_ope;
                    oWs.Cells[_fila, 2].Value = oBj.total;
                    oWs.Cells[_fila, 3].Value = (oBj.realizado);
                    oWs.Cells[_fila, 4].Value = (oBj.conFoto);
                    oWs.Cells[_fila, 5].Value = (oBj.pendiente);
                    oWs.Cells[_fila, 6].Value = (oBj.avance);
                    oWs.Cells[_fila, 7].Value = (oBj.f_ini);
                    oWs.Cells[_fila, 8].Value = (oBj.f_fin);
                    oWs.Cells[_fila, 9].Value = (oBj.horas);

                    _fila++;
                }

                oWs.Row(1).Style.WrapText            = true;
                oWs.Row(1).Style.Font.Bold           = true;
                oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                oWs.Column(1).Style.Font.Bold = true;
                oWs.Column(1).AutoFit();
                oWs.Column(2).AutoFit();
                oWs.Column(3).AutoFit();
                oWs.Column(4).AutoFit();
                oWs.Column(5).AutoFit();
                oWs.Column(6).AutoFit();
                oWs.Column(7).AutoFit();
                oWs.Column(8).AutoFit();
                oWs.Column(9).AutoFit();

                oEx.Save();
            }

            return(new ContentResult
            {
                Content = "{ \"__a\": \"" + _servidor + "\" }",
                ContentType = "application/json"
            });
        }
        public string Reporte_DetalleLecturas(int id_local, int id_servicio, string fechaini, string fechaFin, int ipOperario, int categoriaLectura)
        {
            string Res = "";
            string _servidor;
            string _ruta;
            string resultado = "";
            int    _fila     = 2;
            string FileRuta  = "";
            string FileExcel = "";


            List <ResumenLecturas> loDatos = new List <ResumenLecturas>();

            try
            {
                NLectura obj_negocio = new NLectura();
                loDatos = obj_negocio.NListaLecturaDetalladoEstado(id_local, id_servicio, fechaini, fechaFin, ipOperario, categoriaLectura);

                if (loDatos.Count == 0)
                {
                    return(_Serialize("0|No hay informacion para mostrar.", true));
                }

                _servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
                FileRuta  = System.Web.Hosting.HostingEnvironment.MapPath("~/Upload/Detalle_Resumen_Lecturas_" + _servidor);
                string rutaServer = ConfigurationManager.AppSettings["servidor-archivos"];


                FileExcel = rutaServer + "Detalle_Resumen_Lecturas_" + _servidor;
                FileInfo _file = new FileInfo(FileRuta);
                if (_file.Exists)
                {
                    _file.Delete();
                    _file = new FileInfo(FileRuta);
                }

                using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
                {
                    Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("ControlProcesos");
                    oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));

                    oWs.Cells[1, 1].Value = "Lecturista";
                    oWs.Cells[1, 2].Value = "Direccion";
                    oWs.Cells[1, 3].Value = "Suministro";
                    oWs.Cells[1, 4].Value = "Medidor";
                    oWs.Cells[1, 5].Value = "Zona";
                    oWs.Cells[1, 6].Value = "Lectura";
                    oWs.Cells[1, 7].Value = "Confirmacion Lectura";
                    oWs.Cells[1, 8].Value = "Observacion";
                    oWs.Cells[1, 9].Value = "Estado";


                    //marco detalle CABECERA
                    for (int i = 1; i <= 11; i++)
                    {
                        oWs.Cells[1, i].Style.Font.Size = 9;
                        oWs.Cells[1, i].Style.Font.Bold = true;
                        oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);    //marco
                        oWs.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }

                    foreach (ResumenLecturas obj in loDatos)
                    {
                        //marco detalle
                        for (int i = 1; i <= 9; i++)
                        {
                            oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);    //marco
                        }

                        oWs.Cells[_fila, 1].Value = obj.id_Operario_Lectura;
                        oWs.Cells[_fila, 2].Value = obj.direccion_lectura;
                        oWs.Cells[_fila, 3].Value = obj.suministro_lectura;
                        oWs.Cells[_fila, 4].Value = obj.medidor_lectura;
                        oWs.Cells[_fila, 5].Value = obj.Zona_lectura;
                        oWs.Cells[_fila, 6].Value = obj.LecturaMovil_Lectura;
                        oWs.Cells[_fila, 7].Value = obj.confirmacion_Lectura;
                        oWs.Cells[_fila, 8].Value = obj.abreviatura_observacion;
                        oWs.Cells[_fila, 9].Value = obj.descripcion_estado;

                        _fila++;
                    }

                    oWs.Cells.Style.Font.Size            = 8; //letra tamaño
                    oWs.Row(1).Style.Font.Bold           = true;
                    oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
                    oWs.Row(1).Style.VerticalAlignment   = Style.ExcelVerticalAlignment.Center;

                    for (int i = 1; i <= 9; i++)
                    {
                        oWs.Column(i).AutoFit();
                    }

                    oEx.Save();
                }
                return(_Serialize("1|" + FileExcel, true));
            }
            catch (Exception ex)
            {
                return(_Serialize("0|" + ex.Message, true));
            }
        }
        public void ImportFormName(ExcelWorksheet sheet, IOrganizationService service, BackgroundWorker worker)
        {
            var rowsCount  = sheet.Dimension.Rows;
            var cellsCount = sheet.Dimension.Columns;
            var requests   = new List <SetLocLabelsRequest>();

            for (var rowI = 1; rowI < rowsCount; rowI++)
            {
                var currentFormId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString());

                var request = new SetLocLabelsRequest
                {
                    EntityMoniker = new EntityReference("systemform", currentFormId),
                    AttributeName = ZeroBasedSheet.Cell(sheet, rowI, 2).Value.ToString() == "Name" ? "name" : "description"
                };

                var labels = new List <LocalizedLabel>();

                var columnIndex = 3;
                while (columnIndex < cellsCount)
                {
                    if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
                    {
                        var lcid  = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
                        var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
                        labels.Add(new LocalizedLabel(label, lcid));
                    }

                    columnIndex++;
                }

                request.Labels = labels.ToArray();

                requests.Add(request);
            }

            int i = 0;

            foreach (var request in requests)
            {
                try
                {
                    service.Execute(request);

                    OnResult(new TranslationResultEventArgs
                    {
                        Success   = true,
                        SheetName = sheet.Name
                    });
                }
                catch (Exception error)
                {
                    OnResult(new TranslationResultEventArgs
                    {
                        Success   = false,
                        SheetName = sheet.Name,
                        Message   = $"{request.EntityMoniker.Id}/{request.AttributeName}: {error.Message}"
                    });
                }

                i++;
                worker.ReportProgressIfPossible(0, new ProgressInfo
                {
                    Item = i * 100 / requests.Count
                });
            }
        }