NPOI 操作Excel

今天药忘吃喽~ 2021-10-25 12:12 500阅读 0赞

**一、NPOI组件导入**

右键项目菜单，“管理NuGet程序包”

![1444520-20190814162029824-1469147933.png][]

直接搜索“NPOI”即会出现列表，下载第一个进行安装即可

![1444520-20190814162113665-1717189286.png][]

安装完成后项目引用会出现以下几项

![1444520-20190814162402671-268576495.png][]

**二、基础使用**

添加引用

using NPOI.HPSF;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;

Excel操作开始

HSSFWorkbook hssfworkbook = new HSSFWorkbook();//先创建Workbook
    ISheet sheet= hssfworkbook.CreateSheet("Sheet1");//创建sheet页
    IRow row1=sheet.CreateRow(0);//创建行
    row1.CreateCell(0).SetCellValue("test");//创建单元格并设置值

文件导出

FileStream file = new FileStream(sFilePath, FileMode.Create);
    hssfworkbook.Write(file);
    file.Close();

测试代码(以MVC实例)：

/// <summary>
            /// 读取xls内容
            /// </summary>
            /// <returns></returns>
            public ActionResult Index()
            {
                string sFilePath = @"C:\text.xls";
                //根据路径获取读取xls文件
                HSSFWorkbook wb = new HSSFWorkbook(new FileStream(sFilePath, FileMode.Open));
                //获取sheet对象
                ISheet sheet1 = wb.GetSheet("Sheet1");
                //获取行
                IRow row = sheet1.GetRow(1);
                //获取单元格
                ICell cell = row.GetCell(0);
                ICell cell1 = row.GetCell(1);
    
                var cellValue = cell.ToString();
                return View();
            }
    
            /// <summary>
            /// NPIO导出excel
            /// </summary>
            /// <returns></returns>
            public FileResult DownFile()
            {
                string sFilePath = @"C:\text.xls";
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();//先创建Workbook
                ISheet sheet = hssfworkbook.CreateSheet("Sheet1");//创建sheet页
                IRow row1 = sheet.CreateRow(0);//创建行
                row1.CreateCell(0).SetCellValue("test");//创建单元格并设置值
    
                //设置列宽（字符宽度）
                sheet.SetColumnWidth(0, 10 * 256);//第一列，10个字符宽度
                //设置行高
                row1.HeightInPoints = 40;//高度（像素）
                //设置网格的默认行高和列框
                sheet.DefaultColumnWidth = 100 * 256;
                sheet.DefaultRowHeightInPoints = 30;
    
                //设置单元格样式 
                //样式创建
                ICellStyle style = hssfworkbook.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文字横向居中
                style.VerticalAlignment = VerticalAlignment.Center;//文字纵向居中
                IFont font = hssfworkbook.CreateFont();//字体
                font.FontHeight = 20 * 20;
                style.SetFont(font);
    
                ICell cell = row1.Cells[0];//获取单元格对象
                cell.CellStyle = style;//绑定样式
    
                //单元格合并
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));//合拼了A1到I1
    
                //设置文件的详细信息
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "公司名称";//公司
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "主题";//主题
                hssfworkbook.DocumentSummaryInformation = dsi;
                hssfworkbook.SummaryInformation = si;
    
                FileStream file = new FileStream(sFilePath, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                return File(sFilePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "text.xls");
            }

转载于:https://www.cnblogs.com/chenze-Index/p/11381078.html

[1444520-20190814162029824-1469147933.png]: /images/20211025/4064ded2e45c47c78bc0d3ebf4a27b3b.png
[1444520-20190814162113665-1717189286.png]: /images/20211025/b1b2549ac7e84953b4a22ee9a8a90eaf.png
[1444520-20190814162402671-268576495.png]: /images/20211025/60381288365446a48c2dfba730961053.png