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

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [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 }