NPOI 读取Excel中的数据,返回DataTable

/*
 * 由SharpDevelop创建。
 * 用户: Changweihua
 * 日期: 2013/6/4
 * 时间: 19:15
 *
 * changweihua@outlook.com
 * http://www.cmono.net
 *
 *
要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.IO;
namespace ImportExcelNPOI2
{
    class Program
    {
        static IWorkbook workBook;
                    
        public static void Main(string[] args)
        {
            DataTable dt = ImportExcelFile(@"C:\Users\Changweihua\Desktop\Grade.xls");
            if(dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Console.Write("{0} |", dt.Rows[i][j]);
                    }
                    Console.WriteLine("\n------------------------------------------");
                }
            }
                        
            Console.ReadKey(true);
        }
                    
        #region 
                    
                    
        public static DataTable ImportExcelFile(string filePath) 
        { 
            #region//初始化信息 
            try 
            { 
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 
                { 
                    workBook = WorkbookFactory.Create(file);
                    Console.WriteLine("{0}", workBook.NumberOfSheets);
                    //xssfworkbook = new XSSFWorkbook(file); 
                } 
            } 
            catch (Exception e) 
            { 
                Console.WriteLine(e.Message);
            } 
            #endregion 
            if(workBook == null)
            {
                Console.WriteLine("NULL");
                return null;
            }
            ISheet sheet = workBook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 
            DataTable dt = new DataTable(); 
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) 
            { 
                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); 
            } 
            while (rows.MoveNext()) 
            { 
                IRow row = (IRow)rows.Current;
                DataRow dr = dt.NewRow(); 
                for (int i = 0; i < row.LastCellNum; i++) 
                { 
                    ICell cell = row.GetCell(i); 
                    if (cell == null) 
                    { 
                        dr[i] = null; 
                    } 
                    else 
                    { 
                        dr[i] = cell.ToString(); 
                    } 
                } 
                dt.Rows.Add(dr); 
            } 
            return dt; 
        } 
        #endregion
    }
}

/*
 * 由SharpDevelop创建。
 * 用户: Changweihua
 * 日期: 2013/6/4
 * 时间: 18:51
 *
 * changweihua@outlook.com
 * http://www.cmono.net
 *
 *
要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace ImportExcel
{
    class Program
    {
        static HSSFWorkbook hssfworkbook;
           
        public static void Main(string[] args)
        {
            DataTable dt = ImportExcelFile(@"C:\Users\Changweihua\Desktop\Grade.xls");
            if(dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Console.Write("{0} |", dt.Rows[i][j]);
                    }
                    Console.WriteLine("\n------------------------------------------");
                }
            }
               
            Console.ReadKey(true);
        }
           
        #region 
           
           
        public static DataTable ImportExcelFile(string filePath) 
        { 
            #region//初始化信息 
            try 
            { 
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 
                { 
                    hssfworkbook = new HSSFWorkbook(file); 
                } 
            } 
            catch (Exception e) 
            { 
                Console.WriteLine(e.Message);
            } 
            #endregion 
            if(hssfworkbook == null)
            {
                Console.WriteLine("NULL");
                return null;
            }
            ISheet sheet = hssfworkbook.GetSheet("Sheet1");
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 
            DataTable dt = new DataTable(); 
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) 
            { 
                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); 
            } 
            while (rows.MoveNext()) 
            { 
                HSSFRow row = (HSSFRow)rows.Current; 
                DataRow dr = dt.NewRow(); 
                for (int i = 0; i < row.LastCellNum; i++) 
                { 
                    ICell cell = row.GetCell(i); 
                    if (cell == null) 
                    { 
                        dr[i] = null; 
                    } 
                    else 
                    { 
                        dr[i] = cell.ToString(); 
                    } 
                } 
                dt.Rows.Add(dr); 
            } 
            return dt; 
        } 
        #endregion
           
    }
}


知识共享许可协议
《NPOI 读取Excel中的数据,返回DataTable》常伟华 创作。
采用 知识共享 署名-相同方式共享 3.0 中国大陆 许可协议进行许可。
相邻依据:发表时间
  • 多说评论
  • 签名
  • 新浪微博
  • 默认评论
  • Tab Header 5

1 条评论 / 点击此处发表评论

Tab Content 5

开发技术


开发平台和工具

sitemap     163.14ms