Excel 操作

Aspose.Cells.dll ：无需安装office相关组件就可以操作excel。

using System;
using System.Web;
using Aspose.Cells;
using System.Data;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Reflection;
using System.IO;

namespace APP.ExcelOperation
{
    public class AsposeCellsHelper
    {
        #region 导出excel数据
        /// <summary>
        /// 导出excel数据
        /// </summary>
        /// <param name="dt">数据table</param>
        /// <param name="coll">标题对映数据列名</param>
        public static void ExportExcel(DataTable dt, NameValueCollection coll, string fileName)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            Aspose.Cells.Style s = new Aspose.Cells.Style();
            s.Font.IsBold = true;
            s.Font.Size = 12;
            s.Number = 49;

            //设置标题及格式
            for (int i = 0; i < coll.Count; i++)
            {
                sheet.Cells[0, i].SetStyle(s);
                sheet.Cells[0, i].Value = coll[i];
            }
            //填充内容
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int c = 0; c < coll.Count; c++)
                {
                    sheet.Cells[i + 1, c].Value = dt.Rows[i][coll.Keys[c]];
                }
            }
            ResponseFile(workbook, fileName);
        }

        public static void ExportExcel<T>(IEnumerable<T> data,string fileName)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];

            PropertyInfo[] ps = typeof(T).GetProperties();
            var colIndex = "A";

            foreach (var p in ps)
            {

                sheet.Cells[colIndex + 1].PutValue(p.Name);
                int i = 2;
                foreach (var d in data)
                {
                    sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                    i++;
                }

                colIndex = ((char)(colIndex[0] + 1)).ToString();
            }
            ResponseFile(workbook, fileName);
        }
        private static void ResponseFile(Workbook workbook, string fileName)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.ContentType = "application/ms-excel";
            response.BinaryWrite(workbook.SaveToStream().ToArray());
            response.End();
        }

        #endregion 导出excel数据end

        #region 读取excel中的数据

        /// <summary>
        /// 读取excel文件流到datatable
        /// </summary>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static DataTable ReadExcel(Stream fileStream)
        {
            Workbook book = new Workbook(fileStream);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>
        /// 读取excel文件流到datatable，格式是string
        /// 推荐使用
        /// </summary>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static DataTable ReadExcelAsString(Stream fileStream)
        {
            Workbook book = new Workbook(fileStream);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>
        /// 读取excel文件流到DataSet，格式是string
        /// 推荐使用
        /// </summary>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static DataSet ReadExcelAsStringToDataSet(Stream fileStream)
        {
            Workbook book = new Workbook(fileStream);
            DataSet ds = new DataSet();
            for (int i = 0; i < book.Worksheets.Count; i++)
            {
                Worksheet sheet = book.Worksheets[i];
                Cells cells = sheet.Cells;
                if (cells.Rows.Count > 0)
                {
                    var dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
                    dt.TableName = sheet.Name;
                    ds.Tables.Add(dt);
                }
            }
            return ds;
        }

        /// <summary>
        /// 读取excel文件到datatable
        /// </summary>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static DataTable ReadExcel(String strFileName)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>
        /// 读取excel文件到datatable，格式是string
        /// 推荐使用
        /// </summary>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static DataTable ReadExcelAsString(String strFileName)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }
        #endregion

    }
}