I have a Win Form Application in C# that reads in data from a database and creates 2 tables. I add the 2 tables into a dataset and then perform a mail merge with a word .docx template. This is the code that I execute:
string connectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;
DataSet dataSetAllData = new DataSet();
DataTable dataTableGeneral = new DataTable("GeneralData");
DataTable dataTableParcel = new DataTable("ParcelData");
// Add columns to the dataTableGeneral
dataTableGeneral.Columns.Add("ClientID", typeof(string));
dataTableGeneral.Columns.Add("FileNum", typeof(string));
dataTableGeneral.Columns.Add("Petitioner", typeof(string));
dataTableGeneral.Columns.Add("ClientAttention", typeof(string));
dataTableGeneral.Columns.Add("ClientName", typeof(string));
dataTableGeneral.Columns.Add("ClientAdd1", typeof(string));
dataTableGeneral.Columns.Add("ClientAdd2", typeof(string));
dataTableGeneral.Columns.Add("ClientAdd3", typeof(string));
dataTableGeneral.Columns.Add("ClientCityState", typeof(string));
dataTableGeneral.Columns.Add("ClientZipCode", typeof(string));
dataTableGeneral.Columns.Add("Street", typeof(string));
dataTableGeneral.Columns.Add("Locale", typeof(string));
dataTableGeneral.Columns.Add("TotalTaxSavings", typeof(string));
dataTableGeneral.Columns.Add("DueToHK", typeof(string));
// Add columns to the dataTableParcel
dataTableParcel.Columns.Add("ClientID", typeof(string));
dataTableParcel.Columns.Add("FileNum", typeof(string));
dataTableParcel.Columns.Add("C_Section", typeof(string));
dataTableParcel.Columns.Add("C_Block", typeof(string));
dataTableParcel.Columns.Add("C_Lot", typeof(string));
dataTableParcel.Columns.Add("OrigTaxableAV", typeof(string));
dataTableParcel.Columns.Add("ReducedTaxableAV", typeof(string));
dataTableParcel.Columns.Add("TaxableReduction", typeof(string));
dataTableParcel.Columns.Add("GeneralTaxRate", typeof(string));
dataTableParcel.Columns.Add("TotalTaxSavings", typeof(string));
List<HeaderData> lHeaderData = new List<HeaderData>();
List<ParcelData> lParcelData = new List<ParcelData>();
using (SqlConnection connection1 = new SqlConnection(connectionString))
{
string selectColumns = string.Join(", ", dataTableGeneral.Columns.Cast<DataColumn>().Where(c => c.ColumnName != "TotalTaxSavings" && c.ColumnName != "DueToHK").Select(c => "[" + c.ColumnName + "]"));
string query = @"WITH CTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY FileNum ORDER BY FileNum) AS RowNum FROM EVERYTHING) SELECT " +
selectColumns + " FROM CTE WHERE RowNum = 1 and FileNum is not NULL ORDER BY FileNum";
SqlDataAdapter adapter = new SqlDataAdapter(query, connection1);
connection1.Open();
adapter.Fill(dataTableGeneral);
connection1.Close();
string xmlFilePathGeneral = @"C:\Development\Herman Katz\Output_General.xml";
dataTableGeneral.WriteXml(xmlFilePathGeneral);
}
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
string selectColumns = string.Join(", ", dataTableParcel.Columns.Cast<DataColumn>().Select(c => "[" + c.ColumnName + "]"));
string query = @"SELECT ClientID, FileNum, C_Section, C_Block, C_Lot, OrigTaxableAV, ReducedTaxableAV, TaxableReduction, GeneralTaxRate, TotalTaxSavings FROM EVERYTHING WHERE FileNum is not NULL ORDER BY FILENUM, C_Section, C_Block, C_Lot ";
SqlDataAdapter adapter = new SqlDataAdapter(query, connection2);
connection2.Open();
adapter.Fill(dataTableParcel);
connection2.Close();
string xmlFilePathParcel = @"C:\Development\Herman Katz\Output_parcel.xml";
dataTableParcel.WriteXml(xmlFilePathParcel);
}
foreach (DataRow row in dataTableGeneral.Rows)
{
string clientAttention = row["ClientAttention"].ToString();
string clientName = row["ClientName"].ToString();
string clientAdd1 = row["ClientAdd1"].ToString();
string clientAdd2 = row["ClientAdd2"].ToString();
string clientAdd3 = row["ClientAdd3"].ToString();
string clientCityState = row["ClientCityState"].ToString();
string clientZipCode = row["ClientZipCode"].ToString();
string newClientName = string.IsNullOrEmpty(clientAttention) ? "" : clientAttention;
newClientName = string.IsNullOrEmpty(clientName) ? newClientName : newClientName + "\r\n" + clientName;
newClientName = string.IsNullOrEmpty(clientAdd1) ? newClientName : newClientName + "\r\n" + clientAdd1;
newClientName = string.IsNullOrEmpty(clientAdd2) ? newClientName : newClientName + "\r\n" + clientAdd2;
newClientName = string.IsNullOrEmpty(clientAdd3) ? newClientName : newClientName + "\r\n" + clientAdd3;
newClientName = string.IsNullOrEmpty(clientCityState) ? newClientName : newClientName + "\r\n" + clientCityState;
newClientName = string.IsNullOrEmpty(clientZipCode) ? newClientName : newClientName + " " + clientZipCode;
row.SetField("ClientName", newClientName);
string fileNum = row["FileNum"].ToString();
DataRow[] parcelRows = dataTableParcel.Select("FileNum = '" + fileNum + "'");
decimal totalTaxSavings = 0.0m;
decimal dueToHK = 0.0m;
foreach (DataRow parcelRow in parcelRows)
{
// Process each row from dataTableParcel with the same FileNum
// For example:
string sTotalTaxSavings = parcelRow["TotalTaxSavings"].ToString();
if (!string.IsNullOrEmpty(sTotalTaxSavings))
{
decimal dbtotalTaxSavings = decimal.Parse(sTotalTaxSavings) + .005m;
totalTaxSavings = totalTaxSavings + dbtotalTaxSavings;
}
}
dueToHK = totalTaxSavings * .25m;
row.SetField("TotalTaxSavings", totalTaxSavings.ToString("C"));
row.SetField("DueToHK", dueToHK.ToString("C"));
}
dataSetAllData.Tables.Add(dataTableGeneral);
dataSetAllData.Tables.Add(dataTableParcel);
//dataSetAllData.Relations.Add(dataSetAllData.Tables[1].Columns["FileNum"], dataSetAllData.Tables[0].Columns["FileNum"]);
// Create a DataRelation based on the "FileNum" column
DataRelation relation = new DataRelation("GeneralParcelRelation",
dataSetAllData.Tables["GeneralData"].Columns["FileNum"],
dataSetAllData.Tables["ParcelData"].Columns["FileNum"]);
// Add the relation to the DataSet
dataSetAllData.Relations.Add(relation);
string masterFilePath = @"C:\Development\Herman Katz\test.docx";
Aspose.Words.License license = new Aspose.Words.License();
license.SetLicense("Aspose.Total.060419.lic");
Aspose.Pdf.License oPdfLicense = new Aspose.Pdf.License();
oPdfLicense.SetLicense("Aspose.Total.060419.lic");
Aspose.Words.Document masterTemplate = new Aspose.Words.Document(masterFilePath);
DataTable jointData = dataSetAllData.Tables[0];
jointData.TableName = "JointData";
masterTemplate.MailMerge.ExecuteWithRegions(jointData);
masterTemplate.MailMerge.Execute(dataTableGeneral);
string finalFile = @"C:\Development\Herman Katz\results\finalOutput.pdf";
masterTemplate.Save(finalFile, Aspose.Words.SaveFormat.Pdf);
When I comment out the masterTemplate.MailMerge.ExecuteWithRegions(jointData); line, the resulting document gets saved and I am able to view the resulting .pdf file (although the data from this table is understandably not generated).
When I execute the masterTemplate.MailMerge.ExecuteWithRegions(jointData); line, the applicaiton runs up to the masterTemplate.Save(finalFile, Aspose.Words.SaveFormat.Pdf); line. Then, once this line executes, ASPOSE never returns to the application.
The resulting .pdf file is generated but it has 0 bytes.
It obviously has something to do with the data that is being passed to the MailMerge.ExecuteWithRegions but I really have no idea how to debug this.
I have uploaded the xml files that are generated as well as the word .docx template document I am using.
Any assistance is greatly appreciated.
Thank you.
ASPOSE.zip (43.4 KB)