[Asp.net]常见数据导入Excel，Excel数据导入数据库解决方案，总有一款适合你！-阿里云开发者社区

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

引言

项目中常用到将数据导入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。很久没研究过这玩意儿了。

案例

官网地址：http://npoi.codeplex.com/，可以从官网下载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
{
    /// <summary>
    /// 使用NPOI组件
    /// 需引入ICSharpCode.SharpZipLib.dll/NPOI.dll/NPOI.OOXML.dll/NPOI.OpenXml4Net.dll/NPOI.OpenXmlFormats.dll
    /// office2007
    /// </summary>
    public class NPOIExcelHelper
    {
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        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;

        }
        /// <summary>
        /// 将指定sheet中的数据导入到datatable中
        /// </summary>
        /// <param name="sheet">指定需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在的行号，-1没有列头</param>
        /// <param name="needHeader"></param>
        /// <returns></returns>
        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;
        }
        /// <summary>
        /// 将DataTable中的数据导入Excel文件中
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        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();
            }
        }
        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        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

参考：http://www.cnblogs.com/Joetao/articles/3247909.html

测试结果

导入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
{
    /// <summary>
    /// 使用com组件 操作Excel
    /// </summary>
    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;
        /// <summary>
        /// 以时间字符串作为保存文件的名称
        /// </summary>
        public string Timestamp
        {
            get { return timestamp; }
            set { timestamp = value; }
        }
        private object mValue = System.Reflection.Missing.Value;
        /// <summary>
        ///是否打开Excel界面 
        /// </summary>
        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);
        }
        /// <summary>
        /// 加载Excel
        /// </summary>
        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);
            }
        }
        /// <summary>
        /// 加载Excel
        /// </summary>
        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);
            }
        }
        /// <summary>
        /// 新建工作表
        /// </summary>
        /// <param name="sheetName"></param>
        public void NewWorkSheet(string sheetName)
        {
            sheet = workbook.Sheets.Add(workbook.Sheets[1], mValue, mValue, mValue);
            sheet.Name = sheetName;
        }
        /// <summary>
        /// 在指定的单元格插入指定的值
        /// </summary>
        /// <param name="strCell">单元格 如"A4"</param>
        /// <param name="objValue">文本 数字等值</param>
        public void WriteCell(string strCell, object objValue)
        {
            sheet.get_Range(strCell, mValue).Value2 = objValue;
        }
        /// <summary>
        /// 在指定Range中插入指定的值
        /// </summary>
        /// <param name="strStartCell">Range的开始单元格</param>
        /// <param name="strEndCell">Range的结束单元格</param>
        /// <param name="objValue">文本、数字等值</param>
        public void WriteRange(string strStartCell, string strEndCell, object objValue)
        {
            sheet.get_Range(strStartCell, strEndCell).Value2 = objValue;
        }
        /**/
        /// <summary>
        /// 合并单元格，并在合并后的单元格中插入指定的值
        /// </summary>
        /// <param name="strStartCell"></param>
        /// <param name="strEndCell"></param>
        /// <param name="objValue"></param>
        public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)
        {
            sheet.get_Range(strStartCell, strEndCell).Merge(mValue);
            sheet.get_Range(strStartCell, mValue).Value2 = objValue;
        }

        /**/
        /// <summary>
        /// 在连续单元格中插入一个DataTable中的值
        /// </summary>
        /// <param name="strStartCell">开始的单元格</param>
        /// <param name="dtData">存储数据的DataTable</param>
        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;
        }

        /**/
        /// <summary>
        /// 在连续单元格中插入一个DataTable并作超级链接
        /// </summary>
        /// <param name="strStartCell">起始单元格标识符</param>
        /// <param name="dtData">存储数据的DataTable</param>
        /// <param name="strLinkField">链接的地址字段</param>
        /// <param name="strTextField">链接的文本字段</param>
        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;
        }

        /**/
        /// <summary>
        /// 为单元格设置公式
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        /// <param name="strFormula">公式</param>
        public void SetFormula(string strCell, string strFormula)
        {
            sheet.get_Range(strCell, mValue).Formula = strFormula;
        }
        /**/
        /// <summary>
        /// 设置单元格或连续区域的字体为黑体
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        public void SetBold(string strCell)
        {
            sheet.get_Range(strCell, mValue).Font.Bold = true;
        }

        /**/
        /// <summary>
        /// 设置连续区域的字体为黑体
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        public void SetBold(string strStartCell, string strEndCell)
        {
            sheet.get_Range(strStartCell, strEndCell).Font.Bold = true;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域的字体颜色
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        /// <param name="clrColor">颜色</param>
        public void SetColor(string strCell, System.Drawing.Color clrColor)
        {
            sheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        }

        /**/
        /// <summary>
        /// 设置连续区域的字体颜色
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        /// <param name="clrColor">颜色</param>
        public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor)
        {
            sheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域的边框：上下左右都为黑色连续边框
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        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;


        }

        /**/
        /// <summary>
        /// 设置连续区域的边框：上下左右都为黑色连续边框
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        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;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域水平居左
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        public void SetHAlignLeft(string strCell)
        {
            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        }

        /**/
        /// <summary>
        /// 设置连续区域水平居左
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        public void SetHAlignLeft(string strStartCell, string strEndCell)
        {
            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域水平居左
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        public void SetHAlignCenter(string strCell)
        {
            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        }

        /**/
        /// <summary>
        /// 设置连续区域水平居中
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        public void SetHAlignCenter(string strStartCell, string strEndCell)
        {
            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域水平居右
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        public void SetHAlignRight(string strCell)
        {
            sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
        }

        /**/
        /// <summary>
        /// 设置连续区域水平居右
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        public void SetHAlignRight(string strStartCell, string strEndCell)
        {
            sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域的显示格式
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        /// <param name="strNF">如"#,##0.00"的显示格式</param>
        public void SetNumberFormat(string strCell, string strNF)
        {
            sheet.get_Range(strCell, mValue).NumberFormat = strNF;
        }

        /**/
        /// <summary>
        /// 设置连续区域的显示格式
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        /// <param name="strNF">如"#,##0.00"的显示格式</param>
        public void SetNumberFormat(string strStartCell, string strEndCell, string strNF)
        {
            sheet.get_Range(strStartCell, strEndCell).NumberFormat = strNF;
        }

        /**/
        /// <summary>
        /// 设置单元格或连续区域的字体大小
        /// </summary>
        /// <param name="strCell">单元格或连续区域标识符</param>
        /// <param name="intFontSize"></param>
        public void SetFontSize(string strCell, int intFontSize)
        {
            sheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();
        }

        /**/
        /// <summary>
        /// 设置连续区域的字体大小
        /// </summary>
        /// <param name="strStartCell">开始单元格标识符</param>
        /// <param name="strEndCell">结束单元格标识符</param>
        /// <param name="intFontSize">字体大小</param>
        public void SetFontSize(string strStartCell, string strEndCell, int intFontSize)
        {
            sheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString();
        }

        /**/
        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="strColID">列标识，如A代表第一列</param>
        /// <param name="decWidth">宽度</param>
        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;
        }

        /**/
        /// <summary>
        /// 为单元格添加超级链接
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        /// <param name="strAddress">链接地址</param>
        /// <param name="strTip">屏幕提示</param>
        /// <param name="strText">链接文本</param>
        public void AddHyperLink(string strCell, string strAddress, string strTip, string strText)
        {
            sheet.Hyperlinks.Add(sheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText);
        }

        /**/
        /// <summary>
        /// 已知开始的单元格标识，求intR行、intColumn列后的单元格标识
        /// </summary>
        /// <param name="strStartCell">开始单元格标识</param>
        /// <param name="intR">行数</param>
        /// <param name="intC">列数</param>
        /// <returns>单元格标识符结果</returns>
        public string GetEndCell(string strStartCell, int intR, int intC)
        {
            string endcell = string.Empty;
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\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;

        }

        /**/
        /// <summary>
        /// 获取单元格标识符中的字母
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        /// <returns>单元格标识符对应的字母</returns>
        public string GetCellLetter(string strCell)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
            return regex.Match(strCell).Result("${vLetter}");
        }

        /**/
        /// <summary>
        /// 获取单元格标识符中的数字
        /// </summary>
        /// <param name="strCell">单元格标识符</param>
        public int GetCellNumber(string strCell)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
            return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}"));
        }


        /**/
        /// <summary>
        /// 另存为xls文件
        /// </summary>
        /// <param name="strFilePath">文件路径</param>
        public void SaveAs(string strFilePath)
        {
            workbook.SaveCopyAs(strFilePath);

        }

        /**/
        /// <summary>
        /// 另存为xls文件
        /// </summary>
        /// <param name="strFilePath">文件路径</param>
        public void Save()
        {
            workbook.Save();

        }
        /// <summary>
        /// 将Excel中的数据 读入DataTable中
        /// </summary>
        /// <param name="filePath">excel文件的路径</param>
        /// <param name="dtName">datatable的名字</param>
        /// <param name="headName">表头，以逗号分隔</param>
        /// <returns></returns>
        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;
        }
        /**/
        /// <summary>
        /// 另存为html文件
        /// </summary>
        /// <param name="strFilePath">文件路径</param>
        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()
        {

        }






        /**/
        /// <summary>
        /// 字母转换为数字，Excel列头，如A-1;AA-27
        /// </summary>
        /// <param name="strLetter">字母</param>
        /// <returns>字母对应的数字</returns>
        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;

        }

        /**/
        /// <summary>
        /// 数字转换为字母，Excel列头，如1-A;27-AA
        /// </summary>
        /// <param name="intNumber">数字</param>
        /// <returns>数字对应的字母</returns>
        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

参考：http://www.cnblogs.com/waxdoll/archive/2005/10/28/264071.html

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         /// </summary> 
 20         /// 导出Excel文件，自动返回可下载的文件流 
 21         /// </summary> 
 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("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());
 41                 curContext.Response.End();
 42             }
 43         }
 44 
 45         /// <summary>
 46         /// 导出Excel文件，转换为可读模式
 47         /// </summary>
 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         /// <summary>
 73         /// 导出Excel文件，并自定义文件名
 74         /// </summary>
 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         /// <summary>
102         /// 将数据导出至Excel文件
103         /// </summary>
104         /// <param name="Table">DataTable对象</param>
105         /// <param name="ExcelFilePath">Excel文件路径</param>
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                 //如果列属于数字列，则设置该列的数据类型为double
150                 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                     else
161                     {
162                         TableStructStr += col.ColumnName + " double,";
163                     }
164                 }
165                 else
166                 {
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                     else
176                     {
177                         TableStructStr += col.ColumnName + " varchar,";
178                     }
179                 }
180                 i++;
181             }
182 
183             //创建Excel文件及文件结构
184             try
185             {
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                 else
214                 {
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                     else
232                     {
233                         para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
234                     }
235                 }
236                 try
237                 {
238                     objCmd.CommandText = InsertSql;
239                     objCmd.ExecuteNonQuery();
240                 }
241                 catch (Exception exp)
242                 {
243                     string str = exp.Message;
244                 }
245             }
246             try
247             {
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         /// <summary>
261         /// 将数据导出至Excel文件
262         /// </summary>
263         /// <param name="Table">DataTable对象</param>
264         /// <param name="Columns">要导出的数据列集合</param>
265         /// <param name="ExcelFilePath">Excel文件路径</param>
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                 try
287                 {
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                     else
344                     {
345                         TableStructStr += col.Caption.Trim() + " Double,";
346                     }
347                 }
348                 else
349                 {
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                     else
359                     {
360                         TableStructStr += col.Caption.Trim() + " VarChar,";
361                     }
362                 }
363             }
364 
365             //创建Excel文件及文件结构
366             try
367             {
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                 else
396                 {
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                     else
417                     {
418                         para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
419                     }
420                 }
421                 try
422                 {
423                     objCmd.CommandText = InsertSql;
424                     objCmd.ExecuteNonQuery();
425                 }
426                 catch (Exception exp)
427                 {
428                     string str = exp.Message;
429                 }
430             }
431             try
432             {
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         #endregion
445 
446         /// <summary>
447         /// 获取Excel文件数据表列表
448         /// </summary>
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                     try
458                     {
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         /// <summary>
483         /// 将Excel文件导出至DataTable(第一行作为表头)
484         /// </summary>
485         /// <param name="ExcelFilePath">Excel文件路径</param>
486         /// <param name="TableName">数据表名，如果数据表名错误，默认为第一个数据表名</param>
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             try
509             {
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             finally
521             {
522                 if (dbcon.State == ConnectionState.Open)
523                 {
524                     dbcon.Close();
525                 }
526             }
527             return table;
528         }
529 
530         /// <summary>
531         /// 获取Excel文件指定数据表的数据列表
532         /// </summary>
533         /// <param name="ExcelFileName">Excel文件名</param>
534         /// <param name="TableName">数据表名</param>
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 <Row>
 2     <Cell><Data ss:Type="String">998</Data></Cell>
 3     <Cell><Data ss:Type="String">柳雪巧</Data></Cell>
 4     <Cell><Data ss:Type="String">f</Data></Cell>
 5     <Cell><Data ss:Type="String">1971/4/30 0:00:00</Data></Cell>
 6     <Cell><Data ss:Type="String">2005/1/15 0:00:00</Data></Cell>
 7     <Cell><Data ss:Type="String">台湾省  屏东县</Data></Cell>
 8     <Cell><Data ss:Type="String">Dolores19710430@139.com</Data></Cell>
 9     <Cell><Data ss:Type="String">12616310511</Data></Cell>
10     <Cell><Data ss:Type="String">False</Data></Cell>
11     <Cell><Data ss:Type="String">2014/3/15 10:13:54</Data></Cell>
12     <Cell><Data ss:Type="String">5</Data></Cell>
13    </Row>

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

方案五

js插件

官网地址：http://datatables.net/extras/tabletools/

方案六

导出为csv文件

分享一个辅助类

 1 using System.Data;
 2 using System.IO;
 3 
 4 public static class CsvHelper
 5 {
 6     /// <summary>
 7     /// 导出报表为Csv
 8     /// </summary>
 9     /// <param name="dt">DataTable</param>
10     /// <param name="strFilePath">物理路径</param>
11     /// <param name="tableheader">表头</param>
12     /// <param name="columname">字段标题,逗号分隔</param>
13     public static bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)
14     {
15         try
16         {
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         catch
36         {
37             return false;
38         }
39     }
40 
41     /// <summary>
42     /// 将Csv读入DataTable
43     /// </summary>
44     /// <param name="filePath">csv文件路径</param>
45     /// <param name="n">表示第n行是字段title,第n+1行是记录开始</param>
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

不再测试。

方案七

使用模版的方式，最简单的模版，就是将表头列出，然后再导入数据。第一行为表头，从第二行开始写入数据。导入过程可参考前面的解决方案。

方案八

使用Aspose.Cells组件，貌似收费。

可参考：http://www.cnblogs.com/lanyue52011/p/3372452.html

http://www.cnblogs.com/hongjiumu/archive/2013/03/15/2962277.html

http://www.cnblogs.com/wujy/archive/2012/07/19/2600162.html

方案九

OpenXML

OpenXML库：DocumentFormat.OpenXml.dll；

参考：http://www.cnblogs.com/zlgcool/archive/2008/12/31/1365993.html

http://blog.sina.com.cn/s/blog_46e9573c01011bvs.html

总结

列出常见的几种解决方案，在实际项目中，用哪一种，还是根据项目环境来决定吧。

最近项目中用到了Excel导出，导入的功能，就想着将常见的方式总结一下。也许还有遗漏，请留言，将你知道也分享给大家，谢谢。

如果该文章对你有所帮助，不妨推荐一下，让更多的人知道，毕竟分享是件快乐的事情。

代码下载：链接：http://pan.baidu.com/s/1dD3lleT 密码：axli

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

引言

方案一

方案二

方案三

方案四

方案五

方案六

方案七

方案八

方案九

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件