NPOI 2.0 Excel读取显示

昵称10504424 2014-03-14

展开全文

最近接到需求，需要把excel表格里的数据原样展示到web页面，主要是满足随意跨行跨列。

之前用过一点NPOI，不过接触的不太多，趁这次机会再熟悉一下。由于操作的excel都是2007以上的版本，所以选择了2.0的版本。

这里稍微提一下2.0与1.X的区别：2.0主要针对2007及以上版本，1.X主要针对2003，此外方法也略有不同，但是过渡还是很平缓的，这里不做过多的赘述。

日期	买入
日期	买入额（万元）	偿还额（万元）	净买入额（万元）
2014-1-30	67644.71	58602.77	9041.94
2013-12-31	520660.88	449425.22	71235.66
2013-11-29	515912.92	525626.82	-9713.91
2013-10-31	758822.25	738848.47	19973.79

后台代码：

　　　　using NPOI;
　　　　using NPOI.HSSF.UserModel; 　　//2003版本
　　　　using NPOI.XSSF.UserModel;　　 //2007版本
　　　　using NPOI.SS.UserModel;



         public string ConvertExcelToJsonString()
         {
             try
             {
  　　　　　　　　　string excelName = "data.xlsx";
                 string sheet = "Sheet3";
                 string filePath = HttpContext.Current.Server.MapPath(String.Format("~/App_Data/{0}", excelName));
                 //HSSFWorkbook wb = new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
                 //HSSFSheet sht = (HSSFSheet)wb.GetSheet(sheet); 如果是2003 则用HSS开头的对象。
                 FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                 XSSFWorkbook xworkbook = new XSSFWorkbook(file);
                 XSSFSheet xsheet = (XSSFSheet)xworkbook.GetSheet(sheet);
                 int rowsCount = xsheet.PhysicalNumberOfRows; //取行Excel的最大行数
                 int colsCount = xsheet.GetRow(0).PhysicalNumberOfCells;//取得Excel的列数
                 StringBuilder excelJson = new StringBuilder();
                 //StringBuilder table = new StringBuilder();
 
                 int colSpan;
                 int rowSpan;
                 bool isByRowMerged;
 
                 excelJson.Append("[");
                 //table.Append("<table border='1px'>");
                 for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
                 {
                     if (rowIndex > 0) { excelJson.Append(","); }
                     excelJson.Append("[");
                     for (int colIndex = 0; colIndex < colsCount; colIndex++)
                     {
                         //获取Table某个TD合并的列数和行数等信息。与Excel中对应Cell的合并行数和列数一致。
                         GetTdMergedInfo(xsheet, rowIndex, colIndex, out colSpan, out rowSpan, out isByRowMerged);
                         //被合并的行或列不输出的处理方式不一样
                         //如果已经被 行 合并包含进去了就不输出TD了。
                         if (isByRowMerged)
                         {
                             continue;
                         }
                         excelJson.Append("{");
                         excelJson.Append(string.Format("Text:'{0}'", xsheet.GetRow(rowIndex).GetCell(colIndex)));
                         excelJson.Append(string.Format(",ColSpan:'{0}'",colSpan));
                         excelJson.Append(string.Format(",RowSpan:'{0}'",rowSpan));
                         excelJson.Append(",Width:''");
                         excelJson.Append(",Align:'center'");
                         excelJson.Append(",Index:''");
                         excelJson.Append("},");
                         //列被合并之后此行将少输出colSpan-1个TD。
                         if (colSpan > 1)
                             colIndex += colSpan - 1;
                         
                     }
                     excelJson.Remove(excelJson.Length-1,1);
                     excelJson.Append("]");
                 }
                 excelJson.Append("]");
                 return excelJson.toString();             }
             catch (Exception ex)
             {
                 return null;
             }
         }

         /// <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="isByRowMerged">此单元格是否被某个行合并包含在内。如果被包含在内，将不输出TD。</param>
         /// <returns></returns>
         private void GetTdMergedInfo(XSSFSheet xsheet, int rowIndex, int colIndex, out int colspan, out int rowspan, out bool isByRowMerged)
         {
             colspan = 1;
             rowspan = 1;
             isByRowMerged = false;
             int regionsCuont = xsheet.NumMergedRegions;//取得合并单元格的个数
             //Region region;
             for (int i = 0; i < regionsCuont; i++)
             {
                 //region = xsheet.GetMergedRegionAt(i); 此方法为1.2版本，高版本已去掉
                 CellRangeAddress range = xsheet.GetMergedRegion(i);//取得第i个合并单元格的跨越范围
                 xsheet.IsMergedRegion(range);
                 //region = xsheet.GetMergedRegion(i);
                 if (range.FirstRow == rowIndex && range.FirstColumn == colIndex) 
                 {
                     colspan = range.LastColumn - range.FirstColumn + 1;
                     rowspan = range.LastRow - range.FirstRow + 1;
                     return;
                 }
                 else if (rowIndex > range.FirstRow && rowIndex <= range.LastRow && colIndex >= range.FirstColumn && colIndex <= range.LastColumn)
                 {
                     isByRowMerged = true;
                 }
             }
         }

前台代码：

function ExcelDataBind(data) {
    var data = result.Data.toJSON() //此处拿到后台Json字符串
    var tableObj= $('<table border="1"  width="100%"></table>');
    var theadObj = $("<thead></thead>");
    var tbodyobj = $("<tbody></tbody>");
    for (var i = 0; i < data.length; i++) {
        var trHtml = '<tr>';
       //以下特殊业务需要，请参考自己逻辑修改
        for (var j = 0; j < data[i].length; j++) {
            var tdType = data[i][j].Index == 1 ? "th" : "td";
            var colspan = data[i][j].ColSpan == 1 ? "" : " colspan=" + data[i][j].ColSpan;
            var rowspan = data[i][j].RowSpan == 1 ? "" : " rowspan=" + data[i][j].RowSpan;
            trHtml += '<' + tdType + colspan + rowspan + ' width=' + data[i][j].Width + ' align="' + data[i][j].Align + '">' + data[i][j].Text + '</' + tdType + '>';
        }
        trHtml += '</tr>';
        
        if (data[i][0].Index == 1) {
            theadObj.append(trHtml);
        }else {
            tbodyobj.append(trHtml);
        }
    }
    tableObj.append(theadObj).append(tbodyobj);
    
}

以上功能是将 excel 里的数据转化成json格式（如下），因为还有别的用处，所以就没直接转换成Html的table。如果想直接转换成Table，请参考官网例子

[[{Text:'日期',ColSpan:'1',RowSpan:'2',Width:'',Align:'center',Index:''},{Text:'买入',ColSpan:'3',RowSpan:'1',Width:'',Align:'center',Index:''}],[{Text:'买入额（万元）',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'偿还额（万元）',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'净买入额（万元）',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''}],[{Text:'30-一月-2014',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'67644.71',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'58602.77',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'9041.94',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''}],[{Text:'31-十二月-2013',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'520660.88',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'449425.22',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'71235.66',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''}],[{Text:'29-十一月-2013',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'515912.92',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'525626.82',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'-9713.91',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''}],[{Text:'31-十月-2013',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'758822.25',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'738848.47',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''},{Text:'19973.79',ColSpan:'1',RowSpan:'1',Width:'',Align:'center',Index:''}]]

以上仅作为学习资料，方便自己以后查找，写的不是很详细，如有疑问可以留言。谢谢！