asp.net操作excel表格

Question

asp.netexcel数据库

Answer 1

有几种方法，一是用ODBC象连接普通数据库那样连接Excel文件并读取数据，适合于简单的表格数据的读取；二是用NPOI这个组件直接读取，适合于表格比较复杂的情况：https://npoi.codeplex.com/========================
http://www.webdiyer.com

Answer 2

protected void UploadBtn_Click(object sender, EventArgs e)
    {
        if (AlarmInfo.HasFile)
        {            
            DataTable dt = null;
            try
            {                
                using (MemoryStream stream = new MemoryStream(AlarmInfo.FileBytes))
                {
                    int num = 0;
                    dt = ExcelUtils.TranslateToTable(stream, 0);
                    //TODO 操作DataTable就好
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger(GetType()).Error("数据上传出现错误！", ex);
                throw ex;
            }
        }
        else
        {
            JQueryAlert("请选择上传文件！");
        }
    }最近刚做了个，ExcelUtils这个就好用着

Answer 3

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using Net.SourceForge.Koogra.Excel;/// <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 && row.Cells[0] != null && row.Cells[0].Value.ToString() != "")
                {
                    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.FormattedValue() : 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));
            }
            //新增自定义判断日期格式是否转换 2014.2.26 Gt
            double value = default(double);
            try
            {
                if (sheet.Rows.MaxRow > 0)
                {
                    double.TryParse(sheet.Rows[1].Cells[2].FormattedValue(), out value);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("文件中日期列未调整单元格格式为日期！",ex);
            }            
            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 = DateTime.MaxValue;
            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
}using Net.SourceForge.Koogra.Excel;

Answer 4

Refer this:
http://www.cnblogs.com/insus/archive/2012/06/12/2545801.html