. Net use npoi to export/import data to an Excel file

Currently, most software supports the Excel Import and Export function. Since the data in the software is exported to excel, the finance specialist can conveniently operate the data. This can save a lot of work. In addition, the data import function allows users to import tens of thousands of records to the database of the software system without having to spend time enabling the software. This greatly reduces the workload of users. Otherwise, users can only enter entries one by one. If the entries are not saved, they can only ....., Everyone knows.

Let's take a look at how to use npoiProgramThe Excel file for data export in. Of course, there are many ways to export data from a program to an Excel file. However, I think the biggest advantage of npoi is that even if your favorite computer does not have office or Wps installed, it can still be exported normally. However, if you use the office component class library, you will not be able to compliment it...

Let's take a look at the great charm of npoi!

Here I will write a small example to help you understand the usage of npoi.

Step 2: Create a New winfrom form application named exportorimport. Put a dview control and a button control on the form.

Step 2: Create a new Lib folder in the current project, copy the following class libraries to the folder, right-click "Reference" → "add reference", and find the DLL files of Lib, complete adding.

Step 3: Introduce the following namespace in the project:

Using npoi. hssf. usermodel;

Using npoi. hpsf;

Using npoi;

Using system. IO;

Step 4: read data from the database to the datagridview using ADO. net

String STR = "Data Source =.; initial catalog = myschool; uid = sa ";

Sqlconnection con = new sqlconnection (STR );

String SQL = "select studentno, studentname, address, birthday from student ";

Sqldataadapter da = new sqldataadapter (SQL, con );

Dataset DS = new dataset ();

Da. Fill (DS, "student ");

Datagridview1.datasource = Ds. Tables [0];

Step 5: export data from the datagridview to an Excel file

Let's just move on.Code

// A save dialog box is displayed through the savefiledialog class.

SavefiledialogSFD = new savefiledialog ();

// Set the file storage type. The Excel file is selected by default.

SFD. Filter = "Excel file | *. xls ";

// Set the name of the file to be saved by default.

SFD. filename = "student information table ";

// If the user clicks the OK button in the SAVE dialog box

If (SFD. showdialog () = dialogresult. OK)

{

// Obtain the Excel file name

String filename = SFD. filename;

// Obtain the student list

// List <student> List = maid as list <student>;

// Create an empty Excel file in the memory

Hssfworkbook workbook = new hssfworkbook ();

// Create a worksheet for hssfsheet in an Excel file

Hssfsheet sheet = Workbook. createsheet ("Students ");

// Add a row to the worksheet

Hssfrow row1 = sheet. createrow (0 );

// Create a column on the added airline ticket

Hssfcell cell1 = row1.createcell (0, hssfcell. cell_type_string );

// Set the value of this column

Cell1.setcellvalue ("student ID ");

 

Cell1 = row1.createcell (1, hssfcell. cell_type_string );

Cell1.setcellvalue ("Student name ");

 

Cell1 = row1.createcell (2, hssfcell. cell_type_string );

Cell1.setcellvalue ("residential address ");

 

Cell1 = row1.createcell (3, hssfcell. cell_type_string );

Cell1.setcellvalue ("Date of Birth ");

// Traverse all columns in the datagridview and add the columns to the Excel worksheet

For (INT I = 1; I <= maid. Count; I ++)

{

// Set the font

Hssffont font = Workbook. createfont ();

// Font name

Font. fontname = "文 ";

// Set the font size

Font. fontheightinpoints = 25;

 

// Set the Column Style

Hssfcellstyle style1 = Workbook. createcellstyle ();

// Set the column background color

Style1.fillforegroundcolor = npoi. hssf. util. hssfcolor. Orange. index;

// Set the filled border Style

Style1.fillpattern = hssfcellstyle. solid_foreground;

// Set the font display style

Style1.setfont (font );

 

Hssfcellstyle style2 = Workbook. createcellstyle ();

Style2.fillforegroundcolor = npoi. hssf. util. hssfcolor. Yellow. index;

Style2.fillpattern = hssfcellstyle. solid_foreground;

 

Hssfrow ROW = sheet. createrow (I );

Hssfcell cell = row. createcell (0, hssfcell. cell_type_numeric );

Cell. cellstyle = style1;

Cell. setcellvalue (maid [I-1]. cells [0]. value. tostring ());

 

Cell = row. createcell (1, hssfcell. cell_type_string );

Cell. cellstyle = style1;

Cell. setcellvalue (maid [I-1]. cells [1]. value. tostring ());

 

Cell = row. createcell (2, hssfcell. cell_type_string );

Cell. cellstyle = style2;

Cell. setcellvalue (maid [I-1]. cells [2]. value. tostring ());

 

Cell = row. createcell (3, hssfcell. cell_type_string );

Cell. cellstyle = style2;

Cell. setcellvalue (maid [I-1]. cells [3]. value. tostring ());

}

Using (filestream FS = new filestream (filename, filemode. openorcreate ))

{

// Write the content to the hard disk

Workbook. Write (FS );

}

MessageBox. Show ("exported successfully! ");

}

In fact, there is another way of thinking, which is left for everyone to think about.

Next, let's take a look at how to import an existing Excel file to the dview.

Since the export process is analyzed in detail, the import process is only a reverse process, so you can directly read the code here.

Void initializeworkbook (string path)

{

// Read the template via filestream, it is suggested to use fileaccess. Read to prevent file lock.

// Book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.

Using (filestream file = new filestream (path, filemode. Open, fileaccess. Read ))

{

Hssfworkbook = new hssfworkbook (File );

}

}

Dataset DS = new dataset ();

Void converttodatatable ()

{

Hssfsheet sheet = hssfworkbook. getsheetat (0 );

System. Collections. ienumerator rows = sheet. getrowenumerator ();

Datatable dt = new datatable ();

For (Int J = 0; j <5; j ++)

{

DT. Columns. Add (convert. tochar (INT) 'A') + J). tostring ());

}

 

While (rows. movenext ())

{

Hssfrow ROW = (hssfrow) rows. Current;

Datarow DR = DT. newrow ();

 

For (INT I = 0; I <row. lastcellnum; I ++)

{

Hssfcell cell = row. getcell (I );

If (cell = NULL)

{

Dr [I] = NULL;

}

Else

{

Dr [I] = cell. tostring ();

}

}

DT. Rows. Add (DR );

}

DS. Tables. Add (DT );

}

// Import from Excel

Private void button2_click (Object sender, eventargs E)

{

Initializeworkbook ("student information table .xls ");

Converttodatatable ();

 

Dgvexport. datasource = Ds. Tables [0];

}

Now, write it here! Hope to help you.