using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
namespace Action.Common
{
public class OfficeUtil
{
/// <summary>
/// 使用OLEDB的方式读EXCEL文件内容,返回DataSet集合
/// </summary>
/// <param name="filename">EXCEL文件名,包含完整路径</param>
/// <param name="hasTitle">EXCEL文件第一行是否是标题,true表示第一行是标题</param>
/// <param name="version">OLEDB使用的EXCEL连接的版本,请输入"8.0"</param>
/// <returns>DataSet</returns>
public static DataSet ExcelRead(string filename, bool hasTitle, string tableName, string version)
{
string titleStr = hasTitle ? "Yes" : "No";
if (string.IsNullOrEmpty(version))
{
version = "8.0";
}
if (string.IsNullOrEmpty(tableName))
{
tableName = "[Sheet1$]";
}
else if (tableName.IndexOf("{1}quot;) == -1)
{
tableName ="["+tableName+ "{1}quot;+"]";
}
string connStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0.0;Data Source={0};Extended Properties='Excel {1};HDR={2};IMEX=1'", filename, version, titleStr);
DataSet ds = null;
using (OleDbConnection conn = new OleDbConnection(connStr))
{
try
{
conn.Open();
string cmdStr = "select * from " + tableName;
OleDbDataAdapter da = new OleDbDataAdapter(cmdStr, connStr);
ds = new DataSet();
da.Fill(ds, "table1");
}
catch
{
if (!conn.State.ToString().ToLower().Equals("open"))
{
conn.Open();
}
tableName = filename.Substring(filename.LastIndexOf("/") + 1, filename.Length - filename.LastIndexOf("/") - 1);
tableName = "[" + tableName.Substring(0, tableName.IndexOf(".")) + "$]";
string cmdStr = "select * from " + tableName;
OleDbDataAdapter da = new OleDbDataAdapter(cmdStr, connStr);
ds = new DataSet();
da.Fill(ds, "table1");
}
finally
{
if (conn.State.ToString().ToLower().Equals("open"))
{
conn.Close();
}
}
}
return ds;
}
/// <summary>
/// 使用OFFICE提供的方式读EXCEL文件内容,返回DataSet集合
/// </summary>
/// <param name="filename">EXCEL文件名,包含完整路径</param>
/// <param name="hasTitle">EXCEL文件第一行是否是标题,true表示第一行是标题</param>
/// <returns>DataSet</returns>
public static DataSet ExcelRead(string filename, bool hasTitle)
{
Excel.Application app = new Excel.Application();
if (null == app)
{
return null;
}
Excel.Workbook wb = null;
try
{
wb = app.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch
{
app.Quit();
app = null;
return null;
}
Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
if (null == ws)
{
wb = null;
app.Quit();
app = null;
return null;
}
int rowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.Columns.Count;
if ((hasTitle && rowCount < 2) || (!hasTitle && rowCount < 1))
{
return null;
}
int rowIndexBegin = 1;
int colIndexBegin = 1;
string cell1 = "A1";
string cell2Column = ((char)((colCount % 26) + 64)).ToString();
string cell2Row = rowCount.ToString();
string cell2 = cell2Column + cell2Row;
Excel.Range range = ws.get_Range(cell1, cell2);
DataTable dt = new DataTable("table1");
for (int j = colIndexBegin; j < colCount + 1; j++)
{
if (hasTitle)
{
Excel.Range r = range[rowIndexBegin, j] as Excel.Range;
dt.Columns.Add(r.Value2.ToString());
}
else
{
dt.Columns.Add("col" + j.ToString());
}
}
if (hasTitle)
{
rowIndexBegin++;
}
for (int i = rowIndexBegin; i < rowCount + 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = colIndexBegin; j < colCount + 1; j++)
{
Excel.Range r = range[i, j] as Excel.Range;
dr[j - 1] = r.Value2;
}
dt.Rows.Add(dr);
}
ws = null;
wb = null;
app.Quit();
app = null;
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
}
}
导入Excel数据!
最新推荐文章于 2024-09-10 14:14:32 发布