Uploading excel file (for parsing and moving data) to sharepoint 2010 list

Question

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 22-Nov-16 4:52am

TheAbominable

Updated 23-Nov-16 5:18am

v5

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2016-11-22T06:15:00

Solution 1

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))
{
    ...

Posted 22-Nov-16 6:15am

Richard Deeming

Updated 23-Nov-16 2:56am

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!