C#利用NPOI操作excel

来源：互联网发布：阿里云代金券怎么用编辑：程序博客网时间：2024/05/02 04:35

首先添加引用NPOI.dll

using NPOI.HSSF.UserModel

using NPOI.SS.UserModel

HSSFWorkbook wb1 = new HSSFWorkbook();
Sheet sheet1 = wb1.CreateSheet();

接上，读取exce（路径可以更改，此处为d:/myxls.xls）：

using (FileStream fsRead = File.OpenRead(@"d:/myxls.xls"))
{
wb1 = new HSSFWorkbook(fsRead);
sheet1 = wb1.GetSheetAt(0);
NumAll = sheet1.LastRowNum + 1; //获取文本最后一行具体为多少，然后加一
if (sheet1.LastRowNum != 0) //清空之后没有数据时无法读取datetime
{
DateTime LastTime = DateTime.Parse(sheet1.GetRow(sheet1.LastRowNum).GetCell(2).ToString());
TimeSpan _ts = LastTime - DateTime.Now;
if (_ts < TimeSpan.Zero)
{
NumToday = Convert.ToInt16(sheet1.GetRow(sheet1.LastRowNum).GetCell(1).ToString()) + 1;
}
else
{
NumToday = 1;
}
}

}

接上读取excel后，写操作：

IRow row = sheet1.CreateRow(NumAll);
ICell[] cell = new ICell[13];
for (int i = 0; i < 13; i++)
{
cell[i] = row.CreateCell(i); //把每个cell[i]和第i+1个单元格对应
}

cell[0].SetCellValue(NumAll); //对应完之后给每个单元格
cell[1].SetCellValue(NumToday.ToString());
cell[2].SetCellValue(DateTime.Now.ToString());
cell[3].SetCellValue(MakeSNNumber(NumToday));
cell[4].SetCellValue(textBox_MAC.Text);
cell[5].SetCellValue(textBox_Temp.Text.ToString());
cell[6].SetCellValue(textBox_Humi.Text.ToString());
cell[7].SetCellValue(textBox_Light.Text.ToString());
cell[8].SetCellValue(textBox_LED.Text.ToString());
cell[9].SetCellValue(textBox_A.Text.ToString());
cell[10].SetCellValue(textBox_V.Text.ToString());
cell[11].SetCellValue(textBox_Power.Text.ToString());
cell[12].SetCellValue(textBox_KWH.Text.ToString());
using (FileStream fs = File.OpenWrite(@"d:/myxls.xls"))
{
wb1.Write(fs);
MessageBox.Show("写入excel完成");
}

0 0