Пример #1
0
        public override void SetBackgroudColor(System.Drawing.Color color)
        {
            IWorkbook  workbook  = _npoiWorksheet.Workbook;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum));
            if (workbook is HSSFWorkbook)
            {
                HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
                HSSFPalette  palette      = hssfWorkbook.GetCustomPalette(); //调色板实例

                //palette.SetColorAtIndex((short)8, color.R, color.G, color.B);

                HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);

                cellStyle.FillPattern = FillPattern.SolidForeground;

                cellStyle.FillForegroundColor = hssFColor.Indexed;
            }
            else
            {
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
                HSSFPalette  palette      = hssfWorkbook.GetCustomPalette(); //调色板实例

                //palette.SetColorAtIndex((short)8, color.R, color.G, color.B);

                HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);

                cellStyle.FillPattern = FillPattern.SolidForeground;

                cellStyle.FillForegroundColor = hssFColor.Indexed;
                //No way!
            }
            _npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle);
        }
Пример #2
0
        public virtual void TestDefaultColumnStyle()
        {
            IWorkbook  wb    = _testDataProvider.CreateWorkbook();
            ICellStyle style = wb.CreateCellStyle();
            ISheet     sheet = wb.CreateSheet();

            sheet.SetDefaultColumnStyle(/*setter*/ 0, style);
            Assert.IsNotNull(sheet.GetColumnStyle(0));
            Assert.AreEqual(style.Index, sheet.GetColumnStyle(0).Index);

            IRow       row    = sheet.CreateRow(0);
            ICell      cell   = row.CreateCell(0);
            ICellStyle style2 = cell.CellStyle;

            Assert.IsNotNull(style2);
            Assert.AreEqual(style.Index, style2.Index, "style should match");
        }
Пример #3
0
        private void SetUpHeader(ISheet sheet, ClassMapping classMapping)
        {
            var workbook = sheet.Workbook;
            var row      = sheet.GetRow(0) ?? sheet.CreateRow(0);

            InitMappingColumns(classMapping);
            CacheColumnIndices(classMapping);

            //freeze header row
            sheet.CreateFreezePane(0, 1);

            //create header and column style for every mapped column
            foreach (var mapping in classMapping.PropertyMappings)
            {
                var cellIndex = CellReference.ConvertColStringToIndex(mapping.Column);
                var cell      = row.GetCell(cellIndex) ?? row.CreateCell(cellIndex);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(mapping.Header);
                cell.CellStyle = GetStyle(DataStatus.Header, workbook);

                //set default column style if not defined but available
                var style = GetStyle(mapping.Status, workbook);
                if (mapping.Status == DataStatus.None)
                {
                    continue;
                }
                var existStyle = sheet.GetColumnStyle(cellIndex);
                if (
                    existStyle != null &&
                    existStyle.FillForegroundColor == style.FillForegroundColor &&
                    existStyle.BorderTop == style.BorderTop &&
                    existStyle.TopBorderColor == style.TopBorderColor
                    )
                {
                    continue;
                }

                //create new style
                sheet.SetDefaultColumnStyle(cellIndex, style);
                sheet.SetColumnHidden(cellIndex, false);
                //set default width
                sheet.SetColumnWidth(cellIndex, 256 * 15);
                //hide if defined
                if (mapping.Hidden)
                {
                    sheet.SetColumnHidden(cellIndex, true);
                }
            }

            //set up filter
            var lastPropMap = classMapping.PropertyMappings.OrderBy(p => p.ColumnIndex).LastOrDefault();

            if (lastPropMap != null)
            {
                sheet.SetAutoFilter(new CellRangeAddress(0, 1, 0, lastPropMap.ColumnIndex));
            }
        }
Пример #4
0
        public static Stream DataTableToExcel(string fileName, DataTable data,
                                              List <ExcelColumnFormat> colFmt,
                                              string cols,
                                              Func <object, string, object> tranfunc = null,
                                              bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null)
        {
            if (data.Rows.Count == 0)
            {
                return(null);
            }                                         //data为空,直接退出
            int              i        = 0;
            int              j        = 0;
            int              count    = 0;
            IWorkbook        workbook = null;
            ISheet           sheet    = null;
            NPOIMemoryStream ms       = null;

            try
            {
                //转换对齐
                Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment =>
                {
                    switch (alignment)
                    {
                    case System.Drawing.ContentAlignment.BottomRight:
                    case System.Drawing.ContentAlignment.MiddleRight:
                    case System.Drawing.ContentAlignment.TopRight:
                        return(HorizontalAlignment.Right);

                    case System.Drawing.ContentAlignment.BottomCenter:
                    case System.Drawing.ContentAlignment.MiddleCenter:
                    case System.Drawing.ContentAlignment.TopCenter:
                        return(HorizontalAlignment.Center);
                    }
                    return(HorizontalAlignment.Left);
                };
                //设置值的格式
                Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) =>
                {
                    string rlt  = "";
                    string vstr = Ass.P.PStr(v);
                    string tv   = null;
                    if (tranfunc != null)
                    {
                        tv = Ass.P.PStr(tranfunc(v, colCode));
                    }

                    ExcelColumnFormat fmt = null;
                    if (tv != null && tv != vstr)
                    {
                        rlt = tv;
                    }
                    else if (colFmt.Any(m => m.ColumnNameCode == colCode))
                    {
                        rlt = vstr;
                        string v0 = "";
                        fmt = colFmt.Find(m => m.ColumnNameCode == colCode);
                        if (v != null && fmt.ToStringFormat.IsNotEmpty())
                        {
                            v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v);
                            if (v0 != vstr)
                            {
                                rlt = v0;
                            }
                        }
                    }
                    else
                    {
                        rlt = vstr;
                    }

                    //设置值
                    var    cell = row.CreateCell(j);
                    var    t = rlt.GetStringType();
                    double dbval = 0; DateTime dtval;
                    if (double.TryParse(rlt, out dbval))
                    {
                        cell.SetCellValue(dbval);
                    }
                    else if (DateTime.TryParse(rlt, out dtval))
                    {
                        cell.SetCellValue(rlt);
                    }
                    else
                    {
                        cell.SetCellValue(rlt);
                    }
                };


                using (ms = new NPOIMemoryStream())
                {
                    if (!GetWorkbook(fileName, out workbook))
                    {
                        return(null);
                    }
                    List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合
                    sheet = workbook.CreateSheet(sheetName);

                    //表标题
                    if (tableTitle.IsNotEmpty())
                    {
                        IRow          row     = sheet.CreateRow(count++);
                        ICell         cell    = row.CreateCell(0);
                        XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                        IFont         ztFont  = workbook.CreateFont();
                        ztFont.FontHeightInPoints = 14;
                        ztFont.Underline          = FontUnderlineType.DoubleAccounting;
                        ztStyle.SetFont(ztFont);
                        ztStyle.Alignment = HorizontalAlignment.Center;
                        cell.CellStyle    = ztStyle;
                        cell.SetCellValue(tableTitle);
                        var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1));    //合并单元格
                    }

                    Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>();
                    //写入DataTable的列名
                    if (isColumnWritten == true)
                    {
                        IRow row = sheet.CreateRow(count++);
                        if (colList.Count > 0) //设定了输出的列
                        {
                            for (j = 0; j < colList.Count(); j++)
                            {
                                var key     = colList[j];
                                var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key;
                                var cell    = row.CreateCell(j);
                                var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
                                cell.SetCellValue(keyname);

                                //设置对齐与数据格式
                                var cStyle = workbook.CreateCellStyle(); bool bfmt = false;
                                if (cellfmt.Alignment != null)
                                {
                                    bfmt             = true;
                                    cStyle.Alignment = _transHAlignment(cellfmt.Alignment);
                                }
                                if (cellfmt.DataFormat.IsNotEmpty())
                                {
                                    bfmt = true;
                                    short?dfmt = null; IDataFormat format = workbook.CreateDataFormat();
                                    switch (cellfmt.DataFormat)
                                    {
                                    case "Price": dfmt = format.GetFormat("#,##0.00"); break;

                                    case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break;

                                    case "DateTime": dfmt = 0x16; break;   // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break;

                                    case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break;
                                    }
                                    cStyle.DataFormat = dfmt.Value;
                                }
                                if (bfmt)
                                {
                                    _columeCellStyles.Add(j, cStyle);
                                }

                                //设置列宽
                                if (cellfmt.ColumnWidth.HasValue)
                                {
                                    sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256));
                                }
                            }
                        }
                        else
                        {
                            for (j = 0; j < data.Columns.Count; j++)
                            {
                                string key = data.Columns[j].ColumnName;
                                if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文
                                {
                                    var cell    = row.CreateCell(j);
                                    var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
                                    cell.SetCellValue(cellfmt.ColumnName);
                                    //设置对齐
                                    if (cellfmt.Alignment != null)
                                    {
                                        sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment);
                                    }
                                    //设置列宽
                                    if (cellfmt.ColumnWidth.HasValue)
                                    {
                                        sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value);
                                    }
                                }
                                else
                                {
                                    row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                                }
                            }
                        }
                    }

                    //写入数据
                    if (colList.Count > 0)
                    {
                        for (i = 0; i < data.Rows.Count; i++, count++)
                        {
                            IRow row = sheet.CreateRow(count);
                            for (j = 0; j < colList.Count; j++)
                            {
                                _val(row, j, data.Rows[i][colList[j]], colList[j]);
                            }
                        }
                    }
                    else
                    {
                        for (i = 0; i < data.Rows.Count; i++, count++)
                        {
                            IRow row = sheet.CreateRow(count);
                            for (j = 0; j < data.Columns.Count; j++)
                            {
                                _val(row, j, data.Rows[i][j], data.Columns[j].ColumnName);
                            }
                        }
                    }

                    //设置列格式
                    foreach (int colIndex in _columeCellStyles.Keys)
                    {
                        sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]);
                    }


                    workbook.Write(ms); //写入到excel
                    ms.Flush();
                    ms.Position = 0;
                    return(ms);
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message);
                return(null);
            }
        }
Пример #5
0
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource">数据表</param>
        /// <param name="drSource">来源数据行</param>
        /// <param name="currentExcelRow">当前数据行</param>
        /// <param name="excelSheet">sheet集合</param>
        /// <param name="excelWorkBook">数据表</param>
        /// <param name="execlCellStyleList">表头样式</param>
        /// <param name="rowCount">execl当前行</param>
        private static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook, List <ExeclCellStyle> execlCellStyleList, int rowCount)
        {
            int cellIndex = 0;

            foreach (var item in ListColumnsName)
            {
                //列名称
                string columnsName = item.ToString();
                //根据列名称设置列样式
                ExeclCellStyle execlCellStyle = execlCellStyleList?.FirstOrDefault(it => it.ColumnsName == columnsName) ?? null;
                ICellStyle     columnStyle    = excelSheet.GetColumnStyle(cellIndex);
                //设置列样式
                //excelSheet.SetDefaultColumnStyle(cellIndex, SetExeclColumnStyle(columnStyle, excelWorkBook, execlCellStyle));
                //设置列宽
                if ((execlCellStyle?.Width ?? 0) > 0)
                {
                    excelSheet.SetColumnWidth(cellIndex, execlCellStyle.Width);
                }
                if (execlCellStyle?.IsHidden ?? false)
                {
                    excelSheet.SetColumnHidden(cellIndex, true);
                }
                ICell       newCell = null;
                System.Type rowType = drSource[columnsName].GetType();
                string      drValue = drSource[columnsName].ToString().Trim();
                //错误消息列
                string errColumn = columnsName + "ErrMsg";
                string errMsg    = string.Empty;
                if (dtSource.Columns.Contains(errColumn))
                {
                    errMsg = drSource[errColumn].ToString().Trim();
                }
                switch (rowType.ToString())
                {
                case "System.String":    //字符串类型
                    drValue = drValue.Replace("&", "&");
                    drValue = drValue.Replace(">", ">");
                    drValue = drValue.Replace("<", "<");
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(drValue);
                    break;

                case "System.Guid":    //GUID类型
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(drValue);
                    break;

                case "System.DateTime":    //日期类型
                    DateTime dateV;
                    DateTime.TryParse(drValue, out dateV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(dateV);
                    //格式化显示
                    ICellStyle  cellStyle = excelWorkBook.CreateCellStyle();
                    IDataFormat format    = excelWorkBook.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                    newCell.CellStyle    = cellStyle;
                    break;

                case "System.Boolean":    //布尔型
                    bool boolV = false;
                    bool.TryParse(drValue, out boolV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(boolV);
                    break;

                case "System.Int16":    //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(drValue, out intV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(intV.ToString());
                    break;

                case "System.Decimal":    //浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(drValue, out doubV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(doubV);
                    break;

                case "System.DBNull":    //空值处理
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue("");
                    break;

                default:
                    throw (new Exception(rowType.ToString() + ":类型数据无法处理!"));
                }
                //设置单元格样式
                SetExeclCellStyle(newCell, execlCellStyle);
                //设置批注
                if (!string.IsNullOrWhiteSpace(errMsg))
                {
                    SetComment(errMsg, currentExcelRow, newCell, excelWorkBook.GetCreationHelper(), (HSSFPatriarch)excelSheet.CreateDrawingPatriarch());
                }
                //设置计算表达式
                if (!string.IsNullOrEmpty(execlCellStyle.SetCellFormula))
                {
                    int rowIndex = rowCount + 1;
                    newCell.SetCellFormula(string.Format(execlCellStyle.SetCellFormula, rowIndex));
                    if (excelSheet.ForceFormulaRecalculation == false)
                    {
                        excelSheet.ForceFormulaRecalculation = true;//没有此句,则不会刷新出计算结果
                    }
                }
                cellIndex++;
            }
            bool shouldLock = execlCellStyleList?.FirstOrDefault(it => it.IsLock == true)?.IsLock ?? false;

            if (shouldLock == true)
            {
                excelSheet.ProtectSheet("password");//设置密码保护
            }
        }
Пример #6
0
        /// <summary>
        /// 设置列样式,默认带边框
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columnIndex"></param>
        /// <param name="columnName"></param>
        /// <param name="columnType"></param>
        public void SetColumnDefaultStyle(ISheet sheet, int columnIndex, Type columnType = null, HorizontalAlignment?align = null)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException("sheet");
            }
            if (columnIndex < 0)
            {
                throw new IndexOutOfRangeException("columnIndex");
            }
            var previousColumnStyle = sheet.GetColumnStyle(columnIndex);

            var        styleKey = Tuple.Create(columnType, align);
            ICellStyle defaultColumnStyle;

            if (!styles.TryGetValue(styleKey, out defaultColumnStyle))
            {
                defaultColumnStyle = CreateCellStyle();
                defaultColumnStyle.CloneStyleFrom(defaultCellStyle);
                if (columnType != null && columnType.IsValueType)
                {
                    if (columnType == typeof(DateTime))
                    {
                        defaultColumnStyle.DataFormat = (short)Workbook.CreateFormat("yyyy-MM-dd");
                    }
                    defaultColumnStyle.Alignment = HorizontalAlignment.Right;
                }
                else if (columnType == typeof(string))
                {
                    defaultColumnStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                }
                if (align.HasValue)
                {
                    switch (align)
                    {
                    case HorizontalAlignment.Center:
                        defaultColumnStyle.Alignment = HorizontalAlignment.Center;
                        break;

                    case HorizontalAlignment.Left:
                        defaultColumnStyle.Alignment = HorizontalAlignment.Left;
                        break;

                    case HorizontalAlignment.Right:
                        defaultColumnStyle.Alignment = HorizontalAlignment.Right;
                        break;
                    }
                }
                styles[styleKey] = defaultColumnStyle;
            }

            sheet.SetDefaultColumnStyle(columnIndex, defaultColumnStyle);

            // 刷新已知样式
            if (previousColumnStyle != null)
            {
                for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                {
                    var cell = sheet.GetRow(i).GetCell(columnIndex);
                    if (cell != null && Equals(cell.CellStyle, previousColumnStyle))
                    {
                        cell.CellStyle = defaultColumnStyle;
                    }
                }
            }
        }
Пример #7
0
 public ICellStyle GetColumnStyle(int column)
 {
     return(_sheet.GetColumnStyle(column));
 }