本文实例讲述了C#基于COM方式读取Excel表格的方法。分享给大家供大家参考，具体如下：
using System; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Collections; //TestEnviroment:VS2013Update4 Excel2007 //Read by COM Object namespace SmartStore.LocalModel { public class ExcelTable { private string _path; public ExcelTable() { _path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; _path += "条码对照表.xls"; } public void ReadEPC2BarCode(out ArrayList arrayPI) { DataTable dt = ReadSheet(2); arrayPI = new ArrayList(); foreach (DataRow dr in dt.Rows) { EPC2BarCode eb = new EPC2BarCode(); eb.EPC = (string)dr["epcID"]; eb.Barcode = (string)dr["条形码"]; eb.EPC = eb.EPC.Trim(); eb.Barcode = eb.Barcode.Trim(); if (eb.EPC == null || eb.EPC.Length <= 0) break; arrayPI.Add(eb); } } public void ReadProductInfo(out ArrayList arrayPI) { DataTable dt = ReadSheet(1); arrayPI = new ArrayList(); foreach (DataRow dr in dt.Rows) { ProductInfo pi = new ProductInfo(); pi.Name = (string)dr["商品名称"]; pi.SN = (string)dr["商品编号"]; pi.BarCode = (string)dr["商品条码"]; pi.Brand = (string)dr["品牌"]; pi.Color = (string)dr["颜色"]; pi.Size = (string)dr["尺码"]; pi.Name = pi.Name.Trim(); pi.SN = pi.SN.Trim(); pi.BarCode = pi.BarCode.Trim(); pi.Brand = pi.Brand.Trim(); pi.Color = pi.Color.Trim(); pi.Size = pi.Size.Trim(); if (pi.Name == null || pi.Name.Length <= 0) break; arrayPI.Add(pi); } } private DataTable ReadSheet(int indexSheet) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Sheets sheets; Microsoft.Office.Interop.Excel.Workbook workbook = null; object oMissiong = System.Reflection.Missing.Value; System.Data.DataTable dt = new System.Data.DataTable(); try { workbook = app.Workbooks.Open(_path, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); //将数据读入到DataTable中——Start sheets = workbook.Worksheets; //输入1, 读取第一张表 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(indexSheet); if (worksheet == null) return null; string cellContent; int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Range range; //负责列头Start DataColumn dc; int ColumnID = 1; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]; while (range.Text.ToString().Trim() != "") { dc = new DataColumn(); dc.DataType = System.Type.GetType("System.String"); dc.ColumnName = range.Text.ToString().Trim(); dt.Columns.Add(dc); range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID]; } //End for (int iRow = 2; iRow <= iRowCount; iRow++) { DataRow dr = dt.NewRow(); for (int iCol = 1; iCol <= iColCount; iCol++) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol]; cellContent = (range.Value2 == null) " : range.Text.ToString(); //if (iRow == 1) //{ // dt.Columns.Add(cellContent); //} //else //{ dr[iCol - 1] = cellContent; //} } //if (iRow != 1) dt.Rows.Add(dr); } //将数据读入到DataTable中——End return dt; } catch { return null; } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); } } } }
希望本文所述对大家C#程序设计有所帮助。
【C#基于COM方式读取Excel表格的方法】相关文章：