C# EXCEL表格处理相关函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
using exDataTable = Microsoft.Office.Interop.Excel.DataTable;
using DataTable = System.Data.DataTable;
namespace new_project
{
    class File_deal
    {
        //获得所打开文件夹中所有CSV路径字符串数组
        public static string[] OpenFolder()
        {
            //打开文件夹获得路径
            System.Windows.Forms.FolderBrowserDialog folderBrowserDialog = new System.Windows.Forms.FolderBrowserDialog();

            if (folderBrowserDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return null;
            }

            else
            {
                string path = folderBrowserDialog.SelectedPath + @"\";
                string[] allFiles;
                //遍历path目录下的某种特定类型文件（不包括子目录中的） 
                allFiles = System.IO.Directory.GetFiles(path, "*.csv");
                return allFiles;

            }

        }

        //返回打开文件结果字符串数组，string【0】是打开文件完整路径，string【1】是文件名无后缀
        public static string[] OpenFile()
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "CSV文件|*.CSV";
            //openFileDialog1.InitialDirectory = "D:\\";


            if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
            {
                return null;
            }

            else
            {
                string filePath = openFileDialog1.FileName;
                string fileName = System.IO.Path.GetFileNameWithoutExtension(filePath);
                string[] result = new string[2];
                result[0] = filePath;
                result[1] = fileName;
                return result;
            }
        }

        //打开csv，并读取为datatable
        public static DataTable OpenCSV(string fileName)
        {
            DataTable dt = new DataTable();
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
            //记录每次读取的一行记录
            string strLine = "";
            //记录每行记录中的各字段内容
            string[] aryLine;
            //标示列数
            int columnCount = 0;
            //标示是否是读取的第一行
            bool IsFirst = true;
            //逐行读取CSV中的数据
            while ((strLine = sr.ReadLine()) != null)
            {
                aryLine = strLine.Split(',');
                if (IsFirst == true)
                {
                    IsFirst = false;
                    columnCount = aryLine.Length;
                    //创建列
                    for (int i = 0; i < columnCount; i++)
                    {
                        DataColumn dc = new DataColumn(aryLine[i]);
                        dt.Columns.Add(dc);
                    }
                }
                else
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < columnCount; j++)
                    {
                        dr[j] = aryLine[j];
                    }
                    dt.Rows.Add(dr);
                }
            }
            sr.Close();
            fs.Close();
            return dt;
        }

        //将datatable保存为Excel的CSV的函数 messageFlag控制提示窗口        
        public static void SaveCSV(System.Data.DataTable dt, string fileName, int messageFlag)
        {
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
            string data = "";
            //写出列名称
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                data += dt.Columns[i].ColumnName.ToString();
                if (i < dt.Columns.Count - 1)
                {
                    data += ",";
                }
            }
            sw.WriteLine(data);
            //写出各行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    data += dt.Rows[i][j].ToString();
                    if (j < dt.Columns.Count - 1)
                    {
                        data += ",";
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
            if (messageFlag == 1)
                MessageBox.Show("CSV文件保存成功！");
        }

        //datatable保存为xlsx表格,需引用添加excel.exe（我的是2013）
        public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null)
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;
            //要添加引用using Microsoft.Office.Interop.Excel
            Microsoft.Office.Interop.Excel.Application xIAPP = new Microsoft.Office.Interop.Excel.Application();
            xIAPP.DefaultFilePath = "";
            xIAPP.SheetsInNewWorkbook = 1;
            Workbook xIBOOK = xIAPP.Workbooks.Add(true);
            foreach (DataColumn dc in tmpDataTable.Columns)
            {
                columnIndex++;
                xIAPP.Cells[rowIndex, columnIndex] = dc.ColumnName;
            }
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    columnIndex++;
                    xIAPP.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                }
            }
            xIBOOK.SaveCopyAs(strFileName);
        }
    }
}