C#操作excel(读取excel里的图片并show在页面上)

         在操作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

 

 

转载于:https://www.cnblogs.com/MR_ke/archive/2010/03/08/1680576.html