Excel txt导入导出到 LISTVIEW

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Reflection;
using Excel;
using System.Collections;
using System.Collections.Specialized;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace LogOperator.Class
{
    public delegate void ColorEventHandler(ListViewItem lvi);
    /// <summary>
    /// 该类提供日志系统导入导出的方法、方式
    /// </summary>
    public class IOHelper
    {
        #region 属性  初始化

        /// <summary>
        /// 连接字符串参数 Provider和 Extended Properties
        /// </summary>
        private static string xlspro;
        private static string xlsex;

        private static int rowCount;
        private static int columnCount;
        private static int i;
        private static int j;
        private static string filePath;
        private static DataSet ds;
        private static string strConn;

        /// <summary>
        /// 定义一个委托  用于为不同的数据加色
        /// </summary>
        public static event ColorEventHandler color;

        public IOHelper()
        {
        }

        #endregion 属性  初始化

        #region 导出

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="listView"></param>
        public static void ExportTo(ListView listView)
        {
            SaveFileDialog dialog = new SaveFileDialog();

            dialog.Filter = "Excel2003(*.xls)|*.xls|Excel2007(*.xlsx)|*.xlsx|文本文件(*.txt)|*.txt|All Files(*.*)|*.*";
            dialog.FileName = "SysLog";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                if (dialog.FilterIndex==3)
                {
                    ExportToTxt(dialog,listView);
                    return;
                }
                //Microsoft.Office.Interop.Excel.Application  :Excel全部应用程序接口
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                //缺少的   Object   通过反射产生一个函数的默认参数
                object missing = System.Reflection.Missing.Value;
                try
                {
                    if (xlApp == null)
                    {
                        MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                        return;
                    }

                    //Workbook类表示 Excel 应用程序中的单个工作簿
                    Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
                    //XlWBATemplate  指定创建的workbook的种类
                    Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
                    Microsoft.Office.Interop.Excel.Range range = null;
                    //****** 抬头 *********************************************************************************
                   
                                        range = xlSheet.get_Range("A1", "I1");
                                        range.Merge(Missing.Value);         // 合并单元格
                                        range.Columns.AutoFit();            // 设置列宽为自动适应                 
                                        // 设置单元格左边框加粗
                                        range.Borders[XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                                        // 设置单元格右边框加粗
                                        range.Borders[XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                                        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;// 设置单元格水平居中
                                        range.Value2 = "系统日志记录";
                                        range.Font.Size = 18;                        // 设置字体大小
                                        range.Font.ColorIndex = 5;                  // 设置字体颜色                   
                                        //range.Interior.ColorIndex = 6;  // 设置单元格背景色
                                        range.RowHeight = 25;           // 设置行高
                                        range.ColumnWidth = 20;         // 设置列宽

                                        xlSheet.Cells[2, 1] = "记录时间";
                                        xlSheet.Cells[2, 2] = "IP地址";
                                        xlSheet.Cells[2, 3] = "进程";
                                        xlSheet.Cells[2, 4] = "主机名";
                                        xlSheet.Cells[2, 5] = "线程ID";
                                        xlSheet.Cells[2, 6] = "级别";
                                        xlSheet.Cells[2, 7] = "日志名称";
                                        xlSheet.Cells[2, 8] = "消息";
                                        xlSheet.Cells[2, 9] = "异常";

                   
                    int rowIndex = 3;//这个用来标记数据有多少行位置
                   
                    //-----------------------设置单元格 --------------------------------------------------------------------------------
                    range = xlSheet.get_Range(xlSheet.Cells[3, 9], xlSheet.Cells[rowIndex + listView.Items.Count, 9]);//异常
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 200;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 8], xlSheet.Cells[rowIndex + listView.Items.Count, 8]);//消息
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 50;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 7], xlSheet.Cells[rowIndex +listView.Items.Count, 7]);//日志名称
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 20;


                    range = xlSheet.get_Range(xlSheet.Cells[3, 6], xlSheet.Cells[rowIndex + listView.Items.Count, 6]);//级别
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 5;


                    range = xlSheet.get_Range(xlSheet.Cells[3, 5], xlSheet.Cells[rowIndex + listView.Items.Count, 5]);//线程ID
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 10;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 4], xlSheet.Cells[rowIndex + listView.Items.Count, 4]); //主机名
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.ColumnWidth = 18;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 3], xlSheet.Cells[rowIndex + listView.Items.Count, 3]); //进程
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.ColumnWidth = 20;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 2], xlSheet.Cells[rowIndex + listView.Items.Count, 2]); //IP
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "@";//文本格式
                    range.ColumnWidth = 13;

                    range = xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[rowIndex + listView.Items.Count, 1]); //时间
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                    range.NumberFormatLocal = "yyyy-MM-dd HH:mm";//日期格式
                    range.ColumnWidth = 20;

                    //-----------------------设置单元格 --------------------------------------------------------------------------------
                    //标题栏
                    range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 9]);
                    range.Interior.ColorIndex = 45;//设置标题背景色为 浅橙色
                    range.Font.Bold = true;//标题字体加粗



                    foreach (ListViewItem objItem in listView.Items)
                    {
                        xlSheet.Cells[rowIndex, 1] = Convert.ToDateTime(objItem.Text);
                        xlSheet.Cells[rowIndex, 2] = objItem.SubItems[1].Text;
                        xlSheet.Cells[rowIndex, 3] = objItem.SubItems[2].Text;
                        xlSheet.Cells[rowIndex, 4] = objItem.SubItems[3].Text;
                        xlSheet.Cells[rowIndex, 5] = objItem.SubItems[4].Text;
                        xlSheet.Cells[rowIndex, 6] = objItem.SubItems[5].Text;
                        xlSheet.Cells[rowIndex, 7] = objItem.SubItems[6].Text;
                        xlSheet.Cells[rowIndex, 8] = objItem.SubItems[7].Text;
                        xlSheet.Cells[rowIndex, 9] = objItem.SubItems[8].Text;

                        rowIndex += 1;
                    }


                    //数据区域
                    range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 9]);
                    range.Borders.LineStyle = 1;
                    range.Font.Size = 10;

                   

                    range = xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, 9]);
                    range.Merge(Missing.Value);         // 合并单元格
                    // range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
                    // 设置单元格右边框加粗
                    // range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
                    range.RowHeight = 20;
                    range.Value2 = "导出时间: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;// 设置单元格水平居左
                   
                    //***** 格式设定 ******************************************************************************




                    if (xlSheet != null)
                    {
                        xlSheet.SaveAs(dialog.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                        xlApp.Visible = true;
                    }


                }
                catch (Exception)
                {
                    xlApp.Quit();
                    //throw;
                }
            }
        }

        /// <summary>
        ///导出到txt
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private static void ExportToTxt(SaveFileDialog  dl,ListView lst)
        {
            using (StreamWriter objWriter = new StreamWriter(dl.FileName, false, System.Text.Encoding.UTF8))
            {
                foreach (ListViewItem objItem in lst.Items)
                {
                    objWriter.WriteLine(objItem.Text.PadRight(20) + "@" + objItem.SubItems[1].Text.PadLeft(15) + "@" + objItem.SubItems[2].Text.PadLeft(20) + "@" + objItem.SubItems[3].Text.PadLeft(18)
                                        + "@" + objItem.SubItems[4].Text.PadLeft(10) + "@" + objItem.SubItems[5].Text.PadLeft(8) + "@" + objItem.SubItems[6].Text.PadLeft(20)
                                        + "@" + objItem.SubItems[7].Text.PadLeft(15) + "@" + objItem.SubItems[8].Text.PadLeft(100));
                }      
                objWriter.Close();

            }

        }

        #endregion 导出

        #region 导入

        /// <summary>
        /// 导入方法
        /// </summary>
        /// <param name="lst"></param>
        public static void ImportMethod(ListView lst)
        {
            OpenFileDialog open = new OpenFileDialog();
            if (open.ShowDialog() == DialogResult.OK)
            {
                filePath = open.FileName;

            }
            else
            {
                return;
            }

            string extName = System.IO.Path.GetExtension(filePath);
            if (extName == ".txt")
            {
                ds = doImportTxt(filePath);
                rowCount = ds.Tables[0].Rows.Count;
                columnCount = ds.Tables[0].Columns.Count;
                lst.BeginUpdate();
                lst.Items.Clear();
                string[] lstitems = new string[columnCount];
                for (i = 0; i < rowCount; i++)
                {
                    for (j = 0; j < columnCount; j++)
                    {
                        lstitems[j] = ds.Tables[0].Rows[i][j].ToString();
                    }
                    ListViewItem lvi = new ListViewItem(lstitems);
                    color(lvi);//为listview各项 添加背景色
                    lst.Items.Add(lvi);
                }
                lst.Refresh();
                lst.EndUpdate();
            }
            else if (extName == ".xls" || extName == ".xlsx")
            {
                ds = doImportExcle(filePath);
                rowCount = ds.Tables[0].Rows.Count;
                columnCount = ds.Tables[0].Columns.Count;
                lst.BeginUpdate();
                lst.Items.Clear();
                string[] lstitems = new string[columnCount];
                for (i = 1; i < rowCount - 1; i++)
                {
                    for (j = 0; j < columnCount; j++)
                    {
                        lstitems[j] = ds.Tables[0].Rows[i][j].ToString();
                    }
                    ListViewItem lvi = new ListViewItem(lstitems);
                    color(lvi);
                    lst.Items.Add(lvi);
                }
                lst.Refresh();
                lst.EndUpdate();
            }
            else
            {
                MessageBox.Show("导入格式错误！");
            }


        }

        #region 从excel导入

       
        private static DataSet doImportExcle(string strFileName)
        {

            if (strFileName == "") return null;
            string extName = System.IO.Path.GetExtension(strFileName);
            if (extName == ".xls")
            {
                xlspro = ConfigurationManager.AppSettings["xls03pro"].ToString();
                xlsex = ConfigurationManager.AppSettings["xls03ex"].ToString();
            }
            else
            {
                if (extName == ".xlsx")
                {
                    xlspro = ConfigurationManager.AppSettings["xls07pro"].ToString();
                    xlsex = ConfigurationManager.AppSettings["xls07ex"].ToString();
                }
                else
                {
                    return null;
                }
            }
            string strConn = xlspro + "Data Source=" + strFileName + ";" + xlsex;
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs);

            }
            catch (Exception err)
            {
                System.Console.WriteLine(err.ToString());
            }
            return ExcelDs;
        }

        #endregion 从excel导入

        #region 从txt导入

        public static DataSet doImportTxt(string strFileName)
        {
           
            if (strFileName == "") return null;
            string sLine = "";
            DataSet dt;

            StreamReader objReader = new StreamReader(strFileName, Encoding.GetEncoding("GB2312"));//实现一个 TextReader，使其以GB2312编码从字节流中读取字符。
            sLine = objReader.ReadLine();//从当前流中读取一行字符并将数据作为字符串返回。
            objReader.Close();
            //string[] starry = sLine.ToString().Trim().Split('@');
            string[] starry = sLine.ToString().Split('@');
            dt = TextFileLoader(strFileName, "TableName", starry);
            // DataSet dt = TextFileLoader(strFileName, "TableName");

            return dt;
        }

        static int x = 0;

        public static DataSet TextFileLoader(string FilePath, string TableName, string[] FieldsInArray)    //, string[] FieldsInArray
        {
            DataSet ds = new DataSet();
            System.Data.DataTable dt = new System.Data.DataTable(TableName);//用指定的表明初始化datatable

            FileStream fs = File.Open(FilePath, FileMode.Open, FileAccess.Read);//使用指定的路径、创建模式和读/写权限初始化 FileStream 类的新实例。
            StreamReader sr = new StreamReader(fs, Encoding.GetEncoding("GB2312"));

            for (int i = 0; i < FieldsInArray.Length; i++)
            {
                //dt.Columns.Add(new DataColumn(FieldsInArray[i].Trim().Substring(0, (FieldsInArray[i].Trim().Length - 1)), typeof(string)));

                dt.Columns.Add(new DataColumn(FieldsInArray[i].Trim().Substring(0, (FieldsInArray[i].Trim().Length)), typeof(string)));

            }

            string strRead;
            bool flag = true;
          
            while (flag)
            {
                strRead = sr.ReadLine();

                if (!string.IsNullOrEmpty(strRead))
                {
                    string[] aryVale = strRead.Split('@');

                    DataRow dr = dt.NewRow();
                    for (int k = 0; k < aryVale.Length; k++)
                    {
                        if (!string.IsNullOrEmpty(aryVale[k].Trim()))
                        {
                            string st = aryVale[k].Trim().Substring(0, (aryVale[k].Trim().Length));
                            dr[k] = st;
                        }
                        else {
                            dr[k] = aryVale[k].Trim();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                else
                {
                    flag = false;
                }

                x++;
            }

            ds.Tables.Add(dt);
            return ds;
        }

        #endregion  从txt导入

        #endregion 导入
    }
}