[C #] Use Gembox. SpreadSheet to write data and charts to Excel,

[C #] Use Gembox. SpreadSheet to write data and charts to Excel,

 

This article is an original article and the source code is the original code. For example, repost/copy the original code, please clearly mark the original name, author, and URL on the webpage/code. Thank you!

Development Tool: VS2017

Language: C #

DotNet version:. Net FrameWork 4.0 or later

Name of the used DLL tool: GemBox. Spreadsheet. dll (Version: 37.3.30.1185)

1. GemBox. Spreadsheet tool:

This DLL is a development tool developed by GemBox Based on Excel functions. It is very lightweight and easy to use. We recommend it here.

:

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

This article uses this tool to write data into Excel.

2. Create an Excel file

To use this DLL, you must write the following code before calling it:

SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

Create an Excel file as follows:

ExcelFile excel = new ExcelFile();

Here, only an excel file is created, representing the entire excel file. The code for saving the file is as follows:

Excel. Save ("file path ");

3. Add some attributes to Excel

We can add some content such as document title, author, company, and remarks to excel. The code for implementing 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"));

4. Default excel font

This is to set a unified font for the entire Excel file. The Code is as follows:

excel.DefaultFontName = "Times New Roman";

5. Add a Sheet

You must know that Excel is composed of Sheet tables. Therefore, the code for adding Sheet tables is as follows:

ExcelWorksheet sheet = excel. Worksheets. Add ("table name ");

Above, a data table named "table name" has been added to the excel file.

6. Add password protection to Sheet

Sometimes, to protect your Excel files from tampering, you need to set the Sheet password. The specific code is as follows:

sheet.ProtectionSettings.SetPassword("cnxy");sheet.Protected = true;

7. Make the gridlines invisible

By default, the Sheet gridlines are visible. Sometimes, you can set the gridlines to be invisible. The specific code is as follows:

sheet.ViewOptions.ShowGridLines = false;

8. Write Cells

There are three ways to access cells:

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

The preceding three methods can be used to access cells, but the method for writing data to cells is as follows:

Sheet. Cells ["A1"]. Value = content

The reason for the absence of double quotation marks is that the content is not necessarily a string, but may be numbers or dates.

9. Cell style settings

The CellStyle object must be used for cell settings. The Code is as follows:

CellStyle style = new CellStyle (); // you can specify the horizontal alignment mode. horizontalAlignment = HorizontalAlignmentStyle. center; // set the vertical alignment mode style. verticalAlignment = verticalignmentstyle. center; // set the font style. font. size = 22 * PT; // PT = 20style. font. weight = ExcelFont. boldWeight; style. font. color = Color. blue; sheet. cells ["A1"]. style = style;

The filling method is as follows:

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

10. Merge Cells

To merge Cells, use the CellRange object. We can obtain the CellRange object 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;

11. Create Chart objects

The LineChart object is used. The Code is as follows:

LineChart chart =(LineChart)sheet.Charts.Add(ChartType.Line,"B4","J22");

The above indicates creating a LineChart object from B4 to J22.

Set the chart title to invisible. The Code is as follows:

chart.Title.IsVisible = false;

Set the title of the x-axis and Y-axis to be visible. The Code is as follows:

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

12. Set attributes for the Y axis

The ValueAxis object returned by chart. Axes. VerticalValue is mainly used. The Code is as follows:

ValueAxis axisY = chart. axes. verticalValue; // The maximum and minimum scales of the Y axis axisY. minimum =-100; axisY. maximum = 100; // The main and secondary units of the Y axis, axisY. majorUnit = 20; axisY. minorUnit = 10; // whether the main and secondary grids of the Y axis are visible to axisY. majorGridlines. isVisible = true; axisY. minorGridlines. isVisible = true; // y axis dial type axisY. majorTickMarkType = TickMarkType. cross; axisY. minorTickMarkType = TickMarkType. inside;

13. complete source code is attached.

Using GemBox. spreadsheet; using GemBox. spreadsheet. charts; 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 attribute settings 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 Sheet sheet ExcelWorksheet = excel. worksheets. add (TITLE); // sets table protection sheet. protectionSettings. setPassword ("cnxy"); sheet. protected = true; // sets the grid lines to be invisible to sheet. viewOptions. showGridLines = false; // defines the cell range of a B2-G3 CellRange = sheet. cells. getSubrange ("B2", "J3"); range. value = "Chart"; range. merged = true; // defines a cell style CellStyle style = new CellStyle (); // sets the border style. borders. setBorders (MultipleBorders. outside, Color. black, LineStyle. thin); // sets the horizontal alignment mode style. horizontalAlignment = HorizontalAlignmentStyle. center; // set the vertical alignment mode style. verticalAlignment = verticalignmentstyle. center; // set the font style. font. size = 22 * PT; style. font. weight = ExcelFont. boldWeight; style. font. color = Color. blue; range. style = style; // Add the 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; // The maximum and minimum scales of the Y axis axisY. minimum =-100; axisY. maximum = 100; // The main and secondary units of the Y axis, axisY. majorUnit = 20; axisY. minorUnit = 10; // whether the main and secondary grids of the Y axis are visible to axisY. majorGridlines. isVisible = true; axisY. minorGridlines. isVisible = true; // y axis dial 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)> 50) data [I] = random. nextDouble () * 100; else data [I] =-random. nextDouble () * 100;} chart. series. add ("Random", data); // range = sheet. cells. getSubrange ("B23", "J24"); range. value = $ "Write Time: {DateTime. now: yyyy-MM-dd HH: mm: ss} By CNXY "; range. merged = true; // B25 (three cell modes) 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; // B25, J25 sheet. cells. getSubrangeAbsolute (24, 1, 24, 9 ). merged = true; string filePath =$ @ "{Environment. currentDirectory} \ SheetChart.xlsx "; try {excel. save (filePath); Process. start (filePath); Console. writeLine ("Write successfully");} catch (Exception ex) {Console. writeLine (ex);} Console. write ("Press any key to continue. "); Console. readKey ();}}}

14. generated Excel

Demo Excel:

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

15. generated exe

As follows:

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

The running result is as follows: