1.添加NPOI的相关程序集的引用：
数据库导入Excel文件：
/// <summary>
/// 数据库导入Excel文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOutToExcel_Click(object sender, EventArgs e)
{
string sql = "SELECT * FROM NewsListTable";
using (SqlDataReader reader = dataHelper.SqlDataReader(sql, CommandType.Text, null))
{
if (reader.HasRows)
{
using (FileStream fs = new FileStream("NewsList.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("NewsListSheet");
int index = 0;
//加载列名称
IRow colsrow = sheet.CreateRow(index);
index++;
for (int u = 0; u < reader.FieldCount; u++)
{
colsrow.CreateCell(u).SetCellValue(reader.GetName(u).ToString());
}
while (reader.Read())
{
IRow currentRow = sheet.CreateRow(index);
index++;
int newsid = reader.GetInt32(0);
string newstitle = reader.IsDBNull(1) ? null : reader.GetString(1);
DateTime? date = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2);
//插入数据
currentRow.CreateCell(0).SetCellValue(newsid.ToString());
if (newstitle == null)
{//空值
ICell cell = currentRow.CreateCell(1);
cell.SetCellType(CellType.Blank);
}
else
{
currentRow.CreateCell(1).SetCellValue(newstitle);
}
if (date == null)
{
//空值
ICell cell = currentRow.CreateCell(2);
cell.SetCellType(CellType.Blank);
}
else
{
//日期类型需要在Excel文件中格式化
ICell cell = currentRow.CreateCell(2);//创建单元格
ICellStyle style = workbook.CreateCellStyle();//创建单元格样式
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
cell.CellStyle = style;//单元格应用该样式
cell.SetCellValue((DateTime)date);//为单元格赋值
}
}
//写入磁盘
workbook.Write(fs);
this.Text = "写入成功！";
}
}
else
{
this.Text = "数据导出失败！";
}
}
}
Excel文件导入数据库：
/// <summary>
/// Excel导出数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnInDataByExcel_Click(object sender, EventArgs e)
{
using (FileStream fs = new FileStream("NewsList.xls", FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("NewsListSheet");
if (sheet.LastRowNum != 0)
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
string sql = "INSERT INTO NEWSTAB VALUES(@NEWTITLE,@PID)";
SqlParameter[] pms = {
new SqlParameter("@NEWTITLE",SqlDbType.NVarChar,50),
new SqlParameter("@PID",SqlDbType.DateTime),
};
IRow row = sheet.GetRow(i);
if (row == null) { } else { }
for (int r = 1; r < row.LastCellNum; r++)
{
ICell cell = row.GetCell(r);
if (cell == null || cell.CellType == CellType.Blank)
{
//该单元格空值
pms[r - 1].Value = DBNull.Value;
}
else
{
if (r == 2)
{
//日期
pms[r - 1].Value = DateTime.FromOADate(cell.NumericCellValue);
}
else
{
pms[r - 1].Value = (cell.StringCellValue);
}
}
}
//每次执行一行
if (dataHelper.SqlNonQuery(sql, CommandType.Text, pms)) { this.Text = "导出成功！"; } else { this.Text = "导出失败！"; }
}
}
else
{
this.Text = "导入失败，无数据！";
}
}
}
Excel文件追加数据：
private void btnAppend_Click(object sender, EventArgs e)
{
List<NewsListCls> cls = new List<NewsListCls>() {
new NewsListCls(){ _NewsId=1,_NewsTitle="微信",_NewsDate=Convert.ToDateTime("2020/02/04")},
new NewsListCls(){ _NewsId=2,_NewsTitle="QQ",_NewsDate=Convert.ToDateTime("2020/02/06")},
new NewsListCls(){ _NewsId=1,_NewsTitle="微博",_NewsDate=Convert.ToDateTime("2020/02/08")},
};
using (FileStream fs=new FileStream("NewsList.xls",FileMode.Open,FileAccess.Read))//1.先以读取模式打开（获取原有数据）
{
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("NewsListSheet");
//循环每一行数据，加载
for (int i = 0; i < cls.Count; i++)//2.添加新数据(新行)
{
IRow cuurentRow = sheet.CreateRow(sheet.LastRowNum + 1);//每次追加一行
cuurentRow.CreateCell(0).SetCellValue(cls[i]._NewsId.ToString());
cuurentRow.CreateCell(1).SetCellValue(cls[i]._NewsTitle.ToString());
ICell cell = cuurentRow.CreateCell(2);//日期单元格
ICellStyle style = workbook.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
cell.CellStyle = style;//该单元格应用样式
cell.SetCellValue(Convert.ToDateTime(cls[i]._NewsDate));
}
FileStream zs=File.OpenWrite("NewsList.xls");//3.以写入流整体覆盖写入(它整体覆盖旧数据)
workbook.Write(zs);
this.Text = "数据追加成功！";
}
}
Excel文件普通读取：
private void btnRead_Click(object sender, EventArgs e)
{
using (FileStream fs = new FileStream("prs.xls", FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("PersonCls");
List<Person> ulist = new List<Person>();
for (int i = 0; i <= sheet.LastRowNum; i++) //sheet.LastRowNum 3：这个是最后行索引
{
IRow row = sheet.GetRow(i);
for (int r = 0; r < row.LastCellNum; r++)//row.LastCellNum 4:返回的是单元格总个数
{
if (r == 0)
{
Person person = new Person();
person._StuName = row.GetCell(0).StringCellValue;
person._StuAge = Convert.ToInt32(row.GetCell(1).StringCellValue);
person._StuSex = row.GetCell(2).StringCellValue;
person._StuEmail = row.GetCell(3).StringCellValue;
ulist.Add(person);
}
else
{
continue;//跳出此次循环
}
}
}
this.dgperson.DataSource = ulist;
}
}
Excel文件简单写入：
private void btnIn_Click(object sender, EventArgs e)
{
List<Person> ulist = new List<Person>()
{
new Person(){ _StuName="张三",_StuAge=15,_StuSex="男",_StuEmail="1112@qq.com"},
new Person(){ _StuName="李四",_StuAge=12,_StuSex="男",_StuEmail="1555@qq.com"},
new Person(){ _StuName="王五",_StuAge=21,_StuSex="男",_StuEmail="4548@qq.com"},
new Person(){ _StuName="刘明",_StuAge=16,_StuSex="男",_StuEmail="4445@qq.com"},
};
//创建Excel工作簿
IWorkbook workbook = new HSSFWorkbook();//Iworkbook是工作簿接口 HSSFWorkbook是具体实现类，一个HSSFWorkbook类型就是一个EXCEL文件
//创建Sheet表
ISheet sheet = workbook.CreateSheet("PersonCls");
//遍历集合创建每一行
for (int i = 0; i < ulist.Count; i++)
{
IRow row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue(ulist[i]._StuName);
row.CreateCell(1).SetCellValue(ulist[i]._StuAge.ToString());
row.CreateCell(2).SetCellValue(ulist[i]._StuSex);
row.CreateCell(3).SetCellValue(ulist[i]._StuEmail);
}
//写入到磁盘
using (FileStream fs = new FileStream("prs.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
MessageBox.Show("写入成功！");
}
WPS更改之后再次写入读取会发生错误，使用Microsoft文档编辑器不会发生错误！
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
