Solved

Problem to open Excel file

Posted on 2016-11-16
15
222 Views
Last Modified: 2016-11-17
Hi,
As I want to copy details from one given Excel file into New0.xlsx by these
                string Path0 = Folder0 + "\\New0.xlsx";
                string Col0 = "", Row0 = "";

                using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                    {
                        using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                        {
                            using (SpreadsheetDocument doc1 = SpreadsheetDocument.Open(fs1, true))
                            {
                                WorkbookPart workbookPart = doc.WorkbookPart;

                                /*Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                                    Where(s => s.Name == "Sheet Overall").FirstOrDefault();*/
                                Sheet theSheet;

                                int sheetIndex = 0;
                                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.Sheets;
                                //foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                                foreach (Sheet sheet in sheets)
                                {
                                    Boolean b = CreateSpreadsheetWorkbook(Path0, sheet.Name);
                                    ...

Open in new window


But I've got 'File not found exception' due to this line

using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))

Open in new window


Any advice?
0
Comment
Question by:HuaMinChen
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 6

Expert Comment

by:nathaniel
ID: 41890858
on line #1 try taking out one backslash. like this

string Path0 = Folder0 + "\New0.xlsx";
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41890862
make sure the value of Path0 returning a valid path that accessible?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41890881
Where you are setting value of Folder0 ?

Use below-
string Path0 = Folder0 + @"\New0.xlsx";

Also Print Value of Path0 before calling using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))

Hope it helps !!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41890886
a side note, you can also use System.IO.Path.Combine to generate your file path.

hence,
string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");

Open in new window


Path.Combine Method (String, String)
https://msdn.microsoft.com/en-us/library/fyy7a5kt(v=vs.110).aspx
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41890911
Thanks all.
Ryan,
I put this line

string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");

Open in new window


but I still get the same problem (as 3rd using line in above is leading to problem). How to adjust the codes, open New0.xlsx first?

But when writing into New0.xlsx, I want to use the same Sheet name (from the original Excel file). How to adjust the above?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41890919
try hard coding the value once and see  ...   @"C:\dp13\New0.xlsx"

Is this your file name New0.xlsx?

Do you have access on the folder in which this file is present ? Try it on some other drive and check ?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41890924
I adjust the codes to be

                string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(Path0, SpreadsheetDocumentType.Workbook);
                spreadsheetDocument.Close();
                string Col0 = "", Row0 = "";

                using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                    {
                        using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                        {
                            using (SpreadsheetDocument doc1 = SpreadsheetDocument.Open(fs1, true))
                            {
                                WorkbookPart workbookPart = doc.WorkbookPart;

                                /*Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                                    Where(s => s.Name == "Sheet Overall").FirstOrDefault();*/
                                Sheet theSheet;

                                int sheetIndex = 0;
                                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.Sheets;
                                //foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                                foreach (Sheet sheet in sheets)
                                {
                                    Boolean b = CreateSpreadsheetWorkbook(Path0, sheet.Name);
                                    ...

Open in new window

but I now encounter
DocumentFormat.OpenXml.Packaging.OpenXmlPackageException was unhandled
  HResult=-2146233088
  Message=The specified package is invalid. The main part is missing.
  Source=DocumentFormat.OpenXml
  StackTrace:
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(Stream stream, Boolean readWriteMode)
       at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(Stream stream, Boolean isEditable, OpenSettings openSettings)
       at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(Stream stream, Boolean isEditable)
       at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\dp13\General Application\WindowsFormsApplication9_4_0_3\WindowsFormsApplication2\Form1.cs:line 102
       at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.MenuItem.MenuItemData.Execute()
       at System.Windows.Forms.Command.Invoke()
       at System.Windows.Forms.Command.DispatchID(Int32 id)
       at System.Windows.Forms.Control.WmCommand(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at WindowsFormsApplication2.Program.Main() in c:\dp13\General Application\WindowsFormsApplication9_4_0_3\WindowsFormsApplication2\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window

due to 4th using line in above. What to adjust?
0
 
LVL 51

Assisted Solution

by:Ryan Chong
Ryan Chong earned 500 total points
ID: 41890929
back to your original question...

any reason you open the filestream for Path0 and then you save the Path0 again in CreateSpreadsheetWorkbook(Path0, sheet.Name) ?

it happens that I solved some of your previous questions before and this apparently works for me:
string Path0 = Path.Combine(Folder0 ,"New0.xlsx");
            string Col0 = "", Row0 = "";

            using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                {
                    WorkbookPart workbookPart = doc.WorkbookPart;

                    /*Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                        Where(s => s.Name == "Sheet Overall").FirstOrDefault();*/
                    Sheet theSheet;

                    int sheetIndex = 0;
                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.Sheets;
                    //foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                    foreach (Sheet sheet in sheets)
                    {
                        Boolean b = CreateSpreadsheetWorkbook(Path0, sheet.Name);
                    }

                }
            }

Open in new window

then with this function (modified to close the file if the function return false):
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
            {
                workbookpart.Workbook.Save();

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

                return false;
            }
        }

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41890943
Appreciated you a lot.
Sorry, I get

Error	8	'DocumentFormat.OpenXml.Spreadsheet.Sheets' does not contain a definition for 'SheetId' and no extension method 'SheetId' accepting a first argument of type 'DocumentFormat.OpenXml.Spreadsheet.Sheets' could be found (are you missing a using directive or an assembly reference?)	C:\dp13\General Application\WindowsFormsApplication9_4_0_3\WindowsFormsApplication2\Form1.cs	439	106	WindowsFormsApplication2

Open in new window

due to line
sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Select(s => s.SheetId.Value).Max() + 1;

Open in new window


within
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 DocumentFormat.OpenXml.Spreadsheet.Workbook();
            }
            else
            {
                spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                workbookpart = spreadsheetDocument.WorkbookPart;
            }

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

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

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

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

                String relationshipId = workbookpart.GetIdOfPart(worksheetPart);

                // Get a unique ID for the new worksheet.
                uint sheetId = 1;
                if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Count() > 0)
                {
                    sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().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
            {
                workbookpart.Workbook.Save();

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

                return false;
            }
        }

Open in new window

0
 
LVL 51

Assisted Solution

by:Ryan Chong
Ryan Chong earned 500 total points
ID: 41890964
try change:
sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Select(s => s.SheetId.Value).Max() + 1;
to:
sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
1
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41890985
>> any reason you open the filestream for Path0 and then you save the Path0 again in CreateSpreadsheetWorkbook(Path0, sheet.Name) ?
As I want to have the same Sheet name (copied from original Excel file), within New0.xlsx.

Sorry, how to resolve
DocumentFormat.OpenXml.Packaging.OpenXmlPackageException was unhandled
  HResult=-2146233088
  Message=The specified package is invalid. The main part is missing.
  Source=DocumentFormat.OpenXml
  StackTrace:
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(Stream stream, Boolean readWriteMode)
       at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(Stream stream, Boolean isEditable, OpenSettings openSettings)
       at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(Stream stream, Boolean isEditable)
       at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\dp13\General Application\WindowsFormsApplication9_4_0_4\WindowsFormsApplication2\Form1.cs:line 102
       at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.MenuItem.MenuItemData.Execute()
       at System.Windows.Forms.Command.Invoke()
       at System.Windows.Forms.Command.DispatchID(Int32 id)
       at System.Windows.Forms.Control.WmCommand(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at WindowsFormsApplication2.Program.Main() in c:\dp13\General Application\WindowsFormsApplication9_4_0_4\WindowsFormsApplication2\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window

due to 4th using line below
                string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(Path0, SpreadsheetDocumentType.Workbook);
                spreadsheetDocument.Close();
                string Col0 = "", Row0 = "";

                using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
                    {
                        using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                        {
                            using (SpreadsheetDocument doc1 = SpreadsheetDocument.Open(fs1, true))
                            {
                                WorkbookPart workbookPart = doc.WorkbookPart;

                                /*Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                                    Where(s => s.Name == "Sheet Overall").FirstOrDefault();*/
                                Sheet theSheet;

                                int sheetIndex = 0;
                                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.Sheets;
                                //foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)
                                foreach (Sheet sheet in sheets)
                                {
                                    Boolean b = CreateSpreadsheetWorkbook(Path0, sheet.Name);
                                    ...

Open in new window

0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41890997
The specified package is invalid. The main part is missing

Can i make assumption you're opening an Excel file which generated from these 2 lines of codes? These lines of codes are not enough to create a valid Excel file....
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(Path0, SpreadsheetDocumentType.Workbook);
                spreadsheetDocument.Close();

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41891010
Can i make assumption you're opening an Excel file which generated from these 2 lines of codes? These lines of codes are not enough to create a valid Excel file....
Yes. What to adjust?
0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41891025
Yes. What to adjust?
your existing function CreateSpreadsheetWorkbook will create a valid workbook with a worksheet.

The code I have posted in https://www.experts-exchange.com/questions/28983637/Problem-to-open-Excel-file.html#a41890929 should able to handle that. have you tested that?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 41891036
It is LATE to create Workbook till foreach group. This is WHY I try to create such Workbook, right before I START to read the original Excel file.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filter an Excel list by multiple criteria 6 39
Conditional fromatting formula 29 32
I NEED KEEP THE CONDITIONAL  FORMATTING 24 28
Slicers by Groups in Excel 7 20
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 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