请选择 进入手机版 | 继续访问电脑版

马上加入IBC程序猿 各种源码随意下,各种教程随便看! 注册 每日签到 加入编程讨论群

C#教程 ASP.NET教程 C#视频教程程序源码享受不尽 C#问题入口 ASP.NET问题入口

【C#问题提交】 社群合作 申请版主 程序开发 【远程协助】 每天乐一乐 每日签到 【承接毕业设计】 面试-葵花宝典下载

官方一群:

官方二群:

查看: 51|回复: 0

webapi 导入excel处理数据

[复制链接]
  • TA的每日心情
    开心
    昨天 11:12
  • 签到天数: 1465 天

    [LV.10]以坛为家III

    1159

    主题

    3064

    帖子

    9万

    积分

    管理员

    IBC编程社区-原道楠

    Rank: 9Rank: 9Rank: 9

    积分
    93811

    推广达人突出贡献优秀版主荣誉管理论坛元老

    发表于 2019-8-16 13:58:23 | 显示全部楼层 |阅读模式

    马上加入IBC,查看更多教程

    您需要 登录 才可以下载或查看,没有帐号?立即注册

    x

    参考资料

         https://blog.csdn.net/pan_junbiao/article/details/82935992

         https://www.cnblogs.com/dansediao/p/5482467.html

         https://www.cnblogs.com/shiyh/p/7478241.html

    excel转成datatable工具类

    140017vtfyo7r1hrohhih7.gif
    140017zo01llb3pnnoq3nc.gif
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Data;
    4. using System.IO;
    5. using System.Reflection;
    6. using NPOI.HSSF.UserModel;
    7. using NPOI.SS.UserModel;
    8. using NPOI.SS.Util;
    9. using NPOI.XSSF.UserModel;
    10. namespace ELearning.Common.Extensions
    11. {
    12. public static class ExcelHelp
    13. {
    14. /// <summary>
    15. /// Id的列号
    16. /// </summary>
    17. private const int EXCEL_COL_PLAT_FORM_INDUSTRY = 14;
    18. //获取列名委托方法
    19. public delegate string GetColumnName(string columnName);
    20. #region Excel2007
    21. /// <summary>
    22. /// excel文件流转化成datatable
    23. /// </summary>
    24. public static DataTable ExcelToTableForXLSX(Stream fileStream, bool haveNote = false)
    25. {
    26. var dt = new DataTable();
    27. using (var fs = fileStream)
    28. {
    29. var xssfworkbook = new XSSFWorkbook(fs);
    30. var sheet = xssfworkbook.GetSheetAt(0);
    31. //表头 判断是否包含备注
    32. var firstRowNum = sheet.FirstRowNum;
    33. if (haveNote)
    34. {
    35. firstRowNum += 1;
    36. }
    37. var header = sheet.GetRow(firstRowNum);
    38. var columns = new List<int>();
    39. for (var i = 0; i < header.LastCellNum; i++)
    40. {
    41. var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
    42. if (obj == null || obj.ToString() == string.Empty)
    43. {
    44. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    45. //continue;
    46. }
    47. else
    48. dt.Columns.Add(new DataColumn(obj.ToString()));
    49. columns.Add(i);
    50. }
    51. //数据
    52. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    53. {
    54. var dr = dt.NewRow();
    55. var hasValue = false;
    56. if (sheet.GetRow(i) == null)
    57. {
    58. continue;
    59. }
    60. foreach (var j in columns)
    61. {
    62. var cell = sheet.GetRow(i).GetCell(j);
    63. if (cell != null && cell.CellType == CellType.Numeric)
    64. {
    65. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    66. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    67. {
    68. dr[j] = cell.DateCellValue;
    69. }
    70. else //其他数字类型
    71. {
    72. dr[j] = cell.NumericCellValue;
    73. }
    74. }
    75. else
    76. {
    77. dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
    78. }
    79. if (dr[j] != null && dr[j].ToString() != string.Empty)
    80. {
    81. hasValue = true;
    82. }
    83. }
    84. if (hasValue)
    85. {
    86. dt.Rows.Add(dr);
    87. }
    88. }
    89. }
    90. return dt;
    91. }
    92. /// <summary>
    93. /// 将Excel文件中的数据读出到DataTable中(xlsx)
    94. /// </summary>
    95. /// <param name="file"></param>
    96. /// <param name="haveNote">是否有备注</param>
    97. /// <returns></returns>
    98. public static DataTable ExcelToTableForXLSX(string file, bool haveNote = false)
    99. {
    100. var dt = new DataTable();
    101. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    102. {
    103. var xssfworkbook = new XSSFWorkbook(fs);
    104. var sheet = xssfworkbook.GetSheetAt(0);
    105. //表头 判断是否包含备注
    106. var firstRowNum = sheet.FirstRowNum;
    107. if (haveNote)
    108. {
    109. firstRowNum += 1;
    110. }
    111. var header = sheet.GetRow(firstRowNum);
    112. var columns = new List<int>();
    113. for (var i = 0; i < header.LastCellNum; i++)
    114. {
    115. var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
    116. if (obj == null || obj.ToString() == string.Empty)
    117. {
    118. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    119. //continue;
    120. }
    121. else
    122. dt.Columns.Add(new DataColumn(obj.ToString()));
    123. columns.Add(i);
    124. }
    125. //数据
    126. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    127. {
    128. var dr = dt.NewRow();
    129. var hasValue = false;
    130. if (sheet.GetRow(i) == null)
    131. {
    132. continue;
    133. }
    134. foreach (var j in columns)
    135. {
    136. var cell = sheet.GetRow(i).GetCell(j);
    137. if (cell != null && cell.CellType == CellType.Numeric)
    138. {
    139. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    140. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    141. {
    142. dr[j] = cell.DateCellValue;
    143. }
    144. else //其他数字类型
    145. {
    146. dr[j] = cell.NumericCellValue;
    147. }
    148. }
    149. else
    150. {
    151. dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
    152. }
    153. if (dr[j] != null && dr[j].ToString() != string.Empty)
    154. {
    155. hasValue = true;
    156. }
    157. }
    158. if (hasValue)
    159. {
    160. dt.Rows.Add(dr);
    161. }
    162. }
    163. }
    164. return dt;
    165. }
    166. /// <summary>
    167. /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xlsx)
    168. /// </summary>
    169. /// <param name="file">文件路径</param>
    170. /// <param name="excelFirstName">excel中第一条数据列名</param>
    171. /// <param name="noteExist">是否存在填写须知(根据表格内容判断)</param>
    172. /// <param name="haveNote">是否有备注</param>
    173. /// <returns></returns>
    174. public static DataTable ExcelToTableForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
    175. {
    176. var dt = new DataTable();
    177. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    178. {
    179. var xssfworkbook = new XSSFWorkbook(fs);
    180. var sheet = xssfworkbook.GetSheetAt(0);
    181. //表头 判断是否包含备注
    182. var firstRowNum = sheet.FirstRowNum;
    183. noteExist = haveNote;
    184. if (haveNote)
    185. {
    186. firstRowNum += 1;
    187. var obj = GetValueTypeForXLSX(sheet.GetRow(firstRowNum).GetCell(0) as XSSFCell);
    188. if (obj != null && !string.IsNullOrEmpty(excelFirstName))
    189. {
    190. //判断第二行第一列是否包含excel数据第一列列名,假如包含,则表示存在填写须知
    191. if (!obj.ToString().Contains(excelFirstName))
    192. {
    193. noteExist = false;
    194. firstRowNum -= 1;
    195. }
    196. }
    197. }
    198. var header = sheet.GetRow(firstRowNum);
    199. var columns = new List<int>();
    200. for (var i = 0; i < header.LastCellNum; i++)
    201. {
    202. var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
    203. if (obj == null || obj.ToString() == string.Empty)
    204. {
    205. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    206. //continue;
    207. }
    208. else
    209. dt.Columns.Add(new DataColumn(obj.ToString()));
    210. columns.Add(i);
    211. }
    212. //数据
    213. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    214. {
    215. var dr = dt.NewRow();
    216. var hasValue = false;
    217. if (sheet.GetRow(i) == null)
    218. {
    219. continue;
    220. }
    221. foreach (var j in columns)
    222. {
    223. var cell = sheet.GetRow(i).GetCell(j);
    224. if (cell != null && cell.CellType == CellType.Numeric)
    225. {
    226. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    227. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    228. {
    229. dr[j] = cell.DateCellValue;
    230. }
    231. else //其他数字类型
    232. {
    233. dr[j] = cell.NumericCellValue;
    234. }
    235. }
    236. else
    237. {
    238. dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
    239. }
    240. if (dr[j] != null && dr[j].ToString() != string.Empty)
    241. {
    242. hasValue = true;
    243. }
    244. }
    245. if (hasValue)
    246. {
    247. dt.Rows.Add(dr);
    248. }
    249. }
    250. }
    251. return dt;
    252. }
    253. /// <summary>
    254. /// 将Excel文件中的数据读出到DataTable中(xlsx)包括空行
    255. /// </summary>
    256. /// <param name="haveNote">是否有备注</param>
    257. public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, bool haveNote = false)
    258. {
    259. var dt = new DataTable();
    260. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    261. {
    262. var xssfworkbook = new XSSFWorkbook(fs);
    263. var sheet = xssfworkbook.GetSheetAt(0);
    264. //表头 判断是否包含备注
    265. var firstRowNum = sheet.FirstRowNum;
    266. if (haveNote)
    267. {
    268. firstRowNum += 1;
    269. }
    270. var header = sheet.GetRow(firstRowNum);
    271. var columns = new List<int>();
    272. for (var i = 0; i < header.LastCellNum; i++)
    273. {
    274. var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
    275. if (obj == null || obj.ToString() == string.Empty)
    276. {
    277. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    278. //continue;
    279. }
    280. else
    281. dt.Columns.Add(new DataColumn(obj.ToString()));
    282. columns.Add(i);
    283. }
    284. //数据
    285. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    286. {
    287. var dr = dt.NewRow();
    288. IRow row = sheet.GetRow(i) as XSSFRow;
    289. foreach (var j in columns)
    290. {
    291. if (row != null)
    292. {
    293. ICell cell = row.GetCell(j) as XSSFCell;
    294. if (cell != null && cell.CellType == CellType.Numeric)
    295. {
    296. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    297. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    298. {
    299. dr[j] = cell.DateCellValue;
    300. }
    301. else //其他数字类型
    302. {
    303. dr[j] = cell.NumericCellValue;
    304. }
    305. }
    306. else
    307. {
    308. dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
    309. }
    310. }
    311. }
    312. dt.Rows.Add(dr);
    313. }
    314. }
    315. return dt;
    316. }
    317. /// <summary>
    318. /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
    319. /// <param name="file">文件路径</param>
    320. /// <param name="excelFirstName">excel中第一条数据列名</param>
    321. /// <param name="noteExist">是否存在填写须知(根据表格内容判断)</param>
    322. /// <param name="haveNote">是否有备注</param>
    323. /// </summary>
    324. public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
    325. {
    326. var dt = new DataTable();
    327. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    328. {
    329. var xssfworkbook = new XSSFWorkbook(fs);
    330. var sheet = xssfworkbook.GetSheetAt(0);
    331. //表头 判断是否包含备注
    332. var firstRowNum = sheet.FirstRowNum;
    333. noteExist = haveNote;
    334. if (haveNote)
    335. {
    336. firstRowNum += 1;
    337. var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
    338. if (obj != null && !string.IsNullOrEmpty(excelFirstName))
    339. {
    340. //判断第二行第一列是否包含excel数据第一列列名,假如包含,则表示存在填写须知
    341. if (!obj.ToString().Contains(excelFirstName))
    342. {
    343. noteExist = false;
    344. firstRowNum -= 1;
    345. }
    346. }
    347. }
    348. var header = sheet.GetRow(firstRowNum);
    349. var columns = new List<int>();
    350. for (var i = 0; i < header.LastCellNum; i++)
    351. {
    352. var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
    353. if (obj == null || obj.ToString() == string.Empty)
    354. {
    355. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    356. //continue;
    357. }
    358. else
    359. dt.Columns.Add(new DataColumn(obj.ToString()));
    360. columns.Add(i);
    361. }
    362. //数据
    363. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    364. {
    365. var dr = dt.NewRow();
    366. IRow row = sheet.GetRow(i) as XSSFRow;
    367. foreach (var j in columns)
    368. {
    369. if (row != null)
    370. {
    371. ICell cell = row.GetCell(j) as XSSFCell;
    372. if (cell != null && cell.CellType == CellType.Numeric)
    373. {
    374. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    375. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    376. {
    377. dr[j] = cell.DateCellValue;
    378. }
    379. else //其他数字类型
    380. {
    381. dr[j] = cell.NumericCellValue;
    382. }
    383. }
    384. else
    385. {
    386. dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
    387. }
    388. }
    389. }
    390. dt.Rows.Add(dr);
    391. }
    392. }
    393. return dt;
    394. }
    395. /// <summary>
    396. /// 将DataTable数据导出到Excel文件中(xlsx),生成excel文件的字节数组
    397. /// </summary>
    398. /// <param name="dt"></param>
    399. /// <param name="file"></param>
    400. public static byte[] TableToExcelForXLSX(DataTable dt)
    401. {
    402. var xssfworkbook = new XSSFWorkbook();
    403. var sheet = xssfworkbook.CreateSheet("Sheet1");
    404. //表头
    405. var row = sheet.CreateRow(0);
    406. dt.Columns.Remove("SignTime");//去掉signtime字段,留下格式化以后的
    407. for (var i = 0; i < dt.Columns.Count; i++)
    408. {
    409. var cell = row.CreateCell(i);
    410. //列名称,数据库中字段
    411. var columnName = dt.Columns[i].ColumnName;
    412. var convertColumnName = "";
    413. switch (columnName)
    414. {
    415. case "UserName":
    416. convertColumnName = "用户名";
    417. break;
    418. case "Name":
    419. convertColumnName = "真实姓名";
    420. break;
    421. case "LastCourseTime":
    422. convertColumnName = "最后上课日期";
    423. break;
    424. case "FinishRate":
    425. convertColumnName = "看课完成率";
    426. break;
    427. case "Phone":
    428. convertColumnName = "手机号";
    429. break;
    430. case "CardNo":
    431. convertColumnName = "身份证号";
    432. break;
    433. case "RefereeName":
    434. convertColumnName = "推荐人";
    435. break;
    436. case "SignTimeStr":
    437. convertColumnName = "报名日期";
    438. break;
    439. default:
    440. convertColumnName = "无列名";
    441. break;
    442. }
    443. cell.SetCellValue(convertColumnName);
    444. }
    445. //数据
    446. for (var i = 0; i < dt.Rows.Count; i++)
    447. {
    448. var row1 = sheet.CreateRow(i + 1);
    449. for (var j = 0; j < dt.Columns.Count; j++)
    450. {
    451. var cell = row1.CreateCell(j);
    452. cell.SetCellValue(dt.Rows[i][j].ToString());
    453. }
    454. }
    455. //转为字节数组
    456. var stream = new MemoryStream();
    457. xssfworkbook.Write(stream);
    458. var buf = stream.ToArray();
    459. return buf;
    460. }
    461. /// <summary>
    462. /// 将DataTable转化为excel
    463. /// </summary>
    464. /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
    465. /// <param name="getColumnName">获取列名的方法</param>
    466. /// <returns>excel</returns>
    467. public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
    468. {
    469. var xssfworkbook = new XSSFWorkbook();
    470. var sheet = xssfworkbook.CreateSheet("Sheet");
    471. //表头
    472. var row = sheet.CreateRow(0);
    473. for (var i = 0; i < dt.Columns.Count; i++)
    474. {
    475. var cell = row.CreateCell(i);
    476. //列名称,数据库中字段
    477. var columnName = dt.Columns[i].ColumnName;
    478. var convertColumnName = getColumnName(columnName);
    479. cell.SetCellValue(convertColumnName);
    480. }
    481. //数据
    482. for (var i = 0; i < dt.Rows.Count; i++)
    483. {
    484. var row1 = sheet.CreateRow(i + 1);
    485. for (var j = 0; j < dt.Columns.Count; j++)
    486. {
    487. var cell = row1.CreateCell(j);
    488. cell.SetCellValue(dt.Rows[i][j].ToString());
    489. }
    490. }
    491. //转为字节数组
    492. var stream = new MemoryStream();
    493. xssfworkbook.Write(stream);
    494. var buf = stream.ToArray();
    495. return buf;
    496. }
    497. /// <summary>
    498. /// 将DataTable转化为excel
    499. /// </summary>
    500. /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
    501. /// <returns>excel</returns>
    502. public static byte[] GetExcelForXLSX(DataTable dt)
    503. {
    504. var xssfworkbook = new XSSFWorkbook();
    505. var sheet = xssfworkbook.CreateSheet("Sheet");
    506. //表头
    507. var row = sheet.CreateRow(0);
    508. for (var i = 0; i < dt.Columns.Count; i++)
    509. {
    510. var cell = row.CreateCell(i);
    511. //列名称,数据库中字段
    512. var columnName = dt.Columns[i].ColumnName;
    513. cell.SetCellValue(columnName);
    514. }
    515. //数据
    516. for (var i = 0; i < dt.Rows.Count; i++)
    517. {
    518. var row1 = sheet.CreateRow(i + 1);
    519. for (var j = 0; j < dt.Columns.Count; j++)
    520. {
    521. var cell = row1.CreateCell(j);
    522. cell.SetCellValue(dt.Rows[i][j].ToString());
    523. }
    524. }
    525. //转为字节数组
    526. var stream = new MemoryStream();
    527. xssfworkbook.Write(stream);
    528. var buf = stream.ToArray();
    529. return buf;
    530. }
    531. /// <summary>
    532. /// 将DataTable转化为excel(招生计划完成率报表使用)
    533. /// </summary>
    534. /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
    535. /// <param name="getColumnName">获取列名的方法</param>
    536. /// <returns>excel</returns>
    537. public static byte[] GetExcelForXlsxForRateReport(DataTable dt, GetColumnName getColumnName, string tableName)
    538. {
    539. var xssfworkbook = new XSSFWorkbook();
    540. var sheet = xssfworkbook.CreateSheet("Sheet");
    541. //表头
    542. var row = sheet.CreateRow(0);
    543. //顶级标头
    544. for (var i = 0; i < dt.Columns.Count; i++)
    545. {
    546. var cell = row.CreateCell(i);
    547. cell.SetCellValue(tableName);
    548. }
    549. //二级表头
    550. var sencondRow = sheet.CreateRow(2);
    551. for (var i = 0; i < dt.Columns.Count; i++)
    552. {
    553. var cell = sencondRow.CreateCell(i);
    554. //列名称,数据库中字段
    555. var columnName = dt.Columns[i].ColumnName;
    556. var convertColumnName = getColumnName(columnName);
    557. cell.SetCellValue(convertColumnName);
    558. }
    559. sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 6)); //合并招生完成率中表名
    560. //数据
    561. for (var i = 0; i < dt.Rows.Count; i++)
    562. {
    563. var row1 = sheet.CreateRow(i + 3);
    564. for (var j = 0; j < dt.Columns.Count; j++)
    565. {
    566. var cell = row1.CreateCell(j);
    567. cell.SetCellValue(dt.Rows[i][j].ToString());
    568. }
    569. }
    570. //转为字节数组
    571. var stream = new MemoryStream();
    572. xssfworkbook.Write(stream);
    573. var buf = stream.ToArray();
    574. return buf;
    575. }
    576. /// <summary>
    577. /// 将DataTable转化为excel,表头自己填充,招生计划完成率导出使用(未封装)
    578. /// </summary>
    579. /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
    580. /// <param name="dataStartRow">excel中数据开始的行数(从0开始)</param>
    581. /// <returns>excel</returns>
    582. public static byte[] GetExcelForXLSXForRate(DataTable dt, GetColumnName getColumnName)
    583. {
    584. var xssfworkbook = new XSSFWorkbook();
    585. var style = xssfworkbook.CreateCellStyle();
    586. //设置单元格的样式:水平对齐居中
    587. style.Alignment = HorizontalAlignment.Center;
    588. var sheet = xssfworkbook.CreateSheet("Sheet1");
    589. //表头
    590. var row = sheet.CreateRow(0);
    591. //顶级标头
    592. for (var i = 0; i < dt.Columns.Count; i++)
    593. {
    594. var cell = row.CreateCell(i);
    595. cell.SetCellValue("convertColumnName");
    596. var convertColumnName = "";
    597. var columnName = dt.Columns[i].ColumnName;
    598. convertColumnName = getColumnName(columnName);
    599. cell.SetCellValue(convertColumnName);
    600. }
    601. //二级表头
    602. var rowSencond = sheet.CreateRow(1);
    603. for (var i = 2; i < dt.Columns.Count; i++)
    604. {
    605. var cell = rowSencond.CreateCell(i);
    606. //列名称,数据库中字段
    607. if (i % 3 == 2)
    608. {
    609. cell.SetCellValue("完成");
    610. }
    611. else if (i % 3 == 0)
    612. {
    613. cell.SetCellValue("目标");
    614. }
    615. else
    616. {
    617. cell.SetCellValue("完成率");
    618. }
    619. }
    620. //合并表头
    621. sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); //合并招生完成率中的年份
    622. sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1)); //合并招生完成率中的姓名
    623. var r = 2; //合并单元格开始所在列
    624. for (var range = 0; range < 13; range++) //13是指12个月加上年度合计
    625. {
    626. sheet.AddMergedRegion(new CellRangeAddress(0, 0, r, r + 2));
    627. r += 3;
    628. }
    629. //数据
    630. for (var i = 0; i < dt.Rows.Count; i++)
    631. {
    632. var row1 = sheet.CreateRow(i + 2);
    633. for (var j = 0; j < dt.Columns.Count; j++)
    634. {
    635. var cell = row1.CreateCell(j);
    636. cell.SetCellValue(dt.Rows[i][j].ToString());
    637. }
    638. }
    639. //转为字节数组
    640. var stream = new MemoryStream();
    641. xssfworkbook.Write(stream);
    642. var buf = stream.ToArray();
    643. return buf;
    644. }
    645. /// <summary>
    646. /// 获取单元格类型(xlsx)
    647. /// </summary>
    648. /// <param name="cell"></param>
    649. /// <returns></returns>
    650. private static object GetValueTypeForXLSX(XSSFCell cell)
    651. {
    652. if (cell == null)
    653. return null;
    654. switch (cell.CellType)
    655. {
    656. case CellType.Blank: //BLANK:
    657. return null;
    658. case CellType.Boolean: //BOOLEAN:
    659. return cell.BooleanCellValue;
    660. case CellType.Numeric: //NUMERIC:
    661. return cell.NumericCellValue;
    662. case CellType.String: //STRING:
    663. return cell.StringCellValue;
    664. case CellType.Error: //ERROR:
    665. return cell.ErrorCellValue;
    666. case CellType.Formula: //FORMULA:
    667. default:
    668. return "=" + cell.CellFormula;
    669. }
    670. }
    671. #endregion
    672. #region Exce97-2003
    673. /// <summary>
    674. /// 将Excel文件中的数据读出到DataTable中(xls)
    675. /// </summary>
    676. /// <param name="file"></param>
    677. /// <param name="haveNote"></param>
    678. /// <returns></returns>
    679. public static DataTable ExcelToTableForXLS(string file, bool haveNote = false)
    680. {
    681. var dt = new DataTable();
    682. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    683. {
    684. var xssfworkbook = new HSSFWorkbook(fs);
    685. var sheet = xssfworkbook.GetSheetAt(0);
    686. //表头 判断是否包含备注
    687. var firstRowNum = sheet.FirstRowNum;
    688. if (haveNote)
    689. {
    690. firstRowNum += 1;
    691. }
    692. var header = sheet.GetRow(firstRowNum);
    693. var columns = new List<int>();
    694. for (var i = 0; i < header.LastCellNum; i++)
    695. {
    696. var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
    697. if (obj == null || obj.ToString() == string.Empty)
    698. {
    699. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    700. //continue;
    701. }
    702. else
    703. dt.Columns.Add(new DataColumn(obj.ToString()));
    704. columns.Add(i);
    705. }
    706. //数据
    707. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    708. {
    709. var dr = dt.NewRow();
    710. var hasValue = false;
    711. foreach (var j in columns)
    712. {
    713. var cell = sheet.GetRow(i).GetCell(j);
    714. if (cell != null && cell.CellType == CellType.Numeric)
    715. {
    716. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    717. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    718. {
    719. dr[j] = cell.DateCellValue;
    720. }
    721. else //其他数字类型
    722. {
    723. dr[j] = cell.NumericCellValue;
    724. }
    725. }
    726. else
    727. {
    728. dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
    729. }
    730. if (dr[j] != null && dr[j].ToString() != string.Empty)
    731. {
    732. hasValue = true;
    733. }
    734. }
    735. if (hasValue)
    736. {
    737. dt.Rows.Add(dr);
    738. }
    739. }
    740. }
    741. return dt;
    742. }
    743. /// <summary>
    744. /// 将Excel文件中的数据读出到DataTable中(xls)
    745. /// </summary>
    746. /// <param name="file">文件路径</param>
    747. /// <param name="excelFirstName">excel中第一条数据列名</param>
    748. /// <param name="noteExist">是否存在填写须知(根据表格内容判断)</param>
    749. /// <param name="haveNote">是否有备注</param>
    750. /// <returns></returns>
    751. public static DataTable ExcelToTableForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
    752. {
    753. var dt = new DataTable();
    754. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    755. {
    756. var xssfworkbook = new HSSFWorkbook(fs);
    757. var sheet = xssfworkbook.GetSheetAt(0);
    758. //表头 判断是否包含备注
    759. var firstRowNum = sheet.FirstRowNum;
    760. noteExist = haveNote;
    761. if (haveNote)
    762. {
    763. firstRowNum += 1;
    764. var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
    765. if (obj != null && !string.IsNullOrEmpty(excelFirstName))
    766. {
    767. //判断第二行第一列是否包含excel数据第一列列名,假如包含,则表示存在填写须知
    768. if (!obj.ToString().Contains(excelFirstName))
    769. {
    770. noteExist = false;
    771. firstRowNum -= 1;
    772. }
    773. }
    774. }
    775. var header = sheet.GetRow(firstRowNum);
    776. var columns = new List<int>();
    777. for (var i = 0; i < header.LastCellNum; i++)
    778. {
    779. var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
    780. if (obj == null || obj.ToString() == string.Empty)
    781. {
    782. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    783. //continue;
    784. }
    785. else
    786. dt.Columns.Add(new DataColumn(obj.ToString()));
    787. columns.Add(i);
    788. }
    789. //数据
    790. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    791. {
    792. var dr = dt.NewRow();
    793. var hasValue = false;
    794. foreach (var j in columns)
    795. {
    796. var cell = sheet.GetRow(i).GetCell(j);
    797. if (cell != null && cell.CellType == CellType.Numeric)
    798. {
    799. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    800. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    801. {
    802. dr[j] = cell.DateCellValue;
    803. }
    804. else //其他数字类型
    805. {
    806. dr[j] = cell.NumericCellValue;
    807. }
    808. }
    809. else
    810. {
    811. dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
    812. }
    813. if (dr[j] != null && dr[j].ToString() != string.Empty)
    814. {
    815. hasValue = true;
    816. }
    817. }
    818. if (hasValue)
    819. {
    820. dt.Rows.Add(dr);
    821. }
    822. }
    823. }
    824. return dt;
    825. }
    826. /// <summary>
    827. ///将Excel文件中的数据读出到DataTable中(xls)包括空行
    828. /// </summary>
    829. public static DataTable ExcelToTableWithEmptyRowForXLS(string file, bool haveNote = false)
    830. {
    831. var dt = new DataTable();
    832. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    833. {
    834. var xssfworkbook = new HSSFWorkbook(fs);
    835. var sheet = xssfworkbook.GetSheetAt(0);
    836. //表头 判断是否包含备注
    837. var firstRowNum = sheet.FirstRowNum;
    838. if (haveNote)
    839. {
    840. firstRowNum += 1;
    841. }
    842. var header = sheet.GetRow(firstRowNum);
    843. var columns = new List<int>();
    844. for (var i = 0; i < header.LastCellNum; i++)
    845. {
    846. var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
    847. if (obj == null || obj.ToString() == string.Empty)
    848. {
    849. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    850. //continue;
    851. }
    852. else
    853. dt.Columns.Add(new DataColumn(obj.ToString()));
    854. columns.Add(i);
    855. }
    856. //数据
    857. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    858. {
    859. var dr = dt.NewRow();
    860. IRow row = sheet.GetRow(i) as HSSFRow;
    861. foreach (var j in columns)
    862. {
    863. if (row != null)
    864. {
    865. ICell cell = row.GetCell(j) as HSSFCell;
    866. if (cell != null && cell.CellType == CellType.Numeric)
    867. {
    868. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    869. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    870. {
    871. dr[j] = cell.DateCellValue;
    872. }
    873. else //其他数字类型
    874. {
    875. dr[j] = cell.NumericCellValue;
    876. }
    877. }
    878. else
    879. {
    880. dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
    881. }
    882. }
    883. }
    884. dt.Rows.Add(dr);
    885. }
    886. }
    887. return dt;
    888. }
    889. /// <summary>
    890. /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
    891. /// <param name="file">文件路径</param>
    892. /// <param name="excelFirstName">excel中第一条数据列名</param>
    893. /// <param name="noteExist">是否存在填写须知(根据表格内容判断)</param>
    894. /// <param name="haveNote">是否有备注</param>
    895. /// </summary>
    896. public static DataTable ExcelToTableWithEmptyRowForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
    897. {
    898. var dt = new DataTable();
    899. using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    900. {
    901. var xssfworkbook = new HSSFWorkbook(fs);
    902. var sheet = xssfworkbook.GetSheetAt(0);
    903. //表头 判断是否包含备注
    904. var firstRowNum = sheet.FirstRowNum;
    905. noteExist = haveNote;
    906. if (haveNote)
    907. {
    908. firstRowNum += 1;
    909. var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
    910. if (obj != null && !string.IsNullOrEmpty(excelFirstName))
    911. {
    912. //判断第二行第一列是否包含excel数据第一列列名,假如包含,则表示存在填写须知
    913. if (!obj.ToString().Contains(excelFirstName))
    914. {
    915. noteExist = false;
    916. firstRowNum -= 1;
    917. }
    918. }
    919. }
    920. var header = sheet.GetRow(firstRowNum);
    921. var columns = new List<int>();
    922. for (var i = 0; i < header.LastCellNum; i++)
    923. {
    924. var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
    925. if (obj == null || obj.ToString() == string.Empty)
    926. {
    927. dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
    928. //continue;
    929. }
    930. else
    931. dt.Columns.Add(new DataColumn(obj.ToString()));
    932. columns.Add(i);
    933. }
    934. //数据
    935. for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
    936. {
    937. var dr = dt.NewRow();
    938. IRow row = sheet.GetRow(i) as HSSFRow;
    939. foreach (var j in columns)
    940. {
    941. if (row != null)
    942. {
    943. ICell cell = row.GetCell(j) as HSSFCell;
    944. if (cell != null && cell.CellType == CellType.Numeric)
    945. {
    946. //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    947. if (DateUtil.IsCellDateFormatted(cell)) //日期类型
    948. {
    949. dr[j] = cell.DateCellValue;
    950. }
    951. else //其他数字类型
    952. {
    953. dr[j] = cell.NumericCellValue;
    954. }
    955. }
    956. else
    957. {
    958. dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
    959. }
    960. }
    961. }
    962. dt.Rows.Add(dr);
    963. }
    964. }
    965. return dt;
    966. }
    967. /// <summary>
    968. /// 将DataTable数据导出到Excel文件中(xls)
    969. /// </summary>
    970. /// <param name="dt"></param>
    971. /// <param name="file"></param>
    972. public static void TableToExcelForXLS(DataTable dt, string file)
    973. {
    974. var xssfworkbook = new HSSFWorkbook();
    975. var sheet = xssfworkbook.CreateSheet("Sheet1");
    976. //表头
    977. var row = sheet.CreateRow(0);
    978. for (var i = 0; i < dt.Columns.Count; i++)
    979. {
    980. var cell = row.CreateCell(i);
    981. cell.SetCellValue(dt.Columns[i].ColumnName);
    982. }
    983. //数据
    984. for (var i = 0; i < dt.Rows.Count; i++)
    985. {
    986. var row1 = sheet.CreateRow(i + 1);
    987. for (var j = 0; j < dt.Columns.Count; j++)
    988. {
    989. var cell = row1.CreateCell(j);
    990. cell.SetCellValue(dt.Rows[i][j].ToString());
    991. }
    992. }
    993. //转为字节数组
    994. var stream = new MemoryStream();
    995. xssfworkbook.Write(stream);
    996. var buf = stream.ToArray();
    997. //保存为Excel文件
    998. using (var fs = new FileStream(file, FileMode.Create, FileAccess.Write))
    999. {
    1000. fs.Write(buf, 0, buf.Length);
    1001. fs.Flush();
    1002. }
    1003. }
    1004. /// <summary>
    1005. /// 获取单元格类型(xls)
    1006. /// </summary>
    1007. /// <param name="cell"></param>
    1008. /// <returns></returns>
    1009. private static object GetValueTypeForXLS(HSSFCell cell)
    1010. {
    1011. if (cell == null)
    1012. return null;
    1013. switch (cell.CellType)
    1014. {
    1015. case CellType.Blank: //BLANK:
    1016. return null;
    1017. case CellType.Boolean: //BOOLEAN:
    1018. return cell.BooleanCellValue;
    1019. case CellType.Numeric: //NUMERIC:
    1020. return cell.NumericCellValue;
    1021. case CellType.String: //STRING:
    1022. return cell.StringCellValue;
    1023. case CellType.Error: //ERROR:
    1024. return cell.ErrorCellValue;
    1025. case CellType.Formula: //FORMULA:
    1026. default:
    1027. return "=" + cell.CellFormula;
    1028. }
    1029. }
    1030. #endregion
    1031. #region 转化实体为dataTable
    1032. /// <summary>
    1033. /// Convert a List{T} to a DataTable.
    1034. /// </summary>
    1035. public static DataTable ToDataTable<T>(this List<T> items)
    1036. {
    1037. var tb = new DataTable(typeof(T).Name);
    1038. PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    1039. foreach (PropertyInfo prop in props)
    1040. {
    1041. Type t = GetCoreType(prop.PropertyType);
    1042. tb.Columns.Add(prop.Name, t);
    1043. }
    1044. foreach (T item in items)
    1045. {
    1046. var values = new object[props.Length];
    1047. for (int i = 0; i < props.Length; i++)
    1048. {
    1049. values[i] = props[i].GetValue(item, null);
    1050. }
    1051. tb.Rows.Add(values);
    1052. }
    1053. return tb;
    1054. }
    1055. /// <summary>
    1056. /// Return underlying type if type is Nullable otherwise return the type
    1057. /// </summary>
    1058. public static Type GetCoreType(Type t)
    1059. {
    1060. if (t != null && IsNullable(t))
    1061. {
    1062. if (!t.IsValueType)
    1063. {
    1064. return t;
    1065. }
    1066. else
    1067. {
    1068. return Nullable.GetUnderlyingType(t);
    1069. }
    1070. }
    1071. else
    1072. {
    1073. return t;
    1074. }
    1075. }
    1076. /// <summary>
    1077. /// Determine of specified type is nullable
    1078. /// </summary>
    1079. public static bool IsNullable(Type t)
    1080. {
    1081. return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
    1082. }
    1083. #endregion
    1084. #region datatable to list
    1085. /// <summary>
    1086. /// DataTable转成List
    1087. /// </summary>
    1088. public static List<T> ToDataList<T>(this DataTable dt)
    1089. {
    1090. var list = new List<T>();
    1091. var plist = new List<PropertyInfo>(typeof(T).GetProperties());
    1092. foreach (DataRow item in dt.Rows)
    1093. {
    1094. var s = Activator.CreateInstance<T>();
    1095. for (var i = 0; i < dt.Columns.Count; i++)
    1096. {
    1097. var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
    1098. if (info != null)
    1099. {
    1100. try
    1101. {
    1102. if (!Convert.IsDBNull(item[i]))
    1103. {
    1104. object v = null;
    1105. if (info.PropertyType.ToString().Contains("System.Nullable"))
    1106. {
    1107. v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
    1108. }
    1109. else
    1110. {
    1111. v = Convert.ChangeType(item[i], info.PropertyType);
    1112. }
    1113. info.SetValue(s, v, null);
    1114. }
    1115. }
    1116. catch (Exception ex)
    1117. {
    1118. throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
    1119. }
    1120. }
    1121. }
    1122. list.Add(s);
    1123. }
    1124. return list;
    1125. }
    1126. #endregion
    1127. }
    1128. }
    复制代码
    View Code

    SqlBulkCopyHelper工具类

    1. using System;
    2. using System.Configuration;
    3. using System.Data;
    4. using System.Data.SqlClient;
    5. namespace ELearning.Common.Helpers
    6. {
    7. public class SqlBulkCopyHelper
    8. {
    9. public static void SaveTable(DataTable dtTable)
    10. {
    11. var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    12. var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction) {BulkCopyTimeout = 5000};
    13. try
    14. {
    15. sbc.DestinationTableName = dtTable.TableName;
    16. sbc.WriteToServer(dtTable);
    17. }
    18. catch (Exception ex)
    19. {
    20. //处理异常
    21. }
    22. finally
    23. {
    24. //sqlcmd.Clone();
    25. //srcConnection.Close();
    26. //desConnection.Close();
    27. }
    28. }
    29. }
    30. }
    复制代码

    对应excel实体类:

    1. /// <summary>
    2. /// 导入用户视图模型
    3. /// </summary>
    4. public class InsertAdminUsersViewModel
    5. {
    6. public string 性别 { set; get; }
    7. public string 出生日期 { set; get; }
    8. public string 身份证号 { set; get; }
    9. public string 经销商名称 { set; get; }
    10. public string 经销商岗位 { set; get; }
    11. public string 更新时间 { set; get; }
    12. }
    复制代码

    导入信息接口(InsertAdminUsers):

    140018z7vwfa74wva7w44v.gif
    140018nv66vwqbib2ba8vu.gif
    1. /// <summary>
    2. /// 导入用户
    3. /// </summary>
    4. [HttpPost, Route("api/user/InsertAdminUsers")]
    5. [AllowAnonymous]
    6. public object InsertAdminUsers()
    7. {
    8. var filelist = HttpContext.Current.Request.Files;
    9. var users = new List<InsertAdminUsersViewModel>();
    10. if (filelist.Count > 0)
    11. {
    12. for (var i = 0; i < filelist.Count; i++)
    13. {
    14. var file = filelist[i];
    15. var dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream);//excel转成datatable
    16. users = dataTable.ToDataList<InsertAdminUsersViewModel>();//datatable转成list
    17. }
    18. }
    19. var succe = new List<ESysUser>();
    20. var faile = new List<ESysUser>();
    21. var names = userService.FindList(u => !u.IsDelete).Select(u => u.LoginName).ToList();
    22. //数据list转成数据库实体对应的list
    23. foreach (var u in users)
    24. {
    25. if (string.IsNullOrEmpty(u.状态) || !u.状态.Equals("1"))
    26. continue;
    27. var s = new ESysUser
    28. {
    29. CreateTime = DateTime.Now,
    30. Birthday = DateTime.ParseExact(u.出生日期, "yyyyMMdd", CultureInfo.CurrentCulture),
    31. Email = string.Empty,
    32. IsDelete = false,
    33. ModifyTime = DateTime.ParseExact(u.更新时间, "yyyyMMddHHmmssfff", CultureInfo.CurrentCulture),
    34. UserID = GuidUtil.NewSequentialId(),
    35. UserName = u.职员名称,
    36. UserType = "JXS",
    37. Unumber = u.职员代码,
    38. AgentJobName = u.经销商岗位,
    39. AgentName = u.经销商名称.
    40. CardNo = u.身份证号
    41. };
    42. if (!string.IsNullOrEmpty(s.CardNo) && s.CardNo.Length > 14)
    43. {
    44. var str = s.CardNo.Substring(6, 8);
    45. try
    46. {
    47. s.Birthday = DateTime.ParseExact(str, "yyyyMMdd", CultureInfo.CurrentCulture);
    48. }
    49. catch (Exception e)
    50. {
    51. Console.WriteLine(e);
    52. }
    53. }
    54. var t = names.Where(f => f == s.LoginName);
    55. var p1 = succe.Where(o => o.LoginName == s.LoginName);
    56. if (t.Any() || p1.Any())
    57. {
    58. s.Remark = "登录名重复";
    59. faile.Add(s);
    60. }
    61. else
    62. {
    63. succe.Add(s);
    64. }
    65. }
    66. var dt = succe.ToDataTable();//转成 SqlBulkCopy所需要的类型:datatable
    67. if (string.IsNullOrEmpty(dt.TableName))
    68. dt.TableName = "ESysUser";
    69. var r = succe.Count;
    70. SqlBulkCopyHelper.SaveTable(dt);//批量插入
    71. var list = new { succeed = succe.Take(100).ToList(), failed = faile.Take(100).ToList() }; //数据太多的话,浏览器会崩溃
    72. return OK(list);
    73. }
    复制代码
    View Code

    基本上就是这些了。





    来源:https://www.cnblogs.com/dawenyang/p/11363236.html
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则