Click here to Skip to main content
15,891,375 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


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
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;

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