Comparison of third-party Excel class libraries on the. NET platform

 

From http://topming.com/636.html

In a recent project, datatable and Excel must be converted to each other. Excel must be a real xls, rather than a CVS, TVs, or HTML-based XLS. Considering runningProgramNot necessarily dedicated office on the machine, there is no EXCEL program, otherwise, you have to install an office to install a program, then you have to consider copyright, too much trouble. A total of three free libraries are used:

Myxls (http://sourceforge.net/projects/myxls ),

(Http://sourceforge.net/projects/koogra ),

Npoi (http://npoi.codeplex.com /)

The three processes are very fast. After comparison, we found that the functions of the three are not the same:

Myxls is a free open-source library that focuses on Excel output. It can be set to a single cell, but the reading function is weak.

Koogra is the opposite of myxls. It is a very easy-to-use Excel reading class library. However, during the test, koogra cannot read the xls file output by myxls! I don't know if I haven't figured out the reason for the two class libraries. In short, I feel a little sorry.

Npoi is a POI on the. NET platform. Currently, the stable version is an open-source project that can generate real Excel files and implement read/write. The project address is http://npoi.codeplex.com /. Functions include input and output, formula calculation, and advanced cell styles. The class libraries include:

Npoi. util 1.2.1 Basic Assistant class library
Npoi. poifs 1.2.1 ole2 format read/write Library
Npoi. DDF 1.2.1 drawing format read/write Library
Npoi. SS 1.2.1 formula evaluation Library
Npoi. hpsf 1.2.1 summary information and document summary information read/write Library
Npoi. hssf 1.2.1 Excel BIFF format read/write Library

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899

Using System; Using System. Collections. Generic; Using System. LINQ; Using System. text; Using System. IO; Using System. Data; // Reference Using Npoi. hssf. usermodel; Using Npoi. hpsf; Using Npoi. poifs. filesystem; Namespace Oraclekity { Class Datatableexcel { Public Bool Datatabletoexcel (system. Data. datatable dtsource, String Filepath) { Try { // Only one sheet is written to the document. // Create a workbook Hssfworkbook workbook = New Hssfworkbook (); System. Data. datatable dt = dtsource; // Create a sheet Hssfsheet sheet = Workbook. createsheet ( "Sheet1" ); // To avoid the date format being automatically replaced by Excel, set format to @ to indicate that the first rate is regarded as text. Hssfcellstyle textstyle = Workbook. createcellstyle (); Textstyle. dataformat = hssfdataformat. getbuiltinformat ( "@" ); // Use column name as the column name List columns = New List (); For ( Int Colindex = 0; colindex <DT. Columns. Count; colindex ++) { String Name = DT. Columns [colindex]. columnname; Hssfcell cell = sheet. createrow (0). createcell (colindex ); Cell. setcellvalue (name ); Cell. cellstyle = textstyle; Columns. Add (name ); } // Create a content Column For ( Int Row = 0; row <DT. Rows. Count; row ++) { Datarow DR = DT. Rows [row]; For ( Int Col = 0; Col <columns. Count; Col ++) { String Data = Dr [columns [col]. tostring (); Hssfcell cell = sheet. createrow (row + 1). createcell (COL ); Cell. setcellvalue (data ); Cell. cellstyle = textstyle; } } // Write an Excel file Filestream file = New Filestream (filepath, filemode. openorcreate ); Workbook. Write (File ); File. Close (); Return True ; } Catch { Return False ; } } Public System. Data. datatable readexceltodatatable ( String Filepath) { // Open the Excel file to be read Filestream file = New Filestream (filepath, filemode. Open ); // Read the Excel file Hssfworkbook workbook = New Hssfworkbook (File ); File. Close (); Hssfsheet sheet = Workbook. getsheetat (0 ); // Create a new table Datatable dtnew = New Datatable ();; Hssfrow ROW = sheet. getrow (0 ); // Read The 0th column as column name For ( Int Columnindex = 0; columnindex <row. lastcellnum; columnindex ++) { Datacolumn Dc = New Datacolumn (row. getcell (columnindex). tostring ()); Dtnew. Columns. Add (DC ); } Int Rowid = 1; // The first column is the data and the last row is read. While (Rowid <= sheet. lastrownum) { Datarow newrow = dtnew. newrow (); // Read all columns For ( Int Colindex = 0; colindex <dtnew. Columns. Count; colindex ++) { Newrow [dtnew. Columns [colindex] = sheet. getrow (rowid). getcell (colindex). tostring (); } Dtnew. Rows. Add (newrow ); Rowid ++; } Return Dtnew; } } }

If you want to make an Excel input and output, consider which library to use.