C#下使用第三方開原始檔控制讀取Excel檔案的內容

使用了兩個開原始檔控制讀取Excel檔案的內容,不需要安裝Excel或Office,開發環境可能需要vs2008(2005沒測試過)

NPOI,       讀取xls檔案(Excel2003及之前的版本)   (NPOI.dll+Ionic.Zip.dll)     http://npoi.codeplex.com/

EPPlus,    讀取xlsx檔案(Excel2007版本)                (EPPlus.dll)                       http://epplus.codeplex.com/

本文中只實現了Excel檔案的讀取,實際上,這兩個控制項均支援對其內容,格式,公式等進行修改,這些複雜功能尚無需求,所以沒有實現

讀取介面IExcel:

public interface IExcel    {        /// <summary> 開啟檔案 </summary>        bool Open();        /// <summary> 檔案版本 </summary>        ExcelVersion Version { get; }        /// <summary> 檔案路徑 </summary>        string FilePath { get; set; }        /// <summary> 檔案是否已經開啟 </summary>        bool IfOpen { get;  }        /// <summary> 檔案包含工作表的數量 </summary>        int SheetCount { get; }        /// <summary> 當前工作表序號 </summary>        int CurrentSheetIndex { get; set; }        /// <summary> 擷取當前工作表中行數 </summary>        int GetRowCount();        /// <summary> 擷取當前工作表中列數 </summary>        int GetColumnCount();        /// <summary> 擷取當前工作表中某一行中儲存格的數量 </summary>        /// <param name="Row">行序號</param>        int GetCellCountInRow(int Row);        /// <summary> 擷取當前工作表中某一儲存格的值（按字串返回） </summary>        /// <param name="Row">行序號</param>        /// <param name="Col">列序號</param>        string GetCellValue(int Row, int Col);        /// <summary> 關閉檔案 </summary>        void Close();    }public enum ExcelVersion    {        /// <summary> Excel2003之前版本 ,xls </summary>        Excel03,        /// <summary> Excel2007版本 ,xlsx  </summary>        Excel07    }

xls檔案實現:

using NPOI.HSSF.UserModel;public class Excel03:IExcel    {        public Excel03()        { }        public Excel03(string path)        { filePath = path; }        private FileStream file = null;        private string filePath = "";        private HSSFWorkbook book = null;        private int sheetCount=0;        private bool ifOpen = false;        private int currentSheetIndex = 0;        private HSSFSheet currentSheet = null;                     public string FilePath        {            get  { return filePath; }            set { filePath = value; }        }        public bool Open()        {            try            {                file = new FileStream(filePath, FileMode.Open, FileAccess.Read);                book= new HSSFWorkbook(file);                if (book == null) return false;                sheetCount = book.NumberOfSheets;                currentSheetIndex = 0;                currentSheet = (HSSFSheet)book.GetSheetAt(0);                ifOpen = true;            }            catch (Exception ex)            {                throw new Exception("開啟檔案失敗，詳細資料：" + ex.Message);            }            return true;        }        public void  Close()        {            if (!ifOpen) return;            file.Close();        }        public ExcelVersion Version        { get { return ExcelVersion.Excel03; } }        public bool IfOpen        { get { return ifOpen; } }        public int SheetCount        { get { return sheetCount; } }        public int CurrentSheetIndex        {            get { return currentSheetIndex; }            set             {                if (value != currentSheetIndex)                {                    if (value >= sheetCount)                        throw new Exception("工作表序號超出範圍");                    currentSheetIndex = value;                    currentSheet = (HSSFSheet)book.GetSheetAt(currentSheetIndex);                }            }        }        public int GetRowCount()        {            if (currentSheet == null) return 0;            return currentSheet.LastRowNum + 1;                 }        public int GetColumnCount()        {            if (currentSheet == null) return 0;            int colCount = 0;            for (int i = 0; i <= currentSheet.LastRowNum; i++)            {                if (currentSheet.GetRow(i) != null && currentSheet.GetRow(i).LastCellNum+1 > colCount)                    colCount = currentSheet.GetRow(i).LastCellNum + 1;            }            return colCount;        }        public int GetCellCountInRow(int Row)        {            if (currentSheet == null) return 0;            if (Row > currentSheet.LastRowNum) return 0;            if (currentSheet.GetRow(Row) == null) return 0;            return currentSheet.GetRow(Row).LastCellNum+1;        }        public string GetCellValue(int Row, int Col)        {            if (Row > currentSheet.LastRowNum) return "";            if (currentSheet.GetRow(Row) == null) return "";             HSSFRow r = (HSSFRow)currentSheet.GetRow(Row);             if (Col > r.LastCellNum) return "";             if (r.GetCell(Col) == null) return "";             return r.GetCell(Col).StringCellValue;                    }    }

xlsx檔案實現:

using OfficeOpenXml;public class Excel07:IExcel    {         public Excel07()        { }        public Excel07(string path)        { filePath = path; }        private string filePath = "";        private ExcelWorkbook book = null;        private int sheetCount = 0;        private bool ifOpen = false;        private int currentSheetIndex = 0;        private ExcelWorksheet currentSheet = null;        private ExcelPackage ep = null;        public bool Open()        {            try            {                ep = new ExcelPackage(new FileInfo(filePath));                                if (ep == null) return false;                book =ep.Workbook;                sheetCount = book.Worksheets.Count;                currentSheetIndex = 0;                currentSheet = book.Worksheets[1];                ifOpen = true;            }            catch (Exception ex)            {                throw new Exception("開啟檔案失敗，詳細資料：" + ex.Message);            }            return true;        }        public void Close()        {            if (!ifOpen || ep == null) return;            ep.Dispose();        }        public ExcelVersion Version        { get { return ExcelVersion.Excel07; } }        public string FilePath        {            get { return filePath; }            set { filePath = value; }        }        public bool IfOpen        { get { return ifOpen; } }        public int SheetCount        { get { return sheetCount; } }        public int CurrentSheetIndex        {            get  { return currentSheetIndex; }            set            {                if (value != currentSheetIndex)                {                    if (value >= sheetCount)                        throw new Exception("工作表序號超出範圍");                    currentSheetIndex = value;                    currentSheet =book.Worksheets[currentSheetIndex+1];                }            }        }        public int GetRowCount()        {            if (currentSheet == null) return 0;            return currentSheet.Dimension.End.Row;        }        public int GetColumnCount()        {            if (currentSheet == null) return 0;            return currentSheet.Dimension.End.Column;        }        public int GetCellCountInRow(int Row)        {            if (currentSheet == null) return 0;            if (Row >= currentSheet.Dimension.End.Row) return 0;            return currentSheet.Dimension.End.Column;        }        public string GetCellValue(int Row, int Col)        {            if (currentSheet == null) return "";            if (Row >= currentSheet.Dimension.End.Row || Col >= currentSheet.Dimension.End.Column) return "";            object tmpO =currentSheet.GetValue(Row + 1, Col + 1);            if (tmpO == null) return "";            return tmpO.ToString();        }            }

調用類:

 public class ExcelLib    {        /// <summary> 擷取Excel對象 </summary>        /// <param name="filePath">Excel檔案路徑</param>        /// <returns></returns>        public static IExcel GetExcel(string filePath)        {            if (filePath.Trim() == "")                 throw new Exception("檔案名稱不可為空");            if(!filePath.Trim().EndsWith("xls") && !filePath.Trim().EndsWith("xlsx"))                throw new Exception("不支援該檔案類型");            if (filePath.Trim().EndsWith("xls"))            {                IExcel res = new Excel03(filePath.Trim());                return res;            }            else if (filePath.Trim().EndsWith("xlsx"))            {                IExcel res = new Excel07(filePath.Trim());                return res;            }            else return null;        }    }

程式中調用:

ExcelLib.IExcel tmp = ExcelLib.ExcelLib.GetExcel(Application.StartupPath + "\\TestUnicodeChars.xls");//ExcelLib.IExcel tmp = ExcelLib.ExcelLib.GetExcel(Application.StartupPath + "\\TestUnicodeChars.xlsx");if (tmp == null) MessageBox.Show("開啟檔案錯誤"); try { if (!tmp.Open()) MessageBox.Show("開啟檔案錯誤"); tmp.CurrentSheetIndex = 1; int asdf = tmp.GetColumnCount(); string sdf = tmp.GetCellValue(0,1); tmp.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); return ; }