excel npoi 连接_使用Npoi操作excel的解决办法

//创建一个常用的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