下载本地execl和导入execl c#

有时我们需要把execl表从本地(服务器本地)下载下来当模板使用，然后根据模板填写信息，将execl表导入数据库中。这是我们就不得不了解execl表的导入导出了，下面分享一下我的代码；

下载本地execl

/// <summary>/// 下载/// </summary>/// <returns></returns>[HttpGet]public HttpResponseMessage Download(){string fileName = \"下载.xls\";string filePath = HttpContext.Current.Server.MapPath(\"~/\") + \"xxx\\\\xxx\\\\xxx\\\\\" + \"下载.xls\";FileStream stream = new FileStream(filePath, FileMode.Open);HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);response.Content = new StreamContent(stream);response.Content.Headers.ContentType = new MediaTypeHeaderValue(\"application/octet-stream\");response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue(\"attachment\") {FileName= HttpUtility.UrlEncode(fileName)};response.Headers.Add(\"Access-Control-Expose-Headers\", \"FileName\");response.Headers.Add(\"FileName\", HttpUtility.UrlEncode(fileName));return response;}

导入execl

实现方法

/// <summary>/// 导入/// </summary>/// <returns></returns>[HttpPost]public async Task<TestClass> Implementation(){var fileList = HttpContext.Current.Request.Files;if (fileList .Count == 0){throw new Exception(\"操作失败!没有上传文件!\");}var file = filelist[0];List<TestClass> result= new List<TestClass>();IWorkbook readWorkbook = WorkbookFactory.Create(file.InputStream);ISheet sheet = readWorkbook.GetSheetAt(0); //读取第一个sheetstring resError = \"\";DataTable dtSheet = GetDataFromSheet(sheet, out resError );string ID= \"\";string Name= \"\";string Code= \"\";for (int i = 0; i < dtSheet.Rows[0].ItemArray.Length; i++){if (dtSheet.Rows[0][i].ToString()== \"ID\")ID= i.ToString();if (dtSheet.Rows[0][i].ToString() == \"姓名\")Name= i.ToString();if (dtSheet.Rows[0][i].ToString() == \"编号\")Code= i.ToString();}if (ID.IsNullOrEmpty() || Name.IsNullOrEmpty() || Code.IsNullOrEmpty())throw new Exceptions(\"表格不合法\");for (int i = 1; i < dtSheet.Rows.Count; i++){result.Add(new TestClass(){ID= dtSheet.Rows[i][0].ToString(),Name= dtSheet.Rows[i][1].ToString(),Code= dtSheet.Rows[i][2].ToString(),});}return result;}

帮助类
这里主要是把execl表数据类型转化成DataTable类型,需要引入using NPOI.SS.UserModel;命名空间

private static DataTable GetDataFromSheet(ISheet wsheet, out string resError){try{DataTable result = new DataTable();resError= \"\";//取sheet最大列数int max_column = 0;for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++){IRow rsheet = wsheet.GetRow(i);if (rsheet != null && rsheet.LastCellNum > max_column){max_column = rsheet.LastCellNum;}}//给DataTable添加列for (int i = 0; i < max_column; i++){result .Columns.Add(\"A\" + i.ToString());}for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++){DataRow dRow = result .NewRow();IRow rsheet = wsheet.GetRow(i);if (rsheet == null) continue;for (int j = rsheet.FirstCellNum; j < rsheet.LastCellNum; j++){ICell csheet = rsheet.GetCell(j);if (csheet == null) continue;switch (csheet.CellType){case CellType.Blank:dRow[j] = \"\";break;case CellType.Boolean:dRow[j] = csheet.BooleanCellValue;break;case CellType.Error:dRow[j] = csheet.ErrorCellValue;break;case CellType.Formula:try{dRow[j] = csheet.NumericCellValue;short format1 = csheet.CellStyle.DataFormat;if (format1 == 177 || format1 == 178 || format1 == 188){dRow[j] = csheet.NumericCellValue.ToString(\"#0.00\");}}catch{dRow[j] = csheet.StringCellValue.Trim();}break;case CellType.Numeric:try{short format2 = csheet.CellStyle.DataFormat;if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58){dRow[j] = csheet.DateCellValue;}else{dRow[j] = csheet.NumericCellValue;}if (format2 == 177 || format2 == 178 || format2 == 188){dRow[j] = csheet.NumericCellValue.ToString(\"#0.00\");}}catch{dRow[j] = csheet.StringCellValue.Trim();}break;case CellType.String:dRow[j] = csheet.StringCellValue.Trim();break;default:dRow[j] = csheet.StringCellValue.Trim();break;}}result .Rows.Add(dRow);}return result ;}catch (Exception ex){resError= ex.Message.ToString();return null;}}

实体类

public class TestClass{#region ID + ID/// <summary>/// ID/// </summary>public string ID { get; set; }#endregion#region 姓名+ Name/// <summary>/// 姓名/// </summary>public string Name{ get; set; }#endregion#region 编号+ Code/// <summary>/// 编号/// </summary>public string Code { get; set; }#endregion}