[代码实例][.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();            }        }    }}