readfromexcel.cs

一个信息管理系统,负责管理一个建筑公司的所有基本资料.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace SPQC.Business.DataImport
{
    public class ReadFromExcel : IReadBaseInfo
    {
        private string filePath;//Excel文件路径
        private int count = 0;//记录无效记录
        private IList<Category> categoryList = new List<Category>();///资料名称等信息列表
        public IList<Category> getCategoryList()
        {
            return categoryList;
        }
        public ReadFromExcel(string filePath)
        {
            this.filePath = filePath;
        }
        public int getCount()
        {
            return count;
        }
        #region IReadBaseInfo 成员
        /// <summary>
        /// 从excel表中读取资料名称等信息
        /// </summary>
        /// <returns></returns>
        public void readCategoryInfo()
        {            
            string strCon = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
            using (OleDbConnection conn = new OleDbConnection(strCon))
            {
                OleDbCommand com = null;
                try
                {
                    conn.Open();
                    com = new OleDbCommand("select * from [Sheet1$]", conn);
                    OleDbDataReader odr = com.ExecuteReader();
                    while (odr.Read())
                    {
                        if (validateInput(Convert.ToString(odr.GetValue(0))) || validateInput(Convert.ToString(odr.GetValue(1))))
                        {
                            count++;
                            continue;
                        }
                        try
                        {
                            string categoryCode = Convert.ToString(odr.GetValue(0));
                            string datumName = Convert.ToString(odr.GetValue(1));
                            long beginNumber = (string.IsNullOrEmpty(Convert.ToString(odr.GetValue(2))) ? 0 : Convert.ToInt64(odr.GetValue(2)));
                            long endNumber = (string.IsNullOrEmpty(Convert.ToString(odr.GetValue(3))) ? 0: Convert.ToInt64(odr.GetValue(3)));
                            int gridId = (string.IsNullOrEmpty(Convert.ToString(odr.GetValue(4))) ? 0 : Convert.ToInt32(odr.GetValue(4)));
                            int boxId = (string.IsNullOrEmpty(Convert.ToString(odr.GetValue(5))) ? 0 : Convert.ToInt32(odr.GetValue(5)));
                            string createDate = Convert.ToString(odr.GetValue(6));
                            DateTime gdDate=DateTime.Now.Date;
                            try
                            {
                                gdDate = Convert.ToDateTime(odr.GetValue(7));
                            }
                            catch
                            {
                                
                            }
                            categoryList.Add(new Category(categoryCode, datumName, beginNumber, endNumber, gridId, boxId, createDate, gdDate));
                        }
                        catch
                        {
                            count++;
                            continue;
                        }
                    }
                    odr.Close();
                }
                catch (OleDbException)
                {
                    throw new Exception("您提供的文件无法访问！");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 验证字符串是否为空，返回true为空，返回false不为空
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private bool validateInput(string input)
        {
            return (string.IsNullOrEmpty(input.Trim()) || (input == ""));
        }
        #endregion
    }
}