none
Copy Cell property RRS feed

  • Question

  • Hi,
    Is there one example to copy Cell Width, Cell's font size, to one other Worksheet, using OpenXML?

    Many Thanks & Best Regards, Hua Min


    Tuesday, August 9, 2016 2:08 AM

Answers

  • >>Is there one example to copy Cell Width, Cell's font size, to one other Worksheet, using OpenXML?

    I suggest you get the cell width and font size firstly in one worksheet and then append new to the other worksheet.

    The following shows how to get the cell's width and font size.

    To append them to another worksheet, you could refer to

    Stylizing your Excel worksheets with Open XML 2.0

    How to: Copy the contents of an Open XML package part to a document part in a different package (Open XML SDK)

     string fileName = (@"C:\Users\Administrator\Desktop\Book1.xlsx");
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    WorksheetPart wsPart = wbPart.WorksheetParts.First();
                    WorkbookStylesPart wstylePart = wbPart.WorkbookStylesPart;
                    Stylesheet ss = wstylePart.Stylesheet;
                  foreach (Cell cell in wsPart.Worksheet.Descendants<Cell>())
                    {
                        if (cell.CellValue != null)
                        {
                            // get column number according to cellreference
                            int columnNumber = Convert.ToInt32(GetColumnIndex(cell.CellReference.Value));
                            // only modified width would be appended, and they are arranged by columnNumber                      
                            foreach (Column cellColumn in wsPart.Worksheet.Descendants<Column>())
                            {
    
                                if (cellColumn.Min.Value == columnNumber) // or cellColumn.Max.Value == columnIndex
                                {
                                    var cellWidth = cellColumn.Width.Value;
                                    Console.WriteLine(cellWidth);
                                }
                            // only cells with formatted have the property,and they are arranged by StyleIndex
                                if (cell.StyleIndex != null)
                                {
                                    int styleIndex = Convert.ToInt32(cell.StyleIndex.Value);
                                    CellFormat cellFormat = ss.Descendants<CellFormat>().ElementAt(styleIndex+1);
    
                                    int fontId = Convert.ToInt32(cellFormat.FontId.Value);
                                    Font font = ss.Descendants<Font>().ElementAt(fontId);
                                    FontSize fontsize = font.FontSize;
                                    Console.WriteLine(fontsize.Val.ToString());
                                }
                            }
                        }                                         
                    }
                }
     private static int GetColumnIndex(string cellReference)
            {
                //remove digits
                string columnReference = Regex.Replace(cellReference.ToUpper(), @"[\d]", string.Empty);
    
                int columnNumber = -1;
                int mulitplier = 1;
    
                //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
                //then multiply that number by our multiplier (which starts at 1)
                //multiply our multiplier by 26 as there are 26 letters
                foreach (char c in columnReference.ToCharArray().Reverse())
                {
                    columnNumber += mulitplier * ((int)c - 64);
    
                    mulitplier = mulitplier * 26;
                }
    
                //the result is zero based so return columnnumber + 1 for a 1 based answer
                //this will match Excel's COLUMN function
                return columnNumber + 1;
            }




    Wednesday, August 10, 2016 5:26 AM
    Moderator

All replies

  • >>Is there one example to copy Cell Width, Cell's font size, to one other Worksheet, using OpenXML?

    I suggest you get the cell width and font size firstly in one worksheet and then append new to the other worksheet.

    The following shows how to get the cell's width and font size.

    To append them to another worksheet, you could refer to

    Stylizing your Excel worksheets with Open XML 2.0

    How to: Copy the contents of an Open XML package part to a document part in a different package (Open XML SDK)

     string fileName = (@"C:\Users\Administrator\Desktop\Book1.xlsx");
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    WorksheetPart wsPart = wbPart.WorksheetParts.First();
                    WorkbookStylesPart wstylePart = wbPart.WorkbookStylesPart;
                    Stylesheet ss = wstylePart.Stylesheet;
                  foreach (Cell cell in wsPart.Worksheet.Descendants<Cell>())
                    {
                        if (cell.CellValue != null)
                        {
                            // get column number according to cellreference
                            int columnNumber = Convert.ToInt32(GetColumnIndex(cell.CellReference.Value));
                            // only modified width would be appended, and they are arranged by columnNumber                      
                            foreach (Column cellColumn in wsPart.Worksheet.Descendants<Column>())
                            {
    
                                if (cellColumn.Min.Value == columnNumber) // or cellColumn.Max.Value == columnIndex
                                {
                                    var cellWidth = cellColumn.Width.Value;
                                    Console.WriteLine(cellWidth);
                                }
                            // only cells with formatted have the property,and they are arranged by StyleIndex
                                if (cell.StyleIndex != null)
                                {
                                    int styleIndex = Convert.ToInt32(cell.StyleIndex.Value);
                                    CellFormat cellFormat = ss.Descendants<CellFormat>().ElementAt(styleIndex+1);
    
                                    int fontId = Convert.ToInt32(cellFormat.FontId.Value);
                                    Font font = ss.Descendants<Font>().ElementAt(fontId);
                                    FontSize fontsize = font.FontSize;
                                    Console.WriteLine(fontsize.Val.ToString());
                                }
                            }
                        }                                         
                    }
                }
     private static int GetColumnIndex(string cellReference)
            {
                //remove digits
                string columnReference = Regex.Replace(cellReference.ToUpper(), @"[\d]", string.Empty);
    
                int columnNumber = -1;
                int mulitplier = 1;
    
                //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
                //then multiply that number by our multiplier (which starts at 1)
                //multiply our multiplier by 26 as there are 26 letters
                foreach (char c in columnReference.ToCharArray().Reverse())
                {
                    columnNumber += mulitplier * ((int)c - 64);
    
                    mulitplier = mulitplier * 26;
                }
    
                //the result is zero based so return columnnumber + 1 for a 1 based answer
                //this will match Excel's COLUMN function
                return columnNumber + 1;
            }




    Wednesday, August 10, 2016 5:26 AM
    Moderator
  • How to resolve
    Error	2	'DocumentFormat.OpenXml.Packaging.WorksheetPart' does not contain a definition for 'WorkbookStylesPart' and no extension method 'WorkbookStylesPart' accepting a first argument of type 'DocumentFormat.OpenXml.Packaging.WorksheetPart' could be found (are you missing a using directive or an assembly reference?)	C:\App\WindowsFormsApplication9_1\WindowsFormsApplication2\Form1.cs	108	72	WindowsFormsApplication2



    due to last line below?
                                foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                                {
                                    WorkbookStylesPart wstylePart = wsPart.WorkbookStylesPart;



    Many Thanks & Best Regards, Hua Min

    Wednesday, August 10, 2016 7:26 AM
  • Hi,

    WorkbookStylesPart is not a child of WorksheetPart. They are sibling nodes. Please see the picture below.

    Change into WorkbookStylesPart wstylePart = workbookPart.WorkbookStylesPart;

    Wednesday, August 10, 2016 7:39 AM
    Moderator
  • Hi,

    It seems that you are using Excel interop and SpreadsheetML at the same time.

    CellFormat is an ambiguous reference, so you should try

    DocumentFormat.OpenXml.Spreadsheet.CellFormat cellFormat = ss.Descendants<DocumentFormat.OpenXml.Spreadsheet.CellFormat>().ElementAt(i);

    Wednesday, August 10, 2016 10:33 AM
    Moderator
  • Hi,

    You get this error because there is cell in your sheet without any formatted. 

    To fix it, add an if statement 

                            foreach (Cell cell in cells)
                            {
                                if (cell.StyleIndex != null)
                                {
                                    int i = Convert.ToInt32(cell.StyleIndex.Value);
                                }
                              
                            }

    Thursday, August 11, 2016 5:26 AM
    Moderator
  • Sorry, how to correct
    Error	2	The type 'System.Drawing.Font' cannot be used as type parameter 'T' in the generic type or method 'DocumentFormat.OpenXml.OpenXmlElement.Descendants<T>()'. There is no implicit reference conversion from 'System.Drawing.Font' to 'DocumentFormat.OpenXml.OpenXmlElement'.	C:\App\WindowsFormsApplication9_1\WindowsFormsApplication2\Form1.cs	137	72	WindowsFormsApplication2



    due to last line below?
                                                DocumentFormat.OpenXml.Spreadsheet.CellFormat cellFormat = ss.Descendants<DocumentFormat.OpenXml.Spreadsheet.CellFormat>().ElementAt(i);
    
                                                int j = Convert.ToInt32(cellFormat.FontId.Value);
                                                System.Drawing.Font font = ss.Descendants<System.Drawing.Font>().ElementAt(j);



    Many Thanks & Best Regards, Hua Min

    Thursday, August 11, 2016 8:56 AM
  • Hi,

    Use:

    DocumentFormat.OpenXml.Spreadsheet.Font font = ss.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ElementAt(j);

    Since you are using OpenXML and Office Interop at the same time, to avoid ambiguous references, when you call the class, you couldn't omit its namespace. And the code in the demo I shared only refers to the OpenXML library, so when you quote it, you need to add the correct namespace. 


    Thursday, August 11, 2016 9:15 AM
    Moderator
  • Thanks a lot and can you please show with more details to retrieve Width of column and TabColor of the sheet?

    Many Thanks & Best Regards, Hua Min


    Friday, August 12, 2016 3:36 AM
  • Hi,

    To retrieve Width of column, you could refer to my first reply. It returns the width of the specific cell based on its cellreference.

    Since the width of columns are appended to Columns node, we firstly need to get the cellreference and return the column number, then we could retrieve the column width according to its index.

    To get the TabColor of the sheet, I suggest you post a new thread because it is a new issue.

    Thanks for your understanding.

    Best Regards,

    Celeste

    Friday, August 12, 2016 12:10 PM
    Moderator
  • Any help?

    Many Thanks & Best Regards, Hua Min

    Monday, August 15, 2016 8:47 AM
  • Hi,

    There is no relationship between cell style index and column index.

    The column index is retrieving from the cell reference.

    E.g. You want to get the width of cell[B1], we firstly get the cellreference which is B1, then use the method GetColumnIndex to return 2 (B is column 2), then we can succeed in getting the column width.

    Please have a look at my first reply.

    Monday, August 15, 2016 9:28 AM
    Moderator
  •  Please notice the default width wouldn’t be append in the Columns node.

    The following is the XML info when I change the width of column A to D.

    <x:cols xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:col min="1" max="1" width="10.7109375" customWidth="1" />
      <x:col min="2" max="2" width="80.7109375" customWidth="1" />
      <x:col min="3" max="3" width="20.7109375" customWidth="1" />
      <x:col min="4" max="4" width="25.7109375" customWidth="1" />
    </x:cols>

    To work around the issue, I suggest you get the width of specific cell which is set differing from the default value.

    E.g. You could change the width of cells which are not empty and retrieve their width.

       using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart workbookPart = doc.WorkbookPart;
                    int sheetIndex = 0;
                    foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                    {
                        WorkbookStylesPart wstylePart = workbookPart.WorkbookStylesPart;
                        Stylesheet ss = wstylePart.Stylesheet;
     
                        string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;
                        if (sheetName != "Sheet Overall")
                        {
                            if (sheetName == null)
                            {
                                throw new ArgumentException("sheetName");
                            }
     
                            var cells = wsPart.Worksheet.Descendants<Cell>();
     
                            foreach (Cell cell in cells)
                            {
                                int j = Convert.ToInt32(GetColumnIndex(cell.CellReference.Value));
                                foreach (Column cellColumn in wsPart.Worksheet.Descendants<Column>())
                                {                             
                                    if (cellColumn.Min.Value == j)
                                    {
                                        var cellWidth = cellColumn.Width.Value;
                                        Console.WriteLine(cellWidth);
                                    }
     
                                }                      
                            }
                            Console.ReadKey();
                        }
                    }
                }
     


    Besides,

    I suggest you use Open XML SDK Tool to check the XML info for each node. And you could easily reflect code by comparing files.

    Click here to download Open XML SDK Tool.


    Monday, August 15, 2016 11:27 AM
    Moderator
  • Why do you do this comparison

    if (cellColumn.Min.Value == j)

    due to such change, it leads to problem in other thread.


    Many Thanks & Best Regards, Hua Min

    Wednesday, August 17, 2016 9:21 AM
  • Any advice?

    Many Thanks & Best Regards, Hua Min

    Thursday, August 18, 2016 2:08 AM
  • Hi,

    Please visit Column class.

    Min (Minimum Column) represents First column affected by this 'column info' record.

    Max (Maximum Column) represents Last column affected by this 'column info' record.

    E.g. After setting the width of column A&B as 10 and column E/F/G/H as 15, the XML code is  

    1 refers to column A, 2 refers to column B, 5 refers to column E and 8 refers to column H.

    For the issue you met, since I couldn’t reproduce your issue and the code works fine with my file, I suggest you focus on the new thread for the new issue.

    Thanks for your understanding.

    Regards,

    Celeste

    Thursday, August 18, 2016 6:44 AM
    Moderator
  • Hi,

    Since the original issue has been resolved and the issue is different from the original, I suggest you post new threads for new issues.

    There would be more community members to help you.

    Thanks for your understanding.

    Regards,

    Celeste

    Thursday, August 18, 2016 8:57 AM
    Moderator