110,537
社区成员
发帖
与我相关
我的任务
分享
private DataSet 读取微软表格文件(string 文档名)
{
DataSet 读取表返回 = new DataSet();
读取 = new Thread(delegate()
{
string 微软表格 = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + 文档名 + "; Extended Properties = \"Excel 12.0 Xml; HDR = No\"";
OleDbConnection 表格文件 = new OleDbConnection(微软表格);
表格文件.Open();
string 读取数据 = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter 连接表格 = new OleDbDataAdapter(读取数据, 表格文件);
连接表格.Fill(读取表返回, "读取Excel数据");
表格文件.Close();
}); 读取.Start();
return 读取表返回;
}
/// <summary>
/// 解析Excel,返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int a = ws.Rows.Count;
int b = ws.Columns.Count;
string name = ws.Name;
for (int i = 1; i <RowCount; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= ColumnsCount; j++)
{
Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:Excel格式不正确!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
}
<span style="color:#ff0000;">-------------------------------------------<span style="font-size:24px;">方法二</span></span>
/// <summary>
/// 解析Excel,根据OleDbConnection直接连Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=No\"";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}