Asp.net: Import common data to Excel

Introduction

The project often uses the function of importing data into Excel to import data in Excel to the database. You have also searched for related content and summarized the previously used solutions.

Solution 1

NPOI

NPOI is the. NET version of the POI project. POI is an open-source Java project for reading and writing Microsoft OLE2 component documents such as Excel and WORD.

With NPOI, you can read and write WORD/EXCEL documents on a machine without an Office or corresponding environment. NPOI is built on POI 3.x. It can read and write Word/Excel documents without installing Office.

Advantages

(1) problems encountered in traditional Excel operations:

1. If yes. NET, you need to install the 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 a problem occurs during the export process, the server may be down.

2. In Excel, only numeric columns are converted to the type, which is a text type. In Excel, numeric columns are converted into numeric columns. For example, the number 000123 is converted to 123.

3. During export, if the field content starts with "-" or "=", Excel regards it as a formula and reports an error.

4. Excel analyzes the data type based on the first eight rows of the Excel file. If a column in the first eight rows is only a number, it considers the column as a numeric type, the column is automatically converted to a format similar to 1.42702E + 17, and the date column is changed to contain dates and numbers.

(2) advantages of using NPOI

1. You can use this framework for free.

2. contains most EXCEL features (cell style, data format, formulas, etc)

3. Professional Technical Support Services (24x7 around the clock) (not free of charge)

4. Supported file formats include xls, xlsx, and docx.

5. Adopt an interface-oriented design architecture (you can view the NPOI. SS namespace)

6. File Import and Export are also supported.

7. Based on. net 2.0, xlsx and docx formats are also supported (of course. net 4.0 is also supported)

8. A large number of successful and authentic test Cases from around the world

9. A large number of instance Codes

11. You do not need to install Microsoft Office on the server to avoid copyright issues.

12. It is more convenient and user-friendly than Office PIA APIs.

13. You do not need to make great efforts to maintain NPOI. The NPOI Team will continuously update and improve NPOI, saving costs absolutely.

NPOI is powerful not because it supports exporting Excel, but because it supports importing Excel and "understands" OLE2 document structure, which is also a weak aspect of other Excel read/write databases. Generally, reading and understanding the structure is far more complicated than exporting, because you must assume that everything is possible for importing, and generating the structure is sufficient to meet your own needs, if you compare the import requirement with the generation requirement, the generation requirement is usually a subset of the import requirement. This rule is not only reflected in the Excel read/write database, but also in the pdf read/write database, currently, most pdf libraries on the market only support generation and cannot be imported.

Composition

NPOI 1.2.x consists of POIFS, DDF, HPSF, HSSF, SS, and Util.

NPOI. POIFS

OLE2/ActiveX document attribute read/write Library

NPOI. DDF

Microsoft Office Drawing read/write Database

NPOI. HPSF

OLE2/ActiveX document read/write Library

NPOI. HSSF

Microsoft Excel BIFF (Excel 97-2003) format read/write Database

NPOI. SS

Excel public interface and Excel Formula computing Engine

NPOI. Util

The Basic Library provides many practical functions and can be used for development of other read/write file format projects.

NPOI Components

The latest version of NPOI 1. x is NPOI 1.2.5, which includes the following features:

1. Read and Write OLE2 documents

2. Read and Write logs mentsummaryinformation and SummaryInformation.

3. LittleEndian-based byte read/write

4. read/write Excel BIFF format

5. Recognize and read common records in Excel BIFF, such as RowRecord, StyleRecord, and ExtendedFormatRecord.

6. You can set the height, width, and style of cells.

7. Some Excel built-in functions, such as sum, countif, and computing symbol, can be called.

8. Supports embedded printing settings in the generated XLS, such as horizontal/vertical printing, scaling, and used paper.

NPOI 2.0 consists of SS, HPSF, DDF, HSSF, XWPF, XSSF, OpenXml4Net, and OpenXmlFormats. The specific list is as follows:

Assembly name module/namespace description

NPOI. DLL

NPOI. POIFS

OLE2/ActiveX document attribute read/write Library

NPOI. DLL

NPOI. DDF

Microsoft Office Drawing read/write Database

NPOI. DLL

NPOI. HPSF

OLE2/ActiveX document read/write Library

NPOI. DLL

NPOI. HSSF

Microsoft Excel BIFF (Excel 97-2003, doc) format read/write Database

NPOI. DLL

NPOI. SS

Excel public interface and Excel Formula computing Engine

NPOI. DLL

NPOI. Util

The Basic Library provides many practical functions and can be used for 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 underlying zip package read/write Library

NPOI. OpenXmlFormats. dll npoi. OpenXmlFormats Microsoft Office OpenXml Object Relational Database

(The above content is from Baidu encyclopedia) We can see from the above table that the NPOI component already supports excel2007. Remember that previously, only excel2003 was supported. I haven't studied this thing for a long time.

Case

Official website address: http://npoi.codeplex.com/. you can download npoi2.xfiles from the official website.

First introduce

ICSharpCode. SharpZipLib. dll

NPOI. dll

NPOI. OOXML. dll

NPOI. OpenXml4Net. dll

NPOI. OpenXmlFormats. dll

Then introduce the namespace:

Using NPOI. XSSF. UserModel;

Using NPOI. SS. UserModel;

Using NPOI. HSSF. UserModel;

Auxiliary class

Using NPOI. XSSF. UserModel;

Using NPOI. SS. UserModel;

Using NPOI. HSSF. UserModel;

Using System;

Using System. Collections. Generic;

Using System. Data;

Using System. IO;

Using System. Linq;

Using System. Text;

Using System. Threading. Tasks;

Using NPOI. SS. Formula. Eval;

Namespace Wolfy. Common

{

///

 

/// Use the NPOI component

/// ICSharpCode. SharpZipLib. dll/NPOI. dll/NPOI. OOXML. dll/NPOI. OpenXml4Net. dll/NPOI. OpenXmlFormats. dll must be introduced.

/// Office2007

///

 

Public class NPOIExcelHelper

{

///

 

/// Read the data in the Excel file to the able.

///

 

///

///

Public static DataTable Excel2DataTable (string file, string sheetName, string tableName)

{

DataTable dt = new DataTable ();

IWorkbook workbook = null;

Using (FileStream fs = new FileStream (file, FileMode. Open, FileAccess. Read ))

{

// Office2003 HSSFWorkbook

Workbook = new XSSFWorkbook (fs );

}

ISheet sheet = workbook. GetSheet (sheetName );

Dt = Export2DataTable (sheet, 0, true );

Return dt;

}

///

 

/// Import the data in the specified sheet to the able

///

 

///Sheet to be exported

///The row number of the column header.-1 does not have a column header.

///

///

Private static DataTable Export2DataTable (ISheet sheet, int HeaderRowIndex, bool needHeader)

{

DataTable dt = new DataTable ();

XSSFRow headerRow = null;

Int cellCount;

Try

{

If (HeaderRowIndex <0 |! NeedHeader)

{

HeaderRow = sheet. GetRow (0) as XSSFRow;

CellCount = headerRow. LastCellNum;

For (int I = headerRow. FirstCellNum; I <= cellCount; I ++)

{

DataColumn column = new DataColumn (Convert. ToString (I ));

Dt. Columns. Add (column );

}

}

Else

{

HeaderRow = sheet. GetRow (HeaderRowIndex) as XSSFRow;

CellCount = headerRow. LastCellNum;

For (int I = headerRow. FirstCellNum; I <= cellCount; I ++)

{

ICell cell = headerRow. GetCell (I );

If (cell = null)

{

Break; // jump out of the loop at the end

}

Else

{

DataColumn column = new DataColumn (headerRow. GetCell (I). ToString ());

Dt. Columns. Add (column );

}

}

}

Int rowCount = sheet. LastRowNum;

For (int I = HeaderRowIndex + 1; I <= sheet. LastRowNum; I ++)

{

XSSFRow row = null;

If (sheet. GetRow (I) = null)

{

Row = sheet. CreateRow (I) as XSSFRow;

}

Else

{

Row = sheet. GetRow (I) as XSSFRow;

}

DataRow dtRow = dt. NewRow ();

For (int j = row. FirstCellNum; j <= cellCount; j ++)

{

If (row. GetCell (j )! = Null)

{

Switch (row. GetCell (j). CellType)

{

Case CellType. Boolean:

DtRow [j] = Convert. ToString (row. GetCell (j). BooleanCellValue );

Break;

Case CellType. Error:

DtRow [j] = ErrorEval. GetText (row. GetCell (j). ErrorCellValue );

Break;

Case CellType. Formula:

Switch (row. GetCell (j). CachedFormulaResultType)

{

Case CellType. Boolean:

DtRow [j] = Convert. ToString (row. GetCell (j). BooleanCellValue );

Break;

Case CellType. Error:

DtRow [j] = ErrorEval. GetText (row. GetCell (j). ErrorCellValue );

Break;

Case CellType. Numeric:

DtRow [j] = Convert. ToString (row. GetCell (j). NumericCellValue );

Break;

Case CellType. String:

String strFORMULA = row. GetCell (j). StringCellValue;

If (strFORMULA! = Null & strFORMULA. Length> 0)

{

DtRow [j] = strFORMULA. ToString ();

}

Else

{

DtRow [j] = null;

}

Break;

Default:

DtRow [j] = "";

Break;

}

Break;

Case CellType. Numeric:

If (DateUtil. IsCellDateFormatted (row. GetCell (j )))

{

DtRow [j] = DateTime. FromOADate (row. GetCell (j). NumericCellValue );

}

Else

{

DtRow [j] = Convert. ToDouble (row. GetCell (j). NumericCellValue );

}

Break;

Case CellType. String:

String str = row. GetCell (j). StringCellValue;

If (! String. IsNullOrEmpty (str ))

{

DtRow [j] = Convert. ToString (str );

}

Else

{

DtRow [j] = null;

}

Break;

Default:

DtRow [j] = "";

Break;

}

}

}

Dt. Rows. Add (dtRow );

}

}

Catch (Exception)

{

Return null;

}

Return dt;

}

///

 

/// Import the data in the DataTable into an Excel file

///

 

///

///

Public static void DataTable2Excel (DataTable dt, string file, string sheetName)

{

IWorkbook workbook = new XSSFWorkbook ();

ISheet sheet = workbook. CreateSheet (sheetName );

IRow header = sheet. CreateRow (0 );

For (int I = 0; I <dt. Columns. Count; I ++)

{

ICell cell = header. CreateCell (I );

Cell. SetCellValue (dt. Columns [I]. ColumnName );

}

// Data

For (int I = 0; I <dt. Rows. Count; I ++)

{

IRow row = sheet. CreateRow (I + 1 );

For (int j = 0; j <dt. Columns. Count; j ++)

{

ICell cell = row. CreateCell (j );

Cell. SetCellValue (dt. Rows [I] [j]. ToString ());

}

}

MemoryStream stream = new MemoryStream ();

Workbook. Write (stream );

Byte [] buffer = stream. ToArray ();

Using (FileStream fs = new FileStream (file, FileMode. Create, FileAccess. Write ))

{

Fs. Write (buffer, 0, buffer. Length );

Fs. Flush ();

}

}

///

 

/// Obtain the cell type

///

 

///

///

Private static object GetValueType (XSSFCell cell)

{

If (cell = null)

{

Return null;

}

Switch (cell. CellType)

{

Case CellType. Blank:

Return null;

Case CellType. Boolean:

Return cell. BooleanCellValue;

Case CellType. Error:

Return cell. ErrorCellValue;

Case CellType. Numeric:

Return cell. NumericCellValue;

Case CellType. String:

Return cell. StringCellValue;

Case CellType. Formula:

Default:

Return "=" + cell. StringCellValue;

}

}

}

}