Problem to open Excel file

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?
LVL 11
HuaMinChenSystem managerAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
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
 
nathanielIT ConsultantCommented:
on line #1 try taking out one backslash. like this

string Path0 = Folder0 + "\New0.xlsx";
0
 
Ryan ChongCommented:
make sure the value of Path0 returning a valid path that accessible?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Pawan KumarDatabase ExpertCommented:
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
 
Ryan ChongCommented:
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
 
HuaMinChenSystem managerAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
HuaMinChenSystem managerAuthor Commented:
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
 
Ryan ChongConnect With a Mentor Commented:
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
 
HuaMinChenSystem managerAuthor Commented:
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
 
Ryan ChongConnect With a Mentor Commented:
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
 
HuaMinChenSystem managerAuthor Commented:
>> 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
 
Ryan ChongCommented:
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
 
HuaMinChenSystem managerAuthor Commented:
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
 
HuaMinChenSystem managerAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.