C# DataTable与Excel读取与导出

 /// <summary>
        /// Excel->DataTable
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        public static DataTable ReadExcel(string filePath)
        {
            IWorkbook iwkX;
            using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                iwkX = WorkbookFactory.Create(fs);
                fs.Close();
            }
            //sheet
            DataTable dt = new DataTable();
            for (int h = 0; h < iwkX.NumberOfSheets; h++)
            {
                ISheet sheet = iwkX.GetSheetAt(h);
                var rows = sheet.GetRowEnumerator();
                bool isMove = rows.MoveNext();
                //循环sheet
                if (isMove)
                {
                    var Cols = (IRow)rows.Current;
                    dt.TableName = sheet.SheetName;
                    for (int i = 0; i < Cols.LastCellNum; i++)
                    {
                        string str = Cols.GetCell(i).ToString();
                        dt.Columns.Add(Cols.GetCell(i).ToString());
                    }
                    while (rows.MoveNext())
                    {
                        var row = (IRow)rows.Current;
                        var dr = dt.NewRow();
                        for (int i = 0; i < row.LastCellNum; i++)
                        {
                            var cell = row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = "";
                            }
                            else
                            {
                                string strdr = cell.ToString();
                                dr[i] = cell.ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="array">首行数组</param>
        /// <param name="fileFullName">文件名</param>
        public static void ExportToExcel(DataTable dt, string[] array, string fileFullName)
        {
            #region
            XSSFWorkbook workbook = new XSSFWorkbook();
            FileStream fs = new FileStream(fileFullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            ISheet sheet = workbook.CreateSheet("Sheet0");
            IRow row;
            row = sheet.CreateRow(0);

            for (int i = 0; i < array.Length; i++)
            {
                row.CreateCell(i).SetCellValue(array[i]);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            workbook.Write(fs); //写入到excel

            #endregion
            // MessageBox.Show("导出成功！");
        }

 

/// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="array">首行数组</param>    
        /// <param name="fileFullName">文件名</param>
        public static void ExportToExcel(DataTable dt, string fileFullName)
        {
            #region
            XSSFWorkbook workbook = new XSSFWorkbook();
            FileStream fs = new FileStream(fileFullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            ISheet sheet = workbook.CreateSheet("Sheet0");
            IRow row;
            row = sheet.CreateRow(0);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            workbook.Write(fs); //写入到excel
            #endregion
        }