c# excel工作表及cell读取

       昨天晚上做了一回雷锋，帮朋友写了个程序，把他2天都未整理好的Excel数据，一个小时搞定。大致如下：我朋友是搞质检的，每天都会产生一个excel文件，每个excel文件都有20来个工作表，他要做的事情是把每个文件的每个工作表的其中6个单元格数据提取出来产生excel台账。悲哀的是，他要处理将近12个月的数据，累计300多个excel文件，估计他已经抓狂了。无奈，我用C#给他写了个excel工作表及cell读取的程序，因为只是他用，读取工作表的那些cell都写死了，没写成可配置。

       处理如下：

       1）文件夹excel文件遍历

       每个月的excel文件都存放在一个目录中，如201001，因此，我要读取该目录，并遍历出该目录的所有excel文件，当然读取哪个目录，他自己指定。

       2）excel工作表读取

       由于每个excel文件都有10到20个工作表，因此需要遍历读取没个工作表的指定cell信息。

       3）excel 工作表单元格读取

       4）读取到的单元格信息汇总成行输出

       简单起见，每个工作表的所有cell值输出成一行，并以/t分隔开，每行结尾输出换行符/r/n，输出到文本框控件。这样我朋友只要复制文本框中的所有信息到excel即可。

       5）当然为了告知当前已经处理到指定目录的哪个excel文件，添加了一个进度条

       界面如下：

       代码如下：

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.IO;

using Excel;

using System.Reflection;

 

namespace jiangbt

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        //文件夹设置

        private void button1_Click(object sender, EventArgs e)

        {

            FolderBrowserDialog folderBrowserDialog1 = new FolderBrowserDialog();

            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)

            {

                txtFolderPath.Text = folderBrowserDialog1.SelectedPath;

                retrieveFolder();

            }

            folderBrowserDialog1.Dispose();

        }

 

        private void retrieveFolder()

        {

            string[] filename = Directory.GetFiles(txtFolderPath.Text);

            listFiles.Items.Clear();

            if (filename != null && filename.Length > 0)

            {

                for (int i = 0; i < filename.Length; i++)

                {

                    if (filename[i].LastIndexOf(".") > 0)

                    {

                        String filetype = filename[i].Substring(filename[i].LastIndexOf(".") + 1);

                        filetype = filetype.ToLower();

                        if (filetype.Equals("xls"))

                        {

                            listFiles.Items.Add(filename[i]);

                        }

                    }

                }

                progressBar1.Minimum = 0;

                progressBar1.Maximum = listFiles.Items.Count;

                progressBar1.Value = 0;

            }

        }

 

        private void btnExtract_Click(object sender, EventArgs e)

        {

            txtOutput.Text = "";

            readExcel();

            MessageBox.Show("提取完毕!");

        }

 

        private void readExcel()

        {

            ApplicationClass app = new ApplicationClass();

            Workbook book = null;

            Worksheet sheet = null;

            Range range = null;

            try

            {

                //遍历文件

                for (int j = 0; j < listFiles.Items.Count; j++)

                {

                    try

                    {

                        app.Visible = false;

                        app.ScreenUpdating = false;

                        app.DisplayAlerts = false;

                        //

                        string execPath = Path.Combine(txtFolderPath.Text, (String)listFiles.Items[j]);

                        book = app.Workbooks.Open(execPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value);

                        int count = book.Worksheets.Count;

                        string lineinfo = "";

                        for (int i = 1; i <= count; i++)

                        {

                            sheet = (Worksheet)book.Worksheets[i];

                            range = sheet.get_Range("A4", Missing.Value);

                            String rangevalue = (String)range.Value2;

                            rangevalue = rangevalue.Trim();

                            if ("产品批号".Equals(rangevalue))

                            {

                                //读取产品批号

                                lineinfo = "";

                                range = sheet.get_Range("B4", Missing.Value);

                                lineinfo += range.Value2 + "/t";

                                //产品规格

                                range = sheet.get_Range("D4", Missing.Value);

                                lineinfo += range.Value2 + "/t";

                                //颜色

                                range = sheet.get_Range("B5", Missing.Value);

                                lineinfo += range.Value2 + "/t";

                                //抽样日期

                                range = sheet.get_Range("B6", Missing.Value);

                                lineinfo += range.Value2 + "/r/n";

                                //

                                txtOutput.Text = txtOutput.Text + lineinfo;

                            }

                        }

                        txtOutput.Text = txtOutput.Text + "/r/n";

                        //

                        range = null;

                        sheet = null;

                        if (book != null)

                            book.Close(false, Missing.Value, Missing.Value);

                        book = null;

                    }

                    catch (Exception exp)

                    {

                    }

                    //

                    progressBar1.Value = j+1;

                }

 

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message);

            }

            finally

            {

                range = null;

                sheet = null;

                if (book != null)

                    book.Close(false, Missing.Value, Missing.Value);

                book = null;

                if (app != null)

                    app.Quit();

                app = null;

            }

        }

    }

}