C#使用NPOI将Excel数据导入数据库和导出

C#使用NPOI将Excel数据导入数据库和导出

1.导出功能实现（话不多说直接上代码）

 //导出
        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                IWorkbook excel = new HSSFWorkbook();//创建.xls文件
                ISheet sheet = excel.CreateSheet("sheet1"); //创建sheet

                DataTable datatable = Main.data;   // (DataTable)dataGridView.DataSource;//获取数据源datatable

                IRow row = sheet.CreateRow(0);//创建行对象，填充表头
                foreach (DataColumn column in datatable.Columns)
                {
                    row.CreateCell(0).SetCellValue("用户名");
                    row.CreateCell(1).SetCellValue("身份证号码");
                    row.CreateCell(2).SetCellValue("年龄");
                    row.CreateCell(3).SetCellValue("性别");
                    row.CreateCell(4).SetCellValue("出生年月");
                    row.CreateCell(5).SetCellValue("政治面貌");
                    row.CreateCell(6).SetCellValue("联系方式");
                    row.CreateCell(7).SetCellValue("户主");
                    row.CreateCell(8).SetCellValue("与户主关系");
                    row.CreateCell(9).SetCellValue("工作单位");
                    row.CreateCell(10).SetCellValue("行政村");
                    row.CreateCell(11).SetCellValue("自然庄(小区)");
                    row.CreateCell(12).SetCellValue("家庭住址");
                    row.CreateCell(13).SetCellValue("备注");

                }

                //填充内容，j从1开始，屏蔽掉第一列，循环读取
                for (int i = 0; i < datatable.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);
                    for (int j = 1; j < datatable.Columns.Count; j++)
                    {
                        row.CreateCell(j - 1).SetCellValue(datatable.Rows[i][j].ToString());
                        sheet.AutoSizeColumn(j);
                    }
                }

                //写入文件
                //string DesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                FileStream xlsfile = new FileStream(txtPath.Text.ToString() + txtName.Text.ToString() + ".xls", FileMode.Create);
                excel.Write(xlsfile);
                xlsfile.Close();

                MessageBox.Show("Excel文件已导出到您的指定位置", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            catch (Exception)
            {
                MessageBox.Show("导出失败");
            }

        }

2.导入数据库

  //导入Excel 内容到 DataTable中(返回DataTable)
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public static void ReadExcelToDataTable(TextBox txt, string sheetName = null, bool isFirstRowColumn = true)
        {
            //定义要返回的datatable对象
            DataTable data = new DataTable();
            //excel工作表
            NPOI.SS.UserModel.ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;
            try
            {
                if (!File.Exists(txt.Text.ToString()))
                {
                    MessageBox.Show("没有选择路径");
                    return;
                }
                //根据指定路径读取文件
                FileStream fs = new FileStream(txt.Text.ToString(), FileMode.Open, FileAccess.Read);
                //根据文件流创建excel数据结构
                NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
                //IWorkbook workbook = new HSSFWorkbook(fs);
                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    //如果没有指定的sheetName，则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                DataTable dt = SQLDBhelp.Query("Select CardID from Main");
                for (int i =0;i<data.Rows.Count;i++)//excle单元格导入总共数据
                {
                    for(int j =0;j<dt.Rows.Count;j++)//数据库
                    {
                        if (data.Rows[i][1].ToString() ==dt.Rows[j][0].ToString())//对比
                        {
                            MessageBox.Show("Excel第 " + (i+2).ToString() + " 行身份证重复，请核对数据");
                            return;
                        }
                    }
                }




                string queryString = "insert into [Census].[dbo].[Main](UserName,CardID,Age,Sex,Birthday,PoliticalStatus,Phone,HeadOfHousehold,Relationship,Employer,AdministrativeVillage,Plot,HomeAddress,Note)Values(@UserName,@CardID,@Age,@Sex,@Birthday,@PoliticalStatus,@Phone,@HeadOfHousehold,@Relationship,@Employer,@AdministrativeVillage,@Plot,@HomeAddress,@Note)";

                for(int i=0;i<data.Rows.Count;i++)
                {
                    SqlParameter[] param = new SqlParameter[14];
                    param[0] = new SqlParameter("@UserName", SqlDbType.NVarChar);
                    param[1] = new SqlParameter("@CardID", SqlDbType.NVarChar);
                    param[2] = new SqlParameter("@Age", SqlDbType.NVarChar);
                    param[3] = new SqlParameter("@Sex", SqlDbType.NVarChar);
                    param[4] = new SqlParameter("@Birthday", SqlDbType.NVarChar);
                    param[5] = new SqlParameter("@PoliticalStatus", SqlDbType.Int);
                    param[6] = new SqlParameter("@Phone", SqlDbType.NVarChar);
                    param[7] = new SqlParameter("@HeadOfHousehold", SqlDbType.NVarChar);
                    param[8] = new SqlParameter("@Relationship", SqlDbType.Int);
                    param[9] = new SqlParameter("@Employer", SqlDbType.NVarChar);
                    param[10] = new SqlParameter("@AdministrativeVillage", SqlDbType.Int);
                    param[11] = new SqlParameter("@Plot", SqlDbType.Int);
                    param[12] = new SqlParameter("@HomeAddress", SqlDbType.NVarChar);
                    param[13] = new SqlParameter("@Note", SqlDbType.NVarChar);


                    param[0].Value = data.Rows[i][0].ToString().Trim();
                    param[1].Value = data.Rows[i][1].ToString().Trim();
                    param[2].Value = data.Rows[i][2].ToString().Trim();
                    param[3].Value = data.Rows[i][3].ToString().Trim();
                    param[4].Value = data.Rows[i][4].ToString().Trim();
                    param[5].Value = data.Rows[i][5].ToString().Trim();
                    param[6].Value = data.Rows[i][6].ToString().Trim();
                    param[7].Value = data.Rows[i][7].ToString().Trim();
                    param[8].Value = data.Rows[i][8].ToString().Trim();
                    param[9].Value = data.Rows[i][9].ToString().Trim();
                    param[10].Value = data.Rows[i][10].ToString().Trim();
                    param[11].Value = data.Rows[i][11].ToString().Trim();
                    param[12].Value = data.Rows[i][12].ToString().Trim();
                    param[13].Value = data.Rows[i][13].ToString().Trim();

                   if(SQLDBhelp.ExecuteSQL(queryString, param) > 0) 
                   else
                    {
                        MessageBox.Show("增加失败");
                    }
                }

                MessageBox.Show("增加成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show("增加失败");
            }
        }