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;
        }