.Net 导出Excel 带图片

  Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        private void ImportDataToExcel(DataTable dt)
        {
            if (dt != null)
            {
                xlWorkBook = new Microsoft.Office.Interop.Excel.Application().Workbooks.Add(Type.Missing);
                xlWorkBook.Application.Visible = false;
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];

                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Rows[1]).Font.Bold = true;
                //((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Rows[1]).Interior.Color = 5000;

                //设置标题

                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] = "职务";
                xlWorkSheet.Cells[1, 8] = "证件照";
                xlWorkSheet.Cells[1, 9] = "注册日期";


                //设置宽度，默认宽度和高度会改变图片的尺寸            
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1]).ColumnWidth = 10;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 2]).ColumnWidth = 20;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 3]).ColumnWidth = 30;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 4]).ColumnWidth = 10;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 5]).ColumnWidth = 20;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 6]).ColumnWidth = 30;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 7]).ColumnWidth = 20;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 8]).ColumnWidth = 25;
                ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 9]).ColumnWidth = 20;
                //设置字体
                xlWorkSheet.Cells.Font.Size = 12;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //设置高度
                    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, 8]).RowHeight = 90;
                    //((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]["RealName"];
                    xlWorkSheet.Cells[i + 2, 2] = dt.Rows[i]["Telephone"];
                    xlWorkSheet.Cells[i + 2, 3] = dt.Rows[i]["CityName"];
                    xlWorkSheet.Cells[i + 2, 4] = Function.GetEmnuEntity<Proj.Entity.EnumDoctType>(Convert.ToInt32(dt.Rows[i]["UType"])).DisplayName;
                    xlWorkSheet.Cells[i + 2, 5] = Function.GetEmnuEntity<Proj.Entity.EnumUserStatusID>(Convert.ToInt32(dt.Rows[i]["StatusID"])).DisplayName;
                    xlWorkSheet.Cells[i + 2, 6] = dt.Rows[i]["HospitalName"];
                    xlWorkSheet.Cells[i + 2, 7] = dt.Rows[i]["JobPosition"];
                    xlWorkSheet.Cells[i + 2, 8] = "";
                    xlWorkSheet.Cells[i + 2, 9] = FormatObjDate(dt.Rows[i]["CTime"]);
                    //CarCode 是身份证号，在前面加个单引号，防止被excel保存为科学计数法


                    #region
                    //可以直接取图片的地址
                    int rangeindex = i + 2;//这里+2，是从第二行开始写入数据，第一行是标题
                    string rangename = "H" + rangeindex;//这里S是excel中列明
                    //SavePic(rangename, dt.Rows[i]["Imglist"].ToString());
                    SavePic(rangename, "https://www.baidu.com/img/bd_logo1.png");
                    #endregion


                }

                string filePath = Server.MapPath("~/upload/temp/" + DateTime.Now.ToFileTime()) + ".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();


                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("content-disposition", "attachment;filename=Test.xls");
                Response.ContentType = "Application/excel";
                Response.WriteFile(filePath);
                Response.End();

                KillProcessexcel("EXCEL");
            }
        }
        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)
            {
                xlWorkSheet.Shapes.AddPicture(filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, PicWidth, PicHeight);//指定位置显示小图
                //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);//指定位置显示小图
                //}
            }
        }
        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
            {
            }
        }

 

 

需添加引用：

Microsoft.CSharp;

Microsoft.office.Core;

Microsoft.office.interop.excel

转载于:https://www.cnblogs.com/cheua/p/9930510.html