//如果单纯是想导出excel,可以移步这里:点击打开链接
- //此方法20条数据导出需要10秒左右,效率太低了,有时间需要再优化,关键是能导出图片了。
- //先要需要引入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
转载地址:http://blog.csdn.net/moniteryao/article/details/42718525