Some samples and examples about npoi

Recently I have seen many people share their npoi usage.

However, many examples are not complete or have no practical combat effect.

Recently, a VB. Net Program was launched to upgrade the original oledb select sheet $.

It is clear that npoi has better controllability and compatibility.

The Processing Method of the Two-step processing rule does not exclude the dual-image processing and restrictions of Office Excel on the server and client. for example, in the office version, the system administrator issues. and whether the server needs Security Office Excel.

Bytes support the. XLSX format and so on ..

Then the previous generation directly:

 

You must first reference hssf and xssf (for. XLSX support ).

 

 

Lower limit ExcelThere is no need to download the file to the XLSX format. If it is necessary to download the file to XLSX, use the corresponding xssf to name the xssfworkbook under the blank space.

Imports NPOI.HSSF.UserModelImports NPOI.XSSF.UserModel Public Shared Function DownLoadToExcel(ByVal dt As DataTable)        Dim book As HSSFWorkbook = New HSSFWorkbook()        Dim sheet As HSSFSheet = book.CreateSheet("Your_Sheet_Name")        Dim row As HSSFRow = sheet.CreateRow(0)        For i = 0 To dt.Columns.Count - 1            row.CreateCell(i).SetCellValue(dt.Columns(i).ColumnName)        Next        For i = 0 To dt.Rows.Count - 1            Dim row2 As HSSFRow = sheet.CreateRow(i + 1)            For j = 0 To dt.Columns.Count - 1                row2.CreateCell(j).SetCellValue(dt.Rows(i)(j).ToString)            Next        Next        Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream()        book.Write(ms)        HttpContext.Current.Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}.xls", Now.ToString))        HttpContext.Current.Response.BinaryWrite(ms.ToArray())        book = Nothing        ms.Close()        ms.Dispose()    End Function

 

Excel.

We usually need to repeat the insert DB Statement through Excel instead of simply inserting it.

Then we directly retrieve the datatable.

The lofile that supports xlsand .xlsx formats is the htmlinputfile control. When using this method, you can directly upload this object.

I am a web project here. for winform. lofile does not need to be saved before processing. you can directly upload the path. in the same case, change the corresponding httpcontext to context.

Public shared function gettabelfromexcel (byval lofile as htmlinputfile) as datatable dim filepath as string = path. combine (httpcontext. current. server. mappath ("bin"), path. getfilename (lofile. postedfile. filename) dim fileext as string = path. getextension (lofile. postedfile. filename) Try lofile. postedfile. saveas (filepath) dim tempname as string = guid. newguid (). tostring dim tempfilepath as string = pa Th. combine (httpcontext. current. server. mappath ("bin"), tempname & fileext) file. move (filepath, tempfilepath) file. delete (filepath) filepath = tempfilepath if fileext = ". XLSX "then '.xlsx 2007 Try Dim WB as xssfworkbook dim file as system. io. filestream = new system. io. filestream (filepath, system. io. filemode. open, system. io. fileaccess. read) using file WB = new xssfworkbook (File) end Using Dim Sheet As xssfsheet = ctype (WB. getsheetat (0), xssfsheet) dim rows as system. collections. ienumerator = sheet. getrowenumerator () dim dT as datatable = new datatable () For I as integer = 0 to CINT (sheet. getrow (0 ). lastcellnum)-1 DT. columns. add (system. convert. tochar (65 + I ). tostring () Next while rows. movenext () dim row as xssfrow = ctype (rows. current, xssfrow) dim Dr as datarow = DT. newrow () for J as integ ER = 0 to CINT (row. lastcellnum)-1 dim cell as xssfcell = ctype (row. getcell (J), xssfcell) If cell is nothing then DR (j) = nothing else DR (j) = cell. tostring () end if next DT. rows. add (DR) end while DT. rows. removeat (0) return DT catch e as system. exception throw new exception ("the first line of the file does not allow a blank space or the file is in use. Please close the file that is being opened first! "& E. message) end try elseif fileext = ". xls "then' Office 2003 Try Dim WB as hssfworkbook dim file as system. io. filestream = new system. io. filestream (filepath, system. io. filemode. open, system. io. fileaccess. read) using file WB = new hssfworkbook (File) end Using Dim sheet as hssfsheet = ctype (WB. getsheetat (0), hssfsheet) dim rows as system. collections. ienumerator = sheet. getrowenumerator () dim dt S datatable = new datatable () For I as integer = 0 to CINT (sheet. getrow (0 ). lastcellnum)-1 DT. columns. add (system. convert. tochar (65 + I ). tostring () Next while rows. movenext () dim row as hssfrow = ctype (rows. current, hssfrow) dim Dr as datarow = DT. newrow () for J as integer = 0 to CINT (row. lastcellnum)-1 dim cell as hssfcell = ctype (row. getcell (J), hssfcell) If cell is nothing then DR (j) = nothi Ng else DR (j) = cell. tostring () end if next DT. rows. add (DR) end while DT. rows. removeat (0) return DT catch e as system. exception throw new exception ("the first line of the file does not allow a blank space or the file is in use. Please close the file that is being opened first! "& E. Message) end try else throw new exception (" select an Office Excel file! ") End if catch ex as exception throw new exception (" select the Office EXCEL file! "& Ex. Message) Finally file. Delete (filepath) end try end Function

After obtaining the datatable, please handle it yourself.

 

Some samples and examples about npoi