Export the required columns in DataGridView to Excel using Npoi

• What is Npoi?

Npoi is the. NET version of the POI, which means "N" for "Net". Both Npoi and POI are actually well-written class libraries. Class Library Everyone is not unfamiliar, we are in the establishment of the BLL layer, DAL layer, facade layer ..., is not all in the creation of a class library AH. So this thing is about the same as theirs.

• What's npoi doing?

Provides the ability to read and write documents in Microsoft Office format. This means not only for reading and writing to Excel, but also for Word,power point.

• Why do you use Npoi

We can check their own npoi advantages, I can personally feel the point is: In doing the first time room charge system (VB version), we want to export Excel, reference Microsoft Excel Object Library, and apply Excel.Workbook, The Excel.Sheet object completes the export function. However, when Microsoft Office Excel is not installed on your computer, you cannot export it successfully.

• Npoi Practice

First download NPOI.dllfrom the Internet. As I mentioned earlier, the Npoi is a class library, so a. dll file is generated, just like DAL.dll.

This is an example of the application of the reconstruction version of the charge system in the computer room.

Add a reference to the U layer and add the NPOI.dll file. Or put the file directly under the Bin\Debug folder in the U layer.

Next, there is the Code section.

However, I am here to talk about my needs first:

1. If there is a record in DataGridView, it is exported as Excel.
2. Because some of the columns in DataGridView are set to hidden (not visible), but when exported to Excel, these hidden columns are exported, and how to set the hidden columns to not be exported.
3. To enable the reuse of the functionality of the exported Excel, encapsulate it in a class.

：

The general user queries the balance form, it does not need to see the "Checkout Status" column, so I set the Visible property of this column to false:

However, when you export to Excel, the checkout Status column is displayed, or the column appears empty.

The requirements and questions are clear, please see my implementation below:

Specific process:

When you click the Export as Excel button, the Save dialog box pops up, the file name is entered, and the save path is selected, and the data is saved to the Excel table.

Imports Npoi. HSSF. Usermodel ' HSSF is a Microsoft Excel 97-2003 format read-write library for the Imports Npoi. Ss. Usermodel ' Excel common interface and Excel formula calculation engine Imports System.IO ' provides classes that allow read and write files and data streams class public class Exportexcel ' &LT;SUMMARY&G    T ' Export as Excel ' </summary> ' ' <param name= ' DGV ' ></param> parameter = DataGridView ' ' &LT;REMARKS&GT;&L  t;/remarks> public Shared Sub exportexcel (ByVal DGV as System.Windows.Forms.DataGridView) Dim workbook as New        Hssfworkbook ' Workbook Dim sheet As Hssfsheet ' worksheet Dim excelrow As Hssfrow ' rowset                    The loop variable for Dim i As Integer ' column Dim m As Integer ' line loop variable Dim n As Integer ' Column of the loop variable Dim savedialog As Windows.Forms.SaveFileDialog ' dialog box to save the file Dim ms as Memorystrea M ' in-memory data stream Dim fs As FileStream ' file stream Dim filename As String ' Save as file name ' If datIf there is no data in Agridview, Excel If DGV is not exported. Rows.Count = 0 Then MsgBox ("Cannot export to excel! ", MsgBoxStyle.Exclamation," the Warm Tip ") Return End If sheet = workbook. Createsheet ' CREATE table in workbook Excelrow = sheet. CreateRow (0) ' Create header row in worksheet rows (0) ' To add column headings per column for i = 0 to DGV. Columns.count-1 ' writes the title of the visible column to the cell (if a column is not visible, the column is hollowed out in the exported Excel) if DGV. Columns (i). Visible = True then Excelrow. Createcell (i). Setcellvalue (DGV. Columns (i). HeaderText) End If Next ' adds additional rows and columns for m = 1 to DGV. Rows.Count Excelrow = sheet. CreateRow (M) ' Each traversing a row, creates a row for n = 0 to DGV in sheet. Columns.count-1 ' traverse all columns in each row ' If the column is visible, export the value of the column cell if DGV. Columns (n). Visible = True then Excelrow. Createcell (n). Setcellvalue (DGV. Rows (m-1). Cells (n). value.tostring) End If ' If the column is not visible, then the columnSet to hide If DGV. Columns (n). Visible = False then sheet. Setcolumnhidden (n, True) End If Next Next savedialog = New Windows.Forms.SaveFileD Ialog ' Save file dialog box Savedialog.defaultext = "xls" ' Set default file name extension Savedi Alog.                                      Filter = "Excel file |*.xls" ' File type Savedialog.showdialog () ' Pop-up save file Dialog filename = Savedialog.filename ' to the filename entered in the dialog box to filename ms = New Memo Rystream () fs = New FileStream (filename, filemode.create) workbook. Write (ms) ' writes data workbook. Write (FS) fs. Close () ' Closes file workbook = Nothing Ms. Close () Ms. Dispose () End subend Class

Call:

' <summary> '    export to Excel ' </summary> ' '    <param name= ' sender ' ></param> "'    <param name=" E "></param>"    <remarks></remarks>    Private Sub Btnexportexcel_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnexportexcel.click        ' When there is a record in DataGridView,        if DtgrdRecord.Columns.Count <> 0 then            ' Sets the Invisible column            dtgrdrecord.columns (4). Visible = False        end If        exportexcel.exportexcel (dtgrdrecord)    end Sub

Effect:

Description:setcolumnhidden can set a column to hide so that when you open Excel, we don't see the hidden columns, so the user looks comfortable, and of course if you hide the columns in Excel, is set to unhide, the column is still displayed. The code above can be implemented without exporting the values in an invisible column to Excel and hiding this column.