Solved

Problem to open Excel file

Posted on 2016-11-16
15
128 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 50

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 50

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 50

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 50

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 50

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 50

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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 be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

813 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

12 Experts available now in Live!

Get 1:1 Help Now