Uploading Zip File in ASP.NET

Step 1

Create A one Default Page Which Contain One Uploader and SubmitButton

Step 2

C# code on Submit Button.

Collapse | Copy Code

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:

Collapse | Copy Code

  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:

Collapse | Copy Code

GetExcelSheetNames(filepath);

C# Code For The GetExcelSheetNames Are:

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

 dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

This is used to get all sheet names.

After that, it is important that:

Collapse | Copy Code

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.