C # Learning Diary 2017-04-22 Npoi Usage Summary (to be continued)

Npoi Foundation know Npoi:

Npoi can still manipulate office objects without the user installing Office, which is a poi. NET version. The supported file formats are. xls. doc. ppt. In most cases where Excel is applied, it is preferable to use Npoi to manipulate Excel files. Instead of using C # to manipulate Excel directly. Advantages of Npoi:

The Advantage of Npoi is that Excel files can be produced and manipulated even if the Office,npoi is not installed on the local computer. There's no need to worry about Office versioning and adaptive issues using interface-oriented design, NPOI developers encapsulate many of the useful operations of Excel, and we just need to know how a feature works, without needing to know the specific implementation of this feature Npoi is completely free, thanks to the Npoi development team's selfless dedication, Because they are constantly updating and upgrading. We are able to have a operation Excel artifact. other ways to manipulate Excel's disadvantages:

Reading Excel from OLE DB: Using the Microsoft Jet provider to connect to an Excel workbook to read data. This method sees the Excel file as
As a data source, you can use SQL statements to link operations, and you do not need to install Office.
But with this method, only simple read and write operations can be performed, and a slightly more complex operation using OLE DB cannot be done (such as cell merging, coloring, etc.).

Invoke COM Component Operations Excel: This method requires the introduction of Microsoft.Office.Interop.Excel, which makes it easy to invoke COM components to complete Excel operations with VSTO (Visual Studio Tools for Office). However, if the Web site is deployed on IIS, it requires that the server has Excel installed, and sometimes you need to configure IIS permissions. The most important point is that COM component operations Excel is read in cell-based mode, so it is slow to read data. This approach is not recommended when working with data in large quantities. NPOI Structure

NPOI2.0 version structure:

Npoi 2.0 is composed of several DLLs, respectively

Npoi. Dll

Npoi. OOXML. Dll

Npoi. OpenXml4Net.dll,

Npoi. OpenXmlFormats.dll, the latter three are 2.0 newly added libraries.

Npoi. Modules included in the DLL

Npoi. Util Base Auxiliary Library
Npoi. Poifs OLE2 format Read and write library, mainly responsible for processing documentinformation
Npoi. DDF Microsoft Drawing Format read-write library
Npoi. interface libraries shared by SS Excel 2003 and Excel 2007 operations libraries, and common module implementations, such as formula calculation libraries
Npoi. Summary Information and document Summary information attribute read-write library of HPSF OLE2
Npoi. HSSF Excel Biff Format read-write library, which is the user's most used namespace

Npoi. Modules included in the Ooxml

Npoi. XSSF Excel 2007 Operations Library, most objects implement the NPOI.SS interface
Npoi. XWPF Word 2007 Operations Library

Npoi. Modules included in the OpenXml4Net.DLL

There is only one, that is, npoi.openxml4net, which is transplanted from POI's subproject openxml4j, which functions like npoi.poifs, except that it operates in the ooxml format. This module is consistent with the System.packaging functionality provided by Microsoft, except that System.packaging supports. NET 3.0, which supports. NET 2.0.

Npoi. Modules included in the OpenXmlFormats.DLL

There is also only one, that is, Openxmlformats, which defines the mapping relationship of all Ooxml objects and C # objects, and assists in serialization and deserialization, separating the read-write and logical tiers of the file. Npoi Operation Npoi Create and manipulate Excel files

Adding references

Using Npoi. HSSF. Usermodel;           The most commonly used reference, you can manipulate the. xls file using Npoi in Excel 2003.
XSSF. Usermodel;           Manipulate the. xlsx file of Excel 2007
using ICSharpCode.SharpZipLib.Zip;   This can be used to compress and decompress a file
using Npoi. Ss. Usermodel;             including XLS and xlsx common Interface Library and common mode of implementation
using System.IO;

Create workbooks, worksheets , rows, cells

            Iworkbook Book1 = new Hssfworkbook ();        Created an XLS format workbook
            iworkbook book2 = new Xssfworkbook ();       Created a xlsx-formatted workbook


            isheet Sheet1 = Book1. Createsheet ();          Create a worksheet
            isheet Sheet2 = Book1. Createsheet ("Sheet1"); Create a worksheet with a name


            irow row = Sheet1. CreateRow (0);              Creates a row of
            icell cell = row within a worksheet. Createcell (0);              Create a row of cells within a worksheet

            Reads an Excel table, path is the file path
            FileStream fileexcel = new FileStream (path,filemode.open,fileaccess.read); 

            Iworkbook Book1 = new Hssfworkbook (fileexcel);    Create a workbook in XLS format and populate the workbook
            isheet sheet = Book1. Getsheetat (0);               Gets the first table of the workbook
            irow row = sheet. GetRow (0);               Gets the first row of the worksheet
            Icell cell = row. Createcell (0);           Gets the value of a cell in a row of data

Npoi Instance DataTable Transformation Excel file

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data.Odbc; Using Npoi. HSSF.
Usermodel; Using Npoi. Ss.
Usermodel;
Using System.Data;


Using System.IO;  Namespace Packagemanagement {public class Excelhelper {readonly int ecxelrowmax = 65536; 
        Set the maximum number of rows in Excel public byte[] Mdatatable2excel (DataTable DT, String sheetname)//Convert DataTable to Excel and return a byte array                         {Iworkbook book = new Hssfworkbook (); Defines a workbook if (dt.
            Rows.Count < Ecxelrowmax)//If the number of rows in the table does not exceed the maximum value. {mdatawrite2sheet (dt, 0, dt.)  Rows.count-1, book, sheetname);
            Call method Creation Table} else//More than the maximum number of a table for the table storage {int page = dt.
                Rows.count/ecxelrowmax;
 for (int i = 0; i < page i++)//cycle pages, create table {                   int istartrow = i * ECXELROWMAX;         Gets the table start index value int iendrow = (i * ecxelrowmax) + ecxelRowMax-1; Gets the table-end index value mdatawrite2sheet (DT, Istartrow, Iendrow, book, sheetname); Call Method Creation Table} mdatawrite2sheet (dt, page * ecxelRowMax-1, dt.  Rows.Count), book, sheetname);   The portion of the sheet remaining less than one page also creates a table} MemoryStream ms = new MemoryStream (); Creates a memory stream object book.                         Write (MS); Writes the contents of the book workbook to return MS in MemoryStream.                    ToArray (); Returns a byte stream array} private void Mdatawrite2sheet (DataTable dt, int startrow, int endrow, Iworkbook book, Strin G sheetname) {isheet sheet = Book.          Createsheet (); Create a workbook IRow header = Sheet.           CreateRow (0); Creates the first row of the workbook, by default it is the header row for (int i = 0; i < dt. Columns.count;
            i++)//Loop Create header row {    Icell cell = header.      Createcell (i); The cell string value = DT that creates the header row. Columns[i]. Caption?? Dt. Columns[i].  ColumnName; Gets the column name cell.               Setcellvalue (value);
            Column name of the cell assignment datatable} int indexrow = 1; for (int i = StartRow i < Endrow; i++)//loop row Data {DataRow dr = dt.                Rows[i]; Creates a new DataRow object IRow row = sheet. CreateRow (indexrow++); Create a row object for the worksheet for (int j = 0; J < Dr. Itemarray.length; J + +)//loop cell data {row. Createcell (j). Setcellvalue (Dr[j]. ToString ()); Add cell data to Row object row}}

Excel File conversion DataTable

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data.Odbc; Using Npoi. HSSF.
Usermodel; Using Npoi. Ss.
Usermodel;
Using System.Data;


Using System.IO; Namespace Packagemanagement {public class Excelhelper {//Read data public DataTable mexcel2datatable (    string path)//convert Excel to datatable {datatable dt = new DataTable ();  Defines a datatable table DataColumn dc = new DataColumn ();               Defines a datacolumn column isheet sheet = null;             Declare a worksheet Iworkbook book = null; Declare a workbook try {FileStream fs = new FileStream (path, FileMode.Open, fileaccess.re  AD);   Read the Excel file with file flow book = new Hssfworkbook (FS);               Gets stream file converted to Workbook object} catch (Exception ex) {throw ex; Throw table occupancy Issue} if (book. Numberofsheets <= 1)//excel only a singleTable {sheet = book.           Getsheetat (0);  Gets the first table mfilldatatable (book, sheet, DC, DT);
                Call method conversion to DataTable table} else//excel has multiple tables { for (int i = 0; i < book. Numberofsheets; i++)//number of loops Table {sheet = book.                Getsheetat (i);       Get more than one table mfilldatatable (book, sheet, DC, DT);
        Call method Convert to DataTable table} return DT;  } private void Mfilldatatable (Iworkbook book, Isheet sheet, DataColumn DC, DataTable DT) {if (sheet!= null)//If the table is not empty {irow HeaderRow = sheet. GetRow (0); Gets the first row of the table, and the header row if (dt. Columns.count = = 0//If the number of columns in the DataTable is 0, it is the first time the Column object {for (int j = headerrow.fi Rstcellnum; J < Headerrow.lastcellnum;
           J + +)//loop the header row cell         {if (headerrow.cells[j]!= null) {s Tring str = headerrow.cells[j].
                            ToString ();
                            DC = new DataColumn (str); Dt.           Columns.Add (DC); Add Column to DataTable}} for (int n = 1; n < = Sheet. Lastrownum; n++)//loop row Data {irow row = sheet.               GetRow (n);
                        Gets the row data for the worksheet if (row!= null)//row is not empty { DataRow dr = dt.             
                        NewRow (); for (int m = row. Firstcellnum; M < row. Lastcellnum; m++)//Loop cell data {dr[m] = row. CELLS[M].
                        ToString (); } dt. Rows.Add (Dr.        ItemArray);
    Populate Data Table}}        }
        }
    }
} 

'