.net中两个关于excel的method

记录一下，偶尔用到……

 

        private string ExcelRead(string fileName) //filename中包含具体的Path
        {
            //string queryString = "select * from [Sheet1$]";
            CtrlSdPartQry ctrlSdPartQry = new CtrlSdPartQry(this.GetUserProfile());
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel  8.0;HDR=No;IMEX=1'";
            OleDbConnection dbConnection = new OleDbConnection(connectionString);
            dbConnection.Open();

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
            DataTable dtSheetName = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //包含excel中表名的字符串数组
            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }

            //从指定的表明查询数据,可先把所有表明列出来供用户选择
            string queryString = "select * from [" + strTableNames[0] + "]";

            OleDbCommand dbCommand = new OleDbCommand(queryString, dbConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

            dataAdapter.SelectCommand = dbCommand;
            dataAdapter.SelectCommand.ExecuteNonQuery();
            dbConnection.Close();

            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Excel");
            DataRowCollection dr = dataSet.Tables[0].Rows;         
          
            ItemExemptionList item; //下面就是我们系统中把excel的内容insert到DB里面了。具体内容大家根据自己的情况处理
            string strProject = DDL_Project.SelectedValue;
            string sitename = string.Empty;
            ArrayList aylSiteName = new ArrayList();
            for (int i = 0; i < DDL_Plant.Items.Count; i++)
            {
                if (DDL_Plant.Items[i].Selected)
                {
                    //sitename += DDL_Plant.Items[i].Value + ",";
                    aylSiteName.Add(DDL_Plant.Items[i].Value);
                }
            }
            //check plant
            if (aylSiteName.Count==0)
            {
                return "NOSITE";
            }
            //check data is null or not
            for (int x = 1; x < dr.Count; x++)
            {
                if (dr[x][0].ToString().Equals("") || dr[x][1].ToString().Equals(""))
                {
                    return "FAIL";
                }
            }
            //import data in DB
            ctrlSdPartQry.deleteDBExemptionList(strProject);
            for (int j = 1; j < dr.Count; j++)
            {
                for (int k = 0; k < aylSiteName.Count; k++)
                {
                    item = new ItemExemptionList();
                    item.UCCSiteName = aylSiteName[k].ToString();
                    item.ProjectCode = strProject;
                    item.PN = dr[j][0].ToString();
                    item.MinGFReq = dr[j][1].ToString();
                    ctrlSdPartQry.InsertDBExemptionList(item);
                }
            }
            return "SUCCESSFUL";
        }

       

         //通过dataset和文件名输出excel

         public void CreateExcel(DataSet ds, string FileName)
        {
            try
            {
                HttpResponse resp;
                resp = Page.Response;
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("Big5");
                resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
                string colHeaders = "", ls_item = "";

                //定义表对象与行对象，同时用DataSet对其值进行初始化
                System.Data.DataTable dt = ds.Tables[0];
                DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的

                int i = 0;
                int cl = dt.Columns.Count;

                //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                    }

                }
                resp.Write(colHeaders);
                //向HTTP输出流中写入取得的数据信息

                //逐行处理数据  
                foreach (DataRow row in myRow)
                {
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                    for (i = 0; i < cl; i++)
                    {
                        if (i == (cl - 1))//最后一列，加n
                        {
                            ls_item += row[i].ToString() + "\n";
                        }
                        else
                        {
                            ls_item += row[i].ToString() + "\t";
                        }

                    }
                    resp.Write(ls_item);
                    ls_item = "";

                }
                resp.End();
            }
            catch (Exception ex)
            {   //这是我们系统处理错误的方法
                //ExceptionHandler.Handle(ex, CtrlSdPartQry.FUNCTION_ID, this.getDefaultFailMsg(), GetUserProfile());
                //this.HandleError();
            }

        }