如何datatable导出excel带图片

Question

大家好!最近有个项目做了datatable导出excel的功能用了国外的一个 MyXls.SL2.dll 组件现在客户要求另外在excel中添加图片.我在datatable一列添加的img或者byte[]在excel中都不能显示成图片.
例如(转成byte):
            //样式
            string strImg = Server.MapPath(dtTemp.Rows[a]["p_img"].ToString());
            System.Drawing.Image img = System.Drawing.Image.FromFile(strImg);
            MemoryStream ms = new MemoryStream();
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf.Serialize(ms, img);
            drTemp[5] = ms.ToArray();excel中显示成
System.Byte[]大家有没有什么解决办法?

Answer 1

/// <summary>
/// 把Gridview中数据导入到Excel的类
/// </summary>
public class GridViewToExcel
{
  public GridViewToExcel()
  {
  //
  // TODO: 在此处添加构造函数逻辑
  //
  }
  /**//// <summary>
  /// /// 把Gridview中数据导入到Excel中
  /// </summary>
  /// <param name="gv">需要导出数据的Gridview</param>
  /// <param name="ds">Gridview的数据源</param>
  /// <param name="strFileName">默认的导出Excel的文件名</param>
  /// <param name="bolPart">全部还是部分导出到Excel.部分：true. 全部：false</param>
  public static void ConvertToExcel(GridView gv, DataSet ds, string strFileName, bool bolPart)
  {
  gv.AllowPaging = bolPart;//设置导出数据是全部还是部分
  gv.DataSource = ds;
  gv.DataBind();  for (int i = 0; i < gv.Columns.Count; i++) //设置每个单元格
  {
  gv.Columns[i].ItemStyle.HorizontalAlign = HorizontalAlign.Left;
  for (int j = 0; j < gv.Rows.Count; j++)
  {
  gv.Rows[j].Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
  }
  }  System.IO.StringWriter sw = new System.IO.StringWriter();
  HtmlTextWriter htw = new HtmlTextWriter(sw);  HttpContext.Current.Response.Clear();
  HttpContext.Current.Response.Charset = "GB2312";
  HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;//设置UTF8有时候出乱码  strFileName += ".xls";
  HttpContext.Current.Response.ContentType = "application/ms-excel";
  HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlPathEncode(strFileName));//设置默认文件名
  HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);  //预防出现控件必须放在具有 runat=server 的窗体标记内的错误
  Page page = new Page();
  HtmlForm form = new HtmlForm();
  gv.EnableViewState = false;
  page.EnableEventValidation = false;
  page.DesignerInitialize();
  page.Controls.Add(form);
  form.Controls.Add(gv);
  page.RenderControl(htw);  HttpContext.Current.Response.Write(sw.ToString());
  HttpContext.Current.Response.End();
  }
}这个还达不到。得下载.

Answer 2

/// <summary>
  /// 将图片填充到Excel中的某个或某些单元格中
  /// </summary>
  /// <param name="ws">Microsoft.Office.Interop.Excel.Worksheet</param>
  /// <param name="m_objRange"> Microsoft.Office.Interop.Excel.Range</param>
  /// <param name="PicturePath">插入图片的绝对物理路径</param>
  /// <param name="IsMergeCells">是否合并上面的单元格</param>
  public void InsertPicture(Microsoft.Office.Interop.Excel.Worksheet ws, Microsoft.Office.Interop.Excel.Range m_objRange, string PicturePath, bool IsMergeCells)
  {
  //计算单元格的宽和高
  float PictuteWidth, PictureHeight;
  PictuteWidth = Convert.ToSingle(m_objRange.Width);
  PictureHeight = Convert.ToSingle(m_objRange.Height);
  PictuteWidth = 150;
  PictureHeight = 200;
  if (IsMergeCells)
  {
  //合并单元格
  m_objRange.Merge(System.Reflection.Missing.Value);
  }
  m_objRange.Select();
  float PicLeft, PicTop;
  PicLeft = Convert.ToSingle(m_objRange.Left);
  PicTop = Convert.ToSingle(m_objRange.Top);
  ws.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
  Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft+2, PicTop, PictuteWidth, PictureHeight);
  }
调用Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ws.Cells[j + 2, k + 1];
  if (dt.Columns[k].ColumnName=="Thumbnails")
  {
  #region 生成图片
  string filePath = Application.StartupPath + "//NetPagePic//" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".jpg";
  Image img= BytesToImage((byte[])dt.Rows[j][k]);
  img.Save(filePath);  #endregion  InsertPicture(ws, rg, filePath, true);//向Excel插入图片
  File.Delete(filePath);//删除文件
  rg.RowHeight = 200;
  rg.ColumnWidth =25;//宽度设置为150时在Excel中实际宽度大大超出了150。顾修改为25
  }
  else
  {
  rg.NumberFormatLocal = "@";
  ws.Cells[j + 2, k + 1] = dt.Rows[j][k].ToString();
  }

Answer 3

我这个需要在生成excel的时候就生成图片.
可以吗?
因为生成excel后马上就下载

Answer 4

/// <summary>
/// 把Gridview中数据导入到Excel的类
/// </summary>
public class GridViewToExcel
{
  public GridViewToExcel()
  {
  //
  // TODO: 在此处添加构造函数逻辑
  //
  }
  /**//// <summary>
  /// /// 把Gridview中数据导入到Excel中
  /// </summary>
  /// <param name="gv">需要导出数据的Gridview</param>
  /// <param name="ds">Gridview的数据源</param>
  /// <param name="strFileName">默认的导出Excel的文件名</param>
  /// <param name="bolPart">全部还是部分导出到Excel.部分：true. 全部：false</param>
  public static void ConvertToExcel(GridView gv, DataSet ds, string strFileName, bool bolPart)
  {
  gv.AllowPaging = bolPart;//设置导出数据是全部还是部分
  gv.DataSource = ds;
  gv.DataBind();  for (int i = 0; i < gv.Columns.Count; i++) //设置每个单元格
  {
  gv.Columns[i].ItemStyle.HorizontalAlign = HorizontalAlign.Left;
  for (int j = 0; j < gv.Rows.Count; j++)
  {
  gv.Rows[j].Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
  }
  }  System.IO.StringWriter sw = new System.IO.StringWriter();
  HtmlTextWriter htw = new HtmlTextWriter(sw);  HttpContext.Current.Response.Clear();
  HttpContext.Current.Response.Charset = "GB2312";
  HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;//设置UTF8有时候出乱码  strFileName += ".xls";
  HttpContext.Current.Response.ContentType = "application/ms-excel";
  HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlPathEncode(strFileName));//设置默认文件名
  HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);  //预防出现控件必须放在具有 runat=server 的窗体标记内的错误
  Page page = new Page();
  HtmlForm form = new HtmlForm();
  gv.EnableViewState = false;
  page.EnableEventValidation = false;
  page.DesignerInitialize();
  page.Controls.Add(form);
  form.Controls.Add(gv);
  page.RenderControl(htw);  HttpContext.Current.Response.Write(sw.ToString());
  HttpContext.Current.Response.End();
  }
}

Answer 5

/// <summary>
        /// 将Web控件导出
        /// </summary>
        /// <param name="source">控件实例</param>
        /// <param name="type">类型:Excel或Word</param>
        public void ExpertControl(System.Web.UI.Control source, DocumentType type)
        {
            //设置Http的头信息,编码格式
            if (type == DocumentType.Excel)
            {
                //Excel
                Response.AppendHeader("Content-Disposition","attachment;filename=result.xls");
                Response.ContentType = "application/ms-excel";
            }
            else if (type == DocumentType.Word)
            {
                //Word
                Response.AppendHeader("Content-Disposition","attachment;filename=result.doc");
                Response.ContentType = "application/ms-word";
            }
            Response.Charset = "UTF-8";  
            Response.ContentEncoding = System.Text.Encoding.UTF8;             //关闭控件的视图状态
            source.Page.EnableViewState =false;              //初始化HtmlWriter
            System.IO.StringWriter writer = new System.IO.StringWriter() ;
            System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
            source.RenderControl(htmlWriter);             //输出
            Response.Write(writer.ToString());
            Response.End();
        }        //文档类型
        public enum DocumentType
        {
            Word,
            Excel
        }

Answer 6

wxr0323
--
你好!我的要求不是直接下载,而是以文件形式存储在服务器的文件夹里.

Answer 7

可以先生成出物理文件
然后
http://blog.csdn.net/net_lover/archive/2007/07/23/1702797.aspx

Answer 8

   foreach (DataGridViewRow row in gridView.Rows)
            {
                int index = 1;
                for (int i = 1; i <= gridView.Columns.Count; i++)
                {
                    if (gridView.Columns[i - 1].Visible == false)
                        continue;
                    Excel.Range range = (Excel.Range)m_CurrentSheet.Cells[rowIndex, index];
                    if (row.Cells[i - 1].Value.ToString() == "System.Byte[]" && row.Cells[i - 1].Value.GetType() ==typeof(byte[]) )
                    {
                        if (!BTDirect)
                        {
                            if (!Directory.Exists(@"c:\Volx\Excel"))//判断是否存在
                            {
                                Directory.CreateDirectory(@"c:\Volx\Excel");//创建新路径
                            }                            BTDirect = true;
                        }
                        byte[] bytes = (byte[])row.Cells[i - 1].Value;
                        System.IO.MemoryStream ms = new System.IO.MemoryStream(bytes);
                        System.Drawing.Bitmap btmp = new Bitmap(ms);
                        System.Drawing.Bitmap bmpresult = new Bitmap(btmp, 100, 50);                        bmpresult.Save(@"c:\Volx\Excel\tmp.bmp");                        //Bitmap bbb = new Bitmap("c:\Volx\Excel\tmp.bmp");
                        //range.Value2 = bbb;                        string PicturePath = @"c:\Volx\Excel\tmp.bmp";
                        int PictuteWidth = bmpresult.Width;
                        int PictureHeight = bmpresult.Height;                        range.Select();
                        float PicLeft, PicTop;
                        PicLeft = Convert.ToSingle(range.Left);
                        PicTop = Convert.ToSingle(range.Top);
                        //参数含义：
                        //图片路径
                        //是否链接到文件
                        //图片插入时是否随文档一起保存
                        //图片在文档中的坐标位置（单位：points）
                        //图片显示的宽度和高度（单位：points）
                        ((Excel.Range)m_CurrentSheet.Rows[rowIndex.ToString() + ":" + rowIndex.ToString(), Type.Missing]).RowHeight = PictureHeight;
                        ((Excel.Range)m_CurrentSheet.Columns[(char)(index + (int)'A' - 1) + ":" + (char)(index + (int)'A' - 1), Type.Missing]).ColumnWidth = PictuteWidth/6;                        m_CurrentSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
                         Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
                        //MessageBox.Show("aaa");
                    }
                    else
                    {
                        try
                        {
                            range.Value2 = row.Cells[i - 1].Value;
                        }
                        catch
                        {
                            range.Value2 = Convert.ToString(row.Cells[i - 1].Value);
                        }
                        range.Borders.Weight = Excel.XlBorderWeight.xlThin;
                        range.Borders.Color = Color.Black.ToArgb();
                        range.EntireColumn.AutoFit();
                        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    }                    //Application.DoEvents();
                    index++;
                }
                rowIndex++;
                //如果工作表所有行用完了，添加新的工作表
                if (rowIndex == 65535)
                {
                    GetWorkSheet();
                    rowIndex = 1;
                }
            }
        }