mvc导出excel 之 新

前段时间做的mvc导出excel

老大说要进行优化，我原来导出是用npoi插件进行导出，格式是将数据放入到datatable中，然后进行导出。

说要优化的时候就想着将datatable数据导出格式改为list集合进行导出效率会好一点

所以就将所有datatable 改为list集合

用datatable 有一个缺点是我在excel显示中的顺序是跟我返回的实体定义的属性的顺序一样的，这样就有点局限性了，想要excel中改变显示顺序就要修改实体中属性的顺序

 

首先创建excel文件  new hssfworkbook();

创建sheet1  book.createsheet("")

创建row   sheet1.createrow(0)

最后给row赋值  row.createcell(0).setcellvalue("")

  //创建Excel文件的对象  
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            string strdate = DateTime.Now.ToString("yyyyMMddHHmmss");//获取当前时间  

            if ((orders != null && orders.Count > 0) || (bookingorders != null && bookingorders.Count > 0))
            {
                #region sheet1本地数据
                //添加一个sheet1 本地的数据导出为excel  
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("本地数据");
                //给sheet1添加第一行的头部标题  
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                if (request.OrderType == "1")  //挂号
                {
                    row1.CreateCell(0).SetCellValue("患者ID");
                    row1.CreateCell(1).SetCellValue("医院流水号"); //ReturnHisOrderId
                    row1.CreateCell(2).SetCellValue("公众服务平台订单号"); //BookOrderId
                    row1.CreateCell(3).SetCellValue("支付平台流水号"); // PayOrderId
                    row1.CreateCell(4).SetCellValue("退款流水号");    //BookOrderId
                    //row1.CreateCell(6).SetCellValue("支付平台代码");   //AgtCode
                    row1.CreateCell(5).SetCellValue("支付方式");   //paysource
                    row1.CreateCell(6).SetCellValue("支付时间");    //PayTime
                    row1.CreateCell(7).SetCellValue("支付金额");
                    row1.CreateCell(8).SetCellValue("就诊卡号");
                    row1.CreateCell(9).SetCellValue("科室");
                    row1.CreateCell(10).SetCellValue("医生");
                    row1.CreateCell(11).SetCellValue("就诊费用");
                    row1.CreateCell(12).SetCellValue("建议就诊时间");
                    row1.CreateCell(13).SetCellValue("出诊身份");
                    row1.CreateCell(14).SetCellValue("退费");
                    row1.CreateCell(15).SetCellValue("退款时间");
                    for (int i = 0; i < bookingorders.Count; i++)
                    {
                        NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(i + 1);
                        row2.CreateCell(0).SetCellValue(bookingorders[i].PatId);
                        row2.CreateCell(1).SetCellValue(bookingorders[i].RtnHisOrderId);
                        row2.CreateCell(2).SetCellValue(bookingorders[i].BKOrderId);
                        row2.CreateCell(3).SetCellValue(bookingorders[i].PayOrderId);
                        row2.CreateCell(4).SetCellValue(bookingorders[i].RtnOrderId);
                        row2.CreateCell(5).SetCellValue(bookingorders[i].PaySource);
                        row2.CreateCell(6).SetCellValue(bookingorders[i].PayTime.ToString());
                        row2.CreateCell(7).SetCellValue(bookingorders[i].PayFee.ToString());
                        row2.CreateCell(8).SetCellValue(bookingorders[i].VisitingCard);
                        row2.CreateCell(9).SetCellValue(bookingorders[i].DeptName);
                        row2.CreateCell(10).SetCellValue(bookingorders[i].DoctorName);
                        row2.CreateCell(11).SetCellValue(bookingorders[i].ClinicFee.ToString());
                        row2.CreateCell(12).SetCellValue(bookingorders[i].AdviceTime);
                        row2.CreateCell(13).SetCellValue(bookingorders[i].ClincName);
                        row2.CreateCell(14).SetCellValue(bookingorders[i].RtnFee.ToString());
                        row2.CreateCell(15).SetCellValue(bookingorders[i].ReturnTime.ToString());
                    }

                    //将数据逐步写入sheet1各个行  
                }
                else if (request.OrderType == "2") //门诊
                {
                    row1.CreateCell(0).SetCellValue("患者ID");
                    row1.CreateCell(1).SetCellValue("医院流水号"); //ReturnHisOrderId
                    row1.CreateCell(2).SetCellValue("公众服务平台订单号"); //BookOrderId
                    row1.CreateCell(3).SetCellValue("支付平台流水号"); // PayOrderId
                    row1.CreateCell(4).SetCellValue("退款账号");    //BookOrderId
                    row1.CreateCell(5).SetCellValue("时间");   //paysource
                    row1.CreateCell(6).SetCellValue("金额");    //BookOrderId
                    for (int i = 0; i < orders.Count; i++)
                    {
                        NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(i + 1);
                        row2.CreateCell(0).SetCellValue(orders[i].PatientID);
                        row2.CreateCell(1).SetCellValue(orders[i].HISReceiptSn);
                        row2.CreateCell(2).SetCellValue(orders[i].HisOrderId);
                        row2.CreateCell(3).SetCellValue(orders[i].TransactionId);
                        row2.CreateCell(4).SetCellValue(orders[i].OutTradeNo);
                        row2.CreateCell(5).SetCellValue(orders[i].PayTime.ToString());
                        row2.CreateCell(6).SetCellValue(orders[i].HisPayFee);
                    }
                }