aspose.cells excel表格导入导出

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Reflection;
using System.IO;
using Aspose.Cells;
using System.Data;
using System.ComponentModel;
using System.Configuration;

namespace src.Common
{
    public static class Excel
    {
        /// <summary>
        /// list转为excel保存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据源</param>
        /// <param name="saveWay">保存路径</param>
        /// <param name="name">文件名</param>
        /// <returns></returns>
        public static string ListToExcel<T>(List<T> list, string saveWay, string name)
        {
            Workbook workBook = new Workbook();
            Worksheet worksheet = workBook.Worksheets[0];
            Cells cells = worksheet.Cells;
            Type t = typeof(T);
            string tempName = "";
            int i = 0, k = 1;
            foreach (PropertyInfo p in t.GetProperties())
            {
                DescriptionAttribute attr = (DescriptionAttribute)p.GetCustomAttributes(typeof(DescriptionAttribute), true).FirstOrDefault();
                if (attr != null)
                {
                    tempName = attr.Description;
                }
                else
                {
                    tempName = p.Name;
                }
                cells[0, i].PutValue(tempName);
                Style style = cells[0, i].GetStyle();
                style.BackgroundColor = System.Drawing.Color.Blue;
                style.HorizontalAlignment = TextAlignmentType.Center;
                cells[0, i].SetStyle(style);
                i++;
            }
            foreach (T tt in list)
            {
                i = 0;
                foreach (PropertyInfo p1 in t.GetProperties())
                {
                    if (p1.GetValue(tt, null) != null)
                    {
                        cells[k, i].PutValue(p1.GetValue(tt, null).ToString());
                    }
                    i++;
                }
                k++;
            }


            //row.Style.BackgroundColor = System.Drawing.Color.Blue;
            //row.Style.HorizontalAlignment = TextAlignmentType.Center;

            worksheet.AutoFitColumns();
            worksheet.AutoFitRows();

            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string baseWay = System.AppDomain.CurrentDomain.BaseDirectory;
            string currentTime = DateTime.Now.ToString("yyyy-MM");
            string fileWay = baseWay + saveWay + "\\" + currentTime;
            if (!File.Exists(fileWay))
            {
                Directory.CreateDirectory(fileWay);
            }

            workBook.Save(fileWay + "\\" + name + fileName);

            return ConfigurationManager.AppSettings["Domain"] + "/" + saveWay + "/" + currentTime + "/" + name + fileName;
        }


        /// <summary>
        /// 表格转为datatable
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="datatable"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public static bool ExcelToDataTable(string filepath, out DataTable datatable, out string error)
        {
            error = "";
            datatable = null;
            try
            {
                if (!File.Exists(filepath))
                {
                    error = "文件不存在";
                    datatable = null;
                    return false;
                }
                Workbook workbook = new Workbook(filepath);
                Worksheet worksheet = workbook.Worksheets[0];
                datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }
        /// <summary>
        /// datatable转list,第一行为列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ToList1<T>(this DataTable dt, string primaryKey)
        {
            List<T> ts = new List<T>();
            Type t = typeof(T);
            List<object> cols = dt.Rows[0].ItemArray.ToList();
            bool isNull = false;
            if (!cols.Contains(primaryKey))
            {
                return null;
            }
            else
            {
                for (int i = 1; i < dt.Rows.Count - 1; i++)
                {
                    isNull = false;
                    T tt = System.Activator.CreateInstance<T>();
                    string tempName = "";
                    foreach (PropertyInfo p in t.GetProperties())
                    {
                        DescriptionAttribute attr = (DescriptionAttribute)p.GetCustomAttributes(typeof(DescriptionAttribute), true).FirstOrDefault();
                        if (attr != null)
                        {
                            tempName = attr.Description;
                        }
                        else
                        {
                            tempName = p.Name;
                        }
                        if (cols.Contains(tempName))
                        {
                            object value = dt.Rows[i][cols.IndexOf(tempName)];
                            if (tempName == primaryKey && (value == null||string.IsNullOrEmpty(value.ToString())))
                            {
                                isNull = true;
                            }
                            if (value != null && (!string.IsNullOrEmpty(value.ToString())))
                            {
                                if (!typeof(DBNull).Equals(p.PropertyType.GetType()))
                                {
                                    p.SetValue(tt, Convert.ChangeType(value, p.PropertyType), null);
                                }
                                else
                                {
                                    p.SetValue(tt, null, null);
                                }
                            }
                        }
                    }
                    if (!isNull)
                    {
                        ts.Add(tt);
                    }

                }
                return ts;
            }
        }
    }
}