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)
