[代码实例][.NET]操作Excel文件

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ImportPicSQL
{
    class Program
    {
        static FileInfo errFile = null;

        static void Main(string[] args)
        {
            int ch;
            DataTable table = new DataTable("PicTable");
            DataColumn column;
            DataRow row;
            FileInfo fileInfo;
            Regex regex;

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Column1";
            table.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Column2";
            table.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Column3";
            table.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Column4";
            table.Columns.Add(column);


            Console.WriteLine("顶层目录为：" + Environment.CurrentDirectory);
            Console.Write("是否从该目录开始生成Excel？[Y/N] ");
            ch = Console.Read();
            Console.WriteLine((char)ch);
            if (ch == 'Y' || ch == 'y' || ch == '\r')
            {
                errFile = new FileInfo(string.Format(@"{0}\{1}", Environment.CurrentDirectory, "errLog.txt"));
                Console.WriteLine("开始生成Excel...");

                string[] subDirs = Directory.GetDirectories(Environment.CurrentDirectory);
                Console.WriteLine("总共有 {0} 个子文件夹需要处理！", subDirs.Length);
                int i = 1;
                foreach (var subDir in subDirs)
                {
                    Console.WriteLine("开始处理第 {0} 个文件夹" + subDir + "...", i++);

                    foreach (var file in Directory.GetFiles(subDir))
                    {
                        fileInfo = new FileInfo(file);
                        row = table.NewRow();

                        regex = new Regex(@"^\d+-\d\.\S{3}$");
                        if (regex.IsMatch(fileInfo.Name))
                        {
                            row["Column1"] = "HNKRLLSCS";
                            row["Column2"] = fileInfo.Name.Split('-')[0];
                            row["Column3"] = "-" + fileInfo.Name.Split('-')[1];
                            row["Column4"] = "0";
                        }
                        else
                        {
                            row["Column1"] = "HNKRLLSCS";
                            row["Column2"] = fileInfo.Name;
                            row["Column3"] = "";
                            row["Column4"] = "0";

                            StreamWriter sw = errFile.AppendText();
                            sw.WriteLine("文件名称不符合要求，需手动更改：" + file);
                            sw.Flush();
                            sw.Close();
                        }
                        table.Rows.Add(row);
                    }
                    GenerateExcel(table, subDir);

                    Console.WriteLine("处理完毕！");
                    table.Clear();
                }

                Console.WriteLine("生成Excel结束！");
            }
            else
            {
                Console.WriteLine("请将本程序移动到正确的目录再执行！");
            }
        }

        private static void GenerateExcel(DataTable table, string subDir)
        {
            // 以新建方式打开Excel
            Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
            appExcel.Visible = false;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = appExcel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add();
            Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1");

            try
            {
                // 对Worksheet进行操作
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        range = worksheet.get_Range(string.Format("{0}{1}", (char)('A' + j), i + 1));
                        range.NumberFormat = "@";
                        range.Value2 = table.Rows[i][j].ToString();
                    }
                }

                // 保存Workbook到文件
                DirectoryInfo directoryInfo = new DirectoryInfo(subDir);
                workbook.SaveAs(string.Format(@"{0}\{1}.xlsx", subDir, directoryInfo.Name));
            }
            catch (Exception err)
            {
                StreamWriter sw = errFile.AppendText();
                sw.WriteLine("出现异常错误：");
                sw.Write("    ");
                sw.Write(err.Message);
                sw.Flush();
                sw.Close();
            }
            finally
            {
                // 关闭workbook并且退出Excel
                workbook.Close();
                appExcel.Quit();

                // 释放COM对象占用的内存
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);

                // 强制回收垃圾
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}