程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> 常見數據導入Excel及Excel數據導入數據庫的解決方案

常見數據導入Excel及Excel數據導入數據庫的解決方案

編輯：關於.NET

項目中常用到將數據導入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

分享一個操作類：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
  
namespace Wolfy.Common
{
    class OleDbExcelHelper
    {
        #region 數據導出至Excel文件
        /// </summary> 
        /// 導出Excel文件，自動返回可下載的文件流 
        /// </summary> 
        public static void DataTable1Excel(System.Data.DataTable dtData)
        {
            GridView gvExport = null;
            HttpContext curContext = HttpContext.Current;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;
            if (dtData != null)
            {
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                curContext.Response.Charset = "utf-8";
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);
                gvExport = new GridView();
                gvExport.DataSource = dtData.DefaultView;
                gvExport.AllowPaging = false;
                gvExport.DataBind();
                gvExport.RenderControl(htmlWriter);
                curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());
                curContext.Response.End();
            }
        }
  
        /// <summary>
        /// 導出Excel文件，轉換為可讀模式
        /// </summary>
        public static void DataTable2Excel(System.Data.DataTable dtData)
        {
            DataGrid dgExport = null;
            HttpContext curContext = HttpContext.Current;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;
  
            if (dtData != null)
            {
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
                curContext.Response.Charset = "";
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);
                dgExport = new DataGrid();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }
  
        /// <summary>
        /// 導出Excel文件，並自定義文件名
        /// </summary>
        public static void DataTable3Excel(System.Data.DataTable dtData, String FileName)
        {
            GridView dgExport = null;
            HttpContext curContext = HttpContext.Current;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;
  
            if (dtData != null)
            {
                HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
                curContext.Response.ContentType = "application nd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
                curContext.Response.Charset = "GB2312";
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);
                dgExport = new GridView();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }
  
        /// <summary>
        /// 將數據導出至Excel文件
        /// </summary>
        /// <param name="Table">DataTable對象</param>
        /// <param name="ExcelFilePath">Excel文件路徑</param>
        public static bool OutputToExcel(System.Data.DataTable Table, string ExcelFilePath)
        {
            if (File.Exists(ExcelFilePath))
            {
                throw new Exception("該文件已經存在！");
            }
  
            if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
            {
                Table.TableName = "Sheet1";
            }
  
            //數據表的列數
            int ColCount = Table.Columns.Count;
  
            //用於記數，實例化參數時的序號
            int i = 0;
  
            //創建參數
            OleDbParameter[] para = new OleDbParameter[ColCount];
  
            //創建表結構的SQL語句
            string TableStructStr = @"Create Table " + Table.TableName + "(";
  
            //連接字符串
            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(connString);
  
            //創建表結構
            OleDbCommand objCmd = new OleDbCommand();
  
            //數據類型集合
            ArrayList DataTypeList = new ArrayList();
            DataTypeList.Add("System.Decimal");
            DataTypeList.Add("System.Double");
            DataTypeList.Add("System.Int16");
            DataTypeList.Add("System.Int32");
            DataTypeList.Add("System.Int64");
            DataTypeList.Add("System.Single");
  
            //遍歷數據表的所有列，用於創建表結構
            foreach (DataColumn col in Table.Columns)
            {
                //如果列屬於數字列，則設置該列的數據類型為double
                if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
                {
                    para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
                    objCmd.Parameters.Add(para[i]);
  
                    //如果是最後一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += col.ColumnName + " double)";
                    }
                    else
                    {
                        TableStructStr += col.ColumnName + " double,";
                    }
                }
                else
                {
                    para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
                    objCmd.Parameters.Add(para[i]);
  
                    //如果是最後一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += col.ColumnName + " varchar)";
                    }
                    else
                    {
                        TableStructStr += col.ColumnName + " varchar,";
                    }
                }
                i++;
            }
  
            //創建Excel文件及文件結構
            try
            {
                objCmd.Connection = objConn;
                objCmd.CommandText = TableStructStr;
  
                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                }
                objCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
  
            //插入記錄的SQL語句
            string InsertSql_1 = "Insert into " + Table.TableName + " (";
            string InsertSql_2 = " Values (";
            string InsertSql = "";
  
            //遍歷所有列，用於插入記錄，在此創建插入記錄的SQL語句
            for (int colID = 0; colID < ColCount; colID++)
            {
                if (colID + 1 == ColCount)  //最後一列
                {
                    InsertSql_1 += Table.Columns[colID].ColumnName + ")";
                    InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
                }
                else
                {
                    InsertSql_1 += Table.Columns[colID].ColumnName + ",";
                    InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
                }
            }
  
            InsertSql = InsertSql_1 + InsertSql_2;
  
            //遍歷數據表的所有數據行
            for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
            {
                for (int colID = 0; colID < ColCount; colID++)
                {
                    if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
                    {
                        para[colID].Value = 0;
                    }
                    else
                    {
                        para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
                    }
                }
                try
                {
                    objCmd.CommandText = InsertSql;
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    string str = exp.Message;
                }
            }
            try
            {
                if (objConn.State == ConnectionState.Open)
                {
                    objConn.Close();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return true;
        }
  
        /// <summary>
        /// 將數據導出至Excel文件
        /// </summary>
        /// <param name="Table">DataTable對象</param>
        /// <param name="Columns">要導出的數據列集合</param>
        /// <param name="ExcelFilePath">Excel文件路徑</param>
        public static bool OutputToExcel(System.Data.DataTable Table, ArrayList Columns, string ExcelFilePath)
        {
            if (File.Exists(ExcelFilePath))
            {
                throw new Exception("該文件已經存在！");
            }
  
            //如果數據列數大於表的列數，取數據表的所有列
            if (Columns.Count > Table.Columns.Count)
            {
                for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)
                {
                    Columns.RemoveAt(s);   //移除數據表列數後的所有列
                }
            }
  
            //遍歷所有的數據列，如果有數據列的數據類型不是 DataColumn，則將它移除
            DataColumn column = new DataColumn();
            for (int j = 0; j < Columns.Count; j++)
            {
                try
                {
                    column = (DataColumn)Columns[j];
                }
                catch (Exception)
                {
                    Columns.RemoveAt(j);
                }
            }
            if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
            {
                Table.TableName = "Sheet1";
            }
  
            //數據表的列數
            int ColCount = Columns.Count;
  
            //創建參數
            OleDbParameter[] para = new OleDbParameter[ColCount];
  
            //創建表結構的SQL語句
            string TableStructStr = @"Create Table " + Table.TableName + "(";
  
            //連接字符串
            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(connString);
  
            //創建表結構
            OleDbCommand objCmd = new OleDbCommand();
  
            //數據類型集合
            ArrayList DataTypeList = new ArrayList();
            DataTypeList.Add("System.Decimal");
            DataTypeList.Add("System.Double");
            DataTypeList.Add("System.Int16");
            DataTypeList.Add("System.Int32");
            DataTypeList.Add("System.Int64");
            DataTypeList.Add("System.Single");
  
            DataColumn col = new DataColumn();
  
            //遍歷數據表的所有列，用於創建表結構
            for (int k = 0; k < ColCount; k++)
            {
                col = (DataColumn)Columns[k];
  
                //列的數據類型是數字型
                if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
                {
                    para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
                    objCmd.Parameters.Add(para[k]);
  
                    //如果是最後一列
                    if (k + 1 == ColCount)
                    {
                        TableStructStr += col.Caption.Trim() + " Double)";
                    }
                    else
                    {
                        TableStructStr += col.Caption.Trim() + " Double,";
                    }
                }
                else
                {
                    para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
                    objCmd.Parameters.Add(para[k]);
  
                    //如果是最後一列
                    if (k + 1 == ColCount)
                    {
                        TableStructStr += col.Caption.Trim() + " VarChar)";
                    }
                    else
                    {
                        TableStructStr += col.Caption.Trim() + " VarChar,";
                    }
                }
            }
  
            //創建Excel文件及文件結構
            try
            {
                objCmd.Connection = objConn;
                objCmd.CommandText = TableStructStr;
  
                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                }
                objCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
  
            //插入記錄的SQL語句
            string InsertSql_1 = "Insert into " + Table.TableName + " (";
            string InsertSql_2 = " Values (";
            string InsertSql = "";
  
            //遍歷所有列，用於插入記錄，在此創建插入記錄的SQL語句
            for (int colID = 0; colID < ColCount; colID++)
            {
                if (colID + 1 == ColCount)  //最後一列
                {
                    InsertSql_1 += Columns[colID].ToString().Trim() + ")";
                    InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
                }
                else
                {
                    InsertSql_1 += Columns[colID].ToString().Trim() + ",";
                    InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
                }
            }
  
            InsertSql = InsertSql_1 + InsertSql_2;
  
            //遍歷數據表的所有數據行
            DataColumn DataCol = new DataColumn();
            for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
            {
                for (int colID = 0; colID < ColCount; colID++)
                {
                    //因為列不連續，所以在取得單元格時不能用行列編號，列需得用列的名稱
                    DataCol = (DataColumn)Columns[colID];
                    if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
                    {
                        para[colID].Value = 0;
                    }
                    else
                    {
                        para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
                    }
                }
                try
                {
                    objCmd.CommandText = InsertSql;
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    string str = exp.Message;
                }
            }
            try
            {
                if (objConn.State == ConnectionState.Open)
                {
                    objConn.Close();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return true;
        }
        #endregion
  
        /// <summary>
        /// 獲取Excel文件數據表列表
        /// </summary>
        public static ArrayList GetExcelTables(string ExcelFileName)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            ArrayList TablesList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }
  
                    //獲取數據表個數
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }
  
        /// <summary>
        /// 將Excel文件導出至DataTable(第一行作為表頭)
        /// </summary>
        /// <param name="ExcelFilePath">Excel文件路徑</param>
        /// <param name="TableName">數據表名，如果數據表名錯誤，默認為第一個數據表名</param>
        public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
        {
            if (!File.Exists(ExcelFilePath))
            {
                throw new Exception("Excel文件不存在！");
            }
  
            //如果數據表名不存在，則數據表名為Excel文件的第一個數據表
            ArrayList TableList = new ArrayList();
            TableList = GetExcelTables(ExcelFilePath);
  
            if (TableName.IndexOf(TableName) < 0)
            {
                TableName = TableList[0].ToString().Trim();
            }
  
            DataTable table = new DataTable();
            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
            OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
  
            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                adapter.Fill(table);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (dbcon.State == ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
            return table;
        }
  
        /// <summary>
        /// 獲取Excel文件指定數據表的數據列表
        /// </summary>
        /// <param name="ExcelFileName">Excel文件名</param>
        /// <param name="TableName">數據表名</param>
        public static ArrayList GetExcelTableColumns(string ExcelFileName, string TableName)
        {
            DataTable dt = new DataTable();
            ArrayList ColsList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    conn.Open();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
  
                    //獲取列個數
                    int colcount = dt.Rows.Count;
                    for (int i = 0; i < colcount; i++)
                    {
                        string colname = dt.Rows[i]["Column_Name"].ToString().Trim();
                        ColsList.Add(colname);
                    }
                }
            }
            return ColsList;
        }
    }
}
  
OleDbExcelHelper

網上搜集的常用類，這裡不再測試。

方案四

將Excel另存為xml文件，對xml文件進行操作。

<Row>
    <Cell><Data ss:Type="String">998</Data></Cell>
    <Cell><Data ss:Type="String">柳雪巧</Data></Cell>
    <Cell><Data ss:Type="String">f</Data></Cell>
    <Cell><Data ss:Type="String">1971/4/30 0:00:00</Data></Cell>
    <Cell><Data ss:Type="String">2005/1/15 0:00:00</Data></Cell>
    <Cell><Data ss:Type="String">台灣省  屏東縣</Data></Cell>
    <Cell><Data ss:Type="String">Dolores19710430@139.com</Data></Cell>
    <Cell><Data ss:Type="String">12616310511</Data></Cell>
    <Cell><Data ss:Type="String">False</Data></Cell>
    <Cell><Data ss:Type="String">2014/3/15 10:13:54</Data></Cell>
    <Cell><Data ss:Type="String">5</Data></Cell>
   </Row>

excel表格中每一行數據，其實是以上格式的xml，有規律，就可以很容易的去解析。

方案五

js插件

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

方案六

導出為csv文件

分享一個輔助類

using System.Data;
using System.IO;
  
public static class CsvHelper
{
    /// <summary>
    /// 導出報表為Csv
    /// </summary>
    /// <param name="dt">DataTable</param>
    /// <param name="strFilePath">物理路徑</param>
    /// <param name="tableheader">表頭</param>
    /// <param name="columname">字段標題,逗號分隔</param>
    public static bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)
    {
        try
        {
            string strBufferLine = "";
            StreamWriter strmWriterObj = new StreamWriter(strFilePath,false,System.Text.Encoding.UTF8);
            strmWriterObj.WriteLine(tableheader);
            strmWriterObj.WriteLine(columname);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strBufferLine = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j > 0)
                        strBufferLine += ",";
                    strBufferLine += dt.Rows[i][j].ToString();
                }
                strmWriterObj.WriteLine(strBufferLine);
            }
            strmWriterObj.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
  
    /// <summary>
    /// 將Csv讀入DataTable
    /// </summary>
    /// <param name="filePath">csv文件路徑</param>
    /// <param name="n">表示第n行是字段title,第n+1行是記錄開始</param>
    public static DataTable csv2dt(string filePath, int n, DataTable dt)
    {
        StreamReader reader = new StreamReader(filePath, System.Text.Encoding.UTF8, false);
        int i = 0, m = 0;
        reader.Peek();
        while (reader.Peek() > 0)
        {
            m = m + 1;
            string str = reader.ReadLine();
            if (m >= n + 1)
            {
                string[] split = str.Split(',');
  
                System.Data.DataRow dr = dt.NewRow();
                for (i = 0; i < split.Length; i++)
                {
                    dr[i] = split[i];
                }
                dt.Rows.Add(dr);
            }
        }
        return dt;
    }
}

不再測試。方案七

使用模版的方式，最簡單的模版，就是將表頭列出，然後再導入數據。第一行為表頭，從第二行開始寫入數據。導入過程可參考前面的解決方案。方案八

使用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 方案十

Epplus操作Excel2007、2010

參考：http://www.cnblogs.com/xinchen/archive/2012/02/15/2352920.html