Excel报表

// GET: Excel

public ActionResult Index()
{
    //保存文件
    string url = Server.MapPath(@"~/Excel/students.xls");
    //创建文件流
    FileStream filename = new FileStream(url,FileMode.Open);
        //通过文件夹打开数据表格
        HSSFWorkbook workbook = new HSSFWorkbook(filename);
        //读取某张表
        HSSFSheet sheet = workbook.GetSheet("Sheet1") as HSSFSheet;
        int  lastindex=sheet.LastRowNum;


        string info = "";

        for (int i = 0; i <= lastindex; i++)
        {

            //读取行
            HSSFRow row = sheet.GetRow(i) as HSSFRow;
            int lastindex1 = row.LastCellNum;
            for (int j = 0; j <lastindex1; j++)
             {
                //读取所对应的值 //读取列
               
                if ( row.GetCell(j).CellType==NPOI.SS.UserModel.CellType.STRING)
                {
                    string values = row.GetCell(j).StringCellValue;
                    info += values + "    ";
                }

                if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                {
                    string values = row.GetCell(j).NumericCellValue.ToString();
                    info += values + "    ";
                }

            }
            info += "<br/>";


        } 
        Response.Write(info);
         
        Response.End();

        return View();
    }

    public ActionResult  daoru()
    { 
        return View();
    }
    

    public   void read()
    {
        if (Request.Files.Count > 0)
        {

            string fileName = Request.Files[0].FileName; 
            string prex = fileName.Substring(fileName.LastIndexOf(".") + 1);
            if (prex.ToLower() == "xls" || prex.ToLower() == "xlsx")
            {
                
                //保存文件
                string url = Server.MapPath(@"~/Excel/" + Guid.NewGuid().ToString().Replace("-", "")  + ".xls");

                Request.Files[0].SaveAs(url);
         
            
                //创建文件流
                FileStream filename = new FileStream(url, FileMode.Open);

                //通过文件夹打开数据表格
                HSSFWorkbook workbook = new HSSFWorkbook(filename);
                //读取某张表
                HSSFSheet sheet = workbook.GetSheet("2017年度财务报表") as HSSFSheet;
                //创建文件流
             
               
                //创建行
                HSSFRow row = sheet.CreateRow(10) as HSSFRow;

                //创建列

                row.CreateCell(0).SetCellValue("姓名");
                row.CreateCell(1).SetCellValue("年龄");
                row.CreateCell(2).SetCellValue("QQ");
                row.CreateCell(3).SetCellValue("微信");
                row.CreateCell(4).SetCellValue("电话");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("资产");

                Random rand = new Random();
                string[] city = { "贵州", "天津", "北京", "上海", "深圳", "重庆", "广东" };
                string[] username = { "花花", "翠翠", "兰兰", "云云", "香香", "梦梦", "玲玲" };
                string[] usernameEnglish = { "huahua", "cuicui", "lanlan", "yunyun", "xiangxiang", "mengmeng", "linlin" };
                int j = 0;
                for (int i = 10; i < 17; i++)
                {

                    HSSFRow row2 = sheet.CreateRow(i) as HSSFRow;
                    row2.CreateCell(0).SetCellValue(username[j]);
                    row2.CreateCell(1).SetCellValue(rand.Next(20, 50));
                    row2.CreateCell(2).SetCellValue(rand.Next(100000, 99999999));
                    row2.CreateCell(3).SetCellValue(usernameEnglish[j] + rand.Next(100000, 99999999));
                    row2.CreateCell(4).SetCellValue(13 + rand.Next(100000, 99999999));
                    row2.CreateCell(5).SetCellValue(city[j]);
                    row2.CreateCell(6).SetCellValue(rand.Next(100, 999) + "万");
                    j++;
                    if (j > 6)
                    {
                        j = 0;

                    }
                }
                MemoryStream memoryStream = new MemoryStream();
                workbook.Write(memoryStream);
                Response.AddHeader("Content-Disposition", "attachment;filename=2017年度财务报表.xls");

                Response.BinaryWrite(memoryStream.ToArray());

            }
             

        }
         

    }

    public ActionResult daochu()
    {

        return View();
    }

    public   void daochu1()
    {
        //动态创建一个表 
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个表
        HSSFSheet sheet = workbook.CreateSheet("富婆通讯有限公司") as HSSFSheet;

        #region   公司说明
        HSSFRow head = sheet.CreateRow(0) as HSSFRow;
        HSSFCell headceel = head.CreateCell(0) as HSSFCell;
        //设置行高
        head.Height = 255 * 3;
        //设置列宽
        for (int i = 0; i < 7; i++)
        {
            sheet.SetColumnWidth(i, 255 * 3 * 4);
        }
        //合并单元格

        sheet.AddMergedRegion(new NPOI.SS.Util.Region(0,0,0,6) );
        //设置样式
        HSSFCellStyle cellstyle = workbook.CreateCellStyle()  as  HSSFCellStyle;
        cellstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
        cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
        //得到字体样式
        HSSFFont font = workbook.CreateFont()  as  HSSFFont;
        font.Boldweight = 600;
        font.FontHeight = 30*14;
        headceel.CellStyle = cellstyle;
        cellstyle.SetFont(font);


        headceel.SetCellValue("富婆通讯有限公司");
        #endregion
        //创建行
        HSSFRow row = sheet.CreateRow(1) as HSSFRow;

        //创建列

        row.CreateCell(0).SetCellValue ("姓名");
        row.CreateCell(1).SetCellValue("年龄");
        row.CreateCell(2).SetCellValue("QQ");
        row.CreateCell(3).SetCellValue("微信");
        row.CreateCell(4).SetCellValue("电话");
        row.CreateCell(5).SetCellValue("地址");
        row.CreateCell(6).SetCellValue("资产");

        Random rand = new Random();
        string[] city = { "贵州", "天津", "北京", "上海", "深圳", "重庆", "广东" };
        string[]  username={"花花","翠翠","兰兰","云云","香香","梦梦","玲玲"};
        string[] usernameEnglish = { "huahua", "cuicui", "lanlan", "yunyun", "xiangxiang", "mengmeng", "linlin" };
        int j = 0;
        for (int i = 2; i < 9; i++)
        {
             
            HSSFRow row2 = sheet.CreateRow(i) as HSSFRow;
            row2.CreateCell(0).SetCellValue(username[j]);
            row2.CreateCell(1).SetCellValue(rand.Next(20,50));
            row2.CreateCell(2).SetCellValue(rand.Next(100000, 99999999));
            row2.CreateCell(3).SetCellValue(usernameEnglish[j]+ rand.Next(100000, 99999999));
            row2.CreateCell(4).SetCellValue(13+rand.Next(100000, 99999999));
            row2.CreateCell(5).SetCellValue(city[j]);
            row2.CreateCell(6).SetCellValue(rand.Next(100, 999)+"万");
            j++;
            if (j>6)
            {
                j = 0;

            }
        }
        HSSFRow row1 = sheet.CreateRow(15) as HSSFRow;
        for (int i = 15; i < 17; i++)
        {
            HSSFRow row2 = sheet.CreateRow(i) as HSSFRow;
            row2.CreateCell(0).SetCellValue("编码");
            row2.CreateCell(1).SetCellValue("月度");
            row2.CreateCell(2).SetCellValue("编码");
            row2.CreateCell(3).SetCellValue("月度");
            row2.CreateCell(4).SetCellValue("编码");
            row2.CreateCell(5).SetCellValue("月度");
            row2.CreateCell(6).SetCellValue("编码");
            row2.CreateCell(7).SetCellValue("月度");
        }
        MemoryStream memoryStream = new MemoryStream(); 
        workbook.Write(memoryStream);
        Response.AddHeader("Content-Disposition", "attachment;filename=富婆通讯有限公司.xls");

        Response.BinaryWrite(memoryStream.ToArray());

    }

效果：