我用的版本是1.25的。每个版本用法有一点不同 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
{
public Form1()
{
InitializeComponent();
}
//http://tonyqus.sinaapp.com/page/4
static ICellStyle Getcellstyle(IWorkbook wb, string str)
{
ICellStyle cellStyle = wb.CreateCellStyle(); //字体
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = ; 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; //缩进
cellStyle.Indention = ;
switch (str)
{
case "头":
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case "时间":
IDataFormat datastyle = wb.CreateDataFormat(); cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case "数字":
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case "钱":
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case "url":
fontcolorblue.Underline = ;
cellStyle.SetFont(fontcolorblue);
break;
case "百分比":
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case "中文大写":
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case "科学计数法":
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case "默认":
cellStyle.SetFont(font);
break;
}
return cellStyle; }
private void btnwrite_Click(object sender, EventArgs e)
{
//创建数据库
IWorkbook wb = new HSSFWorkbook(); //创建表
ISheet sh = wb.CreateSheet("zhiyuan");
// sh.TabColorIndex = HSSFColor.RED.index; //设置单元的宽度
sh.SetColumnWidth(, * );
sh.SetColumnWidth(, * );
sh.SetColumnWidth(, * );
sh.SetColumnWidth(, * ); //读取数据库写入表
string sql = "select top 100 urlnam,url,bdtim,bdsl from zhiyuan";
int i = ; //合并单元格
sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));
IRow row0 = sh.CreateRow();
row0.Height = * ;
ICell icell1top0 = row0.CreateCell();
ICell icell1top1 = row0.CreateCell();
ICell icell1top2 = row0.CreateCell();
ICell icell1top3 = row0.CreateCell();
icell1top0.CellStyle = Getcellstyle(wb, "头");
icell1top0.SetCellValue("标题合并单元"); IRow row1 = sh.CreateRow();
row1.Height = * ; ICell icell1top = row1.CreateCell();
icell1top.CellStyle = Getcellstyle(wb, "头");
icell1top.SetCellValue("网站名"); ICell icell2top = row1.CreateCell();
icell2top.CellStyle = Getcellstyle(wb, "头");
icell2top.SetCellValue("网址"); ICell icell3top = row1.CreateCell();
icell3top.CellStyle = Getcellstyle(wb, "头");
icell3top.SetCellValue("百度快照"); ICell icell4top = row1.CreateCell();
icell4top.CellStyle = Getcellstyle(wb, "头");
icell4top.SetCellValue("百度收录"); i++;
i++; using (SqlDataReader dr = SqlHelper.ExecuteReaderText(sql, null))
{
if (dr.HasRows)
{
while (dr.Read())
{
IRow row = sh.CreateRow(i);
row.Height = * ; ICell icell = row.CreateCell();
icell.CellStyle = Getcellstyle(wb, "默认");
icell.SetCellValue(dr.GetValue().ToString()); ICell icell1 = row.CreateCell();
icell1.CellStyle = Getcellstyle(wb, "url");
icell1.SetCellValue(dr.GetValue().ToString());
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address = (dr.GetValue().ToString());
icell1.Hyperlink = (link); ICell icell2 = row.CreateCell();
icell2.CellStyle = Getcellstyle(wb, "时间");
icell2.SetCellValue(dr.IsDBNull() ? Convert.ToDateTime("1990-1-1") : dr.GetDateTime()); ICell icell3 = row.CreateCell();
icell3.CellStyle = Getcellstyle(wb, "默认");
icell3.SetCellValue(dr.IsDBNull() ? : dr.GetInt32());
i++;
}
}
}
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) ? : dr.GetInt32(j));
}
else if (dr.GetFieldType(j).Name == "Int16")
{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? : dr.GetInt16(j)); }
else if (dr.GetFieldType(j).Name == "Int64")
{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? : 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) ? : dr.GetDouble(j)); }
else if (dr.GetFieldType(j).Name == "Byte[]")
{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? : dr.GetByte(j)); }
else if (dr.GetFieldType(j).Name == "Decimal")
{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? : dr.GetDouble(j)); }
else
{ row.CreateCell(j).SetCellValue(dr.IsDBNull(j) ? "" : dr.GetValue(j).ToString());
} } 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 = ; i < wk.NumberOfSheets; i++)
{
//申明表
ISheet wk1 = wk.GetSheetAt(i); txtout.AppendText("====================" + wk1.SheetName + "================\r\n"); //获取表的行
for (int j = ; j < wk1.LastRowNum + ; j++)
{
//申明行
IRow row = wk1.GetRow(j);
for (int k = ; k < row.LastCellNum + ; k++)
{
txtout.AppendText(string.Format("{0}\t", row.GetCell(k) == null ? "" : row.GetCell(k).ToString()));
}
txtout.AppendText("\r\n");
}
} }
} } //把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,,b.Length);
}
}
}
} } 生成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().LastCellNum;
int colSpan;
int rowSpan;
bool isByRowMerged;
StringBuilder table = new StringBuilder(rowsCount * );
table.Append("<table border='1px'>");
for (int rowIndex = ; rowIndex < rowsCount; rowIndex++)
{
table.Append("<tr>");
for (int colIndex = ; colIndex < colsCount; colIndex++)
{
GetTdMergedInfo(rowIndex, colIndex, out colSpan, out rowSpan, out isByRowMerged);
//如果已经被行合并包含进去了就不输出TD了。
//注意被合并的行或列不输出的处理方式不一样，见下面一处的注释说明了列合并后不输出TD的处理方式。
if (isByRowMerged)
{
continue;
}
table.Append("<td");
if (colSpan > )
table.Append(string.Format(" colSpan={0}", colSpan));
if (rowSpan > )
table.Append(string.Format(" rowSpan={0}", rowSpan));
table.Append(">");
table.Append(sht.GetRow(rowIndex).GetCell(colIndex));
//列被合并之后此行将少输出colSpan-1个TD。
if (colSpan > )
colIndex += colSpan - ;
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 = ;
rowspan = ;
isByRowMerged = false;
int regionsCuont = sht.NumMergedRegions; NPOI.SS.Util.CellRangeAddress region; for (int i = ; i < regionsCuont; i++)
{ region = sht.GetMergedRegion(i); if (region.FirstRow == rowIndex && region.FirstColumn == colIndex)
{
colspan = region.LastColumn - region.FirstColumn + ;
rowspan = region.LastRow - region.FirstRow + ;
return;
}
else if (rowIndex > region.FirstRow && rowIndex <= region.LastRow && colIndex >= region.FirstColumn && colIndex <= region.LastColumn)
{
isByRowMerged = true;
}
}
}
}
}