阅读:2302
回复:2
|
[技术实例]10.4自定义开发列表导出功能
说明:使用的NPOI导出功能,只是做个小分享,希望可以抛砖引玉,大家可以分享出更好的代码来。
活动:技术分享,享豪礼,动起来! 列使用的Dictionary这样sql中搜出来的列,不想显示的不加在Dictionary就好了。因为前端有些字段还是需要的,只不过太懒了不想改SQL。 效果图: 图片:1.png 图片:2.png HTML代码: <div class="btn-group btn-default searchBtn"> <a href="javascript:void(0);" class="btn btn-default fa fa-file-excel-o" ng-click="ExportExcel()"> 导出Excel </a></div> AngularJS代码: $scope.ExportExcel = function () { var path = PortalRoot + "/TaskTypeList/ExportExcel“; window.location.href = path; } Controller代码: public FileResult ExportExcel(string TypeNo, string TypeName) { var data = _TaskTypeListDal.ExportExcel(); string strFileName = "导出测试.xls"; string strHeaderText = "任务类别"; Dictionary<string, string> columnList = new Dictionary<string, string>(); columnList.Add("TypeNo", "类别编码"); columnList.Add("TypeName", "类别名称"); columnList.Add("CreatedBy", "创建人"); columnList.Add("CreatedDept", "创建部门"); columnList.Add("CreatedTime", "创建时间"); return File(ExportExcelBLL.ExportStreamNoUsing(data, strHeaderText, columnList), "application/vnd.ms-excel", strFileName); } DAL代码: public DataTable ExportExcel(string TypeNo, string TypeName) { SqlHelper sqlHelper = new SqlHelper(); var sql = @"SELECT a.ObjectID,a.TypeName, a.TypeNo,b.Name CreatedBy,c.Name CreatedDept,a.CreatedTime FROM dbo.I_PM_TaskType aLEFT JOIN dbo.OT_User b ON a.CreatedBy=b.ObjectID LEFT JOIN dbo.OT_OrganizationUnit c ON a.CreatedByParentId=c.ObjectID Order By CreatedTime desc"; var data = sqlHelper.ExecuteDataTable(sql, CommandType.Text); return data; } ExportExcelBLL代码: /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="columnNames">列名</param> public static MemoryStream ExportStreamNoUsing(DataTable dtSource, string strHeaderText, Dictionary<string, string> columnList = null) { MemoryStream ms = new MemoryStream(); HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ""; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "Jelly"; si.Title = strHeaderText; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { if (columnList == null) { throw new ArgumentException("参数不正确:columnList不能为空!"); } else { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columnList.Count - 1)); workbook.SetSheetName(0, strHeaderText); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); int columnListIndex = 0; foreach (DataColumn column in dtSource.Columns) { if (columnList.ContainsKey(column.ColumnName)) { headerRow.CreateCell(columnListIndex).SetCellValue(columnList[column.Caption]); headerRow.GetCell(columnListIndex).CellStyle = headStyle; columnListIndex++; //设置列宽 // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //如果传过来的列没有但是datatable有,那么就不需要了 //else //{ // headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //} } } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int columnListIndexDate = 0; foreach (DataColumn column in dtSource.Columns) { if (columnList.ContainsKey(column.ColumnName)) { ICell newCell = dataRow.CreateCell(columnListIndexDate); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } columnListIndexDate++; } } #endregion rowIndex++; } } //自动设置Excel列宽 AutoSizeColumns(sheet); workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } |
|
1楼#
发布于:2018-08-20 14:59
沙发一个~~~ 不明觉厉
|
|
|
2楼#
发布于:2018-08-21 09:13
技术分享奖励您500榴莲币,请查收哦~~~
|
|
|