.net导出不规则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中颜色名
http://blog.163.com/jackie_dai@126/blog/static/34830764201171142848285/
