使用NPOI操作Excel文件及其日期处理

工作中经常遇到需要读取或导出Excel文件的情况，而NPOI是目前最宜用、效率最高的操作的Office（不只是Excel哟）文件的组件，使用方便，不详细说明了。

Excel工作表约定：整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

1、先上一个Excel2003的例子，代码如下：

//读取xls文件         private void button2_Click(object sender, EventArgs e)         {   StringBuilder sbr = new StringBuilder();             using (FileStream fs = File.OpenRead(@"c:/myxls.xls"))   //打开myxls.xls文件             {                 HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中                 for (int i = 0; i < wk.NumberOfSheets; i++)  //NumberOfSheets是myxls.xls中总共的表数                 {                     ISheet sheet = wk.GetSheetAt(i);   //读取当前表数据                     for (int j = 0; j <= sheet.LastRowNum; j++)  //LastRowNum 是当前表的总行数                     {                         IRow row = sheet.GetRow(j);  //读取当前行数据                         if (row != null)                         {                             sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限                             for (int k = 0; k <= row.LastCellNum; k++)  //LastCellNum 是当前行的总列数                             {                                 ICell cell = row.GetCell(k);  //当前表格                                 if (cell != null)                                 {                                                                        sbr.Append(cell.ToString());   //获取表格中的数据并转换为字符串类型                                 }                             }                         }                     }                 }                            }             sbr.ToString();             using (StreamWriter wr = new StreamWriter(new FileStream(@"c:/myText.txt", FileMode.Append)))  //把读取xls文件的数据写入myText.txt文件中             {                 wr.Write(sbr.ToString());                 wr.Flush();             }         }

 

//创建一个常用的xls文件         private void button3_Click(object sender, EventArgs e)         {                       IWorkbook wb = new HSSFWorkbook();             //创建表               ISheet sh = wb.CreateSheet("zhiyuan");             //设置单元的宽度               sh.SetColumnWidth(0, 15 * 256);             sh.SetColumnWidth(1, 35 * 256);             sh.SetColumnWidth(2, 15 * 256);             sh.SetColumnWidth(3, 10 * 256);             int i = 0;             #region 练习合并单元格             sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));

            //CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。             IRow row0 = sh.CreateRow(0);             row0.Height = 20 * 20;             ICell icell1top0 = row0.CreateCell(0);             icell1top0.CellStyle = Getcellstyle(wb, stylexls.头);             icell1top0.SetCellValue("标题合并单元");             #endregion             i++;             #region 设置表头             IRow row1 = sh.CreateRow(1);             row1.Height = 20 * 20;

            ICell icell1top = row1.CreateCell(0);             icell1top.CellStyle = Getcellstyle(wb, stylexls.头);             icell1top.SetCellValue("网站名");

            ICell icell2top = row1.CreateCell(1);             icell2top.CellStyle = Getcellstyle(wb, stylexls.头);             icell2top.SetCellValue("网址");

            ICell icell3top = row1.CreateCell(2);             icell3top.CellStyle = Getcellstyle(wb, stylexls.头);             icell3top.SetCellValue("百度快照");

            ICell icell4top = row1.CreateCell(3);             icell4top.CellStyle = Getcellstyle(wb, stylexls.头);             icell4top.SetCellValue("百度收录");             #endregion               using(FileStream stm=File.OpenWrite(@"c:/myMergeCell.xls"))             {                 wb.Write(stm);                  MessageBox.Show("提示：创建成功！");             }         } 

        #region 定义单元格常用到样式的枚举         public enum stylexls         {             头,             url,             时间,             数字,             钱,             百分比,             中文大写,             科学计数法,             默认         }         #endregion

        #region 定义单元格常用到样式         static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)         {             ICellStyle cellStyle = wb.CreateCellStyle();

            //定义几种字体               //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的               IFont font12 = wb.CreateFont();             font12.FontHeightInPoints = 10;             font12.FontName = "微软雅黑";

            IFont font = wb.CreateFont();             font.FontName = "微软雅黑";             //font.Underline = 1;下划线 

            IFont fontcolorblue = wb.CreateFont();             fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;             fontcolorblue.IsItalic = true;//下划线               fontcolorblue.FontName = "微软雅黑";

 

            //边框               cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;             cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;             cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;             cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;             //边框颜色               cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;             cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;

            //背景图形，我没有用到过。感觉很丑               //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;               //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;               cellStyle.FillForegroundColor = HSSFColor.WHITE.index;             // cellStyle.FillPattern = FillPatternType.NO_FILL;               cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;

            //水平对齐               cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;

            //垂直对齐               cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

            //自动换行               cellStyle.WrapText = true;

            //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对               cellStyle.Indention = 0;

            //上面基本都是设共公的设置  

            //下面列出了常用的字段类型               switch (str)             {                 case stylexls.头:                     // cellStyle.FillPattern = FillPatternType.LEAST_DOTS;                       cellStyle.SetFont(font12);                     break;                 case stylexls.时间:                     IDataFormat datastyle = wb.CreateDataFormat();

                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");                     cellStyle.SetFont(font);                     break;                 case stylexls.数字:                     cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");                     cellStyle.SetFont(font);                     break;                 case stylexls.钱:                     IDataFormat format = wb.CreateDataFormat();                     cellStyle.DataFormat = format.GetFormat("￥#,##0");                     cellStyle.SetFont(font);                     break;                 case stylexls.url:                     fontcolorblue.Underline = 1;                     cellStyle.SetFont(fontcolorblue);                     break;                 case stylexls.百分比:                     cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");                     cellStyle.SetFont(font);                     break;                 case stylexls.中文大写:                     IDataFormat format1 = wb.CreateDataFormat();                     cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");                     cellStyle.SetFont(font);                     break;                 case stylexls.科学计数法:                     cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");                     cellStyle.SetFont(font);                     break;                 case stylexls.默认:                     cellStyle.SetFont(font);                     break;             }             return cellStyle;

        }         #endregion

 

2、再来一个Excel2007的并绑定到GridView中例子，代码如下：

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.IO;

using NPOI.SS.UserModel;

using NPOI.HSSF.Util;

using NPOI.HSSF.UserModel;

using NPOI.XSSF.UserModel;

/// <summary>

///NPOI操作Excel，这个例子是针对Excel 2007. 

/// </summary>

public class ExcelHelper{

    IWorkbook hssfworkbook;

    public DataTable ImportExcelFile(string filePath)

    {

        #region//初始化信息

        try

        {

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))

            {

                hssfworkbook = new XSSFWorkbook(file);

            }

        }

        catch (Exception e)

        {

            throw e;

        }

        #endregion

 

        ISheet sheet = hssfworkbook.GetSheetAt(0);

        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

 

        DataTable dt = new DataTable();

        //一行最后一个方格的编号 即总的列数

        for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)

        {

            dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());

        }

 

        while (rows.MoveNext())

        {

            IRow row = (XSSFRow)rows.Current;

            DataRow dr = dt.NewRow();

            for (int i = 0; i < row.LastCellNum; i++)

            {

                ICell cell = row.GetCell(i);

                if (cell == null)

                {

                    dr[i] = null;

                }

                else

                {

                    dr[i] = cell.ToString();

                }

            }

            dt.Rows.Add(dr);

        }

        return dt;

    }

    hssfworkbook= null;

    sheet = null; 

}

    //绑定GridView显示只需要两名代码

    this.gvExcel.DataSource = dt;

    this.gvExcel.DataBind();

3、NPOI中的日期处理，NOPI版本为2.0.2。

POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型。

/**

*Excel中的日期格式在解析为DataTable时会依格式不同解析为不同的值，因为所有的日期格式都可以通过下面DataFormat来判断，

*excel2013，测试结果如下：*时分(HH:mm) - 20，时间(HH:mm:ss) - 21，日期时间(yyyy-MM-dd HH:mm:ss) - 22，

*年月(yyyy-MM) - 17，年月日(yyyy-MM-dd)-14，yyyy年m月-------57，月日(MM-dd) - 58，

*yyyy年m月d日---31，h时mm分  -------32

* */

 if (cell.CellType == CellType.NUMERIC)

 {

      short format = cell.CellStyle.DataFormat;

      if (format == 14 || format == 31 || format == 57 || format == 58)

      {

          DateTime date = cell.DateCellValue;

          string re = date.ToString("yyy-MM-dd");

       }

｝