c# excel工作表及cell读取 .

转发至：http://blog.youkuaiyun.com/chuangxin/article/details/6085577

    昨天晚上做了一回雷锋，帮朋友写了个程序，把他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;             }         }     } }