如何datatable导出excel带图片

[SinGooCMS]

大家好!

最近有个项目做了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[]

大家有没有什么解决办法?

[SeanDing1989]

你客户真操蛋。

[SeanDing1989]

/// <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();
  }
}

这个还达不到。得下载.

[怪蜀黍的花季雨季]

/// <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();
  }

[SinGooCMS]

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

[zq710860677]

/// <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();
  }
}

[SinGooCMS]

上面这个可以导出图片?

[子夜__]

[Quote=引用 6 楼 liqiang665 的回复:]

上面这个可以导出图片?
[/Quote]

/// <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
        }

[SinGooCMS]

似乎没多少人关注?

[孟子E章]

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

[SinGooCMS]

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

[chuizi250]

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;
}
}
}