C# NPOI 读写 Excel

引用NPOI包

通过Nuget添加NPOI库文件

样例代码

前台代码

<Grid>
        <Button Name="Button_Write" Click="Button_Write_Click" Content="写入" HorizontalAlignment="Left" Margin="38,37,0,0" VerticalAlignment="Top" Width="75"/>
        <Button Name="Button_Read" Click="Button_Read_Click" Content="读取" HorizontalAlignment="Left" Margin="118,37,0,0" VerticalAlignment="Top" Width="75"/>
        <TextBlock Name="TextBlock1" Margin="10,76,10,10" Text=""/>
</Grid>

后台代码

public void WriteToExcel()
        {
            HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建工作簿  
            ISheet SheetOne = workbook2003.CreateSheet("Sheet1");  //新建1个Sheet工作表              

            //对工作表先添加行，下标从0开始  
            SheetOne.CreateRow(0);   //创建10行  

            ICellStyle csNumeric = workbook2003.CreateCellStyle();
            csNumeric.DataFormat = 194;

            IRow SheetRow = SheetOne.GetRow(0);  //获取Sheet1工作表的首行  

            SheetRow.CreateCell(0).SetCellValue(true);  //创建Cell并赋bool值  

            SheetRow.CreateCell(1).SetCellValue("赋字符串值");  //创建Cell并赋字符串值 

            SheetRow.CreateCell(2,CellType.Numeric).SetCellValue(0.22222);  //创建Cell并赋字符串值 
            SheetRow.GetCell(2).CellStyle = csNumeric;

            SheetRow.CreateCell(3,CellType.Numeric).SetCellValue(8888);  //创建Cell并赋整型值 
            SheetRow.GetCell(3).CellStyle = csNumeric;

            IDataFormat dataFormatCustom = workbook2003.CreateDataFormat();
            SheetRow.CreateCell(4).CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyy/MM/dd");
            SheetRow.GetCell(4).SetCellValue(DateTime.Now); //设置日期

            //设置列宽
            SheetOne.SetColumnWidth(0, 6 * 256);
            SheetOne.SetColumnWidth(1, 20 * 256);
            SheetOne.SetColumnWidth(2, 25 * 256);

            SheetOne.CreateRow(1);

            //合并单元格，首行、末行、首列、末列
            SheetOne.AddMergedRegion(new CellRangeAddress(1, 1, 0, 5));
            //填充内容，注意：上一步添加了合并的单元格后仍然需要create cell
            SheetOne.GetRow(1).CreateCell(0).SetCellValue("合并单元格内容");

            //设置单元格的样式
            ICellStyle headerStyle = workbook2003.CreateCellStyle();
            headerStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
            headerStyle.FillPattern = FillPattern.SolidForeground;
            //设置字体
            IFont font = workbook2003.CreateFont();
            font.FontHeightInPoints = 25;
            font.FontName = "微软雅黑";
            headerStyle.SetFont(font);
            //内容右对齐
            SheetOne.GetRow(1).GetCell(0).CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            SheetOne.GetRow(1).GetCell(0).CellStyle = headerStyle;

            FileStream file2003 = new FileStream(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls", FileMode.Create);
            workbook2003.Write(file2003);
            file2003.Close();
            workbook2003.Close();
        }

        public void ReadFromExcel()
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls");
            try
            {
                FileStream fs = File.OpenRead(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls");
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                IRow row = sheet.GetRow(0);  //读取当前行数据

                //LastRowNum 是当前表的总行数-1（注意）
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row != null)
                    {
                        //LastCellNum 是当前行的总列数
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //读取该行的第j列数据
                            if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                TextBlock1.Text += row.GetCell(j).DateCellValue.ToString()+"\t";
                            else
                            {
                                string value = row.GetCell(j).ToString();
                                TextBlock1.Text += (value.ToString() + "\t");
                            }
                        }
                        TextBlock1.Text += ("\n");
                    }
                }
            }
            catch (Exception e)
            {
                TextBlock1.Text += (e.Message);
            }
        }

        private void Button_Read_Click(object sender, RoutedEventArgs e)
        {
            ReadFromExcel();
        }

        private void Button_Write_Click(object sender, RoutedEventArgs e)
        {
            WriteToExcel();
        }

运行效果

写入效果

读取效果