Click here to Skip to main content
15,907,326 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
---i want the excel which i have generated----

-->i want the column of header to be in color
-->the columns are not wrapped



C#
public DataTable ConvertArraylistToDataSet(ArrayList arrayList)
   {

       DataTable dtEntityReturned = new DataTable();

       dtEntityReturned.Columns.Add("UserID", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("ScheduleDate", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("ShiftStartTime", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("ShiftEndTime", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("Creation_Date", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("Cab_Pick", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("Cab_Drop", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("IsWeekend", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("IsClientHoliday", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("Remarks", System.Type.GetType("System.String"));
       dtEntityReturned.Columns.Add("Shift_Type", System.Type.GetType("System.String"));


       foreach (TMS.API.Schedule.VO_Schedule_SR objreqSR in arrayList)
       {
           DataRow myRow = dtEntityReturned.NewRow();

           myRow["UserID"] = objreqSR.UserID;
           myRow["ScheduleDate"] = objreqSR.ScheduleDate;
           myRow["ShiftStartTime"] = objreqSR.ShiftStartTime;
           myRow["ScheduleDate"] = objreqSR.ScheduleDate;
           myRow["Creation_Date"] = objreqSR.Creation_Date;
           myRow["Cab_Pick"] = objreqSR.Cab_Pick;
           myRow["Cab_Drop"] = objreqSR.Cab_Drop;
           myRow["IsWeekend"] = objreqSR.IsWeekend;
           myRow["Remarks"] = objreqSR.Remarks;
           myRow["Shift_Type"] = objreqSR.Shift_Type;


           dtEntityReturned.Rows.Add(myRow);
       }
       return dtEntityReturned;
   }

----------------Here the datatable are added in the sheet------------------------------------------i need to change here ---how to do it ?

-->i want the column of header to be in color
-->the columns values should be wrapped...but its not coming in wrapped..i have to expand each column to read the things in each column.....


C#
    protected void ExportToExcel_Click(object sender, EventArgs e)
    {

        TMS.API.Schedule.VO_Schedule_SC obj_Schedule = new TMS.API.Schedule.VO_Schedule_SC();
        obj_Schedule.StartDate = DateTime.Parse(txt_From_date.Text).ToString("MM/dd/yyyy HH:mm:ss");
        obj_Schedule.EndDate = DateTime.Parse(txt_To_date.Text).ToString("MM/dd/yyyy HH:mm:ss");
        obj_Schedule.Hub_ID = ddl_Hub.SelectedValue;
        obj_Schedule.UserID = string.IsNullOrEmpty(txtEmployeeID.Text) ? string.Empty : txtEmployeeID.Text;
        obj_Schedule.fullrecords="1";
        INT_Schedule obj_Int_Schedule = new INT_Schedule();
        ArrayList dsRecords = obj_Int_Schedule.GetAllImportedUser_Records(obj_Schedule, PageToDisplay, PageSize);
        DataTable dsEntityReturned = ConvertArraylistToDataSet(dsRecords);
        DataSet ds =new DataSet();
        
          if (isExecuteReportGeneration(dsEntityReturned))
            {

                string attachment = "attachment; filename=Importeduserschedule.xls";
                Response.ClearContent();
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/vnd.ms-excel";

                DataTable dt = null;
                dt = dsEntityReturned;
                string tab = "";
                
                foreach (DataColumn dc in dt.Columns)
                {

                    Response.Write(tab + dc.ColumnName);

                     tab = "\t";
                }
                Response.Write("\n");


                

                int i;
                foreach (DataRow dr in dt.Rows)
                {
                    tab = "";
                    for (i = 0; i < dt.Columns.Count; i++)
                    {
                        Response.Write(tab + dr[i].ToString());
                        tab = "\t";
                    }
                    Response.Write("\n");
                }

                //HttpContext.Current.ApplicationInstance.CompleteRequest();
                Response.End();
            }

            else
            {
                ShowMessageBox("No Record(s) Found.");
            }



        }

protected bool isExecuteReportGeneration( DataTable dsResult)
    {
        bool result = true;
        try
        {


            if (dsResult == null)
            {
                result = false;
            }

            if (dsResult.Rows.Count == 0)
            {
                result = false;
            }

        }

        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
        }
        return result;

    }
Posted
Updated 4-Jul-13 0:53am
v2

1 solution

---------------i go the solution-----------------------------------

protected void ExportToExcel_Click(object sender, EventArgs e)
{
HttpContext.Current.Response.Write("");

TMS.API.Schedule.VO_Schedule_SC obj_Schedule = new TMS.API.Schedule.VO_Schedule_SC();
obj_Schedule.StartDate = DateTime.Parse(txt_From_date.Text).ToString("MM/dd/yyyy HH:mm:ss");
obj_Schedule.EndDate = DateTime.Parse(txt_To_date.Text).ToString("MM/dd/yyyy HH:mm:ss");
obj_Schedule.Hub_ID = ddl_Hub.SelectedValue;
obj_Schedule.UserID = string.IsNullOrEmpty(txtEmployeeID.Text) ? string.Empty : txtEmployeeID.Text;
obj_Schedule.fullrecords="1";
INT_Schedule obj_Int_Schedule = new INT_Schedule();
ArrayList dsRecords = obj_Int_Schedule.GetAllImportedUser_Records(obj_Schedule, PageToDisplay, PageSize);
DataTable dsEntityReturned = ConvertArraylistToDataSet(dsRecords);
DataSet ds =new DataSet();

if (isExecuteReportGeneration(dsEntityReturned))
{

string attachment = "attachment; filename=Importeduserschedule.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";

DataTable dt = null;
dt = dsEntityReturned;
string tab = "";


HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.Write("
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900