Solved

Change to event

Posted on 2016-10-06
1
113 Views
Last Modified: 2016-10-06
Hi,
To this event
        public static void CreateSpreadsheetWorkbook(string filepath,string SheetName0)
        {
                SpreadsheetDocument spreadsheetDocument;
                if (System.IO.File.Exists(filepath) == false)
                {
                    // Create a spreadsheet document by supplying the filepath.
                    // By default, AutoSave = true, Editable = true, and Type = xlsx.
                    spreadsheetDocument = SpreadsheetDocument.
                        Create(filepath, SpreadsheetDocumentType.Workbook);
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, false);
                }

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());

                // Add Sheets to the Workbook.
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = SheetName0
                };
                sheets.Append(sheet);

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();
        }

Open in new window

I only want to append SheetName0 as a new worksheet to the Workbook, if SheetName0 does not exist within the workbook. And no change is applied to workbook, if SheetName0 is already existing inside the workbook. What to adjust?
0
Comment
Question by:HuaMinChen
1 Comment
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41832971
i build this as a function instead, try this:
public static Boolean CreateSpreadsheetWorkbook(string filepath, string SheetName0)
        {
            SpreadsheetDocument spreadsheetDocument = null;
            WorkbookPart workbookpart = null;
            WorksheetPart worksheetPart = null;
            Sheet sh = null;

            if (System.IO.File.Exists(filepath) == false)
            {
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                
                // Add a WorkbookPart to the document.
                workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
            }
            else
            {
                spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                workbookpart = spreadsheetDocument.WorkbookPart;
            }

            // Add a WorksheetPart to the WorkbookPart.
            worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            try
            {
                sh = workbookpart.Workbook.Sheets.Elements<Sheet>().FirstOrDefault(s => s.Name.HasValue && s.Name.Value == SheetName0);
            }
            catch (Exception) { }

            if (sh == null)
            {
                Sheets sheets = workbookpart.Workbook.GetFirstChild<Sheets>();

                // Add Sheets to the Workbook.
                if (sheets == null)
                {
                    sheets = workbookpart.Workbook.AppendChild<Sheets>(new Sheets());
                }

                String relationshipId = workbookpart.GetIdOfPart(worksheetPart);

                // Get a unique ID for the new worksheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                // Give the new worksheet a name.
                String sheetName = SheetName0;

                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();

                return true;
            }
            else
            {
                return false;
            }
        }

Open in new window

call it like:
Boolean b = CreateSpreadsheetWorkbook(@"D:\yourPath\test.xlsx", "worksheetName");

Open in new window

1

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to refer to value 8 50
Fulfillment API php code sample 1 41
Getting error in connectionstring with Excel. 30 35
parsing JSON help 1 10
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
The viewer will learn how to implement Singleton Design Pattern in Java.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question