关于C#对EXCEL的一些简单操作

项目正好需要，学习了下，这里就介绍一种方法吧
首先需要追加一个com的dell
然后需要加代码引用
using Microsoft.Office.Interop.Excel;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Interop.Excel;

namespace Csv_to_Excel
{
    class Program
    {
        static void Main(string[] args)
        {


            Console.WriteLine("処理開始");
            try
            {
                #region csv变换成datatable
                //原始文件路径
                string path = @"e:\output\一覧_20150929.csv";
                bool isDtHasColumn = false;
                StreamReader reader = new StreamReader(path, System.Text.Encoding.Default);  //数据流
                Microsoft.Office.Interop.Excel.Application App1 = new Microsoft.Office.Interop.Excel.Application();
                App1.Visible = false;
                App1.DisplayAlerts = false;
                //模版
                Workbook Book1 = App1.Workbooks.Open(@"E:\output\excel_test.xlsx");
                Worksheet sheet1 = (Worksheet)Book1.Sheets[1];
                Worksheet sheet2 = (Worksheet)Book1.Sheets[2];



                //一覧path
                Workbook BookInput = App1.Workbooks.Open(@"E:\output\xxxxxxxxxx.xlsx");
                Worksheet sheetInput = (Worksheet)BookInput.Sheets[1];
                int InputRowCount = sheetInput.UsedRange.Rows.Count;

                System.Data.DataTable dt = new System.Data.DataTable();
                //模版的开始写入行
                int excelRow1 = 20;
                int excelRow2 = 21;

                while (!reader.EndOfStream)
                {
                    string meaage = reader.ReadLine();
                    string[] splitResult = meaage.Split(',');  //读取一行 以逗号分隔 存入数组

                    DataRow row = dt.NewRow();

                    for (int i = 0; i < splitResult.Length; i++)
                    {
                        if (!isDtHasColumn) //如果还没有生成列
                        {
                            dt.Columns.Add("ID", typeof(string));
                            dt.Columns.Add("xx", typeof(string));
                            dt.Columns.Add("xxx", typeof(string));
                            dt.Columns.Add("xxx", typeof(string));
                            dt.Columns.Add("xxx", typeof(string));
                            dt.Columns.Add("xxx", typeof(string));
                            dt.Columns.Add("xxx", typeof(string));
                            dt.Columns.Add("xxxx", typeof(string));
                        }
                        //row[i]= splitResult[i];
                        string[] sArray = splitResult[i].Split('\t');
                        //csv字段小于14时错误
                        if (sArray.Length < 14)
                        {
                            Console.WriteLine("error！");
                            Book1.Close();
                            BookInput.Close();
                            App1.Quit();
                            return;
                        }
                        //foreach (string k in sArray)
                        //{
                        //    Console.WriteLine(k.ToString() + "</br>");
                        //}
                        row["ID"] = sArray[1];
                        row["xxxx"] = sArray[2];
                        row["xxx"] = sArray[3];
                        row["xxx"] = sArray[0];
                        row["xxx"] = sArray[8];
                        row["xx"] = sArray[12];
                        row["xxx"] = sArray[5];
                        row["xxx"] = "";
                    }
                    dt.Rows.Add(row);
                    isDtHasColumn = true;
                }
                //ＩＤ来排序
                dt.DefaultView.Sort = "ID";
                dt = dt.DefaultView.ToTable();
                Console.WriteLine("datatableを生成しました！");

                #endregion



                #region sheet生成

                string kaishaID = string.Empty;
                string dtkaishaName = string.Empty;
                string inputkaishaName = string.Empty;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i > 0 && kaishaID != dt.Rows[i]["会社ID"].ToString() || i == dt.Rows.Count - 1)
                    {
                        try
                        {
                            //追加审查者
                            for (int k = 0; k <= InputRowCount-3; k++)
                            {
                                if (sheetInput.Cells[k + 3, 2].Value == null)
                                {
                                    sheetInput.Cells[k + 3, 2].Value = "";
                                }
                                inputkaishaName = sheetInput.Cells[k + 3, 2].Value.ToString();

                                //if (dtkaishaName == sheetInput.Cells[k + 3, 2].Value.ToString())
                                if (KaishaFormat(dtkaishaName) == KaishaFormat(inputkaishaName))
                                {
                                    //ID
                                    sheet2.Cells[excelRow2, 1].Value = sheetInput.Cells[k + 3, 3].Value;
                                    //xxx
                                    sheet2.Cells[excelRow2, 2].Value = sheetInput.Cells[k + 3, 4].Value;
                                    //xxx
                                    sheet2.Cells[excelRow2, 3].Value = sheetInput.Cells[k + 3, 7].Value;
                                    //xxx
                                    sheet2.Cells[excelRow2, 4].Value = sheetInput.Cells[k + 3, 8].Value;
                                    //xxx
                                    sheet2.Cells[excelRow2, 5].Value = sheetInput.Cells[k + 3, 9].Value;
                                    //xxx
                                    sheet2.Cells[excelRow2, 6].Value = sheetInput.Cells[k + 3, 10].Value;
                                    //xxxx
                                    sheet2.Cells[excelRow2, 7].Value = sheetInput.Cells[k + 3, 11].Value;
                                    excelRow2++;
                                }
                            }
                            //審査者時間

                            //生成的excel保存路径
                            Book1.SaveAs(@"E:\output\out\"+dtkaishaName+"xxxxx" + kaishaID + ".xlsx");
                            Console.WriteLine("excel" + dtkaishaName + "生成！");
                            //清空数据
                            sheet1.get_Range("A20", "F" + excelRow1).Clear();
                            sheet2.get_Range("A21", "G" + excelRow2).Clear();
                            //sheet1.Cells.Clear();
                            //sheet2.Cells.Clear();
                            //重置开始写入行
                            excelRow1 = 20;
                            excelRow2 = 21;
                        }
                        catch (Exception ex)
                        {
                            Book1.Close();
                            BookInput.Close();
                            App1.Quit();
                            Console.WriteLine(ex.Message);
                        }
                    }
                    kaishaID = dt.Rows[i]["ID"].ToString();
                    dtkaishaName = dt.Rows[i]["名"].ToString();

                    //出力excel 1,2,3,4,5,6要改
                    if (dt.Rows[i]["xxx"].ToString() == "xxx")
                    {

                        sheet1.Cells[excelRow1, 1].Value = dt.Rows[i]["ID"];

                        sheet1.Cells[excelRow1, 2].Value = dt.Rows[i]["xxx"];

                        sheet1.Cells[excelRow1, 3].Value = dt.Rows[i]["XXX"];

                        sheet1.Cells[excelRow1, 4].Value = dt.Rows[i]["xx"];

                        sheet1.Cells[excelRow1, 5].Value = dt.Rows[i]["xxx"];

                        sheet1.Cells[excelRow1, 6].Value = dt.Rows[i]["xxx"];

                        excelRow1++;
                    }
                }

                #endregion

                Book1.Close();
                BookInput.Close();
                App1.Quit();
                Console.WriteLine("処理終了！");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }



        /// <summary>
        /// 名比较用
        /// </summary>
        /// <param name="kaishaName">kaishaName</param>
        /// <returns>kaishaName</returns>
        private static string KaishaFormat(string kaishaName)
        {

            if (kaishaName.Contains("aaa"))
            {
                kaishaName = kaishaName.Replace("AAA", "aaa");
            }


            return kaishaName;
        }
    }
}