Click here to Skip to main content
15,792,340 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I asked a question earlier on how to export a winform chart in C# to excel. I got some great suggestions and managed to do so.

private void exportToExcel(string mR, string mM, string mD, string mS, Color mK, string mC)
    string data = null;
    int i = 0;
    int j = 0;

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkSheet.Columns.ColumnWidth = 18;

    SqlDataAdapter daa = new SqlDataAdapter
    ("SELECT [dt], [cons] FROM [dbo].[cons] WHERE "
        + "[dt] > '" + new DateTime(Convert.ToInt16(mR), Convert.ToInt16(mM), Convert.ToInt16(mD), 0, 0, 0).ToString()
        + "' AND [dt] <= '" + new DateTime(Convert.ToInt16(mR), Convert.ToInt16(mM), Convert.ToInt16(mD), 23, 59, 59).ToString()
        + "' AND [ship_id] = " + mS.Substring(0, mS.IndexOf(" "))
        + " ORDER BY [DT]"
        , sqlc);

    DataSet dsa = new DataSet();

    for (i = 0; i <= dsa.Tables[0].Rows.Count - 1; i++)
        for (j = 0; j <= dsa.Tables[0].Columns.Count - 1; j++)
            data = dsa.Tables[0].Rows[i].ItemArray[j].ToString();
            xlWorkSheet.Cells[i + 2, j + 1] = data;

    Excel.Range chartRange;

    Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
    Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(30, 110, 500, 300);
    Excel.Chart chartPage = myChart.Chart;

    chartRange = xlWorkSheet.get_Range("A2", "B2").EntireColumn;
    //chartRange = xlWorkSheet.get_Range("A1");
    //chartRange.EntireColumn.NumberFormat = " hh:mm";
    xlWorkSheet.Cells[1, 1] = "Tid";
    xlWorkSheet.Cells[1, 2] = "Forbruk liter";
    chartPage.SetSourceData(chartRange, misValue);
    chartPage.ChartType = Excel.XlChartType.xlLine;
    chartPage.ChartTitle.Text = mR + mM + mD + mS;

    xlWorkBook.SaveAs("hallo.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);

    MessageBox.Show("Excel file created , you can find the file c:\\hallo.xls");

private void releaseObject(object obj)
        obj = null;

    catch (Exception ex)
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());


So this is how it looks now. It works, but I have a few issues with the code, and the result.
1. I have to manually change the number format in the columns. I tried this:
//chartRange.EntireColumn.NumberFormat = " hh:mm";
but it doesn't help much. I would like all rows from row 2 to row * in column A to be in my chosen format.

2. I want a button on the form so that I can choose whenever I want to export it to Excel. Right now I call on the method inside the:
public Chart(string mR, string mM, string mD, string mS, Color mK, string mC)
When I tried to call on it inside the button event:
private void exportButton_Click(object sender, EventArgs e)
    exportToExcel(mR, mM, mD, mS, mK, mC);

I just got an error: The name mR, mM, mD, mS, mK, mC does not exist in the current context.

How do I fix this?

Also I would like a saveFileDialog so that I can browse and edit filename.

Thank you :)
Updated 11-Nov-13 23:55pm
Richard MacCutchan 12-Nov-13 5:08am    
1. Use a range.
2. You cannot call a method with the proforma names of the parameters, you have to provide the actual values.
Handyman83 12-Nov-13 5:14am    
Yes, thank you. But the actual values are inherited from listbox and checkedlistbox in another form. ? How do I implement the Range ?

1 solution

I implemented the saveFileDialog like this :
SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel files (*.xls)|*.xls";
            sfd.FilterIndex = 0;
            sfd.RestoreDirectory = true;
            sfd.CreatePrompt = true;
            sfd.Title = "Export Excel File To";
            string name = sfd.FileName.ToString();
            string dir = sfd.InitialDirectory.ToString();
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