标签： npoi

在.NET中，我们操作excel一般都是使用NPOI这个组件，它成熟稳定又好用。下面的代码记录了项目中的一段使用NPOI读取excel的功能的实现：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

IWorkbook hssfwb;
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
        hssfwb = new XSSFWorkbook(fs);
    else // if (fileName.IndexOf(".xls") > 0) // 2003版本
        hssfwb = new HSSFWorkbook(fs); 
}

ISheet sheet = hssfwb.GetSheetAt(0); // 或 ISheet sheet = hssfwb.GetSheet("Arkusz1");
for (int row = 1; row <= sheet.LastRowNum; row++)
{
    if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
    {
        var s = string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue);
    }
}

C#使用NPOI导出Excel

使用代码如下：

using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Text;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

namespace Common
{
    public class NPOIHelper
    {
        public void Export(System.Windows.Forms. ListView listView, string fileName)
        {
            var hssfworkbook = new HSSFWorkbook();
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory .CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;



            //调整列宽
            var sheet1 = hssfworkbook.CreateSheet( "Sheet1");
            sheet1.SetColumnWidth(0, 10000);
            sheet1.SetColumnWidth(1, 10000);
            sheet1.SetColumnWidth(2, 10000);
            sheet1.SetColumnWidth(3, 10000);

            //表头
            var row0 = sheet1.CreateRow(0);
            row0.CreateCell(0).SetCellValue( "企业名称" );
            row0.CreateCell(1).SetCellValue( "中文字号" );
            row0.CreateCell(2).SetCellValue( "中文拼音" );
            row0.CreateCell(3).SetCellValue( "行业特征" );

            for( int i=0; i<listView.Items.Count; i++)
            {
                string a = listView.Items[i].SubItems[0].Text;
                string b = listView.Items[i].SubItems[1].Text;
                string c = listView.Items[i].SubItems[2].Text;
                string d = listView.Items[i].SubItems[3].Text;
                var row = sheet1.CreateRow(i+1); //如果不使用表头，这里就不用+1了
                row.CreateCell(0).SetCellValue(a);
                row.CreateCell(1).SetCellValue(b);
                row.CreateCell(2).SetCellValue(c);
                row.CreateCell(3).SetCellValue(d);
            }


            //Write the stream data of workbook to the root directory
            //MemoryStream file = new MemoryStream();
           // hssfworkbook.Write(file);

            //写入文件
            FileStream file = new FileStream (fileName, FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
     

        }
    }
}

调用：

注意先using Common;

/*保存对话框*/
SaveFileDialog saveFileDialog = new SaveFileDialog ();
saveFileDialog.Filter = "导出Excel(*.xls)|*.xls";

if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
    NPOIHelper npoi = new NPOIHelper();
    npoi.Export(this.listView1, saveFileDialog.FileName);
    MessageBox.Show( "导出完成！" );
              
}