Openxml 笔记

用openxml 生成Excel:

private void GenerateExcelUsingOpenxml(DataTable dataTable, string GeneratePath)
        {
            using (var workbook = SpreadsheetDocument.Create(GeneratePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                InitializeStyleSheet(workbookPart);
                uint sheetId = 1;
                var excelColumns = new DocumentFormat.OpenXml.Spreadsheet.Columns();
                excelColumns.Append(CreateColumnData(1, Convert.ToUInt16(dataTable.Columns.Count + 1), 20));
                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(excelColumns, sheetData);
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetId.ToString() };
                sheets.Append(sheet);
                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                List<String> columns = new List<string>();
                foreach (DataColumn column in dataTable.Columns)
                {
                    columns.Add(column.ColumnName);
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.StyleIndex = 0U;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);
                foreach (DataRow dsrow in dataTable.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                        cell.StyleIndex = 0U;
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }
                workbook.Close();
            }
        }

用Openxml 做mailmerge:

生成组合模板:public DataTable GenerateGroupTeamplates(DataTable DT)
        {           
            //Add a dictinary type collection            
            Dictionary<string, int> TemplatePageCollection = new Dictionary<string, int> { };
            IEnumerable<string> TemplateGroups = ConnectTemplateAndGroup_new(DT);
            DataTable CustomerAndTempInfoDT = AddColumnForCustomerAndTempInfo(DT);   
           
            string MergedGroupTemplatesPath = SMConfig.MergedGroupTemplatePath;
            string TemplateCopiedFolder =SMConfig.TempleFolderPath;
            string SystemTemplatePath = SMConfig.TemplatePath;
            DataTable TemplateCollection = GetTemplateInformation();
            foreach (string TeamplateString in TemplateGroups)
            {
                int PageCount = 0;
                string[] temps = TeamplateString.Split(',');               
                for (int i = 0; i < temps.Length; i++)
                {
                    string OneTeampName = SystemTemplatePath + "\" + TemplateCollection.Select("TemplateIndex='" + temps[i] + "'")[0]["TemplateName"].ToString();//Rows[indexRow]["TemplateName"].ToString();           
                    File.Copy(OneTeampName, TemplateCopiedFolder + "\" + temps[i]+ ".docx", true);
                }
                string ConnectFile = TemplateCopiedFolder + "\" + temps[0] + ".docx";                
                for (int i = 1; i < temps.Length; i++)
                {
                    string RecursiveFile = TemplateCopiedFolder + "\" + temps[i] + ".docx";
                    using (WordprocessingDocument myDoc = WordprocessingDocument.Open(ConnectFile, true))
                    {
                        try
                        {
                            MainDocumentPart mainPart = myDoc.MainDocumentPart;
                            Paragraph SectionPageBreakParagraph = new Paragraph(new ParagraphProperties(new SectionProperties(new SectionType() { Val = SectionMarkValues.NextPage })));
                            Paragraph PageBreakParagraph = new Paragraph(new DocumentFormat.OpenXml.Wordprocessing.Run(new DocumentFormat.OpenXml.Wordprocessing.Break() { Type = BreakValues.Page }));
                            mainPart.Document.Body.Append(PageBreakParagraph);//此处添加空白页   
                            mainPart.Document.Body.Append(SectionPageBreakParagraph);//add section breakparagraph
                            string altChunkId = "AltChunkId0" + i;
                            AlternativeFormatImportPart chunk = mainPart.AddAlternativeFormatImportPart(
                            AlternativeFormatImportPartType.WordprocessingML, altChunkId);
                            using (FileStream fileStream = File.Open(RecursiveFile, FileMode.Open))
                            {
                                chunk.FeedData(fileStream);
                                fileStream.Close();
                            }
                            AltChunk altChunk = new AltChunk();
                            altChunk.Id = altChunkId;
                            using (WordprocessingDocument tempFileDoc = WordprocessingDocument.Open(RecursiveFile, true))
                            {
                                PageCount = CountPage(PageCount, tempFileDoc);
                                IEnumerable<SectionProperties> sectionProperties = tempFileDoc.MainDocumentPart.Document.Body.Elements<SectionProperties>();
                                mainPart.Document.Body.InsertAfter(altChunk, mainPart.Document.Body.Elements<Paragraph>().Last());
                                mainPart.Document.Body.Last().Append(sectionProperties.FirstOrDefault().CloneNode(true));
                                mainPart.Document.Save();
                                myDoc.Close();
                                tempFileDoc.Close();
                            }
                        }
                        catch (Exception ex) { }
                    }
                }
                string GroupFilePath = MergedGroupTemplatesPath + "\" + TeamplateString + ".docx";
                File.Copy(ConnectFile, GroupFilePath, true);  
                string[] CopiedFiles = Directory.GetFiles(TemplateCopiedFolder);
                for (int deleInd = 0; deleInd < CopiedFiles.Length; deleInd++)
                {
                    File.Delete(CopiedFiles[deleInd]);
                }               
                TemplatePageCollection.Add(new FileInfo(GroupFilePath).Name, PageCount+2);
            }
            //装载Page到DataTable
            foreach (DataRow row in CustomerAndTempInfoDT.Rows)
            {
                row["Pages"] = TemplatePageCollection[row["GroupTemplateName"].ToString()];  
            }
            return CustomerAndTempInfoDT;
        }

Do Merge:

private void DoMerge(GeneratePara Param, string Path, string[] files)
        {
            for (int i = 0; i < files.Length; i++)
            {
                string uniqueFileName = string.Format("{0}Part{1}.docx", Path, i);
                File.Copy(files[i], uniqueFileName, true);
                DataRow[] drs = Param.SourceExcel.Select(String.Format("GroupTemplateName = '{0}'", new FileInfo(files[i]).Name));
                CalculateSubPartPages.Add(string.Format("Part{0}.docx", i),Convert.ToInt32(drs[0]["Pages"])*(drs.Count()));
                using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(uniqueFileName, true), doc = WordprocessingDocument.Open(files[i], true))
                {                    
                    XElement body = XElement.Parse(wordDocument.MainDocumentPart.Document.Body.OuterXml);
                    wordDocument.MainDocumentPart.Document.RemoveAllChildren();
                    wordDocument.MainDocumentPart.Document.AppendChild<Body>(new Body());
                    foreach (DataRow row in drs)
                    {
                        try
                        {
                            BindCalculateCustomerPart(i, row);
                            DataRow Row = Param.SourceData.Select("customer_no='" + row.ItemArray[0].ToString().PadLeft(9, '0') + "'")[0];
                            XElement newBody = XElement.Parse(doc.MainDocumentPart.Document.Body.OuterXml);
                            IList<XElement> mailMergeFields =
                          (from el in newBody.Descendants()
                           where ((el.Name == (XMLNS + "t") && el.Value != null && el.Value.Contains("«") && el.Value.Contains("»")))
                           select el).ToList();
                            string fieldName = string.Empty;
                            XElement newElement = null;
                            foreach (XElement field in mailMergeFields)
                            {
                                fieldName = field.Value.Replace("«", "").Replace("»", "").Replace("-", "_");
                                if (Row.Table.Columns.Contains(fieldName))
                                {
                                    if (Row[fieldName] == DBNull.Value)
                                        Row[fieldName] = string.Empty;
                                    newElement = field;
                                    newElement.Value = Regex.Replace(Row.Field<string>(fieldName).Trim(), "\s+", " ", RegexOptions.IgnoreCase);
                                    field.ReplaceWith(newElement);
                                }
                            }
                            Body bo = new Body(newBody.ToString());
                            wordDocument.MainDocumentPart.Document.Body.AppendChild<Body>(bo);                                                        
                            IEnumerable<SectionProperties> sectionProperties = doc.MainDocumentPart.Document.Body.Elements<SectionProperties>();
                            wordDocument.MainDocumentPart.Document.Body.Last().Append(sectionProperties.FirstOrDefault().CloneNode(true));
                            Paragraph PageBreakParagraph = new Paragraph(new ParagraphProperties(new SectionProperties(new SectionType() { Val = SectionMarkValues.EvenPage })));
                            wordDocument.MainDocumentPart.Document.Body.Append(PageBreakParagraph);//此处添加空白页
                        }
                        catch (Exception ex)
                        {
                            BindErrorCustomerNoDT(row.ItemArray[0].ToString());
                            continue;
                        }
                    }
                    wordDocument.MainDocumentPart.Document.Save();
                    wordDocument.Close();
                }
            }
        }