导出Excel

　　把excel另存为xml电子表格(*.xml)，格式复制生成的xml代码，放到view中，然后根据model去生成excel的单元格和列。

　　下面是生成excel和下载的代码。

        Response.Clear();
        Response.ClearContent();
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.ContentType = "application/ms-excel";
        string downLoadFileName = title;
        if (Request.UserAgent.ToLower().IndexOf("msie") > -1)
        {
            downLoadFileName = HttpUtility.UrlPathEncode(downLoadFileName);
        }
        else if (Request.UserAgent.ToLower().IndexOf("firefox") > -1)
        {
            Response.AddHeader("Content-Disposition", "attachment:filename=\"" + downLoadFileName + "\"");
        }
        else
            Response.AddHeader("Content-Disposition", "attachment:filename=" + downLoadFileName + "");

 这是另外一种

/// <summary>
        /// Excel导出
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string ExportGoodsSale(DataSet set)
        {
            try
            {
                IWorkbook workbook = new XSSFWorkbook();
                foreach (DataTable dt in set.Tables)
                {
                    ISheet sheet1 = workbook.CreateSheet(dt.TableName);
                    int CellCount = dt.Columns.Count;//列数

                    IRow RowHead = sheet1.CreateRow(0);  //创建表头
                    //绑定字体样式到表头
                    IFont Headfont = workbook.CreateFont();
                    Headfont.FontName = "微软雅黑";
                    Headfont.Color = HSSFColor.BLACK.index;
                    Headfont.FontHeightInPoints = 11;

                    //绑定字体到样式上
                    ICellStyle Headstyle = workbook.CreateCellStyle();
                    Headstyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直居中
                    Headstyle.Alignment = HorizontalAlignment.CENTER; //横向居中
                    //背景颜色
                    //Headstyle.FillPattern = FillPatternType.BIG_SPOTS;
                    //Headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                    //Headstyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                    Headstyle.SetFont(Headfont);
                    //边框颜色
                    Headstyle.BorderBottom = BorderStyle.DOTTED;
                    Headstyle.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    Headstyle.BorderLeft = BorderStyle.DOTTED;
                    Headstyle.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    Headstyle.BorderRight = BorderStyle.DOTTED;
                    Headstyle.RightBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    Headstyle.BorderTop = BorderStyle.DOTTED;
                    Headstyle.TopBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    //创建表头列
                    List<string> SellerList = new List<string>(); //门店
                    List<string> IntervalList = new List<string>(); //档期
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (col.ColumnName == "PName" || col.ColumnName == "SellerInterval")
                            continue;
                        string[] colname = col.ColumnName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
                        if (!SellerList.Contains(colname[0]))
                            SellerList.Add(colname[0]);
                        if (!IntervalList.Contains(colname[1]))
                            IntervalList.Add(colname[1]);
                    }
                    
                    ICell cell = RowHead.CreateCell(0);
                    cell.SetCellValue("商品名");
                    cell.CellStyle = Headstyle;
                    sheet1.SetColumnWidth(0, 30 * 256);
                    int j=1;
                    foreach(var seller in SellerList)
                    {
                        int s = j;
                        foreach(string Interval in IntervalList)
                        {
                            cell = RowHead.CreateCell(j);
                            cell.SetCellValue(seller);
                            cell.CellStyle = Headstyle;
                            sheet1.SetColumnWidth(j, 21 * 100);
                            j++;
                        }
                        int e = j;
                        sheet1.AddMergedRegion(new CellRangeAddress(0, 0, s, e-1));
                    }
                    cell = RowHead.CreateCell(dt.Columns.Count-1);
                    cell.SetCellValue("销售量");
                    cell.CellStyle = Headstyle;
                    sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156);

                    RowHead.Height = 25 * 20;

                    IRow RowHeadt = sheet1.CreateRow(1);  //创建表头
                    cell = RowHeadt.CreateCell(0);
                    cell.SetCellValue("");
                    cell.CellStyle = Headstyle;
                    sheet1.SetColumnWidth(0, 30 * 256);
                    j = 1;
                    foreach (var seller in SellerList)
                    {
                        foreach (string Interval in IntervalList)
                        {
                            cell = RowHeadt.CreateCell(j);
                            cell.SetCellValue(Interval);
                            cell.CellStyle = Headstyle;
                            sheet1.SetColumnWidth(j, 21 * 100);
                            j++;
                        }
                    }
                    cell = RowHeadt.CreateCell(dt.Columns.Count - 1);
                    cell.SetCellValue("销售量");
                    cell.CellStyle = Headstyle;
                    sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156);
                    RowHeadt.Height = 25 * 20;

                    //合并头部单元格
                    sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
                    sheet1.AddMergedRegion(new CellRangeAddress(0, 1, dt.Columns.Count - 1, dt.Columns.Count - 1));


                    //填充内容
                    //绑定字体样式到表格内容
                    IFont font = workbook.CreateFont();


                    //字体样式
                    font.FontName = "微软雅黑";
                    font.Color = HSSFColor.BLACK.index;
                    font.FontHeightInPoints = 11;
                    ICellStyle style = workbook.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.CENTER;
                    style.Alignment = HorizontalAlignment.RIGHT;
                    style.SetFont(font);
                    //边框样式
                    style.BorderBottom = BorderStyle.DOTTED;
                    style.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    style.BorderLeft = BorderStyle.DOTTED;
                    style.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    style.BorderRight = BorderStyle.DOTTED;
                    style.RightBorderColor = HSSFColor.GREY_40_PERCENT.index;
                    style.BorderTop = BorderStyle.DOTTED;
                    style.TopBorderColor = HSSFColor.GREY_40_PERCENT.index;

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row = sheet1.CreateRow((i + 2));
                        for (int m = 0; m < CellCount; m++)
                        {
                            ICell Tcell = row.CreateCell(m);
                            Tcell.SetCellValue(dt.Rows[i][m].ToString());
                            Tcell.CellStyle = style;
                        }
                        row.Height = 20 * 20;
                    }
                }
                string path = Path.Combine("~/Uploads/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/");
                if (!Directory.Exists(HttpContext.Current.Server.MapPath(path)))
                {
                    Directory.CreateDirectory(HttpContext.Current.Server.MapPath(path));
                }
                string FileName = Guid.NewGuid()+".xlsx";
                var fullPath = path + FileName;
                FileStream sw = File.Create(HttpContext.Current.Server.MapPath(fullPath));
                workbook.Write(sw);
                sw.Close();
                return fullPath;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 文件下载
        /// </summary>
        /// <param name="filename">文件路径</param>
        public static void DownLoad(string FilePath,string FileName)
        {
            FileInfo fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(FilePath));
            //以字符流的形式下载文件
            FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;  filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8));
            HttpContext.Current.Response.BinaryWrite(bytes);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }