/// <summary> /// Converts a DataTable to a csv string /// </summary> /// <param name="dataTable">The dataTable</param> /// <param name="headers">(Optional) Headers</param> /// <param name="ignore">(Optional) Keys to ignore</param> public static string ToCSV(this IEnumerable<Dictionary<string, Object>> dataTable, string[] headers = null, string[] ignore = null) { if (ignore == null) ignore = new string[] { }; var stringWriter = new StringWriter(); var csvWriter = new CsvWriter(stringWriter); if (headers == null) { headers = (from kvp in dataTable.First() where !ignore.Contains(kvp.Key) select kvp.Key).ToArray(); } csvWriter.WriteHeaderRecord(headers); foreach (var record in dataTable) { var values = (from kvp in record where !ignore.Contains(kvp.Key) select kvp.Value == null? "": kvp.Value.ToString()).ToArray(); csvWriter.WriteDataRecord(values); } csvWriter.Close(); var csv = stringWriter.ToString(); return csv; }
public static void ExportCSV(string path, Guid pollId) { var poll = DataService.PerThread.ContentSet.OfType<Poll>().SingleOrDefault(x => x.Id == pollId); if (poll == null) { throw new BusinessLogicException("Указан неверный идентификатор голосования"); } else { var bulletins = poll.Bulletins.OrderByDescending(x => x.Weight).ThenByDescending(x => x.Result).ThenBy(x => x.Id).ToList(); using (var writer = new CsvWriter(path)) { writer.ValueSeparator = ';'; writer.WriteHeaderRecord("Номер бюллетеня", "Голос", "Вес"); foreach (var pollBulletin in bulletins) { if (pollBulletin.Weight.Equals(0)) { writer.WriteDataRecord(pollBulletin.Id, "Голос делегирован", "-"); } else { switch ((VoteOption)pollBulletin.Result) { case VoteOption.Yes: writer.WriteDataRecord(pollBulletin.Id, "За", pollBulletin.Weight); break; case VoteOption.No: writer.WriteDataRecord(pollBulletin.Id, "Против", pollBulletin.Weight); break; case VoteOption.NotVoted: writer.WriteDataRecord(pollBulletin.Id, "Не голосовал", pollBulletin.Weight); break; case VoteOption.Refrained: writer.WriteDataRecord(pollBulletin.Id, "Воздержался", pollBulletin.Weight); break; default: writer.WriteDataRecord(pollBulletin.Id, "Неизвестно", pollBulletin.Weight); break; } } } writer.Close(); } } }
public override void Execute(object parameter) { var stream = GetOutputStream(); if (stream == null) return; using (stream) using (var writer = new CsvWriter(stream)) { writer.WriteHeaderRecord(new[] {"Key"}.Concat(model.ValueCalculations.Select(v => v.Header))); foreach (var reportRow in model.Results) { writer.WriteDataRecord(new object[] { reportRow.Key}.Concat(model.ValueCalculations.Select(v => (object)reportRow.Values[v.Header]))); } } }
private static void Main() { if (Settings.Default.UseAccessPair) { _tokens.AccessToken = Settings.Default.AccessToken; _tokens.AccessTokenSecret = Settings.Default.AccessSecret; } else { if (string.IsNullOrEmpty(Settings.Default.UserAccessToken) || string.IsNullOrEmpty(Settings.Default.UserAccessSecret)) { var tokenResponse = GetUserToken(); Settings.Default.UserAccessToken = tokenResponse.Token; Settings.Default.UserAccessSecret = tokenResponse.TokenSecret; Settings.Default.UserId = (int)tokenResponse.UserId; Settings.Default.Save(); } _tokens.AccessToken = Settings.Default.UserAccessToken; _tokens.AccessTokenSecret = Settings.Default.UserAccessSecret; } /* { var userInfo = GetUserInfo().ToBsonDocument(); var csvWriter = new CsvWriter(Console.Out); csvWriter.WriteHeaderRecord(userInfo.Names.ToArray()); csvWriter.WriteDataRecord(userInfo.Values); Console.WriteLine(); } */ { var users = GetMutualFriends(_tokens); var csvWriter = new CsvWriter(Console.Out); csvWriter.WriteHeaderRecord(users.First().ToBsonDocument().Names.ToArray()); foreach (var user in users) { csvWriter.WriteDataRecord(user.ToBsonDocument().Values.ToArray()); } Console.WriteLine(); } }
/// <summary> /// Gets the locations CSV for a role. /// </summary> /// <param name="roleId">The role id.</param> /// <param name="clientId">The optional client id filter.</param> /// <param name="regionId">The optional region id filter.</param> /// <returns></returns> public byte[] GetLocationsCSVForRole(Guid roleId, Guid clientId, Guid regionId) { var businessAccount = ObjectContext.Owner(roleId).First(); var memoryStream = new MemoryStream(); var csvWriter = new CsvWriter(memoryStream); csvWriter.WriteHeaderRecord("Client", "Location", "Address 1", "Address 2", "City", "State", "Country Code", "Zip Code", "Region", "Latitude", "Longitude"); var locations = ObjectContext.Locations.Where(loc => loc.BusinessAccountId == businessAccount.Id && !loc.BusinessAccountIdIfDepot.HasValue); //Add client context if it exists if (clientId != Guid.Empty) locations = locations.Where(loc => loc.ClientId == clientId); //Add region context if it exists if (regionId != Guid.Empty) locations = locations.Where(loc => loc.RegionId == regionId); var records = from loc in locations //Get the Clients names join c in ObjectContext.Clients on loc.Client.Id equals c.Id orderby loc.AddressLineOne select new { ClientName = c.Name, loc.Name, loc.AddressLineOne, loc.AddressLineTwo, loc.AdminDistrictTwo, loc.AdminDistrictOne, loc.PostalCode, loc.CountryCode, RegionName = loc.Region.Name, loc.Latitude, loc.Longitude }; foreach (var record in records.ToArray()) csvWriter.WriteDataRecord(record.ClientName, record.Name, record.AddressLineOne, record.AddressLineTwo, record.AdminDistrictTwo, record.AdminDistrictOne, record.PostalCode, record.CountryCode, record.RegionName, record.Latitude, record.Longitude); csvWriter.Close(); var csv = memoryStream.ToArray(); memoryStream.Dispose(); return csv; }
/// <summary> /// Converts the DataTable to a CSV. /// </summary> /// <param name="alternateHeaders">The alternate headers.</param> /// <param name="columnsIndexesToIgnore">The columns indexes to ignore.</param> /// <returns>A byte[] CSV.</returns> public byte[] ToCSV(IEnumerable<string> alternateHeaders = null, IEnumerable<int> columnsIndexesToIgnore = null) { var memoryStream = new MemoryStream(); var csvWriter = new CsvWriter(memoryStream); var columnsToTake = this.Columns.Select(c => c.ColumnName).ToList(); //Remove any column indexes to ignores if (columnsIndexesToIgnore != null) foreach (var columnIndexToIgnore in columnsIndexesToIgnore.OrderByDescending(ci => ci)) columnsToTake.RemoveAt(columnIndexToIgnore); //If alternateHeaders is not null use them for the header record //Otherwise use the datatable's column names for the header record csvWriter.WriteHeaderRecord(alternateHeaders.ToArray() ?? columnsToTake.ToArray()); foreach (var row in this.Rows) { var rowValues = columnsToTake.Select(column => row[column]).ToArray(); csvWriter.WriteDataRecord(rowValues); } csvWriter.Close(); var csv = memoryStream.ToArray(); memoryStream.Dispose(); return csv; }
/// <summary> /// Gets the RecurringServices CSV for a role. /// </summary> /// <param name="roleId">The role id.</param> public byte[] GetRecurringServicesCSVForRole(Guid roleId) { var memoryStream = new MemoryStream(); var csvWriter = new CsvWriter(memoryStream); csvWriter.WriteHeaderRecord("Service Type", "Client", "Location", "Address Line 1", "Frequency", "Start Date", "End Date", "Repeat Every", "Repeat On"); var recurringServices = RecurringServicesForServiceProviderOptimized(roleId).Include(rs => rs.Client).Include(rs => rs.Repeat).Include(rs => rs.ServiceTemplate); //Force Load LocationField's Location //TODO: Optimize by IQueryable recurringServices.SelectMany(rs => rs.ServiceTemplate.Fields).OfType<LocationField>().Select(lf => lf.Value).ToArray(); //join RecurringServices with client names var records = from rs in recurringServices orderby rs.Client.Name select new { RecurringService = rs, ServiceType = rs.ServiceTemplate.Name, ClientName = rs.Client.Name }; foreach (var record in records.ToArray()) { //Get destination information from LocationField var destination = record.RecurringService.ServiceTemplate.GetDestination(); var locationName = ""; var address = ""; if (destination != null) { locationName = destination.Name; address = destination.AddressLineOne; } var repeat = record.RecurringService.Repeat; //Select proper end date info var endDate = ""; if (repeat.EndDate.HasValue) endDate = repeat.EndDate.Value.ToShortDateString(); else if (repeat.EndAfterTimes.HasValue) endDate = repeat.EndAfterTimes.Value.ToString(); #region setup RepeatOn string var frequency = repeat.Frequency; var repeatOn = ""; if (frequency == Frequency.Weekly) { var weeklyFrequencyDetail = repeat.FrequencyDetailAsWeeklyFrequencyDetail; //Build a weekly frequency string if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Sunday)) repeatOn += "Sun"; if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Monday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Mon"; } if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Tuesday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Tues"; } if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Wednesday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Wed"; } if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Thursday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Thur"; } if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Friday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Fri"; } if (weeklyFrequencyDetail.Any(fd => fd == DayOfWeek.Saturday)) { if (repeatOn.ToCharArray().Any()) repeatOn += ","; repeatOn += "Sat"; } } else if (frequency == Frequency.Monthly) { var monthlyFrequencyDetail = repeat.FrequencyDetailAsMonthlyFrequencyDetail; if (monthlyFrequencyDetail.HasValue) repeatOn = monthlyFrequencyDetail.Value == MonthlyFrequencyDetail.OnDayInMonth ? "Date" : "Day"; else repeatOn = "Date"; } #endregion //Convert frequency detail to: day or date //"Service Type", "Client", "Location", "Address", "Frequency" csvWriter.WriteDataRecord(record.ServiceType, record.ClientName, locationName, address, frequency.ToString(), //"Start Date", "End Date", "Repeat Every", "Repeat On" repeat.StartDate.ToShortDateString(), endDate, repeat.RepeatEveryTimes.ToString(), repeatOn); } csvWriter.Close(); var csv = memoryStream.ToArray(); memoryStream.Dispose(); return csv; }
public async Task ExportAsync(CancellationToken cancellationToken, Action<int> reportProgress) { reportProgress = reportProgress ?? delegate { }; var context = SynchronizationContext.Current; using (stream) using (var writer = new CsvWriter(stream)) { var extractor = new DocumentColumnsExtractor(columns); writer.WriteHeaderRecord(columns.Select(c => c.Header)); // we do the streaming of documents on a background thread mainly to escape the // SynchronizationContext: when there's no synchronization context involved the // async methods can resume on any thread instead of having to hop back to the UI thread. // This avoids massive overheads await TaskEx.Run(async () => { var totalResults = new Reference<long>(); using (var documentsStream = await collectionSource.StreamAsync(totalResults)) { IList<JsonDocument> documentBatch; var fetchedDocuments = 0; do { documentBatch = await GetNextBatch(documentsStream, cancellationToken); fetchedDocuments += documentBatch.Count; // extracting properties from the documents has to be done on the UI thread // because it might involve using FrameworkElements and Silverlight databinding context.Send(delegate { WriteColumnsForDocuments(writer, documentBatch, extractor); reportProgress((int) (((double) fetchedDocuments/totalResults.Value)*100)); }, null); } while (documentBatch.Count > 0); } }); } }
public Task ExportAsync(CancellationToken cancellationToken, Action<int> reportProgress) { this.cancellationToken = cancellationToken; this.ReportProgress = reportProgress ?? delegate { }; tcs = new TaskCompletionSource<bool>(); writer = new CsvWriter(stream); extractor = new DocumentColumnsExtractor(columns); writer.WriteHeaderRecord(columns.Select(c => c.Header)); FetchNextPage(); return tcs.Task; }