引用NPOI包
通过Nuget添加NPOI库文件
样例代码
前台代码
<Grid>
<Button Name="Button_Write" Click="Button_Write_Click" Content="写入" HorizontalAlignment="Left" Margin="38,37,0,0" VerticalAlignment="Top" Width="75"/>
<Button Name="Button_Read" Click="Button_Read_Click" Content="读取" HorizontalAlignment="Left" Margin="118,37,0,0" VerticalAlignment="Top" Width="75"/>
<TextBlock Name="TextBlock1" Margin="10,76,10,10" Text=""/>
</Grid>
后台代码
public void WriteToExcel()
{
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建工作簿
ISheet SheetOne = workbook2003.CreateSheet("Sheet1"); //新建1个Sheet工作表
//对工作表先添加行,下标从0开始
SheetOne.CreateRow(0); //创建10行
ICellStyle csNumeric = workbook2003.CreateCellStyle();
csNumeric.DataFormat = 194;
IRow SheetRow = SheetOne.GetRow(0); //获取Sheet1工作表的首行
SheetRow.CreateCell(0).SetCellValue(true); //创建Cell并赋bool值
SheetRow.CreateCell(1).SetCellValue("赋字符串值"); //创建Cell并赋字符串值
SheetRow.CreateCell(2,CellType.Numeric).SetCellValue(0.22222); //创建Cell并赋字符串值
SheetRow.GetCell(2).CellStyle = csNumeric;
SheetRow.CreateCell(3,CellType.Numeric).SetCellValue(8888); //创建Cell并赋整型值
SheetRow.GetCell(3).CellStyle = csNumeric;
IDataFormat dataFormatCustom = workbook2003.CreateDataFormat();
SheetRow.CreateCell(4).CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyy/MM/dd");
SheetRow.GetCell(4).SetCellValue(DateTime.Now); //设置日期
//设置列宽
SheetOne.SetColumnWidth(0, 6 * 256);
SheetOne.SetColumnWidth(1, 20 * 256);
SheetOne.SetColumnWidth(2, 25 * 256);
SheetOne.CreateRow(1);
//合并单元格,首行、末行、首列、末列
SheetOne.AddMergedRegion(new CellRangeAddress(1, 1, 0, 5));
//填充内容,注意:上一步添加了合并的单元格后仍然需要create cell
SheetOne.GetRow(1).CreateCell(0).SetCellValue("合并单元格内容");
//设置单元格的样式
ICellStyle headerStyle = workbook2003.CreateCellStyle();
headerStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
//设置字体
IFont font = workbook2003.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
headerStyle.SetFont(font);
//内容右对齐
SheetOne.GetRow(1).GetCell(0).CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
SheetOne.GetRow(1).GetCell(0).CellStyle = headerStyle;
FileStream file2003 = new FileStream(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls", FileMode.Create);
workbook2003.Write(file2003);
file2003.Close();
workbook2003.Close();
}
public void ReadFromExcel()
{
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls");
try
{
FileStream fs = File.OpenRead(@"\\vmware-host\Shared Folders\下载 2\Excel2003.xls");
if (extension.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
fs.Close();
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
IRow row = sheet.GetRow(0); //读取当前行数据
//LastRowNum 是当前表的总行数-1(注意)
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i); //读取当前行数据
if (row != null)
{
//LastCellNum 是当前行的总列数
for (int j = 0; j < row.LastCellNum; j++)
{
//读取该行的第j列数据
if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
TextBlock1.Text += row.GetCell(j).DateCellValue.ToString()+"\t";
else
{
string value = row.GetCell(j).ToString();
TextBlock1.Text += (value.ToString() + "\t");
}
}
TextBlock1.Text += ("\n");
}
}
}
catch (Exception e)
{
TextBlock1.Text += (e.Message);
}
}
private void Button_Read_Click(object sender, RoutedEventArgs e)
{
ReadFromExcel();
}
private void Button_Write_Click(object sender, RoutedEventArgs e)
{
WriteToExcel();
}