public static System.IO.StringWriter CreateExcel_HTML(
DataTable Dt
, ClsExcel_Columns Columns)
{
System.IO.StringWriter Sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter Htw = new System.Web.UI.HtmlTextWriter(Sw);
System.Web.UI.WebControls.Table Tb = new System.Web.UI.WebControls.Table();
System.Web.UI.WebControls.TableRow Tbr_Header = new System.Web.UI.WebControls.TableRow();
foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
{
System.Web.UI.WebControls.TableCell Tbc = new System.Web.UI.WebControls.TableCell();
Tbc.Text = Obj.Value.FieldDesc;
Tbc.Width = Obj.Value.Width;
Tbr_Header.Cells.Add(Tbc);
}
Tb.Rows.Add(Tbr_Header);
foreach (DataRow Dr in Dt.Rows)
{
System.Web.UI.WebControls.TableRow Tbr = new System.Web.UI.WebControls.TableRow();
foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
{
System.Web.UI.WebControls.TableCell Tbc = new System.Web.UI.WebControls.TableCell();
Tbc.Text = Dr[Obj.Value.FieldName].ToString();
Tbr.Cells.Add(Tbc);
}
Tb.Rows.Add(Tbr);
}
Tb.RenderControl(Htw);
return Sw;
}
public static void NativeExcel_CreateExcel(
DataTable Dt
, ClsExcel_Columns Columns
, string SaveFileName = ""
, Excel.XlFileFormat FileFormat = Excel.XlFileFormat.xlExcel5
, string Title = "")
{
Excel.Application Obj_Excel = new Excel.Application();
Excel.Workbook owbook = Obj_Excel.Workbooks.Add();
Excel.Worksheet owsheet = owbook.Worksheets.Add();
Int32 RowCt = 2;
Int32 ColCt = 1;
foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
{
owsheet.Cells[RowCt, ColCt].Value = Obj.Value.FieldDesc;
owsheet.Cells[RowCt, ColCt].Font.Bold = true;
Excel.Range Inner_ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt)
+ (RowCt + Dt.Rows.Count).ToString()];
Inner_ExRange.NumberFormat = Obj.Value.NumberFormat;
ColCt++;
}
RowCt++;
ColCt = 1;
Excel.Range ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt + Columns.pObj.Count)
+ (RowCt + Dt.Rows.Count - 1).ToString()];
ExRange.Value = Do_Methods.ConvertDataTo2DimArray(Dt, Columns.pFieldName);
owsheet.Range["A1;IV65536"].AutoFit();
if (SaveFileName == "")
{ SaveFileName = "Excel_File"; }
owsheet.Range["A1:A1"].Select();
owbook.SaveAs(SaveFileName, FileFormat);
}
void ExportReport()
{
Connection_SqlServer Cn = new Connection_SqlServer();
try
{
Cn.Connect();
List<QueryParameter> List_Sp = new List<QueryParameter>();
List_Sp.Add(new QueryParameter("ExamID", Do_Methods.Convert_Int64(this.mObj_Exam.pDr_Exam["RecruitmentTestExamsID"])));
DataTable Dt = Cn.ExecuteQuery("usp_LoadExam_Detailed", List_Sp).Tables[0];
Layer01_Common.Objects.ClsExcel_Columns Col = new Layer01_Common.Objects.ClsExcel_Columns();
Col.Add("Desc");
Col.Add("Correct_Desc");
System.IO.StringWriter Sw = Methods_Excel.CreateExcel_HTML(Dt, Col);
this.Response.Clear();
this.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Report.xls"));
this.Response.ContentType = "application/ms-excel";
this.Response.ContentEncoding = System.Text.Encoding.Default;
this.Response.Charset = string.Empty;
this.Response.Write(Sw.ToString());
this.Response.End();
}
catch { }
finally
{ Cn.Close(); }
}
