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')Sheet1contentnbsp;
注:如遇到如下问题:
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();
- }
- }