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

来源：互联网发布：微课制作软件下载编辑：程序博客网时间：2024/04/29 23:39
项目正好需要，学习了下，这里就介绍一种方法吧
首先需要追加一个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;        }    }}
0 0