用NPOI从Excel到DataTable

最新推荐文章于 2024-11-12 10:56:30 发布

weixin_30788239

最新推荐文章于 2024-11-12 10:56:30 发布

阅读量77

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/senlinzhang/p/8608708.html

版权

NPOI功能强大，不用装Excel，就可以操作表格中数据----Excel.Sheet------>DataTable

 private IWorkbook workbook = null;
        private ISheet sheet = null;
        private string fileName = "";//文档路径
        private FileStream fs = null;

        public ExcelHelper()
        {
        }
        //构造函数
        public ExcelHelper(string file)
        {
            this.fileName = file;
        }
        /// <summary>
        /// 用NPOI从Excel到DatTable
        /// </summary>
        /// <returns></returns>
        public DataTable ExcelToDataTable()
        {
            DataTable dt = new DataTable();
            fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (System.IO.Path.GetExtension(fileName) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (System.IO.Path.GetExtension(fileName) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                throw new Exception("文件类型错误");
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);

                //表头
                for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        DataColumn column = new DataColumn(cell.StringCellValue);
                        dt.Columns.Add(column);
                    }
                }
                //表数据 Access数据库时，加“等号”-j <= sheet.LastRowNum 
                for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
                {
                    IRow row = sheet.GetRow(j);
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dataRow = dt.NewRow();
                    for (int i = row.FirstCellNum; i < firstRow.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        //加日期处理
                        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                        {
                            dataRow[i] = cell.DateCellValue.ToString();
                        }
                        else
                        {
                            dataRow[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dataRow);
                }
            }
            return dt;
        }