(整理)C导入Excel数据的方式两种.

C#导入Excel数据的方式（两种）
方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等
/// <summary>
/// 导入数据到数据集中
/// 备注:此种方法只支持excel原文件
/// </summary>
/// <param name="Path">文件路劲</param>
/// <param name="exceptionMsg">异常信息</param>
/// <returns></returns>
public static System.Data.DataTable InputExcel(string Path, ref string exceptionMsg)
{
System.Data.DataTable dt = null;
try
{
string strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
System.Data.DataTable sheetDt =
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string[] sheet = new
string[sheetDt.Rows.Count];
for (int i = 0; i < sheetDt.Rows.Count; i++)
{
sheet[i] =
sheetDt.Rows[i]["TABLE_NAME"].ToString();
}
string strExcel = string.Format("select * from [{0}]", sheet[0]);
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new System.Data.DataTable();
myCommand.Fill(dt);
conn.Close();
}
}
catch (Exception ex)
{
exceptionMsg = ex.Message;
}
return dt;
}
方法二、读取Excel文件，然后根据里面的数据信息拼装
#region 读取Excel表格中数据到DataTable中
public static System.Data.DataTable ChangeExcelToDateTable(string _path)
{
System.Data.DataTable tempdt = new System.Data.DataTable();
tempdt.TableName = "Excel";
Application app = new Application();
object obj = System.Reflection.Missing.Value;
try
{
Workbook _wBook = app.Workbooks.Open(_path, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);
Worksheet _wSheet =
(Worksheet)_wBook.Worksheets.get_Item(1);
DataRow newRow = null;
DataColumn newColumn = null;
for (int i = 2; i <= _edRange.Rows.Count; i++)
{
newRow = tempdt.NewRow();
for (int j = 1; j <=
_edRange.Columns.Count; j++)
{
if (i == 2 && j == 1)
{
//表头
for (int k = 1; k <=
_edRange.Columns.Count; k++)
{
string str =
(_edRange[1, k] as Range).Value2.ToString();
newColumn = new DataColumn(str);
newRow.Table.Colu mns.Add(newColumn);
}
}
Range range = _wSheet.Cells[i, j] as Range;
if (range != null
&& !"".Equals(range.Text.ToString()))
{
newRow[j - 1] =
range.Value2;
}
}
tempdt.Rows.Add(newRow);
}
_wSheet = null;
_wBook = null;
app.Quit();
Kill(app);
int generation = System.GC.GetGeneration(app);
app = null;
System.GC.Collect(generation);
return tempdt;
}
catch (Exception ex)
{
app.Quit();
Kill(app);
int generation = System.GC.GetGeneration(app);
app = null;
throw ex;
}
}
#endregion
#region 结束进程
[DllImport("User32.dll", CharSet = CharSet.Auto)]
private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
private static void
Kill(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p =
System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k
} #endregion。