以下方式是本人总结的一些经验,肯定有很多种方法,在此先记下,留待以后补充...
希望朋友们一起来探讨相关想法,请在下方留言.
A-1:EXCEL模板导出
非常简单,将EXCEL模板上传到项目中后,将其浏览URL保存下来(excelUrl),然后:
window.location.href="http://localhost:10086/yuanxin/Resources/BusRoute.xlsx" //EXCEL浏览路径
或者使用<a href="excelUrl"></a>都行。
A-2:EXCEL数据导出--Web项目--C#代码导出:
/// <summary> /// EXCEL帮助类 /// </summary> /// <typeparam name="T">泛型类</typeparam> /// <typeparam name="TCollection">泛型类集合</typeparam> public class ExcelHelp<T, TCollection> where T : new() where TCollection : List<T>, new() { //http请求Request对象 public static HttpRequest baseRequest = HttpContext.Current.Request; //http请求Response对象 public static HttpResponse baseResponse = HttpContext.Current.Response;/// <summary> /// 将数据导出EXCEL /// </summary> /// <param name="columnNameAndShowNameDic">列名+显示名</param> /// <param name="tColl">数据集(tColl里的类属性名必须和字典中的列名一致)</param> public static void ExportExcelData(Dictionary<string, string> columnNameAndShowNameDic, TCollection tColl) { WorkBook workbook = WorkBook.CreateNew(); WorkSheet worksheet = workbook.Sheets["sheet1"]; List<string> columnNameList = columnNameAndShowNameDic.Keys.ToList(); List<string> showNameList = columnNameAndShowNameDic.Values.ToList(); //暂定26行 string[] zm = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; //设置首列显示 for (var i = 0; i < columnNameList.Count; i++) { worksheet.Cells[zm[i] + "1"].Value = showNameList[i]; //加粗 worksheet.Cells[zm[i] + "1"].Style.Font.Bold = true; } for (int i = 0; i < tColl.Count; i++) { for (int j = 0; j < columnNameList.Count; j++) { worksheet.Cells[zm[j] + (i + 2)].Value = getPropertyValue(tColl[i], columnNameList[j]); } } byte[] buffer = workbook.SaveAsBytes(); baseResponse.Clear(); baseResponse.Buffer = true; baseResponse.ContentEncoding = System.Text.Encoding.UTF8; //baseResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; baseResponse.ContentType = "application/vnd.ms-excel"; //设置导出文件名 baseResponse.AddHeader("content-disposition", "attachment; filename=" + "MaintainReport" + ".xlsx"); baseResponse.AddHeader("Content-Length", buffer.Length.ToString()); baseResponse.BinaryWrite(buffer); baseResponse.Flush(); baseResponse.End(); } /// <summary> /// 获取属性值 /// </summary> /// <param name="t">T对象实例</param> /// <param name="propertyName">属性名</param> /// <returns></returns> public static string getPropertyValue(T t, string propertyName) { PropertyInfo info = t.GetType().GetProperty(propertyName); //获取属性值转换暂设置如下字段,可根据实际情况添加 if (info.PropertyType == typeof(DateTime)) { return Convert.ToDateTime(info.GetValue(t)).ToString("yyyy-MM-dd HH:mm"); } return info.GetValue(t).ToString(); } }
A-2--EXCEL数据导出--WebAPI项目--NPOI--接口导出:
说明:以下方法借助第三方插件:NPOI专门处理EXCEL等办公文档的类库,因此项目中需引用该插件,NuGet--所有NPOI即可安装。
/// <summary> /// 班车数据导出成EXCEL /// </summary> /// <param name="conferenceID"></param> /// <returns></returns> [HttpGet] public HttpResponseMessage ExportExcelDataForBusRoute(string conferenceID) { HttpResponseMessage result = new HttpResponseMessage(); ControllerHelp.RunAction(() => { BusRouteModelCollection busColl = BusRouteModelAdapter.Instance.Load(m => m.AppendItem("ConferenceID", conferenceID)); Dictionary<string, string> dicColl = new Dictionary<string, string>() { {"路线标题","Title" }, {"发车时间","DepartDate" }, {"出发地","BeginPlace" }, {"对接人","ContactsName" }, {"对接人电话","ContactsPhone" } }; result = ExcelHelp<BusRouteModel, BusRouteModelCollection>.ExportExcelData(dicColl, busColl, "BusRoute"); }); return result; }
/// <summary> /// 得到excel文件流 /// </summary> /// <returns></returns> private MemoryStream ExcelStream(Dictionary<string, string> displayAndColumnName, TCollection tlist) { //var list = dc.v_bs_dj_bbcdd1.Where(eps).ToList(); HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); //1-创建首行 IRow headRow = sheet1.CreateRow(0); //1.1-设置首行样式 IFont font = workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; //字体加粗 ICellStyle headCellStyle = workbook.CreateCellStyle(); headCellStyle.SetFont(font); headCellStyle.Alignment = HorizontalAlignment.Center; //字体居中 List<string> displayNameList = displayAndColumnName.Keys.ToList(); //1.2-设置首行标题 for (var i = 0; i < displayAndColumnName.Keys.Count; i++) { ICell cell = headRow.CreateCell(i); cell.SetCellValue(displayNameList[i].ToString()); cell.CellStyle = headCellStyle; } //2-设置内容列 List<string> columnNameList = displayAndColumnName.Values.ToList(); //2.1-内容行样式 ICellStyle contentCellStyle = workbook.CreateCellStyle(); contentCellStyle.Alignment = HorizontalAlignment.Center; //字体居中 for (var i = 0; i < tlist.Count; i++) { IRow row = sheet1.CreateRow(i + 1); //设置内容 for (var j = 0; j < columnNameList.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(getPropertyValue(tlist[i], columnNameList[j])); cell.CellStyle = contentCellStyle; } } //3-自动列宽-根据内容长度自动展开 for (int i = 0; i < displayNameList.Count; i++) { sheet1.AutoSizeColumn(i); } MemoryStream file = new MemoryStream(); workbook.Write(file); //这句代码非常重要,如果不加,会报:打开的EXCEL格式与扩展名指定的格式不一致 file.Seek(0, SeekOrigin.Begin); return file; } /// <summary> /// 导出excel功能控制器 /// </summary> /// <returns></returns> public HttpResponseMessage ExportExcelDataForWebAPI(Dictionary<string, string> displayAndColumnName, TCollection tlist,string exportFileName) { var file = ExcelStream(displayAndColumnName, tlist); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StreamContent(file); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = exportFileName+".xls"; return result; }
B-1:EXCEL数据导入--C#获取数据:
public class ExcelHelp { //获取http请求Request对象 public static HttpRequest baseRequest = HttpContext.Current.Request; //获取http请求Response对象 public static HttpResponse baseResponse = HttpContext.Current.Response; /// <summary> /// 获取Post请求EXCEL文件数据 /// </summary> /// <param name="fileFormName">EXCEL的表单名称</param> /// <returns></returns> public static DataTable GetExcelData(string fileFormName) { //获取Post请求--表单文件的文件名 string uploadFileName = baseRequest.Files[fileFormName].FileName; //上传文件到指定目录(项目根目录/Resources/uploadFileName)--可根据项目文件目录进行调整 string path = HttpContext.Current.Server.MapPath("~/Resources/" + uploadFileName); baseRequest.Files[fileFormName].SaveAs(path); DataTable dataTable = new DataTable(); DataSet dataSet = new DataSet(); //解析文件数据,并存入服务器 //备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。 //"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。 string strConn = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1';", path); OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn); oada.Fill(dataSet); ) { dataTable = dataSet.Tables[]; } if (System.IO.File.Exists(path)) { //如果存在则删除 System.IO.File.Delete(path); } return dataTable; } }
B-2--EXCEL数据导入--NPOI--C#获取数据:
说明:以下方法借助第三方插件:NPOI专门处理EXCEL等办公文档的类库,因此项目中需引用该插件,NuGet--所有NPOI即可安装。
public HSSFWorkbook GetExcelDataByNPOI(string fileFormName) { HSSFWorkbook workbook = new HSSFWorkbook(); //获取Post提交的EXCEL文件 HttpPostedFile postFile = baseRequest.Files[fileFormName]; string uploadFileName = postFile.FileName; //设置Excel临时保存路径(路径可修改,可作为参数传入) string path = HttpContext.Current.Server.MapPath("~/Resources/" + uploadFileName); postFile.SaveAs(path); System.IO.File.Delete(path); //文件流 using (Stream stream = new FileStream(path, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(stream); ISheet sheet = workbook.GetSheetAt(); //Execel第一行是标题,不是要导入数据库的数据 //for (int i = 1; i <= sheet.LastRowNum; i++) //{ // IRow row = sheet.GetRow(i); // //获取行的文本内容 // string UserName = row.GetCell(0).StringCellValue; // //获取行的时间内容 // DateTime time = row.GetCell(4).DateCellValue; // //获取行的数字内容 // double num = row.GetCell(3).NumericCellValue; // //获取行的bool内容 // bool b = row.GetCell(3).BooleanCellValue; //} } return workbook; } #endregion
备注: 使用C#创建EXCEL,暂未研究,以后有空再做补充。
在编写本博客时,查阅了下面帮助博客:导入excel错误:外部表不是预期的格式 解决方案