NPOI 读取 excel通用类

来源：互联网发布：天工造价软件下载编辑：程序博客网时间：2024/05/17 22:31
using System;using System.Collections.Generic;using System.Text;using System.IO;using System.Data;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;namespace NPOI2{    /// <summary>    /// Excel读取类（将excel数据转换成DataTable/DataSet返回)       /// </summary>    public class ExcelReader    {        HSSFWorkbook hssfworkbook;        public ExcelReader(string xlsPath)        {            InitializeWorkbook(xlsPath);        }        void InitializeWorkbook(string path)        {            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.             using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new HSSFWorkbook(file);            }        }        /// <summary>        /// 将excel的数据转换成dataset返回        /// </summary>        /// <returns></returns>        public DataSet ReadAllSheet()        {            DataSet ds = new DataSet();            for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)            {                ds.Tables.Add(ConvertToDataTable(i));            }            return ds;        }        /// <summary>        /// 将第sheetIndex个工作簿转换成DataTable        /// </summary>        /// <param name="sheetIndex">工作簿索引</param>        /// <returns></returns>        public DataTable ConvertToDataTable(int sheetIndex)        {            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            DataTable dt = initDT();            while (rows.MoveNext())            {                IRow row = (HSSFRow)rows.Current;                DataRow dr = dt.NewRow();                for (int i = 0; i < row.LastCellNum && i < 255; i++)                {                    ICell cell = row.GetCell(i);                    if (cell == null)                    {                        dr[i] = null;                    }                    else                    {                        dr[i] = cell.ToString();                    }                }                dt.Rows.Add(dr);            }            return dt;        }        /// <summary>        /// 构造一个255列的空表        /// </summary>        /// <returns></returns>        private DataTable initDT()        {            DataTable dt = new DataTable();            for (int j = 0; j < 255; j++)            {                dt.Columns.Add("F" + (j + 1).ToString());            }            return dt;        }    }}
如果您觉得好用，请进入下面的网址，付费支持作者 ~
https://me.alipay.com/tommysz
谢谢！
注：需要引用NPOI.dll
0 0