Click here to Skip to main content
Click here to Skip to main content

Uploading Zip File in ASP.NET

, 4 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Uploading Zip File in ASP.NET
In this article, we will see how to upload the zip file which only contains a number of Excel Sheets.
 
This file we uploaded and saved in server. After that, we extracted that file on server side and reading all Excel sheets, we stored in the SQL Database. The Data Of Excesheet gets saved in the database.
 
The main thing Is: ICSharpCode.SharpZipLib.dll

Step 1

Create A one Default Page Which Contain One Uploader and SubmitButton

Step 2

C# code on Submit Button.
protected void button_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Class"].ConnectionString)) //SQl Server ConnectionString
    {
    if (FileUpload1.HasFile)
    {
    //SqlDataSource1.Insert();
    string filename = Path.GetFileName(FileUpload1.FileName);
    string fullpath = Server.MapPath("~/FileUpload/ZipFile/") + filename; //Zip File Save On ServerSide.
    if (File.Exists(fullpath))
    {
        File.Delete(fullpath);
    }
    FileUpload1.SaveAs(fullpath);
    ArrayList zippedList = UnZipFile(fullpath);  //method for Extracted the Zip File.

    foreach (string filepath in zippedList)
    {
        DataTable SheetNames = GetExcelSheetNames(filepath);   //Get All SheetName contain By individual ExcelSheet.

        foreach (DataRow dr in SheetNames.Rows)
        {
        string strFileType = System.IO.Path.GetExtension(filepath.ToLower());
        string sSourceConstr = String.Empty;
 
        if (strFileType.Trim() == ".xls")
        {
        sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + filepath + "; Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=2\"";
        }
        else if (strFileType.Trim() == ".xlsx")
        {
        sSourceConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + filepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
        }
 
        string sDestConstr = ConfigurationManager.ConnectionStrings["Class"].ConnectionString;
        OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
        using (sSourceConnection)
        {
        sSourceConnection.Open();
 
        string sql = "select * from [" + dr["SheetName"].ToString() + "]"; //"select * from $",sheet
        OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
 
        OleDbDataAdapter da = new OleDbDataAdapter();
        da = new OleDbDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables.Count > 0)
        {
           //here Your Code To Save in The Database.
				//i hope that you know that. the data of excel Sheet stored on database.
        }
        }
        }
        }
    }
    }
    }
}

Step 3

UnZipFile(fullpath); Method
 
We used this method to extract the zip file.
 
C# code is:
  private ArrayList UnZipFile(string fullpath)
    {
    ArrayList pathList = new ArrayList(); //contain the number of Excel file.
    try
    {
    if (File.Exists(fullpath))
    {
    string baseDirectory = Path.GetDirectoryName(fullpath);
 
    using (ZipInputStream ZipStream = new ZipInputStream(File.OpenRead(fullpath)))
    {
        ZipEntry theEntry;
        while ((theEntry = ZipStream.GetNextEntry()) != null)
        {
        if (theEntry.IsFile)
        {
        if (theEntry.Name != "")
        {
        string strNewFile = @"" + baseDirectory + @"\Excel\" + theEntry.Name;
        if (File.Exists(strNewFile))
        {
        //continue;
        }
 
        using (FileStream streamWriter = File.Create(strNewFile))
        {
        pathList.Add(strNewFile);
        int size = 2048;
        byte[] data = new byte[2048];
        while (true)
        {
            size = ZipStream.Read(data, 0, data.Length);
            if (size > 0)
            streamWriter.Write(data, 0, size);
            else
            break;
        }
        streamWriter.Close();
        }
        }
        }
        else if (theEntry.IsDirectory)
        {
        string strNewDirectory = @"" + baseDirectory + @"\" + theEntry.Name;
        if (!Directory.Exists(strNewDirectory))
        {
        Directory.CreateDirectory(strNewDirectory);
        }
        }
        }
        ZipStream.Close();
    }
    }
    }
    catch (Exception ex)
    {
    throw ex;
    }
    return pathList;
    }

Step 4

Method for get ExcelSheet Name is:
GetExcelSheetNames(filepath);
C# Code For The GetExcelSheetNames Are:
private DataTable GetExcelSheetNames(string filepath)
    {
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;
    String[] excelSheets;
 
    try
    {
 
    string strFileType = System.IO.Path.GetExtension(filepath.ToLower());
    string connString = String.Empty;
    if (strFileType.Trim() == ".xls")
    {
    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + filepath + "; Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=2\"";
    }
    else if (strFileType.Trim() == ".xlsx")
    {
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + filepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
    }
 
    // Create connection object by using the preceding connection string. 
    objConn = new OleDbConnection(connString);
 
    // Open connection with the database. 
    objConn.Open();
 
    // Get the data table containg the schema guid. 
    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
    if (dt == null)
    {
    return null;
    }
 
    DataTable table = new DataTable();
    table.Columns.Add("SheetName");
 
    // Add the sheet name to the string array. 
    foreach (DataRow row in dt.Rows)
    {
    DataRow dr = table.NewRow();
    if (row["TABLE_NAME"].ToString().EndsWith("_"))
    {
        continue;
    }
    else
    {
        string name = row["TABLE_NAME"].ToString();
        dr["SheetName"] = name.Replace("'", "");
        table.Rows.Add(dr);
    }
    }
    //foreach (DataRow row in table.Rows)
    //{
    //    string row = row[SheetName].ToString();
    //}
    return table;
    }
 
    catch (Exception ex)
    {
    throw ex;
    }
    finally
    {
    // Clean up. 
    if (objConn != null)
    {
    objConn.Close();
    objConn.Dispose();
    }
    if (dt != null)
    {
    dt.Dispose();
    }
    }
    }
Exactly how this works:
 
Upload the zip file using the uploader. After uploading, it will first save on server side. After that, we will pass the whole path of that zip to unzip the file.

How UnzipMethod Work

For that, we have one arraylist, i.e., pathlist contains all Excel files which are in zip folder.

ZipInputStream

This is used to open and read the zip folder. Using this, we can get all files present in that zip file.
ZipEntry theEntry = ZipStream.GetNextEntry())
Get the file from the zip.
Then using FileStream streamWriter, we create the extracted file and store in pathlist and return the pathlist.

How GetSheetName Work

One Excel Sheeet contains a number of sheets. Now we want all sheets to read and then save on database. Therefore we used this method to get all sheetname from that particular Excel sheet.
 dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
This is used to get all sheet names.
 
After that, it is important that:
if (row["TABLE_NAME"].ToString().EndsWith("_"))
    {
        continue;
    }
    else
    {
        string name = row["TABLE_NAME"].ToString();
        dr["SheetName"] = name.Replace("'", "");
        table.Rows.Add(dr);
    }
This code is important because the Excel sheet name contains some garabage sheet name with ends with '_' operator. That's why we used the above code to remove that sheet name.
 
And finally, we get sheets one by one. We just read that sheet and save on database.
 
I hope that this article is useful for you!
 
If you have any problems regarding this article, please let me know and we will solve that, or if you see any changes in that, please tell me.
 
Thank and regards,
@ChetanV@

License

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

Share

About the Author

hi
Currently I am working on Asp.net and sql Server 2005
just having 1+ year experianced!

Comments and Discussions

 
Generalas per your suggestion i am trying to devlope this in ef 4.2... Pinmemberchetan virkar3-Feb-12 18:16 
Generalthank u for your comment. but now i done this a week ago tha... Pinmemberchetan virkar3-Feb-12 18:15 
Generalvery old technology could you maybe do it in ef 4.2 and asp.... PinmemberDean Oliver3-Feb-12 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150327.1 | Last Updated 4 Feb 2012
Article Copyright 2012 by chetan virkar
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid