C# 操作Excel 并把EXCel 轉為cvs格式

 

 

 

1：首先在項目中加這個office的dll（Microsoft.Office.Interop.Excel;）

 

2：加個類：

        using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace XlsToTxt
{
 public   class ExcelHelper
    {
        private Excel._Application excelApp;
        private string fileName = string.Empty;
        private Excel.WorkbookClass wbclass;
        public ExcelHelper(string _filename)
        {
            excelApp = new Excel.Application();
            object objOpt = System.Reflection.Missing.Value;
            wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
        }
        /**/
        /// <summary>
        /// 所有sheet的名称列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetSheetNames()
        {
            List<string> list = new List<string>();
            Excel.Sheets sheets = wbclass.Worksheets;
            string sheetNams = string.Empty;
            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }
        public Excel.Worksheet GetWorksheetByName(string name)
        {
            Excel.Worksheet sheet = null;
            Excel.Sheets sheets = wbclass.Worksheets;
            foreach (Excel.Worksheet s in sheets)
            {
                if (s.Name == name)
                {
                    sheet = s;
                    break;
                }
            }
            return sheet;
        }
        /**/
        /// <summary>
        ///
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public Array GetContent(string sheetName)
        {
            Excel.Worksheet sheet = GetWorksheetByName(sheetName);
            //获取A1 到AM24范围的单元格
            Excel.Range rang = sheet.get_Range("A1", "BH1");
           // Excel.Range rang = sheet.get_Range("A1", "AM24");
            //读一个单元格内容
            //sheet.get_Range("A1", Type.Missing);
            //不为空的区域,列,行数目
            //   int l = sheet.UsedRange.Columns.Count;
            // int w = sheet.UsedRange.Rows.Count;
            //  object[,] dell = sheet.UsedRange.get_Value(Missing.Value) as object[,];
            System.Array values = (Array)rang.Cells.Value2;
            return values;
        }

    
        public void Close()
        {
            excelApp.Quit();
            excelApp = null;
        }

    }
}

 

3：在winform中加這些代碼

          using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Diagnostics;

 

namespace XlsToTxt
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string[] namePaths;
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog OP = new OpenFileDialog();
            OP.Title = "請選擇文件...";
            OP.Filter = "全部XL文件(*.xls)|*.xls";
            OP.CheckFileExists = true;
            OP.CheckPathExists = true;
            OP.Multiselect = false;
            if (OP.ShowDialog() == DialogResult.OK)
            {
                namePaths = OP.FileNames;
                int ii = 1;
                //  textBox1.Text = Path.GetDirectoryName(namePaths[0]);
                textBox1.Text = namePaths[0].Trim();
                StringBuilder name = new StringBuilder("第1個文件： ");
                foreach (string namePath in namePaths)
                {

                    name = name.Append(Path.GetFileName(namePath));
                    name = name.Append("/r/n");
                    ii++;
                    name = name.Append("第" + ii.ToString() + "個文件： ");

                }
                if (ii > 0)
                {
                    name = name.Append("/r/n");
                    name = name.Replace("第" + ii.ToString() + "個文件： ", "");

                    name = name.Append("總共有" + namePaths.Length.ToString() + "個文件！");
                    richTextBox1.Text = name.ToString();
                }

            }
        }

        DataSet ds;

 

        protected DataSet xsldata(string filepath, string tableName)
        {
            string tableNames = "[" + tableName + "$]";
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
            OleDbConnection Conn = new OleDbConnection(strCon);
            string strCom = "SELECT * FROM  " + tableNames;
            Conn.Open();
            using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, Conn))
            {
                ds = new DataSet();
                myCommand.Fill(ds, tableNames);
            }
            Conn.Close();
            return ds;

        }

        private int ImportData(string fileurl, string saveFileUrl, string tableName)
        {
            int sum = 0;

            try
            {
                ds = xsldata(fileurl, tableName);

                if (ds != null)
                {
                    int rows = ds.Tables[0].Rows.Count;
                    int colums = ds.Tables[0].Columns.Count;
                    using (FileStream fs = new FileStream(saveFileUrl, FileMode.Create, FileAccess.Write))
                    {
                        using (StreamWriter sw = new StreamWriter(fs))
                        {
                            for (int i = 0; i < rows; i++)
                            {

                                for (int j = 0; j < colums; j++)
                                {
                                    string Value = string.Empty;
                                    Value = ds.Tables[0].Rows[i][j].ToString().Trim();
                                    sw.Write(Value);
                                    if (j < colums - 1)
                                    {
                                        sw.Write(",");
                                    }
                                    sum++;

                                }
                                sw.WriteLine();

                            }
                        }

                    }
                }

                return sum;

            }
            catch (Exception err)
            {
                return sum;

            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != null)
            {
                SaveFileDialog sf = new SaveFileDialog();
                sf.Filter = "記事本 (*.txt)|*.txt|All 所有文件 (*.*)|*.* ";
                sf.RestoreDirectory = true;
                if (sf.ShowDialog() == DialogResult.OK)
                {
                    int sum = 0;

                    sum = ImportData(textBox1.Text.Trim(), sf.FileName, comboBox1.Text.Trim());

                    MessageBox.Show("生成txt文件成功！共有" + sum.ToString() + "個字段被寫入");
                }
            }
            else MessageBox.Show("必須先打開文件選擇啦！");

        }

 

        private void button3_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        static string s = null;
        ExcelHelper ExcelHelper;
        private void textBox1_TextChanged(object sender, EventArgs e)
        {

            s = textBox1.Text.Trim();
            ExcelHelper = new ExcelHelper(s);
            //ExcelHelper = new ExcelHelper(textBox1.Text.Trim());

            List<string> sheets = ExcelHelper.GetSheetNames();
            if (sheets.Count > 0)
            {
                comboBox1.DataSource = sheets;

            }
            //  ExcelHelper.Close();
        }

        private void comboBox1_TextChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text.Trim() != null)
            {
              ExcelHelper = new ExcelHelper(s);
                Array array=ExcelHelper.GetContent(comboBox1.Text.Trim());
                StringBuilder list = new StringBuilder();
                if (array .Length >0)
                {

                    int sum = 1;
                    foreach (string s1 in array)
                    {
                        if (s1!=null)
                        {
                            list = list.Append("第" + sum.ToString() + "列是 : " + s1);
                            list = list.Append("/r/n");
                            sum++;
                        }
                    }
                    list = list.Append("/r/n");
                    list.Append("共有：");
                 
                    list.Append((sum-1).ToString()+"個列");
                }
                    else
                    {
                        list.Append("該Sheet為空！");
                    }
                    ExcelHelper.Close();
                    richTextBox2.Text = list.ToString();
                }

            }
          
        }

  

      
    }