Excel中一般是通过Worksheet.Shapes.AddPicture方法添加图片,方法的参数格式为:
    AddPicture([In] string Filename,    [In] MsoTriState LinkToFile,    [In] MsoTriState SaveWithDocument,    [In] float Left,    [In] float Top,    [In] float Width,    [In] float Height);
    其中Filename必须是一个uri路径,内存中的图片对象则没有办法使用此方法插入。
    我们的项目是一个SmartClient的VSTO项目,当时总有一个意识在支配我,那就是尽可能不要访问本地资源,而如果内存中的图片不写到本地又该如何插入到EXCEL中呢?Google吧,结果搜到一个连他自己都说“Heavyweight”的解决方案,那就是在本地临时创建一个最小型的HTTP Server,将内存中的图片数据交给它,然后由它再展交给AddPicture,代码如下:
using System;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading;

namespace YourNameSpace
{
    
public class LocalWebServer:IDisposable
    
{
        
private TcpListener myListener;
        
private static readonly Guid terminationGuid = new Guid("{7b0d9a9d-fbec-4fe5-8ea7-fc3b313510fc}");
        
byte[] contentsToServe;
        Thread thread;
        
string fileName;
        
int myPort;

        
//The constructor which makee the TcpListener start listening on the
        
//given port. It also creates a Thread on the method StartListen(). 

        
public LocalWebServer(int port, string fileName, byte[] contents)
        
{
            
try
            
{
                
//start listing on the given port
                myListener = new TcpListener(IPAddress.Parse("127.0.0.1"), port);
                contentsToServe 
= contents;
                
this.fileName = fileName;
                
this.myPort = port;

                myListener.Start();
                System.Diagnostics.Debug.WriteLine(
"Web Server Running Press ^C to Stop");

                
//start the thread which calls the method 'StartListen'
                Thread th = new Thread(new ThreadStart(StartListen));
                
this.thread = th;
                th.Start();
            }

            
catch (Exception e)
            
{
                System.Diagnostics.Debug.WriteLine(
"An Exception Occurred while Listening :" + e.ToString());
            }

        }


        
public void SendHeader(string sHttpVersion, string sMIMEHeader, int iTotBytes, string sStatusCode, TcpClient tcpClient)
        
{
            StringBuilder sBuffer 
= new StringBuilder();
            
// if Mime type is not provided set default to text/html

            
if (string.IsNullOrEmpty(sMIMEHeader))
            
{
                sMIMEHeader 
= "text/xml";  // Default Mime Type is text/xml
            }


            sBuffer.Append(sHttpVersion);
            sBuffer.AppendLine(sStatusCode);
            sBuffer.AppendLine(
"Server: VSTOServer");
            sBuffer.Append(
"Content-Type: ");
            sBuffer.AppendLine(sMIMEHeader);
            sBuffer.AppendLine(
"Accept-Ranges: bytes");
            sBuffer.Append(
"Content-Length: ");
            sBuffer.AppendLine(iTotBytes.ToString());
            sBuffer.AppendLine(
"");

            Byte[] bSendData 
= Encoding.ASCII.GetBytes(sBuffer.ToString());
            SendToBrowser(bSendData, tcpClient);
            System.Diagnostics.Debug.WriteLine(
"Total Bytes : " + iTotBytes.ToString());
        }


        
public void SendToBrowser(Byte[] bSendData, TcpClient tcpClient)
        
{
            
try
            
{
                
if (tcpClient.Connected)
                
{
                    NetworkStream stream 
= tcpClient.GetStream();
                    stream.Write(bSendData, 
0, bSendData.Length);
                    stream.Flush();
                }

                
else
                
{
                    System.Diagnostics.Debug.WriteLine(
"Connection Dropped.");
                }

            }

            
catch (Exception e)
            
{
                System.Diagnostics.Debug.WriteLine(String.Format(
"Error Occurred : {0} ", e));
            }

        }


        
public void StartListen()
        
{

            
int iStartPos = 0;
            String sErrorMessage;
            
while (true)
            
{
                
//Accept a new connection
                using (TcpClient tcpClient = myListener.AcceptTcpClient())
                
{
                    
if (tcpClient.Connected)
                    
{
                        System.Diagnostics.Debug.WriteLine(String.Format(
"\nClient Connected!!\n==================\nClient IP {0}\n", tcpClient.Client.RemoteEndPoint));
                        NetworkStream stream 
= tcpClient.GetStream();
                        
//make a byte array and receive data from the client 
                        Byte[] bReceive = new Byte[1024];
                        
int i = stream.Read(bReceive, 0, bReceive.Length);
                        
//Convert Byte to String
                        string sBuffer = Encoding.ASCII.GetString(bReceive);
                        
// Look for HTTP request
                        iStartPos = sBuffer.IndexOf("HTTP"1);
                        
// Get the HTTP text and version e.g. it will return "HTTP/1.1"
                        string sHttpVersion = sBuffer.Substring(iStartPos, 8);
                        
//At present we will only deal with GET type
                        
// if OPTION received, still OK
                        if (!sBuffer.StartsWith("GET"))
                        
{
                            System.Diagnostics.Debug.WriteLine(
"Only Get Method is supported..");
                            SendHeader(sHttpVersion, 
null0" 501 Not Implemented", tcpClient);
                            
continue;
                        }

                        
if (sBuffer.Contains(terminationGuid.ToString()))
                        
{
                            SendHeader(sHttpVersion, 
null0" 200 OK", tcpClient);
                            
break;
                        }

                        
if (!sBuffer.Contains(fileName))
                        
{
                            sErrorMessage 
= "<H2>404 Error! File Does Not Exist</H2>";
                            SendHeader(sHttpVersion, 
"", sErrorMessage.Length, " 404 Not Found", tcpClient);
                            SendToBrowser(Encoding.ASCII.GetBytes(sErrorMessage), tcpClient);
                            
continue;
                        }


                        SendHeader(sHttpVersion, 
null, contentsToServe.Length, " 200 OK", tcpClient);
                        SendToBrowser(contentsToServe, tcpClient);
                    }

                }

            }

            myListener.Stop();
        }


        
IDisposable Members

    }

}

    调用时可以使用这个方法:
        private void AddPictureByWeb(Excel.Worksheet worksheet, string fileName, byte[] contents, float left, float top, float width, float height)
        
{
            
// Use any private port in the range 49152–65535
            int portNumber = 50213;
            fileName 
= System.Web.HttpUtility.UrlEncode(fileName);
            
using (new LocalWebServer(portNumber, fileName, contents))
            
{
                
// the request will land with the WebServer object in this process
                string url = @"http://localhost:" + portNumber + "/" + fileName;
                worksheet.Shapes.AddPicture(url, Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue,
                                   left, top, width, height);
            }

        }

    内存的图片数据交给byte[] contents即可。
    当这个方案成功后,我却突然开了窍,既然是VSTO的项目,一般来说需要客户端FullTrust许可,那么写到本地临时目录中根本不成问题呀,何苦如此大动干戈呢?于是:
.
Bitmap image 
= new Bitmap(new MemoryStream(buffer))
image.Save(filePath, ImageFormat.Jpeg);
worksheet.Shapes.AddPicture(filePath, Office.MsoTriState.msoFalse,  Office.MsoTriState.msoTrue, left, top, width, height);
File.Delete(filePath);
.
posted on 2006-09-14 17:37  愚公  阅读(5087)  评论(0编辑  收藏  举报