giovedì 26 giugno 2014

Export a list items in an excel file.

c# .NET 4.0 (WPF) - Excel 2013

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

                // These are columns of the excel file and these are the properties that I want to export.
                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!

Nessun commento:

Posta un commento