在操作excel的时候，除了制作chart之外，很多时候还要读取excel里的图片到数据库里。下面简单介绍下从excel里抓图片到db,并从db里捞出来。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 using System.Data; 7 using Microsoft.Office.Interop.Excel; 8 using System.Configuration; 9 using System.Windows.Forms; 10 using System.Drawing; 11 using System.IO; 12 namespace a_stock 13 { 14 class ExcelWithPicture 15 { 16 //excel里有图片的路径 17 public static readonly string path = "C:\\GDS\\NBQAA Mockup DFM Report(1126).xls"; 18 //数据库连接字符串 19 public static readonly string conStr = ConfigurationSettings.AppSettings["strConn"].ToString(); 20 public static void Read_excel_withpicture() 21 { 22 Microsoft.Office.Interop.Excel.Application MyExcel = new Microsoft.Office.Interop.Excel.Application(); 23 //打开excel 24 MyExcel.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 25 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 26 Microsoft.Office.Interop.Excel.Workbook It_book = null; 27 Microsoft.Office.Interop.Excel.Worksheet It_sheet = null; 28 Microsoft.Office.Interop.Excel.Range It_rang = null; 29 MyExcel.DisplayAlerts = false; 30 It_book = MyExcel.Workbooks[1]; 31 //获取sheet表 32 It_sheet = (Worksheet)It_book.Worksheets[1]; 33 //initial the data 34 int Priority = -9999, Fsize = 0; 35 string Initial_date = "", DFX_number = "", Special_Issue = "", Issue_location = "", Suggestion_DFX = "", NPR = "", Category = "", Owner = "", status = ""; 36 byte[] Special_Picture={}; 37 It_rang = It_sheet.get_Range("E11", "G11"); 38 It_rang.Select(); 39 //拷贝单元格的图片，记住get_Range所选取的矩阵范围一定要比图片所占据单元格的范围大，否则只会显示一部分 40 It_rang.CopyPicture(XlPictureAppearance.xlScreen,XlCopyPictureFormat.xlBitmap); 41 if (Clipboard.ContainsImage()) 42 { 43 MemoryStream imagestream=new MemoryStream(); 44 Clipboard.GetImage().Save(imagestream,System.Drawing.Imaging.ImageFormat.Jpeg); 45 //int length = (int)imagestream.Length; 46 Fsize = (int)imagestream.Length; 47 //图片字节流 48 Special_Picture=imagestream.ToArray(); 49 //imagestream.Read(Special_Picture,0,length); 50 } 51 //取得其他单元格相应的值 52 Priority =Convert.ToInt32( It_sheet.get_Range("B11",Type.Missing).Text.ToString()); 53 Initial_date = It_sheet.get_Range("C11",Type.Missing).Text.ToString(); 54 DFX_number = It_sheet.get_Range("D11", Type.Missing).Text.ToString(); 55 Special_Issue = It_sheet.get_Range("E11", Type.Missing).Text.ToString(); 56 Issue_location = It_sheet.get_Range("H11", Type.Missing).Text.ToString(); 57 Suggestion_DFX = It_sheet.get_Range("I11", Type.Missing).Text.ToString(); 58 NPR = It_sheet.get_Range("O10", Type.Missing).Formula.ToString(); 59 Category = It_sheet.get_Range("Q11", Type.Missing).Text.ToString(); 60 Owner = It_sheet.get_Range("R11", Type.Missing).Text.ToString(); 61 status = It_sheet.get_Range("S11", Type.Missing).Text.ToString(); 62 63 //把图片相关信息录入数据库的存储过程 64 excute_insert(conStr,Priority,Initial_date,DFX_number,Special_Issue,Special_Picture,Issue_location,Suggestion_DFX,NPR,Category,Owner,status,Fsize); 65 66 67 MyExcel.Quit(); 68 System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel); 69 MyExcel = null; 70 It_book = null; 71 It_sheet = null; 72 73 74 } 75 public static void excute_insert(string con, int Priority, string Initial_date, string DFX_number, string Special_Issue, byte[] Special_Picture, string Issue_location, string Suggestion_DFX, string NPR, string Category, string Owner, string status, int Fsize) 76 { 77 SqlConnection con_ = new SqlConnection(con); 78 SqlCommand cmd = new SqlCommand("Insert_ExcelWithPicture", con_); 79 cmd.CommandType = CommandType.StoredProcedure; 80 cmd.CommandTimeout = 0; 81 82 cmd.Parameters.Add("@Priority", SqlDbType.Int); 83 cmd.Parameters.Add("@Initial_date", SqlDbType.NVarChar); 84 cmd.Parameters.Add("@DFX_number", SqlDbType.NVarChar); 85 cmd.Parameters.Add("@Special_Issue", SqlDbType.NVarChar); 86 cmd.Parameters.Add("@Special_Picture", SqlDbType.Image); 87 cmd.Parameters.Add("@Issue_location", SqlDbType.NVarChar); 88 cmd.Parameters.Add("@Suggestion_DFX", SqlDbType.NVarChar); 89 cmd.Parameters.Add("@NPR", SqlDbType.NVarChar); 90 cmd.Parameters.Add("@Category", SqlDbType.NVarChar); 91 cmd.Parameters.Add("@Owner", SqlDbType.NVarChar); 92 cmd.Parameters.Add("@status", SqlDbType.NVarChar); 93 cmd.Parameters.Add("@Fsize", SqlDbType.Int); 94 95 cmd.Parameters["@Priority"].Value =Priority; 96 cmd.Parameters["@Initial_date"].Value =Initial_date; 97 cmd.Parameters["@DFX_number"].Value = DFX_number; 98 cmd.Parameters["@Special_Issue"].Value = Special_Issue; 99 cmd.Parameters["@Special_Picture"].Value = Special_Picture; 100 cmd.Parameters["@Issue_location"].Value = Issue_location; 101 cmd.Parameters["@Suggestion_DFX"].Value = Suggestion_DFX; 102 cmd.Parameters["@NPR"].Value = NPR; 103 cmd.Parameters["@Category"].Value = Category; 104 cmd.Parameters["@Owner"].Value = Owner; 105 cmd.Parameters["@status"].Value = status; 106 cmd.Parameters["@Fsize"].Value = Fsize; 107 108 con_.Open(); 109 cmd.ExecuteNonQuery(); 110 con_.Close(); 111 } 112 } 113 } 114
从db里面抓出来show在web上：
1 using System; 2 using System.Collections; 3 using System.Configuration; 4 using System.Data; 5 using System.Linq; 6 using System.Web; 7 using System.Web.Security; 8 using System.Web.UI; 9 using System.Web.UI.HtmlControls; 10 using System.Web.UI.WebControls; 11 using System.Web.UI.WebControls.WebParts; 12 using System.Xml.Linq; 13 using System.Data.SqlClient; 14 using System.IO; 15 public partial class Get_picture : System.Web.UI.Page 16 { 17 //string strConn = ConfigurationManager.AppSettings["strConn"]; 18 public static readonly string strConn = ConfigurationManager.AppSettings["strConn1"]; 19 20 protected void Page_Load(object sender, EventArgs e) 21 { 22 SqlConnection conn = new SqlConnection(); 23 conn.ConnectionString =strConn; 24 conn.Open(); 25 string sql = "select * from [Get_ExcelWithPicture] where ID=7"; //这里id使用的是1，实际可以根据需要传一个值 26 SqlCommand cmd = new SqlCommand(sql, conn); 27 cmd.CommandTimeout =0; 28 SqlDataReader dr = cmd.ExecuteReader(); 29 30 //if (dr.Read()) 31 //{ 32 // byte[] imgdata = (byte[])dr["Special_Picture"]; 33 // Response.BinaryWrite(imgdata); 34 // dr.Close(); 35 // conn.Close(); 36 // Response.End(); 37 //} 38 39 if (dr.Read()) 40 { 41 //Response.ContentType = dr["ID"].ToString().Trim(); 42 Response.Clear(); 43 //输出图片文件二进制数据流 44 Response.OutputStream.Write((byte[])dr["Special_Picture"], 0, 1+(int)dr["Fsize"]); 45 Response.End(); 46 } 47 48 } 49 } 50
