Asp.net操作Excel文件事例小结，基于ExtAspNet控件库
SQL查询Excel文件的语句：
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\hxl\gxjt\gxjt\pay\excel\list.xls;Extended Properties=Excel 8.0; HDR=YES;
IMEX=1')...Sheet1$
注：如遇到如下问题：
1）、已拒绝对 OLE DB 访问接口 'Microsoft.Jet.OLEDB.4.0' 的即席访问。必须通过链接服务器来访问此访问接口。
答：登录数据库用户必须具有sa权限
1.把Excel里的数据转换为DataSet
方法一：
/// <summary>
/// 把Excel里的数据转换为DataSet，并返回DataSet,缺点：当Excel文件中有两列的名称一样，则第二列起名称自动在末尾加‘1’
/// </summary>
/// <param name="filenameurl">服务器上的路径</param>
/// <param name="sheetname">sheet名称</param>
/// <returns></returns>
public DataSet ExecleToDataSet(string filenameurl, string sheetname)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + sheetname + "$]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, sheetname);
return ds;
}
注：当Excel文件中有两列的名称一样，则第二列起名称自动在末尾加'1'
方法二：
/// <summary>
/// 把Excel里的数据转换为DataTable,应用引用的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件
/// </summary>
/// <param name="filenameurl">物理路径</param>
/// <param name="sheetIndex">sheet名称的索引</param>
/// <param name="splitstr">如果是已存在列，则自定义添加的字符串</param>
/// <returns></returns>
public DataTable ExecleToDataSet(string filenameurl, int sheetIndex, string splitstr)
{
//
bool isEqual = false;//不相等
ArrayList columnArr = new ArrayList();//列字段表
DataSet myDs = new DataSet();
DataTable xlsTable = myDs.Tables.Add("show");
object missing = System.Reflection.Missing.Value;
Excel.ApplicationClass excel = new Excel.ApplicationClass();//lauch excel application
if (excel == null)
{
Alert.Show("不能进入excel！");
}
else
{
excel.Visible = false;
excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Excel.Workbook wb = excel.Workbooks.Open(filenameurl, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(sheetIndex);
//取得总记录行数(包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数
DataRow dr;
for (int i = 1; i <= columnsint; i++)
{
//判断是否有列相同
if (i >= 2)
{
int r = 0;
for (int k = 1; k <= i - 1; k++)//列从第一列到第i-1列遍历进行比较
{
if (((Excel.Range)ws.Cells[1, i]).Text.ToString() == ((Excel.Range)ws.Cells[1, k]).Text.ToString())
{
//如果该列的值等于前面列中某一列的值
xlsTable.Columns.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString() + splitstr + (r+1).ToString(), typeof(string));
columnArr.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString() + splitstr + (r + 1).ToString());
isEqual = true;
r++;
break;
}
else
{
isEqual = false;
continue;
}
}
if (!isEqual)
{
xlsTable.Columns.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString(), typeof(string));
columnArr.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString());
}
}
else
{
xlsTable.Columns.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString(), typeof(string));
columnArr.Add(((Excel.Range)ws.Cells[1, i]).Text.ToString());
}
}
for (int i = 2; i <= rowsint; i++)
{
dr = xlsTable.NewRow();
for (int j = 1; j <= columnsint; j++)
{
dr[columnArr[j - 1].ToString()] = ((Excel.Range)ws.Cells[i, j]).Text.ToString();
}
xlsTable.Rows.Add(dr);
}
}
excel.Quit();
excel = null;
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process pro in procs)
{
pro.Kill();//没有更好的方法,只有杀掉进程
}
GC.Collect();
return xlsTable;
}
2.获取Excel文件的Sheet名称，并邦定到DropDownList
[DllImport("user32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int id);
/// <summary>
/// 获取Excel文件的Sheet名称，并邦定到DropDownList
/// </summary>
/// <param name="FileFullName">服务器，物理路径 </param>
protected void ExcelSheetToBind(string FileFullName)
{
string[] SplitString = FileFullName.Split('\\');
string FileName = SplitString[SplitString.Length - 1].ToString();
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbook oBook = oExcel.Workbooks.Open(FileFullName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
DropDownList_datetable.Items.Clear();
DropDownList_datetable.Items.Add("请选择工作表", "0");
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in oBook.Sheets)
{
DropDownList_datetable.Items.Add(new ExtAspNet.ListItem(sheet.Name, sheet.Name));
}
}
catch
{
}
finally
{
IntPtr t = new IntPtr(oExcel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
}
