//
- HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
- XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.HPSF;
using NPOI.SS.Util;
namespace NPOI
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
#region 写
//xls
HSSFWorkbook wk = new HSSFWorkbook();
//表
ISheet tb = wk.CreateSheet("QWER");
for (int i = 0; i < 20; i++)
{
IRow row = tb.CreateRow(i);
row.Height = 200 * 2;
for (int j = 0; j < 5; j++)
{
ICell cell = row.CreateCell(j);
switch (j)
{
case 0:
HSSFCellStyle styleF = (HSSFCellStyle)wk.CreateCellStyle();
HSSFFont font = (HSSFFont)wk.CreateFont();
font.FontName = "宋体";//字体
font.FontHeightInPoints = 14;//字号
font.Color = HSSFColor.PaleBlue.Index;//颜色
//font.Underline = NPOI.SS.UserModel.FontUnderlineType.Double;//下划线
//font.IsStrikeout = true;//删除线
//font.IsItalic = true;//斜体
styleF.SetFont(font);
cell.CellStyle = styleF;
cell.SetCellValue("q");
tb.SetColumnWidth(j, 10 * 256);
break;
case 1:
ICellStyle colorstyle = wk.CreateCellStyle();
colorstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
colorstyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = colorstyle;
cell.SetCellValue("w");
tb.SetColumnWidth(j, 20 * 256);
break;
case 2:
cell.SetCellValue("e");
tb.SetColumnWidth(j, 30 * 256);
break;
case 3:
cell.SetCellValue("r");
tb.SetColumnWidth(j, 40 * 256);
break;
case 4:
cell.SetCellValue("t");
tb.SetColumnWidth(j, 50 * 256);
break;
}
}
}
string path = "E://项目//练习//" + DateTime.Now.ToString("yyyyMMddHHmms") + ".xls";
using (FileStream fs = File.OpenWrite(path))
{
wk.Write(fs);
MessageBox.Show("创建成功");
}
#endregion
}
private void button2_Click(object sender, EventArgs e)
{
#region 读
StringBuilder sbr = new StringBuilder();
using (FileStream fs = File.OpenRead(@"d:/myxls.xls")) //打开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()); //获取表格中的数据并转换为字符串类型
}
}
}
}
}
}
#endregion
}
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(@"d:/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.OliveGreen.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.OliveGreen.Blue.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.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
private void button4_Click(object sender, EventArgs e)
{
}
}
}
