SpreadsheetML TotalsRow

Question

Hi,

I am creating a spreadsheet programmely using SDK 2.0.

I am trying to add a totalsrow to my table, and put in a count and sum formula.

Any one have an exsample on how to do this, have tried to use Produktivity tool to see code, and it is also showing the totalsrow, if i decide not to create the formula cells on the last row. What can i do wrong ?

When i open i excel it says error in table tag, and repairs. After repair, the totals row are gone, but my to forulas are there on last row, shoing correct results.

Regards

Michael Frostholm

Answer 1

Hi Michael,

Thanks for posting in the MSDN Forum.

I hope this snippet can help you:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ConsoleApplication7
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Multiselect = false;
            ofd.Filter = "Excel Document|*.xlsx";
            ofd.ShowDialog();
            string opath = ofd.FileName;
            string newpath = AppDomain.CurrentDomain.BaseDirectory 
                + "test.xlsx";
            File.Copy(opath, newpath, true);
            using (SpreadsheetDocument sd = SpreadsheetDocument
                .Open(newpath, true))
            {
                WorkbookPart wp = sd.WorkbookPart;
                Workbook wb = wp.Workbook;
                Sheet s = wb.Descendants<Sheet>().Where(w => w.Name == "Sheet1")
                    .FirstOrDefault();
                WorksheetPart sp = wp.GetPartById(s.Id) as WorksheetPart;
                if (sp != null)
                {
                    SheetData sda = sp.Worksheet.Descendants<SheetData>()
                        .FirstOrDefault();
                    Row cr = new Row() { RowIndex = (UInt32Value)8U };
                    Cell c1 = CreateCell("A8", "SUBTOTAL(109,A2:A7)", "51");
                    Cell c2 = CreateCell("B8", "SUBTOTAL(109,B2:B7)", "57");
                    Cell c3 = CreateCell("C8", "SUBTOTAL(109,C2:C7)", "63");
                    cr.Append(c1);
                    cr.Append(c2);
                    cr.Append(c3);
                    sda.Append(cr);
                    CalculationChainPart ccp = wp
                        .AddNewPart<CalculationChainPart>();
                    CalculationChain cc = new CalculationChain();
                    CalculationCell cc1 = new CalculationCell()
                    {
                        CellReference = "A8",
                        SheetId = 1,
                        NewLevel = true
                    };
                    CalculationCell cc2 = new CalculationCell()
                    {
                        CellReference = "B8",
                        SheetId = 1
                    };
                    CalculationCell cc3 = new CalculationCell()
                    {
                        CellReference = "C8",
                        SheetId = 1
                    };
                    cc.Append(cc1);
                    cc.Append(cc2);
                    cc.Append(cc3);
                    ccp.CalculationChain = cc;
                }
                wb.Save();
            }
        }

        private static Cell CreateCell(string p, string p_2, string p_3)
        {
            Cell result = new Cell() { CellReference = p };
            CellFormula cf = new CellFormula() { Text = p_2 };
            CellValue cv = new CellValue() { Text = p_3 };
            result.Append(cf);
            result.Append(cv);
            return result;
        }
    }
}

This is the original WorkBook:

And This is the result:

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Tom

Thanks for your reply, what you gave me as an exsample is what i have tried and i have been able to make sums like you had, but its not exactly what i wan to have.

I need to programmely create a totalsRow like this one :



Where you can click the arrow and change, or put in new formulas.

I have the following set in my table xml (the result xml from my program.

If create the calculation chain with calculation cells excel tells me there are error in table and repair it, if i remove this part, i would get a empty totalsRow below, so i hope this part would help understand my problem.

Regards

Michael Frostholm

Answer 3

Hi Michael,

Would you please share your Excel document on Skydrive for further research?

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Tom

This is the spreadsheet i have created from my c# program : http://mgo.cybermaniac.dk/newTest.xlsx

This is how I would like the program to create the spreadsheet : http://mgo.cybermaniac.dk/newTest2.xlsx

Thanks in advance.

Regards

Michael

Answer 5

HI Michael,

I think you have icorrect code in the TablePart. Please double check it.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hi,

I have checked, checked and checked again but with no success, i stille get same error and i cant se what different from another excel where i have made the CalculationRow manually.

I can see thoug if i remove formulas from the column fields, and also the calculation cells, i will get the totalRow showing empty but with no errors. If add it all it tells me i have an error in tablePart but not where (it seems like it removes totalsRowCount="1", but why i dont know.

If i then try to remove everything wich contains totalrow, but still adds my calculation cells (and also change the formula from SUBTOTAL(103,[Avtalenummer]) to lets say SUBTOTAL(103,A1:A10) it would show the result as a normaly formal outside a totalrow.

So i am really confused, and hopefully some of you can put me in the right direction.

Regards

Michael F

Answer 7

Hi

Maybe the problem lays in my table crations, is there someone who can give a simple sample in how to have multiple tables on one sheet ?

I have looked and looked, compared many sheets, and i cant se any difference in the excel file i create and the one i do manually, but still the one done by open xml is telling me i have an error i table part .

It removes the "totalsRowCount="1" and there for my calculation cells are failing in the reference.

Answer 8

I stille have this problem, and i have not managed to solve it.

Is there not someone who can give me a simple exsample on a method i could call with a worksheetpart, wich creates a table on the sheet. I should be able to call this method many times for same worksheet to insert multiple tables on same sheet.

Really hopes for some help

Regards

Michael