C # Component Family--another Excel processing artifact Spire.xls, you deserve

Foreword: Some of the recent projects in the needs of Excel operations, bloggers want to think, Npoi Bai, simple, open source, free, we all like! Indeed, for some simple Excel import, export, merge cells and so on, it is not much of a problem, but this time the demand for two points is npoi:

1. After you import Excel, you need to cut the sheet page of Excel, and then each sheet page generates a separate PDF file.
2. When you export Excel, you need to show some data tables in Excel in the form of a chart.

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 server above the Office components to go through the things. This way you need to install Office on the server, which is second, and most importantly, the problem of permissions is a headache. Bo Master has been implemented in this way, debugging is not a problem, deployed to IIS after the issue of various permissions, it is hard to deploy on one server successfully, put on the other server in the same way, but still prompt "access denied." The blogger is also drunk. For Excel to generate the chart, Npoi temporarily did not find the implementation of the way, COM components can be, but the implementation of a slightly complex, and this thing is huge, not too stable, especially the majority of our personal computer installed on the office is not genuine, and the use of the egg hurts.

Based on this, after some effort, we find such a third-party component Spire.xls. These two days experience a piece, use is also more handy, here to briefly introduce the use of this component.

Original address of this article: http://www.cnblogs.com/landeanfen/p/5888973.html

First, the introduction of components

Spire.xls is one of a set of enterprise-grade professional Office document processing components developed by E-iceblue, full name Spire.office for. NET. It has a variety of professional components such as Spire.doc,spire Xls,spire.pdf,spire.barcode, which provides a great deal of convenience for various Office documents, and the official provides a large number of online APIs for various functions, simplifying the difficulty of using components. Components do not require support for local office components when they are used. Spire.office is an enterprise-class component that offers a paid version and a free version of two levels, which, in general, are sufficient for individual applications. For example, the above bloggers have encountered problems, Spire.xls components provide a good implementation mechanism, if you also encounter Npoi can not solve the problem, may wish to try this.

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

Second, the installation and use of components

There are two ways to install a component in this case:

1, the official download installation

。 The official download of the installation package is the end of the MSI, the installation needs to choose the supported VS version of the information, the installation of the software does not do too much to explain, interested can download try.

2. NuGet Installation

Your favorite should be the nuget approach, simple, convenient, and easy to manage. Bloggers are also less likely to download a single installation package for a component.

Spire.xls also offers a way to NuGet, just search for Spire and select the free version of the component:

The required DLLs are automatically referenced when the installation is complete

Three, the function of the component introduction

Some of the commonly used operations of Excel, such as taking values, assigning values, setting cell styles, and so on, do not introduce much, whether it is COM components, Npoi, or aspose, which are the most basic functions. The following are highlighted in response to several of the questions raised above.

1. Excel to PDF (1) A review of the implementation of COM components

For Excel to PDF implementations, the solution found on the web is basically the same as the approximate code:

　　　　　 <summary>///convert Excel files to PDF format///</summary>//<param name= "source Path > Source file path </param>//<param name= "TargetPath" > Destination file path </param>///&LT;RETURNS&GT;TR ue= Conversion succeeded </returns> public bool Xlsconverttopdf (string SourcePath, String TargetPath) {Logge            R.info ("Start to 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; } catch (Exception ex) {logger.error ("Excel to PDF exception, exception info:" + 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 method needs to rely on the Office COM components on this machine, if you install Office, there is no COM component related DLL installed, this method is not used, and there is one of the biggest problem is to execute 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, it is troublesome to configure a series of permissions.

(2) Spire.xls implementation conversion

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

The front end has an upload control:

<input type= "File" Name= "Txt_file" id= "Txt_file" class= "file-loading"/>

There is a way to receive the upload file in the background 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\" + "first" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". pdf";            Book. Savetopdf (strfullname);            Return Json (New Object {}, Jsonrequestbehavior.allowget);        }

Just a few simple words to convert the uploaded Excel into a PDF file. Spire.xls provides several ways to generate workbook objects from source files, and the two methods we use most often are as follows:

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

2.1. The most primitive conversion

Original Excel file:

After converting to PDF

2.2, not good to see? Add a border.

After conversion

2.3. PDF of Custom conversions

In some cases, we have a number of columns in Excel, resulting in the default generated PDF wrap problem, which will result in a poor readability of the PDF, in this case, Spire.xls gives us a way to customize the conversion of PDF, for example, you can specify the PDF page width, page height, size and other properties.

For example, the following Excel document needs to be converted into a PDF file:

If you follow a regular conversion, the resulting PDF is not wide enough to display all of Excel's columns, so the effect of the conversion is as follows:

To solve 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\" + "first" + 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;//Specifies the height of the PDF P            Dfconvertersettings 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 work fine, and if you have more Excel columns, you can adjust the width and height appropriately. The results are as follows

There are more powerful features that everyone is interested in exploring, and the official documents are written in detail.

2.4. Excel to other types

In addition to conversion to Pdf,spire.xls, it also supports converting to other types, such as common XML, Image, HTML, and so on. If you have any demand in this area, you can look into it.

2, Excel generate chart 2.1, Excel chart Generation principle Analysis

Let's take a look at the principle of generating graphs in Excel by the following diagram

From this graph we can see thatExcel generates a chart first requires a data table in the current document, then select the appropriate data table, and finally select the generated chart type, the Excel application will automatically help you generate the corresponding data chart .

2.2. Spire.xls Create a simple chart

Knowing how Excel generated the chart above, let's look at how the Spire.xls component helps us solve the problem of generating graphs. The Spire.xls component provides a number of options for generating charts, covering various types of charts, statistical methods, etc. in Excel. Let's start with 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 the 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 the 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\" + "Ex" Port "+ DateTime.Now.ToString (" Yyyymmddhhmmss ") + ". xlsx"; Book.            SaveToFile (Strfullname, excelversion.version2010);        } catch (Exception ex) {} return Json (true, jsonrequestbehavior.allowget);            } private void Setchart (Worksheet sheet, Excelcharttype chartformat) {//1. Setting the name of the sheet page Sheet.            Name = "Chart data"; Sheet.            Gridlinesvisible = false; Chart Chart = Sheet.            Charts.add (); 2. Specify the area of the chart to be generated. DataRange = sheet.            range["A1:b6"]; Chart.            Seriesdatafromrange = false; 3. Specify where the chart is located 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 = "Departmental 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 of persons"; 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 through the above code is as follows:

Code Explanation: The code above is not difficult, but still want to do some simple explanation.

1. First fill in the tabular data, Spire.xls read and write data table using Sheet.range[i, icellcount++]. Text this way. It is worth mentioning that the index of the row and column here is starting from 1. Range provides a range["B1" In addition to the way the row and column indexes are provided. Text this way to read the value.
2. By using Excel above to generate the chart, we know that there is a data table, and we have to select the area where the chart is generated, and the above code passes through the chart . DataRange = sheet. range["A1:b6"]; This sentence goes to the specified area and is consistent with the way Excel operates.
3. Through chart. ChartType = ChartFormat; To specify the type of chart that needs to be generated, Spire.xls contains the various chart types through an enumeration type.
4. In addition to the above, the component supports specifying the position of the chart in the document, the maximum minimum value of the chart coordinates. And be able to pass

   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 one of the simplest examples, if you want to count multiple columns? We continue to look at this example, and we change the code to this:

　　　　 [HttpPost] public jsonresult exportdata () {try {Workbook book =                New Workbook (); Worksheet sheet = Book.                Worksheets[0];                var random = new Random ();                var icellcount = 1; 1. Set the table header sheet. Range[1, icellcount++].                Text = "department name"; Sheet. Range[1, icellcount++].                Text = "Number of employees"; Sheet. Range[1, icellcount++].                Text = "Number of separations";                var lstdeptname = new list<string> () {"Marketing department", "Planning department", "PR", "Administrative department", "Development Department"};                var a = 0;                    2. Construct the 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. Generating a chart SetCHart (sheet, excelcharttype.barclustered);                var strfullname = @ "D:\Data\Upload\" + "Export" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". xlsx"; Book.            SaveToFile (Strfullname, excelversion.version2010);        } catch (Exception ex) {} return Json (true, jsonrequestbehavior.allowget);            } private void Setchart (Worksheet sheet, Excelcharttype chartformat) {//1. Setting the name of the sheet page Sheet.            Name = "Chart data"; Sheet.            Gridlinesvisible = false; Chart Chart = Sheet.            Charts.add (); 2. Specify the area of the chart to be generated. DataRange = sheet.            range["A1:c6"]; Chart.            Seriesdatafromrange = false; 3. Specify where the chart is located 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 = "Departmental 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 of persons"; 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, as long as you specify which part of the area we need to generate the chart from, Excel automatically calculates and generates the chart.

2.4, various types of chart display

As stated above, chart. ChartType = ChartFormat; This sentence can be set to the type of the chart, and a series of chart types are defined in Spire.xls:

Excelcharttype

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 chart

2.4.5, round column chart

3. Introduction of other functions

About the other highlights of Spire.xls, 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) Grouping and sorting of data, (6) Inserting pictures like Excel, setting picture styles and so on . Some of these functions have already been realized, some of them are still under study, and they will be sent out for your reference later. Because of the space problem, this article first come here.

Iv. Summary

The above simple summary of the next Spire.xls components of several features, a good solution to the Bo master problems encountered, Bo Master felt that to a certain extent, Spire.xls components can be npoi, COM components of the partial insufficiency. There are many other features that will be sent together with the test demo after finishing. If you also encounter problems that other components can't solve, try it and maybe surprise you. Of course, if this article can help you, or hope that the park friends to help recommend , Bo master the next time to continue efforts!

Original source of this article: http://www.cnblogs.com/landeanfen/

Welcome to reprint, but without the author's consent, reprint article must be in the article page obvious location to the author and the original link , otherwise reserve the right to pursue legal responsibility

C # Component Family--another Excel processing artifact Spire.xls, you deserve