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