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