[技术实例]10.4自定义开发列表导出功能

楼主^#

更多发布于：2018-08-17 15:12

说明：使用的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;
   }

喜欢2

[技术实例]10.4自定义开发列表导出功能

最新喜欢：