asp.net导入excel方法大集合

    经典三大方法：

http://www.cnblogs.com/songliang/archive/2009/06/04/1495881.html

    开源组件：

http://www.oschina.net/project/tag/258/excel-tools?sort=view&lang=20&os=0

    组件比较：

http://kb.cnblogs.com/a/2324852/

自己在项目中只用到了Koogra，只说一下自己遇到的问题吧，关于2007到读取的问题，

（Invalid header magic number.）

Koogra读取2003和2007是不一样的，需要你写两个不同的配置类，放代码：

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Net.SourceForge.Koogra.Excel;

/// <summary>
///ExcelUtils 的摘要说明
/// </summary>
public class FrameworkOffice
{
    /// <summary>
    /// Excel工具类
    /// </summary> 
    public class ExcelUtils
    {
        private Net.SourceForge.Koogra.Excel.Workbook book;

        private Net.SourceForge.Koogra.Excel2007.Workbook book2007;

        public ExcelUtils(string path)
        {
            this.book = new Workbook(path);
        }
        public ExcelUtils(System.IO.Stream stream)
        {
            this.book = new Workbook(stream);
        }
        protected DataTable SaveAsDataTable(Worksheet sheet)
        {
            DataTable dt = new DataTable();
            uint minRow = sheet.Rows.MinRow;
            uint maxRow = sheet.Rows.MaxRow;
            Row firstRow = sheet.Rows[minRow];
            uint minCol = firstRow.Cells.MinCol;
            uint maxCol = firstRow.Cells.MaxCol;
            for (uint i = minCol; i <= maxCol; i++)
            {
                dt.Columns.Add(firstRow.Cells[i].FormattedValue());
            }
            for (uint i = minRow + 1; i <= maxRow; i++)
            {
                Row row = sheet.Rows[i]; if (row != null)
                {
                    DataRow dr = dt.NewRow();
                    for (uint j = minCol; j <= maxCol; j++)
                    {
                        Cell cell = row.Cells[j]; if (cell != null)
                        {
                            dr[Convert.ToInt32(j)] = cell.Value != null ? cell.Value.ToString() : string.Empty;
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public DataTable ToDataTable(int index)
        {
            Worksheet sheet = this.book.Sheets[index]; if (sheet == null)
            {
                throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在！", index));
            } return this.SaveAsDataTable(sheet);
        }
        public DataTable ToDataTable(string sheetName)
        {
            Worksheet sheet = this.book.Sheets.GetByName(sheetName);
            if (sheet == null)
            {
                throw new ApplicationException(string.Format("名称[{0}]所指定的电子表格不存在！", sheetName));
            }
            return this.SaveAsDataTable(sheet);
        }
        #region 静态方法
        /// <summary>
        /// 单元格格式为日期时间，使用此方法转换为DateTime类型，若解析失败则返回‘0001-01-01’
        /// </summary>        
        public static DateTime ParseDateTime(string cellValue)
        {
            DateTime date = default(DateTime);
            double value = default(double);
            if (double.TryParse(cellValue, out value))
            {
                date = DateTime.FromOADate(value);
            }
            else
            {
                DateTime.TryParse(cellValue, out date);
            }
            return date;
        }
        /// <summary>
        /// 
        /// 转换为DataTable(文件路径 表名)
        /// </summary>     
        public static DataTable TranslateToTable(string path, string sheetName)
        {
            ExcelUtils utils = new ExcelUtils(path);
            return utils.ToDataTable(sheetName);
        }
        /// <summary>
        /// 转换为DataTable(文件路径 表索引)
        /// </summary>        
        public static DataTable TranslateToTable(string path, int sheetIndex)
        {
            ExcelUtils utils = new ExcelUtils(path);
            return utils.ToDataTable(sheetIndex);
        }/// <summary>
        /// 转换为DataTable(文件路径)
        /// </summary>       
        public static DataTable TranslateToTable(string path)
        {
            ExcelUtils utils = new ExcelUtils(path);
            return utils.ToDataTable(0);
        }
        /// <summary>
        /// 转换为DataTable(内存流 表名)
        /// </summary>      
        public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
        {
            ExcelUtils utils = new ExcelUtils(stream);
            return utils.ToDataTable(sheetName);
        }
        /// <summary>
        /// 转换为DataTable(内存流 表索引)
        /// </summary>      
        public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex)
        {
            ExcelUtils utils = new ExcelUtils(stream);
            return utils.ToDataTable(sheetIndex);
        }
        /// <summary>
        /// 转换为DataTable(内存流)
        /// </summary>       
        public static DataTable TranslateToTable(System.IO.Stream stream)
        {
            ExcelUtils utils = new ExcelUtils(stream); return utils.ToDataTable(0);
        }
        #endregion
    }


    /// <summary>
    /// Excel工具类
    /// </summary> 
    public class ExcelUtils2007
    {
        private Net.SourceForge.Koogra.Excel2007.Workbook book;

        public ExcelUtils2007(string path)
        {
            this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(path);
        }
        public ExcelUtils2007(System.IO.Stream stream)
        {
            this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(stream);
        }
        protected DataTable SaveAsDataTable(Net.SourceForge.Koogra.Excel2007.Worksheet sheet)
        {
            DataTable dt = new DataTable();
            uint minRow = sheet.CellMap.FirstRow;
            uint maxRow = sheet.CellMap.LastRow;
            Net.SourceForge.Koogra.Excel2007.Row firstRow = sheet.GetRow(minRow);
            uint minCol = sheet.CellMap.FirstCol;
            uint maxCol = sheet.CellMap.LastCol;
            for (uint i = minCol; i <= maxCol; i++)
            {
                dt.Columns.Add(firstRow.GetCell(i).GetFormattedValue());
            }
            for (uint i = minRow + 1; i <= maxRow; i++)
            {
                Net.SourceForge.Koogra.Excel2007.Row row = sheet.GetRow(i);
                if (row != null)
                {
                    DataRow dr = dt.NewRow();
                    for (uint j = minCol; j <= maxCol; j++)
                    {
                        Net.SourceForge.Koogra.ICell cell = row.GetCell(j); 
                        if (cell != null)
                        {
                            dr[Convert.ToInt32(j)] = cell.Value != null ? cell.Value.ToString() : string.Empty;
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public DataTable ToDataTable(int index)
        {
            Net.SourceForge.Koogra.Excel2007.Worksheet sheet = this.book.GetWorksheet(0);
            if (sheet == null)
            {
                throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在！", index));
            }
            return this.SaveAsDataTable(sheet);
        }
        public DataTable ToDataTable(string sheetName)
        {
            Net.SourceForge.Koogra.Excel2007.Worksheet sheet = this.book.GetWorksheetByName(sheetName);
            if (sheet == null)
            {
                throw new ApplicationException(string.Format("名称[{0}]所指定的电子表格不存在！", sheetName));
            }
            return this.SaveAsDataTable(sheet);
        }
        #region 静态方法
        /// <summary>
        /// 单元格格式为日期时间，使用此方法转换为DateTime类型，若解析失败则返回‘0001-01-01’
        /// </summary>        
        public static DateTime ParseDateTime(string cellValue)
        {
            DateTime date = default(DateTime);
            double value = default(double);
            if (double.TryParse(cellValue, out value))
            {
                date = DateTime.FromOADate(value);
            }
            else
            {
                DateTime.TryParse(cellValue, out date);
            }
            return date;
        }
        /// <summary>
        /// 
        /// 转换为DataTable(文件路径 表名)
        /// </summary>     
        public static DataTable TranslateToTable(string path, string sheetName)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(path);
            return utils.ToDataTable(sheetName);
        }
        /// <summary>
        /// 转换为DataTable(文件路径 表索引)
        /// </summary>        
        public static DataTable TranslateToTable(string path, int sheetIndex)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(path);
            return utils.ToDataTable(sheetIndex);
        }/// <summary>
        /// 转换为DataTable(文件路径)
        /// </summary>       
        public static DataTable TranslateToTable(string path)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(path);
            return utils.ToDataTable(0);
        }
        /// <summary>
        /// 转换为DataTable(内存流 表名)
        /// </summary>      
        public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(stream);
            return utils.ToDataTable(sheetName);
        }
        /// <summary>
        /// 转换为DataTable(内存流 表索引)
        /// </summary>      
        public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(stream);
            return utils.ToDataTable(sheetIndex);
        }
        /// <summary>
        /// 转换为DataTable(内存流)
        /// </summary>       
        public static DataTable TranslateToTable(System.IO.Stream stream)
        {
            ExcelUtils2007 utils = new ExcelUtils2007(stream); return utils.ToDataTable(0);
        }
        #endregion
    }
}

 

  测试过没有问题！！！

 再次修改2012年3月1日17:19:37。