Open XML SDK 2.0 - Recent Threads

Lock Cell of Worksheet

kaushal.jani@valuechain.co.in — Thu, 09 May 2013 06:49:38 GMT

Hi,

Below is the function which reading my XLLX and write the data in it. It also protect it with password.

Now here I want to lock perticular cell of this sheet. Please suggest me options for that.

What I have tried:

1. CellFormat.ApplyProtection

2. Tried to remove "Locked" property of locked sheet.

3. Tried through "Protection".

4. As per below code.

Code :

private void readXLSX(string Filepath)

{

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(Filepath, true))

{

WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

// rId must be unique within the spreadsheet.

// You might be able to use the SpreadSheetDocument.Parts.Count() to do this.

// i.e. string relationshipID = "rId" + (spreadsheetDocument.Parts.Count() + 1).ToString();

//string rId = "rId6";

string rId = "rId" + (spreadsheetDocument.Parts.Count() + 1).ToString();

// Sheet.Name and Sheet.SheetId must be unique within the spreadsheet.

Sheet sheet = new Sheet() { Name = Guid.NewGuid().ToString(), SheetId = 4U, Id = rId };

workbookPart.Workbook.Sheets.Append(sheet);

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(rId);

Worksheet worksheet = new Worksheet();

worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

// I don't know what SheetDimension.Reference is used for, it doesn't seem to change the resulting xml.

SheetDimension sheetDimension = new SheetDimension() { Reference = "A1:A3" };

SheetViews sheetViews = new SheetViews();

// If more than one SheetView.TabSelected is set to true, it looks like Excel just picks the first one.

SheetView sheetView = new SheetView() { TabSelected = false, WorkbookViewId = 0U };

// I don't know what Selection.ActiveCell is used for, it doesn't seem to change the resulting xml.

Selection selection = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };

sheetView.Append(selection);

sheetViews.Append(sheetView);

SheetFormatProperties sheetFormatProperties = new SheetFormatProperties() { DefaultRowHeight = 15D };

SheetData sheetData = new SheetData();

// I don't know what the InnerText of Row.Spans is used for. It doesn't seem to change the resulting xml.

Row row = new Row() { RowIndex = 1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" } };

//ProtectedRange pRange = new ProtectedRange() { SequenceOfReferences = A1:C1, Name="ProtRange" };

CellFormat cellFormat = new CellFormat();

//setting protection to the cell

cellFormat.Protection = new Protection() { Locked = false };

Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("99") };

Cell cell2 = new Cell() { CellReference = "B1", DataType = CellValues.Number, CellValue = new CellValue("55") };

Cell cell3 = new Cell() { CellReference = "C1", DataType = CellValues.Number, CellValue = new CellValue("33") };

row.Append(cell1);

row.Append(cell2);

row.Append(cell3);

sheetData.Append(row);

// Setting up a password protected sheet

SheetProtection sheetProtection = new SheetProtection()

{

SelectLockedCells = true,

SelectUnlockedCells = true,

Sheet = true,

AutoFilter = false,

Objects = true,

Scenarios = true,

//AlgorithmName = "SHA-1",

//HashValue = "YV+dAThAIz3o3XCXmftMUngaqd0=",

//SaltValue = "salt1",

//SpinCount = 3,

Password = "111"

};

//SheetProtection sheetProtection1 = new SheetProtection() { Password = "111", Sheet = true, Objects = true, Scenarios = true };

PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.7D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

//worksheet.Append(pRange);

worksheet.Append(cellFormat);

worksheet.Append(sheetDimension);

worksheet.Append(sheetViews);

worksheet.Append(sheetFormatProperties);

worksheet.Append(sheetData);

worksheet.Append(sheetProtection);

worksheet.Append(pageMargins);

// Range(UseRange).Style.Protection.SetLocked(FALSE)

worksheetPart.Worksheet = worksheet;

}

Thanks & Regards,

Kaushal Jani

Issues with altChunk - document showing garbled characters - â€‹ Â

Maverickht — Wed, 01 May 2013 14:32:12 GMT

Hi,
I'm using altChunk to merge Rich text format (as XHTML) into a word document, the program seems to work but shows garbled characters (â€‹ Â ) not sure whats causing it, please help!
The code is below:

Code to open a word document and get a Plain Text Content Control called response1:

using (WordprocessingDocument myDoc =
WordprocessingDocument.Open(ms, true))
 {
 MainDocumentPart mainPart = myDoc.MainDocumentPart;

 List<OpenXmlElement> sdtList = InfoPathToWord.GetContentControl(mainPart.Document, "response1");
 AddRichText(0, response1, ref mainPart, ref sdtList);
}

Method to add the RichText:
===========================

//Example of rtfVale = "<html xml:space=\"preserve\" xmlns=\"http://www.w3.org/1999/xhtml\">\r\n <ol>\r\n<li>Test Data</li>\r\n<li>Test data2</li>\r\n<li style=\"text-decoration:line-through\">TEst again</li></ol>\r\nTest data</html>"

public static void AddRichText(int id, string rtfValue,
 ref MainDocumentPart mainPart, ref List<OpenXmlElement> sdtList)
 {
 if (sdtList.Count != 0)
 {
 id++;
 string altChunkId = "AltChunkId" + id;
 AlternativeFormatImportPart chunk =
 mainPart.AddAlternativeFormatImportPart(
 AlternativeFormatImportPartType.Xhtml, altChunkId);

 using (MemoryStream ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(rtfValue)))
 {
 chunk.FeedData(ms);
 ms.Close();
 }

 AltChunk altChunk = new AltChunk();
 altChunk.Id = altChunkId;

 if (sdtList.Count != 0)
 {
 foreach (OpenXmlElement sdt in sdtList)
 {
 OpenXmlElement parent = sdt.Parent;
 parent.InsertAfter(element, sdt);
 sdt.Remove();
 }
 }

 }
 }

In the Word document it generates proof errors (shown after I reopen and save the document in Word) , like following:

 <w:proofErr w:type="spellStart"/>
 <w:r>
 <w:rPr>
 <w:rFonts w:eastAsia="Times New Roman"/>
 </w:rPr>
 <w:t>â€‹Test</w:t>
 </w:r>
 <w:proofErr w:type="spellEnd"/>

I've tried everything but to no avail, I would appreciate help ASAP as its affecting our project.

Many thanks
Mave

Excel formula (Total and Subtotal)

newbee — Mon, 29 Apr 2013 21:08:34 GMT

Hi,

I'm a new bee to open XML . One of my projects include creating a spreadsheet from the database and apply formats, calculations etc to the spreadsheet. I successfully created one but applying formulas (subtotals and totals) is the part I need help with.

How can I let Excel know (through sheet.xml) to calculate Subtotal and Grand Totals based on every change in a specific column?

I tried manually creating a spreadsheet with Subtotal and totals,saved as zip file ,extracted and viewed the *.xml files but I couldn't figure out how the cell ranges and formulas were applied.

Can someone please help....like I said, I'm very new to this topic and need help.

Thanks!

Show file name/caption in embedded object(doc file) in word document using OpenXML

josephrxavier — Tue, 09 Apr 2013 09:55:33 GMT

Hi,

With the help of following link "http://msdn.microsoft.com/en-ca/library/ee355229.aspx" i am able to embed documents in word successfully. After inserting the document as object it shows the caption as "Embedded Document". When i gone through the code, caption comes from the image part. By default image stored as a Base 64 string. Then its written in BinaryWriter writer.

After decoding (base64 to String) the Base64 String it shows the "Embedded Document" in two places. I tried to to replace the string "Embedded Document" with other values without success. After replacing with new values its not showing the image in embedded object.

In following ways i replaced the string "Embedded Document".

byte[] toEncodeAsBytes = Convert.FromBase64String(iconBytes); // Convert to byte from base64. iconBytes contains required base64string

string returnValue = System.Text.UnicodeEncoding.Unicode.GetString(toEncodeAsBytes); // Convert to string from byte

returnValue = returnValue.Replace("Embedded Document", "Embedded Document1");

toEncodeAsBytes = System.Text.UnicodeEncoding.Unicode.GetBytes(returnValue); // Convert to byte from string

iconBytes = System.Convert.ToBase64String(toEncodeAsBytes); // Convert to base64 from byte.

Any ideas ?. Thanks in advance :)

OpenXML Excel - updating cell and making it bold

Nate Pate — Thu, 04 Apr 2013 22:11:42 GMT

I have the following code which successfully opens up an excel file and inserts a cell value. I am having a hard time though tweakingthe code so that it can make that cell bold. Any help would be appreciated. I have attempted to make the font bold as you can see below but have not been successful.

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
 

namespace portfolio
{
    public class PortfolioReport
    {

        // Given a document name and text, 
        // inserts a new work sheet and writes the text to column and row specified.
        
        public static void InsertText(string docName, string text,string column, uint row)
        {
            // Open the document for editing.
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
            {
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                {
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                }
                else
                {
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                }

                // Insert the text into the SharedStringTablePart.
                int index = InsertSharedStringItem(text, shareStringPart);

                // Insert a new worksheet.
              //  WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

                Workbook workBook = spreadSheet.WorkbookPart.Workbook;
                WorksheetPart worksheetPart = workBook.WorkbookPart.WorksheetParts.First();

                // Insert cell A1 into the new worksheet.
               // Cell cell = InsertCellInWorksheet("F", 1, worksheetPart);
                Cell cell = InsertCellInWorksheet(column, row, worksheetPart);
                

                // Set the value of cell A1.
                cell.CellValue = new CellValue(index.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                // Save the new worksheet.
                worksheetPart.Worksheet.Save();
            }
        }

        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }


        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Borders borders1 = new Borders() { Count = (UInt32Value)1U };

                Border border1 = new Border();
                LeftBorder leftBorder1 = new LeftBorder();
                RightBorder rightBorder1 = new RightBorder();
                TopBorder topBorder1 = new TopBorder();
                BottomBorder bottomBorder1 = new BottomBorder();

                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);

                borders1.Append(border1);

                Fonts fonts1 = new Fonts() {Count = (UInt32Value) 1U, KnownFonts = true};
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };

                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);

                fonts1.Append(font1);
 


                Cell newCell = new Cell() { CellReference = cellReference, StyleIndex = (UInt32Value) 1U }; 
                 
                row.InsertBefore(newCell, refCell);
                sheetData.Append(borders1);
                sheetData.Append(fonts1);
              
                worksheet.Save();
                return newCell;
            }
        }

    }
}

How to identify if a WordprocessingDocument element is a Paragraph or a Table using open xml sdk 2.0?

saravanan — Thu, 21 Mar 2013 09:32:38 GMT

Hi All,

I want to check if the first element of the word document is Table or Paragraph.And Based on this i have to do some action.Is there any functions available for check this?...

bool isStartingWithTable=false;

using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(docFile, true))

{

DocumentFormat.OpenXml.Wordprocessing.Table table = (DocumentFormat.OpenXml.Wordprocessing.Table)wordDoc.MainDocumentPart.Document.Body.ElementAt(0);

isStartingWithTable=true;

}

How to convert html string into OOXML(Word 2007)formatted string using openxmlsdk2.0?

saravanan — Wed, 20 Mar 2013 11:28:47 GMT

Hi All,

I have a string in HTML format(It may contains HTML tables).Now i want to convert this html string into OOXML represenation of WORD 2007 based string and store it into my DB table. In future,Once i have collected all the html strings then i have to build the word document from the OOXML string that are stored in the DB.

So, now my problem is i have to convert my html string into equivalent OOXML formatted string for WORD 2007.

Could you please help me to get out of this issue...

I already tried with AlternativeFormatImportPart class.But it is directly build the Word document for the given html string.I dont want to do it. I just want to convert from HTML to OOXML format of WORD2007.

Adding Bookmark inside a bookmark not added properly

sonu gupta — Tue, 19 Mar 2013 11:15:43 GMT

Hi,

I am working on Open xml Project for wordprocessing, my requirement is to add book mark inside a book mark, after adding the book mark when i open the document, and go to bookmark using Document UI, it select different paragraphs.

Can you please tell me how to add Hierarchical bookmarks.

Thanks.

Newbie trying to set text for a collection of cells in excel

ericstein86 — Fri, 15 Mar 2013 15:09:46 GMT

Hi all,

I have just started woking with openXML for excel and want to set the text of all the cells in a range without having to loop through each row.

I was able to set data validations by specifying a range. Is there any way to do this and just set text for the cells?

how to findout layout of each slide using OpenXml?

Alpa — Mon, 04 Mar 2013 09:42:34 GMT

Hello All,

I am having .pptx file in which each slide having different layout. i want to find out layout of each slides using C# coding, using OpenXml? Does anyone have any idea?

Thanks in advance.

Alpa

How to read ActiveX control value in Excel and PPt using OpenXML

sonu gupta — Fri, 01 Mar 2013 09:17:51 GMT

Hi,

I am working with OpenXml sdk 2.0 for replacing ActiveX Control in Excel and PPT, i didn't find any way for reading ActiveX control value.

Please let me know if we have solution regarding this.

Thanks for your response in advance.

Formating spreadsheets with Open Xml

risho — Tue, 26 Feb 2013 22:56:37 GMT

I thought this would be easy. I'm creating an Excel workbook with two spreadsheets but what I get is unformated output. I wanted to at least set certain

cell widths, make the top row stand out, and create alternate row background color settnings. I thought that by formating the output manually, then save

the file and open it with Open XML SDK Productivity tool I would be able to copy the code from the method that I thought was responsible for styling,

namely, GenerateWorkbookStylesPart1Content. While there is no error, there is also no styling.

I wanted to see if someone has experience with this and is willing to share how this is done. I've tried to paste the entire code but it is very long all won't fit.

Here is a code that I was able modify to make things happen - I don't fully understand how it's all done. I just went through a tutorial at

http://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx and in itself it starting to make sense but my understanding of the tutorial and

what I need to get done are two different things. And thanks in advance! R.

namespace GridToExcel.Helper
{
    public class ExcelHelper
    {
        public class ColumnCaption
        {
            private static string[] Alphabets = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
            private static ColumnCaption instance = null;
            private List<string> cellHeaders = null;
            public static ColumnCaption Instance
            {
                get
                {
                    if (instance == null)
                        return new ColumnCaption();
                    else return ColumnCaption.Instance;
                }
            }

            public ColumnCaption()
            {
                this.InitCollection();
            }

            private void InitCollection()
            {
                cellHeaders = new List<string>();

                foreach (string sItem in Alphabets)
                    cellHeaders.Add(sItem);

                foreach (string item in Alphabets)
                    foreach (string sItem in Alphabets)
                        cellHeaders.Add(item + sItem);
            }

            public string Get(int rowIndex, int columnIndex)
            {
                return this.cellHeaders.ElementAt(columnIndex) + (rowIndex + 1).ToString();
            }
        }

        public static string ExportToExcel(DataTable table, DataTable table2, string fileName1,int interval, string source)
        {
            string excelfile = fileName1;
            using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(excelfile, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                CreateExcelParts(excelDoc, table, table2, interval, source);
            }
            return excelfile;
        }

        private static void CreateExcelParts(SpreadsheetDocument spreadsheetDoc, DataTable data, DataTable data2 ,int interval,string source)
        {
            bool _multisheet = false;
            if (data2 != null)
                _multisheet = true;

            WorkbookPart workbookPart = spreadsheetDoc.AddWorkbookPart();
            CreateWorkbookPart(workbookPart, _multisheet);

            int workBookPartCount = 1;

            WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId" + (workBookPartCount++).ToString());
            CreateWorkbookStylesPart(workbookStylesPart);

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId" + (101).ToString());
            CreateWorksheetPart(workbookPart.WorksheetParts.ElementAt(0), data, interval, source);

            if (data2 != null)
            {
                WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>("rId" + (102).ToString());
                CreateWorksheetPart2(workbookPart.WorksheetParts.ElementAt(1), data2, interval, source);
            }

            SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>("rId" + (workBookPartCount++).ToString());
            CreateSharedStringTablePart(sharedStringTablePart, data);
            CreateSharedStringTablePart2(sharedStringTablePart, data2);

            workbookPart.Workbook.Save();
        }

       private static void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable sheetData)
        {
            UInt32Value stringCount = Convert.ToUInt32(sheetData.Rows.Count) + Convert.ToUInt32(sheetData.Columns.Count);

            SharedStringTable sharedStringTable = new SharedStringTable()
            {
                Count = stringCount,
                UniqueCount = stringCount
            };

            for (int columnIndex = 0; columnIndex < sheetData.Columns.Count; columnIndex++)
            {
                SharedStringItem sharedStringItem = new SharedStringItem();
                Text text = new Text();
                text.Text = sheetData.Columns[columnIndex].ColumnName;
                sharedStringItem.Append(text);
                sharedStringTable.Append(sharedStringItem);
            }

            for (int rowIndex = 0; rowIndex < sheetData.Rows.Count; rowIndex++)
            {
                SharedStringItem sharedStringItem = new SharedStringItem();
                Text text = new Text();
                text.Text = sheetData.Rows[rowIndex][0].ToString();
                sharedStringItem.Append(text);
                sharedStringTable.Append(sharedStringItem);
            }

            sharedStringTablePart.SharedStringTable = sharedStringTable;
        }

        private static void CreateSharedStringTablePart2(SharedStringTablePart sharedStringTablePart, DataTable sheetData)
        {
            UInt32Value stringCount = Convert.ToUInt32(sheetData.Rows.Count) + Convert.ToUInt32(sheetData.Columns.Count);

            SharedStringTable sharedStringTable = new SharedStringTable()
            {
                Count = stringCount,
                UniqueCount = stringCount
            };

            for (int columnIndex = 0; columnIndex < sheetData.Columns.Count; columnIndex++)
            {
                SharedStringItem sharedStringItem = new SharedStringItem();
                Text text = new Text();
                text.Text = sheetData.Columns[columnIndex].ColumnName;
                sharedStringItem.Append(text);
                sharedStringTable.Append(sharedStringItem);
            }

            for (int rowIndex = 0; rowIndex < sheetData.Rows.Count; rowIndex++)
            {
                SharedStringItem sharedStringItem = new SharedStringItem();
                Text text = new Text();
                text.Text = sheetData.Rows[rowIndex][0].ToString();
                sharedStringItem.Append(text);
                sharedStringTable.Append(sharedStringItem);
            }

            sharedStringTablePart.SharedStringTable = sharedStringTable;
        }

        private static void CreateWorksheetPart(WorksheetPart worksheetPart, DataTable data, int interval, string source)
        {
            Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");


            SheetViews sheetViews = new SheetViews();
            SheetView sheetView = new SheetView() { WorkbookViewId = (UInt32Value)0U };
            Selection selection = new Selection() { ActiveCell = "A1" };
            sheetView.Append(selection);
            sheetViews.Append(sheetView);

            PageMargins pageMargins = new PageMargins()
            {
                Left = 0.7D,
                Right = 0.7D,
                Top = 0.75D,
                Bottom = 0.75D,
                Header = 0.3D,
                Footer = 0.3D
            };

            SheetFormatProperties sheetFormatPr = new SheetFormatProperties()
            {
                DefaultRowHeight = 15D,
                DyDescent = 0.25D
            };

            SheetData sheetData = new SheetData();

            UInt32Value rowIndex = 1U;

            Row row1 = new Row()
            {
                RowIndex = rowIndex++,
                Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                DyDescent = 0.25D
            };

            // Creates the first row hearder of the spreadsheet
            for (int columnIndex = 0; columnIndex < data.Columns.Count; columnIndex++)
            {
                Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), columnIndex), DataType = CellValues.String };
                CellValue cellValue = new CellValue();

                // Format cells with Dates heading
                if (source == "")
                {
                    if (columnIndex > 1)
                    {
                        string[] _dateParse;
                        string _year = string.Empty;
                        string _month = string.Empty;

                        if (interval == 3) // Interval: Total
                        {

                        }
                        else if (interval == 2)  // Interval: Year
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString();
                            cellValue.Text = _year;
                        }
                        else // IntgervalL Month
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString().Remove(0, 2);
                            _month = getMonthAccronyms(_dateParse[1].ToString());
                            cellValue.Text = _month + "-" + _year;
                        }

                        _dateParse = null;
                    }
                    else
                        cellValue.Text = data.Columns[columnIndex].ColumnName.ToString();
                }
                else
                {
                    if (columnIndex > 0)
                    {
                        string[] _dateParse;
                        string _year = string.Empty;
                        string _month = string.Empty;

                        if (interval == 3) // Interval: Total
                        {

                        }
                        else if (interval == 2)  // Interval: Year
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString();
                            cellValue.Text = _year;
                        }
                        else // IntgervalL Month
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString().Remove(0, 2);
                            _month = getMonthAccronyms(_dateParse[1].ToString());
                            cellValue.Text = _month + "-" + _year;
                        }

                        _dateParse = null;
                    }
                    else
                        cellValue.Text = data.Columns[columnIndex].ColumnName.ToString();
                }

                cell.Append(cellValue);

                row1.Append(cell);
            }
            sheetData.Append(row1);

            Row extraRow = new Row();

            for (int rIndex = 0; rIndex < data.Rows.Count; rIndex++)
            {
                Row row = new Row()
                {
                    RowIndex = rowIndex++,
                    Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                    DyDescent = 0.25D
                };

                for (int cIndex = 0; cIndex < data.Columns.Count; cIndex++)
                {
                    if (source == "")
                    {
                        if (cIndex > 1)
                        {
                            Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.Number };
                            CellValue cellValue = new CellValue();
                            cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                            cell.Append(cellValue);

                            row.Append(cell);
                        }
                        else
                        {
                            Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.String };
                            CellValue cellValue = new CellValue();
                            cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                            cell.Append(cellValue);

                            row.Append(cell);
                        }
                    }
                    else
                    {
                        if (cIndex > 0)
                        {
                            Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.Number };
                            CellValue cellValue = new CellValue();
                            cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                            cell.Append(cellValue);

                            row.Append(cell);
                        }
                        else
                        {
                            Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.String };
                            CellValue cellValue = new CellValue();
                            cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                            cell.Append(cellValue);

                            row.Append(cell);
                        }
                    }
                }
                sheetData.Append(row);

                Cell extraCell = new Cell();
                CellValue extraCellValue = new CellValue();
                extraCellValue.Text = "**********";
                extraCell.Append(extraCellValue);
                sheetData.Append(extraRow);
            }


            worksheet.Append(sheetViews);
            worksheet.Append(sheetFormatPr);
            worksheet.Append(sheetData);
            worksheet.Append(pageMargins);
            worksheetPart.Worksheet = worksheet;
        }

        private static void CreateWorksheetPart2(WorksheetPart worksheetPart, DataTable data, int interval, string source)
        {
            Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");


            SheetViews sheetViews = new SheetViews();
            SheetView sheetView = new SheetView() { WorkbookViewId = (UInt32Value)0U };
            Selection selection = new Selection() { ActiveCell = "A1" };
            sheetView.Append(selection);
            sheetViews.Append(sheetView);

            PageMargins pageMargins = new PageMargins()
            {
                Left = 0.7D,
                Right = 0.7D,
                Top = 0.75D,
                Bottom = 0.75D,
                Header = 0.3D,
                Footer = 0.3D
            };

            SheetFormatProperties sheetFormatPr = new SheetFormatProperties()
            {
                DefaultRowHeight = 15D,
                DyDescent = 0.25D
            };

            SheetData sheetData = new SheetData();

            UInt32Value rowIndex = 1U;

            Row row1 = new Row()
            {
                RowIndex = rowIndex++,
                Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                DyDescent = 0.25D
            };

            for (int columnIndex = 0; columnIndex < data.Columns.Count; columnIndex++)
            {
                Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), columnIndex), DataType = CellValues.String };
                CellValue cellValue = new CellValue();

                if (source == "")
                {

                    // Format cells with Dates heading
                    if (columnIndex > 1)
                    {
                        string[] _dateParse;
                        string _year = string.Empty;
                        string _month = string.Empty;

                        if (interval == 3) // Interval: Total
                        {

                        }
                        else if (interval == 2)  // Interval: Year
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString();
                            cellValue.Text = _year;
                        }
                        else // IntgervalL Month
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString().Remove(0, 2);
                            _month = getMonthAccronyms(_dateParse[1].ToString());
                            cellValue.Text = _month + "-" + _year;
                        }

                        _dateParse = null;
                    }
                    else
                        cellValue.Text = data.Columns[columnIndex].ColumnName.ToString();
                }
                else
                {// Format cells with Dates heading
                    if (columnIndex > 0)
                    {
                        string[] _dateParse;
                        string _year = string.Empty;
                        string _month = string.Empty;

                        if (interval == 3) // Interval: Total
                        {

                        }
                        else if (interval == 2)  // Interval: Year
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString();
                            cellValue.Text = _year;
                        }
                        else // IntgervalL Month
                        {
                            _dateParse = data.Columns[columnIndex].ColumnName.ToString().Split('-');
                            _year = _dateParse[0].ToString().Remove(0, 2);
                            _month = getMonthAccronyms(_dateParse[1].ToString());
                            cellValue.Text = _month + "-" + _year;
                        }

                        _dateParse = null;
                    }
                    else
                        cellValue.Text = data.Columns[columnIndex].ColumnName.ToString();
                }

                cell.Append(cellValue);

                row1.Append(cell);
            }
            sheetData.Append(row1);

            for (int rIndex = 0; rIndex < data.Rows.Count; rIndex++)
            {
                Row row = new Row()
                {
                    RowIndex = rowIndex++,
                    Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                    DyDescent = 0.25D
                };

                for (int cIndex = 0; cIndex < data.Columns.Count; cIndex++)
                {
                    if (cIndex > 1)
                    {
                        Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.Number };
                        CellValue cellValue = new CellValue();
                        cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                        cell.Append(cellValue);

                        row.Append(cell);
                    }
                    else
                    {
                        Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.String };
                        CellValue cellValue = new CellValue();
                        cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                        cell.Append(cellValue);

                        row.Append(cell);
                    }
                }
                sheetData.Append(row);
            }

            worksheet.Append(sheetViews);
            worksheet.Append(sheetFormatPr);
            worksheet.Append(sheetData);
            worksheet.Append(pageMargins);
            worksheetPart.Worksheet = worksheet;
        }

       
        private static void CreateWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
        {
            Stylesheet stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };

            Font font1 = new Font();
            FontSize fontSize1 = new FontSize() { Val = 11D };
            Color color1 = new Color() { Theme = (UInt32Value)1U };
            FontName fontName1 = new FontName() { Val = "Calibri" };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };

            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme2);

            Font font2 = new Font();
            Bold bold1 = new Bold();
            FontSize fontSize2 = new FontSize() { Val = 12D };
            Color color2 = new Color() { Theme = (UInt32Value)1U };
            FontName fontName2 = new FontName() { Val = "Calibri" };
            FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme3 = new FontScheme() { Val = FontSchemeValues.Minor };

            font2.Append(bold1);
            font2.Append(fontSize2);
            font2.Append(color2);
            font2.Append(fontName2);
            font2.Append(fontFamilyNumbering2);
            font2.Append(fontScheme3);

            fonts1.Append(font1);
            fonts1.Append(font2);

            Fills fills1 = new Fills() { Count = (UInt32Value)4U };

            Fill fill1 = new Fill();
            PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };

            fill1.Append(patternFill1);

            Fill fill2 = new Fill();
            PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

            fill2.Append(patternFill2);

            Fill fill3 = new Fill();

            PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor1 = new ForegroundColor() { Theme = (UInt32Value)3U, Tint = 0.79998168889431442D };
            BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };

            patternFill3.Append(foregroundColor1);
            patternFill3.Append(backgroundColor1);

            fill3.Append(patternFill3);

            Fill fill4 = new Fill();

            PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor2 = new ForegroundColor() { Theme = (UInt32Value)0U, Tint = -0.249977111117893D };
            BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };

            patternFill4.Append(foregroundColor2);
            patternFill4.Append(backgroundColor2);

            fill4.Append(patternFill4);

            fills1.Append(fill1);
            fills1.Append(fill2);
            fills1.Append(fill3);
            fills1.Append(fill4);

            Borders borders1 = new Borders() { Count = (UInt32Value)2U };

            Border border1 = new Border();
            LeftBorder leftBorder1 = new LeftBorder();
            RightBorder rightBorder1 = new RightBorder();
            TopBorder topBorder1 = new TopBorder();
            BottomBorder bottomBorder1 = new BottomBorder();
            DiagonalBorder diagonalBorder1 = new DiagonalBorder();

            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);

            Border border2 = new Border();

            LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
            Color color3 = new Color() { Theme = (UInt32Value)0U, Tint = -0.14996795556505021D };

            leftBorder2.Append(color3);

            RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
            Color color4 = new Color() { Theme = (UInt32Value)0U, Tint = -0.14996795556505021D };

            rightBorder2.Append(color4);

            TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
            Color color5 = new Color() { Theme = (UInt32Value)0U, Tint = -0.14996795556505021D };

            topBorder2.Append(color5);

            BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
            Color color6 = new Color() { Theme = (UInt32Value)0U, Tint = -0.14996795556505021D };

            bottomBorder2.Append(color6);
            DiagonalBorder diagonalBorder2 = new DiagonalBorder();

            border2.Append(leftBorder2);
            border2.Append(rightBorder2);
            border2.Append(topBorder2);
            border2.Append(bottomBorder2);
            border2.Append(diagonalBorder2);

            borders1.Append(border1);
            borders1.Append(border2);

            CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

            cellStyleFormats1.Append(cellFormat1);

            CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)3U };
            CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
            CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };

            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);
            cellFormats1.Append(cellFormat4);

            CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

            cellStyles1.Append(cellStyle1);
            DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
            TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };

            StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
            StylesheetExtension stylesheetExtension = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
            stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
            stylesheetExtension.Append(slicerStyles);
            stylesheetExtensionList.Append(stylesheetExtension);

            //stylesheet.Append(stylesheetExtensionList);

            stylesheet.Append(fonts1);
            stylesheet.Append(fills1);
            stylesheet.Append(borders1);
            stylesheet.Append(cellStyleFormats1);
            stylesheet.Append(cellFormats1);
            stylesheet.Append(cellStyles1);
            stylesheet.Append(differentialFormats1);
            stylesheet.Append(tableStyles1);
            stylesheet.Append(stylesheetExtensionList);

            workbookStylesPart.Stylesheet = stylesheet;
        }

        /// <summary>
        /// Creates the workbook part.
        /// </summary>
        /// <param name="workbookPart">The workbook part.</param>
        private static void CreateWorkbookPart(WorkbookPart workbookPart, bool _multisheet)
        {
            Workbook workbook = new Workbook();
            Sheets sheets = new Sheets();

            if (_multisheet)
            {
                Sheet sheet1 = new Sheet()
                    {
                        Name = "Wuc Count JDD Output",
                        SheetId = Convert.ToUInt32(101),
                        Id = "rId" + (101).ToString()
                    };
                sheets.Append(sheet1);
                
                Sheet sheet2 = new Sheet()
                {
                    Name = "WUC Count ESR Output",
                    SheetId = Convert.ToUInt32(102),
                    Id = "rId" + (102).ToString()
                };
                sheets.Append(sheet2);
            }
            else
            {
                Sheet sheet1 = new Sheet()
                {
                    Name = "System R&M Risk Report Output",
                    SheetId = Convert.ToUInt32(101),
                    Id = "rId" + (101).ToString()
                };
                sheets.Append(sheet1);
            }
            
            CalculationProperties calculationProperties = new CalculationProperties()
            {
                CalculationId = (UInt32Value)123456U  // some default Int32Value
            };

            workbook.Append(sheets);
            workbook.Append(calculationProperties);

            workbookPart.Workbook = workbook;
        }
    }
}

Read and edit Word Files client-side only with JavaScript

radu tut — Mon, 25 Feb 2013 13:23:20 GMT

Hi all,

I would like to achieve the following functionality.

Read a .docx file contents using JavaScript, process it with Open XML on the client ( replace some text) and then upload it to a new location, or trigger a download for the user.

Would this be possible?

Thank you!

C# [Asp.NET MVC] Add Image to Header

JacoPD — Thu, 21 Feb 2013 11:31:31 GMT

Hi!
I have a problem I cannot solve: I looked everywhere, in every dedicated thread in this forum and on the whole web, and I didn't find out a way to manage my issue.
So, I want to add an image to my header. I have to use my header, I can't replace it with a new one cause in the preformatted one I have some text to dinamically manage in my code.
So far, this is how I edit my header:

foreach (HeaderPart header in wordDoc.MainDocumentPart.HeaderParts)
                {
                    using (StreamReader sh = new StreamReader(header.GetStream()))
                    {
                        head = sh.ReadToEnd(); //head is a string
                    }
                    Regex npiano = new Regex("{PLANNAME}");
                    if (!String.IsNullOrWhiteSpace(piano.Avviso) || !String.IsNullOrWhiteSpace(piano.Fondo))
                        head = npiano.Replace(head, " n. " + piano.Avviso + " di " + piano.Fondo);
                    else
                        head = npiano.Replace(head, "");

                   /* ... ... ... */

                    using (StreamWriter swh = new StreamWriter(header.GetStream(FileMode.Create)))
                    {
                        swh.Write(head);
                        swh.Flush();
                        swh.Close();
                    }
                }

and, for text-only parts it works.
Now I'd need a method to add an image in that same header, beneath every string I wrote.
How can I do that?
Hoping for your helps,
Thanks!

Getting System.Runtime.InteropServices.COMException while accessing content control range wordopenxml

sonu gupta — Tue, 19 Feb 2013 07:09:50 GMT

Hi,

I am working on VSTO project, the requirement is something like to get wordopenxml on content control exit event, when i am accessing wordopenxml i am getting following error -

System.Runtime.InteropServices.COMException was unhandled by user code
HelpLink=wdmain11.chm#25410
Message=The object is not valid.
Source=Microsoft Word
ErrorCode=-2146822358
StackTrace:
at Microsoft.Office.Interop.Word.Range.get_WordOpenXML()

if i quick watch the contentcontrol.range.wordopenxml, i didn't get any exception, please guide me how can i handle this error.

Thanks in advance.

Adding image parts to a word document

lohiarahul — Mon, 18 Feb 2013 08:19:29 GMT

Hi,

I have attached a code snippet in which I copy data and images from a flat open xml source to a content control in the destination document. The images appear as a base64 string in the source which I convert to a stream and add it to the destination as an image part.

However when I do this the file size increases exponentially even with very little data. If you open the document, edit it and save it the file size drops back to normal with the same content

I previously used the AltChunk approach to copy data but it had the exactly same issues too.That is why I switched to manually copying data, but now I am back to square 1.

I have multiple rich text content controls on the destination document and this problem is a show stopper. Even with 10-12 small images the file size may reach 20-30 MB. I am in a fix. Please help.

Convert or remove AltChunk

lohiarahul — Wed, 13 Feb 2013 11:20:50 GMT

I have implemented a mechanism in which I copy complete documents to a content control using an AltChunk. This implementation allows a user to change data frequently. When these changes are triggered I create yet another AltChunk to replace the previous contents. However the older chunk still contributes to the file size and it increases until the file is manually opened and saved in WinWord.Exe

Scenario : I tried to insert 10 images using AltChunk which increased the file size from 18K to 2187K. Next I tried to replace the 10 images with a plain small string. The document is updated with the new content but the size remained 2187K. And then when I opened the file manually in WinWord.Exe and saved it, the size dropped back to 19K. On research I found that Word application converts the AltChunk to Paragraphs and Runs.

My question : Can I mimic this Word Application behavior in Open Xml?

If I could remove the previous AltChunk and replace it with the new one would also the trick

Word 2010 richtext content control selects all content at once

sonu gupta — Tue, 12 Feb 2013 14:03:48 GMT

Hi,

I have VSTO application in which when i going to click inside content control it select the whole text, there is no way to modify partial text.

Thanks in advance

AltChunk corrupts rich text content control

lohiarahul — Tue, 12 Feb 2013 11:20:03 GMT

I used altchunk object to copy data from a docx file to a rich text content control in another file. The copy works fine. But now the content control cannot be cast into a SdtElement in open xml nor to a ContentControl in VSTO.

This is the code I used

SdtElement sdtElement = destinationdocument.MainDocumentPart.Document.Body.Descendants<SdtElement>().Where(b => b.SdtProperties.GetFirstChild<Tag>() != null).FirstOrDefault();
string altChunkId = "AltChunkId" + Guid.NewGuid().ToString();
AlternativeFormatImportPart chunk = destinationdocument.MainDocumentPart.AddAlternativeFormatImportPart(AlternativeFormatImport    PartType.WordprocessingML, altChunkId);
chunk.FeedData(File.Open("sourceFile", FileMode.OpenOrCreate));
AltChunk altChunk = new AltChunk();
altChunk.Id = altChunkId;
sdtElement.RemoveAllChildren();
sdtElement.Append(altChunk);

the first time the code works fine. But at the second run the first line throws an unable to cast exception. The same problem occurs while using VSTO at the client side the ContentControl object cannot hold the content control in which the altchunk was inserted. Somehow this procedure corrupts the rich text content control.

Is there anything I am doing wrong? Or is there a better alternative?

Content control Range WordOpenXml get modified

sonu gupta — Mon, 11 Feb 2013 10:07:15 GMT

Hi,

I am getting the WordOpeXml from content control range object using VSTO Project. I am getting different WordOpenXMl on content control enter and exit event with any change in content control.

on enter event i am getting wordopenxml like-

<pkg:part pkg:name="/word/document.xml" pkg:contentType="application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml">
- <pkg:xmlData>
- <w:document mc:Ignorable="w14 wp14" xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml" xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup" xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml" xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape">
- <w:body>
- <w:p w:rsidR="00000000" w:rsidRDefault="00747F03" w:rsidP="00747F03">

On Exit event i am getting wordopenxml like-

- <pkg:part pkg:name="/word/document.xml" pkg:contentType="application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml">
- <pkg:xmlData>
- <w:document mc:Ignorable="w14 wp14" xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml" xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup" xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml" xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape">
- <w:body>
- <w:p w:rsidR="00000000" w:rsidRDefault="00144C37" w:rsidP="00144C37">

please let me know how i can compare the rich text of a content control using VSTO

thanks in advance

How to copy the contents of a rich text content control to a content control in a different word document

lohiarahul — Thu, 07 Feb 2013 13:56:46 GMT

I currently have this need to copy the contents of a rich text content control alongwith images and formatting and assign it to a similar content control in a different document.

I have this contentControl.Range.WordOpenXml property in C#.NET which returns me a open xml with all the images inline in a byte stream.

The XML begins something like this

<?xml version="1.0" standalone="yes"?>
<?mso-application progid="Word.Document"?>
<pkg:package xmlns:pkg="http://schemas.microsoft.com/office/2006/xmlPackage">
<pkg:part pkg:name="/_rels/.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="512">

I want to add this content to the content control to the other file and I havent been able to find a single sample or an example to tell me how to do that. Is this even possible?

How can i add summary slide into powerpoint with openxml, C#

cYounes — Wed, 06 Feb 2013 09:53:08 GMT

Hi ,

I've a Powerpoint file generated with Openxml , i want to know if i can add a new slide in specified position (usually second slide) that contains a summary for all the titled slides in my presentation?

If you have any code suggests please help me!

Thanks

Word found unreadable content in "XXX". Do you want to recover the contents of this document?

jerltlim — Mon, 04 Feb 2013 04:10:48 GMT

I have one issues when open word document
it will prompt some error message, but when you save it again
it will be fine. I had search the internet and find this cause by
when using openxml altchunk function some content type didn't
append into [Content_Types].xml. This can be verify by change the
extension from .docx to .zip, any zip archiver will be able to
open it. In the archive you will find a couple of xml files,
if you compare the [Content_Types].xml between export word
document and after save again export word document there are
some missing [Content_Types].xml. anyone can help on this?

Server Side Docx Generation

yvkevitch — Tue, 29 Jan 2013 10:50:14 GMT

Hello everybody,

I'm currently working on a project. One part of this project is a word document generation web service.

The process of generating a document should work like this.

- Copy the template as the new document.

- For each content control of the template

- Get the tag of the content control.

- The tag indicate me the data to retrieve.

- Get the data in database

- Fill the content control with the data.

I've made some test and i'm able to put simple text in plain text content control when they are in a SdtRun element. But sometimes they are in a SdtBlock element.

Another problem is that sometimes, data is not a simple text as a name, it's a list of data.

I'm going crazy with this f.....g document generation.

Am i on the good option to do this with OpenXml? Some advices would be very appreciated.

Thanks

Create Excel file using OpenXML

cms9651 — Thu, 24 Jan 2013 16:21:16 GMT

hi there, hope in your help.

i need Create Excel file in ASP.NET C# using OpenXML and this is my example link:

http://www.c-sharpcorner.com/uploadfile/jayendra/how-to-create-excel-file-in-asp-net-c-sharp/

i tried this code but i have error:

CS0246: The type or namespace name 'OpenXmlDataDataContext' could not
be found (are you missing a using directive or an assembly reference?)

Can you help me?
Any other suggestion for my problem?
.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="OpenXml.aspx.cs" Inherits="OpenXml" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btn_Excel" runat="server" Text="Excel" 
            onclick="btn_Excel_Click" />
    </div>
    </form>
</body>
</html>
.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;

public partial class OpenXml : System.Web.UI.Page
{
    DataTable Dt = new DataTable();
    object[] query;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {


        }
    }

    private void GetRecoredForExcelfile()
    {
        using (OpenXmlDataDataContext db = new OpenXmlDataDataContext())
        {
            var info = from p in db.userinfos
                       select p;

            if (info != null)
            {
                query = info.ToArray();
                Dt = ConvertToDatatable(query);
            }
        }
    }

    public static DataTable ConvertToDatatable(Object[] array)
    {
        PropertyInfo[] properties = array.GetType().GetElementType().GetProperties();
        DataTable dt = CreateDataTable(properties);
        if (array.Length != 0)
        {
            foreach (object o in array)
                FillData(properties, dt, o);
        }
        return dt;
    }

    #region Private Methods

    private static DataTable CreateDataTable(PropertyInfo[] properties)
    {
        DataTable dt = new DataTable();
        DataColumn dc = null;
        foreach (PropertyInfo pi in properties)
        {
            dc = new DataColumn();
            dc.ColumnName = pi.Name;
            dt.Columns.Add(dc);
        }
        return dt;
    }
      
    private static void FillData(PropertyInfo[] properties, DataTable dt, Object o)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyInfo pi in properties)
        {
            dr[pi.Name] = pi.GetValue(o, null);
        }
        dt.Rows.Add(dr);
    }

    #endregion
    protected void btn_Excel_Click(object sender, EventArgs e)
    {
        GetRecoredForExcelfile();
        string newFilePath = Server.MapPath("ExcelFile/ErrorList.xlsx");
        string templateFilePath = Server.MapPath("ExcelFile/ErrorListtemplate.xlsx");
        FileInfo newFile = new FileInfo(newFilePath);
        FileInfo template = new FileInfo(templateFilePath);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
        {
            foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
            {
                aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
            }
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
            int startrow = 5;
            int row = 0;
            int col = 0;
            for (int j = 0; j < Dt.Columns.Count; j++)
            {
                col++;
                for (int i = 0; i < Dt.Rows.Count; i++)
                {
                    row = startrow + i;
                    ExcelCell cell = worksheet.Cell(row, col);
                    cell.Value = Dt.Rows[i][j].ToString();
                    xlPackage.Save();
                }
            }
        }
    }
}