Re: Merging Cells in Open Office xml in asp.net using C#

Hi,

I am using OPEN OFFICE XML for exporting excel in my project.

How do I merge cells in Open Office xml ?

I want to merge this cells because I am binding the data from Database for this cells.

Please suggest me some source code .

Kindly Help.

Hi,
Do u mean by merging cells means you want to concatenate data from different cells?If  yes, then you can use below sample function to get any cell information.

///document: Spreadsheet document object

///sheetname :  sheet name which is having cells to be readed

/// CellAddress: address of cell e.g. "A3" "B2" etc.
public static string XLGetCellValue(SpreadsheetDocument document, string sheetName, string CellAddress)
        {
            string value = null;

            WorkbookPart wbPart = document.WorkbookPart;

            // Find the sheet with the supplied name, and then use that Sheet
            // object to retrieve a reference to the appropriate worksheet.
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);

            if (theSheet == null)
            {
                throw new ArgumentException("sheetName");
            }

            // Retrieve a reference to the worksheet part, and then use its 
            // Worksheet property to get a reference to the cell whose 
            // address matches the address you supplied:
            var wsPart =
                (WorksheetPart) (wbPart.GetPartById(theSheet.Id));
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == CellAddress);

            // If the cell does not exist, return an empty string:
            if (theCell != null)
            {
                value = theCell.InnerText;

                // If the cell represents a numeric value, you are done. 
                // For dates, this code returns the serialized value that 
                // represents the date. The code handles strings and Booleans
                // individually. For shared strings, the code looks up the 
                // corresponding value in the shared string table. For Booleans, 
                // the code converts the value into the words TRUE or FALSE.
                if (theCell.DataType != null)
                {
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            // For shared strings, look up the value in the shared 
                            // strings table.
                            SharedStringTablePart stringTable = wbPart.
                                GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            // If the shared string table is missing, something is 
                            // wrong. Return the index that you found in the cell.
                            // Otherwise, look up the correct text in the table.
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.
                                    ElementAt(int.Parse(value)).InnerText;
                            }
                            break;

                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                default:
                                    value = "TRUE";
                                    break;
                            }
                            break;
                    }
                }
            }
            return value;
        }

----------------------------------------------------------------------------------------

And other best way and easy way to work with Excel is to use EPPlus library. It is available on codeplex.

http://epplus.codeplex.com/releases/view/79802

Pranay...