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