Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I m creating web application in which I m create an excel sheet based on datatable and dataset. I m success in this task now my problem is that I want to display the simple chart in same excel file based on dataset or data which shows on excel?
I m providing my code please edit this.

Thankx in Advance.
C#
protected void btn_download_Click(object sender, EventArgs e)
    {
        from = (fromCalendar.SelectedDate);
        to = (ToCalendar.SelectedDate);
        client_id = Convert.ToInt32(drp_Client.SelectedValue);
        Compaign = drp_Compaign.SelectedValue;
        
        try
        {

            SqlCommand cmd_down_tw = new SqlCommand("proc_GetSemDataFromToDate", con);
            cmd_down_tw.CommandType = CommandType.StoredProcedure;

            cmd_down_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
            cmd_down_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
            cmd_down_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
            cmd_down_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

            SqlDataAdapter da_down_tw = new SqlDataAdapter(cmd_down_tw);
            da_down_tw.SelectCommand = cmd_down_tw;
            DataTable dt_down_tw = new DataTable();
            da_down_tw.Fill(dt_down_tw);

            SqlCommand cmd_comp_tw = new SqlCommand("proc_GetAllSemDataFromToDate", con);
            cmd_comp_tw.CommandType = CommandType.StoredProcedure;

            cmd_comp_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
            cmd_comp_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
            cmd_comp_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
            cmd_comp_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

            da_comp_tw = new SqlDataAdapter(cmd_comp_tw);
            da_comp_tw.SelectCommand = cmd_comp_tw;
            dt_comp_tw = new DataTable();
            da_comp_tw.Fill(dt_comp_tw);

            dt_down_tw.Merge(dt_comp_tw);

            //DataTable[] data_tables = new DataTable[2];
            //data_tables[0] = dt_comp_tw;
            //data_tables[1] = dt_down_tw;

            ExportDataSetToExcel(dt_down_tw, "Report.xls");
            dt_down_tw.Dispose();            
        }
        catch
        {

        }
    }
    public void ExportDataSetToExcel(DataTable dt, string filename)
    {
        HttpResponse response = HttpContext.Current.Response;

        // first let's clean up the response.object   
        response.Clear();
        response.Charset = "";

        // set the response mime type for excel   
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        // create a string writer   
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid   
                DataGrid dg = new DataGrid();
                dg.DataSource = dt;

                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }
    }
Posted

Look out this[^] example, this might be helpful for you..
 
Share this answer
 
Comments
faisal23 23-Nov-12 6:18am    
HI Krunal thank you for help but it is not work properly.
Can u tell me which microsoft excel library i have to insert.
I got the problem start from

workbook.LoadFromFile(@"..\..\ProductInfo.xlsx", ExcelVersion.Version2010);
Worksheet sheet1 = workbook.Worksheets[0];
Worksheet sheet2 = workbook.Worksheets[1];
[no name] 23-Nov-12 7:52am    
Try this: using Excel = Microsoft.Office.Interop.Excel;


Hi,
try out this option,


C#
 // use this reference for MS-Excel  
using Excel = Microsoft.Office.Interop.Excel;
//1.create the Excel Chart object
Excel.ChartObjects xlCharts = (Excel.ChartObjects)excelSheet.ChartObjects(Type.Missing);  
//2. Set the position of chart where you need to place inside the Excel sheet
 Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 5, 300, 250);                     
//3. create a new chart page to display your value
 Excel.Chart chartPage = myChart.Chart;
//4.Set the X & Y axis Range of data columns   
  //4.1 it takes Excel A Column as as X axis; Data value is from A20-A30
  //4.2 it takes Excel B Column as as Y axis; Data value is from A20-A30
Excel.Range chartRange; = excelSheet.get_Range("A20", "B30");
//5.Set the chart Source data from your chart range
chartPage.SetSourceData(chartRange, Type.Missing);
//6.select the chart type to render your data values
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
//7.If you need to declare the chart title please follow the two steps
myChart.Chart.HasTitle = true;
chartPage.ChartTitle.Text = "Column Chart";


--Sowraaj

 
Share this answer
 
Comments
faisal23 26-Nov-12 1:04am    
Hi Sowraaj it shows error in excelSheet .... does not exists in current contex
raghu teja 2-Apr-14 7:46am    
Hi soowraj by using the code i got an error saying that the "the name 'excelSheet' does not exist in the current context" can you please help me out with this
please do write a reply @ raghu.teja.alapati@gmail.com

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