NpoiHelper.cs 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156
  1. /* ==============================================================================
  2. * 功能描述:NpoiHelper
  3. * 创 建 者:SAGACLOUD
  4. * 创建日期:2017/8/22 9:28:17
  5. * ==============================================================================*/
  6. using System;
  7. using System.Collections.Generic;
  8. using System.IO;
  9. using System.Linq;
  10. using System.Text;
  11. using System.Threading.Tasks;
  12. using Autodesk.Revit.DB;
  13. using Microsoft.Win32;
  14. using NPOI.SS.UserModel;
  15. using NPOI.XSSF.UserModel;
  16. using CellType = NPOI.SS.UserModel.CellType;
  17. namespace SpacePlugin.CobineWall
  18. {
  19. /// <summary>
  20. /// NpoiHelper
  21. /// </summary>
  22. class NpoiHelper
  23. {
  24. /// <summary>
  25. /// 读取excel新创建的虚拟墙信息
  26. /// </summary>
  27. /// <param name="z"></param>
  28. /// <returns></returns>
  29. public static List<KeyValuePair<XYZ, XYZ>> ReadExcelXyzs(string path,double z)
  30. {
  31. List<KeyValuePair<XYZ, XYZ>> pairs = new List<KeyValuePair<XYZ, XYZ>>();
  32. using (var fp = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
  33. {
  34. XSSFWorkbook book = new XSSFWorkbook(fp);
  35. var sheet = book.GetSheetAt(0);
  36. for (int i = 1; i <= sheet.LastRowNum; i++)
  37. {
  38. var row = sheet.GetRow(i);
  39. var str1 = row.GetCell(1)?.ToString();
  40. var str2 = row.GetCell(2)?.ToString();
  41. var str3 = row.GetCell(3)?.ToString();
  42. var str4 = row.GetCell(4)?.ToString();
  43. if (String.IsNullOrEmpty(str1) || String.IsNullOrEmpty(str2) ||
  44. String.IsNullOrEmpty(str3) || String.IsNullOrEmpty(str4)) continue;
  45. var cell1 = Double.Parse(str1);
  46. var cell2 = Double.Parse(str2);
  47. var cell3 = Double.Parse(str3);
  48. var cell4 = Double.Parse(str4);
  49. XYZ start = new XYZ(cell1, cell2, z);
  50. XYZ end = new XYZ(cell3, cell4, z);
  51. KeyValuePair<XYZ, XYZ> pair = new KeyValuePair<XYZ, XYZ>(start, end);
  52. pairs.Add(pair);
  53. }
  54. }
  55. return pairs;
  56. }
  57. /// <summary>
  58. /// 导出数据
  59. /// </summary>
  60. /// <param name="xyzsList"></param>
  61. public static void ExportToExcel(List<List<XYZ>> xyzsList)
  62. {
  63. SaveFileDialog sflg=new SaveFileDialog();
  64. sflg.Filter = "Excel(*.xlsx)|*.xlsx";
  65. if (sflg.ShowDialog() != true) return;
  66. ExportToExcel(xyzsList, sflg.FileName, sflg.FilterIndex);
  67. }
  68. /// <summary>
  69. /// 导出数据到指定目录
  70. /// </summary>
  71. /// <param name="xyzsList"></param>
  72. /// <param name="fileName"></param>
  73. public static void ExportToExcel(List<List<XYZ>> xyzsList,string fileName,int filterIndex=0)
  74. {
  75. IWorkbook book = new XSSFWorkbook();
  76. ISheet sheet = book.CreateSheet("Summary");
  77. #region 添加表头
  78. IRow row = sheet.CreateRow(0);
  79. ICell cell0 = row.CreateCell(0);
  80. cell0.SetCellType(CellType.String);
  81. cell0.SetCellValue("Edge");
  82. ICell cell1 = row.CreateCell(1);
  83. cell1.SetCellType(CellType.String);
  84. cell1.SetCellValue("端点 X");
  85. ICell cell2 = row.CreateCell(2);
  86. cell2.SetCellType(CellType.String);
  87. cell2.SetCellValue("端点 Y");
  88. ICell cell3 = row.CreateCell(3);
  89. cell3.SetCellType(CellType.String);
  90. cell3.SetCellValue("起点 X");
  91. ICell cell4 = row.CreateCell(4);
  92. cell4.SetCellType(CellType.String);
  93. cell4.SetCellValue("起点 Y");
  94. ICell cell5 = row.CreateCell(5);
  95. cell5.SetCellType(CellType.String);
  96. cell5.SetCellValue("角度");
  97. #endregion
  98. #region 添加数据
  99. int index = 0;
  100. foreach (List<XYZ> xyzs in xyzsList)
  101. {
  102. int count = xyzs.Count;
  103. for (int i = 0; i < count - 1; i++)
  104. {
  105. XYZ xyz0 = xyzs[i];
  106. XYZ xyz1 = xyzs[i + 1];
  107. index++;
  108. row = sheet.CreateRow(index);
  109. ICell cell = row.CreateCell(0, CellType.Numeric);
  110. cell.SetCellValue(index);
  111. cell = row.CreateCell(1, CellType.Numeric);
  112. cell.SetCellValue(xyz1.X);
  113. cell = row.CreateCell(2, CellType.Numeric);
  114. cell.SetCellValue(xyz1.Y);
  115. cell = row.CreateCell(3, CellType.Numeric);
  116. cell.SetCellValue(xyz0.X);
  117. cell = row.CreateCell(4, CellType.Numeric);
  118. cell.SetCellValue(xyz0.Y);
  119. }
  120. }
  121. #endregion
  122. #region 写入
  123. MemoryStream ms = new MemoryStream();
  124. book.Write(ms);
  125. book = null;
  126. using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
  127. {
  128. byte[] data = ms.ToArray();
  129. fs.Write(data, 0, data.Length);
  130. fs.Flush();
  131. }
  132. ms.Close();
  133. ms.Dispose();
  134. #endregion
  135. }
  136. }
  137. }