在网上找了相关参考资料,作个记录 下载Microsoft.Office.Interop.Excel;
参考资料
引用:命名空间
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