• NPOI读写Excel


    1、整个Excel表格叫做工作表:WorkBook(工作薄),包含的叫页(工作表):Sheet;行:Row;单元格Cell。

    2、NPOI是POI的C#版本,NPOI的行和列的index都是从0开始

    3、POI读取Excel有两种格式一个是HSSF,另一个是XSSF。 HSSF和XSSF的区别如下: 
    HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. 
    XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. 
    即:HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

    下面是用NPOI读写Excel的例子:ExcelHelper封装的功能主要是把DataTable中数据写入到Excel中,或者是从Excel读取数据到一个DataTable中。

     ExcelHelper类:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using NPOI.HSSF.UserModel;
    using System.IO;
    using System.Data;
    
    namespace GMapDemo
    {
    class ExcelHelper : IDisposable
    {
    private string fileName = null; //文件名
    private IWorkbook workbook = null;
    private FileStream fs = null;
    private bool disposed;
    
    public ExcelHelper(string fileName)
    {
    this.fileName = fileName;
    disposed = false;
    }
    
    /// <summary>
    /// 将DataTable数据导入到excel中
    /// </summary>
    /// <param name="data">要导入的数据</param>
    /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
    /// <param name="sheetName">要导入的excel的sheet的名称</param>
    /// <returns>导入数据行数(包含列名那一行)</returns>
    public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
    {
    int i = 0;
    int j = 0;
    int count = 0;
    ISheet sheet = null;
    
    fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
    workbook = new XSSFWorkbook();
    else if (fileName.IndexOf(".xls") > 0) // 2003版本
    workbook = new HSSFWorkbook();
    
    try
    {
    if (workbook != null)
    {
    sheet = workbook.CreateSheet(sheetName);
    }
    else
    {
    return -1;
    }
    
    if (isColumnWritten == true) //写入DataTable的列名
    {
    IRow row = sheet.CreateRow(0);
    for (j = 0; j < data.Columns.Count; ++j)
    {
    row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
    }
    count = 1;
    }
    else
    {
    count = 0;
    }
    
    for (i = 0; i < data.Rows.Count; ++i)
    {
    IRow row = sheet.CreateRow(count);
    for (j = 0; j < data.Columns.Count; ++j)
    {
    row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
    }
    ++count;
    }
    workbook.Write(fs); //写入到excel
    return count;
    }
    catch (Exception ex)
    {
    Console.WriteLine("Exception: " + ex.Message);
    return -1;
    }
    }
    
    /// <summary>
    /// 将excel中的数据导入到DataTable中
    /// </summary>
    /// <param name="sheetName">excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
    {
    ISheet sheet = null;
    DataTable data = new DataTable();
    int startRow = 0;
    try
    {
    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
    workbook = new XSSFWorkbook(fs);
    else if (fileName.IndexOf(".xls") > 0) // 2003版本
    workbook = new HSSFWorkbook(fs);
    
    if (sheetName != null)
    {
    sheet = workbook.GetSheet(sheetName);
    }
    else
    {
    sheet = workbook.GetSheetAt(0);
    }
    if (sheet != null)
    {
    IRow firstRow = sheet.GetRow(0);
    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
    
    if (isFirstRowColumn)
    {
    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
    {
    DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
    data.Columns.Add(column);
    }
    startRow = sheet.FirstRowNum + 1;
    }
    else
    {
    startRow = sheet.FirstRowNum;
    }
    
    //最后一列的标号
    int rowCount = sheet.LastRowNum;
    for (int i = startRow; i <= rowCount; ++i)
    {
    IRow row = sheet.GetRow(i);
    if (row == null) continue; //没有数据的行默认是null       
    
    DataRow dataRow = data.NewRow();
    for (int j = row.FirstCellNum; j < cellCount; ++j)
    {
    if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
    dataRow[j] = row.GetCell(j).ToString();
    }
    data.Rows.Add(dataRow);
    }
    }
    
    return data;
    }
    catch (Exception ex)
    {
    Console.WriteLine("Exception: " + ex.Message);
    return null;
    }
    }
    
    public void Dispose()
    {
    Dispose(true);
    GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
    if (!this.disposed)
    {
    if (disposing)
    {
    if (fs != null)
    fs.Close();
    }
    
    fs = null;
    disposed = true;
    }
    }
    }
    }
    View Code

    测试代码:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace NPOIExcelExample
    {
        class Program
        {
            static DataTable GenerateData()
            {
                DataTable data = new DataTable();
                for (int i = 0; i < 5; ++i)
                {
                    data.Columns.Add("Columns_" + i.ToString(), typeof(string));
                }
    
                for (int i = 0; i < 10; ++i)
                {
                    DataRow row = data.NewRow();
                    row["Columns_0"] = "item0_" + i.ToString();
                    row["Columns_1"] = "item1_" + i.ToString();
                    row["Columns_2"] = "item2_" + i.ToString();
                    row["Columns_3"] = "item3_" + i.ToString();
                    row["Columns_4"] = "item4_" + i.ToString();
                    data.Rows.Add(row);
                }
                return data;
            }
    
            static void PrintData(DataTable data)
            {
                if (data == null) return;
                for (int i = 0; i < data.Rows.Count; ++i)
                {
                    for (int j = 0; j < data.Columns.Count; ++j)
                        Console.Write("{0} ", data.Rows[i][j]);
                    Console.Write("
    ");
                }
            }
    
            static void TestExcelWrite(string file)
            {
                try
                {
                    using (ExcelHelper excelHelper = new ExcelHelper(file))
                    {
                        DataTable data = GenerateData();
                        int count = excelHelper.DataTableToExcel(data, "MySheet", true);
                        if (count > 0)
                            Console.WriteLine("Number of imported data is {0} ", count);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
    
            static void TestExcelRead(string file)
            {
                try
                {
                    using (ExcelHelper excelHelper = new ExcelHelper(file))
                    {
                        DataTable dt = excelHelper.ExcelToDataTable("MySheet", true);
                        PrintData(dt);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
    
            static void Main(string[] args)
            {
                string file = "..\..\myTest.xlsx";
                TestExcelWrite(file);
                TestExcelRead(file);
            }
        }
    }
    View Code

    1.NPOI下载地址:http://npoi.codeplex.com/releases/view/38113

    2.NPOI学习系列教程推荐:http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

    参考:

    http://www.cnblogs.com/Erik_Xu/archive/2012/06/08/2541957.html

    http://www.cnblogs.com/linzheng/archive/2010/12/20/1912137.html

    http://www.cnblogs.com/knowledgesea/archive/2012/11/16/2772547.html

  • 相关阅读:
    FileUpload1上传控件
    docker如何push镜像到docker hub个人的仓库
    docker的ubuntu镜像无ifconfig和ping命令
    keystone同步数据库的时候提示error
    openstack安装dashboard后访问horizon出错 500 or 504
    装了ubuntu之后,只能进入ubuntu系统,不能进入windows系统
    Kernal Panic
    无法获得锁 /var/lib/dpkg/lock -open
    用户 'NT AUTHORITYIUSR' 登录失败
    配置错误:不能在此路径中使用此配置节。
  • 原文地址:https://www.cnblogs.com/gldblogs/p/4138012.html
Copyright © 2020-2023  润新知