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; }
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 }
public CalculationDataDrawable(CalculationData data, string clientNick, int columnCount, ExcelWorksheet excel) { _data = data; _clientNick = clientNick; _excel = excel; _columnCount = columnCount; }
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); } } }
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++; }
internal ExcelNamedRange(string name,ExcelWorkbook wb, ExcelWorksheet nameSheet, int index) : base(wb, nameSheet, name, true) { Name = name; _sheet = nameSheet; Index = index; }
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; }
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(); }
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(); }
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(); }
/// <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; }
/// <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); } } }
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(); } }
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; } }
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; }
/// <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; }
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]; } }
/// <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; }
/// <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(); }
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); }
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"; }
/// <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(); }
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); }
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); } }
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; } }
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; } }
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); } }
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; } }
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); }
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(); }
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()); }
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); }
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); }
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(); } } }
/// <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)); }
/// <summary> /// Default constructor /// </summary> /// <param name="sheet">The XLS sheet to wrap</param> public EPPlusExcelSheet(EPPlus.ExcelWorksheet sheet) { this._sheet = sheet; }
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); }
public override void Parse(OfficeOpenXml.ExcelWorksheet worksheet, int currRow) { throw new NotImplementedException(); }
public static ExcelRange Cell(this OfficeOpenXml.ExcelWorksheet worksheet, int rowNumber, String headerName) { int columnNumber = worksheet.ColumnHeaderNumber(headerName, true); return(worksheet.Cells[rowNumber, columnNumber]); }
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; } }
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" }); }
/// <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)); }
public virtual void Write(OfficeOpenXml.ExcelWorksheet ew, int row) { }
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 }); } }