asp.net 导出excel 以及插入图片

在网上找了相关参考资料,作个记录 下载Microsoft.Office.Interop.Excel;

参考资料http://csharp.net-informations.com/excel/csharp-insert-picture-excel.htm

引用:命名空间

using Excel=Microsoft.Office.Interop.Excel;

Microsoft.Office.Core;

 

代码如下:

 #region 导出
        protected void btntoexcel_Click(object sender, EventArgs e)
        {
            Glant.BLL.BLLProduct bll = new Glant.BLL.BLLProduct();
      
            System.Data.DataTable dt = bll.GetList("id=661 or id=657").Tables[0];//获取要导出的dt;
            if (dt != null)
            {
                #region 操作excel
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                xlWorkBook = new Excel.Application().Workbooks.Add(Type.Missing);
                xlWorkBook.Application.Visible = false;
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
                //设置标题
                xlWorkSheet.Cells[1, 1] = "标题1";
                xlWorkSheet.Cells[1, 2] = "标题2";
                xlWorkSheet.Cells[1, 3] = "标题3";
                xlWorkSheet.Cells[1, 4] = "图片";
                //设置宽度            
                ((Excel.Range)xlWorkSheet.Cells[1, 2]).ColumnWidth = 15;
                ((Excel.Range)xlWorkSheet.Cells[1, 4]).ColumnWidth = 50;//图片的宽度
                 
                //设置字体
                xlWorkSheet.Cells.Font.Size = 12;
                xlWorkSheet.Cells.Rows.RowHeight = 100;
                #region 为excel赋值
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //为单元格赋值。
                    xlWorkSheet.Cells[i + 2, 1] = dt.Rows[i]["sku"].ToString();
                    xlWorkSheet.Cells[i +2, 2] = dt.Rows[i]["supplier_sku"].ToString();
                    xlWorkSheet.Cells[i + 2, 3] = dt.Rows[i]["name"].ToString();
                   
                    #region
                    //可以直接取图片的地址
                    string filename = Server.MapPath("~/" + dt.Rows[i]["smallpic_url"].ToString());
                    //也可以用下面的方法把图片从数据库里取出来。
                    //byte[] filedata = (byte[])dtimg.Rows[j]["img"];
                    //System.IO.MemoryStream ms = new System.IO.MemoryStream(filedata);
                    //System.Drawing.Image img = System.Drawing.Image.FromStream(ms);
                    //img.Save(filename);
                    #endregion
                    //int rangeindex = i+1;
                    //string rangename = "D" + rangeindex;
                    //Excel.Range range = xlWorkSheet.get_Range(rangename, Type.Missing);
                    //range.Select();
                    //Excel.Pictures pict = (Excel.Pictures)xlWorkSheet.Pictures(Type.Missing);
                    //pict.Insert(filename, Type.Missing);
                     
                    //Left , Top , Width and Height.
                    xlWorkSheet.Shapes.AddPicture(filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
                        220, 100+i*100, 100, 100); 


                }
                #endregion
                #region 保存excel文件
                string filePath = Server.MapPath("/") + "" + System.DateTime.Now.ToString().Replace(":", "") + "导出.xls";
                xlWorkBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    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
                #endregion
                #region 导出到客户端
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("导出", System.Text.Encoding.UTF8) + ".xls");
                Response.ContentType = "Application/excel";
                Response.WriteFile(filePath);
                Response.End();
                #endregion
                KillProcessexcel("EXCEL");
            }
        }
        #endregion
        #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);
            }
        }
        #endregion