使用NPOI导出，读取EXCEL（可追加功能）
使用NPOI导出，读取EXCEL（可追加功能）
冰封一夏
发布于 2019-09-11 15:23:45
发布于 2019-09-11 15:23:45
使用NPOI导出，读取EXCEL，具有可追加功能
看代码
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.IO;
5 using System.Data;
6 using NPOI.SS.UserModel;
7 using NPOI.XSSF.UserModel;
8 using NPOI.HSSF.UserModel;
9
10 namespace NPOIExcel
11 {
12 public class ExcelEX
13 {
14
15 /// <summary>
16 /// 将DataTable数据导入到excel中
17 /// </summary>
18 /// <param name="fileName">文件名</param>
19 /// <param name="data">要导入的数据</param>
20 /// <param name="sheetName">要导入的excel的sheet的名称</param>
21 /// <param name="blnAppled">是否是追加模式</param>
22 /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
23 /// <returns>导入数据行数(包含列名那一行)</returns>
24 public static int DataTableToExcel(string fileName, DataTable data, bool blnAppled = false, string sheetName = "sheet1", bool isColumnWritten = true)
25 {
26 int i = 0;
27 int j = 0;
28 int count = 0;
29 ISheet sheet = null;
30 IWorkbook workbook = null;
31 using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
32 {
33 if (!blnAppled)
34 {
35 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
36 workbook = new XSSFWorkbook();
37 else if (fileName.IndexOf(".xls") > 0) // 2003版本
38 workbook = new HSSFWorkbook();
39 }
40 else
41 {
42 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
43 workbook = new XSSFWorkbook(fs);
44 else if (fileName.IndexOf(".xls") > 0) // 2003版本
45 workbook = new HSSFWorkbook(fs);
46 }
47
48 try
49 {
50 if (!blnAppled && !string.IsNullOrEmpty(sheetName))
51 {
52 if (workbook != null)
53 {
54 sheet = workbook.CreateSheet(sheetName);
55 }
56 else
57 {
58 return -1;
59 }
60 }
61 else
62 {
63 sheet = workbook.GetSheetAt(0);
64 }
65
66 if (!blnAppled)
67 {
68 if (isColumnWritten == true) //写入DataTable的列名
69 {
70 IRow row = sheet.CreateRow(0);
71 for (j = 0; j < data.Columns.Count; ++j)
72 {
73 row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
74 }
75 count = 1;
76 }
77 else
78 {
79 count = 0;
80 }
81 }
82
83 count = sheet.LastRowNum + 1;
84
85 for (i = 0; i < data.Rows.Count; ++i)
86 {
87 IRow row = sheet.CreateRow(count);
88 for (j = 0; j < data.Columns.Count; ++j)
89 {
90 row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
91 }
92 ++count;
93 }
94
95 }
96 catch (Exception ex)
97 {
98 Console.WriteLine("Exception: " + ex.Message);
99 return -1;
100 }
101 }
102
103 FileStream outFs = new FileStream(fileName, FileMode.Open);
104 workbook.Write(outFs);
105 outFs.Close();
106 return count;
107 }
108
109 /// <summary>
110 /// 将excel中的数据导入到DataTable中
111 /// </summary>
112 /// <param name="sheetName">excel工作薄sheet的名称</param>
113 /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
114 /// <returns>返回的DataTable</returns>
115 public static DataTable ExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
116 {
117 ISheet sheet = null;
118 DataTable data = new DataTable();
119 int startRow = 0;
120 IWorkbook workbook = null;
121 try
122 {
123 FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
124 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
125 workbook = new XSSFWorkbook(fs);
126 else if (fileName.IndexOf(".xls") > 0) // 2003版本
127 workbook = new HSSFWorkbook(fs);
128
129 if (sheetName != null)
130 {
131 sheet = workbook.GetSheet(sheetName);
132 if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
133 {
134 sheet = workbook.GetSheetAt(0);
135 }
136 }
137 else
138 {
139 sheet = workbook.GetSheetAt(0);
140 }
141 if (sheet != null)
142 {
143 IRow firstRow = sheet.GetRow(0);
144 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
145
146 if (isFirstRowColumn)
147 {
148 for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
149 {
150 ICell cell = firstRow.GetCell(i);
151 if (cell != null)
152 {
153 string cellValue = cell.StringCellValue;
154 if (cellValue != null)
155 {
156 DataColumn column = new DataColumn(cellValue);
157 data.Columns.Add(column);
158 }
159 }
160 }
161 startRow = sheet.FirstRowNum + 1;
162 }
163 else
164 {
165 startRow = sheet.FirstRowNum;
166 }
167
168 //最后一列的标号
169 int rowCount = sheet.LastRowNum;
170 for (int i = startRow; i <= rowCount; ++i)
171 {
172 IRow row = sheet.GetRow(i);
173 if (row == null) continue; //没有数据的行默认是null
174
175 DataRow dataRow = data.NewRow();
176 for (int j = row.FirstCellNum; j < cellCount; ++j)
177 {
178 if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
179 dataRow[j] = row.GetCell(j).ToString();
180 }
181 data.Rows.Add(dataRow);
182 }
183 }
184
185 return data;
186 }
187 catch (Exception ex)
188 {
189 Console.WriteLine("Exception: " + ex.Message);
190 return null;
191 }
192 }
193
194
195 }
196 }看测试
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data;
6 using NPOIExcel;
7
8 namespace Test
9 {
10 class Program
11 {
12 static void Main(string[] args)
13 {
14 DataTable dt = new DataTable();
15 dt.Columns.Add("A", typeof(string));
16 dt.Columns.Add("B", typeof(string));
17 dt.Columns.Add("C", typeof(string));
18 for (int i = 0; i < 50; i++)
19 {
20 DataRow dr = dt.NewRow();
21 for (int j = 0; j < 3; j++)
22 {
23 dr[j] = "1_" + i.ToString() + "_" + j.ToString();
24 }
25 dt.Rows.Add(dr);
26 }
27 ExcelEX.DataTableToExcel("d:\\123.xlsx",dt);
28 dt.Rows.Clear();
29
30 for (int i = 0; i < 50; i++)
31 {
32 DataRow dr = dt.NewRow();
33 for (int j = 0; j < 3; j++)
34 {
35 dr[j] = "2_" + i.ToString() + "_" + j.ToString();
36 }
37 dt.Rows.Add(dr);
38 }
39
40 ExcelEX.DataTableToExcel("d:\\123.xlsx", dt,true);
41 Console.ReadKey();
42 }
43 }
44 }看结果
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016-10-25 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读