学步园

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using CSharpJExcel.Jxl;
using CSharpJExcel.Jxl.Write;
using CsvHelper.Configuration;

namespace Common.Extensions
{
    public static class ExcelExtension
    {
        public static IEnumerable<T> GetRecordsFromExcel<T>(this string path) where T : class, new()
        {
            var file = new FileInfo(path);

            Debug.Assert(!string.IsNullOrWhiteSpace(file.DirectoryName));

            Type type = typeof(T);
            var properties = type.GetProperties().Where(t => t.IsDefined(typeof(CsvFieldAttribute), true)).ToList();
            var headerNames = properties.Select(GetCsvFieldName).ToList();

            var workbook = Workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet(0);

            var headerCells = sheet.getRow(0);

            var query = from headerName in headerNames
                        join headerCell in headerCells
                            on headerName equals headerCell.getContents()
                        select new
                                   {
                                       Key = headerName,
                                       Value = headerCell.getColumn()
                                   };

            var nameIndexes = query.ToDictionary(t => t.Key, t => t.Value);
            var result = new List<T>();
            for (int row = 1; row < sheet.getRows(); row++)
            {
                var t = new T();

                foreach (var property in properties)
                {
                    string csvFiledName = property.GetCsvFieldName();
                    if (nameIndexes.ContainsKey(csvFiledName))
                    {
                        var column = nameIndexes[csvFiledName];
                        var value = sheet.getCell(column, row).getContents();
                        property.SetValue(t, value, null);
                    }
                }
                result.Add(t);
            }

            workbook.close();
            return result;
        }

        private static string GetCsvFieldName(this PropertyInfo property)
        {
            return ((CsvFieldAttribute)property.GetCustomAttributes(typeof(CsvFieldAttribute), true)[0]).Name;
        }

        public static void WriteRecordsToExcel<T>(this List<T> records, string path) where T : class
        {
            var file = new FileInfo(path);

            Debug.Assert(!string.IsNullOrWhiteSpace(file.DirectoryName));

            if (!Directory.Exists(file.DirectoryName))
                Directory.CreateDirectory(file.DirectoryName);

            var ws = new WorkbookSettings();
            ws.setEncoding("UTF8");
            WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
            WritableSheet sheet = workbook.createSheet(file.Name, 0);

            Type type = typeof(T);

            var properties = type.GetProperties().Where(t => t.IsDefined(typeof(CsvFieldAttribute), true)).ToList();

            for (int j = 0; j < properties.Count(); j++)
            {
                var attribute = (CsvFieldAttribute)properties[j].GetCustomAttributes(typeof(CsvFieldAttribute), true)[0];
                var cell = new Label(j, 0, attribute.Name);
                sheet.addCell(cell);
            }

            for (int i = 0; i < records.Count(); i++)
            {
                for (int j = 0; j < properties.Count(); j++)
                {
                    object value = properties[j].GetValue(records[i], null);
                    string s = value == null ? "" : value.ToString();
                    var cell = new Label(j, i + 1, s);
                    sheet.addCell(cell);
                }
            }

            workbook.write();
            workbook.close();
        }
    }
}