C# NPOI操作Excel（上）

NPOI 官網下載DLL：http://npoi.codeplex.com/releases

1、讀取Excel轉為DataTable

        /// <summary>
        /// 讀取excel轉為DataTable
        /// </summary>
        /// <param name="fileName">文件路徑</param>
        /// <param name="sheetName">指定sheet</param>
        /// <param name="isColumnName">第一行是否為列名</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string fileName, string sheetName, bool isColumnName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            //初始化開始行
            int startRow = 0;
            DataTable dt = new DataTable();
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            //低於2007版本
            if (Path.GetExtension(fileName) == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            //2007及以上版本
            else if (Path.GetExtension(fileName) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            //判斷是否指定sheet上傳
            if (sheetName != null)
            {
                //獲取指定sheet
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                {
                    //獲取不到時取第一個sheet
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //sheet中第一行
                IRow firstRow = sheet.GetRow(0);
                //判斷第一行是否是列名
                if (isColumnName)
                {
                    //遍歷第一行的單元格
                    for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
                    {
                        //得到列名
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            //得到列名的值,若列名不是字元則不能使用StringCellValue，最好使用ToString()
                            string cellValue = cell.ToString();
                            if (cellValue != null)
                            {
                                try
                                {
                                    //將列放入datatable中
                                    DataColumn column = new DataColumn(cellValue);
                                    dt.Columns.Add(column);
                                }
                                catch
                                {
                                    throw new Exception("列名有誤！");
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                }
                //遍歷所有行
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {      
                    //得到i行
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    //datatable新增行
                    DataRow dr = dt.NewRow();
                    //遍歷i行的單元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    try
                    {
                        //將行放入datatable中
                        dt.Rows.Add(dr);
                    }
                    catch
                    {
                        throw new Exception("第" + i + "行有誤！");
                    }
                }
            }
            return dt;
        }