EXCEL操作

EXCEL操作#region  EXCEL操作
        public static DataTable getRec(string FileName, out string status)
        ...{
            status = "";
            DataTable ResultDt = new DataTable();
            string connstring = string.Format("Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0", FileName);
            OleDbConnection conn = new OleDbConnection(connstring);
            string SheetName1 = getAllTables(FileName)[0];
            string sqlstr = "select * from [" + SheetName1 + "]";
            OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn);
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            da.Dispose();
            conn.Dispose();
            string err = "";
            ResultDt = dt;
            status += err;
            return ResultDt;
        }

        /**//// <summary>
        /// 转为电子表格文件
        /// </summary>
        /// <param name="ctl"></param>
        /// <param name="FileName"></param>
        public static void ToExcel(System.Web.UI.Control ctl, string FileName)
        ...{
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
            ctl.Page.EnableViewState = false;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }

        /**//// <summary>
        /// 外部文件存储
        /// </summary>
        /// <param name="File1">控件</param>
        /// <param name="FileName">文件名</param>
        /// <param name="ErrText"></param>
        public static void FileSave(System.Web.UI.HtmlControls.HtmlInputFile File1, string FileName, out string ErrText)
        ...{
            ErrText = "";
            string FilePath = FileName.Substring(0, FileName.LastIndexOf('/'));
            if (!System.IO.Directory.Exists(FilePath))
            ...{
                System.IO.Directory.CreateDirectory(FilePath);
            }

            if (System.IO.Directory.Exists(FileName))
            ...{
                System.IO.Directory.Delete(FileName);
            }
            File1.PostedFile.SaveAs(FileName);
        }

        /**//// <summary>
        /// 获取外部文件的所有sheet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static string[] getAllTables(string fileName)
        ...{
            string strCon = string.Format(" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties=Excel 8.0;", fileName);
            //ADOX.CatalogClass catalog = new ADOX.CatalogClass(); 
            ADOX.CatalogClass catalog = new ADOX.CatalogClass();
            ADODB.ConnectionClass _connection = new ADODB.ConnectionClass();
            _connection.Open(strCon, null, null, 0);
            catalog.ActiveConnection = _connection;
            string[] tables = new string[catalog.Tables.Count];
            int i = 0;
            foreach (ADOX.Table table in catalog.Tables)
            ...{
                tables[i] = table.Name;
                i += 1;
            }
            _connection.Close();
            _connection.Cancel();
            return tables;
        }
        #endregion