EXCEL 2010
来源:互联网 发布:美国矩阵风投官网 编辑:程序博客网 时间:2024/06/06 01:22
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.Sql;
public partial class xml_Excel : System.Web.UI.Page
{
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
public static string path = @"D:\QTP\WebSite\Webs\example.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Create connection object by using the preceding connection string.
OleDbConnection odc = new OleDbConnection(connStr);
// Open connection with the database.
odc.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [dogs$]", odc);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ds");
// Bind data to DataGrid control.
this.GridView1.DataSource = objDataset1;
GridView1.DataBind();
// Clean up objects.
odc.Close();
}
}
///// <summary>
///// 获取指定路径、指定工作簿名称的Excel数据:取第一个sheet的数据
///// </summary>
///// <param name="FilePath">文件存储路径</param>
///// <param name="WorkSheetName">工作簿名称</param>
///// <returns>如果争取找到了数据会返回一个完整的Table,否则返回异常</returns>
//public DataTable GetExcelData(string astrFileName)
//{
// string strSheetName = GetExcelWorkSheets(astrFileName)[0].ToString();
// return GetExcelData(astrFileName, strSheetName);
//}
///// <summary>
///// 返回指定文件所包含的工作簿列表;如果有WorkSheet,就返回以工作簿名字命名的ArrayList,否则返回空
///// </summary>
///// <param name="strFilePath">要获取的Excel</param>
///// <returns>如果有WorkSheet,就返回以工作簿名字命名的ArrayList,否则返回空</returns>
//public ArrayList GetExcelWorkSheets(string strFilePath)
//{
// ArrayList alTables = new ArrayList();
// OleDbConnection odn = new OleDbConnection(GetExcelConnection(strFilePath));
// odn.Open();
// DataTable dt = new DataTable();
// dt = odn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// if (dt == null)
// {
// throw new Exception("无法获取指定Excel的架构。");
// }
// foreach (DataRow dr in dt.Rows)
// {
// string tempName = dr["Table_Name"].ToString();
// int iDolarIndex = tempName.IndexOf('$');
// if (iDolarIndex > 0)
// {
// tempName = tempName.Substring(0, iDolarIndex);
// }
// //修正了Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
// if (tempName[0] == '\'')
// {
// if (tempName[tempName.Length - 1] == '\'')
// {
// tempName = tempName.Substring(1, tempName.Length - 2);
// }
// else
// {
// tempName = tempName.Substring(1, tempName.Length - 1);
// }
// }
// if (!alTables.Contains(tempName))
// {
// alTables.Add(tempName);
// }
// }
// odn.Close();
// if (alTables.Count == 0)
// {
// return null;
// }
// return alTables;
//}
/// <summary>
/// 获取指定路径、指定工作簿名称的Excel数据
/// </summary>
/// <param name="FilePath">文件存储路径</param>
/// <param name="WorkSheetName">工作簿名称</param>
/// <returns>如果争取找到了数据会返回一个完整的Table,否则返回异常</returns>
public DataTable GetExcelData(string FilePath, string WorkSheetName)
{
DataTable dtExcel = new DataTable();
OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName + "$]", con);
//读取
con.Open();
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
con.Close();
dtExcel.TableName = WorkSheetName;
//返回
return dtExcel;
}
/// <summary>
/// 获取链接字符串
/// </summary>
/// <param name="strFilePath"></param>
/// <returns></returns>
public string GetExcelConnection(string strFilePath)
{
if (!File.Exists(strFilePath))
{
throw new Exception("指定的Excel文件不存在!");
}
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";
//@"Provider=Microsoft.Jet.OLEDB.4.0;" +
//@"Data Source=" + strFilePath + ";" +
//@"Extended Properties=" + Convert.ToChar(34).ToString() +
//@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
}
}
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.Sql;
public partial class xml_Excel : System.Web.UI.Page
{
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
public static string path = @"D:\QTP\WebSite\Webs\example.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Create connection object by using the preceding connection string.
OleDbConnection odc = new OleDbConnection(connStr);
// Open connection with the database.
odc.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [dogs$]", odc);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ds");
// Bind data to DataGrid control.
this.GridView1.DataSource = objDataset1;
GridView1.DataBind();
// Clean up objects.
odc.Close();
}
}
///// <summary>
///// 获取指定路径、指定工作簿名称的Excel数据:取第一个sheet的数据
///// </summary>
///// <param name="FilePath">文件存储路径</param>
///// <param name="WorkSheetName">工作簿名称</param>
///// <returns>如果争取找到了数据会返回一个完整的Table,否则返回异常</returns>
//public DataTable GetExcelData(string astrFileName)
//{
// string strSheetName = GetExcelWorkSheets(astrFileName)[0].ToString();
// return GetExcelData(astrFileName, strSheetName);
//}
///// <summary>
///// 返回指定文件所包含的工作簿列表;如果有WorkSheet,就返回以工作簿名字命名的ArrayList,否则返回空
///// </summary>
///// <param name="strFilePath">要获取的Excel</param>
///// <returns>如果有WorkSheet,就返回以工作簿名字命名的ArrayList,否则返回空</returns>
//public ArrayList GetExcelWorkSheets(string strFilePath)
//{
// ArrayList alTables = new ArrayList();
// OleDbConnection odn = new OleDbConnection(GetExcelConnection(strFilePath));
// odn.Open();
// DataTable dt = new DataTable();
// dt = odn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// if (dt == null)
// {
// throw new Exception("无法获取指定Excel的架构。");
// }
// foreach (DataRow dr in dt.Rows)
// {
// string tempName = dr["Table_Name"].ToString();
// int iDolarIndex = tempName.IndexOf('$');
// if (iDolarIndex > 0)
// {
// tempName = tempName.Substring(0, iDolarIndex);
// }
// //修正了Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
// if (tempName[0] == '\'')
// {
// if (tempName[tempName.Length - 1] == '\'')
// {
// tempName = tempName.Substring(1, tempName.Length - 2);
// }
// else
// {
// tempName = tempName.Substring(1, tempName.Length - 1);
// }
// }
// if (!alTables.Contains(tempName))
// {
// alTables.Add(tempName);
// }
// }
// odn.Close();
// if (alTables.Count == 0)
// {
// return null;
// }
// return alTables;
//}
/// <summary>
/// 获取指定路径、指定工作簿名称的Excel数据
/// </summary>
/// <param name="FilePath">文件存储路径</param>
/// <param name="WorkSheetName">工作簿名称</param>
/// <returns>如果争取找到了数据会返回一个完整的Table,否则返回异常</returns>
public DataTable GetExcelData(string FilePath, string WorkSheetName)
{
DataTable dtExcel = new DataTable();
OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName + "$]", con);
//读取
con.Open();
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
con.Close();
dtExcel.TableName = WorkSheetName;
//返回
return dtExcel;
}
/// <summary>
/// 获取链接字符串
/// </summary>
/// <param name="strFilePath"></param>
/// <returns></returns>
public string GetExcelConnection(string strFilePath)
{
if (!File.Exists(strFilePath))
{
throw new Exception("指定的Excel文件不存在!");
}
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";
//@"Provider=Microsoft.Jet.OLEDB.4.0;" +
//@"Data Source=" + strFilePath + ";" +
//@"Extended Properties=" + Convert.ToChar(34).ToString() +
//@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
}
}
- EXCEL 2010
- java 操作 excel 2010
- ControlNotFoundException in Excel 2010
- excel 2010 插入时间
- Excel 2010 拆分窗口
- Excel 2010去掉网格线
- Excel 2010使用记录
- Excel 2010 编辑公式
- Excel 2010总结
- 【Excel-2010】瀑布图
- 【Excel-2010】VLOOKUP函数
- 【Excel-2010】日期函数
- Microsoft Excel 2010 直方图
- Excel
- Excel
- ExCel
- excel
- Excel
- 使用cocoapods管理iOS第三方库
- oracle 创建自增表
- hdu 1045 Fire Net-二分匹配
- linux下andorid-ndk安装
- Win7 64 ADS1.2 编译弹出Error: L6218E: Undefined symbol sprintf (referred from 2440...)
- EXCEL 2010
- 【Learning】TimeZone Issue Summary
- MSF命名集合
- OpenSessionInViewFilter 的配置及作用
- hdu 1045 Fre Net
- Hibernate中lazy,inverse,cascade,fetch
- 在Eclipse中统计项目的代码行数
- 安卓中文SDK连载(6)--触控响应
- MySQL 学习笔记 一