NPOI.dll 用法。单元格，样式，字体，颜色，行高，宽度。读写excel

1.25 NPOI.dll

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.POIFS.FileSystem;

using NPOI.HSSF.Util;

using NPOI.SS.UserModel;

using System.IO;

using SqlHelPerXHC;

using NPOI.HSSF.Record.CF;

namespace Excl

{

public partial class Form1 : Form

{

//http://tonyqus.sinaapp.com/page/4  官网使用说明

public Form1()

{

InitializeComponent();

}

#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.MAROON.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

//从数据库读取数据写入到excel中

private void btnwrite_Click(object sender, EventArgs e)

{

#region 创建数据库,表，设置单元的宽度

//创建数据库

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);

#endregion

int i = 0;

#region 练习合并单元格

sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));

IRow row0 = sh.CreateRow(0);

row0.Height = 20 * 20;

ICell icell1top0 = row0.CreateCell(0);

ICell icell1top1 = row0.CreateCell(1);

ICell icell1top2 = row0.CreateCell(2);

ICell icell1top3 = row0.CreateCell(3);

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

i++;

#region 读取数据库写入表

string sql = "select top 100 urlnam,url,bdtim,bdsl from zhiyuan";

using (SqlDataReader dr = SqlHelper.ExecuteReaderText(sql, null))

{

if (dr.HasRows)

{

while (dr.Read())

{

//创建行

IRow row = sh.CreateRow(i);

row.Height = 18 * 20;

//创建第1列

ICell icell = row.CreateCell(0);

icell.CellStyle = Getcellstyle(wb, stylexls.默认);

icell.SetCellValue(dr.GetValue(0).ToString());

//创建第2列

ICell icell1 = row.CreateCell(1);

icell1.CellStyle = Getcellstyle(wb, stylexls.url);

icell1.SetCellValue(dr.GetValue(1).ToString());

HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);

link.Address = (dr.GetValue(1).ToString());

icell1.Hyperlink = (link);

//创建第3列

ICell icell2 = row.CreateCell(2);

icell2.CellStyle = Getcellstyle(wb, stylexls.时间);

icell2.SetCellValue(dr.IsDBNull(2) ? Convert.ToDateTime("1990-1-1") : dr.GetDateTime(2));

//创建第4列

ICell icell3 = row.CreateCell(3);

icell3.CellStyle = Getcellstyle(wb, stylexls.默认);

icell3.SetCellValue(dr.IsDBNull(3) ? 0 : dr.GetInt32(3));

i++;

}

}

}

#endregion

using (FileStream fs = File.OpenWrite("xxx.xls"))

{

wb.Write(fs);

MessageBox.Show("Excel已经写入成功！");

}

}

//这个函数可以不看。

private void CreateRow(IRow row, int j, SqlDataReader dr, ICellStyle cellstyle)

{

if (dr.GetFieldType(j).Name == "Int32")

{

row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetInt32(j));

}

else if (dr.GetFieldType(j).Name == "Int16")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetInt16(j)); }

else if (dr.GetFieldType(j).Name == "Int64")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetInt64(j)); }

else if (dr.GetFieldType(j).Name == "String")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? "" : dr.GetString(j)); }

else if (dr.GetFieldType(j).Name == "DateTime")

{

ICell cell = row.CreateCell(j);

cell.CellStyle = cellstyle;

cell.SetCellValue(dr.IsDBNull(j) ? Convert.ToDateTime("1990-1-1") : dr.GetDateTime(j));

}

else if (dr.GetFieldType(j).Name == "Double")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetDouble(j)); }

else if (dr.GetFieldType(j).Name == "Byte[]")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetByte(j)); }

else if (dr.GetFieldType(j).Name == "Decimal")

{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? 0 : dr.GetDouble(j)); }

else

{

row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? "" : dr.GetValue(j).ToString());

}

}

#region 读取excel

private void btnreade_Click(object sender, EventArgs e)

{

//先创建文件流

if (DialogResult.OK == openFileDialog1.ShowDialog())

{

using (FileStream fs = File.OpenRead(openFileDialog1.FileName))

{

//申明数据库对像

IWorkbook wk = new HSSFWorkbook(fs);

//获取数据库中的每个表

for (int i = 0; i < wk.NumberOfSheets; i++)

{

//申明表

ISheet wk1 = wk.GetSheetAt(i);

txtout.AppendText("====================" + wk1.SheetName + "================\r\n");

//获取表的行

for (int j = 0; j < wk1.LastRowNum + 1; j++)

{

//申明行

IRow row = wk1.GetRow(j);

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

{

txtout.AppendText(string.Format("{0}\t", row.GetCell(k) == null ? "" : row.GetCell(k).ToString()));

}

txtout.AppendText("\r\n");

}

}

}

}

}

#endregion

#region 把excel转成htm

private void button1_Click(object sender, EventArgs e)

{

if (DialogResult.OK == openFileDialog1.ShowDialog())

{

string str = htmlxsl.Gethtmlxls(openFileDialog1.FileName);

using (FileStream fs = File.OpenWrite("1.htm"))

{

byte[] b = Encoding.Default.GetBytes(str);

fs.Write(b, 0, b.Length);

}

}

}

#endregion

}

}

生成htm的类

using System.Text;

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.POIFS.FileSystem;

using NPOI.HSSF.Util;

using NPOI.SS.UserModel;

using System.IO;

using SqlHelPerXHC;

using NPOI.HSSF.Record.CF;

namespace Excl

{

public static class htmlxsl

{

private static ISheet sht;

public static string Gethtmlxls(string path)

{

IWorkbook wb = new HSSFWorkbook(new FileStream(path, FileMode.Open));

sht = wb.GetSheet("zhiyuan");

//取行Excel的最大行数

int rowsCount = sht.LastRowNum;

//为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。

//为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。

int colsCount = sht.GetRow(0).LastCellNum;

int colSpan;

int rowSpan;

bool isByRowMerged;

StringBuilder table = new StringBuilder(rowsCount * 32);

table.Append("<table border='1px'>");

for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)

{

table.Append("<tr>");

for (int colIndex = 0; colIndex < colsCount; colIndex++)

{

GetTdMergedInfo(rowIndex, colIndex, out colSpan, out rowSpan, out isByRowMerged);

//如果已经被行合并包含进去了就不输出TD了。

//注意被合并的行或列不输出的处理方式不一样，见下面一处的注释说明了列合并后不输出TD的处理方式。

if (isByRowMerged)

{

continue;

}

table.Append("<td");

if (colSpan > 1)

table.Append(string.Format(" colSpan={0}", colSpan));

if (rowSpan > 1)

table.Append(string.Format(" rowSpan={0}", rowSpan));

table.Append(">");

table.Append(sht.GetRow(rowIndex).GetCell(colIndex));

//列被合并之后此行将少输出colSpan-1个TD。

if (colSpan > 1)

colIndex += colSpan - 1;

table.Append("</td>");

}

table.Append("</tr>");

}

table.Append("</table>");

return table.ToString();

}

/// <summary>

///  获取Table某个TD合并的列数和行数等信息。与Excel中对应Cell的合并行数和列数一致。

/// </summary>

/// <param name="rowIndex">行号</param>

/// <param name="colIndex">列号</param>

///  <param name="colspan">TD中需要合并的行数</param>

/// <param name="rowspan">TD中需要合并的列数</param>

/// <param name="rowspan">此单元格是否被某个行合并包含在内。如果被包含在内，将不输出TD。</param>

/// <returns></returns>

private static void GetTdMergedInfo(int rowIndex, int colIndex, out int colspan, out int rowspan, out bool isByRowMerged)

{

colspan = 1;

rowspan = 1;

isByRowMerged = false;

int regionsCuont = sht.NumMergedRegions;

NPOI.SS.Util.CellRangeAddress region;

for (int i = 0; i < regionsCuont; i++)

{

region = sht.GetMergedRegion(i);

if (region.FirstRow == rowIndex && region.FirstColumn == colIndex)

{

colspan = region.LastColumn - region.FirstColumn + 1;

rowspan = region.LastRow - region.FirstRow + 1;

return;

}

else if (rowIndex > region.FirstRow && rowIndex <= region.LastRow && colIndex >= region.FirstColumn && colIndex <= region.LastColumn)

{

isByRowMerged = true;

}

}

}

}

}