#pragma warning disable CA1822 // Mark members as static public Cell Cell(object?value, DefaultStyle template, UInt32Value styleIndex) #pragma warning restore CA1822 // Mark members as static { string excelValue = value == null ? "" : template == DefaultStyle.DateTime ? ExcelExtensions.ToExcelDate(((DateTime)value)) : template == DefaultStyle.Date ? value is DateTime dt?ExcelExtensions.ToExcelDate(dt) : ExcelExtensions.ToExcelDate(((DateOnly)value).ToDateTime()) : template == DefaultStyle.Time ? ExcelExtensions.ToExcelTime((TimeOnly)value) : template == DefaultStyle.Decimal ? ExcelExtensions.ToExcelNumber(Convert.ToDecimal(value)) : template == DefaultStyle.Boolean ? ToYesNo((bool)value) : template == DefaultStyle.Enum ? ((Enum)value).NiceToString() : value.ToString() !; Cell cell = IsInlineString(template)? new Cell(new InlineString(new Text { Text = excelValue })) { DataType = CellValues.InlineString } : new Cell { CellValue = new CellValue(excelValue), DataType = null }; cell.StyleIndex = styleIndex; return(cell); }
/// <summary> /// Перекладывает первый лист из таблицы Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="excelFilePath"></param> /// <returns></returns> public static List <T> ExcelToList <T>(string excelFilePath) where T : class, new() { List <Dictionary <string, object> > listOfDicts; using (var dt = ExcelToDataTable(excelFilePath)) { listOfDicts = ExcelExtensions.ToDictionaryList(dt); } var descriptions = ExcelExtensions.GetMyPropertyDescriptions(typeof(T)); foreach (var dict in listOfDicts) { foreach (var description in descriptions) { if (!dict.ContainsKey(description.DisplayName)) { continue; } dict[description.PropertyName] = dict[description.DisplayName]; dict.Remove(description.DisplayName); } } return(listOfDicts.Select(x => x.ToObject <T>()).ToList()); }
public void GetRuntimeType_StringFormatCode() { Assert.IsTrue(ExcelExtensions.GetRuntimeType("") == typeof(string)); Assert.IsTrue(ExcelExtensions.GetRuntimeType("#") == typeof(double)); Assert.IsTrue(ExcelExtensions.GetRuntimeType("0") == typeof(double)); Assert.IsTrue(ExcelExtensions.GetRuntimeType("0.0") == typeof(double)); }
public void GetNumberFormatCodeTest() { Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 0)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 1)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 2)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 3)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 4)); Assert.IsNull(ExcelExtensions.GetNumberFormatCode(null, 5)); Assert.IsNull(ExcelExtensions.GetNumberFormatCode(null, 6)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 9)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 10)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 11)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 12)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 13)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 14)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 15)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 16)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 17)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 18)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 19)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 20)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 21)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 22)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 37)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 38)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 39)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 40)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 44)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 45)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 46)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 47)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 48)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 49)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 27)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 30)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 36)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 50)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 57)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 59)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 60)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 61)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 62)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 67)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 68)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 69)); Assert.IsNotNull(ExcelExtensions.GetNumberFormatCode(null, 70)); }
public void GenerateCols(IEnumerable list) { var first = true; foreach (var result in list) { if (first) { var properties = result.GetType().GetProperties() .Where(p => p.GetGridMappingAttributes().Any()) .Where(p => !p.GetGridMappingAttributes().First().IsDataKey) .OrderBy(p => (p.GetGridMappingAttributes().First()).Index) .ToArray(); foreach (var property in properties) { var attribute = property.GetGridMappingAttributes().First(); if (attribute.IsDataKey || attribute.IsTemplate) { continue; } if (string.IsNullOrEmpty(attribute.HeaderText) && attribute.ResourceName == null && attribute.VariableName == null) { continue; } var headerText = Format(string.IsNullOrEmpty(attribute.HeaderText) ? CultureManager.GetString(attribute.ResourceName, attribute.VariableName) : attribute.HeaderText).Replace(" ", "_").Replace("/", "_").Replace("%", "_").Replace("-", "_"); var excelData = _wbPart.GetDefinedName(headerText); var excelCol = new ExcelColumn { ExcelCol = ExcelExtensions.ParseExcelColumn(excelData), ExcelRow = ExcelExtensions.ParseExcelRow(excelData), PropertyName = property.Name, PropertyType = property.PropertyType.FullName, }; var templateCell = excelCol.ExcelCol + (excelCol.ExcelRow + 1); excelCol.Style = _wbPart.GetStyle(SheetName, templateCell); excelCol.ConditionalFormatStart = _wbPart.HasConditionals(SheetName, templateCell) ? templateCell : string.Empty; _excelColumns.Add(excelCol); } first = false; } } }
public void GenerateColumns <T>(List <T> list) { Logger.Debug("GenerateColumns start"); var properties = typeof(T).GetProperties() .Where(p => p.GetGridMappingAttributes().Any()) .OrderBy(p => (p.GetGridMappingAttributes().First()).Index); Logger.Debug("GenerateColumns foreach properties"); foreach (var property in properties) { var attribute = property.GetGridMappingAttributes().First(); if (attribute.IsDataKey || (attribute.IsTemplate && (attribute.DataFormatString.Trim().Equals(string.Empty) || attribute.DataFormatString.Trim().Equals("{0}")))) { continue; } if (string.IsNullOrEmpty(attribute.HeaderText) && attribute.ResourceName == null && attribute.VariableName == null) { continue; } //var templateName = !string.IsNullOrEmpty(attribute.ExcelTemplateName) // ? attribute.ExcelTemplateName // : Format(string.IsNullOrEmpty(attribute.HeaderText) // ? CultureManager.GetString(attribute.ResourceName, attribute.VariableName) // : attribute.HeaderText).Replace(" ", "_").Replace("/", "_").Replace("%", "_").Replace("-", "_"); var templateName = attribute.VariableName.Replace(" ", "_").Replace("/", "_").Replace("%", "_").Replace("-", "_"); var headerText = CultureManager.GetString(attribute.ResourceName, attribute.VariableName); var excelData = _wbPart.GetDefinedName(templateName); var excelCol = new ExcelColumn { ExcelCol = ExcelExtensions.ParseExcelColumn(excelData), ExcelRow = ExcelExtensions.ParseExcelRow(excelData), PropertyName = property.Name, PropertyType = property.PropertyType.FullName, DataFormatString = attribute.ExcelTextFormat, }; var templateCell = excelCol.ExcelCol + (excelCol.ExcelRow + 1); excelCol.Style = _wbPart.GetStyle(SheetName, templateCell); excelCol.ConditionalFormatStart = _wbPart.HasConditionals(SheetName, templateCell) ? templateCell : string.Empty; _excelColumns.Add(excelCol); _wbPart.UpdateValue(SheetName, _wbPart.GetDefinedName(templateName), headerText, 0, CellValues.SharedString, _sharedStrings); } Logger.Debug("GenerateColumns end"); }
private static InvoiceItem CreateInvoiceItem(IDictionary row) { InvoiceItem item = new InvoiceItem(); if (row.Contains("A")) { item.Description = ExcelExtensions.ParseDate(row["A"].ToString()).ToShortDateString(); } if (row.Contains("D")) { item.Hours = double.Parse(row["D"].ToString()); } return(item); }
public static void SetCellValue(this Cell cell, object?value, Type type) { if (type == typeof(string)) { cell.RemoveAllChildren(); cell.Append(new InlineString(new Text((string?)value !))); cell.DataType = CellValues.InlineString; } else { string excelValue = value == null ? "" : type.UnNullify() == typeof(DateTime) ? ExcelExtensions.ToExcelDate(((DateTime)value)) : type.UnNullify() == typeof(bool) ? (((bool)value) ? "TRUE": "FALSE") : IsNumber(type.UnNullify()) ? ExcelExtensions.ToExcelNumber(Convert.ToDecimal(value)) : value.ToString() !; cell.CellValue = new CellValue(excelValue); } }
/// <summary> /// Exports the provided data in format defined in the export configuration as excel file /// </summary> /// <param name="exportConfiguration"></param> /// <param name="data"></param> /// <returns></returns> public Stream Export(IExportConfiguration <TData> exportConfiguration, List <TData> data) { return(ExcelExtensions.Export(exportConfiguration as ExportConfiguration <TData>, data)); }
/// <summary> /// Returns imported data from the stream /// </summary> /// <param name="importConfiguration"></param> /// <param name="stream"></param> /// <returns></returns> public ListResult <TResponse> Import(IImportConfiguration <TResponse> importConfiguration, Stream stream) { return(new ListResult <TResponse>(ExcelExtensions.Import(stream, importConfiguration as ExcelImportConfiguration <TResponse>))); }
private static async Task Main(string[] args) { string orgName = null; string outputFileName = null; string cacheLocation = null; string githubToken = null; string ospoToken = null; string policyRepo = null; var help = false; var options = new OptionSet() .Add("org=", "The {name} of the GitHub organization", v => orgName = v) .Add("o|output=", "The {path} where the output .csv file should be written to.", v => outputFileName = v) .Add("cache-location=", "The {path} where the .json cache should be written to.", v => cacheLocation = v) .Add("github-token=", "The GitHub API {token} to be used.", v => githubToken = v) .Add("ospo-token=", "The OSPO API {token} to be used.", v => ospoToken = v) .Add("policy-repo=", "The GitHub {repo} policy violations should be file in.", v => policyRepo = v) .Add("h|?|help", null, v => help = true, true) .Add(new ResponseFileSource()); try { var unprocessed = options.Parse(args); if (help) { var exeName = Path.GetFileNameWithoutExtension(Environment.GetCommandLineArgs()[0]); Console.Error.WriteLine($"usage: {exeName} --org <org> [OPTIONS]+"); options.WriteOptionDescriptions(Console.Error); return; } if (unprocessed.Count > 0) { orgName = unprocessed[0]; unprocessed.RemoveAt(0); } if (orgName == null) { Console.Error.WriteLine($"error: --org must be specified"); return; } if (unprocessed.Any()) { foreach (var option in unprocessed) { Console.Error.WriteLine($"error: unrecognized argument {option}"); } return; } } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return; } if (outputFileName == null && !ExcelExtensions.IsExcelInstalled()) { Console.Error.WriteLine("error: you must specify an output path because you don't have Excel."); return; } await RunAsync(orgName, outputFileName, cacheLocation, githubToken, ospoToken, policyRepo); }
public void GetRuntimeType_ParamsNull_ReturnNull() { Assert.IsTrue(ExcelExtensions.GetRuntimeType(null) == typeof(string)); }
private static async Task Main(string[] args) { string orgName = null; var repoNames = new List <string>(); var teamNames = new List <string>(); var userNames = new List <string>(); string outputFileName = null; string cacheLocation = null; var help = false; var options = new OptionSet() .Add("org=", "The {name} of the GitHub organization", v => orgName = v) .Add("r|repo=", "The {name} of the repo to analyze impact for", v => repoNames.Add(v)) .Add("t|team=", "The {name} of the team to analyze impact for", v => teamNames.Add(v)) .Add("u|user="******"The {name} of the user to analyze impact for", v => userNames.Add(v)) .Add("o|output=", "The {path} where the output .csv file should be written to.", v => outputFileName = v) .Add("cache-location=", "The {path} where the .json cache is located.", v => cacheLocation = v) .Add("h|?|help", null, v => help = true, true) .Add(new ResponseFileSource()); try { var unprocessed = options.Parse(args); if (help) { var exeName = Path.GetFileNameWithoutExtension(Environment.GetCommandLineArgs()[0]); Console.Error.WriteLine($"This tool computes what would happen if a given team was removed."); Console.Error.WriteLine(); Console.Error.WriteLine($"usage: {exeName} --org <org> [OPTIONS]+"); options.WriteOptionDescriptions(Console.Error); return; } if (unprocessed.Count > 0) { orgName = unprocessed[0]; unprocessed.RemoveAt(0); } if (orgName == null) { Console.Error.WriteLine($"error: --org must be specified"); return; } if (unprocessed.Any()) { foreach (var option in unprocessed) { Console.Error.WriteLine($"error: unrecognized argument {option}"); } return; } } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return; } if (outputFileName == null && !ExcelExtensions.IsExcelInstalled()) { Console.Error.WriteLine("error: you must specify an output path because you don't have Excel."); return; } await RunAsync(orgName, repoNames, teamNames, userNames, outputFileName, cacheLocation); }
private static async Task Main(string[] args) { if (args.Length < 1 || 2 < args.Length) { var exeName = Path.GetFileNameWithoutExtension(Environment.GetCommandLineArgs()[0]); Console.Error.WriteLine("error: wrong number of arguments"); Console.Error.WriteLine($"usage {exeName} <org-name> [output-path]"); return; } var orgName = args[0]; var outputFileName = args.Length < 2 ? null : args[1]; var isForExcel = outputFileName == null; if (outputFileName == null && !ExcelExtensions.IsExcelInstalled()) { Console.Error.WriteLine("error: you must specify and output path because you don't have Excel."); return; } var client = await GitHubClientFactory.CreateAsync(); var cachedOrg = await LoadCachedOrgAsync(client, orgName); var csvDocument = new CsvDocument("repo", "repo-state", "repo-last-pushed", "principal-kind", "principal", "permission", "via-team"); using (var writer = csvDocument.Append()) { foreach (var repo in cachedOrg.Repos) { var publicPrivate = repo.IsPrivate ? "private" : "public"; var lastPush = repo.LastPush.ToLocalTime().DateTime.ToString(); var repoUrl = $"https://github.com/{cachedOrg.Name}/{repo.Name}"; foreach (var teamAccess in repo.Teams) { var permissions = teamAccess.Permission.ToString().ToLower(); var teamName = teamAccess.Team.Name; var teamUrl = $"https://github.com/orgs/{cachedOrg.Name}/teams/{teamName.ToLower()}"; writer.Write(CreateHyperlink(isForExcel, repoUrl, repo.Name)); writer.Write(publicPrivate); writer.Write(lastPush); writer.Write("team"); writer.Write(CreateHyperlink(isForExcel, teamUrl, teamName)); writer.Write(permissions); writer.Write(teamName); writer.WriteLine(); } foreach (var userAccess in repo.Users) { var via = cachedOrg.DescribeAccess(userAccess); var userUrl = $"https://github.com/{userAccess.User}"; var permissions = userAccess.Permission.ToString().ToLower(); writer.Write(CreateHyperlink(isForExcel, repoUrl, repo.Name)); writer.Write(publicPrivate); writer.Write(lastPush); writer.Write("user"); writer.Write(CreateHyperlink(isForExcel, userUrl, userAccess.User)); writer.Write(permissions); writer.Write(via); writer.WriteLine(); } } } if (outputFileName == null) { csvDocument.ViewInExcel(); } else { var extension = Path.GetExtension(outputFileName); if (extension == ".md") { csvDocument.SaveAsMarkdownTable(outputFileName); } else { csvDocument.Save(outputFileName); } } }