asp.net 导出excel带图片

//先要需要引入Microsoft.Office.Interop.Excel.dll  
//申明定义  
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;  
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;    
protected void btnDown_Click(object sender, EventArgs e)  
    {  
          
        string strWhere = " flag=1 ";  
          
        ds = dal.GetList(strWhere);//取数据  
        dt = ds.Tables[0];  
          
        ImportDataToExcel(dt);  
    }  
    #region 导出  
    private void ImportDataToExcel(DataTable dt)  
    {  
        if (dt != null)  
        {  
            #region 操作excel  
            xlWorkBook = new Microsoft.Office.Interop.Excel.Application().Workbooks.Add(Type.Missing);  
            xlWorkBook.Application.Visible = false;  
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];  
  
            //设置标题  
  
            xlWorkSheet.Cells[1, 1] = "物品类型";  
            xlWorkSheet.Cells[1, 2] = "物品名称";  
            xlWorkSheet.Cells[1, 3] = "颜色";  
            xlWorkSheet.Cells[1, 4] = "价格/元";  
            xlWorkSheet.Cells[1, 5] = "重量/克";  
            xlWorkSheet.Cells[1, 6] = "加工置换类型";  
            xlWorkSheet.Cells[1, 7] = "是否提供来源";  
  
            //设置宽度，默认宽度和高度会改变图片的尺寸              
            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 19]).ColumnWidth = 50;  
            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 20]).ColumnWidth = 50;  
            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 21]).ColumnWidth = 50;  
            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 22]).ColumnWidth = 50;  
            //设置字体  
            xlWorkSheet.Cells.Font.Size = 12;  
             
            #endregion  
 
            #region 为excel赋值  
  
            for (int i = 0; i < dt.Rows.Count; i++)  
            {  
                //设置高度  
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, 19]).RowHeight = 200;  
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, 20]).RowHeight = 200;  
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, 21]).RowHeight = 200;  
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, 22]).RowHeight = 200;  
                //为单元格赋值。  
                xlWorkSheet.Cells[i + 2, 1] = dt.Rows[i]["Type"] == null ? "" : SysDataMg.GetDataName(dt.Rows[i]["Type"].ToString(), "GoodType");  
                xlWorkSheet.Cells[i + 2, 2] = dt.Rows[i]["GoodName"] == null ? "" : dt.Rows[i]["GoodName"].ToString();  
                xlWorkSheet.Cells[i + 2, 11] = dt.Rows[i]["CarCode"] == null ? "" : "'" + dt.Rows[i]["CarCode"].ToString();  
        //CarCode 是身份证号，在前面加个单引号，防止被excel保存为科学计数法  
 
                 
                #region  
                //可以直接取图片的地址  
                if (dt.Rows[i]["Img1"] != null)  
                {  
                    string filename = dt.Rows[i] == null ? "" : Common.GetServerPath() + "WSJW/WSBB/" + dt.Rows[i]["Img1"].ToString();  
                    //<span style="font-family: Arial, Helvetica, sans-serif;">Common.GetServerPath()  这儿方法是取当前网站所在硬盘的路径</span>  
  
                    int rangeindex = i + 2;//这里+2，是从第二行开始写入数据，第一行是标题  
                    string rangename = "S" + rangeindex;//这里S是excel中列明  
                    SavePic(rangename, filename);  
                }  
                 
                #endregion  
                  
  
            }  
            #endregion  
 
            #region 保存excel文件  
  
            string filePath = Server.MapPath("excel") + "" + System.DateTime.Now.ToString().Replace(":", "") + ".xls";  
            xlWorkBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);  
            xlWorkBook.Application.Quit();  
            xlWorkSheet = null;  
            xlWorkBook = null;  
            GC.Collect();  
            System.GC.WaitForPendingFinalizers();  
 
            #endregion  
 
 
            #region 导出到客户端  
              
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");  
            Response.AppendHeader("content-disposition", "attachment;filename=" + Common.GetGuid() + ".xls");  
            Response.ContentType = "Application/excel";  
            Response.WriteFile(filePath);  
            Response.End();  
            #endregion  
  
            KillProcessexcel("EXCEL");  
        }  
    }  
    #endregion  
    private void SavePic(string rangename, string filename)  
    {  
  
        Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range(rangename, Type.Missing);  
        range.Select();  
        /////////////  
        float PicLeft, PicTop, PicWidth, PicHeight;　　　　//距离左边距离，顶部距离，图片宽度、高度  
        PicTop = Convert.ToSingle(range.Top);  
        PicWidth = Convert.ToSingle(range.MergeArea.Width);  
        PicHeight = Convert.ToSingle(range.Height);  
        PicWidth = Convert.ToSingle(range.Width);  
        PicLeft = Convert.ToSingle(range.Left);  
        ////////////////////  
  
        Microsoft.Office.Interop.Excel.Pictures pict = (Microsoft.Office.Interop.Excel.Pictures)xlWorkSheet.Pictures(Type.Missing);  
        if (filename.IndexOf(".") > 0)  
        {  
            if (System.IO.File.Exists(filename))  
            {  
                //pict.Insert(filename, Type.Missing);//显示原图   重叠在一起  
                xlWorkSheet.Shapes.AddPicture(filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, PicWidth, PicHeight);//指定位置显示小图  
            }  
        }  
    }  
    #region   
    private void KillProcessexcel(string processName)  
    { //获得进程对象，以用来操作  
        System.Diagnostics.Process myproc = new System.Diagnostics.Process();  
        //得到所有打开的进程  
        try  
        {  
            //获得需要杀死的进程名  
            foreach (Process thisproc in Process.GetProcessesByName(processName))  
            { //立即杀死进程  
                thisproc.Kill();  
            }  
              
        }  
        catch (Exception Exc)  
        {  
            throw new Exception("", Exc);  
        }  
        finally  
        {  
        }  
    }  
    #endregion