SpreadsheetML - Recent Threads

Grouping Excel rows using open xml

krishnaravi — Tue, 09 Apr 2013 15:30:59 GMT

Hi,

Can someone tell me if it is possible to group excel rows using open xml? I tried to search for the same on the web but couldn't find anything. If it's not possible to group using open xml then I might look for other tools to automate excel on the serverside.

Any help on this will be appreciated.

Thank you

Ravi

Embedded Excel spreadsheet in Word Document - cannot update data

benone_ionescu — Tue, 09 Apr 2013 08:00:39 GMT

Hi,

I developed an application to automatically update an embedded Excel Spreadsheet in a Word document using Open XML SDK 2.0.

The problem is I cannot update the “Word view” of the embedded spreadsheet.

I need double click on the embedded Excel spreadsheet to update the “Word view” in order to have the changed values reflected correctly.

I presume is the same situation as in the case of embedded charts in a Word document.

I can easily update the Excel spread sheet of the embedded chart , but to reflect this update in the “Word view” of the chart I have to update also the cache of the chart (in Open XML I use chartSpace class – XML node <c:chartSpace>)

The code to update the "Word view" of the embedded chart looks like this :

/////////////////////

DocumentFormat.OpenXml.Drawing.Charts.Chart chart =
    this.chartPart.ChartSpace.Elements<DocumentFormat.OpenXml.Drawing.Charts.Chart>().FirstOrDefault();
try
{
    if (null != chart)
    {
        PlotArea plotArea = chart.Elements<PlotArea>().FirstOrDefault();
         if (null != plotArea)
        {
            foreach (LineChart lc in plotArea.Elements<LineChart>())
            {
                if (lc != null)
                {
                    foreach (LineChartSeries lcs in lc.Elements<LineChartSeries>())
                    {
                        if (lcs != null)
                        {
                            try
                            {
                                SeriesText seriesTextLcs = lcs
                                            .Elements<DocumentFormat.OpenXml.Drawing.Charts.SeriesText>()
                                            .FirstOrDefault();

                                if (null != seriesTextLcs)
                                {
                                     StringReference strRefLcs = seriesTextLcs
                                                .Elements<DocumentFormat.OpenXml.Drawing.Charts.StringReference>()
                                                .FirstOrDefault();

                                    if (null != strRefLcs)
                                    {
                                        /*..........*/
                                    }
                                }
                            }
                            catch (Exception /*ex*/)
                            {
								/*..........*/
                            }
                        }
                    }
                }
            }
        }
    }
}
finally
{
    try
    {
        if (null != this.chartPart)
            this.chartPart.ChartSpace.Save();
    }
    catch (Exception /*ex*/)
    {
         /*..........*/
     }
}
/////////////////////

So my question is: does Open XML have a similar class to update the cache of the embedded Excel spreadsheet, without manual intervention?

Or probably there is another technique which I could use?

Thanks in advance.

Open Xml

Sumanth — Wed, 27 Mar 2013 09:30:37 GMT

I Have opened the already existing ExcelFile and Updated it with OpenXml , i was done, but when iam trying to open my ExcelFile it is saying that

"We found some problem with some content in doc1.xlsx fileDo you want to try to recover as much as we can?Press if Yes"

If i press yes iam getting all the updated data into my excel file , but i don't want to get any dialogbox like that when i open my excelfile , can any one say how to get the clean excel with out any dialogbox when i open my excelfile.

Practicality of converting xlsx to docx?

dave.dawley — Sun, 24 Mar 2013 15:21:21 GMT

I've been doing some small amount of work with OOXML SDK and PT4OOXML and been moderately successful thanks to the resources on this site. In the process now of doubling back to fill in the gaps in my high level understanding by going through the RPFT screen cast set.

I've been asked to provide an opinion on the practicality of extracting the content from a multi-tab excel workbook and saving that content complete with images and charts into a word document. On the surface this seems a perfect application of RPFT, but I would like to hear input from folks with more experience regarding what sorts of blocks I should be on the lookout for.

I'm not asking anyone to write the code for me (although I'll gratefully review all offerings :-), just feedback on what sorts of issues folks have encountered attempting similar transformations.

Thanks!

How to insert data to multiple sheet in the excel using openxml

dineshharaveer — Thu, 21 Mar 2013 10:30:52 GMT

Hi,

i have been trying to insert data to multiple sheets.For my excel i have two sheets which is "charts" sheet and "ChartData" sheet, i'm able to update the data to the sheet2 i.e chartdata sheet but i'm unable to insert data to the sheet1.Here is the below code which i have tried to insert data to excel sheets.Here data is coming from database.

double ticks = DateTime.Now.Ticks;
            // MarketAnalysis ms = new MarketAnalysis();
            //ms.Marketanalysis();

            File.Copy(Srcpath, @"E:\Works\OpenXML\DownloadTemplates\ExcelGenerated" + ticks + ".xlsx", true);
            using (SpreadsheetDocument myworkbok = SpreadsheetDocument.Open(@"E:\Works\OpenXML\DownloadTemplates\ExcelGenerated" + ticks + ".xlsx", true))
            {
                //Acess the main workbook which contain all the references

                WorkbookPart workbookpart = myworkbok.WorkbookPart;
                //Get sheet by name
                Sheet sheet = workbookpart.Workbook.Descendants<Sheet>().Where(s => s.Name == "ChartData").FirstOrDefault();

                //Worksheet Part by ID
                WorksheetPart worksheetpart = workbookpart.GetPartById(sheet.Id) as WorksheetPart;

                //Sheet data contains all the data
                SheetData sheetdata = worksheetpart.Worksheet.GetFirstChild<SheetData>();
                DataSet ds = db.Chart1Data();

                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    // if (ds.Tables[0].Rows !=DBNull)
                    //{

                    string Rowlabel = ds.Tables[0].Rows[i][0].ToString();
                    // float? FY13Actuval = Convert.ToInt32(ds.Tables[0].Rows[i][1]);

                    if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i][1].ToString()))
                    {
                        // string s = ds.Tables[0].Rows[i][1].ToString();
                        //FY13Actuval=float.Parse(ds.Tables[0].Rows[i][1].ToString());

                        FY13Actuval = Convert.ToDouble(ds.Tables[0].Rows[i][1].ToString());
                    }
                    else
                    {

                        FY13Actuval = null;
                    }

                    double? Budget = Convert.ToDouble(ds.Tables[0].Rows[i][2].ToString());
                    double? Actuval = Convert.ToDouble(ds.Tables[0].Rows[i][3].ToString());
                    Row contentrow = CreateContentRow(index, Product, Actual, Budget, Forecast);

                    index++;
                    sheetdata.AppendChild(contentrow);

                    // }
                }
   .......Same code for the other 3 charts
   Sheet sheet1 = workbookpart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Charts").FirstOrDefault();

                WorksheetPart worksheetpart1 = workbookpart.GetPartById(sheet1.Id) as WorksheetPart;

                //Sheet data contains all the data
                SheetData sheetdata1 = worksheetpart1.Worksheet.GetFirstChild<SheetData>();

                DataSet dsTbl = db.Table();
                int SCMTblIndex=5;
                for (int i = 0; i < dsTbl.Tables[0].Rows.Count; i++)
                {
                    Row tblRow = CreateScorecardMetricTblRow(SCMTblIndex, dsTbl.Tables[0].Rows[i][0].ToString(),
                                                                       dsTbl.Tables[0].Rows[i][1].ToString(),
                                                                       dsTbl.Tables[0].Rows[i][2].ToString());
                    SCMTblIndex++;
                    sheetdata1.AppendChild(tblRow);
                }

                    myworkbok.WorkbookPart.Workbook.Save();
            }

and the Methods to create the cells

public static string[] headerColumns = new string[] { "A", "B", "C", "D", "E", "F", "G", "I", "J" };
        public static string[] header = new string[] { "D", "E", "F" };

private static Row CreateContentRow(int index, string Product, double? Actual, double? Budget, double? Forecast)
        {

            //Create New ROw
            Row r = new Row();
            r.RowIndex = (UInt32)index;

            //Begin colums
            Cell c0 = new Cell();
            c0.CellReference = headerColumns[0] + index;
            CellValue v0 = new CellValue();
            v0.Text = Product;
            c0.AppendChild(v0);
            r.AppendChild(c0);

            Cell c1 = new Cell();
            c1.CellReference = headerColumns[1] + index;
            CellValue v1 = new CellValue();
            v1.Text = Actual.ToString();
            c1.AppendChild(v1);
            r.AppendChild(c1);

            Cell c2 = new Cell();
            c2.CellReference = headerColumns[2] + index;
            CellValue v2 = new CellValue();
            v2.Text = Budget.ToString();
            c2.AppendChild(v2);
            r.AppendChild(c2);

            Cell c3 = new Cell();
            c3.CellReference = headerColumns[3] + index;
            CellValue v3 = new CellValue();
            v3.Text = Forecast.ToString();
            c3.AppendChild(v3);
            r.AppendChild(c3);

            return r;

        }
public static Row CreateScorecardMetricTblRow(int index, string Act_Data, string Bud_Data, string VarPr_Data)
        {
            Row r = new Row();
            r.RowIndex = (UInt32)index;
            //Begin Colums

            Cell c0 = new Cell();
            c0.CellReference = header[0] + index;
            CellValue v0 = new CellValue();
            v0.Text = Act_Data;
            c0.AppendChild(v0);
            r.AppendChild(c0);

            Cell c1 = new Cell();
            c1.CellReference = header[1] + index;
            CellValue v1 = new CellValue();
            v1.Text = Bud_Data;
            c1.AppendChild(v1);
            r.AppendChild(c1);

            Cell c2 = new Cell();
            c2.CellReference = header[2] + index;
            CellValue v2 = new CellValue();
            v2.Text = VarPr_Data;
            c2.AppendChild(v2);
            r.AppendChild(c2);

            return r;
        }

Populating Excel Sheet which already contains some data

sharathvollala@live.com — Wed, 20 Mar 2013 09:08:31 GMT

i am trying to insert data into the sheet which will have data in it. To insert data i create a row and then i create cells and add that cells to row.

Rows are appended to sheet data.

After populating when i open excel file,it is showing

Why it giving error??

i found many examples creating excel sheet from scratch.

Can anyone provide an example how to populate data to excel which already contains data. or plz provide how to address this problem ?

thanks

Creating XSLX Document with A Table and a pivot table.

scarface335 — Sun, 24 Feb 2013 14:23:54 GMT

Hi.

Im All new to excel and OpenXML, i ealier have done some Word projects in open XML.

What i need to do is create a a excel document containing to things.

1) a Table with 3 coulms. UserID, ProductName, Price.

2) a Pivot table showing total for each user /Product Like show that we can see that user A bought 7 cola's 3 toast's, and with price.

We used this to keep track of what each people consume to our lan parties :).

Can anyone point me in a direction or an example of how this can be accomplished.

Open XML SDK SpreadsheetML - Sort a range

Doug2499 — Fri, 25 Jan 2013 23:34:53 GMT

I have tried for the last 3 days trying to get a spreadsheet to sort.

The package validates and opens without a problem. I have appended a SortCondition to a SortSate to a Table.

Here is a snippet of the Table:

table =

new Table(new SortState(new SortCondition() { Reference = "F2:F" + (UInt32)(filter.Length + 1) },

new SortCondition() { Reference = "E2:E" + (UInt32)(filter.Length + 1) }) { Reference =

"A2:R8" + (UInt32)(filter.Length + 1) })UInt32)1U, HeaderRowCount = 1, DisplayName ="Contacts" ,Name ="Contacts",

Reference = "A1:R" + (UInt32

)(filter.Length + 1),BorderFormatId = 0,TotalsRowShown =

false

};

All other features of the table work fine: the styles and tablecolumns.

The cell datatypes are strings and numbers. I am using strings but don't know if cells need to be converted to inlinestrings or not.

If I stick an AutoFilter in...it shows up and the range clearly shows an A to Z sort but the range is not sorted.

I am not sure what else to do.

Thanks,

Doug

{Id = (

Problems Putting XHTML content in a specific Excel cell

willemvandijk — Tue, 08 Jan 2013 08:50:06 GMT

Hi,

I'm working on a report generator that generates an Excel document based on database content. One of the fields the user can fill out is through an HTML editor. I need to put the content of this field, in XHTML format, in one specific cell in my worksheet. I know there are ways to convert HTML to Excel but these generate new sheets with many cells. I just want to convert this content and put it in one cell, with formatting.

Is this possible? Is there an easy way to do this?

Regards,

Willem

Apply Table Style in exce to SQL imported data

sebryan — Thu, 03 Jan 2013 20:18:30 GMT

Hi,

I am new to open xml. I currently have a project that I import sql data into excel package. What I would like to do to apply a table style(basic) to the data i just imported into excel. i have seen example code that does this, but the amout of columns are static, so the range that I am appling the table is static. The data that I import could be 3 columns or it could have 10 columns. So I need to know how to set the table to the size(range) of the spreadsheet that Imported. Hope this make sense.

Any examples will help.

Thanks,

Scott Bryan

Set "Rows to repeat at top" property of an xlsx file from C#

dtbossle — Mon, 26 Nov 2012 16:52:22 GMT

I have an xlsx file that i am generating from a C# application, and I need to make some formatting tweaks before delivering it to the user, as the typical user has only very basic Excel skills.

I have been able to set page margins, paper size, orientation, frozen panes and print scaling. However, I cannot figure out how to set the "Rows to repeat at top" property from C#. Does anyone know how to do this? Here's the code I've got so far, in case it's useful:

using (SpreadsheetDocument xl = SpreadsheetDocument.Open("C:\\" + filename, true))
                    {
                        WorkbookPart wbp = xl.WorkbookPart;
                        IEnumerable<Sheet> sheets = xl.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "alpha_sort_nc");

                        if (sheets.Count() == 0) return; // The specified worksheet does not exist.  
                        WorksheetPart wsp = (WorksheetPart)xl.WorkbookPart.GetPartById(sheets.First().Id);
                        SheetView sw = wsp.Worksheet.SheetViews.FirstOrDefault() as SheetView;

                        //-----------------------------------------
                        // frozen pane
                        //-----------------------------------------
                        Pane pane = new Pane()
                        {
                            VerticalSplit = 4D,
                            HorizontalSplit = 1D,
                            TopLeftCell = "B5",
                            ActivePane = PaneValues.BottomLeft,
                            State = PaneStateValues.Frozen
                        };
                        sw.Append(pane);
                        
                        //-----------------------------------------
                        // orientation, paper size and scale
                        //-----------------------------------------
                        PageSetup ps = wsp.Worksheet.Descendants<PageSetup>().FirstOrDefault();
                        ps.Orientation = OrientationValues.Landscape;
                        ps.PaperSize = 5;
                        ps.Scale = 52;
                        //ps.
                        
                        //-----------------------------------------
                        // margins
                        //-----------------------------------------
                        PageMargins pm = wsp.Worksheet.Descendants<PageMargins>().FirstOrDefault();
                        pm.Left = 0.25D;
                        pm.Right = 0.25D;
                        pm.Top = 0.25D;
                        pm.Bottom = 0.25D;

                        //-----------------------------------------
                        // Save
                        //-----------------------------------------
                        wsp.Worksheet.Save();
                        wbp.Workbook.Save();
                        xl.Close();
                    }

How to Use External Stylesheet

rspiet — Mon, 17 Sep 2012 16:51:48 GMT

I am new to Spreadsheet ML, but have been somewhat sucessful in creating new spreadsheet documents using VB.Net. The last problem I seem to be having is how to use and extrnal stylesheet.xml file (for pre-defined styles). I'm not sure if the format of my PredefinedStyles.xml is the issue or the way I am reading it in. I created a sample spreadsheet and copied out the styles.xml (see attached file). I always get errors that my sytles.xml is invalid. Here is the code that I use.

I have attached a text file which has my styles.xml in it and a snippet of the code that I use to read it in. Any help would be appreciated, thanks.

Creating (XLSM) document from an XLT template using openxml

sghosh — Thu, 30 Aug 2012 16:42:50 GMT

How to create programatically a macro enabled excel 2007 (XLSM) document from an existing macro enabled excel 2003(XLT)template using openxml

Where to find documentation of Ribbon UI tags

Chandan — Thu, 30 Aug 2012 10:52:39 GMT

I have created a ribbon toolbar button using custom ui editor tool following the link http://stackoverflow.com/a/8852767/649524

I need to do further customization. Where can i find the documentation? I have downloaded ECMA 376 (http://www.ecma-international.org/publications/standards/Ecma-376.htm) but could not find relevant information.

Specific thing i am looking at is to create a hyperlink inside ribbon group.

importing unzipped xlsx files back into excel

Sanford — Mon, 13 Aug 2012 21:29:25 GMT

I have been given the following task: unzip an xlsx file, creating a directory structure of xml files, and then read one or more of those xml files back into Excel.

The result should be the same as if I directly opened the xlsx file. The XML file that seems to hold the data is sharedStrings.xml, but when I read that into Excel 2010, it does not produce the proper rows/columns. I suspect that information from the other files is needed, or a schema/stylesheet needs to be defined. The XML files point to schema.openxmlformats.org, but that page is inaccessible. Any ideas as to how to make this work, or if it is even possible?Thanks,Sanford Stein, CyberTools Inc.

write to second sheet of spreadsheet with a datatable using openxml

tibbs3000 — Fri, 27 Jul 2012 22:12:32 GMT

hello, i have gotten alot of good information off of this site and found various blogs showing me exactly how to export a datatable to a spreadsheet through .NET. . . here is what mystifies me . . . how do i write to the second sheet?

i have:

Dim workbook As WorkbookPart = spreadsheet.WorkbookPart

Dim worksheet As WorksheetPart = GetWorksheetPart(workbook, "commissions", spreadsheet)

with get worksheetpart as a function to find my commissions sheet as:

Public Shared Function GetWorksheetPart(workbookPart As WorkbookPart, sheetName As String, ssd As SpreadsheetDocument) As WorksheetPart Dim relId As String = workbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) sheetName.Equals(s.Name)).First().Id
Return DirectCast(ssd.WorkbookPart.GetPartById(relId), WorksheetPart)

End Function

then i say give me sheetdata to collect my info:

Dim data As SheetData = worksheet.Worksheet.GetFirstChild(Of SheetData)()

so i guess my question is do i need to initialize a second worksheet and data container like this to access the second sheet:

Dim worksheet2 As WorksheetPart = GetWorksheetPart(workbook, "on_hold", spreadsheet)

Dim data2 As SheetData = worksheet2.Worksheet.GetFirstChild(Of SheetData)()

i am trying to do this but its not right. When i run my code for just the commission sheet i get all my expected results, i have not been able to write to my 'on hold' sheet. I am copying this new workbook from a template and using various style indexes so i need a multi sheet spreadsheet and i also need my sheetdata containers to put my datatables in . . . im sure this is something small but i just can't see it.

Caputure line breaks in Cells

ByrdT0cm — Tue, 26 Jun 2012 19:59:14 GMT

I am working on a C# project. One of the requirments is to be able to capture line breaks in a cell. In capturing the line break I will have to recreate the cell value containing any line breaks in a word doc. So far I am able to capture all the information I need except the line breaks. Any help would be greatly appreciated.

Content Controls in Spreadsheet

joel — Tue, 26 Jun 2012 13:07:47 GMT

Hi all,

i'm new to this forum but i sure i'm in the right place to ask my question.

Could some tell me if it's possible to insert content in SpreadsheetDocument as in WordDocument and change thier value at runtime.

Actually i have an excel template and woul like to open the file in runtime an change the value of some cell then save the file back.

appreciate any help!

How to move Comment and Button?

Tucsonan999 — Fri, 15 Jun 2012 00:31:51 GMT

I have 2 tables on the same worksheet. The last row of each table has a cell that contains a comment and another cell that contains a button that runs a macro. I can insert a new row into each of the tables and adjust the row index and cell references of the rows that are below the newly added row and adjust the cell reference of the comment, but how do I change the row in the vmlDrawing for where both the comment and the button should appear?

Third party formula won't open

UZX — Tue, 12 Jun 2012 15:56:38 GMT

Hello,

I created this spreadsheet which is quite simple but when I open it, a part of my calcChain and the formula is deleted. The two first line were in a template, I only added the C3 cell. On the other hand I did the same thing manually, excel opens it and when I open it with the SDK productivity tool it seems the same as the generated one to me.

Can anyone help me out with this, or tell me what's wrong in this file ? I will post the manually modified file if necessary.

Thanks

Copy Row, Insert Row, Paste Row

Tucsonan999 — Sun, 10 Jun 2012 19:55:20 GMT

I have a table in a spreadsheet template with 3 columns with headers. The first row in the table is populated, and the first cell in this row contains a formula, the second and third cells contain data validations. On the row just below the table is a button that runs a macro. I need to insert new rows with cells of data into the table following the first row and above the row containing the button, and use the same formula and data validations being used by the cells in the first row.

Is it possible to insert a new row with new cells in the table below the first row and above any remaining rows in the spreadsheet, and apply the formulas and data validations used by the cells in first row to the cells in new row? Can someone provide an example of how to do this?

Thanks!

HTML to Excel using Open XML

sagkas — Sat, 09 Jun 2012 12:47:34 GMT

Hi,

I would like to know how to Convert HTML to Excel using Open XML. I have an html file and I want to export it, as it is in Excel 2007 format.

Please suggest me with sample code.

Below is a Html file that is getting generated

Receipt

Receipt No:

Receipt Date:

06/06/2012

Received From:

Name, Surname

Referance No:

ABCD

Payment Type:

Cash

Referance Date:

06/06/2012

Payment Mode:

cash

Received Amount:

1000

Receipt Type:

Memo

Sr. No.	Receipt No.	Receipt Date	Amount	Received Amount
1	2	06/06/2012	1000	1000

Receipt

MS Excel - vs - ECMA-376 sheetProtection hashing algorithm?

randym — Mon, 21 May 2012 23:34:07 GMT

The ECMA spec uses the following three attributes to generate a password hash for sheet protection:

18.3.1.85 sheetProtection (Sheet Protection Options)

algorithmName

(defaults to SHA-1)

saltValue

Specifies the salt which was prepended to the user-supplied password before it was hashed using the hashing algorithm defined by the preceding attribute values to generate the hashValue attribute, and which shall also be prepended to the user-supplied password before attempting to generate a hash value for comparison.

spinCount

Specifies the number of times the hashing function shall be iteratively run (runs using each iteration's result plus a 4 byte value (0-based, little endian) containing the number of the iteration as the input for the next iteration) when attempting to compare a user- supplied password with the value stored in the hashValue attribute.

hashValue

Those attributes, in addition to the user supplied password are used to generated the hash value used for comparison when disabling sheet protection.

However after a frustrating day of implementing this and watching excel happily tell us that we must have the caps lock key enabled as the password entered is certainly not what is stored in the document we ended up going with Daniel Rentz' fairly old writeup here: http://www.openoffice.org/sc/excelfileformat.pdf.

(Un?)Fortunately that actually seems to work - but it is a FAR cry from what is actually documented

Wondering if anyone has been able to implement the specification, and actually get it to work with a modern (2007 and up) version MS Excel?

How to update a chart.xml

Matt Balraj — Wed, 16 May 2012 12:08:15 GMT

I have created an excel sheet that contains a chart. I update the data in an the excel file with OpenXML. The data gets updated and I am able to view the chart. However when I export the chart to Powerpoint the chart is empty. If I open this file in excel and if I save it the chart is updated properly. Using the Productivity tool I tried to find out what is different between a excel file that is saved in excel and another file that is not saved. I notice that Chart.xml is not updated. Specifically there is some element called cacheddata that gets updated if the file is opened and saved in Excel.

Can anyone tell me what I am doing wrong Or what I need to do to ensure that Chart.xml is updated properly

Where to find documentation on namespaces?

mothman — Thu, 10 May 2012 14:42:22 GMT

Hello,

Im new to openxml and trying two generate spreadsheets on our as/400 via openxml.

I can generated the basics such as font, color, background ... no problems. Im creating the SS and basically copying the generated xml into our programming.

Where can I go to find the namespace attributes and properties (what properties can be used with font/background/width/etc.) that can be used(where in openxml do they go) ? Im trying to get a bit more sophisticated and cant seem to find what Im looking for. This is a cgi based programming (not java/.net).

Thanx in advance ...