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();
daa.Fill(dsa);
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;
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);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\hallo.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
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:
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 :)