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, null, 0, " 501 Not Implemented", tcpClient);
continue;
}
if (sBuffer.Contains(terminationGuid.ToString()))
{
SendHeader(sHttpVersion, null, 0, " 200 OK", tcpClient);
break;
}
if (!sBuffer.Contains(fileName))
{
sErrorMessage = "
404 Error! File Does Not Exist
";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#region IDisposable Members
public void Dispose()
{
// Send a termination request to stop the web server
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://localhost:" + myPort + "/" + terminationGuid.ToString());
WebResponse response = request.GetResponse();
}
#endregion
}
}
调用时可以使用这个方法：
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);
.