/// <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();
}
}
}
/// <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>
/// Сохраняет DataGridView в CSV в файл с указанным именем.
/// </summary>
/// <param name="dgvInf">Таблица DataGridView</param>
/// <param name="filename">Имя файла</param>
public static void DGVtoCSV(DataGridView dgvInf, string filename)
{
using (CsvWriter writer = new CsvWriter(filename))
{
writer.ValueSeparator = Char.Parse(";");
string[] dataString = new string[dgvInf.ColumnCount];
for (int i = 0; i < dgvInf.RowCount; i++)
{
for (int b = 0; b < dgvInf.ColumnCount; b++)
{
dataString[b] = dgvInf.Rows[i].Cells[b].Value.ToString();
}
writer.WriteDataRecord(dataString);
}
writer.Close();
}
}
/// <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;
}