How to read excel File from ASHX page?

Question

Hi i have uploaded file to one amazon s3 server,how can i read excel file and want to send excel data to database. my code is

<script type="text/javascript">
         var obj = null;
         $(function () {
             $('#fileupload').fileupload({
                 replaceFileInput: false,
                 formData: function (form) {
                     return [{ name: "name1", value: "value1" }, { name: "name2", value: "value2"}];
             $('#btnGo').click(function () {
                 obj.submit();
             });
         });
     </script>

And my ashx page, where i need to read excel data

public class AjaxFileHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            var file = context.Request.Files[0];
            string fileName=fileName = Guid.NewGuid().ToString() + file.FileName;
            Stream streamContentFile = context.Request.Files[0].InputStream;

            var iFileSize = context.Request.Files[0].ContentLength;
            byte[] data = new byte[iFileSize];
            int bytes_read = 0;
            while (bytes_read < iFileSize)
            {
                int bytes_read_this_iteration = streamContentFile.Read(data, bytes_read, iFileSize - bytes_read);
            streamContentFile.Close();
            streamContentFile.Dispose();
            CommonBLL.UploadTemporaryFilesToS3Bucket(fileName, data);
//Here i need to read excel code can you provide how to do that pleas
    }

Emanuele Greco · Answer 1 · 2012-07-23T10:01:24.050

you need two things:

a Driver that allows code to read Excel content
access to this file
a query over excel data

In this sample:

I use ACE (Microsoft Access Database Engine) driver, that must be installed on the server
the file is in App_Data folder (in your case the file should be rached CommonBLL library, i suppose)

the query is an UPDATE query; you can replace with a SELECT or INSERT query, using common DB SNippets.

string fileName= Server.MapPath( "~/App_Data/MyFile.xls");
string sheetName= "Sheet1";
string connString = string.Format(
      "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'"
      , fileName);
string command = string.Format("UPDATE [{0}${1}:{1}] SET F1='{2}'", sheetName,cellName, cellValue);

   using (OleDbConnection oledbConn = new OleDbConnection(connString))
    {
        oledbConn.Open();
        using (OleDbCommand cmd = new OleDbCommand(command, oledbConn))
            cmd.ExecuteNonQuery();

        oledbConn.Close();
    }

I am getting error as "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data." — user1527989, Jul 23 '12 at 10:21
@user1527989 The Application needs write authorization to that file. Googling you can find many solutions. Anyway, if AmanzonSrBucket has too many permissions restrictions, you can edit the file in Application\App_Data folder an then send it to Amazon — Emanuele Greco, Jul 23 '12 at 10:34

score 0 · Answer 2 · answered Jul 23 '12 at 09:47

I would use open source libraries for excel, EPPlus (xslx) or NPOI (xls). They are very easy to use, and I am using EPPlus for numerous excel imports / exports and it's working great. These libraries have no external dependancies, and you can use it on server side.

How to read excel File from ASHX page?

2 Answers2