Uploading Zip File in ASP.NET

• Download sharpziplib

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@