C# Exex导入导出

最新推荐文章于 2024-07-31 10:46:59 发布

a13585907079

最新推荐文章于 2024-07-31 10:46:59 发布

阅读量363

点赞数

本文链接：https://blog.csdn.net/a13585907079/article/details/89437598

版权

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using WebDaoRuDaoChu.Models;
using System.Data;
using ICSharpCode.SharpZipLib.Zip;

 	/// <summary>
    /// 导入到数据库
    /// </summary>
    /// <returns></returns>

   public ActionResult daoru()
    {
        model models = new model();
        var filename = Request.Files["FileName"];
        if (filename == null)
        {
            return Content("<script>alert('无文件请重新选择')；location.href='/Demo/Index'</script>");
        }
        else
        {
            var fileStream = filename.InputStream; //读取Excel的文件内容
            IWorkbook workbook = new XSSFWorkbook(fileStream); //创建工作谱
            var Sheet = workbook.GetSheet("Sheet1"); //创建工作页
            var Count = Sheet.LastRowNum;
            var Num = Sheet.FirstRowNum + 1;
            //逐条数据导入
            for (int i = Num; i <= Count; i++)
            {
                IRow cells = Sheet.GetRow(i);
                models.Name = cells.GetCell(0).ToString();
                models.Pwd = cells.GetCell(1).ToString();
                models.Sex = cells.GetCell(2).ToString();
                models.Dpt = cells.GetCell(3).ToString();
                models.States = cells.GetCell(4).ToString();
                string sql = $"insert into Department values('{models.Name}','{models.Pwd}','{models.Sex}','{models.Dpt}','{models.States}')";
                DBhelper.ExecuteNonQuery(sql);
            }
            return Content("<script>alert('导入成功！');location.href='/Demo/Index'</script>");
        }
    }
    
    /// <summary>
    /// 导出至ecex
    /// </summary>
    /// <returns></returns>
    public ActionResult DaoChu()
    {
        IWorkbook workbook = new XSSFWorkbook();
        var Sheet = workbook.CreateSheet("Sheet1");
        string sql = "select Name,Pwd,Sex,Dpt,States from Department";
        string xlsxs = DateTime.Now.ToString("yyMMddHHmmssfff")+".xlsx";
        DataTable dt = DBhelper.GetTable(sql);
        var Row = Sheet.CreateRow(0);
        Row.CreateCell(0).SetCellValue("姓名");
        Row.CreateCell(1).SetCellValue("密码");
        Row.CreateCell(2).SetCellValue("性别");
        Row.CreateCell(3).SetCellValue("部门");
        Row.CreateCell(4).SetCellValue("状态");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
           Row = Sheet.CreateRow(i + 1);
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                Row.CreateCell(k).SetCellValue(dt.Rows[i][k].ToString());
            }
        }
        string luj = @"D:\";
        using (var luji = System.IO.File.OpenWrite(luj + xlsxs))
        {
            workbook.Write(luji);
        }
        using (ZipFile zip = ZipFile.Create(luj+"sss.zip"))
        {
            zip.BeginUpdate();
            zip.Add(luj+xlsxs);
            zip.CommitUpdate(); 
        }
            return Content("导出完了");
    }