发布：2023/3/22 16:11:57作者：管理员 来源：本站 浏览次数：265
在操作excel的时候，除了制作chart之外，很多时候还要读取excel里的图片到数据库里。下面简单介绍下从excel里抓图片到db,并从db里捞出来。
在操作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
复制代码
© Copyright 2014 - 2024 柏港建站平台 ejk5.com. 渝ICP备16000791号-4