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

2012-06-24

NPOI.dll 用法。单元格，样式，字体，颜色，行高，宽度。读写excel我用的版本是1.25的。每个版本用法有一点不同usi

NPOI.dll 用法。单元格，样式，字体，颜色，行高，宽度。读写excel
我用的版本是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 { //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;                }            }        }    }}

热点排行

编程

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