C# 导出不规则Excel

导出数据到不规则Excel

using Hamp.App.BLL;
using Hamp.App.Model;
using Hamp.App.Model.QualityManagement;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;

namespace QualityManagement
{
    /// <summary>
    /// BaseToExcel 的摘要说明
    /// </summary>
    public partial class BaseToExcel : System.Web.UI.Page
    {
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="modQMPA"></param>
        /// <param name="strPath"></param>
        /// <param name="strExcModel"></param>
        public void ToExcel(DataTable dt, QMProblemAnalysisMOD modQMPA, string strCustomerName,string strProjectName)
        {
            Excel.Range excelRange = m_objExcel.get_Range(m_objExcel.Cells[1, 1], m_objExcel.Cells[27, 15]);
            //excelRange.NumberFormat = "@";
            excelRange.Font.Color = 1;//黑色字体
            //excelRange.WrapText = false;//不换行
            Excel.Range excelRange2 = m_objExcel.get_Range(m_objExcel.Cells[7, 1], m_objExcel.Cells[27, 15]);
            //excelRange2.WrapText = true;//换行
            Excel.Range excelRange3 = m_objExcel.get_Range(m_objExcel.Cells[9, 1], m_objExcel.Cells[12, 12]);
            //excelRange3.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //excelRange3.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            UserBLL u = new UserBLL();
            m_objExcel.Cells[2, 2] = modQMPA.EightDisciplinesNO;
            m_objExcel.Cells[5, 3] = strCustomerName;//客户名称
            m_objExcel.Cells[5, 5] = strProjectName;//产品编号
            m_objExcel.Cells[5, 9] = dt.Rows[0]["DefectQuantity"].ToString();//不良总数
            if (dt.Rows[0]["ComplaintsDate"] != DBNull.Value && dt.Rows[0]["ComplaintsDate"] != null)
            {//投诉日期
                DateTime dtime = new DateTime(1, 1, 1);
                if (DateTime.TryParse(dt.Rows[0]["ComplaintsDate"].ToString(), out dtime))
                {
                    m_objExcel.Cells[5, 13] = dtime.ToString("yyyy-MM-dd");
                }
            }
            if (dt.Rows[0]["AddBy"] != DBNull.Value && dt.Rows[0]["AddBy"] != null)
            {//问题确认人
                UserInfo problemAddBy = u.GetModelByAID(StringUtils.ToInt(dt.Rows[0]["AddBy"]), false);
                m_objExcel.Cells[9, 13] = problemAddBy.LastName;
            }

            if (dt.Rows[0]["ShipDate"] != DBNull.Value && dt.Rows[0]["ShipDate"] != null)
            {//交货日期
                DateTime dtime = new DateTime(1, 1, 1);
                if (DateTime.TryParse(dt.Rows[0]["ShipDate"].ToString(), out dtime))
                {
                    m_objExcel.Cells[5, 11] = dtime.ToString("yyyy-MM-dd");
                }
            }
            if (dt.Rows[0]["DeliveryAmount"] != DBNull.Value && dt.Rows[0]["DeliveryAmount"] != null)
            {//交货总数
                m_objExcel.Cells[5, 7] = dt.Rows[0]["DeliveryAmount"].ToString();
            }
            m_objExcel.Cells[7, 1] = modQMPA.QualityTeam;
            m_objExcel.Cells[9, 1] = StripHTML(modQMPA.Content);
            m_objExcel.Cells[15, 1] = modQMPA.QATempSolution;
            if (!string.IsNullOrWhiteSpace(modQMPA.QATempExecutor))
            {
                string tempResponsibleby = "";
                string[] TempExecutorArr = modQMPA.QATempExecutor.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string item in TempExecutorArr)
                {
                    string[] itemArr = item.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    if (itemArr.Length > 1)
                    {
                        tempResponsibleby += itemArr.GetValue(1) + "，";
                    }
                }
                m_objExcel.Cells[15, 11] = tempResponsibleby.TrimEnd('，');
            }
            if (modQMPA.QATempSolveDate.HasValue)
            {
                m_objExcel.Cells[15, 13] = modQMPA.QATempSolveDate.Value.ToString("yyyy-MM-dd");
            }
            m_objExcel.Cells[17, 3] = modQMPA.QMOnline;
            m_objExcel.Cells[17, 8] = modQMPA.QMInStock;
            if (modQMPA.QMOtherCheckBy.HasValue)
            {
                UserInfo QMOtherCheckBy = u.GetModelByAID(StringUtils.ToInt(modQMPA.QMOtherCheckBy.Value), false) ?? new UserInfo();
                m_objExcel.Cells[17, 13] = QMOtherCheckBy.LastName;
            }
            m_objExcel.Cells[19, 1] = modQMPA.QAAnalysis;
            m_objExcel.Cells[19, 13] = modQMPA.AnalysisBy;
            m_objExcel.Cells[21, 1] = modQMPA.QAFinalSolution;
            if (!string.IsNullOrWhiteSpace(modQMPA.QAFinalExecutor))
            {
                string FinalResponsibleby = "";
                string[] FinalExecutorArr = modQMPA.QAFinalExecutor.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string item in FinalExecutorArr)
                {
                    string[] itemArr = item.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    if (itemArr.Length > 1)
                    {
                        FinalResponsibleby += itemArr.GetValue(1) + "，";
                    }
                }
                m_objExcel.Cells[21, 11] = FinalResponsibleby.TrimEnd('，');
            }
            m_objExcel.Cells[21, 13] = modQMPA.QAFinalSolveDate;
            m_objExcel.Cells[23, 1] = modQMPA.ResultConfirm;
            if (modQMPA.ResultCheckedBy.HasValue)
            {
                UserInfo ResultCheckedBy = u.GetModelByAID(StringUtils.ToInt(modQMPA.ResultCheckedBy.Value), false) ?? new UserInfo();
                m_objExcel.Cells[23, 11] = ResultCheckedBy.LastName;
            }
            m_objExcel.Cells[23, 13] = modQMPA.ResultCheckedDate;
            m_objExcel.Cells[25, 1] = modQMPA.PreventAction;
            if (modQMPA.Approver.HasValue)
            {
                UserInfo ApproverUser = u.GetModelByAID(StringUtils.ToInt(modQMPA.Approver.Value), false) ?? new UserInfo();
                m_objExcel.Cells[25, 11] = ApproverUser.LastName;
            }
            m_objExcel.Cells[25, 13] = modQMPA.VerifyDate;
            if (modQMPA.IsFinished.HasValue)
            {
                if (modQMPA.IsFinished.Value)
                {
                    m_objExcel.Cells[27, 4] = "YES /是";
                }
                else
                {
                    m_objExcel.Cells[27, 4] = "No /非";
                }
            }
        }


        /// <summary>
        /// 功能：实现Excel应用程序的打开
        /// </summary>
        /// <param name="TemplateFilePath">模板文件物理路径</param>
        public void Open(string TemplateFilePath)
        {
            //打开对象
            m_objExcel = new Excel.Application();
            m_objExcel.Visible = false;
            m_objExcel.DisplayAlerts = false;

            if (m_objExcel.Version != "11.0")
            {
                //    MessageBox.Show("您的 Excel 版本不是 11.0 （Office 2003），操作可能会出现问题。");
                //m_objExcel.Quit();
                //return;
            }

            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            if (TemplateFilePath.Equals(String.Empty))
            {
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            }
            else
            {
                m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                 m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            }
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
            m_objExcel.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
        }

        private void m_objExcel_WorkbookBeforeClose(Excel.Workbook m_objBooks, ref bool _Cancel)
        {
            //   MessageBox.Show("保存完毕！");
        }

        /// <summary>
        /// 将图片插入到指定的单元格位置。
        /// 注意：图片必须是绝对物理路径
        /// </summary>
        /// <param name="RangeName">单元格名称，例如：B4</param>
        /// <param name="PicturePath">要插入图片的绝对路径。</param>
        public void InsertPicture(string RangeName, string PicturePath)
        {
            m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
            m_objRange.Select();
            Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
            pics.Insert(PicturePath, m_objOpt);
        }

        /// <summary>
        /// 将图片插入到指定的单元格位置，并设置图片的宽度和高度。
        /// 注意：图片必须是绝对物理路径
        /// </summary>
        /// <param name="RangeName">单元格名称，例如：B4</param>
        /// <param name="PicturePath">要插入图片的绝对路径。</param>
        /// <param name="PictuteWidth">插入后，图片在Excel中显示的宽度。</param>
        /// <param name="PictureHeight">插入后，图片在Excel中显示的高度。</param>
        public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight)
        {
            m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
            m_objRange.Select();
            float PicLeft, PicTop;
            PicLeft = Convert.ToSingle(m_objRange.Left);
            PicTop = Convert.ToSingle(m_objRange.Top);
            //参数含义：
            //图片路径
            //是否链接到文件
            //图片插入时是否随文档一起保存
            //图片在文档中的坐标位置（单位：points）
            //图片显示的宽度和高度（单位：points）
            m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
             Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
        }

        /// <summary>
        /// 将Excel文件保存到指定的目录，目录必须事先存在，文件名称不一定要存在。
        /// </summary>
        /// <param name="OutputFilePath">要保存成的文件的全路径。</param>
        public void SaveFile(string OutputFilePath)
        {
            if (System.IO.File.Exists(OutputFilePath))
            {
                System.IO.File.Delete(OutputFilePath);
            }

            m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt,
             m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
             m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

            this.Close();
        }
        /// <summary>
        /// 关闭应用程序
        /// </summary>
        private void Close()
        {
            m_objBook.Close(false, m_objOpt, m_objOpt);
            m_objExcel.Quit();
        }

        /// <summary>
        /// 释放所引用的COM对象。注意：这个过程一定要执行。
        /// </summary>
        public void Dispose()
        {
            ReleaseObj(m_objSheets);
            ReleaseObj(m_objBook);
            ReleaseObj(m_objBooks);
            ReleaseObj(m_objExcel);
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();
        }
        /// <summary>
        /// 释放对象，内部调用
        /// </summary>
        /// <param name="o"></param>
        private void ReleaseObj(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally { o = null; }
        }
        /// <summary>
        /// 從WEB頁面文件下載
        /// </summary>
        /// <param name="strFile">要下載的文件的絕對路徑 </param>
        public void DownloadFile(string strFile)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(System.IO.Path.GetFileName(strFile).Trim()) + "\"");
            HttpContext.Current.Response.Flush();

            HttpContext.Current.Response.WriteFile(strFile);
            HttpContext.Current.Response.End();
        }

        private Excel.Application m_objExcel = null;
        private Excel.Workbooks m_objBooks = null;
        private Excel._Workbook m_objBook = null;
        private Excel.Sheets m_objSheets = null;
        private Excel._Worksheet m_objSheet = null;
        private Excel.Range m_objRange = null;
        private object m_objOpt = System.Reflection.Missing.Value;

        ///   <summary>
        ///   去除HTML标记
        ///   </summary>
        ///   <param   name="strHtml">包括HTML的源码   </param>
        ///   <returns>已经去除后的文字</returns>
        public string StripHTML(string strHtml)
        {
            string[] aryReg =
                {
                  @"<script[^>]*?>.*?</script>",
                  @"<(\/\s*)?!?((\w+:)?\w+)(\w+(\s*=?\s*(([""'])(\\[""'tbnr]|[^\7])*?\7|\w+)|.{0})|\s)*?(\/\s*)?>", @"([\r\n])[\s]+", @"&(quot|#34);",
                  @"&(amp|#38);", @"&(lt|#60);", @"&(gt|#62);", @"&(nbsp|#160);", @"&(iexcl|#161);", @"&(cent|#162);",
                  @"&(pound|#163);",@"&(copy|#169);", @"&#(\d+);", @"-->", @"<!--.*\n"
                };

            string[] aryRep =
                {
                  "", "", "", "\"", "&", "<", ">", "   ", "\xa1",  //chr(161),
                  "\xa2",  //chr(162),
                  "\xa3",  //chr(163),
                  "\xa9",  //chr(169),
                  "", "\r\n", ""
                };

            string newReg = aryReg[0];
            string strOutput = strHtml;
            for (int i = 0; i < aryReg.Length; i++)
            {
                Regex regex = new Regex(aryReg[i], RegexOptions.IgnoreCase);
                strOutput = regex.Replace(strOutput, aryRep[i]);
            }
            strOutput.Replace("<", "");
            strOutput.Replace(">", "");
            strOutput.Replace("\r\n", "");
            return strOutput;
        }
        /// <summary>
        /// 取出图片地址
        /// </summary>
        /// <param name="HTMLStr"></param>
        /// <returns></returns>
        public string[] GetImgUrl(string HTMLStr)
        {
            // 定义正则表达式用来匹配 img 标签             
            Regex regImg = new Regex(@"<img\b[^<>]*?\bsrc[\s\t\r\n]*=[\s\t\r\n]*[""']?[\s\t\r\n]*(?<imgUrl>[^\s\t\r\n""'<>]*)[^<>]*?/?[\s\t\r\n]*>", RegexOptions.IgnoreCase);

            // 搜索匹配的字符串            
            MatchCollection matches = regImg.Matches(HTMLStr);

            int i = 0;
            string[] sUrlList = new string[matches.Count];

            // 取得匹配项列表           
            foreach (Match match in matches)
                sUrlList[i++] = match.Groups["imgUrl"].Value;

            return sUrlList;
        }

    }
}

调用方法

string strExcelTemplate = Server.MapPath("~/file/Protected/QualityManagement/AnalysisReport/test.xlsx");
                base.Open(strExcelTemplate);
                base.ToExcel(dt, modQMPA, strCustomerName, strProjectName);
                string[] imgSrc = base.GetImgUrl(modQMPA.Content);
                foreach (string itemsrc in imgSrc)
                {
                    string PicturePath = itemsrc;
                    string uri = Request.Url.AbsoluteUri.ToLower() ?? "";
                    string strUri = uri.Substring(0, uri.IndexOf("webfile"));
                    if (PicturePath.Contains(strUri + "WebFile/"))
                    {
                        PicturePath = PicturePath.Replace(strUri + "WebFile/", "/WebFile/");
                    }
                    PicturePath = Server.MapPath(PicturePath);

                    base.InsertPicture("A11", PicturePath, 330, 210);
                }
                string strPath = "~/file/public/QualityManagement/test.xlsx";
                string filePath = Server.MapPath(strPath);
                base.SaveFile(filePath);

                if (System.IO.File.Exists(filePath))
                {
                   base.DownloadFile(strPath);
                }
                Dispose();

 参考文件:C# 常用的Excel 格式化操作 及 常用颜色定义对应Excel中颜色名

将DataTable和图片导出到Excel中