NPOI Excel导入导出

NPOI Excel导入导出

首先需要在项目中添加NPOI的dll文件，然后再添加引用这些dll文件，就可以在项目中进行使用了。

1：NPOI Excel导出

/// <summary>
/// NPOI方法导出Excel文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, RoutedEventArgs e)
{
    SaveFileDialog sdfExport = new SaveFileDialog();
    sdfExport.Filter = "Excel文件|*.xls";
    if (sdfExport.ShowDialog() != true)
    {
        return;
    }
    string filename = sdfExport.FileName;
    HSSFWorkbook workbook = new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("员工数据");

    IRow rowHeader = sheet.CreateRow(0);//表头行
    rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
    rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
    rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职日期");

    //把查询结果导出到Excel
    Employee[] employees = (Employee[])datagrid.ItemsSource;
    for (int i = 0; i < employees.Length; i++)
    {
        Employee employee = employees[i];
        IRow row = sheet.CreateRow(i + 1);
        row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
        row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);

        //格式设置
        ICellStyle styledate = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
        ICell cellInDate = row.CreateCell(2, CellType.NUMERIC);
        cellInDate.CellStyle = styledate;
        cellInDate.SetCellValue(employee.InDate);
    }
    using (Stream stream = File.OpenWrite(filename))
    {
        workbook.Write(stream);
    }
}

 

2：NPOI Excel文件导入