内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用
如何使用C#读取Excel文件?我打开一个Excel文件来读取并复制到剪贴板上搜索电子邮件格式,但是我不知道如何去做。
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(); } } } }
你可以用Microsoft.Office.Interop.Excel
程序集处理EXCEL文件。
Add reference
...。添加Microsoft.Office.Interop.Excel程序集。using Microsoft.Office.Interop.Excel;
利用装配下面是示例代码:
using Microsoft.Office.Interop.Excel; //create the Application object we can use in the member functions. Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application(); _excelApp.Visible = true; string fileName = "C:\\sampleExcelFile.xlsx"; //open the workbook Workbook workbook = _excelApp.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //select the first sheet Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; //find the used range in worksheet Range excelRange = worksheet.UsedRange; //get an object array of all of the cells in the worksheet (their values) object[,] valueArray = (object[,])excelRange.get_Value( XlRangeValueDataType.xlRangeValueDefault); //access the cells for (int row = 1; row <= worksheet.UsedRange.Rows.Count; ++row) { for (int col = 1; col <= worksheet.UsedRange.Columns.Count; ++col) { //access each cell Debug.Print(valueArray[row, col].ToString()); } } //clean up stuffs workbook.Close(false, Type.Missing, Type.Missing); Marshal.ReleaseComObject(workbook); _excelApp.Quit(); Marshal.FinalReleaseComObject(_excelApp);
ExcelVSTO编程中最难理解的概念之一是,不像数组那样引用单元格,Worksheet[0][0]
不会给你单元格A1,它会错误地出现在你身上。即使在打开Excel时键入A1,实际上也是将数据输入范围A1。因此,可以将单元格称为Range。下面是一个例子:
Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet; Excel.Range range = sheet.get_Range("A1", Missing.Value)
现在可以按字面顺序键入:
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)
如果你想做这样的事:
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 }
也许有更好的方法,但这对我有用。
你需要使用的原因Value2
而不是Value
是因为Value
属性是参数化的,C#还不支持它们。
注:
Visibility
属性Excel不显示,这可能会使用户感到不安,但是如果想要直接提取数据,那可能就足够了。以下是一个完整的示例:
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); } } } }
明天我会发布ExcelTools的函数,我也没有那个代码。
编辑:正如承诺的,下面是您可能需要的ExcelTools函数。
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; } }
坦率地说,如果你使用VB.NET,这个东西就容易多了。它是用C#写的,因为它不是我写的。NET很好地选择参数,C#没有,因此类型错误。
下面是一个例子
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
下面是另一个
range = sheet.Cells.Find("Value to find", Type.Missing, Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false, false, Type.Missing);
它有助于理解参数。