Clever Geek Handbook

Concatenate cells in Excel using "Openxml writer"

I want to merge cells. Using a DOM approach, I can do this easily. But since my excel file is too big, when I try to get the sheet, it throws an out of memory exception. So I have to use SAX approach to read the excel file. But I don't know how to merge cells in this approach. Searched a lot, but each one gives a solution to the DOM approach.

Can anyone help, thanks in advance.

+3


source to share


2 answers


You can do it with EPPlus :



using (ExcelPackage package = new ExcelPackage(new FileInfo(@"C:\Temp\example.xlsx")))
{
    ExcelWorksheet ws = package.Workbook.Worksheets.First();
    ws.Cells["A1:A2"].Merge = true;
    package.Save();
}
0


source


My solution: read all data from old sheet using openxmlreader than write to new sheet using openxmlwriter so that I can add the merge cell element after writing the data to the sheetdata element.

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(YourExcelfileName, true))
        {
            // Create reference of main Workbook part, which contains all reference.
            WorkbookPart objworkbook = myDoc.WorkbookPart;

            // pick up first worksheet
            WorksheetPart objworksheet = objworkbook.WorksheetParts.First();

            // will be used in end while creating sheet data
            string objorigninalSheetId = objworkbook.GetIdOfPart(objworksheet);
            WorksheetPart objreplacementPart = objworkbook.AddNewPart<WorksheetPart>();
            string objreplacementPartId = objworkbook.GetIdOfPart(objreplacementPart);

            Sheets sheets = objworkbook.Workbook.Sheets;
            Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(objreplacementPart), SheetId = 2, Name = "Sheet" };
            sheets.Append(sheet);

            // Create object reader to read from excel file.
            OpenXmlReader objreader = OpenXmlReader.Create(objworksheet);

            // create writer object to write in excel sheet.
            OpenXmlWriter objOpenXmwriter = OpenXmlWriter.Create(objreplacementPart);

            objOpenXmwriter.WriteStartElement(new Worksheet());
            objOpenXmwriter.WriteStartElement(new SheetData());


            while (objreader.Read())
            {
            //read the context of original sheetdata.
            //then put all data to new sheetdata by openxmlwriter

            }

            // this is for Sheetdata
            objOpenXmwriter.WriteEndElement();

            //* after Sheetdata you can merge cells by openxmlwriter like that:    

            objOpenXmwriter.WriteStartElement(new MergeCells());
            objOpenXmwriter.WriteElement(new MergeCell() { Reference = "P1:Q1" });
            objOpenXmwriter.WriteElement(new MergeCell() { Reference = "R1:S1" });
            objOpenXmwriter.WriteEndElement();

            // this is for Worksheet
            objOpenXmwriter.WriteEndElement();

            //close all objects
            objreader.Close();
            objOpenXmwriter.Close();

            Sheet sheetreplace = objworkbook.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(objorigninalSheetId)).First();
            sheetreplace.Id.Value = objreplacementPartId;
            objworkbook.DeletePart(objworksheet);

        }

The way to combine cells in Excel using Openxml Writer is below:

objOpenXmwriter.WriteStartElement(new MergeCells());
objOpenXmwriter.WriteElement(new MergeCell() { Reference = "P1:Q1" });
objOpenXmwriter.WriteElement(new MergeCell() { Reference = "R1:S1" });
objOpenXmwriter.WriteEndElement();


Recommendations:

Merge cell example

Cell merge function

openxmlwriter example

0


source






More articles:


All Articles