Ticket/Tools/ExcelHelper.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using Ticket.Model.Models;
using NPOI;
using NPOI.HSSF.UserModel;
using System.IO;
using Microsoft.Ajax.Utilities;

namespace Gym.Model.Tools
{
    public class ExcelHelper
    {
        public static System.Data.DataTable GetExcelDatatable(string fileUrl)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.4.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));

            //打开连接
            if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            //获取Excel的第一个Sheet名称
            string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

            //查询sheet中的数据
            string strSql = "select * from [" + sheetName + "]";
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0];

            return dt;


        }

        public static IEnumerable<TrainNumber> GetEnittiesFromExcel(string filePath)
        {
            FileStream fs = File.Open(filePath, FileMode.Open);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            var sheet = workbook.GetSheetAt(0);
            var list = new List<TrainNumber>();
            if (sheet != null)
            {
                var firstRowNum = sheet.FirstRowNum;
                var lastRowNum = sheet.LastRowNum;
                for (var i = firstRowNum + 1; i <= lastRowNum; i++)