[Asp.net]常见数据导入Excel，Excel数据导入数据库解决方案，总有一款适合你！-白红宇

[Asp.net]常见数据导入Excel，Excel数据导入数据库解决方案，总有一款适合你！

阅读量：5921 次

发布时间：2019-06-19

本文共 55515 字，大约阅读时间需要 185 分钟。

引言

项目中常用到将数据导入Excel，将Excel中的数据导入数据库的功能，曾经也查找过相关的内容，将曾经用过的方案总结一下。

方案一

NPOI

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。

使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

优势

（一）传统操作Excel遇到的问题：

1、如果是.NET，需要在服务器端装Office，且及时更新它，以防漏洞，还需要设定权限允许.NET访问COM+，如果在导出过程中出问题可能导致服务器宕机。

2、Excel会把只包含数字的列进行类型转换，本来是文本型的，Excel会将其转成数值型的，比如编号000123会变成123。

3、导出时，如果字段内容以“-”或“=”开头，Excel会把它当成公式进行，会报错。

4、Excel会根据Excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702E+17格式，日期列变成包含日期和数字的。

（二）使用NPOI的优势

1、您可以完全免费使用该框架

2、包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)

3、专业的技术支持服务(24*7全天候) (非免费)

4、支持处理的文件格式包括xls, xlsx, docx.

5、采用面向接口的设计架构( 可以查看 NPOI.SS 的命名空间)

6、同时支持文件的导入和导出

7、基于.net 2.0 也支持xlsx 和 docx格式(当然也支持.net 4.0)

8、来自全世界大量成功且真实的测试Cases

9、大量的实例代码

11、你不需要在服务器上安装微软的Office，可以避免版权问题。

12、使用起来比Office PIA的API更加方便，更人性化。

13、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。

NPOI之所以强大，并不是因为它支持导出Excel，而是因为它支持导入Excel，并能“理解”OLE2文档结构，这也是其他一些Excel读写库比较弱的方面。通常，读入并理解结构远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在Excel读写库中，也体现在pdf读写库中，目前市面上大部分的pdf库仅支持生成，不支持导入。

构成

NPOI 1.2.x主要由POIFS、DDF、HPSF、HSSF、SS、Util六部分组成。

NPOI.POIFS	OLE2/ActiveX文档属性读写库
NPOI.DDF	Microsoft Office Drawing读写库
NPOI.HPSF	OLE2/ActiveX文档读写库
NPOI.HSSF	Microsoft Excel BIFF(Excel 97-2003)格式读写库
NPOI.SS	Excel公用接口及Excel公式计算引擎
NPOI.Util	基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发

NPOI组成部分

NPOI 1.x的最新版为NPOI 1.2.5，其中包括了以下功能：

1、读写

OLE2文档

2、读写

DocummentSummaryInformation和

SummaryInformation

3、基于

LittleEndian的字节读写

4、读写

Excel BIFF格式

5、识别并读写

Excel BIFF中的常见

Record，如RowRecord, StyleRecord, ExtendedFormatRecord

6、支持设置单元格的高、宽、样式等

7、支持调用部分

Excel内建函数，比如说

sum, countif以及计算符号

8、支持在生成的

XLS内嵌入打印设置，比如说横向/纵向打印、缩放、使用的纸张等。

NPOI 2.0主要由SS, HPSF, DDF, HSSF, XWPF, XSSF, OpenXml4Net, OpenXmlFormats组成，具体列表如下：

Assembly名称	模块/命名空间	说明
NPOI.DLL	NPOI.POIFS	OLE2/ActiveX文档属性读写库
NPOI.DLL	NPOI.DDF	微软Office Drawing读写库
NPOI.DLL	NPOI.HPSF	OLE2/ActiveX文档读写库
NPOI.DLL	NPOI.HSSF	微软Excel BIFF(Excel 97-2003, doc)格式读写库
NPOI.DLL	NPOI.SS	Excel公用接口及Excel公式计算引擎
NPOI.DLL	NPOI.Util	基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发
NPOI.OOXML.DLL	NPOI.XSSF	Excel 2007(xlsx)格式读写库
NPOI.OOXML.DLL	NPOI.XWPF	Word 2007(docx)格式读写库
NPOI.OpenXml4Net.DLL	NPOI.OpenXml4Net	OpenXml底层zip包读写库
NPOI.OpenXmlFormats.DLL	NPOI.OpenXmlFormats	微软Office OpenXml对象关系库

(以上内容来自百度百科）从上表可知NPOI组件已支持excel2007，记得之前用的时候只支持excel2003。很久没研究过这玩意儿了。

案例

官网地址：，可以从官网下载NPOI2.X版本的。

首先引入

ICSharpCode.SharpZipLib.dll

NPOI.dll

NPOI.OOXML.dll

NPOI.OpenXml4Net.dll

NPOI.OpenXmlFormats.dll

然后引入命名空间：

using NPOI.XSSF.UserModel;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;

辅助类

using NPOI.XSSF.UserModel;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using NPOI.SS.Formula.Eval;namespace Wolfy.Common{    ///     /// 使用NPOI组件    /// 需引入ICSharpCode.SharpZipLib.dll/NPOI.dll/NPOI.OOXML.dll/NPOI.OpenXml4Net.dll/NPOI.OpenXmlFormats.dll    /// office2007    /// 
    public class NPOIExcelHelper    {        ///         /// 将Excel文件中的数据读出到DataTable中        /// 
        ///         /// 
              public static DataTable Excel2DataTable(string file, string sheetName, string tableName)        {            DataTable dt = new DataTable();            IWorkbook workbook = null;            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))            {                //office2003 HSSFWorkbook                workbook = new XSSFWorkbook(fs);            }            ISheet sheet = workbook.GetSheet(sheetName);            dt = Export2DataTable(sheet, 0, true);            return dt;        }        ///         /// 将指定sheet中的数据导入到datatable中        /// 
        /// 指定需要导出的sheet        /// 列头所在的行号，-1没有列头        ///         /// 
              private static DataTable Export2DataTable(ISheet sheet, int HeaderRowIndex, bool needHeader)        {            DataTable dt = new DataTable();            XSSFRow headerRow = null;            int cellCount;            try            {                if (HeaderRowIndex < 0 || !needHeader)                {                    headerRow = sheet.GetRow(0) as XSSFRow;                    cellCount = headerRow.LastCellNum;                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)                    {                        DataColumn column = new DataColumn(Convert.ToString(i));                        dt.Columns.Add(column);                    }                }                else                {                    headerRow = sheet.GetRow(HeaderRowIndex) as XSSFRow;                    cellCount = headerRow.LastCellNum;                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)                    {                        ICell cell = headerRow.GetCell(i);                        if (cell == null)                        {                            break;//到最后 跳出循环                        }                        else                        {                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());                            dt.Columns.Add(column);                        }                    }                }                int rowCount = sheet.LastRowNum;                for (int i = HeaderRowIndex + 1; i <= sheet.LastRowNum; i++)                {                    XSSFRow row = null;                    if (sheet.GetRow(i) == null)                    {                        row = sheet.CreateRow(i) as XSSFRow;                    }                    else                    {                        row = sheet.GetRow(i) as XSSFRow;                    }                    DataRow dtRow = dt.NewRow();                    for (int j = row.FirstCellNum; j <= cellCount; j++)                    {                        if (row.GetCell(j) != null)                        {                            switch (row.GetCell(j).CellType)                            {                                case CellType.Boolean:                                    dtRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                    break;                                case CellType.Error:                                    dtRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                    break;                                case CellType.Formula:                                    switch (row.GetCell(j).CachedFormulaResultType)                                    {                                        case CellType.Boolean:                                            dtRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                            break;                                        case CellType.Error:                                            dtRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                            break;                                        case CellType.Numeric:                                            dtRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);                                            break;                                        case CellType.String:                                            string strFORMULA = row.GetCell(j).StringCellValue;                                            if (strFORMULA != null && strFORMULA.Length > 0)                                            {                                                dtRow[j] = strFORMULA.ToString();                                            }                                            else                                            {                                                dtRow[j] = null;                                            }                                            break;                                        default:                                            dtRow[j] = "";                                            break;                                    }                                    break;                                case CellType.Numeric:                                    if (DateUtil.IsCellDateFormatted(row.GetCell(j)))                                    {                                        dtRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);                                    }                                    else                                    {                                        dtRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);                                    }                                    break;                                case CellType.String:                                    string str = row.GetCell(j).StringCellValue;                                    if (!string.IsNullOrEmpty(str))                                    {                                        dtRow[j] = Convert.ToString(str);                                    }                                    else                                    {                                        dtRow[j] = null;                                    }                                    break;                                default:                                    dtRow[j] = "";                                    break;                            }                        }                    }                    dt.Rows.Add(dtRow);                }            }            catch (Exception)            {                return null;            }            return dt;        }        ///         /// 将DataTable中的数据导入Excel文件中        /// 
        ///         ///         public static void DataTable2Excel(DataTable dt, string file, string sheetName)        {            IWorkbook workbook = new XSSFWorkbook();            ISheet sheet = workbook.CreateSheet(sheetName);            IRow header = sheet.CreateRow(0);            for (int i = 0; i < dt.Columns.Count; i++)            {                ICell cell = header.CreateCell(i);                cell.SetCellValue(dt.Columns[i].ColumnName);            }            //数据            for (int i = 0; i < dt.Rows.Count; i++)            {                IRow row = sheet.CreateRow(i + 1);                for (int j = 0; j < dt.Columns.Count; j++)                {                    ICell cell = row.CreateCell(j);                    cell.SetCellValue(dt.Rows[i][j].ToString());                }            }            MemoryStream stream = new MemoryStream();            workbook.Write(stream);            byte[] buffer = stream.ToArray();            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))            {                fs.Write(buffer, 0, buffer.Length);                fs.Flush();            }        }        ///         /// 获取单元格类型        /// 
        ///         /// 
              private static object GetValueType(XSSFCell cell)        {            if (cell == null)            {                return null;            }            switch (cell.CellType)            {                case CellType.Blank:                    return null;                case CellType.Boolean:                    return cell.BooleanCellValue;                case CellType.Error:                    return cell.ErrorCellValue;                case CellType.Numeric:                    return cell.NumericCellValue;                case CellType.String:                    return cell.StringCellValue;                case CellType.Formula:                default:                    return "=" + cell.StringCellValue;            }        }    }}

NPOIExcelHelper

参考：

测试结果

导入Excel，student.xlsx

导入DataTable，这里只将数据导入DataTable，导入数据库部分，就不再写了。

测试数据，向qq群里一朋友要的。大概有5w多条，lz机子是老爷机跑不起，只导出了其中的1k条。

方案二

利用office的com组件

首先添加com引用

引入命名空间

using Excel = Microsoft.Office.Interop.Excel;

Com操作Excel辅助类

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using Excel = Microsoft.Office.Interop.Excel;using System.Web.UI;using System.Web;using System.Data;namespace Wolfy.Common{    ///     /// 使用com组件 操作Excel    /// 
    public class ComExcelHelper    {        private Excel.Application appExcel = null;        private Excel.Workbook workbook = null;        private Excel.Worksheet sheet = null;        private DateTime dtBefore;        private DateTime dtAfter;        private string filePath;        public string FilePath        {            get { return filePath; }            set { filePath = value; }        }        private string timestamp;        ///         /// 以时间字符串作为保存文件的名称        /// 
        public string Timestamp        {            get { return timestamp; }            set { timestamp = value; }        }        private object mValue = System.Reflection.Missing.Value;        ///         ///是否打开Excel界面         /// 
        public bool Visible        {            set            {                appExcel.Visible = value;            }        }        public ComExcelHelper()        {            this.dtBefore = DateTime.Now;            appExcel = new Excel.Application();            this.dtAfter = DateTime.Now;            this.timestamp = DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();        }        public ComExcelHelper(string strFilePath)        {            this.dtBefore = DateTime.Now;            appExcel = new Excel.Application();            this.dtAfter = DateTime.Now;            this.workbook = (Excel.Workbook)appExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);            this.timestamp = DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();        }        public void Dispose()        {            try            {                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);                sheet = null;                workbook.Close(false, mValue, mValue);                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                workbook = null;                appExcel.Quit();                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);                appExcel = null;                GC.Collect();                GC.WaitForPendingFinalizers();            }            catch (Exception ex)            {                throw ex;            }            finally            {                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))                {                    if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)                    {                        pro.Kill();                    }                }            }            System.GC.SuppressFinalize(this);        }        ///         /// 加载Excel        /// 
        public void Load()        {            if (workbook == null && this.filePath != null)            {                workbook = appExcel.Workbooks.Open(this.filePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);            }        }        ///         /// 加载Excel        /// 
        public void Load(string strFilePath)        {            if (workbook == null)            {                workbook = appExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);            }        }        ///         /// 新建工作表        /// 
        ///         public void NewWorkSheet(string sheetName)        {            sheet = workbook.Sheets.Add(workbook.Sheets[1], mValue, mValue, mValue);            sheet.Name = sheetName;        }        ///         /// 在指定的单元格插入指定的值        /// 
        /// 单元格 如"A4"        /// 文本 数字等值        public void WriteCell(string strCell, object objValue)        {            sheet.get_Range(strCell, mValue).Value2 = objValue;        }        ///         /// 在指定Range中插入指定的值        /// 
        /// Range的开始单元格        /// Range的结束单元格        /// 文本、数字等值        public void WriteRange(string strStartCell, string strEndCell, object objValue)        {            sheet.get_Range(strStartCell, strEndCell).Value2 = objValue;        }        /**/        ///         /// 合并单元格，并在合并后的单元格中插入指定的值        /// 
        ///         ///         ///         public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)        {            sheet.get_Range(strStartCell, strEndCell).Merge(mValue);            sheet.get_Range(strStartCell, mValue).Value2 = objValue;        }        /**/        ///         /// 在连续单元格中插入一个DataTable中的值        /// 
        /// 开始的单元格        /// 存储数据的DataTable        public void WriteTable(string strStartCell, System.Data.DataTable dtData)        {            object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count];            for (int i = 0; i < dtData.Rows.Count; i++)                for (int j = 0; j < dtData.Columns.Count; j++)                    arrData[i, j] = dtData.Rows[i][j];            sheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 1)).Value2 = arrData;            arrData = null;        }        /**/        ///         /// 在连续单元格中插入一个DataTable并作超级链接        /// 
        /// 起始单元格标识符        /// 存储数据的DataTable        /// 链接的地址字段        /// 链接的文本字段        public void WriteTableAndLink(string strStartCell, System.Data.DataTable dtData, string strLinkField, string strTextField)        {            object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count - 1];            for (int i = 0; i < dtData.Rows.Count; i++)            {                for (int j = 0; j < dtData.Columns.Count; j++)                {                    if (j > dtData.Columns.IndexOf(strLinkField))                        arrData[i, j - 1] = dtData.Rows[i][j];                    else if (j < dtData.Columns.IndexOf(strLinkField))                        arrData[i, j] = dtData.Rows[i][j];                }            }            sheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 2)).Value2 = arrData;            for (int i = 0; i < dtData.Rows.Count; i++)                this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell)) + dtData.Columns.IndexOf(strTextField)) + System.Convert.ToString(this.GetCellNumber(strStartCell) + i), dtData.Rows[i][strLinkField].ToString() + ".htm", "点击查看详细", dtData.Rows[i][strTextField].ToString());            arrData = null;        }        /**/        ///         /// 为单元格设置公式        /// 
        /// 单元格标识符        /// 公式        public void SetFormula(string strCell, string strFormula)        {            sheet.get_Range(strCell, mValue).Formula = strFormula;        }        /**/        ///         /// 设置单元格或连续区域的字体为黑体        /// 
        /// 单元格标识符        public void SetBold(string strCell)        {            sheet.get_Range(strCell, mValue).Font.Bold = true;        }        /**/        ///         /// 设置连续区域的字体为黑体        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        public void SetBold(string strStartCell, string strEndCell)        {            sheet.get_Range(strStartCell, strEndCell).Font.Bold = true;        }        /**/        ///         /// 设置单元格或连续区域的字体颜色        /// 
        /// 单元格标识符        /// 颜色        public void SetColor(string strCell, System.Drawing.Color clrColor)        {            sheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);        }        /**/        ///         /// 设置连续区域的字体颜色        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        /// 颜色        public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor)        {            sheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);        }        /**/        ///         /// 设置单元格或连续区域的边框：上下左右都为黑色连续边框        /// 
        /// 单元格标识符        public void SetBorderAll(string strCell)        {            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;        }        /**/        ///         /// 设置连续区域的边框：上下左右都为黑色连续边框        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        public void SetBorderAll(string strStartCell, string strEndCell)        {            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);            sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;        }        /**/        ///         /// 设置单元格或连续区域水平居左        /// 
        /// 单元格标识符        public void SetHAlignLeft(string strCell)        {            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;        }        /**/        ///         /// 设置连续区域水平居左        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        public void SetHAlignLeft(string strStartCell, string strEndCell)        {            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;        }        /**/        ///         /// 设置单元格或连续区域水平居左        /// 
        /// 单元格标识符        public void SetHAlignCenter(string strCell)        {            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;        }        /**/        ///         /// 设置连续区域水平居中        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        public void SetHAlignCenter(string strStartCell, string strEndCell)        {            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;        }        /**/        ///         /// 设置单元格或连续区域水平居右        /// 
        /// 单元格标识符        public void SetHAlignRight(string strCell)        {            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;        }        /**/        ///         /// 设置连续区域水平居右        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        public void SetHAlignRight(string strStartCell, string strEndCell)        {            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;        }        /**/        ///         /// 设置单元格或连续区域的显示格式        /// 
        /// 单元格标识符        /// 如"#,##0.00"的显示格式        public void SetNumberFormat(string strCell, string strNF)        {            sheet.get_Range(strCell, mValue).NumberFormat = strNF;        }        /**/        ///         /// 设置连续区域的显示格式        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        /// 如"#,##0.00"的显示格式        public void SetNumberFormat(string strStartCell, string strEndCell, string strNF)        {            sheet.get_Range(strStartCell, strEndCell).NumberFormat = strNF;        }        /**/        ///         /// 设置单元格或连续区域的字体大小        /// 
        /// 单元格或连续区域标识符        ///         public void SetFontSize(string strCell, int intFontSize)        {            sheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();        }        /**/        ///         /// 设置连续区域的字体大小        /// 
        /// 开始单元格标识符        /// 结束单元格标识符        /// 字体大小        public void SetFontSize(string strStartCell, string strEndCell, int intFontSize)        {            sheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString();        }        /**/        ///         /// 设置列宽        /// 
        /// 列标识，如A代表第一列        /// 宽度        public void SetColumnWidth(string strColID, double dblWidth)        {            ((Excel.Range)sheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, sheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth;        }        /**/        ///         /// 为单元格添加超级链接        /// 
        /// 单元格标识符        /// 链接地址        /// 屏幕提示        /// 链接文本        public void AddHyperLink(string strCell, string strAddress, string strTip, string strText)        {            sheet.Hyperlinks.Add(sheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText);        }        /**/        ///         /// 已知开始的单元格标识，求intR行、intColumn列后的单元格标识        /// 
        /// 开始单元格标识        /// 行数        /// 列数        /// 
      
       单元格标识符结果
              public string GetEndCell(string strStartCell, int intR, int intC)        {            string endcell = string.Empty;            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?
      
       [A-Z]+)(?
       
        \d+)");            if (regex.IsMatch(strStartCell))            {                endcell = this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}")) + intC) + System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}")) + intR));            }            return endcell;        }        /**/        /// 
                /// 获取单元格标识符中的字母        /// 
        /// 
        单元格标识符        /// 
        
         单元格标识符对应的字母
                public string GetCellLetter(string strCell)        {            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?
        
         [A-Z]+)(?
         
          \d+)");            return regex.Match(strCell).Result("${vLetter}");        }        /**/        /// 
                  /// 获取单元格标识符中的数字        /// 
        /// 
          单元格标识符        public int GetCellNumber(string strCell)        {            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?
          
           [A-Z]+)(?
           
            \d+)"); return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}")); } /**/ /// 
             /// 另存为xls文件 /// 
 /// 
            文件路径 public void SaveAs(string strFilePath) { workbook.SaveCopyAs(strFilePath); } /**/ /// 
             /// 另存为xls文件 /// 
 /// 
            文件路径 public void Save() { workbook.Save(); } /// 
             /// 将Excel中的数据 读入DataTable中 /// 
 /// 
            excel文件的路径 /// 
            datatable的名字 /// 
            表头，以逗号分隔 /// 
             public DataTable ReadExcel2DataTable(string filePath, string dtName, string headName) { workbook = this.appExcel.Workbooks.Open(filePath, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue); sheet = workbook.Worksheets.get_Item(1); DataTable dt = new DataTable(dtName); string[] heads = !string.IsNullOrEmpty(headName) ? headName.Split(',') : null; if (heads != null) { for (int i = 0; i < heads.Length; i++) { DataColumn column = new DataColumn(heads[i]); //这里没对类型 进行处理 dt.Columns.Add(column); } for (int i = 0; i <= sheet.UsedRange.Rows.Count; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j < heads.Length; j++) { string cellString = NtoL(j + 1); dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2; } dt.Rows.Add(dr); } } else { for (int i = 0; i <= sheet.UsedRange.Rows.Count; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j <= sheet.UsedRange.Columns.Count; j++) { dr[j + 1] = sheet.get_Range(NtoL(j + 1) + (i + 1)).Value2; } dt.Rows.Add(dr); } } return dt; } /**/ /// 
             /// 另存为html文件 /// 
 /// 
            文件路径 public void SaveHtml(string strFilePath) { workbook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue); } public void CreateHtmlFile() { } /**/ /// 
             /// 字母转换为数字，Excel列头，如A-1;AA-27 /// 
 /// 
            字母 /// 
            
             字母对应的数字
             private int LtoN(string strLetter) { int intRtn = 0; string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; if (strLetter.Length == 2) intRtn += (strLetters.IndexOf(strLetter.Substring(0, 1)) + 1) * 26; intRtn += strLetters.IndexOf(strLetter.Substring(strLetter.Length - 1, 1)) + 1; return intRtn; } /**/ /// 
             /// 数字转换为字母，Excel列头，如1-A;27-AA /// 
 /// 
            数字 /// 
            
             数字对应的字母
             private string NtoL(int intNumber) { if (intNumber > 702) return String.Empty; if (intNumber == 702) return "ZZ"; string strRtn = String.Empty; string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; if (intNumber > 26) strRtn = strLetters.Substring(intNumber / 26 - 1, 1); strRtn += strLetters.Substring((intNumber % 26) - 1, 1); return strRtn; } }}

ComExcelHelper

参考：

DataTable导入Excel

Excel读入DataTable

这里未对类型进行处理，datetime类型的数据会转换成数值类型的

方案三

将Excel数据表当作数据源，通过 OleDb来实现。

同样需要引入Microsoft Excel 14.0 Object Library

分享一个操作类：

1 using System;  2 using System.Collections;  3 using System.Collections.Generic;  4 using System.Data;  5 using System.Data.OleDb;  6 using System.IO;  7 using System.Linq;  8 using System.Text;  9 using System.Threading.Tasks; 10 using System.Web; 11 using System.Web.UI; 12 using System.Web.UI.WebControls; 13  14 namespace Wolfy.Common 15 { 16     class OleDbExcelHelper 17     { 18         #region 数据导出至Excel文件 19         ///   20         /// 导出Excel文件，自动返回可下载的文件流  21         ///   22         public static void DataTable1Excel(System.Data.DataTable dtData) 23         { 24             GridView gvExport = null; 25             HttpContext curContext = HttpContext.Current; 26             StringWriter strWriter = null; 27             HtmlTextWriter htmlWriter = null; 28             if (dtData != null) 29             { 30                 curContext.Response.ContentType = "application/vnd.ms-excel"; 31                 curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); 32                 curContext.Response.Charset = "utf-8"; 33                 strWriter = new StringWriter(); 34                 htmlWriter = new HtmlTextWriter(strWriter); 35                 gvExport = new GridView(); 36                 gvExport.DataSource = dtData.DefaultView; 37                 gvExport.AllowPaging = false; 38                 gvExport.DataBind(); 39                 gvExport.RenderControl(htmlWriter); 40                 curContext.Response.Write("
      " + strWriter.ToString()); 41                 curContext.Response.End(); 42             } 43         } 44  45         ///  46         /// 导出Excel文件，转换为可读模式 47         /// 
 48         public static void DataTable2Excel(System.Data.DataTable dtData) 49         { 50             DataGrid dgExport = null; 51             HttpContext curContext = HttpContext.Current; 52             StringWriter strWriter = null; 53             HtmlTextWriter htmlWriter = null; 54  55             if (dtData != null) 56             { 57                 curContext.Response.ContentType = "application/vnd.ms-excel"; 58                 curContext.Response.ContentEncoding = System.Text.Encoding.UTF8; 59                 curContext.Response.Charset = ""; 60                 strWriter = new StringWriter(); 61                 htmlWriter = new HtmlTextWriter(strWriter); 62                 dgExport = new DataGrid(); 63                 dgExport.DataSource = dtData.DefaultView; 64                 dgExport.AllowPaging = false; 65                 dgExport.DataBind(); 66                 dgExport.RenderControl(htmlWriter); 67                 curContext.Response.Write(strWriter.ToString()); 68                 curContext.Response.End(); 69             } 70         } 71  72         ///  73         /// 导出Excel文件，并自定义文件名 74         /// 
 75         public static void DataTable3Excel(System.Data.DataTable dtData, String FileName) 76         { 77             GridView dgExport = null; 78             HttpContext curContext = HttpContext.Current; 79             StringWriter strWriter = null; 80             HtmlTextWriter htmlWriter = null; 81  82             if (dtData != null) 83             { 84                 HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8); 85                 curContext.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls"); 86                 curContext.Response.ContentType = "application nd.ms-excel"; 87                 curContext.Response.ContentEncoding = System.Text.Encoding.UTF8; 88                 curContext.Response.Charset = "GB2312"; 89                 strWriter = new StringWriter(); 90                 htmlWriter = new HtmlTextWriter(strWriter); 91                 dgExport = new GridView(); 92                 dgExport.DataSource = dtData.DefaultView; 93                 dgExport.AllowPaging = false; 94                 dgExport.DataBind(); 95                 dgExport.RenderControl(htmlWriter); 96                 curContext.Response.Write(strWriter.ToString()); 97                 curContext.Response.End(); 98             } 99         }100 101         /// 102         /// 将数据导出至Excel文件103         /// 
104         /// DataTable对象105         /// Excel文件路径106         public static bool OutputToExcel(System.Data.DataTable Table, string ExcelFilePath)107         {108             if (File.Exists(ExcelFilePath))109             {110                 throw new Exception("该文件已经存在！");111             }112 113             if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))114             {115                 Table.TableName = "Sheet1";116             }117 118             //数据表的列数119             int ColCount = Table.Columns.Count;120 121             //用于记数，实例化参数时的序号122             int i = 0;123 124             //创建参数125             OleDbParameter[] para = new OleDbParameter[ColCount];126 127             //创建表结构的SQL语句128             string TableStructStr = @"Create Table " + Table.TableName + "(";129 130             //连接字符串131             string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";132             OleDbConnection objConn = new OleDbConnection(connString);133 134             //创建表结构135             OleDbCommand objCmd = new OleDbCommand();136 137             //数据类型集合138             ArrayList DataTypeList = new ArrayList();139             DataTypeList.Add("System.Decimal");140             DataTypeList.Add("System.Double");141             DataTypeList.Add("System.Int16");142             DataTypeList.Add("System.Int32");143             DataTypeList.Add("System.Int64");144             DataTypeList.Add("System.Single");145 146             //遍历数据表的所有列，用于创建表结构147             foreach (DataColumn col in Table.Columns)148             {149                 //如果列属于数字列，则设置该列的数据类型为double150                 if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)151                 {152                     para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);153                     objCmd.Parameters.Add(para[i]);154 155                     //如果是最后一列156                     if (i + 1 == ColCount)157                     {158                         TableStructStr += col.ColumnName + " double)";159                     }160                     else161                     {162                         TableStructStr += col.ColumnName + " double,";163                     }164                 }165                 else166                 {167                     para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);168                     objCmd.Parameters.Add(para[i]);169 170                     //如果是最后一列171                     if (i + 1 == ColCount)172                     {173                         TableStructStr += col.ColumnName + " varchar)";174                     }175                     else176                     {177                         TableStructStr += col.ColumnName + " varchar,";178                     }179                 }180                 i++;181             }182 183             //创建Excel文件及文件结构184             try185             {186                 objCmd.Connection = objConn;187                 objCmd.CommandText = TableStructStr;188 189                 if (objConn.State == ConnectionState.Closed)190                 {191                     objConn.Open();192                 }193                 objCmd.ExecuteNonQuery();194             }195             catch (Exception exp)196             {197                 throw exp;198             }199 200             //插入记录的SQL语句201             string InsertSql_1 = "Insert into " + Table.TableName + " (";202             string InsertSql_2 = " Values (";203             string InsertSql = "";204 205             //遍历所有列，用于插入记录，在此创建插入记录的SQL语句206             for (int colID = 0; colID < ColCount; colID++)207             {208                 if (colID + 1 == ColCount)  //最后一列209                 {210                     InsertSql_1 += Table.Columns[colID].ColumnName + ")";211                     InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";212                 }213                 else214                 {215                     InsertSql_1 += Table.Columns[colID].ColumnName + ",";216                     InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";217                 }218             }219 220             InsertSql = InsertSql_1 + InsertSql_2;221 222             //遍历数据表的所有数据行223             for (int rowID = 0; rowID < Table.Rows.Count; rowID++)224             {225                 for (int colID = 0; colID < ColCount; colID++)226                 {227                     if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")228                     {229                         para[colID].Value = 0;230                     }231                     else232                     {233                         para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();234                     }235                 }236                 try237                 {238                     objCmd.CommandText = InsertSql;239                     objCmd.ExecuteNonQuery();240                 }241                 catch (Exception exp)242                 {243                     string str = exp.Message;244                 }245             }246             try247             {248                 if (objConn.State == ConnectionState.Open)249                 {250                     objConn.Close();251                 }252             }253             catch (Exception exp)254             {255                 throw exp;256             }257             return true;258         }259 260         /// 261         /// 将数据导出至Excel文件262         /// 
263         /// DataTable对象264         /// 要导出的数据列集合265         /// Excel文件路径266         public static bool OutputToExcel(System.Data.DataTable Table, ArrayList Columns, string ExcelFilePath)267         {268             if (File.Exists(ExcelFilePath))269             {270                 throw new Exception("该文件已经存在！");271             }272 273             //如果数据列数大于表的列数，取数据表的所有列274             if (Columns.Count > Table.Columns.Count)275             {276                 for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)277                 {278                     Columns.RemoveAt(s);   //移除数据表列数后的所有列279                 }280             }281 282             //遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除283             DataColumn column = new DataColumn();284             for (int j = 0; j < Columns.Count; j++)285             {286                 try287                 {288                     column = (DataColumn)Columns[j];289                 }290                 catch (Exception)291                 {292                     Columns.RemoveAt(j);293                 }294             }295             if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))296             {297                 Table.TableName = "Sheet1";298             }299 300             //数据表的列数301             int ColCount = Columns.Count;302 303             //创建参数304             OleDbParameter[] para = new OleDbParameter[ColCount];305 306             //创建表结构的SQL语句307             string TableStructStr = @"Create Table " + Table.TableName + "(";308 309             //连接字符串310             string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";311             OleDbConnection objConn = new OleDbConnection(connString);312 313             //创建表结构314             OleDbCommand objCmd = new OleDbCommand();315 316             //数据类型集合317             ArrayList DataTypeList = new ArrayList();318             DataTypeList.Add("System.Decimal");319             DataTypeList.Add("System.Double");320             DataTypeList.Add("System.Int16");321             DataTypeList.Add("System.Int32");322             DataTypeList.Add("System.Int64");323             DataTypeList.Add("System.Single");324 325             DataColumn col = new DataColumn();326 327             //遍历数据表的所有列，用于创建表结构328             for (int k = 0; k < ColCount; k++)329             {330                 col = (DataColumn)Columns[k];331 332                 //列的数据类型是数字型333                 if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)334                 {335                     para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);336                     objCmd.Parameters.Add(para[k]);337 338                     //如果是最后一列339                     if (k + 1 == ColCount)340                     {341                         TableStructStr += col.Caption.Trim() + " Double)";342                     }343                     else344                     {345                         TableStructStr += col.Caption.Trim() + " Double,";346                     }347                 }348                 else349                 {350                     para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);351                     objCmd.Parameters.Add(para[k]);352 353                     //如果是最后一列354                     if (k + 1 == ColCount)355                     {356                         TableStructStr += col.Caption.Trim() + " VarChar)";357                     }358                     else359                     {360                         TableStructStr += col.Caption.Trim() + " VarChar,";361                     }362                 }363             }364 365             //创建Excel文件及文件结构366             try367             {368                 objCmd.Connection = objConn;369                 objCmd.CommandText = TableStructStr;370 371                 if (objConn.State == ConnectionState.Closed)372                 {373                     objConn.Open();374                 }375                 objCmd.ExecuteNonQuery();376             }377             catch (Exception exp)378             {379                 throw exp;380             }381 382             //插入记录的SQL语句383             string InsertSql_1 = "Insert into " + Table.TableName + " (";384             string InsertSql_2 = " Values (";385             string InsertSql = "";386 387             //遍历所有列，用于插入记录，在此创建插入记录的SQL语句388             for (int colID = 0; colID < ColCount; colID++)389             {390                 if (colID + 1 == ColCount)  //最后一列391                 {392                     InsertSql_1 += Columns[colID].ToString().Trim() + ")";393                     InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";394                 }395                 else396                 {397                     InsertSql_1 += Columns[colID].ToString().Trim() + ",";398                     InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";399                 }400             }401 402             InsertSql = InsertSql_1 + InsertSql_2;403 404             //遍历数据表的所有数据行405             DataColumn DataCol = new DataColumn();406             for (int rowID = 0; rowID < Table.Rows.Count; rowID++)407             {408                 for (int colID = 0; colID < ColCount; colID++)409                 {410                     //因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称411                     DataCol = (DataColumn)Columns[colID];412                     if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")413                     {414                         para[colID].Value = 0;415                     }416                     else417                     {418                         para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();419                     }420                 }421                 try422                 {423                     objCmd.CommandText = InsertSql;424                     objCmd.ExecuteNonQuery();425                 }426                 catch (Exception exp)427                 {428                     string str = exp.Message;429                 }430             }431             try432             {433                 if (objConn.State == ConnectionState.Open)434                 {435                     objConn.Close();436                 }437             }438             catch (Exception exp)439             {440                 throw exp;441             }442             return true;443         }444         #endregion445 446         /// 447         /// 获取Excel文件数据表列表448         /// 
449         public static ArrayList GetExcelTables(string ExcelFileName)450         {451             System.Data.DataTable dt = new System.Data.DataTable();452             ArrayList TablesList = new ArrayList();453             if (File.Exists(ExcelFileName))454             {455                 using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))456                 {457                     try458                     {459                         conn.Open();460                         dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });461                     }462                     catch (Exception exp)463                     {464                         throw exp;465                     }466 467                     //获取数据表个数468                     int tablecount = dt.Rows.Count;469                     for (int i = 0; i < tablecount; i++)470                     {471                         string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');472                         if (TablesList.IndexOf(tablename) < 0)473                         {474                             TablesList.Add(tablename);475                         }476                     }477                 }478             }479             return TablesList;480         }481 482         /// 483         /// 将Excel文件导出至DataTable(第一行作为表头)484         /// 
485         /// Excel文件路径486         /// 数据表名，如果数据表名错误，默认为第一个数据表名487         public static DataTable InputFromExcel(string ExcelFilePath, string TableName)488         {489             if (!File.Exists(ExcelFilePath))490             {491                 throw new Exception("Excel文件不存在！");492             }493 494             //如果数据表名不存在，则数据表名为Excel文件的第一个数据表495             ArrayList TableList = new ArrayList();496             TableList = GetExcelTables(ExcelFilePath);497 498             if (TableName.IndexOf(TableName) < 0)499             {500                 TableName = TableList[0].ToString().Trim();501             }502 503             DataTable table = new DataTable();504             OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");505             OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);506             OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);507 508             try509             {510                 if (dbcon.State == ConnectionState.Closed)511                 {512                     dbcon.Open();513                 }514                 adapter.Fill(table);515             }516             catch (Exception exp)517             {518                 throw exp;519             }520             finally521             {522                 if (dbcon.State == ConnectionState.Open)523                 {524                     dbcon.Close();525                 }526             }527             return table;528         }529 530         /// 531         /// 获取Excel文件指定数据表的数据列表532         /// 
533         /// Excel文件名534         /// 数据表名535         public static ArrayList GetExcelTableColumns(string ExcelFileName, string TableName)536         {537             DataTable dt = new DataTable();538             ArrayList ColsList = new ArrayList();539             if (File.Exists(ExcelFileName))540             {541                 using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))542                 {543                     conn.Open();544                     dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });545 546                     //获取列个数547                     int colcount = dt.Rows.Count;548                     for (int i = 0; i < colcount; i++)549                     {550                         string colname = dt.Rows[i]["Column_Name"].ToString().Trim();551                         ColsList.Add(colname);552                     }553                 }554             }555             return ColsList;556         }557     }558 }

OleDbExcelHelper

网上搜集的常用类，这里不再测试。

方案四

将Excel另存为xml文件，对xml文件进行操作。

1 
     
       2     
      
       998
       3     
      
       柳雪巧
       4     
      
       f
       5     
      
       1971/4/30 0:00:00
       6     
      
       2005/1/15 0:00:00
       7     
      
       台湾省  屏东县
       8     
      
       Dolores19710430@139.com
       9     
      
       12616310511
      10     
      
       False
      11     
      
       2014/3/15 10:13:54
      12     
      
       5
      13

excel表格中每一行数据，其实是以上格式的xml，有规律，就可以很容易的去解析。

方案五

js插件

官网地址：

方案六

导出为csv文件

分享一个辅助类

1 using System.Data; 2 using System.IO; 3  4 public static class CsvHelper 5 { 6     ///  7     /// 导出报表为Csv 8     /// 
 9     /// DataTable10     /// 物理路径11     /// 表头12     /// 字段标题,逗号分隔13     public static bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)14     {15         try16         {17             string strBufferLine = "";18             StreamWriter strmWriterObj = new StreamWriter(strFilePath,false,System.Text.Encoding.UTF8);19             strmWriterObj.WriteLine(tableheader);20             strmWriterObj.WriteLine(columname);21             for (int i = 0; i < dt.Rows.Count; i++)22             {23                 strBufferLine = "";24                 for (int j = 0; j < dt.Columns.Count; j++)25                 {26                     if (j > 0)27                         strBufferLine += ",";28                     strBufferLine += dt.Rows[i][j].ToString();29                 }30                 strmWriterObj.WriteLine(strBufferLine);31             }32             strmWriterObj.Close();33             return true;34         }35         catch36         {37             return false;38         }39     }40 41     /// 42     /// 将Csv读入DataTable43     /// 
44     /// csv文件路径45     /// 表示第n行是字段title,第n+1行是记录开始46     public static DataTable csv2dt(string filePath, int n, DataTable dt)47     {48         StreamReader reader = new StreamReader(filePath, System.Text.Encoding.UTF8, false);49         int i = 0, m = 0;50         reader.Peek();51         while (reader.Peek() > 0)52         {53             m = m + 1;54             string str = reader.ReadLine();55             if (m >= n + 1)56             {57                 string[] split = str.Split(',');58 59                 System.Data.DataRow dr = dt.NewRow();60                 for (i = 0; i < split.Length; i++)61                 {62                     dr[i] = split[i];63                 }64                 dt.Rows.Add(dr);65             }66         }67         return dt;68     }69 }

View Code

不再测试。