Excel数据导入导出

1.将NPOI文件文件添加引用到项目中

2.编写代码
2.1Excel数据导入数据库

 private void toolStripButton1_Click(object sender, EventArgs e)
        {
            if (importDialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            string filename = importDialog.FileName;
            using (FileStream stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                HSSFSheet sheet = workbook.GetSheetAt(0);
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i);
                    //顾客姓名
                    string custName = row.GetCell(0).StringCellValue;
                    //住宅电话
                    string telNum = row.GetCell(1).StringCellValue;
                    //手机
                    string mobileNum = row.GetCell(2).StringCellValue;
                    //详细通讯地址
                    string address = row.GetCell(3).StringCellValue;
                    //邮政编码
                    string postcode = row.GetCell(4).StringCellValue;
                    //车号
                    string carNum = row.GetCell(5).StringCellValue;
                    //车架号
                    string brNum = row.GetCell(6).StringCellValue;
                    //购买日期
                    string strBuyDate = row.GetCell(7).StringCellValue.ToString();
                    //分店编号
                    string branchNum = row.GetCell(8).StringCellValue;

                    //如果已经存在的车号、车架号，跳过不导入
                    if (customersTableAdapter.GetDataByCarNum(carNum).Count > 0)
                    {
                        continue;
                    }
                    if (customersTableAdapter.GetDataByBracketNum(brNum).Count > 0)
                    {
                        continue;
                    }

                    DateTime? buyDate = null;
                    if (!string.IsNullOrEmpty(strBuyDate))
                    {
                        buyDate = DateTime.Parse(strBuyDate);
                    }
                    CallCenter.DAL.BranchDS.BranchsDataTable branchTable = branchsTableAdapter.GetDataByNumberShop(branchNum);
                    if (branchTable.Count <= 0)
                    {
                        UIHelper.ShowError("错误的分店编号");
                        return;
                    }
                    if (branchTable.Count > 1)
                    {
                        throw new Exception("存在多条同样Number的门店信息");
                    }

                    CallCenter.DAL.BranchDS.BranchsRow branchRow = branchTable[0];
                    int branchId = branchRow.Id;
                    customersTableAdapter.Insert(Guid.NewGuid(), custName, telNum, mobileNum, postcode, "", address, branchId, buyDate, carNum, brNum, "", "", "", "", "");
                }
            }
            MessageBox.Show("数据导入成功");
        }

 

 2.2Excel数据导出数据库

private void toolStripButton2_Click(object sender, EventArgs e)
        {
            if (saveFileDialog1.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            string filename = saveFileDialog1.FileName;
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet("客户资料");
            HSSFRow row = sheet.CreateRow(0);
            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("分店编号");

            CustomersTableAdapter uadap = new CustomersTableAdapter();
            DAL.CustomersDs.CustomersDataTable userTable = uadap.GetData();

            int i = 1;
            foreach (CallCenter.DAL.CustomersDs.CustomersRow custRow in userTable)
            {
                HSSFRow newRow = sheet.CreateRow(i);
                newRow.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.Name);
                newRow.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.TelPhone);
                newRow.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.MobilePhone);
                newRow.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.Address);
                newRow.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.PostCode);
                newRow.CreateCell(5, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.CarNum);
                newRow.CreateCell(6, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.BracketNum);

                if (custRow.IsBuyDateNull())
                {
                    newRow.CreateCell(7, HSSFCell.CELL_TYPE_STRING).SetCellValue("");
                }
                else
                {
                    newRow.CreateCell(7, HSSFCell.CELL_TYPE_STRING).SetCellValue(custRow.BuyDate);
                }

                CallCenter.DAL.BranchDS.BranchsRow branch = branchsTableAdapter.GetDataById(custRow.BranchsId).Single();
                newRow.CreateCell(8, HSSFCell.CELL_TYPE_STRING).SetCellValue(branch.NumberShop);
                i++;
            }

            using (FileStream stream = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(stream);
            }
            MessageBox.Show("导出完毕");
            Process.Start(filename);//自动打开Excel文件
        }