C# ISheet.SetColumnWidth方法代码示例

 static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
 {
     sheet.SetColumnWidth(0, 6000);
     sheet.SetColumnWidth(1, 6000);
     sheet.SetColumnWidth(2, 3600);
     sheet.SetColumnWidth(3, 3600);
     sheet.SetColumnWidth(4, 2400);
     sheet.SetColumnWidth(5, 2400);
     sheet.SetColumnWidth(6, 2400);
     sheet.SetColumnWidth(7, 2400);
     sheet.SetColumnWidth(8, 2400);
     IRow row = sheet.CreateRow(0);
     ICellStyle style = wb.CreateCellStyle();
     IFont font = wb.CreateFont();
     font.Boldweight = (short)FontBoldWeight.BOLD;
     style.SetFont(font);
     WriteHeaderCell(row, 0, "Raw Long Bits A", style);
     WriteHeaderCell(row, 1, "Raw Long Bits B", style);
     WriteHeaderCell(row, 2, "Value A", style);
     WriteHeaderCell(row, 3, "Value B", style);
     WriteHeaderCell(row, 4, "Exp Cmp", style);
     WriteHeaderCell(row, 5, "LT", style);
     WriteHeaderCell(row, 6, "EQ", style);
     WriteHeaderCell(row, 7, "GT", style);
     WriteHeaderCell(row, 8, "Check", style);
 }

        // 表格头部设置
        private static void HeaderSetting(ExportExcelPageMaker configData, IWorkbook workbook, ISheet sheet)
        {
            IRow dataRow = sheet.CreateRow(0);

            int index = 0;
            foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
            {
                ICell cell = dataRow.CreateCell(index);
                ICellStyle styleHeader = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Header);
                cell.SetCellValue(fieldInfo.DisplayName);
                cell.CellStyle = styleHeader;
                int colWith = GetColWidth(fieldInfo);
                sheet.SetColumnWidth(index, colWith << 8);
                index++;
            }
        }

        private static void DrawSheet1(ISheet sheet1)
        {
            // Create a row and size one of the cells reasonably large.
            IRow row = sheet1.CreateRow(2);
            row.Height = ((short)2800);
            row.CreateCell(1);
            sheet1.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();

            // Draw some lines and an oval.
            DrawLinesToCenter(patriarch);
            DrawManyLines(patriarch);
            DrawOval(patriarch);
            DrawPolygon(patriarch);

            // Draw a rectangle.
            HSSFSimpleShape rect = patriarch.CreateSimpleShape(new HSSFClientAnchor(100, 100, 900, 200, (short)0, 0, (short)0, 0));
            rect.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
        }

 static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
 {
     sheet.SetColumnWidth(0, 3000);
     sheet.SetColumnWidth(1, 6000);
     sheet.SetColumnWidth(2, 6000);
     sheet.SetColumnWidth(3, 6000);
     sheet.SetColumnWidth(4, 6000);
     sheet.SetColumnWidth(5, 1600);
     sheet.SetColumnWidth(6, 20000);
     IRow row = sheet.CreateRow(0);
     ICellStyle style = wb.CreateCellStyle();
     IFont font = wb.CreateFont();
     WriteHeaderCell(row, 0, "Value", style);
     font.Boldweight = (short)FontBoldWeight.Bold;
     style.SetFont(font);
     WriteHeaderCell(row, 1, "Raw Long Bits", style);
     WriteHeaderCell(row, 2, "JDK Double Rendering", style);
     WriteHeaderCell(row, 3, "Actual Rendering", style);
     WriteHeaderCell(row, 4, "Expected Rendering", style);
     WriteHeaderCell(row, 5, "Match", style);
     WriteHeaderCell(row, 6, "Java Metadata", style);
 }

    private void adjustcolum(ISheet sheet)
    {
        for (int columnNum = 0; columnNum < dcs.Count; columnNum++)
        {
            int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
            for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
            {
                IRow currentRow = sheet.GetRow(rowNum);
                ICell currentCell = currentRow.GetCell(columnNum);

                int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
                if (columnWidth < length + 1)
                {
                    columnWidth = length + 1;
                }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
            }
            sheet.SetColumnWidth(columnNum, columnWidth*256);
        }
    }

        /// <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);
            }
        }

 /// <summary>
 /// 初始化样式
 /// </summary>
 private void InitSheetWidth(int width, ISheet sheet, int colCount)
 {
     for (int i = 0; i < colCount; i++)
     {
         sheet.SetColumnWidth(i, width);
     }
 }

        private void SetWidth(ISheet sheet)
        {
            var widthDix = 2760;
            sheet.SetColumnWidth(1, widthDix);
            sheet.SetColumnWidth(2, widthDix);
            sheet.SetColumnWidth(3, widthDix);
            sheet.SetColumnWidth(4, widthDix);
            sheet.SetColumnWidth(5, widthDix);

            sheet.SetColumnWidth(9, widthDix);
            sheet.SetColumnWidth(10, widthDix);
            sheet.SetColumnWidth(11, widthDix);
            sheet.SetColumnWidth(12, widthDix);
            sheet.SetColumnWidth(13, widthDix);
            sheet.SetColumnWidth(14, widthDix);
            sheet.SetColumnWidth(15, widthDix);

            sheet.SetColumnWidth(20, widthDix);
            sheet.SetColumnWidth(21, widthDix);
            sheet.SetColumnWidth(22, widthDix);
            sheet.SetColumnWidth(23, widthDix);
        }

        /// <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
//.........这里部分代码省略.........

        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);
                    });
            }
        }

 private void SetDetailValue(string leftTitle, string[] leftContent, Dictionary<int, double[]> values, ref int startRowNum, ISheet sheet1, ICellStyle cellstyleContent, ICellStyle cellstyleTitle, ref int startKey, ICellStyle cellstyleLeft)
 {
     var oriStartRowNum = startRowNum;
        for (int k = 0;k<leftContent.Length;k++)
        {
        IRow row = sheet1.CreateRow(startRowNum);
        for (int i = 0; i < 6; i++)
        {
            if (i == 0)
            {
                ICell cell = row.CreateCell(i);
                cell.CellStyle = cellstyleTitle;
                cell.SetCellValue(leftTitle);
            }
            else if (i == 1)
            {
                ICell cell = row.CreateCell(i);
                cell.CellStyle = cellstyleLeft;
                cell.SetCellValue(leftContent[k]);
            }
            else
            {
                ICell cell = row.CreateCell(i);
                cell.CellStyle = cellstyleContent;
                sheet1.SetColumnWidth(i, 15 * 256);
                cell.SetCellValue(values[startKey][i - 1]+"人");
            }
        }
        startKey++;
        startRowNum++;
        }
        SetCellRangeAddress(sheet1, oriStartRowNum, startRowNum-1, 0, 0);
 }

 private void SetColumnSize(ISheet worksheet)
 {
     ForEachColumn((i, f) => worksheet.SetColumnWidth(i, f.Width));
 }

        private static void DrawSheet3(ISheet sheet3)
        {
            // Create a row and size one of the cells reasonably large
            IRow row = sheet3.CreateRow(2);
            row.HeightInPoints = 140;
            row.CreateCell(1);
            sheet3.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet3.CreateDrawingPatriarch();

            // Create a shape group.
            HSSFShapeGroup group = patriarch.CreateGroup(
                    new HSSFClientAnchor(0, 0, 900, 200, (short)2, 2, (short)2, 2));

            // Create a couple of lines in the group.
            HSSFSimpleShape shape1 = group.CreateShape(new HSSFChildAnchor(3, 3, 500, 500));
            shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
            ((HSSFChildAnchor)shape1.Anchor).SetAnchor((short)3, 3, 500, 500);
            HSSFSimpleShape shape2 = group.CreateShape(new HSSFChildAnchor((short)1, 200, 400, 600));
            shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

        }

        private static void DrawSheet2(ISheet sheet2)
        {
            // Create a row and size one of the cells reasonably large.
            IRow row = sheet2.CreateRow(2);
            row.CreateCell(1);
            row.HeightInPoints = 240;
            sheet2.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet2.CreateDrawingPatriarch();

            // Draw a grid in one of the cells.
            DrawGrid(patriarch);
        }

        private int MSWriteToSheet1(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
        {
            if (data == null || data.Count == 0)
                return -1;

            List<int> setRow = new List<int>();
            setRow.Add(3);
            setRow.Add(4);
            setRow.Add(5);
            setRow.Add(6);
            setRow.Add(7);

            ICellStyle fontBold = this.setFontBold();
            ICellStyle cellBorder = this.setCellBorder();

            foreach (var r in data)
            {
                IRow row = sheet.CreateRow(r.Key);

                for (int col = 0; col < r.Value.Count(); col++)
                {
                    ICell cell = row.CreateCell(col);
                    cell.SetCellValue(r.Value[col].CellValue);
                    ////给加盟费等标签字体加粗
                    if (r.Value[col].IsFontBold)
                    {
                        cell.CellStyle = fontBold;
                    }

                    if (r.Value[col].HasCellBorder)
                    {
                        cell.CellStyle = cellBorder;
                    }
                }
                ////合并单元格并设置边框
                if (setRow.Contains(r.Key))
                {
                    CellRangeAddress region1 = new CellRangeAddress(r.Key, r.Key, 1, 2);
                    CellRangeAddress region2 = new CellRangeAddress(r.Key, r.Key, 5, 6);
                    sheet.AddMergedRegion(region1);
                    sheet.AddMergedRegion(region2);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                }
            }
            //设置合并单元格的长度，需全部显示文字
            sheet.SetColumnWidth(2, 30 * 256);
            sheet.SetColumnWidth(6, 30 * 256);

            return 0;
        }