C#导入Excel数据的两种方式
方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等
///
/// 导入数据到数据集中
/// 备注:此种方法只支持excel原文件
///
///
///
///
public static System.Data.DataTable InputExcel(string Path, ref string exceptionMsg)
{
System.Data.DataTable dt = null;
try
{
string strConn =
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
sheet[i] =
sheetDt.Rows[i][
}
string strExcel = string.Format(
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 =
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
{
newRow = tempdt.NewRow();
for (int j = 1; j
_wSheet.UsedRange.Columns.Count; j++)
{
if (i == 2 && j == 1)
{
//表头
for (int k = 1; k
{
string str = (_wSheet.UsedRange[1, k] as Range).Value2.ToString();
newColumn = new DataColumn(str);
newRow.Table.Columns.Add(newColumn);
}
}
Range range = _wSheet.Cells[i, j] as Range;
if (range != null
&& !
{
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(
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
C#导入Excel数据的两种方式
方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等
///
/// 导入数据到数据集中
/// 备注:此种方法只支持excel原文件
///
///
///
///
public static System.Data.DataTable InputExcel(string Path, ref string exceptionMsg)
{
System.Data.DataTable dt = null;
try
{
string strConn =
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
sheet[i] =
sheetDt.Rows[i][
}
string strExcel = string.Format(
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 =
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
{
newRow = tempdt.NewRow();
for (int j = 1; j
_wSheet.UsedRange.Columns.Count; j++)
{
if (i == 2 && j == 1)
{
//表头
for (int k = 1; k
{
string str = (_wSheet.UsedRange[1, k] as Range).Value2.ToString();
newColumn = new DataColumn(str);
newRow.Table.Columns.Add(newColumn);
}
}
Range range = _wSheet.Cells[i, j] as Range;
if (range != null
&& !
{
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(
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