C # Component Series you deserve to have an Excel processing artifact spire.xls_c# tutorial

Foreword: The recent project inside has some to Excel Operation's demand, the blogger thought did not think, Npoi Bai, simple, open source, free, everybody likes! It's true that for some simple Excel import, export, and merge cells, it's not much of a problem, but the demand for this one two points is npoi:

1, import Excel, you need to cut the sheet page of Excel, and then each sheet page to generate a separate PDF file.

2, the export of Excel, the project needs to be some data tables in the form of a chart in Excel display.

Looking for a lap of data, for Excel to generate PDFs, the answer on the Web is the same: using COM components, by calling the contents of the Office components above the server to turn. This is the way to install Office on the server, which is second, and most importantly, the issue of permissions is a headache. Bo Master has been implemented in this way, debugging when there is no problem, deployed to IIS and then a variety of permissions after the problem, very easy to deploy on a server above the deployment of the same as the same way, but still prompted the "Access denied." The blogger is also drunk. And for Excel to generate a chart, Npoi temporarily did not find the implementation, the way the COM component can, but the implementation is slightly complicated, and this thing is huge, unstable, especially the majority of our personal computers installed in the office is not genuine, it is also troublesome to use.

Based on this, after a number of efforts to find such a third-party component Spire.xls. These two days experience a, use up more smoothly, here to briefly introduce the use of this component.

Introduction of components

Spire.xls is one of the E-iceblue developed components of professional Office document processing based on enterprise, Spire.office for. NET. Spire.doc,spire Xls,spire.pdf,spire.barcode has a variety of professional components, for various Office documents in the process of processing to provide a lot of convenience, the official for a variety of functions to provide a large number of online APIs, simplifying the use of components of the difficulty. Component does not require support for local office components when used. Spire.office is an enterprise-class component that provides a fee version and a free version of two levels, generally speaking, for personal applications, the free version is sufficient. For example, for the above bloggers encounter problems, Spire.xls components provide a very good implementation mechanism, if you also encountered Npoi solve problems, may wish to try this.

"XLS" is one of the suffixes of the Excel file, as the name suggests, Spire.xls of course is for Excel table processing components, this article, the blogger combined with the problems encountered above to see the powerful features of Spire.xls components.

Ii. installation and use of components

There are two ways to install a component here:

1. Official Download and Installation

Download the address. The official download of the installation package is the MSI end of the installation, you need to choose the supported VS version of information, software installation does not do too much description, interested can download to try.

2, NuGet installation

Everyone's favorite should still be NuGet way, simple, convenient, and easy to manage. Bloggers are also less likely to download a single installation package for one component.

Spire.xls also provides a nuget way to simply search Spire and select a free version of the component:

The required DLL is automatically referenced when the installation is complete

Introduction of Component function

Some common operations on Excel, such as value, assignment, set cell style, and so on, here is not too much to introduce, whether it is COM components, npoi or aspose, these are the most basic functions. The following are highlighted in response to a number of issues raised above.

1. Excel Turn PDF

(1) A review of the realization of COM components

On the implementation of Excel to PDF, the solution found on the web is basically the same as the approximate code:

<summary>///convert Excel file to PDF file///</summary>///<param name= "SourcePath" > source file path </pa ram>///<param name= "TargetPath" > Target file path </param>///<returns>true= conversion successful </returns> Publi
   c bool Xlsconverttopdf (string SourcePath, String TargetPath) {logger.info ("start pdf");
   BOOL result = FALSE;
   Xlfixedformattype targettype = xlfixedformattype.xltypepdf;
   Object missing = Type.Missing;
   Microsoft.Office.Interop.Excel.Application application = null;
   Microsoft.Office.Interop.Excel.Workbook workbook = null;
    try {application = new application (); Application.
    Interactive = false;
    Object target = TargetPath;
    Object type = TargetType; Workbook = Application. Workbooks.Open (SourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
    Missing, missing, missing, missing); Application.
    Interactive = true; Workbook.exportasfixedformat (TargetType, tarGet, Xlfixedformatquality.xlqualitystandard, True, false, missing, missing, missing, missing);
   result = true; The catch (Exception ex) {logger.error ("Excel to PDF exception, exception information:" + ex.) Message + ". Stack info: "+ ex." 
    StackTrace);
   result = false;
     finally {if (workbook!= null) {Workbook.close (True, missing, missing);
    workbook = null; } if (application!= null) {application.
     Quit ();
    application = NULL; } GC.
    Collect (); Gc.
    WaitForPendingFinalizers (); Gc.
    Collect (); Gc.
   WaitForPendingFinalizers ();
  return result;

 }

This approach needs to depend on the Office COM component on this computer, and if you install Office, there is no COM component-related DLL installed, and this method is not used, and one of the biggest problems is the implementation of application. Workbooks.Open (SourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, Miss ing, missing, missing, missing); This method requires that the current user has permission to manipulate Excel application This component, especially after deploying to IIS, which requires a series of permissions to be configured, which is cumbersome.

(2) Spire.xls implementation conversion

Through the above, we know that Spire.office provides Spire.xls and spire.pdf two components, then the conversion between them is simple. We still simulate a file upload function.

There is an upload control on the front:

Copy Code code as follows:

<input type= "File" name= "Txt_file"/>

In the background there is a way to receive uploaded files as follows:

[HttpPost]
  Public Jsonresult UploadFile ()
  {
   var strres = string. Empty;
   var ofile = request.files["Txt_file"];
   Workbook book = new Workbook ();
   Book. Loadfromstream (ofile.inputstream);
   var strfullname = @ "D:\Data\Upload\" + "a" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". pdf";
   Book. Savetopdf (strfullname);
   Return Json (New Object {}, Jsonrequestbehavior.allowget);
  }

So simple a few words can realize will upload Excel into PDF file. The workbook object is generated from the source file, Spire.xls provides a variety of ways, and the two most common ways we use are as follows:

Generates workbook based on the file path.
public void LoadFromFile (string fileName);
Generates workbook based on file stream.
public void Loadfromstream (Stream stream);

2.1, the most original conversion

Original Excel file:

After converting to PDF

2.2, not good to see? Add a border.

After conversion

2.3, customize the conversion of the PDF

In some cases, we have many columns in Excel that cause the default generated PDF to wrap, which will result in poor readability of the PDF, in which case Spire.xls gives us a way to customize the conversion of PDFs, such as the page width, page height, size, and so on for the PDF.

For example, the following Excel documents need to be converted to PDF files:

If you follow a normal conversion, the resulting PDF is not wide enough to display all of the columns in Excel, so the effect is converted:

To address this problem, the component provides us with the following methods:

 [HttpPost] public Jsonresult uploadfile () {var strres = string.
      Empty;

      var ofile = request.files["Txt_file"];
      Workbook book = new Workbook (); Book.
      Loadfromstream (Ofile.inputstream);
      var strfullname = @ "D:\Data\Upload\" + "a" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". pdf";
      Pdfdocument pdfdocument = new Pdfdocument ();
      PdfDocument.PageSettings.Orientation = Pdfpageorientation.landscape; PdfDocument.PageSettings.Width = 1800;//Specifies the width of the pdf pdfDocument.PageSettings.Height = 1000;//The height of the specified PDF Pdfconvert
      Ersettings settings = new Pdfconvertersettings (); Settings.

      Templatedocument = pdfdocument;
      Pdfconverter pdfconverter = new Pdfconverter (book);
      Pdfdocument = Pdfconverter.convert (settings);
      Pdfdocument.savetofile (Strfullname);
    Return Json (New Object {}, Jsonrequestbehavior.allowget); }

This will be normal, if you have more Excel columns, you can adjust the width and height. The results obtained are as follows

There are more powerful features everyone is interested to explore, the official document is also written in detail.

2.4. Excel turns to other types

In addition to switching to Pdf,spire.xls, it also supports conversion to other types, such as common XML, Image, HTML, and so on. If we have such a demand, we can delve into it.

2. Excel generates charts

2.1, Excel Chart Generation principle Analysis

Take a look at the following diagram to see how the chart works in Excel

We can see from this diagram, Excel generates the chart first needs the current document to exist the data table, then selects the corresponding data table, finally chooses the generated chart type, the Excel application will automatically help you to generate the corresponding data chart.

2.2, Spire.xls to generate a simple chart

Knowing how the above Excel generates the chart, let's take a look at how the Spire.xls component helps us solve the problem of generating graphs. With respect to generating charts, the Spire.xls component offers a number of options that cover various types of charts, statistical methods, etc. in Excel. Let's look at an example of a simple point.

[HttpPost] public jsonresult exportdata () {try {workbook book = new Workbook (); Worksheet sheet = Book.
        Worksheets[0];
        var random = new Random ();
        var icellcount = 1; 1. Set table header sheet. Range[1, icellcount++].
        Text = "department name"; Sheet. Range[1, icellcount++].
        Text = "Number of departments";
        var lstdeptname = new list<string> () {"Marketing department", "Planning department", "PR", "Administrative department", "Development Department"};
        var a = 0;
          2. Construct table data for (var i = 2; i < 7; i++) {icellcount = 1; Sheet. Range[i, icellcount++].
          Text = lstdeptname[a++]; Sheet. Range[i, icellcount++]. Numbervalue = random.
        Next (1, 100);; //3. Generate Chart Setchart (sheet, excelcharttype.barclustered); var strfullname = @ "D:\Data\Upload\" + "Export"
        + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". xlsx"; Book.
      SaveToFile (Strfullname, excelversion.version2010); The catch (Exception ex) {} return Json (True, JSonrequestbehavior.allowget); } private void Setchart (worksheet sheet, Excelcharttype chartformat) {//1. Set the sheet page name sheet.
      Name = "Chart data"; Sheet.

      Gridlinesvisible = false; Chart Chart = sheet.

      Charts.add (); 2. Specify the area chart to generate the chart. DataRange = sheet.
      range["A1:b6"]; Chart.

      Seriesdatafromrange = false; 3. Specify the location of the chart chart.
      Leftcolumn = 5; Chart.
      Toprow = 2; Chart.
      Rightcolumn = 11; Chart.
      Bottomrow = 29; Chart.

      ChartType = ChartFormat; 4. Set the name of the chart and the name of the X and Y axes chart.
      ChartTitle = "department information"; Chart.
      Charttitlearea.isbold = true; Chart.

      Charttitlearea.size = 12; Chart.
      Primarycategoryaxis.title = "department"; Chart.
      PrimaryCategoryAxis.Font.IsBold = true; Chart.

      PrimaryCategoryAxis.TitleArea.IsBold = true; Chart.
      Primaryvalueaxis.title = "number"; Chart.
      Primaryvalueaxis.hasmajorgridlines = false; Chart.
      PrimaryValueAxis.TitleArea.TextRotationAngle = 90; Chart. PrimAryvalueaxis.minvalue = 0; Chart.

      PrimaryValueAxis.TitleArea.IsBold = true; 5. Set the value of the chart Spire.Xls.Charts.ChartSerie cs = chart.
      Series[0]; Cs. CategoryLabels = sheet.
      range["A2:a6"]; Cs. Values = sheet.
      range["B2:b6"]; Cs.
      Dataformat.showactivevalue = true; Chart.
    Legend.position = Legendpositiontype.top;
 }

The Excel content obtained from the preceding code is as follows:

Code explanation: About the above code is not difficult, but still want to do some simple instructions.

First populate the table data, Spire.xls read and write data tables using Sheet.range[i, icellcount++]. Text this way. It is worth mentioning that the index of rows and columns here is starting from 1. Range provides range["B1" In addition to the way rows and columns are indexed. Text this way to read the value.

By using Excel to generate chart principles, we know that there are data tables, and we have to select the area where the chart is generated, which is through chart. DataRange = sheet. range["A1:b6"]; This sentence goes to the specified area and is consistent with the way Excel operates.

Through the chart. ChartType = ChartFormat To specify the type of chart you want to generate, Spire.xls contains various chart types through an enumeration type.

In addition to the above, the component also supports specifying the position of the chart in the document, and the maximum minimum value for the chart coordinates. and can pass

Copy Code code as follows:

Spire.Xls.Charts.ChartSerie cs = Chart. Series[0];cs. CategoryLabels = sheet. range["A2:a6"];cs. Values = sheet. range["B2:b6"];

This way to specify the classification and value of the area, more in line with Excel operating habits. Of course, if there is no special, these can not be specified completely.

2.3. Statistics on two or more items

The above is just the simplest example, what if you want to count multiple columns? Let's continue with this example and we'll change the code like this:

[HttpPost] public jsonresult exportdata () {try {workbook book = new Workbook (); Worksheet sheet = Book.
        Worksheets[0];
        var random = new Random ();
        var icellcount = 1; 1. Set table header sheet. Range[1, icellcount++].
        Text = "department name"; Sheet. Range[1, icellcount++].
        Text = "incumbency number"; Sheet. Range[1, icellcount++].
        Text = "turnover number";
        var lstdeptname = new list<string> () {"Marketing department", "Planning department", "PR", "Administrative department", "Development Department"};
        var a = 0;
          2. Construct table data for (var i = 2; i < 7; i++) {icellcount = 1; Sheet. Range[i, icellcount++].
          Text = lstdeptname[a++]; Sheet. Range[i, icellcount++]. Numbervalue = random.
          Next (1, 100); Sheet. Range[i, icellcount++]. Numbervalue = random.
        Next (1, 100);;
        //3. Generate Chart Setchart (sheet, excelcharttype.barclustered);
        var strfullname = @ "D:\Data\Upload\" + "Export" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". xlsx"; BOok.
      SaveToFile (Strfullname, excelversion.version2010);
    The catch (Exception ex) {} return Json (true, jsonrequestbehavior.allowget); } private void Setchart (worksheet sheet, Excelcharttype chartformat) {//1. Set the sheet page name sheet.
      Name = "Chart data"; Sheet.

      Gridlinesvisible = false; Chart Chart = sheet.

      Charts.add (); 2. Specify the area chart to generate the chart. DataRange = sheet.
      range["A1:c6"]; Chart.

      Seriesdatafromrange = false; 3. Specify the location of the chart chart.
      Leftcolumn = 5; Chart.
      Toprow = 2; Chart.
      Rightcolumn = 11; Chart.
      Bottomrow = 29; Chart.

      ChartType = ChartFormat; 4. Set the name of the chart and the name of the X and Y axes chart.
      ChartTitle = "department information"; Chart.
      Charttitlearea.isbold = true; Chart.

      Charttitlearea.size = 12; Chart.
      Primarycategoryaxis.title = "department"; Chart.
      PrimaryCategoryAxis.Font.IsBold = true; Chart.

      PrimaryCategoryAxis.TitleArea.IsBold = true; Chart.
      Primaryvalueaxis.title = "number"; Chart. PrimarYvalueaxis.hasmajorgridlines = false; Chart.
      PrimaryValueAxis.TitleArea.TextRotationAngle = 90; Chart.
      Primaryvalueaxis.minvalue = 0; Chart.

      PrimaryValueAxis.TitleArea.IsBold = true; 5. Set the value of the chart Spire.Xls.Charts.ChartSerie cs = chart.
      Series[0]; Cs.
      Dataformat.showactivevalue = true; Cs.
      Dataformat.showbubble = true; Chart.

    Legend.position = Legendpositiontype.top;
 }

The results were as follows:

The only change here is the data area, and as long as you specify which part of the area we need to generate the chart, Excel automatically calculates and generates the chart.

2.4, various types of graphic display

As mentioned above, chart. ChartType = ChartFormat; This sentence allows you to set the type of chart that defines a series of chart types in Spire.xls:

Amespace Spire.xls

{//Summary://Chart types.
    public enum Excelcharttype {//Summary://Represents the column clustered chart type.
    columnclustered = 0,///Summary://Represents the Stacked column chart type.
    columnstacked = 1,////Summary://Represents the 100% stacked column chart type.
    column100percentstacked = 2,////Summary://Represents the 3D clustered column chart type.
    column3dclustered = 3,////Summary://Represents the 3D stacked column chart type.
    column3dstacked = 4,////Summary://Represents the 3D 100% stacked column chart type.
    column3d100percentstacked = 5,///Summary://Represents the 3D column chart type.
    Column3d = 6,///Summary://Represents the Clustered bar chart type.
    barclustered = 7,///Summary://Represents the stacked bar chart type. barstacked = 8,////Summary://Represents the 100% stacked bar chart TypE. bar100percentstacked = 9,///Summary://Represents the 3D clustered bar chart type.
    bar3dclustered = 10,///Summary://Represents the 3D stacked bar chart type.
    bar3dstacked = 11,////Summary://Represents the 100% 3D stacked bar chart type.
    bar3d100percentstacked = 12,///Summary://Represents the line chart type.
    line = 13,///Summary://Represents the stacked line chart type.
    linestacked = 14,///Summary://Represents the 100% stacked line chart type.
    line100percentstacked = 15,///Summary://Represents the Markers line chart type.
    Linemarkers = 16,///Summary://Represents the stacked markers line chart type.
    linemarkersstacked = 17,////Summary://Represents the 100% stacked markers line chart type.
    linemarkers100percentstacked = 18,///Summary://Represents the 3D line chart type.
    Line3d = 19,Summary://Represents the pie chart type.
    Pie = 20,///Summary://Represents the 3D pie chart type.
    Pie3d = 21,////Summary://Represents the pie of pie chart type.
    Pieofpie = 22,///Summary://Represents the exploded pie chart type.
    pieexploded = 23,///Summary://Represents the 3D exploded pie chart type.
    pie3dexploded = 24,///Summary://Represents the bar pie chart type.
    Piebar = 25,///Summary://Represents the markers scatter chart type.
    Scattermarkers = 26,///Summary://Represents the Scattersmoothedlinemarkers chart type.
    Scattersmoothedlinemarkers = 27,///Summary://Represents the Scattersmoothedline chart type.
    Scattersmoothedline = 28,///Summary://Represents the Scatterlinemarkers chart type.
    scatterlinemarkers = 29,///Summary://Represents the Scatterline chart type. ScatterliNE = 30,///Summary://Represents the area chart type.
    Area = 31,////Summary://Represents the areastacked chart type.
    areastacked = 32,///Summary://Represents the area100percentstacked chart type.
    area100percentstacked = 33,///Summary://Represents the AREA3D chart type.
    Area3d = 34,///Summary://Represents the area3dstacked chart type.
    area3dstacked = 35,///Summary://Represents the area3d100percentstacked chart type.
    area3d100percentstacked = 36,///Summary://Represents the doughnut chart type.
    Doughnut = 37,///Summary://Represents the doughnutexploded chart type.
    doughnutexploded = 38,///Summary://Represents the Radar chart type.
    Radar = 39,///Summary://Represents the Radarmarkers chart type.
    Radarmarkers = 40,///Summary://Represents the radarfilled chart type. Radarfilled= 41,///Summary://Represents the Surface3d chart type.
    Surface3d = 42,///Summary://Represents the Surface3dnocolor chart type.
    Surface3dnocolor = 43,///Summary://Represents the Surfacecontour chart type.
    Surfacecontour = 44,///Summary://Represents the Surfacecontournocolor chart type.
    Surfacecontournocolor = 45,///Summary://Represents the Bubble chart type.
    Bubble = 46,///Summary://Represents the Bubble3d chart type.
    Bubble3d = 47,///Summary://Represents the Stockhighlowclose chart type.
    Stockhighlowclose = 48,///Summary://Represents the Stockopenhighlowclose chart type.
    Stockopenhighlowclose = 49,///Summary://Represents the Stockvolumehighlowclose chart type.
    Stockvolumehighlowclose = 50,///Summary://Represents the Stockvolumeopenhighlowclose chart type.
Stockvolumeopenhighlowclose = 51,    Summary://Represents the cylinderclustered chart type.
    Cylinderclustered = 52,///Summary://Represents the cylinderstacked chart type.
    cylinderstacked = 53,///Summary://Represents the cylinder100percentstacked chart type.
    cylinder100percentstacked = 54,///Summary://Represents the cylinderbarclustered chart type.
    cylinderbarclustered = 55,///Summary://Represents the cylinderbarstacked chart type.
    cylinderbarstacked = 56,///Summary://Represents the cylinderbar100percentstacked chart type.
    cylinderbar100percentstacked = 57,///Summary://Represents the cylinder3dclustered chart type.
    Cylinder3dclustered = 58,///Summary://Represents the coneclustered chart type.
    coneclustered = 59,///Summary://Represents the conestacked chart type. conestacked = 60,///Summary://Represents the Cone100percentstackedChart type.
    cone100percentstacked = 61,///Summary://Represents the conebarclustered chart type.
    conebarclustered = 62,///Summary://Represents the conebarstacked chart type.
    conebarstacked = 63,///Summary://Represents the conebar100percentstacked chart type.
    conebar100percentstacked = 64,///Summary://Represents the cone3dclustered chart type.
    cone3dclustered = 65,///Summary://Represents the pyramidclustered chart type.
    pyramidclustered = 66,///Summary://Represents the pyramidstacked chart type.
    pyramidstacked = 67,///Summary://Represents the pyramid100percentstacked chart type.
    pyramid100percentstacked = 68,///Summary://Represents the pyramidbarclustered chart type.
    pyramidbarclustered = 69,///Summary://Represents the pyramidbarstacked chart type. pyramidbarstacked = 70,///Summary://RepreseNTS the pyramidbar100percentstacked chart type.
    pyramidbar100percentstacked = 71,///Summary://Represents the pyramid3dclustered chart type.
    pyramid3dclustered = 72,///Summary://Represents the Combinationchart chart types.
 Combinationchart = 73,}}

Let's take a look at some of the more common charts.

2.4.1, pie-like chart

Excelcharttype.pie

Excelcharttype.pie3d

2.4.2, Wiring diagram

Excelcharttype.line3d

Excelcharttype.linestacked

2.4.3, Area Map

2.4.4, Radar Map

2.4.5, circular Column chart

3. Other function Introduction

Other highlights of the Spire.xls feature, bloggers are also studying, already know some common functions such as (1) Support cell merging, freezing, annotation, (2) Database mode import and export, (3) sheet page copy, cut, display, hide, etc. (4) Header footer settings (5) Data grouping, sorting, (6) Like Excel inserts a picture, sets the picture style and so on. Some of these features have been implemented, some are still in the study, and then have the opportunity to send for your reference. Because of the question of space, let's get here first.

Iv. Summary

The above simple summary of the next Spire.xls components features, a good solution to the blogger encountered problems, bloggers feel that to a certain extent, Spire.xls components can be fitted npoi, COM components of the partial deficiencies. There are many other features to be sorted out after the test demo together. If you also encounter some other components can not solve the problem, may wish to try it, may bring you surprise. Of course, if this article can help you, or hope that friends of the park to help recommend, bloggers to continue the next efforts!

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.