Winform txt To Excel

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace TxtToExcel
{
    public partial class Form1 : Form
    {
        string fileName = "";
        string defaultPath = "";
        string savePath2 = "";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            openFile.Title = "选择文件";
            //openFile.InitialDirectory = Directory.GetCurrentDirectory();
            openFile.Filter = "文档|*.txt";
            openFile.FilterIndex = 1;
            openFile.RestoreDirectory = true;
            if (openFile.ShowDialog().Equals(DialogResult.OK))
            {
                this.textBox1.Text = openFile.FileName;
                fileName = openFile.SafeFileName.ToString();
                fileName = fileName.Substring(0, fileName.LastIndexOf('.'));
                defaultPath = openFile.FileName.Substring(0, openFile.FileName.Length - openFile.SafeFileName.Length-1);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog path = new FolderBrowserDialog();
            path.ShowDialog();
            this.textBox2.Text = path.SelectedPath;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string[] arr={"收单机构代码","商户代码","系统时间"};
            string open = this.textBox1.Text.ToString().Trim();
            string save = this.textBox2.Text.ToString().Trim();
            string savePath = "";
            if (String.IsNullOrEmpty(save))
            {
                save = defaultPath;
            }
            if (save.Substring(save.Length - 1) == "\\")
                save = save.Substring(0, save.Length - 1);
            savePath = testPath(save);
            if (System.IO.File.Exists(open) == false)
            {
                MessageBox.Show(fileName + "文件不存在");
            }
            
            StreamReader sr = new StreamReader(open,Encoding.Default);
            string str = sr.ReadLine();
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass appClass = new ApplicationClass();
            appClass.Visible = false;
            appClass.DefaultFilePath = "";
            appClass.DisplayAlerts = false;
            appClass.SheetsInNewWorkbook = 1;
            appClass.Application.Workbooks.Add(true);
            
            Workbook book = (Workbook)appClass.ActiveWorkbook;
            Worksheet sheet = (Worksheet)appClass.ActiveSheet;
          
            //Range range = sheet.get_Range("A1", "H100");
            //range.NumberFormatLocal = "@";
            int rowIndex=1;
            List<int> nums = new List<int>();
            List<string> list = new List<string>();
            while (str!=null)
            {
                if (rowIndex == 1)
                {
                    list = str.Split('，').ToList<string>();
                    nums = GetIndexs(list, arr);
                    DeleteList(list, nums);
                }
                else
                {
                    list = str.Split(',').ToList<string>();
                    DeleteList(list, nums);
                    FormatList(list);
                }
                for (int i = 0; i < list.Count;i++)
                {
                    int j=i+1;
                    Range range = sheet.get_Range("A" + j, "H" + j);
                    range.NumberFormatLocal = "@";   //将单元格设置为文本格式
                    Range range2 = sheet.get_Range("E" + j);
                    range2.NumberFormatLocal = "";   //又设为默认格式
                    appClass.Cells[rowIndex, j] = list[i].ToString().Trim();
                }
                rowIndex++;
                str = sr.ReadLine();
                if (str == "")
                    str = sr.ReadLine();
            }
            sr.Close();
            
            book.SaveCopyAs(savePath);
            book.Close(false, missing, missing);
            appClass.Quit();
            MessageBox.Show("转换成功！");
        }

        /// <summary>
        /// 判断是否有同名文件，有的话文件名+1
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public string testPath(string filePath, int i = 0)
        {
            if (i == 0)
            {
                savePath2 = filePath + "\\" + fileName + ".xlsx";
            }
            else
            {
                savePath2 = filePath + "\\" + fileName + "(" + i.ToString() + ").xlsx";
            }
            if (File.Exists(savePath2))
            {
                testPath(filePath, ++i);
            }
            return savePath2;
        }

        /// <summary>
        /// 数组中是否存在该字符串
        /// </summary>
        /// <param name="arr"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public bool IsExistStr(string[] arr, string str)
        {
            for (int i = 0; i < arr.Length; i++)
            {
                if (arr[i].ToString().Trim() == str.Trim())
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 根据字符串获取数组中的索引
        /// </summary>
        /// <param name="list"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public  List<int> GetIndexs(List<string> list, string[] str)
        {
            List<int> nums = new List<int>();
            for (int i = 0; i < list.Count; i++)
            {
                for (int j = 0; j < str.Length; j++)
                {
                    if (list[i].ToString().Trim() == str[j].Trim())
                    {
                        nums.Add(i);
                    }
                }
            }
            return nums;
        }

        /// <summary>
        /// 按索引删除元素
        /// </summary>
        /// <param name="list"></param>
        /// <param name="nums"></param>
        public  void DeleteList(List<string> list, List<int> nums)
        {
            if (nums == null)
                return;
            for (int i = 0; i < nums.Count; i++)
            {
                list.RemoveAt(nums[i] - i);
            }
        }

        /// <summary>
        /// 转换Excel中数据的格式
        /// </summary>
        /// <param name="list"></param>
        public void FormatList(List<string> list)
        {
            list[0] = list[0].ToString();
            switch (list[1].ToString())
            {
                case "01": list[1] = "消费交易"; break;
                case "31": list[1] = "消费撤销"; break;
                case "02": list[1] = "预授权"; break;
                case "32": list[1] = "预授权撤销"; break;
                case "03": list[1] = "预授权完成"; break;
                case "33": list[1] = "预授权完成撤销"; break;
                case "04": list[1] = "退货交易"; break;
                default: break;
            }
            list[2] = list[2].ToString();
            //list[3] = Convert.ToDateTime(list[3]).ToString("yyyy-MM-dd HH:mm:ss");
            list[3] = list[3].Substring(0, 4) + "-" + list[3].Substring(4, 2) + "-" + list[3].Substring(6);
            list[4] = (Convert.ToDecimal(list[4].Substring(0, list[4].Length - 1)) / 100).ToString().Trim();
            list[6] = list[6].ToString();            
        }
    }
}

txt

交易流水号，交易类型，收单机构代码，商户代码，商户订单号，系统时间，交易时间，金额，支付方式，卡性质，卡号字段

201301241334098719122,01,03052900,305310048990003,0124133353000000000000385,0124133409,20130124 13:34:09,+000000000010.,5,01,622588*********0

excel