winformNPOI导入导出

最新推荐文章于 2025-05-20 11:26:11 发布

tianpeng_tp

最新推荐文章于 2025-05-20 11:26:11 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： C#

本文链接：https://blog.youkuaiyun.com/ASDTP/article/details/80932669

C# 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了如何在Windows Forms应用程序中利用NPOI库进行Excel文件的数据导入和导出操作。通过示例代码，展示了创建工作簿、添加工作表、填充数据到单元格以及读取单元格数据的详细步骤，帮助开发者实现高效的数据交换功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

        public Form1()
        {
            InitializeComponent();
        }
        private ManagerInfoBll miBll = new ManagerInfoBll();
        private void Form1_Load(object sender, EventArgs e)
        {
            LoadList();
        }
        private void LoadList()
        {
            //列自适应
            dgvList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            //禁用列表的自动生成
            dgvList.AutoGenerateColumns = false;
            dgvList.DataSource = miBll.GetList();
        }

        /// <summary>
        /// NPOI导出，个性化设置
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            //进行execl生成创建操作
            //1、创建workBook，不指定参数，表示创建一个新的工作本
            XSSFWorkbook wb = new XSSFWorkbook();

            //2.创建一个sheet
            ISheet sheet = wb.CreateSheet("管理员信息");

            #region 创建第一行，标题居中
            //3.创建row
            IRow row = sheet.CreateRow(0);

            //4.创建单元格cell
            ICell cell0 = row.CreateCell(0);
            cell0.SetCellValue("店员信息表");

            //5.合并单元格,前2位表示从哪个单元格开始，后2位表示哪个单元格结束
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));

            //6.设置单元格居中
            XSSFCellStyle styleTitle = (XSSFCellStyle)wb.CreateCellStyle();
            styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cell0.CellStyle = styleTitle;
            #endregion

            //创建第二行，表格标题
            IRow rowTitle = sheet.CreateRow(1);
            ICell cellT0 = rowTitle.CreateCell(0);
            cellT0.SetCellValue("编号");
            cellT0.CellStyle = styleTitle;
            ICell cellT1 = rowTitle.CreateCell(1);
            cellT1.SetCellValue("姓名");
            cellT1.CellStyle = styleTitle;
            ICell cellT2 = rowTitle.CreateCell(2);
            cellT2.SetCellValue("密码");
            cellT2.CellStyle = styleTitle;
            ICell cellT3 = rowTitle.CreateCell(3);
            cellT3.SetCellValue("级别");
            cellT3.CellStyle = styleTitle;



            //循环创建正文数据
            List<ManagerInfo> list = miBll.GetList();
            int rowIndex = 2;
            foreach (var mi in list)
            {
                //创建行
                IRow rowData = sheet.CreateRow(rowIndex++);

                //创建单元格
                ICell cellData0 = rowData.CreateCell(0);
                cellData0.SetCellValue(mi.MId);

                ICell cellData1 = rowData.CreateCell(1);
                cellData1.SetCellValue(mi.MName);

                ICell cellData2 = rowData.CreateCell(2);
                cellData2.SetCellValue(mi.MPwd);
                sheet.SetColumnWidth(2, 34 * 256);

                ICell cellData3 = rowData.CreateCell(3);
                cellData3.SetCellValue(mi.MType == 0 ? "店员" : "经理");

            }

            XSSFFont fontTitle = (XSSFFont)wb.CreateFont();
            fontTitle.FontHeightInPoints = 14;
            fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            styleTitle.SetFont(fontTitle);

            //保存工作本
            using (FileStream fs = new FileStream(@"C:\Users\Administrator\Desktop\ex1.xlsx", FileMode.Create))
            {
                wb.Write(fs);
            }

            MessageBox.Show("导出成功");
        }

        /// <summary>
        /// NPOI导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            //这个地方并没有设置选择文件。节省时间

            //1.读取Execl数据，存入list中
            List<ManagerInfo> list = new List<ManagerInfo>();
            using (FileStream fs = new FileStream(@"C:\Users\Administrator\Desktop\ex1.xlsx", FileMode.Open))
            {
                //创建workbook,读取文件流
                XSSFWorkbook wb = new XSSFWorkbook(fs);
                //读取sheet，从0页开始
                XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);

                //读取正文数据，0.1行直接路过
                for (int i = 2; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    ICell cell0 = row.GetCell(0);
                    ICell cell1 = row.GetCell(1);
                    ICell cell2 = row.GetCell(2);
                    ICell cell3 = row.GetCell(3);
                    list.Add(new ManagerInfo()
                    {
                        MId = (int)cell0.NumericCellValue,
                        MName = cell1.StringCellValue,
                        MPwd = cell2.StringCellValue,
                        MType = cell2.StringCellValue == "店员" ? 0 : 1
                    });
                }
            }
            //2.将list赋值给dgvList
            dgvList.DataSource = list;
            MessageBox.Show("导入成功");
        }

效果图