C# ISheet.AutoSizeColumn方法代码示例

        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);

                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }

    private static void CreateHeadRow(DataTable dt, HSSFWorkbook workbook, ISheet sheet)
    {
        ICellStyle titleStyle = workbook.CreateCellStyle();
        var styleHeader = CreateHeaderStyle(workbook,
                HSSFColor.WHITE.index, HSSFColor.GREEN.index);

        IRow row = sheet.CreateRow(0);
        ICell c1 = row.CreateCell(0);
        ICell c2 = row.CreateCell(1);
        ICell c3 = row.CreateCell(2);
        int i = 0;
        foreach (DataColumn  cell in dt.Columns)
        {
            ICell iCell = row.CreateCell(i);
            iCell.SetCellValue(cell.ColumnName);
            iCell.CellStyle = styleHeader;
            sheet.AutoSizeColumn(i);
            i++;
        }
    }

        private void WriteErrors(ISheet errorsSheet, COBieErrorCollection errorCollection)
        {
            // Write Header
            var summary = errorCollection
                          .GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
                          .Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Error), CountWarning = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Warning) })
                          .OrderBy(r => r.SheetName);
            
            //just in case we do not have ErrorLevel property in sheet COBieErrorCollection COBieError
            if (!hasErrorLevel)
            {
                summary = errorCollection
                          .GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
                          .Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(), CountWarning = 0 })
                          .OrderBy(r => r.SheetName);
            }

            
            //Add Header
            if (_row == 0)
            {
                IRow excelRow = errorsSheet.GetRow(0) ?? errorsSheet.CreateRow(0);
                int col = 0;

                ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue("Sheet Name");
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue("Field Name");
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue("Error Type");
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue("Error Count");
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue("Warning Count");
                col++;

                _row++; 
            }
            
            foreach(var error in summary)
            {

                IRow excelRow = errorsSheet.GetRow(_row + 1) ?? errorsSheet.CreateRow(_row + 1);
                int col = 0;

                ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue(error.SheetName);
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue(error.FieldName);
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue(error.ErrorType.ToString());
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue(error.CountError);
                col++;

                excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
                excelCell.SetCellValue(error.CountWarning);
                col++;
                
                _row++;
            }
            for (int c = 0 ; c < 5 ; c++)
            {
                errorsSheet.AutoSizeColumn(c);
            }

        }

 static void CreateCells(ISheet sheet, DataGridColumn col,
     IEnumerable source, int colIndex)
 {
     int rowIndex = 0;
     #region header
     var headerStyle = GetHeaderCellStyle(sheet.Workbook);
     var rowHeader = sheet.GetOrCreateRow(rowIndex++);
     var cellHeader = rowHeader.GetOrCreateCell(colIndex);
     cellHeader.SetCellValue(GetHeaderText(col.Header));
     cellHeader.SetCellType(CellType.String);
     cellHeader.CellStyle = headerStyle;
     #endregion
     var cellStyle = GetCellStyle(col, sheet);
     if (col is DataGridBoundColumn)
     {
         var c = (DataGridBoundColumn)col;
         var be = new BindingEvaluator(c.Binding);
         foreach (var obj in source)
         {
             IRow row = sheet.GetOrCreateRow(rowIndex++);
             ICell cell = row.GetOrCreateCell(colIndex);
             cell.CellStyle = cellStyle;
             be.DataContext = obj;
             cell.SetCellValue(be.Value);
         }
     }
     else if (col is System.Windows.Controls.DataGridComboBoxColumn)
     {
         var c = (System.Windows.Controls.DataGridComboBoxColumn)col;
         var be = new BindingEvaluator(c.SelectedValueBinding);
         var bValue = new BindingEvaluator(new Binding(c.SelectedValuePath));
         var bDisplay = new BindingEvaluator(new Binding(c.DisplayMemberPath));
         var itemSource = new Dictionary<object, object>();
         foreach (var i in c.ItemsSource)
         {
             bValue.DataContext = i;
             bDisplay.DataContext = i;
             var value = bValue.Value;
             if (value != null)
                 itemSource[value] = bDisplay.Value;
         }
         foreach (var obj in source)
         {
             IRow row = sheet.GetOrCreateRow(rowIndex++);
             ICell cell = row.GetOrCreateCell(colIndex);
             cell.CellStyle = cellStyle;
             be.DataContext = obj;
             var value = be.Value;
             if (itemSource.ContainsKey(value))
                 cell.SetCellValue(itemSource[value]);
             else
                 cell.SetCellValue(be.Value);
         }
     }
     else if (col is System.Windows.Controls.DataGridTemplateColumn)
     {
         var c = (DataGridTemplateColumn)col;
         var x = DataGridHelper.GetAttachedBinding(c);
         if (x == null) return;
         foreach (var obj in source)
         {
             var be = new BindingEvaluator(new Binding(x));
             be.DataContext = obj;
             IRow row = sheet.GetOrCreateRow(rowIndex++);
             ICell cell = row.GetOrCreateCell(colIndex);
             cell.CellStyle = cellStyle;
             cell.SetCellValue(be.Value);
         }
     }
     sheet.AutoSizeColumn(colIndex);
 }

        private void FinalizeWorkSheet(ISheet worksheet)
        {
            if (worksheet != null)
            {
                var hssfSheet = worksheet as HSSFSheet;

                if (hssfSheet != null)
                {
                    hssfSheet.SetAutoFilter(new CellRangeAddress(0, _rowIndex - 1, 0, _splitColumns.Length - 1));
                }

                ForEachColumn((i, f) =>
                    {
                        worksheet.AutoSizeColumn(i);
                        // Units are 256 per character.
                        // Maximum width is 255 characters.
                        var width = Math.Min(worksheet.GetColumnWidth(i) + 1024, 255 * 256);
                        worksheet.SetColumnWidth(i, width);
                    });
            }
        }

        /// <summary>
        /// 自动适应列宽
        /// </summary>
        /// <param name="sheet">需要自适应列宽的sheet表</param>
        /// <param name="columnCount">列数</param>
        public void AutoFitColumnWidth(ISheet sheet, int columnCount)
        {
            //列宽自适应，只对英文和数字有效
            for (int ci = 0; ci < columnCount; ci++)
            {
                sheet.AutoSizeColumn(ci);
            }
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = 0; columnNum < columnCount; columnNum++)
            {
                int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
                {
                    if (rowNum == 0 || rowNum == sheet.LastRowNum - 1 || rowNum == sheet.LastRowNum / 2)
                    {
                        IRow currentRow;

                        //当前行未被使用过
                        if (sheet.GetRow(rowNum) == null)
                        {
                            currentRow = sheet.CreateRow(rowNum);
                        }
                        else
                        {
                            currentRow = sheet.GetRow(rowNum);
                        }

                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                            if (columnWidth < length)
                            {
                                columnWidth = length;
                            }
                        }
                    }
                }

                if (columnWidth > 255)
                {
                    columnWidth = 255;
                }

                sheet.SetColumnWidth(columnNum, columnWidth * 256);
            }
        }

    private  void CreateHeadRow(GridView gr, HSSFWorkbook workbook, ISheet sheet)
    {
        ICellStyle titleStyle = workbook.CreateCellStyle();
        var styleHeader = CreateHeaderStyle(workbook,
                HSSFColor.WHITE.index, HSSFColor.GREEN.index);

        IRow row = sheet.CreateRow(0);
        ICell c1 = row.CreateCell(0);
        ICell c2 = row.CreateCell(1);
        ICell c3 = row.CreateCell(2);
        int i = 0;
        foreach (TableCell cell in gr.HeaderRow.Cells)
        {
            ICell iCell = row.CreateCell(i);
            iCell.SetCellValue(cell.Text);
            iCell.CellStyle = styleHeader;
            sheet.AutoSizeColumn(i);
            i++;
        }

    }

        private int WriteReportToPage(ISheet summaryPage, DataTable table, int startingRow, Boolean autoSize = true)
        {
            if (table == null)
                return startingRow;
            
            var iRunningColumn = 0;



            var cellStyle = summaryPage.Workbook.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thick;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;

            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;

            var failCellStyle = summaryPage.Workbook.CreateCellStyle();
            failCellStyle.FillPattern = FillPattern.SolidForeground;
            failCellStyle.FillForegroundColor = IndexedColors.Red.Index;

            IRow excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
            ICell excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);

            excelCell.SetCellValue(table.TableName);
            startingRow++;

            excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
            foreach (DataColumn tCol in table.Columns)
            {
                if (tCol.AutoIncrement)
                    continue;
                var runCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
                iRunningColumn++;
                runCell.SetCellValue(tCol.Caption);
                runCell.CellStyle = cellStyle;
            }

            startingRow++;
            var writer = new ExcelCellVisualValue(summaryPage.Workbook);
            foreach (DataRow row in table.Rows)
            {
                excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
                startingRow++;
                iRunningColumn = -1;
                foreach (DataColumn tCol in table.Columns)
                {
                    if (tCol.AutoIncrement)
                        continue;
                    iRunningColumn++;
                    if (row[tCol] == DBNull.Value)
                        continue;
                    excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);

                    // ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
                    if (row[tCol] is IVisualValue)
                    {
                        writer.SetCell(excelCell, (IVisualValue) row[tCol]);
                    }
                    else
                    {
                        switch (tCol.DataType.Name)
                        {
                            case "String":
                                excelCell.SetCellValue((string) row[tCol]);
                                break;
                            case "Int32":
                                excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
                                break;
                            default:
                                excelCell.SetCellValue((string) row[tCol]);
                                break;
                        }
                    }
                }
            }

            if (!autoSize) 
                return startingRow + 1;
            // sets all used columns to autosize
            for (int irun = 0; irun < iRunningColumn; irun++)
            {
                summaryPage.AutoSizeColumn(irun);
            }
            return startingRow + 1;
        }

    private static void SetAutoSizeColumn(ISheet all, string[] headers, string[] names, string[] patterns)
    {
      foreach (var name in names)
      {
        var index = System.Array.IndexOf(headers, name);
        if (index >= 0)
        {
          all.AutoSizeColumn(index);
        }
      }

      foreach (var pattern in patterns)
      {
        for (int index = 0; index < headers.Length; index++)
        {
          if (Regex.Match(headers[index], pattern).Success)
          {
            all.AutoSizeColumn(index);
          }
        }
      }
    }

        private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
        {
            if (fields != null)
            {
                IRow rowColumn = sheet.CreateRow(0);
                for (int i = 0; i < fields.Count; i++)
                {
                    ICell cell = rowColumn.CreateCell(i);
                    string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
                    cell.SetCellValue(colName);
                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    if (fields[i].IsRequired)
                    {
                        cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
                    }
                    else
                    {
                        cellStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                    }
                    cellStyle.FillPattern = FillPattern.SolidForeground;
                    cell.CellStyle = cellStyle;
                    sheet.AutoSizeColumn(i);

                    switch (fields[i].DataType)
                    {
                        case FieldInfo.DATATYPE_LIST:
                        case FieldInfo.DATATYPE_LIST_SQL:
                            string lstFormulaName = fields[i].FieldName + "fn";
                            int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
                            if (dsEndIndex > dsStartIndex)
                            {
                                IName name = sheet.Workbook.CreateName();
                                name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
                                name.NameName = lstFormulaName;
                                name.SheetIndex = sheetIndex;
                                CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
                                IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
                                IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
                                IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
                                sheet.AddValidationData(validation);
                                dsStartIndex = dsEndIndex;
                            }
                            break;
                    }
                }
            }
        }

 private void PopulateData(ref ISheet sheet, DataTable dt)
 {
     for (int rowIdx = 1; rowIdx < dt.Rows.Count; rowIdx++)
     {
         var row = sheet.CreateRow(rowIdx);
         Int32 columnLength = dt.Columns.Count;
         for (int colIdx = 0; colIdx < columnLength; colIdx++)
         {
             row.CreateCell(colIdx).SetCellValue(dt.Rows[rowIdx][colIdx].ToString());
             sheet.AutoSizeColumn(colIdx);
         }
     }
 }

        /// <summary>
        /// 校验数据是否正常
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="outputStream">输出流</param>
        /// <param name="sheet">数据sheet</param>
        /// <param name="userInfo">用户信息</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
        /// <returns>ImportResult</returns>
        public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary<string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary<string, ImportVerify> DictColumnFields)
        {
            IWorkbook wb = sheet.Workbook;
            ImportResult result = new ImportResult();

            string[] arrErrorMsg = null;
            string errorMsg = string.Empty;
            int columnCount = dt.Columns.Count;
            string columnName = string.Empty;
            ImportVerify objVerify = null;
            ImportVerifyParam objVerifyParam = new ImportVerifyParam { DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0 };
            DataRow row = null;
            object objExtra = null;
            bool isCorrect = true;

            //错误数据行样式
            var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
            ICell errorCell = null;
            IRow sheetRow = null;

            for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
            {
                row = dt.Rows[i];
                arrErrorMsg = new string[columnCount];
                for (int j = 0; j < columnCount; j++)
                {
                    columnName = dt.Columns[j].ColumnName;
                    if (DictColumnFields.TryGetValue(columnName, out objVerify))
                    {
                        if (objVerify.VerifyFunc != null)
                        {
                            objVerifyParam.CellValue = row[j];
                            objVerifyParam.ColumnIndex = j;
                            objVerifyParam.RowIndex = i;
                            objVerifyParam.ColName = objVerify.ColumnName;
                            if (extraInfo != null)
                            {
                                extraInfo.TryGetValue(columnName, out objExtra);
                            }
                            arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
                        }
                    }
                }
                errorMsg = string.Join("，", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
                if (!string.IsNullOrEmpty(errorMsg))
                {
                    isCorrect = false;
                    //设置错误信息
                    sheetRow = sheet.GetRow(StartRowIndex + 1 + i);
                    errorCell = sheetRow.GetCell(columnCount);
                    if (errorCell == null)
                    {
                        errorCell = sheetRow.CreateCell(columnCount);
                    }
                    errorCell.CellStyle = cellErrorStyle;
                    errorCell.SetCellValue(errorMsg);
                }
            }

            //输出错误信息模版
            if (!isCorrect)
            {
                sheetRow = sheet.GetRow(StartRowIndex);
                errorCell = sheetRow.GetCell(columnCount);
                if (errorCell == null)
                {
                    errorCell = sheetRow.CreateCell(columnCount);
                }
                ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
                ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb);
                IFont font = style.GetFont(wb);
                IFont copyfont = copyStyle.GetFont(wb);
                font.FontHeight = copyfont.FontHeight;
                font.FontName = copyfont.FontName;
                style.FillForegroundColor = copyStyle.FillForegroundColor;
                style.BorderBottom = copyStyle.BorderBottom;
                style.BorderLeft = copyStyle.BorderLeft;
                style.BorderRight = copyStyle.BorderRight;
                style.BorderTop = copyStyle.BorderTop;
                errorCell.CellStyle = style;
                errorCell.SetCellValue("错误信息");

                //自适应列宽度
                sheet.AutoSizeColumn(columnCount);
                int width = sheet.GetColumnWidth(columnCount) + 2560;
                sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);

                result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
            }
            else
//.........这里部分代码省略.........