Method for C to read Excel table based on COM mode
- 2021-10-24 23:27:05
- OfStack
This paper illustrates the method of C # reading Excel table based on COM. Share it for your reference, as follows:
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 += " Barcode comparison table .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[" Bar code "];
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[" Commodity name "];
pi.SN = (string)dr[" Commodity number "];
pi.BarCode = (string)dr[" Commodity bar code "];
pi.Brand = (string)dr[" Brand "];
pi.Color = (string)dr[" Color "];
pi.Size = (string)dr[" Size "];
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);
// Read data into the DataTable Middle- Start
sheets = workbook.Worksheets;
// Input 1, Read the 1 Table
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;
// Responsible for column header 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);
}
// Read data into the DataTable Middle- 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();
}
}
}
}
For more readers interested in C # related content, please check the topics on this site: "Summary of C # Operating Excel", "Summary of C # Programming Thread Use Skills", "Summary of XML File Operation Skills in C #", "C # Common Control Usage Tutorial", "WinForm Control Usage Tutorial", "C # Data Structure and Algorithm Tutorial", "C # Array Operation Skills Summary" and "C # Object-Oriented Programming Introduction Tutorial"
I hope this article is helpful to everyone's C # programming.