Using these dll
1. ClosedXML.dll
2. DocumentFormat.OpenXml
======================================================================
1st method btn click
======================================================================
protected void linkDonwloadDatabase_Click(object sender, EventArgs e)
{
DownloadDatabase("Supervisor", Convert.ToInt32(Session["SupervisorAccountId"]));
}
======================================================================
2nd method
======================================================================
public void DownloadDatabase(string AccountType, int AccountID)
{
DataSet ds = Get_ReportRecord(AccountType, AccountID);
if (ds != null && ds.Tables.Count > 0)
{
AccountType = AccountType.ToUpper();
if (AccountType == "SUPERADMIN")
{
ds.Tables[0].TableName = "CountryManager";
ds.Tables[1].TableName = "Ministry وزارة";
ds.Tables[2].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[3].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[4].TableName = "School مدرسة";
ds.Tables[5].TableName = "Supervisor مشرف";
ds.Tables[6].TableName = "Student طالب";
}
else if (AccountType == "COUNTRYMANAGER")
{
ds.Tables[0].TableName = "Ministry وزارة";
ds.Tables[1].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[2].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[3].TableName = "School مدرسة";
ds.Tables[4].TableName = "Supervisor مشرف";
ds.Tables[5].TableName = "Student طالب";
}
else if (AccountType == "MINISTRY")
{
ds.Tables[0].TableName = "MinistryAdmin منسق وزرارة";
ds.Tables[1].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[2].TableName = "School مدرسة";
ds.Tables[3].TableName = "Supervisor مشرف";
ds.Tables[4].TableName = "Student طالب";
}
else if (AccountType == "MINISTRYADMIN")
{
ds.Tables[0].TableName = "EduDistrict منطقة تعليمية";
ds.Tables[1].TableName = "School مدرسة";
ds.Tables[2].TableName = "Supervisor مشرف";
ds.Tables[3].TableName = "Student طالب";
}
else if (AccountType == "EDUCATIONALDISTRICT")
{
ds.Tables[0].TableName = "School مدرسة";
ds.Tables[1].TableName = "Supervisor مشرف";
ds.Tables[2].TableName = "Student طالب";
}
else if (AccountType == "SCHOOL")
{
ds.Tables[0].TableName = "Supervisor مشرف";
ds.Tables[1].TableName = "Student طالب";
}
else if (AccountType == "SUPERVISOR")
{
ds.Tables[0].TableName = "Student طالب";
}
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
//Add DataTable as Worksheet.
wb.Worksheets.Add(dt);
}
//Export the Excel file.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataBase_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + AccountType + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
}
}
========================================================================
3nd method
=======================================================================
public DataSet Get_ReportRecord(string AccountType, int globalid)
{
SqlParameter[] parameter = {
new SqlParameter("@Accounttype", AccountType),
new SqlParameter("@Downloadtypeid", globalid),
};
DataSet ds = SqlHelper.ExecuteDataSet("GetReportData", parameter);
return ds;
}
======================================================================
SqlHelper Class
======================================================================
public class SqlHelper
{
public static string connectionString = ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
public static DataTable ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(cmdText, connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(commandParameters);
using (var adapter = new SqlDataAdapter(command))
{
connection.Open();
DataTable dtResult = new DataTable();
adapter.Fill(dtResult);
return dtResult;
}
}
finally
{
connection.Close();
}
}
}
}
public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] commandParameters)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(cmdText, connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(commandParameters);
using (var adapter = new SqlDataAdapter(command))
{
connection.Open();
DataSet dsResult = new DataSet();
adapter.Fill(dsResult);
return dsResult;
}
}
finally
{
connection.Close();
}
}
}
}
public static DataTable ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(cmdText, connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(commandParameters);
connection.Open();
var dataReader = command.ExecuteReader();
DataTable dtResult = new DataTable();
dtResult.Load(dataReader);
return dtResult;
}
finally
{
connection.Close();
}
}
}
}
public static string ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(cmdText, connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(commandParameters);
connection.Open();
string result = (string)command.ExecuteScalar();
return result;
}
finally
{
connection.Close();
}
}
}
}
}
No comments:
Post a Comment