导出带图片的Excel

【核心代码】

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Services;
using System.Xml;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using NPOI.SS.Util;

namespace ExportExcel
{
    /// <summary>
    /// ExportExcel 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class ExportExcel : System.Web.Services.WebService
    {
        /// <summary>
        /// 导出 带图片 Excel
        /// </summary>
        /// <param name="xmlstr">基础信息</param>
        /// <param name="cypic">整改图片</param>
        /// <param name="zgpic">整改图片</param>
        /// <param name="fkpic">反馈图片</param>
        /// <returns></returns>
        [WebMethod]
        public string OutExcel(string xmlstr, string cypic, string zgpic, string fkpic)
        {
            string filePath = System.Configuration.ConfigurationManager.AppSettings["filePath"];
            string ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"];

            string fileName = "导出文件"   DateTime.Now.ToShortDateString().Replace("/", "").Replace("-", "")
                  DateTime.Now.ToLongTimeString().Replace(":", "")   ".xls"; //文件名

            FileStream files = new FileStream(filePath   fileName, FileMode.Create);

            HSSFWorkbook wk;
            //读入刚复制的要导出的excel文件
            using (FileStream file = new FileStream(ExcelPath, FileMode.Open, FileAccess.Read))
            {
                wk = new HSSFWorkbook(file);
                file.Close();
            }
            try
            {
                XmlDocument xmldocument = new XmlDocument();
                xmldocument.LoadXml(xmlstr);
                XmlNode xmlnode = xmldocument.SelectSingleNode("Row");

                ISheet sheet = wk.GetSheetAt(0);   //读取当前表数据
                ICell cell;

                IRow row = sheet.GetRow(0);
                cell = row.GetCell(0);
                if (Convert.ToInt32(xmlnode.SelectSingleNode("TYPE").InnerText) >= 3)
                    cell.SetCellValue("整改信息");
                else
                    cell.SetCellValue("抽样信息");

                row = sheet.GetRow(2);

                string[] cols = "权属,类型,抽样类型,名称,状态,巡查员,截止日期".Split(',');
                for (int i = 0; i < cols.Length; i  )
                {
                    cell = row.GetCell(i);
                    cell.SetCellValue(xmlnode.SelectSingleNode(cols[i]).InnerText);
                }

                row = sheet.GetRow(5);
                cell = row.GetCell(0);
                cell.SetCellValue(xmlnode.SelectSingleNode("抽样类型").InnerText);
                cell = row.GetCell(1);
                cell.SetCellValue(xmlnode.SelectSingleNode("整改意见").InnerText);
                cell = row.GetCell(4);
                cell.SetCellValue(xmlnode.SelectSingleNode("反馈内容").InnerText);

                string pic = cypic;
                if (zgpic != "")
                    pic = zgpic;

                if (pic != "")
                {
                    row = sheet.GetRow(7);
                    cell = row.GetCell(1);
                    PicInsertExcel(pic, sheet, wk, 7);
                }

                if (fkpic != "")
                {
                    row = sheet.GetRow(8);
                    cell = row.GetCell(1);
                    PicInsertExcel(fkpic, sheet, wk, 8);
                }

                //向excel文件中写入数据并保保存
                wk.Write(files);
                files.Close();
                return fileName;
            }
            catch
            {
                files.Close();
                return "导出失败!!!";
            }
        }


        private void PicInsertExcel(string pic, ISheet sheet, HSSFWorkbook workbook, int row)
        {
            string picPath = System.Configuration.ConfigurationManager.AppSettings["PicPath"];
            int numpic = 0;
            int numr = 3;

            string[] cypicA = pic.Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);
            for (int m = 0; m < cypicA.Length; m  )
            {
                string pc0 = cypicA[m];
                string[] pa0 = pc0.Split(';');

                string pc1 = pa0[0];
                string pc2 = pa0[1];

                string[] pa1 = pc2.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                for (int n = 0; n < pa1.Length; n  )
                {
                    if (numpic > 1) break;
                    if (pa1[n] != "")
                    {
                        string pt = picPath   pc1   "\\"   pa1[n];
                        if (File.Exists(pt))
                        {
                            AddPieChart(sheet, workbook, pt, row, numpic * numr   1);
                            numpic  ;
                        }
                    }
                }
            }
        }

        ///
        /// 向sheet插入图片
        ///
        private void AddPieChart(ISheet sheet,   HSSFWorkbook  workbook, string fileurl,int row,int col)
        {
            try
            {
                //add picture data to this workbook.
                string FileName = fileurl;
                byte[] bytes = System.IO.File.ReadAllBytes(FileName);
                if (!string.IsNullOrEmpty(FileName))
                {
                    int pictureIdx = workbook.AddPicture(bytes,NPOI .SS .UserModel .PictureType.JPEG);                  
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 200, 50, col, row, col   2, row   1);
                    //##处理照片位置，【图片左上角为（col, row）第row 1行col 1列，右下角为（ col  1, row  1）第 col  1 1行row  1 1列，宽为100，高为50
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                
                   // pict.Resize();这句话一定不要，这是用图片原始大小来显示
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

    }
}