Common data import solution for Excel and Excel data import Database

The project is commonly used to import data into Excel, the Excel data into the database function, has also looked for relevant content, will have used the scheme summed up. Programme I

Npoi

Npoi is a. NET version of the POI project. POI is an Open-source Java project that reads and writes Microsoft OLE2 component documents such as Excel, Word, and so on. Using Npoi, you can read and write Word/excel documents on machines that do not have Office or the environment in which they are installed. Npoi is built on top of the POI 3.x version, and it can read and write Word/excel documents without installing Office.

Advantage

(i) Traditional operation Excel encountered problems: 1, if it is. NET, you need to install Office on the server, and update it in time to prevent vulnerabilities, you also need to set permissions to allow. NET access to COM + if problems with the export process can cause server downtime. 2, Excel will only contain a number of columns for type conversion, is text-type, Excel will convert it to numeric type, such as number 000123 will become 123. 3, when the export, if the field content with "-" or "=" beginning, Excel will take it as a formula, will be an error. 4, Excel will be based on the first 8 lines of Excel file analysis of data types, if it happens to you in the first 8 rows of a column is just a number, it will think that the column as a numeric type, automatically converted to a similar 1.42702E+17 format, the date column into a date and number. (ii) Advantages of using Npoi 1, you can use the Framework 2 completely for free, including most of the Excel features (cell styles, data formats, formulas, and so on) 3, professional Technical support Services (24*7 All-weather) (not free) 4, supporting the processing of file formats including XLS, XLSX, docx . 5, the adoption of interface-oriented design framework (can view Npoi. The name space of the SS) 6, supports both import and export of files 7, also supports the xlsx and docx formats based on. NET 2.0 (. NET 4.0) 8, a large number of successful and authentic tests from around the world cases 9, a large number of instance code 11, you do not need to install Microsoft's O on the server ffice, you can avoid copyright problems. 12, the use of the Office PIA API more convenient, more humane. 13, you do not have to spend great effort to maintain Npoi,npoi team will continue to update, improve npoi, absolute cost of the province. Npoi is powerful not because it supports exporting Excel, but because it supports importing Excel and "understands" the OLE2 document structure, which is one of the weaker aspects of other Excel reading and writing cubby. Often, reading and understanding structures is far more complex than exporting, because the import you have to assume that everything is possible, and generate you just make sure that you meet your own needs, if the import requirements and build requirements than two sets, then the generation requirements are usually a subset of the import requirements, This law is not only reflected in Excel reading and writing library, but also in the PDF read-write library, most of the PDF libraries currently available only support generation, do not support import.

Constitute

Npoi 1.2.x is mainly composed of Poifs, DDF, HPSF, HSSF, SS, util six parts.

Npoi. Poifs

Npoi. DDF

Npoi. HPSF

Npoi. HSSF

Npoi. SS

Npoi. The Util

The latest version of the Npoi component Npoi 1.x is Npoi 1.2.5, which includes the following features: 1, read and write OLE2 document 2, read and write Docummentsummaryinformation and SummaryInformation 3, Read-Write 4 based on Littleendian, read and write Excel Biff format 5, identify and read common record in Excel Biff, such as Rowrecord, Stylerecord, Extendedformatrecord 6, Support to set cell height, width, style, etc. 7, support the call to some Excel built-in functions, such as SUM, COUNTIF and calculation symbol 8, support in the generated XLS embedded printing settings, such as Horizontal/portrait printing, scaling, use of paper. Npoi 2.0 consists mainly of SS, HPSF, DDF, HSSF, XWPF, XSSF, Openxml4net, Openxmlformats, and the specific list is as follows:

Assembly name	Module/Namespace	Description
Npoi. Dll	Npoi. Poifs	Ole2/activex document Properties read-write Library
Npoi. Dll	Npoi. DDF	Microsoft Office drawing read and write library
Npoi. Dll	Npoi. HPSF	Ole2/activex document Read/write Library
Npoi. Dll	Npoi. HSSF	Microsoft Excel BIFF (Excel 97-2003, doc) format reading and writing library
Npoi. Dll	Npoi. SS	Excel common interface and Excel formula calculation engine
Npoi. Dll	Npoi. Util	The base Class library, which provides a number of practical features that can be used in the development of other read/write file format projects
Npoi. OOXML. Dll	Npoi. Xssf	Excel 2007 (XLSX) format read-write library
Npoi. OOXML. Dll	Npoi. Xwpf	Word 2007 (docx) format read-write library
Npoi. OpenXml4Net.DLL	Npoi. Openxml4net	OPENXML bottom Zip Packet read-write library
Npoi. OpenXmlFormats.DLL	Npoi. Openxmlformats	Microsoft Office OpenXML Object Relational Library

(The above content from Baidu Encyclopedia) from the table that NPOI component has been supported excel2007, remember to use the time to support only excel2003. Haven't studied this thing in a long time.

Case

Website address: http://npoi.codeplex.com/, you can download the npoi2.x version from the official website.

First introduce

ICSharpCode.SharpZipLib.dll

NPOI.dll

Npoi. OOXML.dll

Npoi. OpenXml4Net.dll

Npoi. OpenXmlFormats.dll

Then introduce namespaces:

Using Npoi. XSSF. Usermodel; Using Npoi. Ss. Usermodel; Using Npoi. HSSF. Usermodel;