用NPOI操作表例码

最新推荐文章于 2025-08-11 21:13:32 发布

原创最新推荐文章于 2025-08-11 21:13:32 发布 · 589 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#object #null #string #button #insert #sql

这是一个关于如何使用NPOI库在C#中进行数据导入和导出的示例。博客详细介绍了如何从数据库读取数据并创建Excel工作表，以及如何从Excel文件读取数据并插入到数据库中。

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;

namespace 通过NPOI进行数据的导入与导出
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sid = txtsid.Text.Trim();
            string sname = txtsname.Text.Trim();
            string tage = txtage.Text.Trim();
            string tenglish = txtenglish.Text.Trim();
            string tmath = txtmath.Text;
            string sql = "insert into student values(@sid,@sname,@tage,@tenglish,@tmath)";
            SqlParameter[] pms = new SqlParameter[]{
                new SqlParameter("@sid",sid==string.Empty?DBNull.Value:(object)sid),
                new SqlParameter("@sname",sname),
                new SqlParameter("@tage",tage==string.Empty?DBNull.Value:(object)tage),//如果为输入空字符串则以NULL插入到数据库。
                new SqlParameter("@tenglish",tenglish==string.Empty?DBNull.Value:(object)tenglish),
                new SqlParameter("@tmath",tmath==string.Empty?DBNull.Value:(object)tmath)
            };
           int r=SQLHelper.ExeucteNonQuery(sql, CommandType.Text, pms);
           MessageBox.Show("插入" + r + "行");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (SqlDataReader reader = SQLHelper.ExecuteReader("select * from student", CommandType.Text))
            {
                if (reader.HasRows)
                {

                    IWorkbook wk = new HSSFWorkbook();
                    ISheet sheet =wk.CreateSheet();
                    int rownum = 0;
                    SqlParameter[] pms = new SqlParameter[]{
                        new SqlParameter("@sid",null)
                    };
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        //int sid = reader.GetInt32(1);//如果为空则异常
                        int? sid = reader.IsDBNull(1) ? null :(int?) reader.GetInt32(1);
                        string sname = reader.GetString(2);
                        int? tage = reader.IsDBNull(3)?null:(int?)reader.GetInt32(3);
                        //double tenglish = reader.GetDouble(4);
                        double tenglish = reader.IsDBNull(4) ? 44444444444 : reader.GetDouble(4);
                        double? tmath =reader.IsDBNull(5)?null:(double?) reader.GetDouble(5);
                        IRow row =sheet.CreateRow(rownum);
                        row.CreateCell(1).SetCellValue(id);
                        if (sid == null)
                        {
                            row.CreateCell(2, CellType.BLANK);
                        }
                        else
                        {
                            row.CreateCell(2).SetCellValue((int)sid);
                        }
                        row.CreateCell(3).SetCellValue(sname);
                        //row.CreateCell(4).SetCellValue(tage);
                        if (tage == null)
                        {
                            row.CreateCell(4, CellType.BLANK);
                        }
                        else
                        {
                            row.CreateCell(4).SetCellValue((int)tage);
                        }
                        row.CreateCell(5).SetCellValue(tenglish);
                        //row.CreateCell(6).SetCellValue(tmath);//在向数据库取值时设为了可null类型并值为null所在这这里要对tmath进行判断。
                        if (tmath == null)
                        {
                            row.CreateCell(6, CellType.BLANK);//不能设置为unkown!!为什么呢？
                        }
                        else
                        {
                            row.CreateCell(6).SetCellValue((double)tmath);
                        }
                        rownum++;
                    }
                    using (FileStream fs = File.OpenWrite("student.xls"))
                    {
                        wk.Write(fs);
                    }
                    MessageBox.Show("0K");
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            using (FileStream fs = File.OpenRead("student.xls"))
            {
                IWorkbook wk = new HSSFWorkbook(fs);
                if (wk.NumberOfSheets > 0)
                {
                    ISheet sheet =wk.GetSheetAt(0);
                    for (int r = 0; r < sheet.LastRowNum; r++)
                    {
                        IRow row =sheet.GetRow(r);
                        /*for (int c = 0; c < row.LastCellNum; c++)
                        {
                            ICell cell = row.GetCell(c);
                        }*///知道怎么取出每一行每个单元格的值吗？
                        //row.GetCell(1).SetCellValue();没有搞错吧这是这行的第一列设值。现在是叫你取值！！
                        int id =(int)row.GetCell(1).NumericCellValue;//这一列不需要取,自动增长不能插
                        //int? sid = (int?)row.GetCell(2).NumericCellValue;
                        int? sid = row.GetCell(2) == null ? null : (int?)row.GetCell(2).NumericCellValue;
                        string sname = row.GetCell(3).StringCellValue;
                        int? tage = (int?)row.GetCell(4).NumericCellValue;
                        //double? tenglish = row.GetCell(5).NumericCellValue; //为null则异常
                        double? tenglish = row.GetCell(5) == null ? null :(double?) row.GetCell(5).NumericCellValue;
                        //double? tmath =(double?) row.GetCell(6).NumericCellValue;//如果为空则异常，未将对象引用到类的实例。
                        double? tmath = row.GetCell(6) == null ? null : (double?)row.GetCell(6).NumericCellValue;
                        string sql = "insert into student values(@sid,@sname,@tage,@tenglish,@tmath)";
                        SqlParameter[] pms = new SqlParameter[]{
                            //new SqlParameter("@id",id),
                            new SqlParameter("@sid",sid==null?DBNull.Value:(object)sid),
                            new SqlParameter("@sname",sname),
                            new SqlParameter("@tage",tage==null?DBNull.Value:(object)tage),
                            new SqlParameter("@tenglish",tenglish==null?DBNull.Value:(object)tenglish),
                            new SqlParameter("@tmath",tmath==null?DBNull.Value:(object)tmath)
                        };
                       SQLHelper.ExeucteNonQuery(sql, CommandType.Text, pms);
                    }
                }
            } MessageBox.Show("ok");
        }
    }
}