Click here to Skip to main content
15,906,816 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am trying to upload and parse an excel file to Sharepoint 2010 via a web part.

The code I have works when the excel file is stored on the server (ie, my user profile Desktop), but it fails when I try to upload the excel file via a user interface. When uploading it via the user interface, I am able to obtain the file name and a few other details, but suddenly, it FAILS always on the line above where it says 'connection.Open();'

What can I do for this to work?

This is part of the code I have:

C#
string fileToUpload = uploadExcelFile.FileName;
System.IO.Stream strm = uploadExcelFile.PostedFile.InputStream;
fileName = uploadExcelFile.FileName;

Boolean replaceExistingFiles = true;

byte[] byt = new byte[Convert.ToInt32(uploadExcelFile.PostedFile.ContentLength)];
strm.Read(byt, 0, Convert.ToInt32(uploadExcelFile.PostedFile.ContentLength));
strm.Close();


C#
SPSite localSite = SPContext.Current.Site;
            SPWeb localWeb = localSite.OpenWeb();
            SPFolder ExcelLibrary = localWeb.Folders["ExcelLib"];
            localWeb.AllowUnsafeUpdates = true;
            ExcelLibrary.Files.Add(System.IO.Path.GetFileName(uploadExcelFile.PostedFile.FileName), byt, replaceExistingFiles);
            ExcelLibrary.Update();




C#
string fileExtension = Path.GetExtension(fileName).ToUpper();
                        string connectionString = "";

                        if (fileExtension == ".XLS")
                        {
                            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
                        }
                        else if (fileExtension == ".XLSX")                           
                            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text;'";

            String[] excelSheets;

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open(); // CODE FAILS HERE

                DataTable dtExcelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

               
Thanks in advance!  I need to get this solved asap. :-(

What I have tried:

I have looked online tirelessly.  This code snippet (above) is well-known and can be found online, however, I wonder why it fails.
Posted
Updated 23-Nov-16 5:18am
v5
Comments
[no name] 22-Nov-16 11:53am    
"is well-known and can be found online", and an explanation for the exception message that you see on your screen that we can't see at all is also well known and can easily be found online.

1 solution

Based on your description, I'm guessing that the preceding code simply takes the path of the file from the file upload control, and doesn't attempt to save it anywhere on the server.

The path provided by the file upload control is the path of the file on the client. (Most browsers will even strip out the folder path, and simply send the file name.)

The file has not been saved in this path on the server. If it was, it would be a major security vulnerability, since attackers could overwrite any file on your server simply by manipulating the path.

Instead, you either need to use the SaveAs[^] method to save the file to a specific folder on the server, or use either the FileContent[^] or FileBytes[^] properties to access the raw content of the file.

C#
string directory = Path.GetTempPath();
string fileExtension = Path.GetExtension(uploadExcelFile.FileName);
string fileName = Path.Combine(directory, Guid.NewGuid().ToString("N") + fileExtension);

string connection;
if (string.Equals(fileExtension, ".xls", StringComparison.OrdinalIgnoreCase))
{
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
}
else if (string.Equals(fileExtension, ".xlsx", StringComparison.OrdinalIgnoreCase))
{
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text;'";
}
else
{
    throw new InvalidOperationException("Invalid file type");
}

uploadExcelFile.SaveAs(fileName);

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    ...
 
Share this answer
 
v2
Comments
TheAbominable 22-Nov-16 17:33pm    
Thank you, Richard. I have updated my question and you will notice that I included code related to the upload process. I have also tried (code not included on my question) uploading the file itself to the Sharepoint 2010 library and it works flawlessly, however, when it comes to running the section of the code that reads/parses the file, I do receive an error in the connection.open() line. So, the file uploaded to the Sharepiont library without issues, but when it comes to parsing it (in order to move its data to a Sharepoint List), it has issues.

Like I said, I have also made it simpler and saved the excel file locally on the server (the file is on myuser\Desktop), use exactly the same logic you see above, and the file is read thoroughly, parsed, and its data is moved to the Sharepiont 2010 List flawlessly. Any idea as to what may be the issue? I also tried uploading the file and use the SaveAs("C:\" + fileName), but this did not work.
Richard Deeming 23-Nov-16 8:43am    
As I suspected, you're taking the path of the file on the client, and trying to open that file on the server.

You need to save the uploaded file to a folder on the server, and then open it from that path.

Alternatively, use a library that lets you read an Excel file from a stream without saving it first - eg:
* EPPlus[^];
* ClosedXML[^];
* ExcelDataReader[^];
* The OpenXML SDK[^];
TheAbominable 23-Nov-16 11:22am    
Thank you, Richard.
I added even more code. As previously mentioned, when uploading any file from the user interface to the server, my code is able to save the file on a Sharepoint 2010 Library ["ExcelLib"] and the file saves successfully. However, when reading the same file that has been uploaded (for the purpose of parsing it and storing its data on a Sharepoint List), the code fails. Does it fail because I have not saved the file within the windows server? Is there any way to read the file directly from where it has been saved, the Sharepoint Library, instead?

Thank you.
Richard Deeming 23-Nov-16 11:43am    
You are trying to read the file from the file system. In order to do that, the file must exist in the file system.

You cannot simply open an OleDbConnection on the server pointing to the path of the file on the client! They are two completely separate computers, and do not have access to each others' file systems.

Either save the file on the server, or use a library which lets you read the file directly from the InputStream.
TheAbominable 23-Nov-16 12:17pm    
Thank you for the explanation!
I will try both approaches and see which one works best. The approaches I will follow are:

1) Save the file on the server (I imagine there is a way to delete the file at the end of the code AFTER having it read)

2) Use a library which lets me read the file directly from the InputStream.

I will update you with my progress.

Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900