NPOI.dll 用法。单元格，样式，字体，颜色，行高，宽度。读写excel
转载:http://yuncode.net/code/c_531e679b3896495
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;
|
}
|
}
|
}
|
}
|
} |