首页 > 其他 > 详细

NPOI的使用

时间:2017-06-29 10:59:29      阅读:294      评论:0      收藏:0      [点我收藏+]

1、下载/导包

  http://npoi.codeplex.com/releases

  下载:NPOI binary 2.1.3.1

  解压源码包后的文件目录结构

  ------------------------------------------

d   dotnet2

d   dotnet4

d   logo

-  LICENSE

-  Read Me.txt

-  Release Notes.txt

  进入 dotnet2 || dotnet4

  

2、添加四个.dll引用

  NPOI.dll  NPOI.OOXML.dll  NPOI.OpenXml4Net  NPOI.OpenmlFormats

3、在官方文档拿示例

  https://dotblogs.com.tw/killysss/archive/2010/01/27/13344.aspx

4、生成方法

 

 


 

public ResultSet<T> ExportDataSetToExcel<T>(List<T> list, string downloadPath, string destFilename, string searchParams = "")
        {
            //创建excel 工作簿对象
            IWorkbook workbook = CreateWorkbook(downloadPath + destFilename);

            //创建excel 表对象
            ISheet sheet = CreateSheet<T>(list, searchParams, workbook);

            //写入到excel文件中
            var stream = WorkBookToMemoryStream(workbook);
            WriteSteamToFile(stream, downloadPath + destFilename);

            return new ResultSet<T> { IsSuccess = true };
        }

 

 


 

private IWorkbook CreateWorkbook(string templateFileName)
        {
            using (FileStream file = new FileStream(templateFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                if ((templateFileName.Substring(templateFileName.LastIndexOf(".")).IndexOf("xlsx")) > 0)
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    workbook = new HSSFWorkbook(file);
                }
                return workbook;

            }

        }

 

private static ISheet CreateSheet<T>(List<T> list, string searchParams, IWorkbook workbook)
        {
            ISheet sheet = workbook.GetSheet("Sheet1");

            var bgRow = sheet.FirstRowNum;
            var edRow = sheet.LastRowNum;

            //插入查询条件到excel
            sheet.GetRow(2).GetCell(1).SetCellValue(searchParams);

            IRow rData = sheet.GetRow(bgRow);//excel表的第一行内容

            DataSet listDataSet = list.ToDataSet<T>();
            for (int i = 0; i < listDataSet.Tables[0].Rows.Count; i++)
            {
                var row = listDataSet.Tables[0].Rows[i];
                IRow irow = sheet.CreateRow(edRow + 1);

                for (int j = rData.FirstCellNum; j < rData.LastCellNum; j++)
                {
                    var columnDataSet = row[rData.GetCell(j).ToString()].IfNull(0);
                    var cell = irow.CreateCell(j);
                    cell.SetCellValue(columnDataSet.ToString());
                }
                edRow++;
            }

            //删除excel一第行
            sheet.ShiftRows(1, edRow, -1);
            return sheet;
        }

  

/// <summary>
/// 转换成输出流
/// </summary>
/// <param name="InputStream"></param>
/// <returns></returns>
public static MemoryStream WorkBookToMemoryStream(IWorkbook workbook)
{
      MemoryStream file = new MemoryStream();
      workbook.Write(file);
      return file;
}

 

/// <summary>
/// 写入到excel文件中
/// </summary>
/// <param name="ms"></param>
/// <param name="FileName"></param>
private static void WriteSteamToFile(MemoryStream ms, string FileName)
{
      FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
      byte[] data = ms.ToArray();

      fs.Write(data, 0, data.Length);
      fs.Flush();
      fs.Close();

      data = null;
      ms = null;
      fs = null;
}

  

5、初步搞定,能根据传来的excel模板插入数据生成一个新的excel

 

NPOI的使用

原文:http://www.cnblogs.com/tooy/p/7093188.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!