如何阅读使用C＃的Excel文件的数据？ [英] How to read data of an Excel file using C#?
问题描述
如何阅读使用C＃的Excel文件？我打开读取Excel文件并把它复制到剪贴板，以搜索电子邮件格式，但我不知道该怎么做。
的FileInfo finfo;
Excel.ApplicationClass ExcelObj =新Excel.ApplicationClass（）;
ExcelObj.Visible = FALSE;Excel.Workbook theWorkbook;
Excel.Worksheet工作;如果（listView1.Items.Count大于0）
{
的foreach（ListViewItem的S IN listView1.Items）
{
finfo =新的FileInfo（s.Text）;
如果（finfo.Extension ==的.xls|| finfo.Extension ==的.xlsx|| finfo.Extension ==名为.xlt|| finfo.Extension ==.XLSM|| finfo.Extension = =名为.csv）
{
theWorkbook = ExcelObj.Workbooks.Open（s.Text，0，真实，5，，，真实，Excel.XlPlatform.xlWindows，\\ t的，假的，假的，0，真的，假的，假的）; 对于（诠释计数= 1;计数＆LT; = theWorkbook.Sheets.Count;计数++）
{
工作表=（Excel.Worksheet）theWorkbook.Worksheets.get_Item（计数）;
worksheet.Activate（）;
worksheet.Visible = FALSE;
worksheet.UsedRange.Cells.Select（）;
}
}
}
}
OK，
一个更困难的概念掌握有关Excel VSTO编程，你不引用单元格状阵列，表[0] [0] 荣获' ŧ给你单元格A1，它会报错了你。甚至当你键入A1当Excel打开时，你实际上是将数据输入范围A1。因此，你是指细胞作为指定范围。这里有一个例子：
Excel.Worksheet表= workbook.Sheets [工作表Sheet1]作为Excel.Worksheet;
Excel.Range范围= sheet.get_Range（A1，Missing.Value）
您现在可以从字面上键入：
range.Text //这会给你的文本，用户可以看到
range.Value2 //这会给你用Excel存储的实际值（不四舍五入）
如果你想要做这样的事情：
Excel.Range范围= sheet.get_Range（A1：A5，Missing.Value）如果（范围1！= NULL）
的foreach（Excel.Range R IN范围1）
{
字符串用户= r.Text
字符串值= r.Value2 }
有可能是一个更好的办法，但是这已经为我工作。
您需要使用值2 ，而不是值的原因是因为值属性是一个参数化和C＃不支持他们呢。
至于清理code，我会后，当我去上班，明天我没有code。与我，但它是非常的样板。你只是关闭并释放您创建它们相反的顺序对象。你不能使用使用（）块，因为一个Excel.Application或Excel.Workbook不执行的IDisposable ，如果你不清理，你将留下内存中的悬挂Excel对象。
请注意：
- 如果您没有设置
能见度属性Excel中不显示，这可能是令人不安的用户，但如果你想只抓取出来的数据，这可能是不够好 - 您可以OLEDB中，将工作过。
我希望让你开始，让我知道如果你需要进一步澄清。我会发布一个完整的
下面是一个完整的示例：
使用系统;
使用System.IO;
使用的System.Reflection;
使用NUnit.Framework;
使用ExcelTools = Ms.Office;
使用Excel =的Microsoft.Office.Interop.Excel;命名空间测试
{
[的TestFixture]
公共类ExcelSingle
{
[测试]
公共无效ProcessWorkbook（）
{
字符串文件= @C：\\用户\\克里斯\\桌面\\ TestSheet.xls
Console.WriteLine（文件）; Excel.Application的Excel = NULL;
Excel.Workbook WKB = NULL; 尝试
{
Excel的=新Excel.Application（）; WKB = ExcelTools.OfficeUtil.OpenBook（EXCEL，文件）; Excel.Worksheet表= wkb.Sheets [数据]作为Excel.Worksheet; Excel.Range范围= NULL; 如果（表！= NULL）
范围= sheet.get_Range（A1，Missing.Value）; 字符串A1 =的String.Empty; 如果（范围！= NULL）
A1 = range.Text.ToString（）; Console.WriteLine（A1值：{0}，A1）; }
赶上（异常前）
{
//如果你需要处理的东西
Console.WriteLine（ex.Message）;
}
最后
{
如果（WKB！= NULL）
ExcelTools.OfficeUtil.ReleaseRCM（WKB）; 如果（EXCEL！= NULL）
ExcelTools.OfficeUtil.ReleaseRCM（EXCEL）;
}
}
}
}
我会后从明天ExcelTools的功能，我没有那个code。与我满意。
编辑：
如所承诺的，这里有来自ExcelTools的功能，您可能需要。
公共静态Excel.Workbook的OpenBook（Excel.Application excelInstance，字符串文件名，布尔只读，已bool的编辑，
布尔updateLinks）{
Excel.Workbook书= excelInstance.Workbooks.Open（
文件名，updateLinks，只读，
Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，
Type.Missing，编辑，Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type.Missing）;
返回书;
}公共静态无效ReleaseRCM（对象o）{
尝试{
System.Runtime.InteropServices.Marshal.ReleaseComObject（O）;
} {抓
} {最后
O = NULL;
}
}
要坦率地说，这东西是容易得多，如果你使用VB.NET。这是在C＃中因为我没有写。 VB.NET确实选项参数良好，C＃不，因此Type.Missing。一旦你键入的连续两次Type.Missing，您从房间尖叫！
至于你的问题，你可以尝试以下操作：
<一个href=\"http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(VS.80).aspx\">http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(VS.80).aspx
我将发布一个例子，当我从我开会回来......欢呼声
编辑：下面是一个例子。
=范围sheet.Cells.Find（值查找
Type.Missing，
Type.Missing，
Type.Missing，
Type.Missing，
Excel.XlSearchDirection.xlNext，
Type.Missing，
Type.Missing，Type.Missing）;range.Text; //给你找到的值
下面是此<一个启发另一个例子href=\"http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find%28VS.80%29.aspx\">site:
=范围sheet.Cells.Find（价值发现，Type.Missing， Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false,假的，Type.Missing）;
它有助于理解的参数。
P.S。我是那些谁喜欢学习COM自动化怪异的人之一。所有这一切code从工具我写的工作，这要求我在从实验室在每个星期一1000+ US preadsheets处理蒸熟。
How to read an Excel file using C#? I open an Excel file for reading and copy it to clipboard to search email format, but I don't know how to do it.
FileInfo finfo;
Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;
Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;
if (listView1.Items.Count > 0)
{
foreach (ListViewItem s in listView1.Items)
{
finfo = new FileInfo(s.Text);
if (finfo.Extension == ".xls" || finfo.Extension == ".xlsx" || finfo.Extension == ".xlt" || finfo.Extension == ".xlsm" || finfo.Extension == ".csv")
{
theWorkbook = ExcelObj.Workbooks.Open(s.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false);
for (int count = 1; count <= theWorkbook.Sheets.Count; count++)
{
worksheet = (Excel.Worksheet)theWorkbook.Worksheets.get_Item(count);
worksheet.Activate();
worksheet.Visible = false;
worksheet.UsedRange.Cells.Select();
}
}
}
}
OK,
One of the more difficult concepts to grasp about Excel VSTO programming is that you don't refer to cells like an array, Worksheet[0][0] won't give you cell A1, it will error out on you. Even when you type into A1 when Excel is open, you are actually entering data into Range A1. Therefore you refer to cells as Named Ranges. Here's an example:
Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet;
Excel.Range range = sheet.get_Range("A1", Missing.Value)
You can now literally type:
range.Text // this will give you the text the user sees
range.Value2 // this will give you the actual value stored by Excel (without rounding)
If you want to do something like this:
Excel.Range range = sheet.get_Range("A1:A5", Missing.Value)
if (range1 != null)
foreach (Excel.Range r in range1)
{
string user = r.Text
string value = r.Value2
}
There might be a better way, but this has worked for me.
The reason you need to use Value2 and not Value is because the Value property is a parametrized and C# doesn't support them yet.
As for the cleanup code, i will post that when i get to work tomorrow, i don't have the code with me, but it's very boilerplate. You just close and release the objects in the reverse order you created them. You can't use a Using() block because the Excel.Application or Excel.Workbook doesn't implement IDisposable, and if you don't clean-up, you will be left with a hanging Excel objects in memory.
Note:
- If you don't set the
Visibilityproperty Excel doesn't display, which can be disconcerting to your users, but if you want to just rip the data out, that is probably good enough - You could OleDb, that will work too.
I hope that gets you started, let me know if you need further clarification. I'll post a complete
here is a complete sample:
using System;
using System.IO;
using System.Reflection;
using NUnit.Framework;
using ExcelTools = Ms.Office;
using Excel = Microsoft.Office.Interop.Excel;
namespace Tests
{
[TestFixture]
public class ExcelSingle
{
[Test]
public void ProcessWorkbook()
{
string file = @"C:\Users\Chris\Desktop\TestSheet.xls";
Console.WriteLine(file);
Excel.Application excel = null;
Excel.Workbook wkb = null;
try
{
excel = new Excel.Application();
wkb = ExcelTools.OfficeUtil.OpenBook(excel, file);
Excel.Worksheet sheet = wkb.Sheets["Data"] as Excel.Worksheet;
Excel.Range range = null;
if (sheet != null)
range = sheet.get_Range("A1", Missing.Value);
string A1 = String.Empty;
if( range != null )
A1 = range.Text.ToString();
Console.WriteLine("A1 value: {0}", A1);
}
catch(Exception ex)
{
//if you need to handle stuff
Console.WriteLine(ex.Message);
}
finally
{
if (wkb != null)
ExcelTools.OfficeUtil.ReleaseRCM(wkb);
if (excel != null)
ExcelTools.OfficeUtil.ReleaseRCM(excel);
}
}
}
}
I'll post the functions from ExcelTools tomorrow, I don't have that code with me either.
Edit: As promised, here are the Functions from ExcelTools you might need.
public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
bool updateLinks) {
Excel.Workbook book = excelInstance.Workbooks.Open(
fileName, updateLinks, readOnly,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return book;
}
public static void ReleaseRCM(object o) {
try {
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
} catch {
} finally {
o = null;
}
}
To be frank, this stuff is much easier if you use VB.NET. It's in C# because I didn't write it. VB.NET does option parameters well, C# does not, hence the Type.Missing. Once you typed Type.Missing twice in a row, you run screaming from the room!
As for you question, you can try to following:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(VS.80).aspx
I will post an example when I get back from my meeting... cheers
Edit: Here is an example
range = sheet.Cells.Find("Value to Find",
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Excel.XlSearchDirection.xlNext,
Type.Missing,
Type.Missing, Type.Missing);
range.Text; //give you the value found
Here is another example inspired by this site:
range = sheet.Cells.Find("Value to find", Type.Missing, Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false, false, Type.Missing);
It helps to understand the parameters.
P.S. I'm one of those weird people who enjoys learning COM automation. All this code steamed from a tool I wrote for work which required me to process over 1000+ spreadsheets from the lab each Monday.
这篇关于如何阅读使用C＃的Excel文件的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
