public async Task<ActionResult> ExportDataToExcel()
        {
            //查询出的数据
            var list = new List<TestDataViewModel>();

            list.Add(new TestDataViewModel
            {
                PlanSubject = "test1",
                MobileNo = "13577778888",
                TelcoOperatorText = "移动"
            });
            list.Add(new TestDataViewModel
            {
                PlanSubject = "test2",
                MobileNo = "13577779999",
                TelcoOperatorText = "移动"
            });
            list.Add(new TestDataViewModel
            {
                PlanSubject = "test3",
                MobileNo = "8077778888",
                TelcoOperatorText = "电信"
            });
            //var model = await DataTrafficGiftRecordQueryEntry.SelectAsync();

            //已经领取了流量
            //var hasGetList = model.Where(m => !string.IsNullOrWhiteSpace(m.MobileNo) && m.MobileNo.Length == 11);
            //var result = hasGetList.MapList<DataTrafficGiftRecordViewModel>().ToList();

            var setting = new ExcelExportOption<TestDataViewModel>
            {
                FileName = "客户领取流量记录",
                DefaultRowHeight = 270
            };
            if (list.Count() >= 65535)
                setting.Description = "结果数据总数:" + list.Count() + "但Excel最多支持65535条数据, 请分开条件导出";
            setting.Source = list.Take(65534);
            var colums = new List<ExcelColum<TestDataViewModel>>
            {
                new ExcelColum<TestDataViewModel>
                {
                    Name = "赠送计划标题",
                    Width = 7000,
                    ColumType = ExcelColumType.String,
                    ResultFunc = x => x.PlanSubject
                },
                new ExcelColum<TestDataViewModel>
                {
                    Name = "手机号码",
                    Width = 7000,
                    ColumType = ExcelColumType.String,
                    ResultFunc = x => x.MobileNo
                },

                 new ExcelColum<TestDataViewModel>
                {
                    Name = "手机运营商",
                    Width = 7000,
                    ColumType = ExcelColumType.String,
                    ResultFunc = x => x.TelcoOperatorText
                },
            };

            setting.Colums = colums;

            //发送文件路径到客户端
            var steam = ExcelUtility.ExportToStream(setting);
            var filename = HttpUtility.UrlEncode(setting.FileName + DateTime.Now.ToString("_yyyyMMdd-HHmmss") + ".xls", System.Text.Encoding.UTF8);
            return File(steam, "application/vnd.ms-excel", filename);
            //Response.AddHeader("Content-Disposition", $"attachment;filename={filename}");
            //Response.Clear();
            //Response.BinaryWrite(steam.GetBuffer());
            //Response.End();
        }
예제 #2
0
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static MemoryStream ExportToStream <T>(ExcelExportOption <T> setting)
        {
            var cols        = setting.Colums.Count();
            var hssworkbook = new HSSFWorkbook();
            var sheet1      = hssworkbook.CreateSheet("Sheet1");

            if (setting.DefaultRowHeight > 0)
            {
                sheet1.DefaultRowHeight = setting.DefaultRowHeight;
            }

            var docSummaryInfo = PropertySetFactory.CreateDocumentSummaryInformation();

            docSummaryInfo.Company = string.Empty;
            var summaryInfo = PropertySetFactory.CreateSummaryInformation();

            summaryInfo.Subject = setting.Title;
            hssworkbook.DocumentSummaryInformation = docSummaryInfo;
            hssworkbook.SummaryInformation         = summaryInfo;

            //自动调整某列宽度
            for (var i = 0; i < cols; i++)
            {
                sheet1.AutoSizeColumn(i, true);
            }

            var currentRowIndex = 0;

            //设置标题信息
            if (!String.IsNullOrWhiteSpace(setting.Title))
            {
                var row0 = sheet1.CreateRow(currentRowIndex);
                for (var i = 0; i < cols; i++)
                {
                    row0.CreateCell(i).Row.Height = 600;
                }
                row0.GetCell(0).SetCellValue(setting.Title);

                currentRowIndex++;

                //标题样式
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
                var titlestyle = hssworkbook.CreateCellStyle();
                titlestyle.WrapText = true;
                var titleFont = hssworkbook.CreateFont();
                titleFont.FontHeight = 20 * 20;
                titlestyle.SetFont(titleFont);
                titlestyle.Alignment = HorizontalAlignment.Center;
                sheet1.GetRow(0).GetCell(0).CellStyle = titlestyle;
            }

            //设置描述信息
            if (!string.IsNullOrWhiteSpace(setting.Description))
            {
                var row1 = sheet1.CreateRow(currentRowIndex);
                for (var i = 0; i < cols; i++)
                {
                    row1.CreateCell(i).Row.Height = 1000;
                }
                row1.GetCell(0).SetCellValue(setting.Description);

                currentRowIndex++;

                //描述样式
                sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, cols - 1));
            }

            //设置列分组
            if (setting.ColumGroups != null && setting.ColumGroups.Any())
            {
                var rowGroup   = sheet1.CreateRow(currentRowIndex);
                var columGroup = setting.ColumGroups;

                for (var i = 0; i < cols; i++)
                {
                    var cell = rowGroup.CreateCell(i, CellType.String);
                    cell.SetCellValue(String.Empty);
                    cell.Row.Height = 500;
                }
                var groupStyle = hssworkbook.CreateCellStyle();
                var groupFont  = hssworkbook.CreateFont();
                groupFont.FontHeight = 15 * 15;
                groupFont.IsBold     = true;
                groupStyle.SetFont(groupFont);
                groupStyle.Alignment = HorizontalAlignment.Center;

                var excelColumGroups = columGroup as ExcelColumGroup[] ?? columGroup.ToArray();
                for (var j = excelColumGroups.Length - 1; j >= 0; j--)
                {
                    var cell = rowGroup.GetCell(excelColumGroups.ElementAt(j).StartIndex);
                    cell.SetCellValue(excelColumGroups.ElementAt(j).Name);
                    cell.CellStyle = groupStyle;
                    sheet1.AddMergedRegion(new CellRangeAddress(2, 2, excelColumGroups.ElementAt(j).StartIndex, excelColumGroups.ElementAt(j).StartIndex + excelColumGroups.ElementAt(j).Cols - 1));
                }

                currentRowIndex++;
            }

            //列头行索引
            var columHeaderRowIndex = currentRowIndex;

            //列头样式
            var headerfont = hssworkbook.CreateFont();

            headerfont.FontHeightInPoints = 12;
            headerfont.IsBold             = true;
            var headerstyle = hssworkbook.CreateCellStyle();

            headerstyle.SetFont(headerfont);

            //设置列头信息
            var row2 = sheet1.CreateRow(columHeaderRowIndex);

            for (var i = 0; i < cols; i++)
            {
                var cell = row2.CreateCell(i);
                cell.Row.Height = 500;
                cell.SetCellValue(setting.Colums.ElementAt(i).Name);
                cell.CellStyle = headerstyle;
            }

            var dataStyle = hssworkbook.CreateCellStyle();

            //水平对齐
            dataStyle.Alignment = HorizontalAlignment.Left;
            //垂直对齐
            dataStyle.VerticalAlignment = VerticalAlignment.Top;
            //自动换行
            dataStyle.WrapText = true;
            //输出数据
            for (var i = 0; i < setting.Source.Count(); i++)
            {
                var model   = setting.Source.ElementAt(i);
                var rowTemp = sheet1.CreateRow(i + columHeaderRowIndex + 1);

                //Type modelType = model.GetType();
                //PropertyInfo[] properties = modelType.GetProperties();
                //var proDic = new Dictionary<string, PropertyInfo>();
                //foreach (var item in properties)
                //{
                //    proDic.Add(item.Name, item);
                //}
                for (var j = 0; j < setting.Colums.Count(); j++)
                {
                    rowTemp.CreateCell(j).SetCellValueT(
                        setting.Colums.ElementAt(j).ColumType,
                        dataStyle,
                        setting.Colums.ElementAt(j).ResultFunc == null ? setting.Colums.ElementAt(j).ResultByFieldName(model, setting.Colums.ElementAt(j).FieldName) : (setting.Colums.ElementAt(j).ResultFunc(model)));
                }
            }

            HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssworkbook);
            //对设定并可行的列求和
            var containSum = setting.Colums.Any(item => item.DoColumSum);

            if (containSum)
            {
                var style = hssworkbook.CreateCellStyle();
                var font  = hssworkbook.CreateFont();
                font.FontHeight = 15 * 15;
                font.IsBold     = true;
                font.Color      = HSSFColor.Blue.Index;


                style.SetFont(font);


                //求和 SUM(A2:A12)
                var letter   = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                var totalRow = sheet1.CreateRow(columHeaderRowIndex + 1 + setting.Source.Count());
                for (var x = 0; x < setting.Colums.Count(); x++)
                {
                    var rowName = x == 0 ? "A" : (x > 26 ? letter.ElementAt(x / 26).ToString() + letter.ElementAt(x % 26).ToString() : letter.ElementAt(x % 26).ToString());
                    if (setting.Colums.ElementAt(x).DoColumSum)
                    {
                        var tempCell = totalRow.CreateCell(x);
                        tempCell.CellFormula = "SUM(" + rowName + (columHeaderRowIndex + 2) + ":" + rowName + (columHeaderRowIndex + 1 + setting.Source.Count()) + ")";
                        tempCell.CellStyle   = style;
                        e.EvaluateInCell(tempCell);
                    }
                    else
                    {
                        var temp = totalRow.CreateCell(x);
                        temp.CellStyle = style;
                        if (x == 0)
                        {
                            temp.SetCellValue("合计");
                        }
                    }
                }
            }

            //表格样式
            for (var i = 0; i < cols; i++)
            {
                if (setting.Colums.ElementAt(i).Width > 0)
                {
                    sheet1.SetColumnWidth(i, setting.Colums.ElementAt(i).Width);
                }
            }

            var stream = new MemoryStream();

            hssworkbook.Write(stream);
            return(stream);
        }
        public async Task ExportDataToExcel2()

        {
            //查询出的数据
            // 1.获取数据集合
            var list = new List<UserViewModel>() {
                    new UserViewModel{Name="刘一",Age=22,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=80,MathScores=90}},
                    new UserViewModel{Name="陈二",Age=23,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=81,MathScores=91} },
                    new UserViewModel{Name="张三",Age=24,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=82,MathScores=92} },
                    new UserViewModel{Name="李四",Age=25,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=83,MathScores=93} },
                    new UserViewModel{Name="王五",Age=26,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=84,MathScores=94} },
                };

            var setting = new ExcelExportOption<UserViewModel>
            {
                FileName = "客户领取流量记录",
                DefaultRowHeight = 270
            };
            if (list.Count() >= 65535)
                setting.Description = "结果数据总数:" + list.Count() + "但Excel最多支持65535条数据, 请分开条件导出";
            setting.Source = list.Take(65534);


            // 2.设置单元格抬头
            // key:实体对象属性名称,可通过反射获取值
            // value:Excel列的名称
            Dictionary<string, string> cellheader = new Dictionary<string, string> {
                    { "Name", "姓名" },
                    { "Age", "年龄" },
                    { "GenderName", "性别" },
                    { "TranscriptsEn", "成绩" },
                    { "TranscriptsEn.ChineseScores", "语文成绩" },
                    { "TranscriptsEn.MathScores", "数学成绩" },
                };

            //发送文件路径到客户端
            var urlPath = ExcelUtility.ExportExcel(cellheader, list, "学生成绩");
            System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
            HttpContext.Response.ContentType = "text/plain";
            HttpContext.Response.Write(js.Serialize(urlPath)); // 返回Json格式的内容
            //var column = new List<ExcelColum<UserViewModel>>
            //{
            //    new ExcelColum<UserViewModel>
            //    {
            //        Name = "姓名",
            //        Width = 7000,
            //        ColumType = ExcelColumType.String,
            //        ResultFunc = x => x.Name
            //    },
            //    new ExcelColum<UserViewModel>
            //    {
            //        Name = "年龄",
            //        Width = 7000,
            //        ColumType = ExcelColumType.String,
            //        ResultFunc = x => x.Age
            //    },
            //    new ExcelColum<UserViewModel>
            //    {
            //        Name = "性别",
            //        Width = 7000,
            //        ColumType = ExcelColumType.String,
            //        ResultFunc = x => x.GenderName
            //    },
            //    new ExcelColum<UserViewModel>
            //    {
            //        Name = "语文成绩",
            //        Width = 7000,
            //        ColumType = ExcelColumType.String,
            //        ResultFunc = x => x.TranscriptsEn.ChineseScores
            //    },
            //     new ExcelColum<UserViewModel>
            //    {
            //        Name = "数学成绩",
            //        Width = 7000,
            //        ColumType = ExcelColumType.String,
            //        ResultFunc = x => x.TranscriptsEn.MathScores
            //    },
            //};
            //var option = new ExcelExportOption<UserViewModel>
            //{
            //    Title = "学生成绩",
            //    Source = list,
            //    Colums = column
            //};
            //var filename = $"民办初中-{DateTime.Now:yyyyMMddHHmmssfff}.xls";
            //var file = ExcelUtility.ExportToStream<UserViewModel>(option);
            //return File(file, "application/vnd.ms-excel", filename);
        }