把查询的数据导出到elsx表关于流的概念

1.获取到需要导出的数据因为这个对象是PagedResulDto类型的所以封装成Table的时候传pageList.Items就可以了

            PagedResultDto<SearchSatisfactionUnfeedbackTaskLineOutput> pageList = await feedbackService.SearchSatisfactionUnfeedbackTaskLine(input);
            DataTable dt = SatisfactionDataTable(pageList.Items);
            return ExportExcel(dt);

2.把数据放到Table中

        private DataTable SatisfactionDataTable(IReadOnlyList<SearchSatisfactionUnfeedbackTaskLineOutput> list)
        {
            DataTable dt = new DataTable("满意度问卷调查");
            //添加列
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.FeedbackTime), Caption = "回访日期", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.CustomerName), Caption = "客户姓名", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.Telephone), Caption = "联系电话", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AccountModelProvinceName), Caption = "省", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AccountModelCityName), Caption = "市", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.QualityScore), Caption = "产品实物质量（50%）", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.TimelinessScore), Caption = "交付及时性（20%）", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.ServiceScore), Caption = "服务质量（15%）", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AppearanceScore), Caption = "产品外观（15%）", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.QuestionnaireContent), Caption = "综合评价及建议", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.remarks), Caption = "备注", DataType = typeof(string) });
            dt.Columns.Add(new DataColumn() { ColumnName = nameof(SearchSatisfactionUnfeedbackTaskLineOutput.FeedbackData), Caption = "得分", DataType = typeof(string) });
            foreach (var model in list)
            {
                //添加行
                DataRow row = dt.NewRow();
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.FeedbackTime)] = model.FeedbackTime.Value.ToLongDateString();
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.CustomerName)] = model.CustomerName;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.Telephone)] = Publics.MaskTelephone(model.Telephone);
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AccountModelProvinceName)] = model.AccountModelProvinceName;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AccountModelCityName)] = model.AccountModelCityName;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.QualityScore)] = model.QualityScore;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.TimelinessScore)] = model.TimelinessScore;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.ServiceScore)] = model.ServiceScore;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.AppearanceScore)] = model.AppearanceScore;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.QuestionnaireContent)] = model.QuestionnaireContent;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.remarks)] = model.remarks;
                row[nameof(SearchSatisfactionUnfeedbackTaskLineOutput.FeedbackData)] = model.FeedbackData;
                dt.Rows.Add(row);
            }
            return dt;

        }

3.把table中的数据放到xlsx工作表中创建XSSFWorkbook对象和ISheet对象需要引用NPOI包可以在官网下载

        private FileResult ExportExcel(DataTable dt)
        {
            //操作xlsx表的对象
            XSSFWorkbook book = new XSSFWorkbook();
            //创建一个工作表
            ISheet sheet = book.CreateSheet(dt.TableName);
            ;
            //给xlsx工作表创建第0行
            IRow headerRow = sheet.CreateRow(rowIndex);
            rowIndex++;
            //设置样式
            ICellStyle style = book.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.Center;
            //循环table中的列名  放到xlsx中当列名
            ; i < dt.Columns.Count; i++)
            {
                //获取当前列 创建第i个单元格 把列名放到单元格中
                DataColumn column = dt.Columns[i];
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(column.Caption);
            }
            //循环table中的数据
            foreach (DataRow dataRow in dt.Rows)
            {
                //在xlsx工作表中创建第rowIndex行
                IRow row = sheet.CreateRow(rowIndex);
                ; i < dt.Columns.Count; i++)
                {
                    //每次都创建一个cell单元格 来接收当前列的数据值
                    DataColumn column = dt.Columns[i];
                    ICell cell = row.CreateCell(i);
                    if (column.DataType == typeof(string))
                    {
                        if (dataRow[column] != null)
                        {
                            cell.SetCellValue(dataRow[column].ToString());
                        }
                    }
                    else if (column.DataType == typeof(int))
                    {
                        if (dataRow[column] != null)
                        {
                            cell.SetCellValue(double.Parse(dataRow[column].ToString()));
                        }
                    }
                    else if (column.DataType == typeof(DateTime))
                    {
                        if (dataRow[column] != null)
                        {
                            //cell.SetCellValue(DateTime.Parse(dataRow[column].ToString()));
                            cell.SetCellValue(dataRow[column].ToString());
                        }
                    }
                }
                rowIndex++;
            }
            ; i < dt.Columns.Count; i++)
            {
                //设置第i列宽度自适应
                sheet.AutoSizeColumn(i);
            }

            var ms = new NPOIMemoryStream();
            book.Write(ms);
            ms.Flush();
            ms.Position = ;
            return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", dt.TableName + ".xlsx");
        }

4.导出需要用到的一个对象

    public class NPOIMemoryStream : MemoryStream
    {
        /// <summary>
        /// 获取流是否关闭
        /// </summary>
        public bool IsColse
        {
            get;
            private set;
        }

        public NPOIMemoryStream(bool colse = false)
        {
            IsColse = colse;
        }

        public override void Close()
        {
            if (IsColse)
            {
                base.Close();
            }
        }
    }

5.返回是调用File() 第一个参数当前流第二个参数 book(当前xlsx工作表的地址可以调试看到) 第三个参数要下载的xlsx的名字

关于ISheet接口中的属性方法介绍

https://github.com/dotnetcore/NPOI/blob/master/src/NPOI/SS/UserModel/Sheet.cs

关于Stream、FileStream、MemoryStream的区别

***********************************

巴特西

把查询的数据导出到elsx表关于流的概念

最新文章

热门文章

巴特西

把查询的数据导出到elsx表 关于流的概念

最新文章

热门文章

把查询的数据导出到elsx表关于流的概念