C#基于COM方式读取Excel表格的方法

本文实例讲述了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#操作Excel技巧总结》、《C#程序设计之线程使用技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》

希望本文所述对大家C#程序设计有所帮助。

以上是 C#基于COM方式读取Excel表格的方法 的全部内容, 来源链接: utcz.com/z/314650.html

回到顶部