ASP. Read and save from Excel, unable to read pictures from memory, Excel components and related IIS configuration and workaround

</pre> purpose: An ASP.:<p></p><p> only provides part of the code, because the focus is on the configuration of the Web page, which reads Excel, (with the Office component). </p><p>1: To read a picture from Excel, you can only copy the picture to memory with the clipboard aspect and then save the picture. &LT;/P&GT;&LT;P&GT;2: The use of the Clipboard to reference Winform</p><p>3:excel is a single-threaded way, so the code uses the Clipboard in a single thread, [STAThread]. </p><p>4: In the debug state of VS2013 in the code through, you can remove and save the pictures in Excel, the error below IIS. </p><p>retrieving the COM class factory for component with CLSID {00024500-0000-0000-c000-000000000046} Failed due to the following error:80080005. </p><p> this is the general Excel error, need to configure the Excel component, the solution is as follows:</p>< P></p><p> excel Component Set up     Open Component Services, componentservices->computers->dcom config-> Microsoft Excel application</p><p>in Launch and Activation permissionoption (Add Network Service account give local launch and local activationpermission) </p><p >in access Permission option (addnetwork service account give local access Permission) </p><p>in change Configuration permissionoption give permission</p><p> (select the Interactive user in Identity) </p><p></p><p><strong>if You Can ' t findmicrosoft Excel application, please new a folder Nam  Ed desktop</strong>:</p><p>create the Desktop folder, because Desktop folder seems to be necessary inthe Systemprofile folder to open file by excel</p><p>1). Windows Server x64  Please create the folder:c:\windows\syswow64\config\systemprofile\desktop</p>< P&GT;2). Windows Server x86</p><p>  Please create this folder:c:\windows\system32\config\systemprofile\ Desktop   </p><p>and give account <span style= "color: #333333; Background:whitesmoke ">neTwork service write Permission</span></p> 5:<strong><span style= "FONT-SIZE:18PX; Color: #ff0000 "> The most important step is the configuration of the application pool for the Web site in IIS and the core configuration of the entire project, where the application pool chooses the identity as localsystem.</span></ Strong><p><strong><span style= "FONT-SIZE:18PX; Color: #ff0000 >      This is critical, select LocalSystem to copy the pictures in Excel to memory. </span></strong></p><p></p><p></p><p></p><p>webform</p ><p></p><p></p><p>  Page Click button event: </p><pre code_snippet_id= "667270" Snippet_file_name= "blog_20150515_2_7423722" name= "code" class= "CSharp" >//Upload image data

        protected void Btnuploadpic_click (object sender, EventArgs e) {try { Div_result.                InnerHtml = "Result area."; if (string. IsNullOrEmpty (Fuuploadpic.filename)) {Div_result.                InnerHtml = "Please select A Excel File"; } else {M_userid = userinfo.user_id.                    Value.tostring ();                    String excelname = fuUploadPic.FileName.Substring (FuUploadPic.FileName.LastIndexOf ('. ')); if (excelname! = ". xls" && excelname! = ". xlsx") {Div_result.                        InnerHtml = "Please select A Excel File";                    Return }//Save upload file to server M_pictureexcelpath = Server.MapPath (System.Configuration.Configuratio                    nmanager.appsettings["Mboarduploadfilepath") + guid.newguid () + excelname; Fuuploadpic.saveas (M_pictureexcelpath);                    String Strsamenamearticleno = "";                    Samenamearticleno (ref Strsamenamearticleno);                    M_samenamearticleno = Strsamenamearticleno;                    String isrelativeorabsolute = system.configuration.configurationmanager.appsettings["Isrelativeorabsolute"]; if (Isrelativeorabsolute = = NULL | | Isrelativeorabsolute = = "") M_picpath = system.configuration.configurationmanager.appsettings["Mboa                    Rduploadpicturepath_absolute "]; else if (Isrelativeorabsolute = = "1") M_picpath = Server.MapPath (System.Configuration.Configuration                    manager.appsettings["Mboarduploadpicturepath_relative"]); else if (Isrelativeorabsolute = = "2") M_picpath = System.Configuration.ConfigurationManager.AppSett                   ings["Mboarduploadpicturepath_absolute"]; <span style= "color: #ff0000;"    >//Extract pictures from Excel store to server specified path               <strong><span style= "FONT-SIZE:18PX;" > System.Threading.Thread cbthread = new System.Threading.Thread (new System.Threading.ThreadStart (uploadpicture))                    ; Cbthread.trysetapartmentstate (System.Threading.ApartmentState.STA);//Specifies a single thread, or the data cannot be read from the Clipboard Cbthread.isbac                    Kground = true;                                        Cbthread.start ();                    while (cbthread.isalive) {System.Threading.Thread.Sleep (1000);                }</span></strong></span>}} catch (Exception ex) {                Writeerrorlog (ex); Div_result. InnerHtml = ex.            Message;            } finally {//killexcel (); }        }

Methods in a single thread:

public void Uploadpicture () {try {//Initialize Excel object Microsoft.offi Ce.                Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application ();                Microsoft.Office.Interop.Excel.Workbook Workbook = null;                    try {String sql = ""; Open the XLS file (note: The following parameters are populated with Type.Missing, indicating the default value for using parameters) Workbook = Excel. Workbooks.Open (M_pictureexcelpath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,                    System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); if (workbook. Worksheets.count > 0) {for (int i = 1; I <= workbook. Worksheets.count;                        i++)//cycle through all the sheet. {Microsoft.Office.Interop.Excel.WorKsheet sheet = workbook.                            Worksheets.get_item (i) as microsoft.office.interop.excel.worksheet;//starting from 1.                            String articlenoall = "";                            String excelarticlenoall = ""; for (int row = 2; row <= sheet. UsedRange.Rows.Count; row++) {//Select the picture name to the cell containing the 2nd column of the nth row St Ring Article_no = ((Microsoft.Office.Interop.Excel.Range) sheet. Cells[row, 2]). Text.tostring (). Trim ().                                Replace ("", "");  if (Article_no.length > 0) {excelarticlenoall + = ","                                    + Article_no;                                    String temexcelarticlenoall = Excelarticlenoall + ",";                                    String Temexcelarticleno = "," + Article_no + ",";                                int a = Temexcelarticlenoall.indexof (Temexcelarticleno);    int b = Temexcelarticlenoall.lastindexof (Temexcelarticleno);                                    if (Temexcelarticlenoall.indexof (temexcelarticleno) = = Temexcelarticlenoall.lastindexof (temExcelArticleNo))  {if (m_samenamearticleno.contains (temexcelarticleno) = = False ||                                        (M_samenamearticleno.contains (Temexcelarticleno) = = True && cbreplacesamename.checked = True))                                            {//Select a picture to the cell where column 1th of row n is located Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range) sheet.                                            Cells[row, 1];                                            R.select ();                                            System.Windows.Forms.Clipboard.Clear (); Copy a cell to the Clipboard r.copypicture (Microsoft.Office.Interop.Excel.XlPictureAppearancE.xlscreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap); string[] Arrstr = System.Windows.Forms.Clipboard.GetDataObject ().                                            GetFormats ();                                            System.Drawing.Image Image = System.Windows.Forms.Clipboard.GetImage (); Determines if a picture exists in the Clipboard and, if present, saves the picture to the specified directory//if (System.Windows.Forms.Clipboard.GetDataObj Ect.                                            ContainsImage ()) if (image! = null)                                                {Articlenoall + = ", '" + article_no + "'";                                                String Picpath = M_picpath + Article_no + ". png"; Image. Save (Picpath, System.Drawing.Imaging.ImageFormat.Png);//saved to local int Pic_heigh t = image.                 Height;                               int pic_width = image.                                                Width; SQL + = "INSERT into Rma_r_mboard_article_picture (article_no,picture_path,operate_user,update_time,pic_height,pic_ width) VALUES (' "+ article_no +" ', ' "+ Picpath +" ', "+ M_userid +", GETDATE (), "+ Pic_height +", "+ Pic_width +") "+"                                            \ n ";                            }                                        }                                    }                                }                            } if (Articlenoall! = "" "&& articlenoall.length > 1)                                {Articlenoall = articlenoall.substring (1);                                sql = "DELETE from Rma_r_mboard_article_picture WHERE article_no in (" + Articlenoall + ") \ n" + SQL;                                int secrowcount = sqlhelp.executenonquerycmd (sql);         if (Secrowcount > 0)                       {Div_result.                                    InnerHtml = "Upload picture successfully";                                Return } else {Div_result.                                    InnerHtml = "Upload picture Failed";                                Return }//div_result.                                InnerHtml = "Upload picture successfully";                            Return } else {Div_result.                                InnerHtml = "The picture already exists";                            Return }}} else {Div_re Sult.                    InnerHtml = "Excel No has picture"; } workbook. CLose (FALSE, NULL, NULL); Excel.                Quit (); } catch (Exception ex) {workbook.                    Close (false, NULL, NULL); Excel.                    Quit ();                    Writeerrorlog (ex); Div_result. InnerHtml = ex.                Message; } finally {//system.runtime.interopservices.marshal.releasecomobject (mys                    Heet);                    MySheet = null;                    System.Runtime.InteropServices.Marshal.ReleaseComObject (workbook);                    workbook = null;                    System.Runtime.InteropServices.Marshal.ReleaseComObject (MyBooks);                    MyBooks = null;                    System.Runtime.InteropServices.Marshal.ReleaseComObject (Excel);                    Excel = null; Workbook.                                        Close (false, NULL, NULL); Excel.                                  Quit ();      Killexcelthread (Excel);                }} catch (Exception ex) {Writeerrorlog (ex); Div_result. InnerHtml = ex.            Message; }        }

Here's how to kill Excel:

private void Killexcel () {//outputexcel (); Gc.            Collect (); Gc.        WaitForPendingFinalizers (); }//Gets the corresponding thread PID for the handle [System.Runtime.InteropServices.DllImport ("User32.dll", CharSet = System.Runtime.InteropSe Rvices.                CharSet.Auto)] private static extern int GetWindowThreadProcessId (INTPTR hwnd, out int ID);            Kill Excel process private void Killexcelthread (Microsoft.Office.Interop.Excel.Application Excel) { try {IntPtr t = new IntPtr (Excel.                Hwnd);//Get this handle, the specific function is to get this memory entry int k = 0; GetWindowThreadProcessId (t, out k);//Get unique mark K System.Diagnostics.Process p = System.Diagnostics.Process.GetP                Rocessbyid (k);//k reference P.kill ();//Close k} catch (Exception ex) {                Writeerrorlog (ex); Div_result. InnerHtml = ex.            Message; }        }

ASP. Read and save from Excel, unable to read pictures from memory, Excel components and related IIS configuration and workaround