C#NPOI对Excel读、写、增、删操作，dataGridView控件常⽤操作1.NPOI写⼊写⼊Excel⽂件
private void button1_Click_1(object nder, EventArgs e)
{
WriteToExcel("Type.xlsx");
}
//创建⼯作薄
public void WriteToExcel(string filePath)
{
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的⽂件格式创建对应的类
if(extension.Equals(".xlsx"))
{
wb =new HSSFWorkbook();
}
el
{
wb =new XSSFWorkbook();
}
ICellStyle style1 = wb.CreateCellStyle();//样式
style1.Alignment = NPOI.SS.UrModel.HorizontalAlignment.Left;//⽂字⽔平对齐⽅式
style1.VerticalAlignment = NPOI.SS.UrModel.VerticalAlignment.Center;//⽂字垂直对齐⽅式
/
/style1.BorderBottom = NPOI.SS.UrModel.BorderStyle.Thin;//设置边框
//style1.BorderLeft = NPOI.SS.UrModel.BorderStyle.Thin;
//style1.BorderRight = NPOI.SS.UrModel.BorderStyle.Thin;
//style1.BorderTop = NPOI.SS.UrModel.BorderStyle.Thin;
//style1.WrapText = true;//⾃动换⾏
ICellStyle style2 = wb.CreateCellStyle();//样式
IFont font1 = wb.CreateFont();//字体
font1.FontName ="楷体";
//font1.Color = HSSFColor.Red.Index;//字体颜⾊
//style2.SetFont(font1);//样式⾥的字体设置具体的字体样式
//style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; //设置背景⾊
/
/style2.FillPattern = FillPattern.SolidForeground;
//style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UrModel.HorizontalAlignment.Left;//⽂字⽔平对齐⽅式
style2.VerticalAlignment = NPOI.SS.UrModel.VerticalAlignment.Center;//⽂字垂直对齐⽅式
ICellStyle dateStyle = wb.CreateCellStyle();//样式
dateStyle.Alignment = NPOI.SS.UrModel.HorizontalAlignment.Left;//⽂字⽔平对齐⽅式
dateStyle.VerticalAlignment = NPOI.SS.UrModel.VerticalAlignment.Center;//⽂字垂直对齐⽅式
IDataFormat dataFormatCustom = wb.CreateDataFormat();//设置数据显⽰格式
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
ISheet sheet = wb.CreateSheet("Sheet0");//创建⼀个表单
int[] columnWidth ={5,20,20,10,10,10,10,10,10};//设置列宽
for(int i =0; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i,256* columnWidth[i]);
}
//测试数据
int rowCount =3, columnCount =8;
object[,] data ={
{"序号","修改⽇期","型号","孔1","孔2","孔3","孔4","孔5","孔6"},
{"1",DateTime.Now,"BEEFY 180-5",100.25,100.25,100.25,100.25,100.25,100.25},
{"2",DateTime.Now,"BEEFY 180-5",100.25,100.25,100.25,100.25,100.25,100.25}
/
/⽇期可以直接传字符串，NPOI会⾃动识别
//如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显⽰为数字
};
IRow row;
ICell cell;
for(int i =0; i < rowCount; i++)
row = sheet.CreateRow(i);//创建第i⾏
for(int j =0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
cell.CellStyle = j %2==0? style1 : style2;
/
/根据数据类型设置不同类型的cell
object obj = data[i, j];
SetCellValue(cell, data[i, j]);
//如果是⽇期，则设置⽇期显⽰的格式
if(obj.GetType()==typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容⾃动调整列宽，需要先tCellValue再调⽤
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上⾓的
//CellRangeAddress(0, 2, 0, 0)，合并0-2⾏，0-0列的单元格
//CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
//sheet.AddMergedRegion(region);
try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs);//向打开的这个Excel⽂件中写⼊表单并保存。
fs.Clo();
}
catch(Exception e)
{
Debug.WriteLine(e.Message);
}
}
//根据数据类型设置不同类型的cell
public static void SetCellValue(ICell cell,object obj)
{
if(obj.GetType()==typeof(int))
{
cell.SetCellValue((int)obj);
}
el if(obj.GetType()==typeof(double))
{
cell.SetCellValue((double)obj);
}
el if(obj.GetType()==typeof(IRichTextString))
{
cell.SetCellValue((IRichTextString)obj);
}
el if(obj.GetType()==typeof(string))
{
cell.SetCellValue(obj.ToString());
}
el if(obj.GetType()==typeof(DateTime))
{
cell.SetCellValue((DateTime)obj);
}
el if(obj.GetType()==typeof(bool))
{
cell.SetCellValue((bool)obj);
}
el
饮酒的诗句
{
cell.SetCellValue(obj.ToString());
}
}
执⾏结果：
1.1.从Excel读取到dataGridView Type.cs
using;
using;
using;
using;
using;
namespace
{
class Type
{
/
/
private string stuIdx;
public string StuIdx
{
get{return stuIdx;}
t{ stuIdx =value;}
}
//
private string stuTime;
public string StuTime
{
get{return stuTime;}
t{ stuTime =value;}
}
//
private string stuType;
public string StuType
{
get{return stuType;}
t{ stuType =value;}
}
//
private string stuDist1;
public string StuDist1
{
get{return stuDist1;}
t{ stuDist1 =value;}
}
//
private string stuDist2;
public string StuDist2
{
get{return stuDist2;}如何缓解焦虑
成长教育电影
get{return stuDist2;}乱伦小书
t{ stuDist2 =value;}
}
//
private string stuDist3;
public string StuDist3
{
get{return stuDist3;}
t{ stuDist3 =value;}
}
//
private string stuDist4;桔子和橘子一样吗
public string StuDist4
{
get{return stuDist4;}
t{ stuDist4 =value;}
}
//
private string stuDist5;
public string StuDist5
{
get{return stuDist5;}
节俭手抄报
t{ stuDist5 =value;}
}
//
private string stuDist6;
public string StuDist6
{
get{return stuDist6;}
t{ stuDist6 =value;}
}
//
private string stuDist7;
public string StuDist7
{
get{return stuDist7;}
t{ stuDist7 =value;}
}
}
}
button1
//读取
private void button1_Click_1(object nder, EventArgs e)
{
List<Type> stuList =new List<Type>();
FileStream fs =new FileStream("Type.xlsx", FileMode.Open, FileAccess.Read); XSSFWorkbook wb =new XSSFWorkbook(fs);
fs.Clo();
XSSFSheet sheet =(XSSFSheet)wb.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int count =1;
while(rows.MoveNext())
{
//if (count == 1)
//{
// count += 1;
// continue;
//}
//el
count +=1;
XSSFRow row =(XSSFRow)rows.Current;
XSSFRow row =(XSSFRow)rows.Current;
Type st =new Type();
for(int i =0; i < row.LastCellNum; i++)
{
//ICell cell = row.GetCell(i);
switch(i)
破壁灵芝孢子粉的功效与作用{
ca0:
st.StuIdx = row.GetCell(i).ToString(); Console.WriteLine(st.StuIdx);
break;
ca1:
st.StuTime = row.GetCell(i).ToString(); Console.WriteLine(st.StuTime);
break;
ca2:
st.StuType = row.GetCell(i).ToString(); Console.WriteLine(st.StuType);
break;
ca3:
st.StuDist1 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist1);
break;
ca4:
st.StuDist2 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist2);
break;
ca5:
st.StuDist3 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist3);
break;
ca6:
st.StuDist4 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist4);
break;
ca7:
st.StuDist5 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist5);
break;
ca8:
st.StuDist6 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist6);
break;
ca9:
st.StuDist7 = row.GetCell(i).ToString(); Console.WriteLine(st.StuDist7);
break;
}
}
stuList.Add(st);
}
this.dataGridView2.DataSource = stuList;
漠然是什么意思}
1.2.从dataGridView写⼊到Excel NpoiExcel.cs
using;
using;
using;
using;
using;
using;
using;
