.net core 转 excel datatable list<t> 互转 xlsx

?

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 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262

using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Runtime.CompilerServices; using System.Threading.Tasks; using Castle.Core.Internal; using Microsoft.AspNetCore.Http; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using OfficeOpenXml;   namespace Gaea.OfficeManagement { public static class ExcelHelper {   public static DataTable ExcelToTable (Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { ExcelPackage package = new ExcelPackage(ExcelFileStream); ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial; ExcelWorksheet worksheet = package.Workbook.Worksheets[SheetIndex];//选定 指定页   int rows = worksheet.Dimension.End.Row; //获取worksheet的列数 int cols = worksheet.Dimension.End.Column;   DataTable dt = new DataTable(worksheet.Name); DataRow dr = null; for (int i = HeaderRowIndex; i <= rows; i++) { if (i > 1) dr = dt.Rows.Add();   for (int j = 1; j <= cols; j++) { //默认将第一行设置为datatable的标题 if (i == HeaderRowIndex) dt.Columns.Add(GetString(worksheet.Cells[i, j].Value)); //剩下的写入datatable else dr[j - 1] = GetString(worksheet.Cells[i, j].Value); } } return dt; }   private static string GetString(object obj) { try { return obj.ToString(); } catch (Exception ex) { return ""; } }     /// <summary> /// 将excel转换为datatable /// </summary> /// <param name="filePaht">文件路径</param> /// <param name="startRow">读取数据的起始行</param> /// <returns>DataTable</returns> public static DataTable ExcelToDataTable(IFormFile file, int startRow) {   MemoryStream ms = new MemoryStream(); file.CopyTo(ms); ms.Seek(0, SeekOrigin.Begin);   //声明一个变量 ISheet sheet = null; DataTable data = new DataTable(); //int startRow = 3; try { //读取excel文件 //FileStream fs = new FileStream(filePaht, FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(ms); //读取excel的第一个sheet页 sheet = workbook.GetSheetAt(0); if (sheet != null) { //起始行 IRow firstRow = sheet.GetRow(startRow); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 //循环添加列 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { //如果导入的是端午活动的手机号码 if(startRow==1) { firstRow.Cells[0].SetCellType(CellType.String); }   //添加列 DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue); data.Columns.Add(column); } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow+1; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null　　　　　　　   //创建行 DataRow dataRow = data.NewRow(); int index = 0; for (int j = row.FirstCellNum; j < cellCount; ++j) { //填充列 dataRow[index] = row.GetCell(j)?.ToString(); index++; } //填充行 data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } }   public static async Task<MemoryStream> ExportAsync(this DataTable table) { var stream = new MemoryStream();   //如果您在非商业环境中使用EPPlus //根据Polyform非商业许可证： ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;   using (ExcelPackage package = new ExcelPackage()) { // add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1"); //First add the headers for (int i = 1; i <= table.Columns.Count; i++) { worksheet.Cells[1, i].Value = $"{(table.Columns[i - 1].Caption.IsNullOrWhiteSpace() ? table.Columns[i - 1].ColumnName : table.Columns[i - 1].Caption)}"; worksheet.Cells[1, i].Style.Font.Bold = true; } //worksheet.Cells[1, 1].Value = "ID"; //worksheet.Cells[1, 2].Value = "Name"; //worksheet.Cells[1, 3].Value = "Gender"; //worksheet.Cells[1, 4].Value = "Salary (in $)";   for (int i = 2; i <= table.Rows.Count; i++) { for (int j = 1; j <= table.Columns.Count; j++) { worksheet.Cells[i, j].Value = $"{table.Rows[i - 2][j - 1]}"; } }   ////Add values //worksheet.Cells["A2"].Value = 1000; //worksheet.Cells["B2"].Value = "Jon"; //worksheet.Cells["C2"].Value = "M"; //worksheet.Cells["D2"].Value = 5000;   //worksheet.Cells["A3"].Value = 1001; //worksheet.Cells["B3"].Value = "Graham"; //worksheet.Cells["C3"].Value = "M"; //worksheet.Cells["D3"].Value = 10000;   //worksheet.Cells["A4"].Value = 1002; //worksheet.Cells["B4"].Value = "Jenny"; //worksheet.Cells["C4"].Value = "F"; //worksheet.Cells["D4"].Value = 5000;   //package.Save(); //Save the workbook. await package.SaveAsAsync(stream);   stream.Seek(0, SeekOrigin.Begin);//没这句话就格式错   return stream; } }   public static DataTable ToDataTable<T>(this IEnumerable<T> collection) { var props = typeof(T).GetProperties(); var dt = new DataTable(); dt.Columns.AddRange(props.Select(p => { var col = new DataColumn(p.Name, p.PropertyType);     var attrdisplay = p.GetAttributes<DisplayAttribute>().FirstOrDefault(); if (attrdisplay != default) { if (!string.IsNullOrWhiteSpace(attrdisplay.Name)) { col.Caption = attrdisplay.Name; } else   if (!string.IsNullOrWhiteSpace(attrdisplay.ShortName)) { col.Caption = $"{attrdisplay.ShortName}"; } else if (!string.IsNullOrWhiteSpace(attrdisplay.Description)) { col.Caption = attrdisplay.Description; } }   var attrdisplayname = p.GetAttributes<DisplayNameAttribute>().FirstOrDefault(); if (attrdisplayname != default && !string.IsNullOrWhiteSpace(attrdisplayname.DisplayName)) { col.Caption = $"{attrdisplayname.DisplayName}"; }   var attrdesc = p.GetAttributes<DescriptionAttribute>().FirstOrDefault(); if (attrdesc != default && !string.IsNullOrWhiteSpace(attrdesc.Description)) { col.Caption = $"{attrdesc.Description}"; }   var attrcol = p.GetAttributes<ColumnAttribute>().FirstOrDefault(); if (attrcol != default && !string.IsNullOrWhiteSpace(attrcol.Name)) { col.Caption = $"{attrcol.Name}"; }     return col; }).ToArray()); if (collection.Count() > 0) { for (int i = 0; i < collection.Count(); i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in props) { object obj = pi.GetValue(collection.ElementAt(i), null); tempList.Add(obj); } object[] array = tempList.ToArray(); dt.LoadDataRow(array, true); } } return dt; }   } }