Upload & Download Excel Method

 

protected void Button1_Click(object sender, EventArgs e)
        {
            //upload excel


            string savedFileName = "";

            if (FileUpload1.PostedFile.FileName == "")
            {
                msg1.ForeColor = Color.Red;
                msg1.Text = "提示:請選擇文件!";
                return;
            }

            Excel.Application app = null;
            try
            {
                Process#region   Process
                savedFileName = UserInfo.UserId + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + "_FGCost.xls";

                string savedFolderAndFile = ConfigurationManager.AppSettings["UploadPath"] + savedFileName;
                FileUpload1.PostedFile.SaveAs(savedFolderAndFile);
                app = new Excel.ApplicationClass();
                app.Visible = false;
                app.DisplayInfoWindow = false;
                Excel.Workbooks books = app.Workbooks;
                Excel.Workbook book = books.Open(savedFolderAndFile, 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);
                Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[1];
                string sheetName = sheet.Name;

                //添加所有的SQL語句
                ArrayList SQLs = new ArrayList();

                for (int i = 0; i < sheet.UsedRange.Rows.Count - 1; i++)
                {
                    //這里有兩種情況是否是新增的MATGROUP OR 更改的MATGROUP.
                    string customer = ((Excel.Range)sheet.Cells[i + 2, 1]).Text.ToString();
                    string modelname = ((Excel.Range)sheet.Cells[i + 2, 2]).Text.ToString();
                    string container = ((Excel.Range)sheet.Cells[i + 2, 3]).Text.ToString();
                    string loading = ((Excel.Range)sheet.Cells[i + 2, 4]).Text.ToString();
                    string unit = ((Excel.Range)sheet.Cells[i + 2, 5]).Text.ToString();
                    string validon = ((Excel.Range)sheet.Cells[i + 2, 6]).Text.ToString();


                    SQLs.Add(string.Format("INSERT INTO BILL_STD_LOADING(CUSTOMER,MODELNAME,CONTAINER,LOADING,UNIT,VALIDON)VALUES ('{0}','{1}','{2}','{3}','{4}',to_date('{5}','yyyy/mm/dd'))", 
                        customer, modelname, container, loading, unit, validon));
                }

                try
                {
                    IDbTool tool = DbTool.setInstance(DbTool.ORACLE_DATAACCESS, "MIX01_BMFCS");
                    int[] types = tool.executeBatchNoneQuery((string[])SQLs.ToArray(typeof(System.String)));

                    msg1.ForeColor = Color.Blue;

                    log.Info(UserInfo.CName + " ,BY EXCEL 更新資料成功！");
                    msg1.Text = "系統更新成功，詳細請在QUERY DATA中查看。";

                }
                catch (Exception ex)
                {
                    log.Error("上傳失敗,資料寫入出問題，ex:" + ex.Message);
                    msg1.Text = ("提示：系統更新出現問題，請聯系管理員。");
                }


                #endregion
            }

            catch (Exception ex)
            {
                msg1.ForeColor = System.Drawing.Color.Red;
                msg1.Text = "系統出錯：" + ex.Message;
                log.Error(UserInfo.CName + " ,系統出錯：" + ex.Message);
            }

            finally
            {
                //回收進程
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                GC.Collect();
            }

        }