interface IExcel
{
void Create();
void SetData(int i, int j, string data);
void SaveAs();
void Release();
}
public class CBExcel : IExcel
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
const string ChartStart = "A1";
string m_ChartEnd;
int m_MaxI;
int m_MaxJ;
public CBExcel()
{
m_ChartEnd = "A1";
m_MaxI = -1;
m_MaxJ = -1;
}
public void SetData(int i, int j, string data)
{
xlWorkSheet.Cells[i, j] = data;
CheckChartEnd(i, j);
}
private void CheckChartEnd(int i, int j)
{
if (m_MaxI <= i)
m_MaxI = i;
if (m_MaxJ <= j)
m_MaxJ = j;
const int a = 0x41;
int word = a + j - 1;
m_ChartEnd = string.Format("{0}{1}", Convert.ToChar(word), m_MaxI);
}
public void SetChart(Excel.XlChartType type)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(ChartStart, m_ChartEnd);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
public void SetChart(string start, string end, Excel.XlChartType type)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(start, end);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
public void Create()
{
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
public void SaveAs()
{
SetChart(Excel.XlChartType.xlLine);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
public void Release()
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
You can use this code to save excel.
private void button1_Click(object sender, EventArgs e)
{
CBExcel excel = new CBExcel();
excel.Create();
excel.SetData(1, 1, "");
excel.SetData(1, 2, "Student1");
excel.SetData(1, 3, "Student2");
excel.SetData(1, 4, "Student3");
excel.SetData(2, 1, "Term1");
excel.SetData(2, 2, "80");
excel.SetData(2, 3, "65");
excel.SetData(2, 4, "45");
excel.SetData(3, 1, "Term2");
excel.SetData(3, 2, "81");
excel.SetData(3, 3, "61");
excel.SetData(3, 4, "41");
excel.SetData(4, 1, "Term3");
excel.SetData(4, 2, "82");
excel.SetData(4, 3, "62");
excel.SetData(4, 4, "42");
excel.SetChart("A1", "D4", Excel.XlChartType.xlLine);
excel.SaveAs();
excel.Release();
}