1. OleDb方式 [using System.Data.OleDb]
Office 2007及以上版本需要使用"Microsoft.ACE.OLEDB.12.0" 的连接字符串,Office 2003及以下版本需要使用"Microsoft.Jet.OLEDB.4.0"。
public IList<ReportItem> LoadExcelFile(string excelFileName)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; //Sql statement
//string strExcel = string.Format("select * from [{0}$]", strSheetName);
string query = "select * from [sheet1$]";
List<ReportItem> reportList = new List<ReportItem>();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbCommand myCommand = new OleDbCommand(query, conn);
conn.Open();
OleDbDataReader rdr = myCommand.ExecuteReader();
// Always call Read before accessing data.
while (rdr.Read())
{
ReportItem item = new ReportItem()
{
LFileName = rdr.GetString(0),
RFileName = rdr.GetString(1),
ResID = rdr.GetString(2),
CoID = rdr.GetString(3),
SouString = rdr.IsDBNull(4) == true ? string.Empty : rdr.GetString(4),
TaString = rdr.IsDBNull(5) == true ? string.Empty : rdr.GetString(5),
Cmts = rdr.GetString(6),
Cul = rdr.GetString(7),
TarCul = rdr.GetString(7),
ModifiedDate = rdr.IsDBNull(8) == true ? null : rdr.GetDateTime(8).ToShortDateString(),
Rul = rdr.GetString(9),
Mes = rdr.GetString(10),
};
reportList.Add(item);
}
rdr.Close();
}
return reportList;
}
2. Microsoft.Office.Interop.Excel
public IList<ReportItem> LoadExcelFile(string excelFileName)
{
List<ReportItem> reportList = new List<ReportItem>();
string RunDateTime = DateTime.UtcNow.ToLongDateString();
Excel.Application excel = null;
Excel.Workbooks wbs = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
Excel.Range range1 = null;
try
{
excel = new Excel.Application();
//excel.UserControl = true;
excel.DisplayAlerts = false;
wb = excel.Application.Workbooks.Open(excelFileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
ws = wb.Worksheets["Sheet1"] as Excel.Worksheet;
int rowCount = ws.UsedRange.Cells.Rows.Count;
//int colCount = ws.UsedRange.Cells.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
range1 = ws.Cells.get_Range("A" + i.ToString(), "K" + i.ToString());
ReportItem item = new ReportItem()
{
LFileName = ((Excel.Range)range1.Cells[1, 1]).Value.ToString(),
RFileName = ((Excel.Range)range1.Cells[1, 2]).Value.ToString(),
ResID = ((Excel.Range)range1.Cells[1, 3]).Value.ToString(),
ComResID = ((Excel.Range)range1.Cells[1, 4]).Value.ToString(),
SouString = ((Excel.Range)range1.Cells[1, 5]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 5]).Value.ToString(),
TarString = ((Excel.Range)range1.Cells[1, 6]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 6]).Value.ToString(),
Cos = ((Excel.Range)range1.Cells[1, 7]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 7]).Value.ToString(),
Cul = ((Excel.Range)range1.Cells[1, 8]).Value.ToString(),
TarCulture = ((Excel.Range)range1.Cells[1, 8]).Value.ToString(),
ModifiedDate = ((Excel.Range)range1.Cells[1, 9]).Value.ToString(),
Ru = ((Excel.Range)range1.Cells[1, 10]).Value.ToString(),
Mes = ((Excel.Range)range1.Cells[1, 11]).Value.ToString(),
};
reportList.Add(item);
}
return reportList;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (excel != null)
{
if (wbs != null)
{
if (wb != null)
{
if (ws != null)
{
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
}
wb.Close(false, Nothing, Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
}
excel.Application.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
}
}