Solved

Change to event

Posted on 2016-10-06
1
142 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 52

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

729 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