// Fourth stage. For each Order ID create a detailed document that will be inserted in place of the DOCVARIABLE field.
// This is the final stage and the Product.Orders template does not contain DOCVARIABLE fields. So, further processing is not required.
void richServerDetailProcessor_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
int currentProductID = GetID(e.Arguments[0].Value);
if (currentProductID == -1)
{
return;
}
if (productID != currentProductID)
{
// Get data source that contains orders for the specified product and supplier.
dataDetailedForOrders = (List <OrderDetail>)GetOrderDataFilteredbyProductAndSupplier(supplierID, currentProductID);
productID = currentProductID;
}
if (e.VariableName == "OrderDetails")
{
IRichEditDocumentServer richServerOrdersTemplate = mainRichEdit.CreateDocumentServer();
RtfLoadHelper.Load("detaildetail.rtf", richServerOrdersTemplate);
richServerOrdersTemplate.Options.MailMerge.DataSource = dataDetailedForOrders;
IRichEditDocumentServer richServerDetailDetailProcessor = mainRichEdit.CreateDocumentServer();
MailMergeOptions options = richServerOrdersTemplate.CreateMailMergeOptions();
options.MergeMode = MergeMode.JoinTables;
richServerOrdersTemplate.MailMerge(options, richServerDetailDetailProcessor);
e.Value = richServerDetailDetailProcessor;
e.Handled = true;
}
}
// Fourth stage. For each Order ID create a detailed document that will be inserted in place of the DOCVARIABLE field.
// This is the final stage and the Product.Orders template does not contain DOCVARIABLE fields. So, further processing is not required.
void richServerDetail_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
int currentProductID = GetID(e.Arguments[0].Value);
if (currentProductID == -1)
{
return;
}
if (productID != currentProductID)
{
// Get data source that contains orders for the specified product.
// The data source is obtained from the data already filtered by supplier.
dataDetailedForOrders = GetOrderDataFilteredbyProductAndSupplier(currentProductID);
productID = currentProductID;
}
if (e.VariableName == "OrderDetails")
{
RichEditDocumentServer richServerDetailDetail = new RichEditDocumentServer();
MailMergeOptions options = ordersRichEdit.CreateMailMergeOptions();
options.DataSource = dataDetailedForOrders;
options.MergeMode = MergeMode.JoinTables;
ordersRichEdit.MailMerge(options, richServerDetailDetail);
e.Value = richServerDetailDetail;
e.Handled = true;
}
}
private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
int count = ((List <Employee>)richEditControl1.Options.MailMerge.DataSource).Count;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "PDF files (*.pdf)|*.pdf|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
using (RichEditDocumentServer docServer = new RichEditDocumentServer()) {
MailMergeOptions options = richEditControl1.CreateMailMergeOptions();
for (int i = 0; i < count; i++)
{
string filename = string.Format("{0}{1}.pdf", saveFileDialog1.FileName, (i + 1).ToString());
options.FirstRecordIndex = options.LastRecordIndex = i;
using (FileStream fs = new FileStream(filename, FileMode.Create, System.IO.FileAccess.Write)) {
richEditControl1.MailMerge(options, docServer.Document);
docServer.ExportToPdf(fs);
}
}
}
}
}
private void ResultRichEdit_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
//Check whether the event is raised to the required field:
if (e.VariableName == "Categories")
{
//Provide the data source for the next document part:
masterRichEdit.Options.MailMerge.DataSource = categories;
//Create a new RichEditDocumentServer for further processing:
IRichEditDocumentServer result = masterRichEdit.CreateDocumentServer();
//Subscribe the new instance to the CalculateDocumentVariable event to handle the detail part:
result.CalculateDocumentVariable += result_CalculateDocumentVariable;
//Set additional mail merge options if necessary:
MailMergeOptions options = masterRichEdit.CreateMailMergeOptions();
options.LastRecordIndex = 4;
//Merge the document and pass it to the RichEditDocumentServer:
masterRichEdit.MailMerge(options, result.Document);
result.CalculateDocumentVariable -= result_CalculateDocumentVariable;
e.Value = result;
e.Handled = true;
}
}
private void MergeToNewDocument()
{
targetRichEditControl.ApplyTemplate();
MailMergeOptions options = sourceRichEditControl.CreateMailMergeOptions();
options.MergeMode = MergeMode.NewSection;
options.LastRecordIndex = 5;
sourceRichEditControl.MailMerge(options, targetRichEditControl.Document);
Document targetDocument = targetRichEditControl.Document;
InsertContentHeading(targetDocument);
Field field = targetDocument.Fields.Create(targetDocument.Paragraphs[1].Range.Start, "TOC \\h");
targetDocument.InsertSection(field.Range.End);
field.Update();
ParagraphStyle paragraphStyle = targetDocument.ParagraphStyles["TOC 1"];
if (paragraphStyle != null)
{
paragraphStyle.Bold = true;
}
resultingDocumentTabItem.IsEnabled = true;
tabControl.SelectedItem = resultingDocumentTabItem;
}
private void mergeToFileItem_ItemClick(object sender, ItemClickEventArgs e)
{
MailMergeOptions myMergeOptions = richEditControl1.Document.CreateMailMergeOptions();
myMergeOptions.DataSource = new SampleData();
myMergeOptions.FirstRecordIndex = 1;
myMergeOptions.LastRecordIndex = 3;
myMergeOptions.MergeMode = MergeMode.NewSection;
SaveFileDialog fileDialog = new SaveFileDialog();
fileDialog.Filter = "MS Word 2007 documents (*.docx)|*.docx|All files (*.*)|*.*";
fileDialog.FilterIndex = 1;
fileDialog.RestoreDirectory = true;
DialogResult dialogResult = fileDialog.ShowDialog();
if (dialogResult == System.Windows.Forms.DialogResult.OK)
{
string fName = fileDialog.FileName;
if (fName != "")
{
richEditControl1.Document.MailMerge(myMergeOptions, fileDialog.FileName, DocumentFormat.OpenXml);
System.Diagnostics.Process.Start(fileDialog.FileName);
}
}
}
private void btnMailMerge_Click(object sender, EventArgs e)
{
MailMergeOptions myMergeOptions = richEditControl1.Document.CreateMailMergeOptions();
myMergeOptions.MergeMode = MergeMode.NewSection;
richEditControl1.Document.MailMerge(myMergeOptions, richEditControl2.Document);
xtraTabControl1.SelectedTabPageIndex = 1;
}
private void barButtonMergeToNewDocument_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
MailMergeOptions options = richEditControl1.Document.CreateMailMergeOptions();
options.MergeMode = MergeMode.NewSection;
frmResultingDocument form = new frmResultingDocument();
richEditControl1.Document.MailMerge(options, form.Document);
form.Show(this);
}
private void MailMergeToNewFile(MailMergeOptions options)
{
string fileName = System.IO.Directory.GetCurrentDirectory() + @"\..\..\MailMergeResult.rtf";
richEditControl1.Document.MailMerge(options, fileName, DocumentFormat.Rtf);
Process process = new Process();
process.StartInfo.FileName = fileName;
process.Start();
}
private void mergeToNewDocumentItem_ItemClick(object sender, ItemClickEventArgs e)
{
#region #merge
MailMergeOptions myMergeOptions = richEditControl1.Document.CreateMailMergeOptions();
myMergeOptions.FirstRecordIndex = 1;
myMergeOptions.LastRecordIndex = 3;
myMergeOptions.MergeMode = MergeMode.NewSection;
richEditControl1.Document.MailMerge(myMergeOptions, richEditControl2.Document);
#endregion #merge
xtraTabControl1.SelectedTabPageIndex = 1;
}
private void MailMergeToNewControl(MailMergeOptions options)
{
Form form = new Form();
RichEditControl control = new RichEditControl();
richEditControl1.Document.MailMerge(options, control.Document);
control.Dock = DockStyle.Fill;
form.Controls.Add(control);
form.Show(this);
}
private void buttonShowResult_Click(object sender, EventArgs e)
{
MailMergeOptions myMergeOptions = richEditControl.Document.CreateMailMergeOptions();
myMergeOptions.FirstRecordIndex = 0;
myMergeOptions.LastRecordIndex = _document.Lines.Count - 1;
myMergeOptions.MergeMode = MergeMode.NewSection;
richEditControl.Document.MailMerge(myMergeOptions, richEditControlResult.Document);
tabControl.SelectedTabPageIndex = 1;
}
private void performMailMergeItem_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
richEditCategoryTemplate.Options.MailMerge.DataSource = NorthwindDataProvider.Categories;
MailMergeOptions mailMergeOptions = richEditCategoryTemplate.CreateMailMergeOptions();
mailMergeOptions.MergeMode = MergeMode.NewParagraph;
richEditCategoryTemplate.MailMerge(mailMergeOptions, richEditResult);
tabControlRichEditControls.SelectedTabPage = tabPageResult;
}
private void CalculateMailMergeOptions(MergeSimpleDataForm form, MailMergeOptions options)
{
if (form.MergeRecords == MergeRecords.FromInterval)
{
options.FirstRecordIndex = Math.Max(0, ((MergeSimpleDataForm)form).StartIndex - 1);
options.LastRecordIndex = Math.Min(NorthwindDataProvider.Categories.Rows.Count - 1, ((MergeSimpleDataForm)form).EndIndex - 1);
}
else if (form.MergeRecords == MergeRecords.Current)
{
options.FirstRecordIndex = dataNavigator1.Position;
options.LastRecordIndex = dataNavigator1.Position;
}
}
void result_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
ArgumentCollection arguments = e.Arguments;
int currentCategoryID = GetID(arguments[0].Value);
if (currentCategoryID == -1)
{
return;
}
if (categoryID != currentCategoryID)
{
currentDataSetProducts = GetData(currentCategoryID).ToList();
categoryID = currentCategoryID;
}
if (e.VariableName == "Products")
{
detailRichEditControl.Options.MailMerge.DataSource = currentDataSetProducts;
IRichEditDocumentServer result = detailRichEditControl.CreateDocumentServer();
MailMergeOptions options = detailRichEditControl.CreateMailMergeOptions();
options.MergeMode = MergeMode.JoinTables;
result.CalculateDocumentVariable += detail_CalculateDocumentVariable;
detailRichEditControl.MailMerge(options, result.Document);
result.CalculateDocumentVariable -= detail_CalculateDocumentVariable;
e.Value = result;
e.Handled = true;
}
if (e.VariableName == "LowestPrice")
{
e.Value = currentDataSetProducts.Min(p => p.UnitPrice);
e.Handled = true;
}
if (e.VariableName == "HighestPrice")
{
e.Value = currentDataSetProducts.Max(p => p.UnitPrice);
e.Handled = true;
}
if (e.VariableName == "ItemsCount")
{
e.Value = currentDataSetProducts.Count();
e.Handled = true;
}
if (e.VariableName == "TotalSales")
{
e.Value = GetTotalSales(arguments);
e.Handled = true;
}
}
private void button2_Click(object sender, EventArgs e)
{
MailMergeOptions myMergeOptions = richEditControl1.Document.CreateMailMergeOptions();
myMergeOptions.MergeMode = MergeMode.NewParagraph;
RichEditDocumentServer server = new RichEditDocumentServer();
server.CalculateDocumentVariable += new CalculateDocumentVariableEventHandler(Document_CalculateDocumentVariable);
richEditControl1.Document.MailMerge(myMergeOptions, server.Document);
richEditControl1.CreateNewDocument();
richEditControl1.Document.AppendDocumentContent(server.Document.Range);
}
private void button1_Click(object sender, EventArgs e)
{
MailMergeOptions mailMergeOptions = richEditControl1.Document.CreateMailMergeOptions();
mailMergeOptions.MergeMode = MergeMode.JoinTables;
RichEditDocumentServer server = new RichEditDocumentServer();
server.CalculateDocumentVariable += server_CalculateDocumentVariable;
richEditControl1.Document.MailMerge(mailMergeOptions, server.Document);
richEditControl1.LoadDocument("HeaderTemplate.rtf");
richEditControl1.Document.AppendDocumentContent(server.Document.Range);
}
private Stream ExecuteMerge(string templateName, DocumentFormat documentFormat)
{
Stream result = new MemoryStream();
MailMergeOptions mailMergeOptions = documentServer.CreateMailMergeOptions();
if (templateName == "preview1")
{
documentServer.LoadDocument(Page.MapPath("~/App_Data/InvoicesDetail.rtf"));
List <Invoice> invoices = new List <Invoice>(10);
invoices.Add(new Invoice(0, "Invoice1", 10.0m));
invoices.Add(new Invoice(1, "Invoice2", 15.0m));
invoices.Add(new Invoice(2, "Invoice3", 20.0m));
mailMergeOptions.DataSource = invoices;
}
else if (templateName == "preview2")
{
documentServer.LoadDocument(Page.MapPath("~/App_Data/SamplesDetail.rtf"));
mailMergeOptions.DataSource = ManualDataSet.CreateData().Tables[0];
}
else if (templateName == "all")
{
Stream part1 = ExecuteMerge("preview1", documentFormat);
Stream part2 = ExecuteMerge("preview2", documentFormat);
part1.Seek(0, SeekOrigin.Begin);
part2.Seek(0, SeekOrigin.Begin);
documentServer.LoadDocument(part1, documentFormat);
documentServer.Document.AppendDocumentContent(part2, documentFormat);
documentServer.SaveDocument(result, documentFormat);
return(result);
}
documentServer.Options.MailMerge.ViewMergedData = true;
documentServer.Options.Export.Html.EmbedImages = true;
mailMergeOptions.MergeMode = MergeMode.JoinTables;
documentServer.MailMerge(mailMergeOptions, result, documentFormat);
return(result);
}
private void result_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
//Check whether the event is raised for the required field:
if (e.VariableName == "Products")
{
//Provide the detail part with the data source:
detailRichEdit.Options.MailMerge.DataSource = products;
//Create an intermediate document server instance:
IRichEditDocumentServer result = detailRichEdit.CreateDocumentServer();
//Set the merged ranges delimitation and a number of records to be merged:
MailMergeOptions options = detailRichEdit.CreateMailMergeOptions();
options.MergeMode = MergeMode.JoinTables;
options.LastRecordIndex = 10;
//Provide a procedure for further processing:
result.CalculateDocumentVariable += detail_CalculateDocumentVariable;
// Create a merged document with a detail template:
detailRichEdit.MailMerge(options, result.Document);
result.CalculateDocumentVariable -= detail_CalculateDocumentVariable;
e.Value = result;
e.Handled = true;
}
//Format other merged fields:
if (e.VariableName == "LowestPrice")
{
e.Value = String.Format(cultureInfo, "{0:C2}", products.Compute("Min(UnitPrice)", String.Empty));
e.Handled = true;
}
if (e.VariableName == "HighestPrice")
{
e.Value = String.Format(cultureInfo, "{0:C2}", products.Compute("Max(UnitPrice)", String.Empty));
e.Handled = true;
}
if (e.VariableName == "ItemsCount")
{
e.Value = products.Rows.Count;
e.Handled = true;
}
}
void richEditResult_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
int categoryId = -1;
if (Int32.TryParse(e.Arguments[0].Value, out categoryId))
{
richEditProductTemplate.Options.MailMerge.DataSource = NorthwindDataProvider.GetProductsByCategoryId(categoryId);
RichEditDocumentServer documentServerProducts = new RichEditDocumentServer();
MailMergeOptions mailMergeOptions = richEditProductTemplate.CreateMailMergeOptions();
mailMergeOptions.MergeMode = MergeMode.JoinTables;
richEditProductTemplate.MailMerge(mailMergeOptions, documentServerProducts);
e.Value = documentServerProducts;
e.Handled = true;
}
}
// Third stage. For each Product ID create a detailed document that will be inserted in place of the DOCVARIABLE field.
void richServerMasterProcessor_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
int currentSupplierID = GetID(e.Arguments[0].Value);
if (currentSupplierID == -1)
{
return;
}
if (supplierID != currentSupplierID)
{
// Get data source that contains products for the specified supplier.
dataDetailedForProducts = (List <Product>)GetProductsDataFilteredbySupplier(currentSupplierID);
supplierID = currentSupplierID;
}
if (e.VariableName == "Product")
{
// Create a document container and load a document containing the Product header section (detail section)
IRichEditDocumentServer richServerProductsTemplate = mainRichEdit.CreateDocumentServer();
RtfLoadHelper.Load("detail.rtf", richServerProductsTemplate);
// Create a text engine to process a document after the mail merge.
IRichEditDocumentServer richServerDetailProcessor = mainRichEdit.CreateDocumentServer();
// Specify data source for mail merge.
richServerProductsTemplate.Options.MailMerge.DataSource = dataDetailedForProducts;
// Specify that the resulting table should be joined with the header table.
// Do not specify this option if calculated fields are not within table cells.
MailMergeOptions options = richServerProductsTemplate.CreateMailMergeOptions();
options.MergeMode = MergeMode.JoinTables;
// Provide a procedure for further processing.
richServerDetailProcessor.CalculateDocumentVariable += new CalculateDocumentVariableEventHandler(richServerDetailProcessor_CalculateDocumentVariable);
// Create a merged document using the Product template. The document will contain DOCVARIABLE fields with OrderID arguments.
// The CalculateDocumentVariable event for the richServerDetail fires.
richServerProductsTemplate.MailMerge(options, richServerDetailProcessor);
richServerDetailProcessor.CalculateDocumentVariable -= richServerDetailProcessor_CalculateDocumentVariable;
// Return the document to insert.
e.Value = richServerDetailProcessor;
// This setting is required for inserting e.Value into the source document. Otherwise it will be ignored.
e.Handled = true;
}
}
static void Main(string[] args)
{
RichEditDocumentServer srv = new RichEditDocumentServer();
srv.LoadDocument("Docs\\invitation.docx");
srv.Document.Fields[0].Locked = true;
srv.Options.MailMerge.DataSource = new SampleData();
srv.Document.CalculateDocumentVariable += Document_CalculateDocumentVariable;
MailMergeOptions myMergeOptions = srv.Document.CreateMailMergeOptions();
srv.MailMergeRecordStarted += srv_MailMergeRecordStarted;
srv.MailMergeRecordFinished += srv_MailMergeRecordFinished;
myMergeOptions.MergeMode = MergeMode.NewSection;
srv.Document.MailMerge(myMergeOptions, "Result.docx", DocumentFormat.OpenXml);
Process.Start("Result.docx");
}
private void MergeToNewDocument()
{
MergeSimpleDataForm form = new MergeSimpleDataForm();
if (form.ShowDialog(this) == DialogResult.OK)
{
MailMergeOptions options = richEditControl1.Document.CreateMailMergeOptions();
CalculateMailMergeOptions(form, options);
options.MergeMode = form.MergeMode;
if (form.MergeDestination == MergeDestination.NewTab)
{
MailMergeToNewControl(options);
}
else
{
MailMergeToNewFile(options);
}
}
}
/// <summary>
/// 生成表格
/// </summary>
public void DoReport()
{
CommonClass common = new CommonClass();
ConnectDB db = new ConnectDB();
DataTable DT = db.GetDataBySql("select GLDWNAME from GISDATA_GLDW where GLDW = '" + common.GetConfigValue("GLDW") + "'");
DataRow dr = DT.Select(null)[0];
string path = common.GetConfigValue("SAVEDIR") + "\\" + dr["GLDWNAME"].ToString() + "\\表格";
//string path = @"D:\report\";
if (Directory.Exists(path) == false)
{
Directory.CreateDirectory(path);
}
int[] selectRows = this.gridView1.GetSelectedRows();
DataTable dtDs = new DataTable();
foreach (int itemRow in selectRows)
{
mydelegate = new myDelegate(setPos);
Thread myThread = new Thread((ThreadStart)(delegate()
{
this.BeginInvoke(mydelegate, new object[] { itemRow });
}));
myThread.IsBackground = true;
myThread.Start();
//Thread MyThread = new Thread(new ParameterizedThreadStart(setPos));
//MyThread.IsBackground = true;
//MyThread.Start(itemRow);
DataRow row = this.gridView1.GetDataRow(itemRow);
String reportType = row["REPORTTYPE"].ToString();
String sheetname = row["SHEETNAME"].ToString();
String sqlstr = row["SQLSTR"].ToString();
String rowname = row["ROWNAME"].ToString().Trim();
String columnsname = row["COLUMNSNAME"].ToString().Trim();
String ValueStr = row["VALUESTRING"].ToString().Trim();
String sortfield = row["SORTFIELD"].ToString().Trim();
string[] dataSourceArr = row["DATASOURCE"].ToString().Split(',');
SpreadsheetControl sheet = new SpreadsheetControl();
Spread.IWorkbook book = sheet.Document;
book.LoadDocument(Application.StartupPath + "\\Report\\" + row["REPORTMOULD"].ToString(), Spread.DocumentFormat.OpenXml);
if (reportType == "任务完成统计表")
{
wwcxmTable = new DataTable();
string gldw = common.GetConfigValue("GLDW") == "" ? "520121" : common.GetConfigValue("GLDW");
DataTable dtTaskDb = db.GetDataBySql("select YZLGLDW,YZLFS,ZCSBND,XMMC,RWMJ from GISDATA_TASK where YZLGLDW = '" + gldw + "'");
dtTaskDb.TableName = "GISDATA_TASK";
DataTable dtTask = common.TranslateDataTable(dtTaskDb);
DataRow[] drTask = dtTask.Select(null);
dtTask.Columns.Add("SBMJ");
dtDs.Columns.Add("YZLGLDW");
dtDs.Columns.Add("YZLFS");
dtDs.Columns.Add("ZCSBND");
dtDs.Columns.Add("XMMC");
dtDs.Columns.Add("RWMJ");
dtDs.Columns.Add("SBMJ");
wwcxmTable.Columns.Add("YZLFS");
wwcxmTable.Columns.Add("ZCSBND");
wwcxmTable.Columns.Add("XMMC");
wwcxmTable.Columns.Add("RWMJ");
wwcxmTable.Columns.Add("SBMJ");
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
for (int i = 0; i < drTask.Length; i++)
{
DataRow rowItem = drTask[i];
string zcsbnd = rowItem["ZCSBND"].ToString();
gldw = rowItem["YZLGLDW"].ToString();
string yzlfs = rowItem["YZLFS"].ToString();
string xmmc = rowItem["XMMC"].ToString();
string sbmj = "";
var query = from t in dt.AsEnumerable()
where (t.Field <string>("YZLGLDW") == gldw && t.Field <string>("ZCSBND") == zcsbnd && t.Field <string>("YZLFS") == yzlfs && t.Field <string>("XMMC") == xmmc)
group t by new { t1 = t.Field <string>("YZLGLDW"), t2 = t.Field <string>("ZCSBND"), t3 = t.Field <string>("YZLFS"), t4 = t.Field <string>("XMMC") } into m
select new
{
gldwItem = m.Key.t1,
zcsbndItem = m.Key.t2,
yzlfsItem = m.Key.t3,
xmmcItem = m.Key.t4,
sbmjItem = m.Sum(n => Convert.ToDouble(n["SBMJ"]))
};
if (query.ToList().Count > 0)
{
query.ToList().ForEach(q =>
{
sbmj = q.sbmjItem.ToString();
});
}
rowItem["SBMJ"] = sbmj == "" ? "0" : sbmj;
if (rowItem["SBMJ"].ToString() != rowItem["RWMJ"].ToString())
{
wwcxmTable.ImportRow(rowItem);
}
dtDs.ImportRow(rowItem);
}
book.MailMergeDataSource = dtDs;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
}
}
}
else if (reportType == "透视表")
{
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
dt.DefaultView.Sort = sortfield;
ConvertPivotTable conver = new ConvertPivotTable();
DataTable dtPivot = conver.CreatePivotTable(dt, columnsname.Trim(), "SBMJ", "", rowname.Trim());
Spread.Worksheet Spreadsheet = book.Worksheets[sheetname];
Model.DocumentModel documentModel = new Model.DocumentModel();
FileInfo xlxsFile = new FileInfo(Application.StartupPath + "\\Report\\" + row["REPORTMOULD"].ToString());
System.IO.Stream stream = xlxsFile.OpenRead();
Spread.DocumentFormat format = documentModel.AutodetectDocumentFormat(xlxsFile.Name);
documentModel.LoadDocument(stream, format, string.Empty);
MailMergeOptions option = new MailMergeOptions(documentModel);
Model.CellRangeBase detail = option.DetailRange;
Model.CellRangeBase header = option.HeaderRange;
IEnumerable <Spread.Cell> dynamiccolArr = Spreadsheet.Search("=DYNAMICCOL(\"" + columnsname + "\")");
IEnumerable <Spread.Cell> dynamicfieldArr = Spreadsheet.Search("=DYNAMICFIELD(\"" + columnsname + "\")");
Spread.Cell dynamiccol = null;
foreach (Spread.Cell str in dynamiccolArr)
{
dynamiccol = str;
break;
}
Spread.Cell dynamicFiled = null;
foreach (Spread.Cell str in dynamicfieldArr)
{
dynamicFiled = str;
break;
}
int ColumnIndexItem = 0;
for (int i = 0; i < dtPivot.Columns.Count; i++)
{
DataColumn itemColumn = dtPivot.Columns[i];
if (itemColumn.Namespace == columnsname)
{
Spread.Cell rangeHeader = Spreadsheet.Cells[dynamiccol.RowIndex, dynamiccol.ColumnIndex + ColumnIndexItem];
Spread.Cell rangeDetail = Spreadsheet.Cells[dynamicFiled.RowIndex, dynamiccol.ColumnIndex + ColumnIndexItem];
rangeHeader.CopyFrom(dynamiccol);
rangeDetail.CopyFrom(dynamicFiled);
rangeHeader.Value = itemColumn.Caption.ToString();
rangeDetail.Calculate();
rangeHeader.Calculate();
rangeDetail.Value = "=FIELD(\"" + itemColumn.ColumnName.ToString() + "\")";
rangeDetail.Formula = "=FIELD(\"" + itemColumn.ColumnName.ToString() + "\")";
ColumnIndexItem++;
}
}
//标题range
if (Spreadsheet.DefinedNames.GetDefinedName("TITLESTRING") != null)
{
Spread.Range titleRange = Spreadsheet.DefinedNames.GetDefinedName("TITLESTRING").Range;
Model.CellPosition TitleStarPosition = new Model.CellPosition(titleRange.LeftColumnIndex, titleRange.TopRowIndex);
Model.CellPosition TitleEndPosition = new Model.CellPosition(titleRange.RightColumnIndex + ColumnIndexItem - 1, titleRange.BottomRowIndex);
Model.CellRange newTitle = new Model.CellRange(header.Worksheet, TitleStarPosition, TitleEndPosition);
titleRange = Spreadsheet.Range[newTitle.ToString()];
Spreadsheet.MergeCells(titleRange);
}
//单位Range
if (Spreadsheet.DefinedNames.GetDefinedName("UNITSTRING") != null)
{
Spread.Range unitRange = Spreadsheet.DefinedNames.GetDefinedName("UNITSTRING").Range;
Model.CellPosition UnitStarPosition = new Model.CellPosition(unitRange.LeftColumnIndex, unitRange.TopRowIndex);
Model.CellPosition UnitEndPosition = new Model.CellPosition(unitRange.RightColumnIndex + ColumnIndexItem - 1, unitRange.BottomRowIndex);
Model.CellRange newUnit = new Model.CellRange(header.Worksheet, UnitStarPosition, UnitEndPosition);
unitRange = Spreadsheet.Range[newUnit.ToString()];
Spreadsheet.MergeCells(unitRange);
Spread.Style unitStyle = unitRange.Style;
unitStyle.Alignment.Horizontal = Spread.SpreadsheetHorizontalAlignment.Right;
}
//Detail Range
Model.CellRange newDetail = new Model.CellRange(header.Worksheet, detail.TopLeft.Column, detail.TopLeft.Row, detail.TopRight.Column + ColumnIndexItem, detail.BottomRight.Row);
Spread.Range detailRange = Spreadsheet.Range[newDetail.ToString()];
Spreadsheet.DefinedNames.GetDefinedName("DETAILRANGE").Range = detailRange;
//Header Range
Model.CellRange newHeader = new Model.CellRange(header.Worksheet, header.TopLeft.Column, header.TopLeft.Row, header.TopRight.Column + ColumnIndexItem, header.BottomRight.Row);
Spread.Range headerRange = Spreadsheet.Range[newHeader.ToString()];
Spreadsheet.DefinedNames.GetDefinedName("HEADERRANGE").Range = headerRange;
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
book.MailMergeDataSource = dtPivot;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
SpreadsheetControl mergesheet = new SpreadsheetControl();
Spread.IWorkbook mergebook = mergesheet.Document;
mergebook.LoadDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx", Spread.DocumentFormat.OpenXml);
Spread.Worksheet MergeSpreadsheet = mergebook.Worksheets[sheetname];
int TableRowCnts = detail.TopLeft.Row + dtPivot.Rows.Count;
int tmpA;
int tmpB;
var PerTxt = "";
var CurTxt = "";
var groupPerTxt = "";
var groupCurTxt = "";
for (int i = detail.TopRight.Column; i > 0; i--)
{
PerTxt = "";
tmpA = 1;
tmpB = 0;
for (int j = detail.TopLeft.Row; j <= TableRowCnts; j++)
{
groupCurTxt = "";
if (j == TableRowCnts)
{
CurTxt = "";
}
else
{
CurTxt = MergeSpreadsheet.GetCellValue(i - 1, j).ToString();
}
for (int k = i - 1; k > 0; k--)
{
groupCurTxt += MergeSpreadsheet.GetCellValue(k - 1, j).ToString();
}
if (PerTxt == CurTxt && groupCurTxt == groupPerTxt)
{
tmpA += 1;
}
else
{
tmpB += tmpA;
if (tmpA > 1)
{
Model.CellRange MergePos = new Model.CellRange(newDetail.Worksheet, i - 1, j - tmpA, i - 1, j - 1);
Spread.Range MergeRange = MergeSpreadsheet.Range[MergePos.ToString()];
MergeSpreadsheet.MergeCells(MergeRange);
}
tmpA = 1;
}
PerTxt = CurTxt;
groupPerTxt = groupCurTxt;
}
}
mergebook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
}
}
}
else
{
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
//dtDs = common.TranslateDataTable(dt);
book.MailMergeDataSource = dt;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
}
}
}
row["STATE"] = "完成";
}
}
static void buttonCustomAction_ItemClick_MailMergeMethod(object sender, ItemClickEventArgs e)
{
RichEditControl richEdit = e.Item.Tag as RichEditControl;
richEdit.LoadDocument("Documents\\MailMergeSimple.rtf", DevExpress.XtraRichEdit.DocumentFormat.Rtf);
System.Data.DataTable MailMergeDataSource = new System.Data.DataTable();
MailMergeDataSource.Columns.Add("FirstName");
MailMergeDataSource.Columns.Add("LastName");
MailMergeDataSource.Columns.Add("HiringDate");
MailMergeDataSource.Columns.Add("Address");
MailMergeDataSource.Columns.Add("City");
MailMergeDataSource.Columns.Add("Country");
MailMergeDataSource.Columns.Add("Position");
MailMergeDataSource.Columns.Add("CompanyName");
MailMergeDataSource.Columns.Add("Gender");
MailMergeDataSource.Columns.Add("Phone");
MailMergeDataSource.Columns.Add("HRManagerName");
string[] firstName = { "Nancy", "Andrew", "Janet", "Margaret",
"Steven", "Michael", "Robert", "Laura", "Anne" };
string[] lastName = { "Davolio", "Fuller", "Leverling", "Peacock",
"Buchanan", "Suyama", "King", "Callahan", "Dodsworth" };
string[] city = { "Seattle", "Tacoma", "Kirkland", "Redmond", "London",
"London", "London", "Seattle", "London" };
string[] country = { "USA", "USA", "USA", "USA",
"UK", "UK", "UK", "USA", "UK" };
string[] address = { "507 - 20th Ave. E. Apt. 2A", "908 W. Capital Way", "722 Moss Bay Blvd.",
"4110 Old Redmond Rd.", "14 Garrett Hill", "Coventry House Miner Rd.",
"Edgeham Hollow Winchester Way", "4726 - 11th Ave. N.E.", "7 Houndstooth Rd." };
string[] position = { "Sales Representative", "Vice President, Sales", "Sales Representative",
"Sales Representative", "Sales Manager", "Sales Representative",
"Sales Representative", "Inside Sales Coordinator", "Sales Representative" };
char[] gender = { 'F', 'M', 'F', 'F', 'M', 'M', 'M', 'F', 'F' };
string[] phone = { "(206) 555-9857", "(206) 555-9482", "(206) 555-3412", "(206) 555-8122",
"(71) 555-4848", "(71) 555-7773", "(71) 555-5598", "(206) 555-1189", "(71) 555-4444" };
string[] companyName = { "Consolidated Holdings", "Around the Horn", "North/South", "Island Trading",
"White Clover Markets", "Trail's Head Gourmet Provisioners", "The Cracker Box",
"The Big Cheese", "Rattlesnake Canyon Grocery", "Split Rail Beer & Ale",
"Hungry Coyote Import Store", "Great Lakes Food Market" };
Random rnd = new Random();
for (int i = 0; i < 9; i++)
{
MailMergeDataSource.Rows.Add(new object[] {
firstName[i],
lastName[i],
DateTime.Now.AddDays(-(rnd.Next(0, 2000))),
address[i],
city[i],
country[i],
position[i],
companyName[i],
gender[i],
phone[i],
"Dan Marino"
});
}
MailMergeOptions options = richEdit.CreateMailMergeOptions();
options.DataSource = MailMergeDataSource;
options.FirstRecordIndex = 2;
options.LastRecordIndex = 5;
options.MergeMode = MergeMode.NewSection;
using (FileStream fsResult = new FileStream("MailMergeResult.rtf", FileMode.OpenOrCreate)) {
richEdit.MailMerge(options, fsResult, DevExpress.XtraRichEdit.DocumentFormat.Rtf);
}
System.Diagnostics.Process.Start("MailMergeResult.rtf");
}
