当前位置:首页 ? 其他

c# excel工作表及cell读取

2010-12-19 20:13 本站整理 浏览(8)

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

}

}

}

}