VB.NET NPOI快速导入导出Excel
记得提前先使用NeGet程序包提前导出NPOI相关dll引用
DataGridView导出Excel
1 ''' <summary>
2 ''' 获取EXCEL表格真正的值
3 ''' </summary>
4 ''' <param name="cell"></param>
5 ''' <returns>不同的类型处理后的值</returns>
6 ''' <remarks></remarks>
7 Private Function GetCellValue(cell As ICell) As String
8 If cell Is Nothing Then
9 Return String.Empty
10 End If
11 Select Case cell.CellType
12 Case CellType.Blank
13 '空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
14 Return String.Empty
15 Case CellType.[Boolean]
16 'bool类型
17 Return cell.BooleanCellValue.ToString()
18 Case CellType.[Error]
19 Return cell.ErrorCellValue.ToString()
20 Case CellType.Numeric
21 '数字类型
22 If HSSFDateUtil.IsCellDateFormatted(cell) Then
23 '日期类型
24 Return cell.DateCellValue.ToString("yyyy/MM/dd") '格式下化下日期,否则会带上时间--可以针对修改需求
25 Else
26 '其它数字
27 Return cell.NumericCellValue.ToString()
28 End If
29 Case CellType.Unknown
30 '无法识别类型
31 '默认类型
32 Return cell.ToString()
33 '
34 Case CellType.[String]
35 'string 类型
36 Return cell.StringCellValue
37 Case CellType.Formula
38 '带公式类型
39 Try
40 'Dim e As New HSSFFormulaEvaluator(cell.Sheet.Workbook)
41 'e.EvaluateInCell(cell)
42 Return cell.StringCellValue '带出公式会出异常，故只返回字符串--可以针对修改调试
43 Catch
44 Return cell.NumericCellValue.ToString()
45 End Try
46 End Select
47 End Function 1 ''' <summary>
2 ''' 导出为Excel
3 ''' </summary>
4 ''' <param name="dgv"></param>参数为DataGridView
5 ''' <remarks></remarks>
6 Public Sub ExportExcel(ByVal dgv As System.Windows.Forms.DataGridView)
7 Dim workbook As New HSSFWorkbook '工作簿
8 Dim sheet As HSSFSheet '工作表
9 Dim excelrow As HSSFRow '行集
10
11 Dim i As Integer '列的循环变量
12 Dim m As Integer '行的循环变量
13 Dim n As Integer '列的循环变量
14
15 Dim saveDialog As Windows.Forms.SaveFileDialog '保存文件的对话框
16
17 Dim ms As MemoryStream '内存中的数据流
18 Dim fs As FileStream '文件流
19 Dim filename As String '保存时的文件名称
20
21
22
23 '如果DataGridView中没有数据，则不导出Excel
24 If dgv.Rows.Count = 0 Then
25 MsgBox("无法导出为Excel！", MsgBoxStyle.Exclamation, "温馨提示")
26 Return
27 End If
28
29 sheet = workbook.CreateSheet '在工作簿中创建表
30 excelrow = sheet.CreateRow(0) '在工作表中创建标题行Row（0）
31
32 '添加每列列标题
33 For i = 0 To dgv.Columns.Count - 1
34 '将可见的列的标题写到单元格中（如果某列不可见，那么会在导出的Excel中空出这列）
35 If dgv.Columns(i).Visible = True Then
36 excelrow.CreateCell(i).SetCellValue(dgv.Columns(i).HeaderText)
37 End If
38 Next
39 '添加其他行和列
40 For m = 1 To dgv.Rows.Count
41 excelrow = sheet.CreateRow(m) '每遍历一行，则在sheet中创建一行
42 For n = 0 To dgv.Columns.Count - 1 '遍历每一行中的所有列
43 '如果该列可见，则导出该列单元格的值
44 If dgv.Columns(n).Visible = True Then
45 excelrow.CreateCell(n).SetCellValue(dgv.Rows(m - 1).Cells(n).Value.ToString)
46 End If
47 '如果该列不可见，则将该列设置为隐藏
48 If dgv.Columns(n).Visible = False Then
49 sheet.SetColumnHidden(n, True)
50 End If
51 Next
52 Next
53
54
55
56 saveDialog = New Windows.Forms.SaveFileDialog '保存文件对话框
57 saveDialog.DefaultExt = "xls" '设置默认文件扩展名
58 saveDialog.Filter = "Excel文件|*.xls" '文件类型
59 saveDialog.ShowDialog() '弹出保存文件对话框
60
61 filename = saveDialog.FileName '将对话框中输入的文件名赋给filename
62 If filename = "" Then '修复一个异常,如果文件名不写,直接取消导出的话,会报错
63 Return
64 End If
65 ms = New MemoryStream()
66 fs = New FileStream(filename, FileMode.Create)
67 workbook.Write(ms) '写入数据
68 workbook.Write(fs)
69 fs.Close() '关闭文件
70 workbook = Nothing
71 ms.Close()
72 ms.Dispose()
73 MsgBox("导出完成")
74 End SubDataGirdview导入Excel
1 ''' <summary>
2 ''' 获取excel内容
3 ''' </summary>
4 ''' <param name="filePath">excel文件路径</param>
5 ''' <returns></returns>
6 Public Function ImportExcel(filePath As String) As DataTable
7 Dim dt As New DataTable()
8 Using fsRead As FileStream = System.IO.File.OpenRead(filePath)
9 Dim wk As IWorkbook = Nothing
10 '获取后缀名
11 Dim extension As String = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower()
12 '判断是否是excel文件
13 If extension = ".xlsx" OrElse extension = ".xls" Then
14 '判断excel的版本
15 If extension = ".xlsx" Then
16 wk = New XSSFWorkbook(fsRead)
17 Else
18 wk = New HSSFWorkbook(fsRead)
19 End If
20
21 '获取第一个sheet
22 Dim sheet As ISheet = wk.GetSheetAt(0)
23 '获取第一行
24 Dim headrow As IRow = sheet.GetRow(0)
25 '创建列
26 For i As Integer = headrow.FirstCellNum To headrow.Cells.Count - 1
27 Dim datacolum = New DataColumn(headrow.GetCell(i).StringCellValue)
28 'Dim datacolum As New DataColumn("F" + CStr(i + 1))
29 dt.Columns.Add(datacolum)
30 Next
31 '读取每行,从第二行起
32 For r As Integer = 1 To sheet.LastRowNum
33 Dim result As Boolean = False
34 Dim dr As DataRow = dt.NewRow()
35 '获取当前行
36 Dim row As IRow = sheet.GetRow(r)
37 '读取每列
38 For j As Integer = 0 To row.Cells.Count - 1
39 Dim cell As ICell = row.GetCell(j)
40 '一个单元格
41 dr(j) = GetCellValue(cell)
42 '获取单元格的值
43 '全为空则不取
44 If dr(j).ToString() <> "" Then
45 result = True
46 End If
47 Next
48 If result = True Then
49 '把每行追加到DataTable
50 dt.Rows.Add(dr)
51 End If
52 Next
53 End If
54 End Using
55 Return dt
56 End Function