Peter Chan
asked on
Problem to open Excel file
Hi,
As I want to copy details from one given Excel file into New0.xlsx by these
But I've got 'File not found exception' due to this line
Any advice?
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);
...
But I've got 'File not found exception' due to this line
using (FileStream fs1 = new FileStream(Path0, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
Any advice?
make sure the value of Path0 returning a valid path that accessible?
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 !!
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 !!
a side note, you can also use System.IO.Path.Combine to generate your file path.
hence,
Path.Combine Method (String, String)
https://msdn.microsoft.com/en-us/library/fyy7a5kt(v=vs.110).aspx
hence,
string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");
Path.Combine Method (String, String)
https://msdn.microsoft.com/en-us/library/fyy7a5kt(v=vs.110).aspx
ASKER
Thanks all.
Ryan,
I put this line
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?
Ryan,
I put this line
string Path0 = System.IO.Path.Combine(Folder0, "New0.xlsx");
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?
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 ?
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 ?
ASKER
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);
...
but I now encounterDocumentFormat.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:
due to 4th using line in above. What to adjust?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciated you a lot.
Sorry, I get
within
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
due to linesheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheets>().Select(s => s.SheetId.Value).Max() + 1;
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;
}
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> 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
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:
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);
...
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();
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
string Path0 = Folder0 + "\New0.xlsx";