使用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");
}
｝
posted on 2014-01-16 15:30 aolinwxfx 阅读(2285) 评论(1) 编辑 收藏 举报
