Excel数据导入导出
1.将NPOI文件文件添加引用到项目中
2.编写代码
2.1Excel数据导入数据库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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("数据导入成功");
}
{
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数据导出数据库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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文件
}
{
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文件
}