Solved

Problem to open Excel file

Posted on 2016-11-16
15
44 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 49

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 49

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 18

Expert Comment

by:Pawan Kumar Khowal
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 49

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 49

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 49

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 49

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Image(7) 1 37
C# Error - Add Failed 12 52
Gantt chart 2 19
EXCEL formula help required 3 15
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now