NPOI将xls文件解析为DataTable类数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;

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

namespace ExcelDataExtractor
{
    static class TableDataParser
    {
        static IWorkbook hssfworkbook;  
        static public DataTable ImportExcelFile(string filePath)  
        {  
            #region//初始化信息  
            try  
            {                
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))  
                {  
                    hssfworkbook = new HSSFWorkbook(file);  
                }  
            }  
            catch (Exception e)  
            {
                MessageBox.Show("error:"+e.Message+"\n");
                return (DataTable)null;
            }  
            #endregion  
  
            ISheet sheet = hssfworkbook.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 = (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;  
        }  
    }
}

 

posted @ 2014-03-03 23:40  摇滚的双桨  阅读(299)  评论(0编辑  收藏  举报