I like to suggest you to the alternate method of reading excel file from stream. Well in my case if I need to to read an Excel spreadsheet from the SharePoint site. Its looks as fi that is just the simple request at any time I required to open any on the Excel file, I just make use of the OleDb connection with the subsequent connection string:
Code:
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Persist Security Info=False;
Extended Properties=""Excel 12.0;HDR=YES""";
connectionString = string.Format(connectionString, filePath);
well certainly that you can’t open the file on the SharePoint site this way, that why you have to understand that how to download the file by the web site. The System.Net namespace give a exceptionally simple means to do this:
Code:
WebClient client = new WebClient();
client.UseDefaultCredentials = true;
Stream stream = client.OpenRead(url);
at the moment you might have the file as a stream, therefore you must should just extract it, save it to a temporary file, after that read it in the standard technique. That will solve the Problem. Now this next bit of code supposed to possibly be optimized to some extent that why the array is not resizing in every single loop
Code:
BinaryReader brdr = new BinaryReader(stream);
byte[] result = new byte[0];
int bufferSize = 32768; // 32k
byte[] buffer = new byte[bufferSize];
long pos = 0;
while (true)
{
buffer = brdr.ReadBytes(bufferSize);
if (pos > 0)
{
// copy old data to bigger result
byte[] temp = new byte[result.LongLength];
Array.Copy(result, temp, result.LongLength);
result = new byte[temp.LongLength + buffer.Length];
Array.Copy(temp, result, temp.LongLength);
// add new data
for (int i = 0; i < buffer.Length; i++)
{
result[pos + i] = buffer[i];
}
pos += buffer.Length;
}
else
{
result = new byte[buffer.Length];
Array.Copy(buffer, result, buffer.Length);
pos = buffer.Length;
}
if (buffer.Length < bufferSize)
break;
}
string tempFile = Path.Combine(Environment.GetEnvironmentVariable("TMP"),
"CopyList.xlsx");
using (var fs = new FileStream(tempFile, FileMode.OpenOrCreate))
{
var writer = new BinaryWriter(fs);
writer.Write(result, 0, result.Length);
writer.Close();
fs.Close();
}
Well this will be worked perfectly for you in many cases as it did for me on my development machine however this is part of a larger application that is influence a SharePoint site through the SharePoint API. consequently, it should have to be running form the and that should be 64-bit. On my first attempt of this , I established the error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" . that is why I just search a little around and got that the perhaps I required to install the driver. It is obtainable on the Microsoft as a separate download, and I thought that I will able to make it work although . I installed it, but I was still getting the similar error . after that I found out that there is no OleDb driver that is required for opening Excel files on a 64-bit machine. So because of that I had to Just accumulate the project as x86, . thought that would going to work but I was so l, wrong, in fact. When the project is compile on the 32-bit, I can't access the SharePoint sites. So I have to do more , to find a technique to read an Excel file straight from a stream. And the Actually, Microsoft have actually useful guide that shows that t how to do it. click here . that said that the t Excel's native format which is called the BIFF (Binary Interchange File Format). therefore, you just have to parse every the bytes from the stream on the correct format. So on my further search I found few code Excel Data Reader
C# code
Code:
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
VB.NET Code
Code:
Dim stream As FileStream = File.Open(filePath, FileMode.Open, FileAccess.Read)
'1. Reading from a binary Excel file ('97-2003 format; *.xls)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)
'...
'2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
'...
'3. DataSet - The result of each spreadsheet will be created in the result.Tables
Dim result As DataSet = excelReader.AsDataSet()
'...
'4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()
'5. Data Reader methods
While excelReader.Read()
'excelReader.GetInt32(0);
End While
'6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close()
this work just for the Excel 2003 format, now this issue can be easily solved Save the spreadsheet to 2003 format and that how you will be able to read it , there will be few error for the reason that stream object returned from the web download didn't support seeking and that is what this code depends on so just have to save the stream out to a temp file and just read it back as the FileStream object, that supports seeking, that its problems solve. This is the way that you be able to open the Excel file from a SharePoint site into a spreadsheet with no use of generic OleDb connection. Hope this help
Bookmarks