蜘蛛资讯网 > 暗夜游侠 > 爱情连连看

佩奇_利用NPOI将EXCEL转换成HTML的C#实现

领导说想做一个网页打印功能,而且模板可以自定义,我考虑了三个方案,一是打印插件,二是在线 html 编辑器,三是 excel 模板,领导建议用的是打印插件的形式,我研究了一下,一个是需要下载安装,二个是模板定义其实也相当不方便,所以我想采用后两种,而在线? html 编辑器的话,直接画出来的并不真的是所见即所得,打印效果肯定需要不停的去调整,而直接 html 代码呢,对客户的要求又比较高(不可否认,很多客户都不知道 html 是什么玩意儿),所以最后选择了 excel 形式,搜了一下 npoi 官网,发现一个 java 版的 html 导出,于是辛苦了一下,把它改造成了 c# 的,在此过种中发现Java版本的没有处理合并单元格,且字体相对较大,我对此进行了一点改进,另外发现了两个NPOI的BUG,因为时间关系,也就没有去弄NPOI的源码了,等我有空了再来解决这两个BUG吧, 代码注释不多,需要看注释的直接去看 java版本的即可。

贴上效果图:

?

using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Format;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace ExcelUtility
{
    public class EXCELTOHTML 
    {
        private IWorkbook wb = null;

        private const String DEFAULTS_CLASS = "excelDefaults";
        private const String COL_HEAD_CLASS = "colHeader";
        private const String ROW_HEAD_CLASS = "rowHeader";

        private const int IDX_TABLE_WIDTH = -2;
        private const int IDX_HEADER_COL_WIDTH = -1;


        private int firstColumn;
        private int endColumn;

        private bool gotBounds;

        private List<>> HALIGN = new List<>>() {
            new KeyValuePair(HorizontalAlignment.Left, "left"),
            new KeyValuePair(HorizontalAlignment.Center, "center"),
            new KeyValuePair(HorizontalAlignment.Right, "right"),
            new KeyValuePair(HorizontalAlignment.Fill, "left"),
            new KeyValuePair(HorizontalAlignment.Justify, "left"),
            new KeyValuePair(HorizontalAlignment.CenterSelection, "center"),
            new KeyValuePair(HorizontalAlignment.General, "left")
        };

        private List<>> VALIGN = new List<>>() {
            new KeyValuePair(VerticalAlignment.Bottom, "bottom"),
            new KeyValuePair(VerticalAlignment.Center, "middle"),
            new KeyValuePair(VerticalAlignment.Top, "top")
        };

        private List<>> BORDER = new List<>>() {
            new KeyValuePair(BorderStyle.DashDot, "dashed 1pt"),
            new KeyValuePair(BorderStyle.DashDotDot, "dashed 1pt"),
            new KeyValuePair(BorderStyle.Dashed, "dashed 1pt"),
            new KeyValuePair(BorderStyle.Dotted, "dotted 1pt"),
            new KeyValuePair(BorderStyle.Double, "double 3pt"),
            new KeyValuePair(BorderStyle.Hair, "dashed 1px"),
            new KeyValuePair(BorderStyle.Medium, "solid 2pt"),
            new KeyValuePair(BorderStyle.MediumDashDot, "dashed 2pt"),
            new KeyValuePair(BorderStyle.MediumDashDotDot, "dashed 2pt"),
            new KeyValuePair(BorderStyle.MediumDashed, "dashed 2pt"),
            new KeyValuePair(BorderStyle.None, "none"),
            new KeyValuePair(BorderStyle.SlantedDashDot, "dashed 2pt"),
            new KeyValuePair(BorderStyle.Thick, "solid 3pt"),
            new KeyValuePair(BorderStyle.Thin, "solid 1pt")
        };

        public EXCELTOHTML(IWorkbook wb)
        {
            this.wb = wb;
        }

        public EXCELTOHTML(string path)
        {
            using (var inputfs = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                NPOIFSFileSystem fs = new NPOIFSFileSystem(inputfs);
                this.wb = new HSSFWorkbook(fs.Root, true);
            }
        }

        public string ToHtml(int sheetIndex = 0, bool completeHtmls = true, bool needTitle = true)
        {
            return ToHtml(wb.GetSheetName(sheetIndex), completeHtmls, needTitle);
        }

        public string ToHtml(string sheetName, bool completeHtmls = true, bool needTitle = true)
        {
            StringBuilder sbRet = new StringBuilder();

            if (completeHtmls)
            {
                sbRet.Append("
");
                sbRet.Append("
");
                sbRet.Append("
");
            }
            sbRet.Append(GetInlineStyle());
            if (completeHtmls)
            {
                sbRet.Append("
");
                sbRet.Append("
");
            }
            sbRet.Append(GetSheets(sheetName, needTitle));
            if (completeHtmls)
            {
                sbRet.Append("
");
                sbRet.Append("
");
            }

            return sbRet.ToString();
        }

        private string GetSheets(string sheetName, bool needTitle)
        {
            StringBuilder sbRet = new StringBuilder();

            ISheet sheet = wb.GetSheet(sheetName);
            sbRet.Append(GetSheet(sheet, needTitle));

            return sbRet.ToString();
        }

        private string GetSheet(ISheet sheet, bool needTitle)
        {
            StringBuilder sbRet = new StringBuilder();

            List<>> widths = computeWidths(sheet);
            int tableWidth = widths.Where(o => o.Key == IDX_TABLE_WIDTH).First().Value;
            sbRet.Append(string.Format("
", DEFAULTS_CLASS, tableWidth));
            sbRet.Append(GetCols(widths, needTitle));
            sbRet.Append(GetSheetContent(sheet, needTitle));
            sbRet.Append("
"); return sbRet.ToString(); } private string GetColumnHeads() { StringBuilder sbRet = new StringBuilder(); sbRet.Append(string.Format(" ")); sbRet.Append(string.Format(" ", COL_HEAD_CLASS)); sbRet.Append(string.Format(" ? ", COL_HEAD_CLASS)); //noinspection UnusedDeclaration for (int i = firstColumn; i < endColumn; i++) { StringBuilder colName = new StringBuilder(); int cnum = i; do { colName.Insert(0, (char)("A" + cnum % 26)); cnum /= 26; } while (cnum > 0); sbRet.Append(string.Format(" {1} ", COL_HEAD_CLASS, colName)); } sbRet.Append(" "); sbRet.Append(" "); return sbRet.ToString(); } private string GetSheetContent(ISheet sheet, bool needTitle) { StringBuilder sbRet = new StringBuilder(); if (needTitle) { sbRet.Append(GetColumnHeads()); } sbRet.Append(string.Format(" ")); IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { IRow row = (IRow)rows.Current; sbRet.Append(string.Format(" ")); if (needTitle) { sbRet.Append(string.Format(" {1} ", ROW_HEAD_CLASS, row.RowNum + 1)); } StringBuilder sbTemp = new StringBuilder(); int mergeCnt = 0; ICell preCell = null; ICell cell = null; for (int i = firstColumn; i < endColumn; i++) { String content = "?"; String attrs = ""; ICellStyle style = null; bool isMerge = false; if (i >= row.FirstCellNum && i < row.LastCellNum) { cell = row.GetCell(i); if (cell != null) { isMerge = cell.IsMergedCell; style = cell.CellStyle; attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format MyCellFormat cf = MyCellFormat.GetInstance(style.GetDataFormatString()); CellFormatResult result = cf.Apply(cell); content = result.Text; //never null if (string.IsNullOrEmpty(content)) { content = "?"; } } } if (isMerge == true && content == "?") { /* * 因为 NPOI 返回的 cell 没有 mergeCnt 属性,只有一个 IsMergedCell 属性 * 如果有5个单元格,后面四个单元格合并成一个大单元格 * 它返回的其实还是5个单元格,IsMergedCell 分别是: false,true,true,true,true * 上头这种情况还算好,我们好歹还能猜到后面四个单元格是合并单元格 * * 但是如果第一个单独,后面四个每两个合并呢? * TMD返回的还是5个单元格,IsMergedCell 仍然是: false,true,true,true,true * 所以这里是有问题的,我没法知道后面的四个单元格是四个合并成一个呢,还是两个两个的分别合并 * 这个是没办法的,除非从NPOI的源代码里头去解决这个问题,介于上班呢,要求的是出结果,所以公司是 * 不太会允许我去干这种投入产出比较差的事情的,所以这个问题我采用了一个成本比较低的办法来绕开 * * 办法就是我们在定义模板的时候,可以通过为每一个合并单元格添加内容来避免。 * 比如说 cell1(内容), cell2,cell3(内容), cell4,cell5(内容) * 这样的话我就能知道 cell1 IsMergedCell = false 是一个独立的单元格 * cell2, cell3, cell4, cell5 的 IsMergedCell 虽然都是 true, 但是因为 cell4 这个位置有内容了, * 那我就晓得 cell2 和 cell3 是合并的, cell4 和 cell5 也是合并的。 * * 当然这里还会有个小小的问题,如果 cell4, cell5 里头是一个会被替换掉的内容,也即 $[字段] 这样的东西 * 如果实际的内容为 null 那么 cell4, cell5 合并单元格的内容也就是 null 了,这又回到了之前的问题了, * 所以此处要求定义模板的时候 $[内容] 后面加一个空格,这样在生成 html 的时候,其实是不影响打印效果的。 * 也即 “$[] ”注意双引号里头的 “]”后头有个空格 */ if (mergeCnt == 1 && preCell != null && preCell.IsMergedCell == false) { sbTemp.Append(string.Format(" {2} ", styleName(style), attrs, content, (isMerge) ? " colspan="1"" : "")); } else { mergeCnt++; } } else { sbTemp.Replace("colspan="1"", string.Format("colspan="{0}"", mergeCnt)); mergeCnt = 1; sbTemp.Append(string.Format(" {2} ", styleName(style), attrs, content, (isMerge) ? " colspan="1"" : "")); } preCell = cell; } sbRet.Append(sbTemp.Replace("colspan="1"", string.Format("colspan="{0}"", mergeCnt)).ToString()); sbRet.Append(string.Format(" ")); } sbRet.Append(string.Format(" ")); return sbRet.ToString(); } private String tagStyle(ICell cell, ICellStyle style) { if (style.Alignment == HorizontalAlignment.General) { switch (ultimateCellType(cell)) { case CellType.String: return "style="text-align: left;""; case CellType.Boolean: case CellType.Error: return "style="text-align: center;""; case CellType.Numeric: default: // "right" is the default break; } } return ""; } private static CellType ultimateCellType(ICell c) { CellType type = c.CellType; if (type == CellType.Formula) { type = c.CachedFormulaResultType; } return type; } private string GetCols(List<>> widths, bool needTitle) { StringBuilder sbRet = new StringBuilder(); if (needTitle) { int headerColWidth = widths.Where(o => o.Key == IDX_HEADER_COL_WIDTH).First().Value; sbRet.Append(string.Format(" ", headerColWidth)); } for (int i = firstColumn; i < endColumn; i++) { int colWidth = widths.Where(o => o.Key == i).First().Value; sbRet.Append(string.Format(" ", colWidth)); } return sbRet.ToString(); } private List<>> computeWidths(ISheet sheet) { List<>> ret = new List<>>(); int tableWidth = 0; ensureColumnBounds(sheet); // compute width of the header column int lastRowNum = sheet.LastRowNum; int headerCharCount = lastRowNum.ToString().Length; int headerColWidth = widthToPixels((headerCharCount + 1) * 256); ret.Add(new KeyValuePair(IDX_HEADER_COL_WIDTH, headerColWidth)); tableWidth += headerColWidth; for (int i = firstColumn; i < endColumn; i++) { int colWidth = widthToPixels(sheet.GetColumnWidth(i)); ret.Add(new KeyValuePair(i, colWidth)); tableWidth += colWidth; } ret.Add(new KeyValuePair(IDX_TABLE_WIDTH, tableWidth)); return ret; } private int widthToPixels(double widthUnits) { return (int)(Math.Round(widthUnits * 9 / 256)); } private void ensureColumnBounds(ISheet sheet) { if (gotBounds) return; IEnumerator iter = sheet.GetRowEnumerator(); if (iter.MoveNext()) firstColumn = 0; else firstColumn = int.MaxValue; endColumn = 0; iter.Reset(); while (iter.MoveNext()) { IRow row = (IRow)iter.Current; short firstCell = row.FirstCellNum; if (firstCell >= 0) { firstColumn = Math.Min(firstColumn, firstCell); endColumn = Math.Max(endColumn, row.LastCellNum); } } gotBounds = true; } private string GetInlineStyle() { StringBuilder sbRet = new StringBuilder(); sbRet.Append(" "); return sbRet.ToString(); } private string GetStyles() { StringBuilder sbRet = new StringBuilder(); HashSet seen = new HashSet(); for (int i = 0; i < wb.NumberOfSheets; i++) { ISheet sheet = wb.GetSheetAt(i); IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { IRow row = (IRow)rows.Current; foreach (ICell cell in row) { ICellStyle style = cell.CellStyle; if (!seen.Contains(style)) { sbRet.Append(GetStyle(style)); seen.Add(style); } } } } return sbRet.ToString(); } private string GetStyle(ICellStyle style) { StringBuilder sbRet = new StringBuilder(); sbRet.Append(string.Format(".{0} .{1} {{ ", DEFAULTS_CLASS, styleName(style))); sbRet.Append(styleContents(style)); sbRet.Append("} "); return sbRet.ToString(); } private string styleContents(ICellStyle style) { StringBuilder sbRet = new StringBuilder(); sbRet.Append(styleOut("text-align", style.Alignment)); sbRet.Append(styleOut("vertical-align", style.VerticalAlignment)); sbRet.Append(fontStyle(style)); sbRet.Append(borderStyles(style)); sbRet.Append(colorStyles(style)); return sbRet.ToString(); } private string colorStyles(ICellStyle style) { StringBuilder sbRet = new StringBuilder(); //sbRet.Append("还未实现!"); return sbRet.ToString(); } private string borderStyles(ICellStyle style) { StringBuilder sbRet = new StringBuilder(); sbRet.Append(styleOut("border-left", style.BorderLeft)); /* * NPOI有BUG,合并单元格的 border-right 永远都是 None * 我们可以通过设置合并单元格后边那个单元格的左边框的解决 * 如果当前合并单元格已经合并到最后一列了,我们就只能再加一列了,为了不影响打印效果 * 这最后加的这一列在设置好左边框后,需要把宽度设置得很小,比如说0.1这样 */ sbRet.Append(styleOut("border-right", style.BorderRight)); sbRet.Append(styleOut("border-top", style.BorderTop)); sbRet.Append(styleOut("border-bottom", style.BorderBottom)); return sbRet.ToString(); } private string fontStyle(ICellStyle style) { StringBuilder sbRet = new StringBuilder(); IFont font = style.GetFont(wb); if (font.Boldweight == 0) { sbRet.Append(" font-weight: bold; "); } if (font.IsItalic) { sbRet.Append(" font-style: italic; "); } double fontheight = font.FontHeight / 10 - 10; if (fontheight == 9) { //fix for stupid ol Windows fontheight = 10; } sbRet.Append(string.Format(" font-size: {0}pt; ", fontheight)); return sbRet.ToString(); } private string styleOut(string k, HorizontalAlignment p) { return k + ":" + HALIGN.Where(o => o.Key == p).First().Value + "; "; } private string styleOut(string k, VerticalAlignment p) { return k + ":" + VALIGN.Where(o => o.Key == p).First().Value + "; "; } private string styleOut(string k, BorderStyle p) { return k + ":" + BORDER.Where(o => o.Key == p).First().Value + "; "; } private string styleName(ICellStyle style) { if (style == null) { style = wb.GetCellStyleAt((short)0); } StringBuilder sb = new StringBuilder(); sb.Append(string.Format("style_{0}", style.Index)); return sb.ToString(); } } }

  javascript部分:

using NPOI. HSSF. UserModel using NPOI. POIFS. FileSystem using NPOI. SS. Format using NPOI. SS. UserModel using System using System. Collections using System. Collections. Generic using System. IO using System. Linq using System. Text using System. Text. RegularExpressions using System. Web namespace ExcelUtility public class EXCELTOHTML private IWorkbook wb null private const String DEFAULTS_CLASS " excelDefaults" private const String COL_HEAD_CLASS " colHeader" private const String ROW_HEAD_CLASS " rowHeader" private const int IDX_TABLE_WIDTH 2 private const int IDX_HEADER_COL_WIDTH 1 private int firstColumn private int endColumn private bool gotBounds private List lt KeyValuePair lt HorizontalAlignment, string gt gt HALIGN new List lt KeyValuePair lt HorizontalAlignment, string gt gt new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. Left, " left", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. Center, " center", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. Right, " right", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. Fill, " left", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. Justify, " left", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. CenterSelection, " center", new KeyValuePair lt HorizontalAlignment, string gt HorizontalAlignment. General, " left" private List lt KeyValuePair lt VerticalAlignment, string gt gt VALIGN new List lt KeyValuePair lt VerticalAlignment, string gt gt new KeyValuePair lt VerticalAlignment, string gt VerticalAlignment. Bottom, " bottom", new KeyValuePair lt VerticalAlignment, string gt VerticalAlignment. Center, " middle", new KeyValuePair lt VerticalAlignment, string gt VerticalAlignment. Top, " top" private List lt KeyValuePair lt BorderStyle, string gt gt BORDER new List lt KeyValuePair lt BorderStyle, string gt gt new KeyValuePair lt BorderStyle, string gt BorderStyle. DashDot, " dashed 1pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. DashDotDot, " dashed 1pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Dashed, " dashed 1pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Dotted, " dotted 1pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Double, " double 3pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Hair, " dashed 1px", new KeyValuePair lt BorderStyle, string gt BorderStyle. Medium, " solid 2pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. MediumDashDot, " dashed 2pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. MediumDashDotDot, " dashed 2pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. MediumDashed, " dashed 2pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. None, " none", new KeyValuePair lt BorderStyle, string gt BorderStyle. SlantedDashDot, " dashed 2pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Thick, " solid 3pt", new KeyValuePair lt BorderStyle, string gt BorderStyle. Thin, " solid 1pt" public EXCELTOHTML IWorkbook wb this. wb wb public EXCELTOHTML string path using var inputfs new FileStream path, FileMode. Open, FileAccess. Read NPOIFSFileSystem fs new NPOIFSFileSystem inputfs this. wb new HSSFWorkbook fs. Root, true public string ToHtml int sheetIndex 0, bool completeHtmls true, bool needTitle true return ToHtml wb. GetSheetName sheetIndex, completeHtmls, needTitle public string ToHtml string sheetName, bool completeHtmls true, bool needTitle true StringBuilder sbRet new StringBuilder if completeHtmls sbRet. Append" lt? xml version" 1. 0" encoding" iso88591" ? gt" sbRet. Append" lt html gt" sbRet. Append" lt head gt" sbRet. Append GetInlineStyle if completeHtmls sbRet. Append" lt head gt" sbRet. Append" lt body gt" sbRet. Append GetSheets sheetName, needTitle if completeHtmls sbRet. Append" lt body gt" sbRet. Append" lt html gt" return sbRet. ToString private string GetSheets string sheetName, bool needTitle StringBuilder sbRet new StringBuilder ISheet sheet wb. GetSheet sheetName sbRet. Append GetSheet sheet, needTitle return sbRet. ToString private string GetSheet ISheet sheet, bool needTitle StringBuilder sbRet new StringBuilder List lt KeyValuePair lt int, int gt gt widths computeWidths sheet int tableWidth widths. Where o gt o. Key IDX_TABLE_WIDTH. First. Value sbRet. Append string. Format" lt table class cellspacing" 0" cellpadding" 0" style" width: 1 px" gt", DEFAULTS_CLASS, tableWidth sbRet. Append GetCols widths, needTitle sbRet. Append GetSheetContent sheet, needTitle sbRet. Append" lt table gt" return sbRet. ToString private string GetColumnHeads StringBuilder sbRet new StringBuilder sbRet. Append string. Format" lt thead gt" sbRet. Append string. Format" lt tr class gt", COL_HEAD_CLASS sbRet. Append string. Format" lt th class gt lt th gt", COL_HEAD_CLASS noinspection UnusedDeclaration for int i firstColumn i lt endColumn i StringBuilder colName new StringBuilder int cnum i do colName. Insert 0, char" A" cnum 26 cnum 26 while cnum gt sbRet. Append string. Format" lt th class gt 1 lt th gt", COL_HEAD_CLASS, colName sbRet. Append" lt tr gt" sbRet. Append" lt thead gt" return sbRet. ToString private string GetSheetContent ISheet sheet, bool needTitle StringBuilder sbRet new StringBuilder if needTitle sbRet. Append GetColumnHeads sbRet. Append string. Format" lt tbody gt" IEnumerator rows sheet. GetRowEnumerator while rows. MoveNext IRow row IRow rows. Current sbRet. Append string. Format" lt tr gt" if needTitle sbRet. Append string. Format" lt td class gt 1 lt td gt", ROW_HEAD_CLASS, row. RowNum 1 StringBuilder sbTemp new StringBuilder int mergeCnt ICell preCell null ICell cell null for int i firstColumn i lt endColumn i String content "?" String attrs "" ICellStyle style null bool isMerge false if i gt row. FirstCellNum amp amp i lt row. LastCellNum cell row. GetCell i if cell ! null isMerge cell. IsMergedCell style cell. CellStyle attrs tagStyle cell, style Set the value that is rendered for the cell also applies the format MyCellFormat cf MyCellFormat. GetInstance style. GetDataFormatString CellFormatResult result cf. Apply cell content result. Text never null if string. IsNullOrEmpty content content "?" if isMerge true amp amp content "?" yin wei NPOI fan hui de cell mei you mergeCnt shu xing, zhi you yi ge IsMergedCell shu xing ru guo you 5 ge dan yuan ge, hou mian si ge dan yuan ge he bing cheng yi ge da dan yuan ge ta fan hui de qi shi hai shi 5 ge dan yuan ge, IsMergedCell fen bie shi: false, true, true, true, true shang tou zhe zhong qing kuang hai suan hao, wo men hao dai hai neng cai dao hou mian si ge dan yuan ge shi he bing dan yuan ge dan shi ru guo di yi ge dan du, hou mian si ge mei liang ge he bing ne? TMD fan hui de hai shi 5 ge dan yuan ge, IsMergedCell reng ran shi: false, true, true, true, true suo yi zhe li shi you wen ti de, wo mei fa zhi dao hou mian di si ge dan yuan ge shi si ge he bing cheng yi ge ne, hai shi liang ge liang ge de fen bie he bing zhe ge shi mei ban fa de, chu fei cong NPOI de yuan dai ma li tou qu jie jue zhe ge wen ti, jie yu shang ban ne, yao qiu de shi chu jie guo, suo yi gong si shi bu tai hui yun xu wo qu gan zhe zhong tou ru chan chu bi jiao cha de shi qing de, suo yi zhe ge wen ti wo cai yong le yi ge cheng ben bi jiao di de ban fa lai rao kai ban fa jiu shi wo men zai ding yi mu ban de shi hou, ke yi tong guo wei mei yi ge he bing dan yuan ge tian jia nei rong lai bi mian. bi ru shuo cell1 nei rong, cell2, cell3 nei rong, cell4, cell5 nei rong zhe yang de hua wo jiu neng zhi dao cell1 IsMergedCell false shi yi ge du li de dan yuan ge cell2, cell3, cell4, cell5 de IsMergedCell sui ran dou shi true, dan shi yin wei cell4 zhe ge wei zhi you nei rong le, na wo jiu xiao de cell2 he cell3 shi he bing de, cell4 he cell5 ye shi he bing de. dang ran zhe li hai hui you ge xiao xiao de wen ti, ru guo cell4, cell5 li tou shi yi ge hui bei ti huan diao de nei rong, ye ji zi duan zhe yang de dong xi ru guo shi ji de nei rong wei null na me cell4, cell5 he bing dan yuan ge de nei rong ye jiu shi null le, zhe you hui dao le zhi qian de wen ti le, suo yi ci chu yao qiu ding yi mu ban de shi hou nei rong hou mian jia yi ge kong ge, zhe yang zai sheng cheng html de shi hou, qi shi shi bu ying xiang da yin xiao guo de. ye ji " " zhu yi shuang yin hao li tou de "" hou tou you ge kong ge if mergeCnt 1 amp amp preCell ! null amp amp preCell. IsMergedCell false sbTemp. Append string. Format" lt td class 1 3 gt 2 lt td gt", styleName style, attrs, content, isMerge ? " colspan" 1"" : "" else mergeCnt else sbTemp. Replace" colspan" 1"", string. Format" colspan" 0"", mergeCnt mergeCnt 1 sbTemp. Append string. Format" lt td class 1 3 gt 2 lt td gt", styleName style, attrs, content, isMerge ? " colspan" 1"" : "" preCell cell sbRet. Append sbTemp. Replace" colspan" 1"", string. Format" colspan" 0"", mergeCnt. ToString sbRet. Append string. Format" lt tr gt" sbRet. Append string. Format" lt tbody gt" return sbRet. ToString private String tagStyle ICell cell, ICellStyle style if style. Alignment HorizontalAlignment. General switch ultimateCellType cell case CellType. String: return " style" textalign: left"" case CellType. Boolean: case CellType. Error: return " style" textalign: center"" case CellType. Numeric: default: " right" is the default break return "" private static CellType ultimateCellType ICell c CellType type c. CellType if type CellType. Formula type c. CachedFormulaResultType return type private string GetCols List lt KeyValuePair lt int, int gt gt widths, bool needTitle StringBuilder sbRet new StringBuilder if needTitle int headerColWidth widths. Where o gt o. Key IDX_HEADER_COL_WIDTH. First. Value sbRet. Append string. Format" lt col style" width: px" gt", headerColWidth for int i firstColumn i lt endColumn i int colWidth widths. Where o gt o. Key i. First. Value sbRet. Append string. Format" lt col style" width: px" gt", colWidth return sbRet. ToString private List lt KeyValuePair lt int, int gt gt computeWidths ISheet sheet List lt KeyValuePair lt int, int gt gt ret new List lt KeyValuePair lt int, int gt gt int tableWidth ensureColumnBounds sheet compute width of the header column int lastRowNum sheet. LastRowNum int headerCharCount lastRowNum. ToString. Length int headerColWidth widthToPixels headerCharCount 1 256 ret. Add new KeyValuePair lt int, int gt IDX_HEADER_COL_WIDTH, headerColWidth tableWidth headerColWidth for int i firstColumn i lt endColumn i int colWidth widthToPixels sheet. GetColumnWidth i ret. Add new KeyValuePair lt int, int gt i, colWidth tableWidth colWidth ret. Add new KeyValuePair lt int, int gt IDX_TABLE_WIDTH, tableWidth return ret private int widthToPixels double widthUnits return int Math. Round widthUnits 9 256 private void ensureColumnBounds ISheet sheet if gotBounds return IEnumerator iter sheet. GetRowEnumerator if iter. MoveNext firstColumn else firstColumn int. MaxValue endColumn iter. Reset while iter. MoveNext IRow row IRow iter. Current short firstCell row. FirstCellNum if firstCell gt firstColumn Math. Min firstColumn, firstCell endColumn Math. Max endColumn, row. LastCellNum gotBounds true private string GetInlineStyle StringBuilder sbRet new StringBuilder sbRet. Append" lt style type" text css" gt" sbRet. Append GetStyles sbRet. Append" lt style gt" return sbRet. ToString private string GetStyles StringBuilder sbRet new StringBuilder HashSet lt ICellStyle gt seen new HashSet lt ICellStyle gt for int i i lt wb. NumberOfSheets i ISheet sheet wb. GetSheetAt i IEnumerator rows sheet. GetRowEnumerator while rows. MoveNext IRow row IRow rows. Current foreach ICell cell in row ICellStyle style cell. CellStyle if ! seen. Contains style sbRet. Append GetStyle style seen. Add style return sbRet. ToString private string GetStyle ICellStyle style StringBuilder sbRet new StringBuilder sbRet. Append string. Format". . 1 ", DEFAULTS_CLASS, styleName style sbRet. Append styleContents style sbRet. Append"" return sbRet. ToString private string styleContents ICellStyle style StringBuilder sbRet new StringBuilder sbRet. Append styleOut" textalign", style. Alignment sbRet. Append styleOut" verticalalign", style. VerticalAlignment sbRet. Append fontStyle style sbRet. Append borderStyles style sbRet. Append colorStyles style return sbRet. ToString private string colorStyles ICellStyle style StringBuilder sbRet new StringBuilder sbRet. Append" hai wei shi xian!" return sbRet. ToString private string borderStyles ICellStyle style StringBuilder sbRet new StringBuilder sbRet. Append styleOut" borderleft", style. BorderLeft NPOI you BUG, he bing dan yuan ge de borderright yong yuan dou shi None wo men ke yi tong guo she zhi he bing dan yuan ge hou bian na ge dan yuan ge de zuo bian kuang de jie jue ru guo dang qian he bing dan yuan ge yi jing he bing dao zui hou yi lie le, wo men jiu zhi neng zai jia yi lie le, wei liao bu ying xiang da yin xiao guo zhe zui hou jia de zhe yi lie zai she zhi hao zuo bian kuang hou, xu yao ba kuan du she zhi de hen xiao, bi ru shuo 0. 1 zhe yang sbRet. Append styleOut" borderright", style. BorderRight sbRet. Append styleOut" bordertop", style. BorderTop sbRet. Append styleOut" borderbottom", style. BorderBottom return sbRet. ToString private string fontStyle ICellStyle style StringBuilder sbRet new StringBuilder IFont font style. GetFont wb if font. Boldweight sbRet. Append" fontweight: bold" if font. IsItalic sbRet. Append" fontstyle: italic" double fontheight font. FontHeight 10 10 if fontheight 9 fix for stupid ol Windows fontheight 10 sbRet. Append string. Format" fontsize: pt", fontheight return sbRet. ToString private string styleOut string k, HorizontalAlignment p return k ":" HALIGN. Where o gt o. Key p. First. Value "" private string styleOut string k, VerticalAlignment p return k ":" VALIGN. Where o gt o. Key p. First. Value "" private string styleOut string k, BorderStyle p return k ":" BORDER. Where o gt o. Key p. First. Value "" private string styleName ICellStyle style if style null style wb. GetCellStyleAt short StringBuilder sb new StringBuilder sb. Append string. Format" style_ 0", style. Index return sb. ToString    javascript bu fen:

?

最后NPOI在处理日期的时候,还有一个BUG

using NPOI.SS.UserModel;
using NPOI.Util;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using NPOI.SS.Format;

namespace ExcelUtility
{
    /// 
    /// 这个东西是为了解决 NPOI CellFormat 的BUG而存在的。
    /// 它在读取 日期格式 的时候有时候会报错。
    /// 
    public class MyCellFormat
    {
        private CellFormat cellformat = null;

        private MyCellFormat(string format) {
            this.cellformat = CellFormat.GetInstance(format);
        }

        public static MyCellFormat GetInstance(string format) {
            return new MyCellFormat(format);
        }

        public CellFormatResult Apply(ICell cell)
        {
            try
            {
                return cellformat.Apply(cell);
            }
            catch (Exception)
            {
                var formatStr = cell.CellStyle.GetDataFormatString();
                var mc = new Regex(@"(yy|M|d|H|s|ms)").Match(formatStr); 
                /*
                 * 目前全部不能正常转换的日期格式都转换成 yyyy - MM - dd 的形式
                 * 比如说:【[$-F800]dddd, mmmm dd, yyyy】这个格式
                 * 稍微 google 了下( https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx)
                 * 这个字符串 0x0800 表示 [System default locale language]
                 * 因时间关系,只能干完手头的活之后再慢慢研究了。
                 */
                if (mc.Success)
                {
                    return CellFormat.GetInstance("yyyy-MM-dd").Apply(cell);
                }
                else return cellformat.Apply(cell.ToString() + "");
            }
        }

        public CellFormatResult Apply(Object v)
        {
            return cellformat.Apply(v);
        }
    }
}

  

当前文章:http://www.stylishl.com/0j0zew/37039-1199375-98588.html

发布时间:00:00:00

王中王开奖493333??11303管家婆彩图2016??www.4311111.com??小鱼儿主页管家婆??www.295555.com??开码结果??168开奖现场报??最快开奖现场报码??www.166h.com??挂牌??