|
1
2
3
4
5
6
7
8
9
10 |
using
System;using
System.Collections.Generic;using
System.Text;using
System.IO;using
NPOI.SS.UserModel; //NPOIusing
NPOI.HSSF.Util; //NPOIusing
NPOI.HSSF.UserModel; //NPOIusing
NPOI.XSSF.UserModel; //NPOIusing
System.Data.SqlClient;using
System.Data; |
请先下载NPOI的所有dll,然后Add reference.
1.导入
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144 |
/// <summary>/// Excel某sheet中内容导入到DataTable中/// 区分xsl和xslx分别处理/// </summary>/// <param name="filePath">Excel文件路径,含文件全名</param>/// <param name="sheetName">此Excel中sheet名</param>/// <returns></returns>public
DataTable ExcelSheetImportToDataTable(string
filePath, string
sheetName){ DataTable dt = new
DataTable(); if
(Path.GetExtension(filePath).ToLower() == ".xls".ToLower()) {//.xls #region .xls文件处理:HSSFWorkbook try { using
(FileStream file = new
FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new
HSSFWorkbook(file); } } catch
(Exception e) { throw
e; } ISheet sheet = hssfworkbook.GetSheet(sheetName); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 for
(int
j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //SET EVERY COLUMN NAME HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } while
(rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); if
(row.RowNum == 0) continue;//The firt row is title,no need import for
(int
i = 0; i < row.LastCellNum; i++) { if
(i>=dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213 { break; } ICell cell = row.GetCell(i); if
((i==0)&&(string.IsNullOrEmpty(cell.ToString())==true))//每行第一个cell为空,break { break; } if
(cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } #endregion } else {//.xlsx #region .xlsx文件处理:XSSFWorkbook try { using
(FileStream file = new
FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new
XSSFWorkbook(file); } } catch
(Exception e) { throw
e; } ISheet sheet = hssfworkbook.GetSheet(sheetName); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 for
(int
j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //SET EVERY COLUMN NAME XSSFCell cell = (XSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } while
(rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); if
(row.RowNum == 0) continue;//The firt row is title,no need import for
(int
i = 0; i < row.LastCellNum; i++) { if
(i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213 { break; } ICell cell = row.GetCell(i); if
((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break { break; } if
(cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } #endregion } return
dt;} |
2.导出
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43 |
/// <summary> /// NPOI导出Excel，不依赖本地是否装有Excel，导出速度快 /// </summary> /// <param name="dataGridView1">要导出的dataGridView控件</param> /// <param name="sheetName">sheet表名</param> /// public
static void ExportToExcel(DataGridView dataGridView1, string
sheetName) { SaveFileDialog fileDialog = new
SaveFileDialog(); fileDialog.Filter = "Excel(97-2003)|*.xls"; if
(fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } //不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误 dataGridView1.AllowUserToAddRows = false; HSSFWorkbook workbook = new
HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); IRow rowHead = sheet.CreateRow(0); //填写表头 for
(int
i = 0; i < dataGridView1.Columns.Count; i++) { rowHead.CreateCell(i, CellType.String).SetCellValue(dataGridView1.Columns[i].HeaderText.ToString()); } //填写内容 for
(int
i = 0; i < dataGridView1.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for
(int
j = 0; j < dataGridView1.Columns.Count; j++) { row.CreateCell(j, CellType.String).SetCellValue(dataGridView1.Rows[i].Cells[j].Value.ToString()); } } using
(FileStream stream = File.OpenWrite(fileDialog.FileName)) { workbook.Write(stream); stream.Close(); } MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); GC.Collect(); } |
使用NPOI导入导出Excel(xls/xlsx)数据到DataTable中
原文：http://www.cnblogs.com/songrun/p/3547738.html