1.25 NPOI.dllusing 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;                }            }        }    }}