using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Runtime.InteropServices;
using System.Data.OleDb;
namespace MyHelper
{
/// 说明如下注释的是另一种方法。。。大多数情况下能用一般要求要安装office2003
// 报错情况下要求要组件注册如果。你的组件服务实在启动不了。又找不到其他方法建议选择现在这种方法
public class ExcelOper
{
[DllImport("kernel32")]
public static extern void GetWindowsDirectory(System.Text.StringBuilder WinDir, int count);
/// <summary>
/// 在指定的ComboBox中帮定某个Excel文件中的Sheet页面名称
/// </summary>
/// <param name="frm">窗体</param>
/// <param name="cb">ComboBox</param>
/// <param name="ExelFileName">Excel文件全路径名</param>
public static void FillExcelSheetToCombo(System.Web.UI.WebControls.DropDownList ddl, string ExelFileName)
{
try
{
ddl.Items.Clear();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExelFileName + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.DataTable tb = new System.Data.DataTable();
tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
for (int k = 0; k < tb.Rows.Count; k++)
{
ddl.Items.Add(tb.Rows[k]["TABLE_NAME"].ToString().Replace("'", "").Replace("$", ""));
}
//Microsoft.Office.Interop.Excel = Excel
//Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//app.Visible = false;
//Microsoft.Office.Interop.Excel.Workbook wkb = app.Workbooks.Open(ExelFileName,
// 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
// );
//foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wkb.Sheets)
//{
// ddl.Items.Add(sheet.Name);
//}
//app.Quit();
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.Message+ex.StackTrace);
}
}
/// </summary>
/// <param name="ExcelFileName">Excel文件</param>
/// <param name="SheetName">Sheet名</param>
/// <returns>null:出错 ,否则返回DataTable</returns>
public static System.Data.DataTable ExcelToDSStuct(string ExcelFileName, string SheetName)
{
Microsoft.Office.Interop.Excel.Application app = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.DataTable tb = new System.Data.DataTable();
string strExcel = "select * from [" + SheetName + "$] ";
System.Data.OleDb.OleDbDataAdapter adpt = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
adpt.Fill(tb);
conn.Close();
return tb;
//app = new Microsoft.Office.Interop.Excel.Application();
//app.Visible = false;
打开Excel文件
//Microsoft.Office.Interop.Excel.Workbook wkb = app.Workbooks.Open(ExcelFileName,
// 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
// );
//Microsoft.Office.Interop.Excel.Worksheet sht = null;
//for (int i = 1; i <= wkb.Sheets.Count; i++)
// if (((Microsoft.Office.Interop.Excel.Worksheet)wkb.Sheets[i]).Name == SheetName)
// sht = (Microsoft.Office.Interop.Excel.Worksheet)wkb.Sheets[i];
//if (sht == null)
//{
// app.Quit();
// return null;
//}
生成Table
//System.Data.DataTable tb = new System.Data.DataTable();
生成Table表头
//for (int i = 1; i <= sht.UsedRange.Columns.Count; i++)
//{
// tb.Columns.Add(((Microsoft.Office.Interop.Excel.Range)sht.Cells[1, i]).Text.ToString(), "".GetType());
//}
填写数据
//for (int i = 2; i <= sht.UsedRange.Rows.Count; i++)
//{
// System.Data.DataRow r = tb.Rows.Add();
// for (int j = 1; j <= sht.UsedRange.Columns.Count; j++)
// {
// Microsoft.Office.Interop.Excel.Range c = (Microsoft.Office.Interop.Excel.Range)sht.Cells[i, j];
// // c.NumberFormat = "@";
// r[j - 1] = c.Text.ToString();
// }
//}
//app.Quit();
//return tb;
}
catch
{
if (app != null)
app.Quit();
return null;
}
}
}
}