C # Excel writes data and graphs

Development tools: VS2017

Language: C

dotnet version:. Net FrameWork 4.0 and above

DLL tool name used: GemBox.Spreadsheet.dll (version: 37.3.30.1185)

First, Gembox.spreadsheet tools:

The DLL is an Excel-based development tool developed by Gembox company, which is lightweight and easy to use, and is recommended here for use.

：

Https://pan.baidu.com/s/1slcBUqh

This article is about writing to Excel using this tool.

Second, create Excel

In order to be able to use the DLL, the following code must be written before the call:

Spreadsheetinfo.setlicense ("Free-limited-key");

Create the Excel file as follows:

Excelfile Excel = new Excelfile ();

This is just to create an Excel, which represents the entire Excel file, and the code to save the file is as follows:

Excel. Save ("File path");

Third, add some properties to Excel

We can add some content such as document title, author, company and comment to Excel, and the code to implement the content is as follows:

Excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.title, Title)); Excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.author, "Cnxy")); Excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.company, "Cnxy")); Excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.comments, "by Cnxy. Website:http://www.cnc6.cn "));

Four, to Excel default font

This is to set a uniform font for the entire Excel, the code is as follows:

Excel. Defaultfontname = "Times New Roman";

Five. Add a sheet table

You know, Excel is made up of sheet tables, so the code to add the sheet table is as follows:

Excelworksheet sheet = Excel. Worksheets.add ("table name");

Above, a data table named "Table name" has been added to Excel.

Six, add password protection to sheet

Sometimes, in order to protect their Excel is not tampered with, you need to set the sheet password, the code is as follows:

Sheet. Protectionsettings.setpassword ("Cnxy"); sheet. Protected = true;

Seven, so that the grid line is not visible

By default, sheet gridlines are visible, and sometimes we can set the gridlines to be invisible, with the following code:

Sheet. Viewoptions.showgridlines = false;

Eight, write cells

There are three ways to access the cells, three of which are as follows:

Sheet. cells["A1"]sheet. Cells[0,0]sheet. Rows[0]. Cells[0]

The above three methods can access the cell, but the following to write cells, in fact, the method is very simple, as follows:

Sheet. cells["A1"]. Value= Content

The reason for not having double quotes above is that the content is not necessarily a string, it could be a number, a date, and so on.

Nine, cell style settings

The cell settings need to use the CellStyle object with the following code:

CellStyle style = new CellStyle ();//sets the horizontal alignment mode style. HorizontalAlignment = horizontalalignmentstyle.center;//Sets the vertical alignment mode style. VerticalAlignment = verticalalignmentstyle.center;//Sets the font style. font.size = * PT; Pt=20style. Font.weight = Excelfont.boldweight;style. Font.Color = Color.blue;sheet. cells["A1"]. style = style;

Fill in the following ways:

Sheet. cells[24,1]. Style.FillPattern.PatternStyle = Fillpatternstyle.solid;          
Sheet. ROWS[24]. CELLS[1]. Style.FillPattern.PatternForegroundColor = Color.gainsboro;

Set the border as follows:

Style. Borders.setborders (Multipleborders.outside, Color.Black, Linestyle.thin);

Ten, Merge Cells

Merging cells requires the use of the CellRange object, which we can do from sheet. Cells.getsubrange or Getsubrangeabsolute, the code is as follows:

CellRange range = Sheet. Cells.getsubrange ("B2", "J3"); Range. Value = "Chart"; Range. merged = true;

Sheet. Cells.getsubrangeabsolute (24, 1, 24, 9). merged = true;

Xi. Creating chart Objects

Using the Linechart object, the code is as follows:

Linechart chart = (Linechart) sheet. Charts.add (Charttype.line, "B4", "J22");

This means creating a Linechart object from B4 to J22.

Set chart title is not visible, the code is as follows:

Chart. Title.isvisible = false;

Set the x-axis and y-axis headings to be visible, with the following code:

Chart. Axes.Horizontal.Title.Text = "Time"; chart. Axes.Vertical.Title.Text = "Voltage";

12. Set properties for y-axis

The main use of the chart. Axes.verticalvalue returns the Valueaxis object with the following code:

Valueaxis Axisy = chart. axes.verticalvalue;//y axis max scale with minimum scale axisy.minimum = -100;axisy.maximum = 100;//y Axis main with minor unit size axisy.majorunit = 20; Axisy.minorunit = 10;//y Axis primary and secondary mesh visible axisY.MajorGridlines.IsVisible = true;axisy.minorgridlines.isvisible = true;// Y-Axis tick-mark type axisy.majortickmarktype = Tickmarktype.cross; Axisy.minortickmarktype = Tickmarktype.inside;

13. Attach the complete source code

Using gembox.spreadsheet;using gembox.spreadsheet.charts;using system;using system.collections.generic;using System.diagnostics;using system.drawing;namespace spreadsheetchartdemo{   class program    {        const int PT = 20;        const int LENGTH = 200;        const string Timesnewroman = "Times New Roman";        const string TITLE = "Spread Sheet Chart Demo";         static void Main (string[] args)        {           spreadsheetinfo.setlicense ("Free-limited-key");            excelfile Excel = new Excelfile ();            //excel default font Excel. Defaultfontname = Timesnewroman;            //excel document properties set Excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> (builtindocumeNtproperties.title, Title));            excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.author, "Cnxy"));            excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.company, "Cnxy"));            excel. DOCUMENTPROPERTIES.BUILTIN.ADD (New keyvaluepair<builtindocumentproperties, string> ( Builtindocumentproperties.comments, "by Cnxy. Website:http://www.cnc6.cn "));            //Create a new sheet table Excelworksheet sheet = Excel. Worksheets.add (TITLE);            //set up table protection sheet. Protectionsettings.setpassword ("Cnxy");            sheet. Protected = true;            //setting gridlines are not visible sheet. Viewoptions.showgridlines = false; &nbSp          //defines a range of cells for b2-g3 cellrange range = sheet. Cells.getsubrange ("B2", "J3");            range. Value = "Chart";            range. merged = true;            //define a cell style cellstyle style = new CellStyle ();            //set border            style. Borders.setborders (Multipleborders.outside, Color.Black, Linestyle.thin);            //Set the horizontal alignment mode style. HorizontalAlignment = Horizontalalignmentstyle.center;            //Set the vertical alignment mode style. VerticalAlignment = Verticalalignmentstyle.center;            //set font style. font.size = * PT;            style. Font.weight = Excelfont.boldweight;            style. Font.Color = Color.Blue;            range. style = style;            //Add chart Linechart chart = (Linechart) sheet. Charts.add (Charttype.line, "B4", "J22");            chart. Title.isvisible = false;            chart. Axes.Horizontal.Title.Text = "Time";            chart. Axes.Vertical.Title.Text = "Voltage";            valueaxis Axisy =  chart. Axes.verticalvalue;            //y axis max scale and minimum scale axisy.minimum =-100;            axisy.maximum = 100;            //y axis main with minor unit size axisy.majorunit = 20;            axisy.minorunit = 10;            //y axis primary and secondary grids are visible axisY.MajorGridlines.IsVisible = true;            axisy.minorgridlines. IsVisible = true;             //y axis tick line type axisy.majortickmarktype = Tickmarktype.cross;            axisy.minortickmarktype = tickmarktype.inside;            random random = new Random ();            double[] data = new Double[length];             for (int i=0;i< LENGTH; i++)            {              if (random. Next (0,100) >                    data[i] = random. Nextdouble () * 100;               else                   &NBSP ;d Ata[i] =-random. Nextdouble () * 100;           &NBSP,}            chart. Series.add ("Random", data);                  //trailer Information     Range = Sheet. Cells.getsubrange ("B23", "J24");            range. Value = $ "Write time:{datetime.now:yyyy-mm-dd HH:mm:ss} by Cnxy";            range. merged = true;           &NBSP;//B25 (three cell mode) sheet. cells["B25"]. Value = "http://www.cnc6.cn";            sheet. cells[24,1]. Style.FillPattern.PatternStyle = Fillpatternstyle.solid;            sheet. ROWS[24]. CELLS[1]. Style.FillPattern.PatternForegroundColor = Color.gainsboro;           &NBSP;//B25,J25 sheet. Cells.getsubrangeabsolute (24, 1, 24, 9). merged = true;            string filePath = [email protected] "{environment.currentdirectory}\ Sheetchart.xlsx ";             try            {               excel. Save (FilePath);                process.start (FilePath);                console.writeline ("Write successfully");           &NBSP,}            catch (Exception ex)     &NBS P      {               console.writeline (ex),       & nbsp   &NBSP,}            console.write ("Press any key to continue.");            console.readkey ();        }    }}

14. Generated Excel

Demo of Excel:

Https://pan.baidu.com/s/1slDPAED

XV, generated EXE

As follows:

Https://pan.baidu.com/s/1nvefYvJ

The results of the operation are as follows:

C # Excel writes data and graphs