I would
like to export my list items (WPF ListView) in an excel file and I would like to do this
using a generic mode.
I did it in
this way:
/// <summary>
/// Exports to CSV.
/// </summary>
private void ExportToCsv()
{
string exportPath = ConfigurationManager.AppSettings["ExportToCsvPath"];
if (!Directory.Exists(exportPath))
Directory.CreateDirectory(exportPath);
string fileName = Path.Combine(exportPath, "ExportActivities.csv");
if (!Help.ExportToCsvStream(CreateDataGrid(), fileName))
{
string msg = String.Format(Strings.def_msgExportToCsvError, Environment.NewLine);
MessageBox.Show(msg, MessageTitle, MessageBoxButton.OK, MessageBoxImage.Error);
}
}
/// <summary>
/// Creates the data grid.
/// </summary>
/// <returns></returns>
private StringBuilder CreateDataGrid()
{
try
{
// These are the column headers of the excel file.
String[] headersToExport =
{
"UserName", "Municipality", "BinderName", "BoxNumber", "ImageNumber", "DiscardedImage",
"DocumentsNumber", "TotalPagesNumber", "StartDate", "EndDate", "SendDate", "ImportDate", "State"
};
String[] propertiesToExport =
{
"PreferedUser.UserName", "Municipality", "BinderName", "BoxNumber", "ImageNumber", "DiscardedImage",
"DocumentsNumber", "TotalPagesNumber", "StartDate", "EndDate", "SendDate", "ImportDate", "State"
};
return Activities.ToCsv(headersToExport,
propertiesToExport);
}
catch (Exception ex)
{
string msg = String.Format(Strings.def_msgExportToCsvError, Environment.NewLine);
MessageBox.Show(msg, MessageTitle, MessageBoxButton.OK, MessageBoxImage.Error);
Log.WriteLog(ex);
return null;
}
}
/// <summary>
/// Exports to CSV stream.
/// </summary>
/// <param name="sb">The sb.</param>
/// <param
name="fileName">Name of the file.</param>
/// <returns></returns>
public static bool ExportToCsvStream(StringBuilder sb, string fileName)
{
try
{
//
Default ANSI code for csv file
Encoding ansi = Encoding.GetEncoding(1252);
byte[] csvBytes =
ansi.GetBytes(sb.ToString());
CreateAndOpenFile(csvBytes,
fileName);
return true;
}
catch (Exception ex)
{
Log.WriteLog(ex);
return false;
}
}
/// <summary>
/// Formats for CSV.
/// </summary>
/// <param
name="dato">The dato.</param>
/// <returns></returns>
public static string FormatForCsv(string dato)
{
if (dato != null)
{
string datoFormatted = dato.Trim();
if (string.IsNullOrEmpty(datoFormatted))
return string.Empty;
datoFormatted =
datoFormatted
.Replace(Environment.NewLine, string.Empty)
.Replace(@"""", "'")
.Replace(";", ".");
if (datoFormatted.Length >
255)
datoFormatted =
datoFormatted.Substring(0, 255);
return string.Format(@"=""{0}""", datoFormatted);
}
return string.Empty;
}
private static int _processIdExcel;
/// <summary>
/// Creates the and open file.
/// </summary>
/// <param
name="bytes">The bytes.</param>
/// <param
name="filename">The filename.</param>
private static void CreateAndOpenFile(byte[] bytes, string filename)
{
try
{
if (File.Exists(filename))
{
CloseExcel();
File.Delete(filename);
}
using (var stream = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
stream.Write(bytes, 0,
bytes.Length);
}
var process = Process.Start(filename);
if (process != null) _processIdExcel = process.Id;
}
catch (Exception ex)
{
Log.WriteLog(ex);
throw;
}
}
/// <summary>
/// Closes the excel.
/// </summary>
private static void CloseExcel()
{
try
{
if (_processIdExcel > 0)
{
// The Excel process already exists and has been instantiated from us.
Process procExcel = Process.GetProcessById(_processIdExcel);
if (String.Equals(procExcel.ProcessName, "excel", StringComparison.OrdinalIgnoreCase))
procExcel.Kill();
_processIdExcel = 0;
}
else
{
// The process has been instantiated but Excel does not check if there is anyway existence.
Process[] processes = Process.GetProcessesByName("Excel");
if (processes.Length > 0)
{
_processIdExcel =
processes[0].Id;
CloseExcel();
}
}
Thread.Sleep(20);
}
catch (Exception ex)
{
Log.WriteLog(ex);
}
}
/// <summary>
/// Gets or sets the activities.
/// </summary>
/// <value>
/// The activities.
/// </value>
public ObservableCollection<Activity> Activities
{
get { return _activities; }
set
{
if (value != _activities)
{
_activities = value;
RaisePropertyChanged(ActivitiesPropertyName);
}
}
}
/// <summary>
/// To the CSV.
/// </summary>
/// <typeparam
name="T"></typeparam>
/// <param
name="enumerableList">The enumerable list.</param>
/// <param
name="headersToExport">The headers to export.</param>
/// <param
name="propertiesToExport">The properties to export.</param>
/// <returns></returns>
public static StringBuilder ToCsv<T>(this IEnumerable<T>
enumerableList, String[]
headersToExport,
String[] propertiesToExport)
{
try
{
if (enumerableList != null)
{
// Create the CSV file to which
grid data will be exported.
var sb = new StringBuilder();
var typeParameterType = typeof (T);
#region Headers
foreach (string t in headersToExport)
sb.Append(String.Concat(t, ";"));
sb.Append(Environment.NewLine);
#endregion
#region Properties
var propertyInfos =
typeParameterType.GetProperties();
foreach (var entity in enumerableList)
{
for (int index = 0; index <
propertiesToExport.Length; index++)
{
String[] properties =
propertiesToExport[index].Split(".".ToCharArray());
PropertyInfo property = null;
if (properties.Count() > 1)
{
#region If it is a composed
property, like PreferedUser.UserName
var pInfos = propertyInfos;
object en = entity;
for (int i = 0; i < properties.Count();
i++)
{
property =
pInfos.FirstOrDefault(pi => pi.Name.Equals(properties[i]));
if (property != null && en != null && i <
properties.Count() - 1)
{
pInfos =
property.PropertyType.GetProperties();
en =
property.GetValue(en, null);
}
}
if (en != null)
{
if (property != null && property.PropertyType
== typeof (DateTime?))
{
if (property.GetValue(en, null) != null)
sb.Append(
String.Concat(
((DateTime?) property.GetValue(en, null)).Value.ToItaFormat(), ";"));
else
sb.Append(";");
}
else if (property != null && property.PropertyType
== typeof (DateTime))
{
sb.Append(
String.Concat(
((DateTime) property.GetValue(entity, null)).ToItaFormat(), ";"));
}
else if (property != null)
sb.Append(String.Concat(property.GetValue(en,
null), ";"));
}
else
sb.Append(";");
#endregion
}
else
{
int index1 = index;
property =
propertyInfos.FirstOrDefault(pi =>
pi.Name.Equals(propertiesToExport[index1]));
if (property != null && property.PropertyType
== typeof (DateTime?))
{
if (property.GetValue(entity, null) != null)
sb.Append(
String.Concat(
((DateTime?) property.GetValue(entity,
null)).Value.ToItaFormat(), ";"));
else
sb.Append(";");
}
else if (property != null && property.PropertyType
== typeof (DateTime))
{
sb.Append(
String.Concat(
((DateTime) property.GetValue(entity, null)).ToItaFormat(), ";"));
}
else if (property != null)
sb.Append(String.Concat(property.GetValue(entity,
null), ";"));
}
}
sb.Append(Environment.NewLine);
}
#endregion
return sb;
}
return null;
}
catch
{
return null;
}
}
If you have any questions, please contact me as well.
See you again soon!
