在项目中使用Npoi导出到Excel

 

 

//调用Npoi实现导出Excel的功能
        private void btnExportToExcel_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;//得到GridView的绑定数据源，数据源是一个员工类数组

            for (int i = 0; i < employees.Length; i++)//遍历员工类
            {
                Employee employee = employees[i]; //得到其中第i个员工对象

                IRow row = sheet.CreateRow(i + 1); //创建第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();

                //格式具体有哪些请看单元格右键中的格式，有说明，excel中的日期类型是使用数字来显示的

                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);
            }
        }