Click here to Skip to main content
13,048,406 members (101,033 online)
Rate this:
 
Please Sign up or sign in to vote.
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.
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 22-Nov-12 20:08pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Look out this[^] example, this might be helpful for you..
  Permalink  
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];
Krunal R 23-Nov-12 7:52am
   
Try this: using Excel = Microsoft.Office.Interop.Excel;
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2


Hi,
try out this option,


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

  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 27 May 2015
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100