Solved

Problem to open Excel file

Posted on 2016-11-16
15
89 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 23

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
 
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 23

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

943 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

5 Experts available now in Live!

Get 1:1 Help Now