关于asp.net导出excel的问题

wggmqj 2011-05-11 10:26:44

把数据库的数据导成excel表，要求一个excel表中包含多个工作薄，效果就是像51job中导出简历excel的格式。谢谢了

...全文

219 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

子夜__ 2011-05-12

打赏
举报

http://blog.csdn.net/ruiruiy/archive/2011/01/06/6120944.aspx
http://dotnet.aspx.cc/file/Export-Gridview-To-Excel-With-Multi-Sheet.aspx

cjh200102 2011-05-11

打赏
举报

先预设Excel模板，再进行数据填充

Lisliefor 2011-05-11

打赏
举报

按照考场分组，不是考场排序，打错了。

Lisliefor 2011-05-11

打赏
举报

导出多个工作簿是可以的，下面这个例子是按考场排序，导出多个考生列表excel的例子：



public MemoryStream ExportQualifiedExaminees(string modelPath, string serviceName, IList<QualifiedExamineeEn> lst, string imgPath)

        {

            MemoryStream imageStream = new MemoryStream();

            HSSFWorkbook workbook = new HSSFWorkbook(File.OpenRead(modelPath));

            Image.FromFile(imgPath).Save(imageStream, System.Drawing.Imaging.ImageFormat.Png);

            byte[] bytes = new byte[imageStream.Length];

            bytes = imageStream.GetBuffer();



            imageStream = null;

            //int picInt = ;

            HSSFClientAnchor ac = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);

            Row r = workbook.GetSheet("合格考生名单").GetRow(4);

            var style = r.GetCell(0).CellStyle;

            var type = r.GetCell(0).CellType;



            IEnumerable<IGrouping<string, QualifiedExamineeEn>> list = lst.GroupBy(m => m.ExamLocationName);



            for (int i = 0; i < list.Count(); i++)

            {

                List<QualifiedExamineeEn> ls = list.ElementAt(i).ToList<QualifiedExamineeEn>();

                // 就是这里new了一些新的sheet，虽然是以克隆模板的方式。

                Sheet sheet = workbook.CloneSheet(0);

                workbook.SetSheetName(i + 1, ls[0].ExamLocationName);

                var patriarch = sheet.CreateDrawingPatriarch();



                patriarch.CreatePicture(ac, workbook.AddPicture(bytes, PictureType.PNG)).Resize();

                sheet.GetRow(2).GetCell(0).SetCellValue("考试服务名称：" + serviceName);

                sheet.GetRow(3).GetCell(0).SetCellValue("考场地址：" + ls[0].LocationAddress);



                int countFlag = 1;

                foreach (var m in ls)

                {

                    var newRow = sheet.CreateRow(sheet.LastRowNum + 1);

                    newRow.HeightInPoints = 18;

                    if (m.IDNumber == null || m.IDNumber.Length == 0)

                    {

                        CreateCell(newRow, 0, style, type, countFlag.ToString());

                        CreateCell(newRow, 1, style, type, m.AdmissionFormId.ToString());

                        CreateCell(newRow, 2, style, type, "");

                        CreateCell(newRow, 3, style, type, "");

                        CreateCell(newRow, 4, style, type, "");

                        CreateCell(newRow, 5, style, type, "");

                        CreateCell(newRow, 6, style, type, "");

                        CreateCell(newRow, 7, style, type, "");

                        CreateCell(newRow, 8, style, type, "");

                    }

                    else

                    {

                        CreateCell(newRow, 0, style, type, countFlag.ToString());

                        CreateCell(newRow, 1, style, type, m.AdmissionFormId.ToString());

                        CreateCell(newRow, 2, style, type, m.FirstName + " " + m.LastName);

                        CreateCell(newRow, 3, style, type, m.Gender ? "男" : "女");

                        CreateCell(newRow, 4, style, type, m.Birthday);

                        CreateCell(newRow, 5, style, type, m.Department);

                        CreateCell(newRow, 6, style, type, m.IDNumber);

                        CreateCell(newRow, 7, style, type, "");

                        CreateCell(newRow, 8, style, type, "");

                    }

                    countFlag++;

                }

            }



            workbook.RemoveSheetAt(0);

            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);

            workbook = null;

            r = null;



            return ms;

        }