返回顶部

收藏

C#读取Excel文件,并保存为文本文件

更多
    using System;  
    using System.Collections.Generic;  
    using System.ComponentModel;  
    using System.Data;  
    using System.Drawing;  
    using System.Linq;  
    using System.Text;  
    using System.Threading.Tasks;  
    using System.Windows.Forms;  
    using System.Data.OleDb;  
    using System.IO;  
    using System.Data.SqlClient;  

    namespace Excel  
    {  
        public partial class Form1 : Form  
        {  
            //全局变量,文件全路径  
            private string stFilePath = string.Empty;  
            public Form1()  
            {  
                InitializeComponent();  
            }  
            private void Form1_Load(object sender, EventArgs e)  
            {  
                DataTable myT = ExcelToDataTable("D:/ex03_demo.xls", "sheet1");  
                String mystr = myT.Rows[0][0].ToString();  

                //用listview1显示打开的excel  
                this.listView1.GridLines = true;//显示表格线  
                this.listView1.View = View.Details;//列表显示方式  
                this.listView1.Scrollable = true;//有滚动条  
                listView1.FullRowSelect = true;//是否选中整行  

                //设置表头  
                for (int i = 0; i < 9; i++)  
                {  
                    this.listView1.Columns.Add("lie" + i, myT.Rows[0][i].ToString());  
                }  

                //设置表的内容  
                for (int j = 1; j < 47; j++)  
                {  
                    ListViewItem item = new ListViewItem();  
                    item.SubItems.Clear();  
                    item.SubItems[0].Text = myT.Rows[j][0].ToString();  
                    for (int k = 1; k < 9; k++)  
                    {  
                        item.SubItems.Add(myT.Rows[j][k].ToString());  
                    }  
                    listView1.Items.Add(item);  
                }  

                //自适应宽度,-1根据内容设置宽度,-2根据标题设置宽度  
                for (int i = 0; i < 9; i++)  
                {  
                    listView1.Columns["lie" + i].Width = -1;  
                    listView1.Columns["lie" + i].Width = -2;  
                }  
            }  

            //将姓名和作业网址用txt存放于bin目录下  
            private void button1_Click(object sender, EventArgs e)  
            {  
                //此处的文本文件在工程下Bin的程序集目录下  
                stFilePath = Application.StartupPath.Trim() + "//ex03_demo" + ".txt";  
                StreamWriter swStream;  
                if (File.Exists(stFilePath))  
                {  
                    swStream = new StreamWriter(stFilePath);  
                }  
                else  
                {  
                    swStream = File.CreateText(stFilePath);  
                }  
                for (int i = 0; i < listView1.Items.Count; i++)  
                {  
                    for (int j = 4; j <= 6; j += 2)  
                    {  
                        string temp = listView1.Items[i].SubItems[j].Text;  
                        swStream.Write(temp);  
                        //插入------分隔符  
                        swStream.Write("--------");  
                    }  
                    swStream.WriteLine();  
                }  
                //关闭流,释放资源  
                swStream.Flush();  
                swStream.Close();  
                MessageBox.Show("已成功保存在bin目录下");  
            }  
            public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)  
            {  
                //源的定义  
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  

                //Sql语句  
                //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法  
                string strExcel = "select * from   [sheet1$]";  

                //定义存放的数据表  
                DataSet ds = new DataSet();  

                //连接数据源  
                OleDbConnection conn = new OleDbConnection(strConn);  

                conn.Open();  

                //适配到数据源  
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);  
                adapter.Fill(ds, strSheetName);  

                conn.Close();  

                return ds.Tables[strSheetName];  
            }  

        }  
    }  

标签:c#

收藏

0人收藏

支持

0

反对

0

发表评论