話不多說我直接進(jìn)入正題，疫情期間先來無事，找點(diǎn)事做。
由于自己寫入2007版xlsx，總是出問題后來查到是.xlsx推薦用EPPLUS，xls推薦用NPOI，還以為自己代碼寫的有問題，氣死我了！??！

1.首先下載npoI組件，后引用對(duì)應(yīng)的命名空間,在Userball中。

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

2.創(chuàng)建一個(gè)類sqlhelper ，讀取dataTable的函數(shù)

      
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

using System.Data.SqlClient;
using System.Data;
namespace sannCeng.DAL33
{
    class sqlHelper33
    {
        //獲取配置文件
      

        private static string connStr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        

        public static DataTable ExecuteQuery(string sql, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(sqlParameters);
                DataTable dt = new DataTable();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);

                }

                return dt;
            }
        }


    }
}

3.創(chuàng)建一個(gè)實(shí)現(xiàn)類UserDal調(diào)用ExecuteQuery

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using sanCeng.Model33;
using System.Data;

namespace sannCeng.DAL33
{
    public class UserDal
    {
     
     

        public DataTable GetAll()
        {
            DataTable dataTable = sqlHelper33.ExecuteQuery("select * from T_users p where p.Isdeleted=0");
            return dataTable;
        }
    }
}

4.創(chuàng)建一個(gè)UserBll調(diào)用UserDal

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sanCeng.Model33;
using sannCeng.DAL33;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace sanCeng.BLL33
{
    public class UserBll
    {
      
        private UserDal uDAL = new UserDal();
    
        public void GetAll()
        {
          
            DataTable tmpDataTable = uDAL.GetAll();
            string TargetFileNamePath = @"C:\Users\xg\Desktop\test.xls";//目標(biāo)文件excel的路徑
           // string sheetName;//excel表中的sheet的名稱，可以根據(jù)情況自己起

          
            int columnIndex = 0;

            int rowNum = tmpDataTable.Rows.Count;//總行數(shù)
            int columnNum = tmpDataTable.Columns.Count;//總列數(shù)


            if (tmpDataTable == null)
            {
                return;
            }
            // 數(shù)據(jù)驗(yàn)證
            if (!File.Exists(TargetFileNamePath))
            {
                //excel文件的路徑不存在
                throw new ArgumentException("excel文件的路徑不存在或者excel文件沒有創(chuàng)建好");
            }


            //根據(jù)Excel文件的后綴名創(chuàng)建對(duì)應(yīng)的workbook
            IWorkbook xlApp = null;
            if (TargetFileNamePath.IndexOf(".xlsx") > 0)
            {  //2007版本的excel
                xlApp = new XSSFWorkbook();
            }
            else if (TargetFileNamePath.IndexOf(".xls") > 0) //2003版本的excel
            {
                xlApp = new HSSFWorkbook();
            }




            //IWorkbook xlApp  = new XSSFWorkbook();

            //excel表的sheet名
            ISheet sheet = xlApp.CreateSheet("user");

            // ISheet sheet2 = xlApp.GetSheetAt(0);

            IRow row = sheet.CreateRow(0);
           //  int lastRowNumber = sheet.LastRowNum;

            foreach (DataColumn dc in tmpDataTable.Columns)
            {
               
                // row.GetRow(rowIndex).GetCell(columnIndex).SetCellValue();
                row.CreateCell(columnIndex).SetCellValue(dc.ColumnName.ToString());
                columnIndex++;
                //  row[rowIndex, columnIndex] = dc.ColumnName;
                // row[rowIndex, columnIndex] = dc.ColumnName;
            }


            //寫入數(shù)據(jù)
            for (int row1 = 1; row1 < tmpDataTable.Rows.Count; row1++)
            {
                //sheet表創(chuàng)建新的一行
                IRow newRow = sheet.CreateRow(row1);
                for (int column = 0; column < tmpDataTable.Columns.Count; column++)
                {
                    newRow.CreateCell(column).SetCellValue(tmpDataTable.Rows[row1][column].ToString());

                }
            }


            //寫入到excel中
            using (Stream fileStream = File.OpenWrite(TargetFileNamePath))
            {

                xlApp.Write(fileStream);
                fileStream.Close();
                
            }
        }
    }
}