Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Feb 13, 2015 7:51 pm

How do I save an existing spreadsheet and close it.
The error is
The process cannot access the file 'C:\Users\rstruck\Desktop\TEST_LargeFile_DEVAPI.xlsx' because it is being used by another process.

Here is my code.
What I am doing is loading the spreadsheet, creating a data table from the column headers in the spreadsheet.
I am adding three more rows to hold a value if there is color formatting for some cells.
Then I need to loop through all the rows. If the first three columns for that row in the spreadsheet have color then I fill in the last three rows with a value.
Later on these rows will be skipped for processing.
Now I want to close the spreadsheet. ( This is where the error happens). If I do not try to close the spreadsheet then everything is good.
Then do a bunch of validation and then processing of the data then open the spreadsheet back up and add a color to any cell that does not match the values in a SQL database.

Code: Select all
        If (File.Exists(txtFile.Text)) = True Then
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile(txtFile.Text)
            Dim sheet As Worksheet = workbook.Worksheets(0)

            For column As Integer = 1 To sheet.LastColumn
                dtimport.Columns.Add(sheet.Range(1, column).Value)
            Next
                                               
            dtimport.Columns.Add("FirstRowFormatted")
            dtimport.Columns.Add("SecondRowFormatted")
            dtimport.Columns.Add("thirdRowFormatted")

            Dim dtcolumn As Integer
            Dim backgroundcolor As String
            backgroundcolor = "-16777216"
            Dim cellcolor As String
            Dim cellcolorb As String
            For row As Integer = 2 To sheet.LastRow
                Dim data() As Object
                [Array].Resize(data, sheet.LastColumn + 3)
                For column As Integer = 0 To sheet.LastColumn
                    dtcolumn = column + 1
                    data(column) = sheet.Range(row, dtcolumn).Value
                Next
                dtimport.Rows.Add(data)
            Next

           workbook.SaveToFile(txtFile.Text)
 

rstruck1
 
Posts: 4
Joined: Mon Feb 09, 2015 5:31 pm

Mon Feb 16, 2015 6:12 am

Hello,

Thanks for your inquiry. How did you close the spreadsheet? Would you please share the code?

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Feb 16, 2015 4:09 pm

Hi Gary, I posted my code and I do not have a "CLOSE" for the spreadsheet. I looked through your examples and never saw a close so I thought that the SaveToFile also did a close in the background.So let me get rid of everything. Here is some code that will use the "Open file dialog " so the user can select the excel spreadsheet that they want to open. I Load the file then I want to save the file.
I seached the tutorials for a close method but nothing came up.
Code: Select all
        OpenFileDialog1.Title = "Please Select a File"
        OpenFileDialog1.InitialDirectory = "C:temp"
        OpenFileDialog1.ShowDialog()
       
            Dim workbook As Workbook = New Workbook
            workbook.Version = ExcelVersion.Version2010
            workbook.LoadFromFile(txtFile.Text)
            Dim sheet As Worksheet = workbook.Worksheets(0)
           workbook.SaveToFile(txtFile.Text)


rstruck1
 
Posts: 4
Joined: Mon Feb 09, 2015 5:31 pm

Mon Feb 16, 2015 10:38 pm

I found out the issue. when I used the OpenFileDialog control it was holding a reference to the file. After I had the file name and path I reset the OpenFileDialog and now everything works.
We can marked this posts as answered.

rstruck1
 
Posts: 4
Joined: Mon Feb 09, 2015 5:31 pm

Tue Feb 17, 2015 1:38 am

Hello,

Thanks for your response, glad to hear that you have managed to resolve the issue.
Please contact us if you have any questions or needs.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Jan 27, 2016 11:10 pm

Is there a fix for this?
I'm having the same issue:

Code: Select all
   
        string file = @"C:\Test.xlsx";
   Spire.Xls.Workbook _WorkBook = new Spire.Xls.Workbook();
   _WorkBook.LoadFromFile(file);
   Spire.Xls.Worksheet _WorkSheet = _WorkBook.Worksheets[0];
   _WorkSheet.Range[3, 5].Text = "54321";
   _WorkBook.Save();

mongoose
 
Posts: 11
Joined: Wed Jan 13, 2016 5:07 pm

Thu Jan 28, 2016 2:15 am

Hi,

Thanks for your posting.
Please use SaveToFile(string fileName, ExcelVersion version) method to save excel file, fox example,
Code: Select all
book.SaveToFile(output,ExcelVersion.Version2010);


Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Fri Jan 29, 2016 8:17 am

Hi,

Has your issue been resolved?
Thanks for your feedback.

Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Fri Jan 29, 2016 12:03 pm

Hi Amy

Unfortunately I'm still having the issue. I have resorted to the following:

Code: Select all
   
bool savefile = false;
   do
   {
      try
      {
         WorkBook.SaveToFile(NewFile,Spire.Xls.ExcelVersion.Version2007);
         savefile = true;
      }
      catch (Exception ex)
      {
         savefile = false;
      }
   } while (savefile == false);

mongoose
 
Posts: 11
Joined: Wed Jan 13, 2016 5:07 pm

Mon Feb 01, 2016 1:57 am

Hi,

Thanks for your feedback.
I am glad that your issue has been resolved. Welcome to write to us again for further problems.

Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Return to Spire.XLS