Ejemplo n.º 1
0
        private static ICellStyle SetRowCellStyle(IWorkbook book, ICellStyle cellStyle, ExportStyleModel styleModel)
        {
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            cellStyle.Alignment         = HorizontalAlignment.LEFT;
            if (!string.IsNullOrEmpty(styleModel.Alignment))
            {
                if (styleModel.Alignment.Equals("center"))
                {
                    cellStyle.Alignment = HorizontalAlignment.CENTER;
                }
                else if (styleModel.Alignment.Equals("left"))
                {
                    cellStyle.Alignment = HorizontalAlignment.LEFT;
                }
                else
                {
                    cellStyle.Alignment = HorizontalAlignment.RIGHT;
                }
            }
            cellStyle.WrapText = true;//换行
            //必须设置单元格背景色 FillForegroundColor 和 FillPattern 的值才能正确显示背景色
            if (styleModel.FillForegroundColor >= 1)
            {
                if (styleModel.FillForegroundColor == 1)
                {
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; //(short)白色
                }
                if (styleModel.FillForegroundColor == 2)
                {
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index; //(short)红色
                }
                if (styleModel.FillForegroundColor == 3)
                {
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; //(short)黑色
                }
                if (styleModel.FillForegroundColor == 4)
                {
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; //(short)浅蓝色
                }
                if (styleModel.FillForegroundColor == 5)
                {
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; //(short)黄色
                }
                cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;                  // CellStyle.SOLID_FOREGROUND
            }
            if (styleModel.borderWidth > 0)
            {
                if (styleModel.borderColor == 1)
                {
                    cellStyle.BorderLeft   = BorderStyle.THIN;
                    cellStyle.BorderRight  = BorderStyle.THIN;
                    cellStyle.BorderBottom = BorderStyle.THIN;
                    cellStyle.BorderTop    = BorderStyle.THIN;
                }
            }
            if (styleModel.Indention > 0)
            {
                cellStyle.Indention = styleModel.Indention;
            }
            //四、设置字体:
            IFont font = book.CreateFont();

            if (styleModel.FontHeightInPoints <= 0)
            {
                styleModel.FontHeightInPoints = (short)11.5;
            }
            font.FontHeightInPoints = styleModel.FontHeightInPoints;//.SetFontHeightInPoints((short)16);//设置字体大小
            if (styleModel.Color == 1)
            {
                font.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
            }
            else if (styleModel.Color == 2)
            {
                font.Color = NPOI.HSSF.Util.HSSFColor.BLUE.index;
            }
            else if (styleModel.Color == 3)
            {
                font.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
            }
            if (string.IsNullOrEmpty(styleModel.FontName))
            {
                font.FontName = "黑体";//.SetFontName("黑体");
            }
            else
            {
                font.FontName = styleModel.FontName;
            }
            cellStyle.SetFont(font);//选择需要用到的字体格式
            return(cellStyle);
        }
Ejemplo n.º 2
0
 public static void CreadDataRows(DataTable dt, ref ISheet sheet, IWorkbook workbook, List <NpoiHeadCfg> heads = null, List <ExportStyleModel> styleModelList = null)
 {
     if (dt == null || dt.Rows.Count == 0)
     {
         return;
     }
     if (heads != null && heads.Count > 0)
     {
         List <NpoiHeadCfg> curhds = new List <NpoiHeadCfg>();
         //遍历所有顶层节点
         for (int x = 0; x < heads.Count; x++)
         {
             List <NpoiHeadCfg> hds = GetAllLeafNode(heads[x]);//获取所有叶子子节点
             curhds.AddRange(hds);
         }
         //遍历所有数据行
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             var myStyleModelList = new ExportStyleModel[] { };
             if (styleModelList != null && styleModelList.Count > 0)
             {
                 myStyleModelList = styleModelList.Where(p => p.rowIndex == i).ToArray();
             }
             IRow row      = sheet.CreateRow(sheet.LastRowNum + 1);//创建空行
             int  colIndex = 0;
             //遍历所有叶子子节点
             for (var y = 0; y < curhds.Count; y++)
             {
                 var ColumnName = curhds[y].FieldName;
                 if (y < dt.Columns.Count)
                 {
                     ColumnName = string.IsNullOrEmpty(ColumnName) ? dt.Columns[y].ColumnName : ColumnName;
                 }
                 if (dt.Columns.Contains(ColumnName))
                 {//数据源列是否含有配置列
                     ICell cell = row.CreateCell(colIndex);
                     if (dt.Rows[i][ColumnName] != DBNull.Value)
                     {
                         var strValue = dt.Rows[i][ColumnName].ToString();
                         SetCellValue(workbook, ColumnName, cell, strValue);
                     }
                     colIndex++;
                     if (myStyleModelList.Length > 0)
                     {
                         var myStyleModel = myStyleModelList.FirstOrDefault(p => p.cellIndex <= 0 && string.IsNullOrEmpty(p.cellName));
                         if (myStyleModel == null)
                         {
                             myStyleModel = myStyleModelList.FirstOrDefault(p => !string.IsNullOrEmpty(p.cellName) && p.cellName.Equals(ColumnName));
                         }
                         if (myStyleModel != null)
                         {
                             ICellStyle cellStyle = workbook.CreateCellStyle();
                             cellStyle      = SetRowCellStyle(workbook, cellStyle, myStyleModel);
                             cell.CellStyle = cellStyle;
                             if (myStyleModel.rowHeight > 0)
                             {
                                 row.Height = myStyleModel.rowHeight;
                             }
                         }
                     }
                 }
             }
         }
     }
     else
     {
         //填充内容
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             var myStyleModelList = new ExportStyleModel[] { };
             if (styleModelList != null && styleModelList.Count > 0)
             {
                 myStyleModelList = styleModelList.Where(p => p.rowIndex == i).ToArray();
             }
             IRow row = sheet.CreateRow(sheet.LastRowNum + 1);//创建空行
             for (int j = 0; j < dt.Columns.Count; j++)
             {
                 string ColumnName = dt.Columns[j].ColumnName;
                 ICell  cell       = row.CreateCell(j);
                 if (dt.Rows[i][ColumnName] != DBNull.Value)
                 {
                     var strValue = dt.Rows[i][ColumnName].ToString();
                     SetCellValue(workbook, ColumnName, cell, strValue);
                 }
                 if (myStyleModelList.Length > 0)
                 {
                     var myStyleModel = myStyleModelList.FirstOrDefault(p => p.cellIndex <= 0 && string.IsNullOrEmpty(p.cellName));
                     if (myStyleModel == null)
                     {
                         myStyleModel = myStyleModelList.FirstOrDefault(p => !string.IsNullOrEmpty(p.cellName) && p.cellName.Equals(ColumnName));
                     }
                     if (myStyleModel != null)
                     {
                         ICellStyle cellStyle = workbook.CreateCellStyle();
                         cellStyle      = SetRowCellStyle(workbook, cellStyle, myStyleModel);
                         cell.CellStyle = cellStyle;
                         if (myStyleModel.rowHeight > 0)
                         {
                             row.Height = myStyleModel.rowHeight;
                         }
                     }
                 }
             }
         }
     }
 }