Use C # To Operate Excel and generate charts

Introduction

Use a small example to demonstrate how to operate Excel in C #

Code

Using system; using system. collections. generic; using system. LINQ; using system. text; using Excel = Microsoft. office. interOP. excel; namespace testexcel {public class cbexcel {excel. application xlapp; // declare the exccel application variable excel. workbook xlworkbook; // declare the work book excel. worksheet xlworksheet; // declare the sheet object misvalue of Excel = system. reflection. missing. value; Public cbexcel () {}// set public void setdata (int I, Int J, string data) {xlworksheet. cells [I, j] = data;} // insert a chart to the Excel page. Public void setchart (string start, string end, Excel. xlcharttype) {excel. range chartrange; excel. chartobjects xlcharts = (Excel. chartobjects) xlworksheet. chartobjects (type. missing); // create a chart in Excel. chartobject MyChart = (Excel. chartobject) xlcharts. add (10, 80,300,250); // Add a chart and set the Excel size. chart chartpage = MyChart. chart; chartrange = xlworksheet. get_range (START, end); // set the start and end chartpage of the chart data. setsourcedata (chartrange, misvalue); chartpage. charttype = type; // set the chart style} // release the private void releaseobject (Object OBJ) {try {system. runtime. interopservices. marshal. releasecomobject (OBJ); OBJ = NULL;} catch {OBJ = NULL;} finally {GC. collect () ;}/// create an Excel public void create () {xlapp = new excel. applicationclass (); // create an Excel xlworkbook = xlapp. workbooks. add (misvalue); xlworksheet = (Excel. worksheet) xlworkbook. worksheets. get_item (1); // retrieve the page on the first page} // Save the Excel public void saveas () {// this way, the storage will jump out of another storage file window xlworkbook. close (true, misvalue, misvalue); xlapp. quit () ;}public void release () {// release the Excel instance releaseobject (xlworksheet), releaseobject (xlworkbook), and releaseobject (xlapp) used );}}}

This is a simple encapsulated class that encapsulates some operations in a class for ease of use.

Code used

Private void button#click (Object sender, eventargs e) {cbexcel Excel = new cbexcel (); // create a cbexcel-type Excel file. create (); // create an Excel file. setdata (1, 1, ""); // set data 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); // set the chart style and start and end position in Excel. saveas (); // save excel. release (); // releases memory}

Excel output result

Download example

Http://download.csdn.net/detail/cloudhsu/5072683

New

Code that supports exporting Excel and CSV data

Some computers in the company may not install Excel in actual requirements. In order to make the export work Work work properly, I made the following corrections:

Interface: iexcel for abstraction

interface IExcel{    void Create();    void SetData(int i, int j, string data);    void SaveAs();    void Release();}

In cbexcel, The iexcel interface is used.

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

An additional cbexcelcsv

I use the lazy practices to directly open a large enough memory to store data.

public class CBExcelCSV : IExcel{    List<string[]> m_data = new List<string[]>();    const int MAX_COLUMN = 150;    const int MAX_LINE = 2000;    int m_MaxLine;    int m_MaxColumn;    #region IExcel Members    public void Create()    {        m_MaxColumn = -1;        m_MaxLine = MAX_LINE;        for (int i = 0; i < MAX_LINE; i++)        {            m_data.Add(new string[MAX_COLUMN]);        }    }    public void SetData(int i, int j, string data)    {        if (i-1 >= m_MaxLine)        {            m_data.Add(new string[MAX_COLUMN]);            m_MaxLine++;        }        if (m_MaxColumn < j)        {            m_MaxColumn = j;        }        m_data[i-1][j-1] = data;    }    public void SaveAs()    {        StringBuilder sb = new StringBuilder();        for (int i = 0; i < m_data.Count; i++ )        {            sb.Append(m_data[i][0]);            for (int j = 1; j < m_MaxColumn; j++)            {                sb.Append(",");                sb.Append(m_data[i][j]);            }            sb.Append("\r\n");        }        save(sb.ToString());    }    void save(string data)    {        SaveFileDialog saveFileDialog1 = new SaveFileDialog();        saveFileDialog1.Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*";        saveFileDialog1.FilterIndex = 1;        saveFileDialog1.RestoreDirectory = true;        if (saveFileDialog1.ShowDialog() == DialogResult.OK)        {            string fileName = saveFileDialog1.FileName;            File.WriteAllText(fileName, data);        }    }    public void Release()    {    }    #endregion}

Another example is cbexcelwrapper, which contains an iexcel member and submits all the executions to the iexcel instance for execution.

Public class cbexcelwrapper: iexcel {iexcel Excel; bool m_isexcelinstalled; Public cbexcelwrapper () {m_isexcelinstalled = checkexcelinstalled (); // use the cbexcel class when Excel is installed, if (m_isexcelinstalled) {Excel = new cbexcel ();} else {Excel = new cbexcelcsv () ;}} bool checkexcelinstalled () {// The focus of this Code Section is to determine whether Excel bool installed = false is installed or not; Type officetype = type. gettypefromprogid ("Excel . Application "); If (officetype! = NULL) {installed = true;} return installed;} # region iexcel members public void create () {excel. create ();} public void saveas () {excel. saveas ();} public void release () {excel. release ();} public void setdata (int I, Int J, string data) {excel. setdata (I, j, data) ;}# endregion}