public static void SetCellValue(Document doc, Cell cell, string val)
{
Aspose.Words.Paragraph p = cell.FirstParagraph;
p.RemoveAllChildren();
p.AppendChild(new Run(doc, val));
//cell.AppendChild(p);
}
/// <summary>
/// 将DataTable表格对象中数据输出到Aspose.Word的表格对象中
/// </summary>
/// <param name="doc">Aspose.Word的document</param>
/// <param name="docTable">Aspose.Word表格对象</param>
/// <param name="source">源数据表格对象</param>
/// <param name="startRow">开始行，不能小于1</param>
/// <param name="startCol">开始列，默认为0</param>
public static void FillDataToTable(Document doc, Table docTable, DataTable source, int startRow = 1, int startCol = 0)
{
var count = source.Rows.Count;
if (startRow < 1)
{
throw new ArgumentException("startRow不能小于1");
}
if (count > 0)
{
for (var i = 0; i < count - 1; i++)
{
var roww = docTable.Rows[startRow];
var row = roww.Clone(true); //复制
docTable.Rows.Insert(startRow + i, row); //将复制的行插入当前行的上方
}
for (var i = 1; i <= count; i++)
{
var row = docTable.Rows[startRow + i - 1];
for (var j = 0; j < source.Columns.Count; j++)
{
var cell = row.Cells[j + startCol];
Aspose.Words.Paragraph p = cell.FirstParagraph;
p.AppendChild(new Run(doc, source.Rows[i - 1][j] + string.Empty));
cell.FirstParagraph.Remove();
cell.AppendChild(p);
}
}
}
}
public void AddBlankPage(int count)
{
if (count > 0)
{
AW.Paragraph par = null;
AW.Run run = null;
for (int i = 0; i < count; i++)
{
par = new AW.Paragraph(wDoc);
run = new AW.Run(wDoc, AW.ControlChar.PageBreak);
par.AppendChild(run);
wDoc.LastSection.Body.AppendChild(par);
}
}
}
private void InsertWatermarkMultiText(Aspose.Words.Document doc)
{
SetParagraphsBackgroundNoColor(doc);
GroupShape groupShape = new GroupShape(doc);
groupShape.AllowOverlap = false;
groupShape.BehindText = true;
groupShape.Width = 300;
groupShape.Height = 200;
groupShape.Rotation = m_rotation;
groupShape.WrapType = WrapType.None;
// groupShape.WrapSide = WrapSide.Default;
var watermark = SetWatermarkFormat(doc, m_waterMarkMultiText.BigText);
// watermark.Bounds = m_rotation == 0 ? new RectangleF(13, 232, 400, 150) : new RectangleF(-90, 150, 400, 200);
watermark.Bounds = m_rotation == 0 ? new RectangleF(0, 0, 400, 150) : new RectangleF(0, 0, 400, 200);
groupShape.AppendChild(watermark);
watermark = SetWatermarkFormat(doc, m_waterMarkMultiText.SmallText);
// watermark.Bounds = m_rotation == 0 ? new RectangleF(13, 352, 400, 20) : new RectangleF(-90, 310, 400, 20);
watermark.Bounds = m_rotation == 0 ? new RectangleF(0, 140, 400, 20) : new RectangleF(0, 181, 400, 20);
groupShape.AppendChild(watermark);
groupShape.CoordSize = new System.Drawing.Size(300, 200);
groupShape.RelativeHorizontalPosition = RelativeHorizontalPosition.Page;
groupShape.RelativeVerticalPosition = RelativeVerticalPosition.Page;
groupShape.VerticalAlignment = Aspose.Words.Drawing.VerticalAlignment.Center;
groupShape.HorizontalAlignment = Aspose.Words.Drawing.HorizontalAlignment.Center;
Paragraph watermarkPara = new Aspose.Words.Paragraph(doc);
watermarkPara.AppendChild(groupShape);
foreach (Aspose.Words.Section sect in doc.Sections)
{
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderPrimary);
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderFirst);
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderEven);
}
}
private void InsertWatermarkText(Aspose.Words.Document doc)
{
SetParagraphsBackgroundNoColor(doc);
var watermark = SetWatermarkFormat(doc, m_waterMarkText);
watermark.Width = 500;
watermark.Height = 100;
//角度
watermark.Rotation = m_rotation;
Aspose.Words.Paragraph watermarkPara = new Aspose.Words.Paragraph(doc);
watermarkPara.AppendChild(watermark);
foreach (Aspose.Words.Section sect in doc.Sections)
{
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderPrimary);
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderFirst);
InsertWatermarkIntoHeader(watermarkPara, sect, HeaderFooterType.HeaderEven);
}
}
public string AsposePost(string docxPath, string cmt)
{
try
{
// Open an existing document to add comments to a paragraph.
Aspose.Words.Document doc = new Aspose.Words.Document(docxPath);
Node[] nodes = doc.GetChildNodes(NodeType.Paragraph, true).ToArray();
//E.g this is the Paragraph to which comments will added
Aspose.Words.Paragraph paragraph = (Aspose.Words.Paragraph)nodes[1];
Aspose.Words.DocumentBuilder builder = new Aspose.Words.DocumentBuilder(doc);
// Create a Comment.
Aspose.Words.Comment comment = new Aspose.Words.Comment(doc);
// Insert some text into the comment.
Aspose.Words.Paragraph commentParagraph = new Aspose.Words.Paragraph(doc);
commentParagraph.AppendChild(new Aspose.Words.Run(doc, cmt));
comment.AppendChild(commentParagraph);
//Move to paragraph where comments will be added
builder.MoveTo(paragraph);
// Insert comment
builder.InsertNode(comment);
var newDocxPath = Path.GetTempFileName().Replace(".tmp", ".docx");
// Save output document.
doc.Save(newDocxPath);
return(newDocxPath);
}
catch (Exception)
{
return("undone");
}
}
public object SetWordFile(string TemplateNameID, string ContractNumber, string fileName)
{
string GUID = System.Guid.NewGuid().ToString();
var flag = true;
try
{
//获取sql语句、模版名称
string SQL = "SELECT TemplateName,sqlstr,DataBaseUrl FROM I_TemplateFile WHERE ObjectID ='" + TemplateNameID + "'";
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(SQL);
//模版名称
var TemplateName = dt.Rows[0]["TemplateName"];
var datasource = dt.Rows[0]["DataBaseUrl"].ToString();
if (string.IsNullOrEmpty(datasource))
{
datasource = "cap";
}
string SQL1 = "SELECT sqlstring FROM I_TemplateFile_SQL WHERE ParentObjectID ='" + TemplateNameID + "'";
DataTable dtstr = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(SQL1);
//模版地址
string path = System.AppDomain.CurrentDomain.BaseDirectory;
path += "Sheets\\Model\\" + TemplateName + ".doc";
var document = new Document(path);
var builder = new DocumentBuilder(document);
//查模版维护表获取标签值
for (int i = 0; i < dtstr.Rows.Count; i++)
{
string sqlstr = dtstr.Rows[i]["sqlstring"].ToString();
sqlstr = HttpUtility.HtmlDecode(sqlstr); // wangxg 19.12 HTML解码
string sql1 = string.Format(sqlstr, ContractNumber.Trim()); // "Br-A000056000");
DataTable dt1 = ExecuteDataTableSql(datasource, sql1);
if (dt1.Rows.Count > 0)
{
//确定为还款计划表
if (dt1.Columns[0].ColumnName == "CUSTOMER_RENTAL_ID")
{
NodeCollection allTables = document.GetChildNodes(NodeType.Table, true); //拿到所有表格
Aspose.Words.Tables.Table table = allTables[2] as Aspose.Words.Tables.Table; //拿到第二个表格
for (int l = 0; l < dt1.Rows.Count; l++)
{
// 复制上一行
Aspose.Words.Tables.Row clonedRow = (Aspose.Words.Tables.Row)table.LastRow.Clone(true);
for (int j = 0; j < clonedRow.Count; j++)
{
clonedRow.Cells[j].RemoveAllChildren(); //清除单元格中内容
Aspose.Words.Paragraph p = new Aspose.Words.Paragraph(document);
var CellVal = dt1.Rows[l][j] + string.Empty;
CellVal = CellVal.Replace("/", "-").Replace("0:00:00", "");
p.AppendChild(new Aspose.Words.Run(document, CellVal));
clonedRow.Cells[j].AppendChild(p);
}
table.Rows.Add(clonedRow); //添加一行
}
}
else
{
//取标签 给标签赋值
foreach (DataColumn dc in dt1.Columns)
{
if (document.Range.Bookmarks[dc.ColumnName] != null)
{
document.Range.Bookmarks[dc.ColumnName].Text = dt1.Rows[0][dc.ColumnName].ToString();
}
}
}
}
}
//文件名称//存在则覆盖，采用原来fileName
if (string.IsNullOrEmpty(fileName))
{
fileName = ContractNumber + TemplateName + GUID;
}
//测试 判断是否重新生成
//else
//{
// string filePath = Server.MapPath("~/TemplateFile/") + fileName + ".pdf";
// document.Save(filePath, SaveFormat.Pdf);
//}
//string filePathDocold = Server.MapPath("~/TemplateFile/Doc/") + fileName + ".DOC";
//string filePathDoc = System.AppDomain.CurrentDomain.BaseDirectory + "\\PrintFilePath\\" + fileName + ".doc";
string filePathDoc = GetfilePath() + fileName + ".DOC";
OThinker.H3.Controllers.AppUtility.Engine.LogWriter.Write("filePathDoc=" + filePathDoc);
//PdfSaveOptions saveOption = new PdfSaveOptions();
//saveOption.SaveFormat = Aspose.Words.SaveFormat.Pdf;
////user pass 设置了打开时，需要密码
////owner pass 控件编辑等权限
//PdfEncryptionDetails encryptionDetails = new PdfEncryptionDetails(string.Empty, "PasswordHere", PdfEncryptionAlgorithm.RC4_128);
//encryptionDetails.Permissions = PdfPermissions.DisallowAll;
//saveOption.EncryptionDetails = encryptionDetails;
//加密文档保存到指定地点---//使用ntko不需要使用pdf暂时注释
// document.Save(filePath, saveOption);
//如不不需要保存doc 请注释下句
document.Save(filePathDoc, SaveFormat.Doc);
}
catch (Exception e)
{
OThinker.H3.Controllers.AppUtility.Engine.LogWriter.Write(e.ToString());
flag = false;
}
return(new
{
Flag = flag,
FileName = fileName
});
}
public object SetWordFile(string TemplateNameID, string ContractNumber)
{
string GUID = Guid.NewGuid().ToString();
var flag = true;
var message = "";
var TemplateName = "";
var instanceType = "";
var SavedDataField = "";
string fileName = "";
double rate_fixed = 0;
double rate_base = 0;
double rate_float = 0;
bool isNewContract = false;
try
{
//获取sql语句、模版名称
string SQL = "SELECT TemplateName,sqlstr,DataBaseUrl,instancetype,SavedDataField FROM I_TemplateFile WHERE ObjectID ='" + TemplateNameID + "'";
DataTable dt = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(SQL);
//模版名称
TemplateName = dt.Rows[0]["TemplateName"] + string.Empty;
instanceType = dt.Rows[0]["instancetype"] + string.Empty;
SavedDataField = dt.Rows[0]["SavedDataField"] + string.Empty;
string loanContractField = ConfigurationManager.AppSettings["LoanContractSavedDataField"] + string.Empty;
if (string.IsNullOrEmpty(SavedDataField))
{
return(new
{
Flag = false,
FileName = "",
Id = "",
Message = "未设置保存的数据项编码，请联系管理员"
});
}
#region 处理打印合同
// 打印合同
if (loanContractField.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).Contains(SavedDataField))
{
string sql = $"SELECT ACTUAL_RTE FROM APPLICATION_CONTRACT_VW WHERE APPLICATION_NUMBER = '{ContractNumber}'";
decimal actualRte = Convert.ToDecimal((ExecuteDataTableSql("cap", sql).Rows[0]["ACTUAL_RTE"] + string.Empty));
sql = $"SELECT STATUS_CODE FROM APPLICATION@TO_CMS WHERE APPLICATION_NUMBER = '{ContractNumber}'";
string statusCode = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(sql) + string.Empty;
if (statusCode == "55")
{
// 已放款状态，取上一次的打印记录
sql = $@"SELECT * FROM (
SELECT TEMPLATENAME, RATE_FIXED, RATE_BASE, RATE_FLOAT FROM C_CONTRACTDETAIL
WHERE APPLICATIONNUMBER = '{ContractNumber}'
ORDER BY CREATEDTIME DESC)
WHERE ROWNUM = 1";
DataTable recordTable = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
if (recordTable.Rows.Count == 0)
{
// 没有打印记录，返回false
return(new
{
Flag = false,
Message = "没有找到打印记录"
});
}
else
{
TemplateName = recordTable.Rows[0]["TEMPLATENAME"] + string.Empty;
string oldUserTemplateName = ConfigurationManager.AppSettings["OldUserLoanContractName"] + string.Empty;
string oldOrgTemplateName = ConfigurationManager.AppSettings["OldOrgLoanContractName"] + string.Empty;
// 有打印记录
// 个人模板并且模板名称不等于维护的老个人模板名称
// 机构模板并且模板名称不等于维护的老机构模板名称
if ((instanceType == "个人" && TemplateName != oldUserTemplateName) ||
(instanceType == "机构" && TemplateName != oldOrgTemplateName))
{
isNewContract = true;
rate_fixed = Convert.ToDouble(recordTable.Rows[0]["RATE_FIXED"]);
rate_base = Convert.ToDouble(recordTable.Rows[0]["RATE_BASE"]);
rate_float = Convert.ToDouble(recordTable.Rows[0]["RATE_FLOAT"]);
}
}
}
else
{
//未放款状态，打印新合同
string rateSql = $@"
SELECT {actualRte} RATE_FIXED, RATEBASE RATE_BASE, {actualRte} - RATEBASE AS RATE_FLOAT FROM (
SELECT HTLL.RATEBASE FROM I_HTLL2 HTLL
INNER JOIN (
SELECT
CASE
WHEN LEASE_TERMS/12 > 1 THEN '5年'
ELSE '1年'
END LEASE_TERMS, createdtime FROM
(select a.createdtime, b.lease_term_in_month as lease_terms
from i_application a
inner join i_Contract_Detail b on b.parentobjectid = a.objectid
where a.application_number = '{ContractNumber}'
)
) CONTRACT
ON HTLL.DURATION = CONTRACT.LEASE_TERMS and to_date('{DateTime.Now.ToShortDateString()}', 'yyyy/MM/dd') >= htll.enabledtime
WHERE HTLL.ISDELETE = 0
ORDER BY HTLL.ENABLEDTIME DESC
) ";
DataTable rateTable = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(rateSql);
// 没有维护LPR，就不让打印
if (rateTable.Rows.Count == 0)
{
return(new
{
Flag = false,
Message = "没有找到对应LPR记录,请维护"
});
}
else
{
isNewContract = true;
rate_fixed = Convert.ToDouble(rateTable.Rows[0]["RATE_FIXED"]);
rate_base = Convert.ToDouble(rateTable.Rows[0]["RATE_BASE"]);
rate_float = Convert.ToDouble(rateTable.Rows[0]["RATE_FLOAT"]);
}
}
}
#endregion
var datasource = dt.Rows[0]["DataBaseUrl"] + string.Empty;
if (string.IsNullOrEmpty(datasource))
{
datasource = "cap";
}
string SQL1 = "SELECT sqlstring FROM I_TemplateFile_SQL WHERE ParentObjectID ='" + TemplateNameID + "'";
DataTable dtstr = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(SQL1);
//模版地址
string path = AppDomain.CurrentDomain.BaseDirectory;
path += "Sheets\\Model\\" + TemplateName + ".doc";
var document = new Document(path);
var builder = new DocumentBuilder(document);
//查模版维护表获取标签值
for (int i = 0; i < dtstr.Rows.Count; i++)
{
string sqlstr = dtstr.Rows[i]["sqlstring"].ToString();
sqlstr = HttpUtility.HtmlDecode(sqlstr); // wangxg 19.12 HTML解码
string sql1 = string.Format(sqlstr, ContractNumber.Trim()); // "Br-A000056000");
DataTable dataSource = ExecuteDataTableSql(datasource, sql1);
//记录打印查询出的数据源；
AppUtility.Engine.LogWriter.Write(ContractNumber + TemplateName + GUID + "数据源" + (i + 1) + "\r\n" +
Newtonsoft.Json.JsonConvert.SerializeObject(dataSource));
if (dataSource.Rows.Count > 0)
{
//确定为还款计划表
if (dataSource.Columns[0].ColumnName == "CUSTOMER_RENTAL_ID")
{
NodeCollection allTables = document.GetChildNodes(NodeType.Table, true); //拿到所有表格
Aspose.Words.Tables.Table table = allTables[2] as Aspose.Words.Tables.Table; //拿到第二个表格
for (int l = 0; l < dataSource.Rows.Count; l++)
{
// 复制上一行
Aspose.Words.Tables.Row clonedRow = (Aspose.Words.Tables.Row)table.LastRow.Clone(true);
for (int j = 0; j < clonedRow.Count; j++)
{
clonedRow.Cells[j].RemoveAllChildren(); //清除单元格中内容
Aspose.Words.Paragraph p = new Aspose.Words.Paragraph(document);
var CellVal = dataSource.Rows[l][j] + string.Empty;
CellVal = CellVal.Replace("/", "-").Replace("0:00:00", "");
p.AppendChild(new Aspose.Words.Run(document, CellVal));
clonedRow.Cells[j].AppendChild(p);
}
table.Rows.Add(clonedRow); //添加一行
}
}
else
{
//取标签 给标签赋值
foreach (DataColumn dc in dataSource.Columns)
{
if (document.Range.Bookmarks[dc.ColumnName] != null)
{
if (instanceType.ToString() == "正源" && dc.DataType.ToString() == "System.DateTime")
{
document.Range.Bookmarks[dc.ColumnName].Text = Convert.ToDateTime(dataSource.Rows[0][dc.ColumnName].ToString()).ToString("yyyy-MM-dd");
}
else
{
document.Range.Bookmarks[dc.ColumnName].Text = dataSource.Rows[0][dc.ColumnName].ToString();
}
}
}
}
}
}
// 如果是新合同，给rate_fixed, rate_base, rate_float三个书签赋值
if (isNewContract)
{
document.Range.Bookmarks["rate_fixed"].Text = rate_fixed.ToString();
document.Range.Bookmarks["rate_base"].Text = rate_base.ToString();
document.Range.Bookmarks["rate_float"].Text = rate_float.ToString();
}
fileName = ContractNumber + TemplateName + GUID;
#region Save
string tempfilePath = Server.MapPath("~/TempImages/") + GUID + ".doc";
document.Save(tempfilePath, SaveFormat.Doc);
SaveAttachment(
FilePath: tempfilePath,
ApplicationNumber: ContractNumber,
TemplateId: TemplateNameID,
TemplateName: TemplateName,
AttachmentId: GUID,
FileName: fileName + ".doc",
SchemaCode: ActionContext.SchemaCode,
DataField: SavedDataField,
BizObjectId: ActionContext.BizObjectID,
InstanceType: instanceType,
InstanceId: ActionContext.InstanceId,
Rate_Fixed: rate_fixed,
Rate_Base: rate_base,
Rate_Float: rate_float
);
if (instanceType.ToString() == "正源")
{
string tempfilePathPDF = Server.MapPath("~/TempImages/") + GUID + ".pdf";
document.Save(tempfilePathPDF, SaveFormat.Pdf);
SaveAttachment(
FilePath: tempfilePathPDF,
ApplicationNumber: ContractNumber,
TemplateId: TemplateNameID,
TemplateName: TemplateName,
AttachmentId: Guid.NewGuid().ToString(),
FileName: fileName + ".pdf",
SchemaCode: ActionContext.SchemaCode,
DataField: SavedDataField,
BizObjectId: ActionContext.BizObjectID,
InstanceType: instanceType,
InstanceId: ActionContext.InstanceId,
Rate_Fixed: rate_fixed,
Rate_Base: rate_base,
Rate_Float: rate_float
);
}
#endregion
}
catch (Exception e)
{
AppUtility.Engine.LogWriter.Write(e.ToString());
flag = false;
message = e.Message;
}
return(new
{
Flag = flag,
FileName = fileName,
Id = GUID,
Message = message
});
}