Use the free spire.xlscontrol to create an excel, spire.xls excel

Use the free spire.xlscontrol to create an excel, spire.xls excel

Controls, I tried to create multiple applications to test all its functions in the next period. To be honest, this control is very powerful, it contains almost all the functions of Microsoft Excel, and does not require Microsoft Office to be installed. In addition, the naming of the method attributes is basically the same as those in MS Excel, for more information about the unclear functions, refer to MS Excel to find the corresponding attributes and methods. For me, I tried almost all the functions in just a few weeks, now we have applied it to our project. I have been using it for so long and have been very satisfied so far. I would like to share it with you here. Because it has too many functions and requires too much time to list them one by one, I will first use an example of creating a report to show some of its functions. I will share more functions with you later.

1. Create an empty excel document and obtain its first sheet

Workbook workbook = new Workbook ();
Worksheet worksheet = workbook. Worksheets [0];

2. Report title

Static void MakeTitle (Worksheet worksheet)
{
CellRange range = worksheet. Range ["A1"]; // get cell A1
Range. Text = "employee and personnel information table"; // Add a title
Range. Style. Font. IsBold = true; // set the title Font to bold.
Range. Style. Font. Size = 14; // set the Font Size to 14.
Range. Style. HorizontalAlignment = HorizontalAlignType. Center; // set the title to Center horizontally.
Range. Style. VerticalAlignment = verticaligntype. Center; // you can specify the vertical Center of the title.
Worksheet. Range ["A1: J1"]. Merge (); // Merge A1 to J1 as a cell
}

:

3. Add Report data. For convenience, here I use the data of a datatable in a database as the report data. Of course, you can assign values to cells respectively.

For example, you can use

Worksheet. Range ["A1"]. Text = data1;

If it is a number, you can use

 

Worksheet. Range ["A1"]. NumberValue = data2;

 

And set its output format

Worksheet. Range ["C5"]. NumberFormat = "#,## 0.00 ";

The following is the code for adding report data:

Static void AddReportData (Worksheet worksheet)
{
Worksheet. InsertDataTable (dataTable, true, 2, 1); // insert a dataTable from the cell in the first column of the Second row, and add the data in the column header
CellStyle style Style = worksheet. Range ["A2: J2"]. style; // get the format of cells A2 to J2
Style. Font. Color = Color. White; // set the Font Color to White.
Style. KnownColor = ExcelColors. Green; // set the background color of the cell to Green.
Style. Font. IsBold = true; // set the Font to bold
Style. HorizontalAlignment = HorizontalAlignType. Center; // you can specify the horizontal Center of the text.
Style. VerticalAlignment = verticaligntype. Center; // you can specify verticaligntype to Center the text vertically.
Style. Borders [BordersLineType. EdgeLeft]. LineStyle = LineStyleType. Thin; // set border on the left.
Style. Borders [BordersLineType. EdgeRight]. LineStyle = LineStyleType. Thin; // set border on the right
Style. Borders [BordersLineType. EdgeTop]. LineStyle = LineStyleType. Thin; // set the above border
Style. Borders [BordersLineType. EdgeBottom]. LineStyle = LineStyleType. Thin; // set the following border

CellStyle oddStyle = worksheet. Workbook. Styles. Add ("oddStyle"); // create a style and name it "oddStyle"
OddStyle. Borders [BordersLineType. EdgeLeft]. LineStyle = LineStyleType. Thin;
OddStyle. Borders [BordersLineType. EdgeRight]. LineStyle = LineStyleType. Thin;
OddStyle. Borders [BordersLineType. EdgeTop]. LineStyle = LineStyleType. Thin;
OddStyle. Borders [BordersLineType. EdgeBottom]. LineStyle = LineStyleType. Thin;
OddStyle. KnownColor = ExcelColors. LightGreen1;

CellStyle evenStyle = worksheet. Workbook. Styles. Add ("evenStyle"); // create a style and name it "evenStyle"
EvenStyle. Borders [BordersLineType. EdgeLeft]. LineStyle = LineStyleType. Thin;
EvenStyle. Borders [BordersLineType. EdgeRight]. LineStyle = LineStyleType. Thin;
EvenStyle. Borders [BordersLineType. EdgeTop]. LineStyle = LineStyleType. Thin;
EvenStyle. Borders [BordersLineType. EdgeBottom]. LineStyle = LineStyleType. Thin;
EvenStyle. KnownColor = ExcelColors. LightTurquoise;
// Set the format for cells A3 to J20. If an odd number of rows use oddStyle, if an even number of rows use evenStyle.
Foreach (CellRange range in worksheet. Range ["A3: J20"]. Rows)
{
If (range. Row % 2 = 0)
Range. CellStyleName = evenStyle. Name;
Else
Range. CellStyleName = oddStyle. Name;
}
Worksheet. AllocatedRange. AutoFitColumns (); // automatically adjust the column width to adapt to the cell data.
Worksheet. AllocatedRange. AutoFitRows (); // automatically adjusts the Row Height to adapt to the cell data.
}

:

4. Add a filter

Worksheet. AutoFilters. Range = worksheet. Range ["A2: J20"];

:

 

5. Now the report is ready, and you can save it to an excel file. It can save files in Excel2003, Excel2007, Excel2010, and Excel2013 formats.

String output = "Report.xlsx ";
Workbook. SaveToFile (output, ExcelVersion. Version2010 );

 

 

 

 

 

 

 

 

Reference page:

Http://www.yuanjiaocheng.net/entity/linq-to-entities-projection.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/core-user-registration.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/attribute-route.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/projectjson.html

Http://www.yuanjiaocheng.net/webapi/create-crud-api-1.html

Http://www.yuanjiaocheng.net/webapi/create-web-api-proj.html

Http://www.yuanjiaocheng.net/CSharp/csharp-class.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/core-login-logout.html

Http://www.yuanjiaocheng.net/Jsp/first.html

Http://www.yuanjiaocheng.net/ASPNET-CORE/project-layout.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-data-types.html