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

         在操作excel的时候，除了制作chart之外，很多时候还要读取excel里的图片到数据库里。下面简单介绍下从excel里抓图片到db,并从db里捞出来。

 

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Data.SqlClient;
 using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Windows.Forms;
using System.Drawing;
using System.IO;
namespace a_stock
{
    class ExcelWithPicture
    {
        //excel里有图片的路径
        public static readonly string path = "C:\\GDS\\NBQAA Mockup DFM Report(1126).xls";
        //数据库连接字符串
        public static readonly string conStr = ConfigurationSettings.AppSettings["strConn"].ToString();
        public static void Read_excel_withpicture()
        {
            Microsoft.Office.Interop.Excel.Application MyExcel = new Microsoft.Office.Interop.Excel.Application();
            //打开excel
            MyExcel.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel.Workbook It_book = null;
            Microsoft.Office.Interop.Excel.Worksheet It_sheet = null;
            Microsoft.Office.Interop.Excel.Range It_rang = null;
            MyExcel.DisplayAlerts = false;
            It_book = MyExcel.Workbooks[1];
            //获取sheet表
            It_sheet = (Worksheet)It_book.Worksheets[1];
            //initial the data
            int Priority = -9999, Fsize = 0;
            string Initial_date = "", DFX_number = "", Special_Issue = "", Issue_location = "", Suggestion_DFX = "", NPR = "", Category = "", Owner = "", status = "";
            byte[] Special_Picture={};
            It_rang = It_sheet.get_Range("E11", "G11");
            It_rang.Select();
            //拷贝单元格的图片，记住get_Range所选取的矩阵范围一定要比图片所占据单元格的范围大，否则只会显示一部分
             It_rang.CopyPicture(XlPictureAppearance.xlScreen,XlCopyPictureFormat.xlBitmap);
            if (Clipboard.ContainsImage())
            { 
                MemoryStream imagestream=new MemoryStream();
               Clipboard.GetImage().Save(imagestream,System.Drawing.Imaging.ImageFormat.Jpeg);
               //int length = (int)imagestream.Length;
               Fsize = (int)imagestream.Length;
                //图片字节流
                Special_Picture=imagestream.ToArray();
                //imagestream.Read(Special_Picture,0,length);
            }
                   //取得其他单元格相应的值
            Priority =Convert.ToInt32( It_sheet.get_Range("B11",Type.Missing).Text.ToString());
            Initial_date = It_sheet.get_Range("C11",Type.Missing).Text.ToString();
            DFX_number = It_sheet.get_Range("D11", Type.Missing).Text.ToString();
            Special_Issue = It_sheet.get_Range("E11", Type.Missing).Text.ToString();
            Issue_location = It_sheet.get_Range("H11", Type.Missing).Text.ToString();
            Suggestion_DFX = It_sheet.get_Range("I11", Type.Missing).Text.ToString();
            NPR = It_sheet.get_Range("O10", Type.Missing).Formula.ToString();
            Category = It_sheet.get_Range("Q11", Type.Missing).Text.ToString();
            Owner = It_sheet.get_Range("R11", Type.Missing).Text.ToString();
            status = It_sheet.get_Range("S11", Type.Missing).Text.ToString();

            //把图片相关信息录入数据库的存储过程
            excute_insert(conStr,Priority,Initial_date,DFX_number,Special_Issue,Special_Picture,Issue_location,Suggestion_DFX,NPR,Category,Owner,status,Fsize);


            MyExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel);
            MyExcel = null;
            It_book = null;
            It_sheet = null;

        
        }
        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)
        {
            SqlConnection con_ = new SqlConnection(con);
            SqlCommand cmd = new SqlCommand("Insert_ExcelWithPicture", con_);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 0;

            cmd.Parameters.Add("@Priority", SqlDbType.Int);
            cmd.Parameters.Add("@Initial_date", SqlDbType.NVarChar);
            cmd.Parameters.Add("@DFX_number", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Special_Issue", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Special_Picture", SqlDbType.Image);
            cmd.Parameters.Add("@Issue_location", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Suggestion_DFX", SqlDbType.NVarChar);
            cmd.Parameters.Add("@NPR", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Category", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Owner", SqlDbType.NVarChar);
            cmd.Parameters.Add("@status", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Fsize", SqlDbType.Int);
          
            cmd.Parameters["@Priority"].Value =Priority;
            cmd.Parameters["@Initial_date"].Value =Initial_date;
            cmd.Parameters["@DFX_number"].Value = DFX_number;
            cmd.Parameters["@Special_Issue"].Value = Special_Issue;
            cmd.Parameters["@Special_Picture"].Value = Special_Picture;
            cmd.Parameters["@Issue_location"].Value = Issue_location;
            cmd.Parameters["@Suggestion_DFX"].Value = Suggestion_DFX;
            cmd.Parameters["@NPR"].Value = NPR;
            cmd.Parameters["@Category"].Value = Category;
            cmd.Parameters["@Owner"].Value = Owner;
            cmd.Parameters["@status"].Value = status;
            cmd.Parameters["@Fsize"].Value = Fsize;

            con_.Open();
            cmd.ExecuteNonQuery();
            con_.Close();
        }
    }
}

 

从db里面抓出来show在web上：

 

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
public partial class Get_picture : System.Web.UI.Page
{
    //string strConn = ConfigurationManager.AppSettings["strConn"];
    public static readonly string strConn = ConfigurationManager.AppSettings["strConn1"];

    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString =strConn;
        conn.Open();
        string sql = "select * from [Get_ExcelWithPicture] where ID=7"; //这里id使用的是1，实际可以根据需要传一个值
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.CommandTimeout =0;
        SqlDataReader dr = cmd.ExecuteReader();

        //if (dr.Read())
        //{
        //    byte[] imgdata = (byte[])dr["Special_Picture"];
        //    Response.BinaryWrite(imgdata);
        //    dr.Close();
        //    conn.Close();
        //    Response.End();
        //}

        if (dr.Read())
        {
            //Response.ContentType =  dr["ID"].ToString().Trim();
            Response.Clear();
            //输出图片文件二进制数据流
            Response.OutputStream.Write((byte[])dr["Special_Picture"], 0, 1+(int)dr["Fsize"]);
            Response.End();
        }

    }
}