public void TestMergedCells() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow row = sheet.CreateRow(0); sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1")); ICell cell0 = row.CreateCell(0); cell0.SetCellValue("Apache Software Foundation"); int defaulWidth = sheet.GetColumnWidth(0); sheet.AutoSizeColumn(0); // column is unChanged if merged regions are ignored (Excel like behavior) Assert.AreEqual(defaulWidth, sheet.GetColumnWidth(0)); sheet.AutoSizeColumn(0, true); Assert.IsTrue(sheet.GetColumnWidth(0) > defaulWidth); }
public void TestAutoSize_bug506819() { IWorkbook wb = _testDataProvider.CreateWorkbook(); BaseTestSheetAutosizeColumn.FixFonts(wb); ISheet sheet = wb.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); ICell cell0 = row.CreateCell(0); String longValue = "www.hostname.com, www.hostname.com, " + "www.hostname.com, www.hostname.com, www.hostname.com, " + "www.hostname.com, www.hostname.com, www.hostname.com, " + "www.hostname.com, www.hostname.com, www.hostname.com, " + "www.hostname.com, www.hostname.com, www.hostname.com, " + "www.hostname.com, www.hostname.com, www.hostname.com, www.hostname.com"; cell0.SetCellValue(longValue); sheet.AutoSizeColumn(0); Assert.AreEqual(255 * 256, sheet.GetColumnWidth(0)); // maximum column width is 255 characters sheet.SetColumnWidth(0, sheet.GetColumnWidth(0)); // Bug 506819 reports exception at this point }
/// <summary>获得列宽</summary> public static double GetColumnWidthInPoints(this ISheet sheet, int colIndex) { var width = sheet.GetColumnWidth(colIndex); if (width == 0) { width = sheet.DefaultColumnWidth; if (width == 0) { return(10.0); } } return(width / 256.0); }
protected void setAutoSizeColumn(ISheet tb_, int columeCount_) { for (int i = 0; i < columeCount_; i++) { tb_.AutoSizeColumn(i); } for (int i = 0; i < columeCount_; i++) { int autoWidth = tb_.GetColumnWidth(i); int targetWidth = (int)((float)autoWidth * 1.1); tb_.SetColumnWidth(i, targetWidth); } }
public ExcelSheetLoader(ISheet sheet) { this.sheet = sheet; rowStart = sheet.FirstRowNum; rowEnd = sheet.LastRowNum; maxColumnWidth = 0; for (var i = rowStart; i <= rowEnd; i++) { var currentColumn = sheet.GetColumnWidth(i); maxColumnWidth = Math.Max(maxColumnWidth, currentColumn); } }
/// <summary> /// 宽度自适应 /// </summary> /// <param name="sheet"></param> /// <param name="cols"></param> private static void AutoColumnWidth(ISheet sheet, int cols) { for (var col = 0; col <= cols; col++) { sheet.AutoSizeColumn(col); //自适应宽度,但是其实还是比实际文本要宽 var columnWidth = sheet.GetColumnWidth(col) / 256; //获取当前列宽度 for (var rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); var cell = row?.GetCell(col); var contextLength = Encoding.UTF8.GetBytes(cell?.ToString() ?? string.Empty).Length;//获取当前单元格的内容宽度 columnWidth = columnWidth < contextLength ? contextLength : columnWidth; } sheet.SetColumnWidth(col, columnWidth * 220); } }
/// <summary> /// 根据单元格内容重新设置列宽 /// </summary> /// <param name="sheet"></param> /// <param name="cell"></param> public static void ReSizeColumnWidth(ISheet sheet, ICell cell) { int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256; const int maxLength = 255 * 256; if (cellLength > maxLength) { cellLength = maxLength; } int colWidth = sheet.GetColumnWidth(cell.ColumnIndex); if (colWidth < cellLength) { sheet.SetColumnWidth(cell.ColumnIndex, cellLength); } }
public void AutoColumnWidth(ISheet sheet, int cols) { for (int col = 0; col < cols; col++) { sheet.AutoSizeColumn(col); //自适应宽度,但是其实还是比实际文本要宽 int columnWidth = sheet.GetColumnWidth(col) / 256; //获取当前列宽度 for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++) { IRow row = sheet.GetRow(rowIndex); ICell cell = row.GetCell(col); int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度 columnWidth = columnWidth < contextLength ? contextLength : columnWidth; } sheet.SetColumnWidth(col, columnWidth * 200); } }
/// <summary> /// 表头 /// </summary> /// <param name="dataTable"></param> /// <param name="TableHead"></param> /// <param name="sheet"></param> /// <param name="xssfworkbook"></param> /// <param name="mergeCount"></param> public void DataList(DataTable dataTable, List <string> TableHead, ISheet sheet, XSSFWorkbook xssfworkbook, int mergeCount) { //设置边框样式 ICellStyle style = xssfworkbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; //自动换行 style.WrapText = true; //设置水平对齐 style.Alignment = HorizontalAlignment.Center; //设置垂直对齐 style.VerticalAlignment = VerticalAlignment.Center; IFont font = xssfworkbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontHeightInPoints = 12; style.SetFont(font); DataColumn dc = null; //赋值给dc,是便于对每一个datacolumn的操作 dc = dataTable.Columns.Add("序号", Type.GetType("System.Int32")); dc.AutoIncrement = true; //自动增加 dc.AutoIncrementSeed = 1; //起始为1 dc.AutoIncrementStep = 1; //步长为1 dc.AllowDBNull = false; // foreach (var item in TableHead) { dc = dataTable.Columns.Add(item, Type.GetType("System.String")); } //表头 IRow row = sheet.CreateRow(mergeCount); row.Height = 30 * 20; for (int i = 0; i < dataTable.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dataTable.Columns[i].ColumnName); //自动适应宽度 sheet.AutoSizeColumn(i); sheet.SetColumnWidth(i, sheet.GetColumnWidth(i)); cell.CellStyle = style; } }
private static void SetColumnWidth2(this ISheet sheet, int index, int textLength) { var colWidth = (textLength + 5) * 256; var width = sheet.GetColumnWidth(index); if (width < colWidth && width != 20000) { if (colWidth < 20000) { sheet.SetColumnWidth(index, colWidth); } else { sheet.SetColumnWidth(index, 20000); } } }
/// <summary> /// 自动列宽 /// </summary> /// <param name="ISheet1"></param> /// <param name="count"></param> /// <param name="startColumn"></param> public static void AutoSizeColumn(this ISheet ISheet1, int count, int startColumn) { for (int j = 0; j < count; j++) { try { int column = j + startColumn; ISheet1.AutoSizeColumn(j + startColumn);//这里会出现空指针异常,可能是越界了。 int width = ISheet1.GetColumnWidth(column); ISheet1.SetColumnWidth(column, width + 1 * 256); } catch (Exception) { //throw; } } }
private static void SetColumnWidth(ISheet sheet, ICell cell) { const int maxLength = 60 * 256; int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 2) * 256; if (cellLength > maxLength) //当单元格内容超过30个中文字符(英语60个字符)宽度,则强制换行 { cellLength = maxLength; cell.CellStyle.WrapText = true; } int colWidth = sheet.GetColumnWidth(cell.ColumnIndex); if (colWidth < cellLength) { sheet.SetColumnWidth(cell.ColumnIndex, cellLength); } }
/// <summary> /// 删除工作表中指定列,并移动右侧列到左侧列 /// </summary> /// <param name="sheet">工作表</param> /// <param name="columnIndex">列索引</param> public static void DeleteColumn(this ISheet sheet, int columnIndex) { int maxColumn = 0; for (int i = 0; i < sheet.LastRowNum + 1; i++) { IRow row = sheet.GetRow(i); if (row == null) { continue; } int lastColumn = row.LastCellNum; if (lastColumn > maxColumn) { maxColumn = lastColumn; } if (lastColumn < columnIndex) { continue; } for (int x = columnIndex + 1; x < lastColumn + 1; x++) { ICell oldCell = row.GetCell(x - 1); if (oldCell != null) { row.RemoveCell(oldCell); } ICell nextCell = row.GetCell(x); if (nextCell != null) { ICell newCell = row.CreateCell(x - 1, nextCell.CellType); CloneCell(oldCell, newCell); } } } // 调整列宽 for (int c = columnIndex; c < maxColumn; c++) { sheet.SetColumnWidth(c, sheet.GetColumnWidth(c + 1)); } }
//private static void WriteBoldHead(ISheet sheet, string[] colHeads) //{ // IRow row = sheet.CreateRow(0); // IFont f = sheet.Workbook.CreateFont(); // f.Boldweight = (short)FontBoldWeight.BOLD; // ICellStyle style = GetICellStyle(sheet.Workbook, CellTypeEnum.Cell, false, f); //sheet.Workbook.CreateCellStyle(); // for (int i = 0; i < colHeads.Length; i++) // { // ////only care head, cells too big memory // sheet.AutoSizeColumn(i); // var wid = sheet.GetColumnWidth(i); // sheet.SetColumnWidth(i, wid + 2000); // ICell cell = row.CreateCell(i, CellType.STRING); // cell.CellStyle = style; // cell.SetCellValue(colHeads[i]); // } // row.RowStyle = style; //} private static void WriteHead(ISheet sheet, string[] colHeads) { IRow row = sheet.CreateRow(0); //row.Height = 300; for (int i = 0; i < colHeads.Length; i++) { ICell cell = row.CreateCell(i, CellType.STRING); //cell.CellStyle = headStyle; cell.SetCellValue(colHeads[i]); ////only care head, cells too big memory sheet.AutoSizeColumn(i); var wid = sheet.GetColumnWidth(i); sheet.SetColumnWidth(i, wid + 2000); } }
private void SetColumnWidth(ISheet sheet, List <ExportColumnProperty> columnProperties) { foreach (var item in columnProperties) { sheet.AutoSizeColumn(item.ColumnIndex); var width = (int)(sheet.GetColumnWidth(item.ColumnIndex) * 1.2); if (item.MinWidth > 0 && width < item.MinWidth) { width = item.MinWidth; } else if (item.MaxWidth > 0 && width > item.MaxWidth) { width = item.MaxWidth; } sheet.SetColumnWidth(item.ColumnIndex, width); } }
public void CrearTabla(List <string> Columns, List <string> Rows, int X, int Y, string headerText, string tableName, string hoja) { ISheet Sheet1 = hssfworkbook.GetSheet(hoja) != null?hssfworkbook.GetSheet(hoja) : hssfworkbook.CreateSheet(hoja); ICell Title = Sheet1.GetRow(Y) != null ? (Sheet1.GetRow(Y).GetCell(X) != null ? Sheet1.GetRow(Y).GetCell(X) : Sheet1.GetRow(Y).CreateCell(X)) : Sheet1.CreateRow(Y).CreateCell(X); Title.CellStyle = EstTitulos; Title.SetCellValue(headerText); Sheet1.AddMergedRegion(new CellRangeAddress(Y, Y + 1, X, X + Columns.Count - 1)); /* CAMPOS */ IRow RowColumns = Sheet1.GetRow(Y + 2) != null?Sheet1.GetRow(Y + 2) : Sheet1.CreateRow(Y + 2); for (int c = 0; c < Columns.Count; c++) { ICell CellColumn = RowColumns.GetCell(X + c) != null?RowColumns.GetCell(X + c) : RowColumns.CreateCell(X + c); CellColumn.CellStyle = EstSubtitulos; CellColumn.SetCellValue(Columns[c]); Sheet1.AutoSizeColumn(CellColumn.ColumnIndex); Sheet1.SetColumnWidth(CellColumn.ColumnIndex, int.Parse((Sheet1.GetColumnWidth(CellColumn.ColumnIndex) * 2).ToString())); } for (int i = 0; i < Rows.Count; i++) { IRow CurrentRow = Sheet1.GetRow(Y + i + 3) != null?Sheet1.GetRow(Y + i + 3) : Sheet1.CreateRow(Y + i + 3); for (int j = 0; j < Columns.Count; j++) { ICell currentCell = CurrentRow.GetCell(X + j) != null?CurrentRow.GetCell(X + j) : CurrentRow.CreateCell(X + j); currentCell.CellStyle = EstCeldas; if (j == 0) { currentCell.CellStyle = EstSubtitulos; currentCell.SetCellValue(Rows[i].ToString()); } } } IName name = hssfworkbook.CreateName(); name.NameName = tableName; //name.RefersToFormula = "'" + hoja + "'" + "!$" + getChar(X + 1) + "$" + (Y + 3) + ":$" + getChar(X + Columns.Count) + "$" + (Y + 3 + Rows.Count); name.RefersToFormula = "'" + hoja + "'" + "!$" + ColumnIndexToColumnLetter(X + 1) + "$" + (Y + 3) + ":$" + ColumnIndexToColumnLetter(X + Columns.Count) + "$" + (Y + 3 + Rows.Count); }
public void StringCells() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); FixFonts(workbook); ISheet sheet = workbook.CreateSheet(); TrackColumnsForAutoSizingIfSXSSF(sheet); IRow row = sheet.CreateRow(0); IFont defaultFont = workbook.GetFontAt((short)0); ICellStyle style1 = workbook.CreateCellStyle(); IFont font1 = workbook.CreateFont(); font1.FontHeight = (/*setter*/ (short)(2 * defaultFont.FontHeight)); style1.SetFont(font1); row.CreateCell(0).SetCellValue("x"); row.CreateCell(1).SetCellValue("xxxx"); row.CreateCell(2).SetCellValue("xxxxxxxxxxxx"); row.CreateCell(3).SetCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines row.CreateCell(4).SetCellValue("Software Foundation"); ICell cell5 = row.CreateCell(5); cell5.SetCellValue("Software Foundation"); cell5.CellStyle = (/*setter*/ style1); // same as in column 4 but the font is twice larger than the default font for (int i = 0; i < 10; i++) { sheet.AutoSizeColumn(i); } Assert.IsTrue(2 * sheet.GetColumnWidth(0) < sheet.GetColumnWidth(1)); // width is roughly proportional to the number of characters Assert.IsTrue(2 * sheet.GetColumnWidth(1) < sheet.GetColumnWidth(2)); Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(3)); Assert.IsTrue(sheet.GetColumnWidth(5) > sheet.GetColumnWidth(4)); //larger font results in a wider column width workbook.Close(); }
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); } }
private static void SetColumnWidth(ISheet st) { int cellNum = st.GetRow(0).LastCellNum; //for (int i = 0; i < cellNum; i++) //{ // st.AutoSizeColumn(i); //} for (int i = 0; i <= cellNum; i++) { st.AutoSizeColumn(i); } //獲取當前列的寬度,然後對比本列的長度,取最大值 for (int columnNum = 0; columnNum <= cellNum; columnNum++) { int columnWidth = st.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= st.LastRowNum; rowNum++) { IRow currentRow; //當前行未被使用過 if (st.GetRow(rowNum) == null) { currentRow = st.CreateRow(rowNum); } else { currentRow = st.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; } } } st.SetColumnWidth(columnNum, columnWidth * 256); } }
/// <summary> /// 设置列头信息及样式 /// </summary> /// <param name="sheetName">表名</param> /// <param name="colNames">列集合</param> /// <param name="row">行号</param> public static void SetLineRow(string sheetName, string[] colNames, int row) { ISheet sheet = workbook.GetSheet(sheetName); IRow headerRow = sheet.CreateRow(row); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = ((short)10); font.Boldweight = 700; font.FontName = "宋体"; headStyle.SetFont(font); //headStyle.FillBackgroundColor = HSSFColor.Blue.Index; headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = HSSFColor.Orange.Index; headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; for (int i = 0; i < colNames.Length; i++) { headerRow.CreateCell(i).SetCellValue(colNames[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 //setColumnWidth(colindex, width):设置第(colindex+1)列宽度为width个字符 //*@param colindex - 要设置的列(从0开始) //*@param width - 以字符宽度的1 / 256为单位的宽度 //sheet.SetColumnWidth(i, (colNames[i].Length + 1) * 256); int colWidth = sheet.GetColumnWidth(i) / 256; //获取当前列宽度 int length = Encoding.UTF8.GetBytes(colNames[i].ToString()).Length; //获取当前单元格的内容宽度 //若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符 if (colWidth < length + 1) { colWidth = length + 1; } sheet.SetColumnWidth(i, colWidth * 256); } }
/// <summary> /// 自动设置中文的Excel列宽 /// </summary> /// <param name="sheet">Excel表</param> /// <param name="startRowNum">需要计算每行每列中文Cell宽度的起始行数</param> /// <param name="endRowNum">需要计算每行每列中文Cell宽度的结束行数</param> private static void AutoSizeChineseCols(ISheet sheet, int startRowNum, int endRowNum, int shift) { if (sheet.PhysicalNumberOfRows > 0) { int maxColumn = sheet.GetRow(0).LastCellNum; //获取当前列的宽度,然后对比本列的长度,取最大值 for (int columnNum = 0; columnNum <= maxColumn; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256 + shift; for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) { 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); string curcell = currentCell.ToString(); int length = Encoding.Default.GetBytes(curcell).Length; if (columnWidth < length) { columnWidth = length + shift; if (columnWidth > 255) { columnWidth = 255; } } } } sheet.SetColumnWidth(columnNum, columnWidth * 256); } } }
/// <summary> /// 根据单元格内容重新设置列宽 /// </summary> /// <param name="sheet"></param> /// <param name="cell"></param> /// <param name="cellType"></param> private static void ReSizeColumnWidth(ISheet sheet, ICell cell, Type cellType) { int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 2) * 256; const int maxLength = 60 * 256; //255 * 256; if (cellLength > maxLength) //当单元格内容超过30个中文字符(英语60个字符)宽度,则强制换行 { cellLength = maxLength; cell.CellStyle.WrapText = true; } int colWidth = sheet.GetColumnWidth(cell.ColumnIndex); if (colWidth < cellLength) { sheet.SetColumnWidth(cell.ColumnIndex, cellLength); } else if (cellType == typeof(DateTime)) { sheet.SetColumnWidth(cell.ColumnIndex, colWidth + 10); } }
/// <summary> /// /// </summary> /// <param name="sheet"></param> /// <param name="columnNum"></param> private void AutoSizeColumn(ISheet sheet, int columnNum) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum) == null? sheet.CreateRow(rowNum) : sheet.GetRow(rowNum); if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = System.Text.Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } sheet.SetColumnWidth(columnNum, columnWidth * 256); }
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 static void CreateSheetHeader(List <Column> columns, ISheet sheet) { var row = sheet.CreateRow(0); var standardStyle = CreateHeaderStyle(sheet.Workbook); var mandatoryStyle = CreateHeaderMandatoryStyle(sheet.Workbook); var charWidth = 256; var minimalColumnWidth = charWidth * 18; for (int i = 0; i < columns.Count; i++) { var cell = row.CreateCell(i); cell.SetCellValue(columns[i].Name); cell.CellStyle = columns[i].IsMandatory ? mandatoryStyle : standardStyle; sheet.AutoSizeColumn(i); if (sheet.GetColumnWidth(i) < minimalColumnWidth) { sheet.SetColumnWidth(i, minimalColumnWidth); } } }
private static void _writeToSheet(ISheet sheet, IEnumerable <string> lst) { IRow row; if (sheet.GetRow(0) == null) { row = sheet.CreateRow(0); } else { row = sheet.CreateRow(sheet.LastRowNum + 1); } int idx = 0; foreach (var s in lst) { var cell = row.CreateCell(idx); double val; if (double.TryParse(s, out val)) { cell.SetCellValue(val); } else { cell.SetCellValue(s); } int curwidth = sheet.GetColumnWidth(idx); sheet.SetColumnWidth(idx, Math.Max(curwidth, _calcSheetWidth(s))); if (row.RowNum == 0) { cell.CellStyle = sheet.Workbook.GetCellStyleAt((short)(sheet.Workbook.NumCellStyles - 1)); } row.Height = 24 * 15; idx++; } }
/// <summary> /// 设置列自动适应宽度 /// </summary> /// <param name="sheet"></param> public void AutoColumnWidth(ISheet sheet, int maxColumnNum) { #region //获取当前列的宽度,然后对比本列的长度,取最大值 for (int columnNum = 0; columnNum <= maxColumnNum; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { 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 > 253) { columnWidth = 253; } sheet.SetColumnWidth(columnNum, (columnWidth + 2) * 256); } #endregion }
private void AutoSizeColumn(ISheet sheet, int columnNum) { int num = sheet.GetColumnWidth(columnNum) / 256; for (int i = 0; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { ICell cell = row.GetCell(columnNum); if (cell != null) { int num2 = Encoding.Default.GetBytes(cell.ToString()).Length; if (num < num2) { num = num2; } } } } sheet.SetColumnWidth(columnNum, num * 256); }
public static void SetColumnWidth(ISheet sheet, int colsCount) { for (int columnNum = 0; columnNum <= colsCount; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //获取当前列宽度 for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++) //在这一列上循环行 { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(columnNum); if (currentCell == null) { continue; } int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度 if (columnWidth < length + 1) { columnWidth = length + 1; }//若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符 } sheet.SetColumnWidth(columnNum, columnWidth * 256 > 255 * 100 ? 255 * 100 : columnWidth * 256); } }
private void CreateDataRows <T>(ISheet worksheet, IList <PropertyInfo> properties, IReadOnlyList <T> enumeratedCollection, StylingOptions stylingOptions, Dictionary <int, short> columnFormatters, Dictionary <string, ICellStyle> styleCache) { var dataFontColorId = GetXlColor(stylingOptions.DataFontColor); for (var rowIndex = 0; rowIndex < enumeratedCollection.Count; rowIndex++) { var row = worksheet.CreateRow(rowIndex + 1); foreach (var columnIndex in properties.Select(properties.IndexOf)) { SetCellValue(row.CreateCell(columnIndex), properties[columnIndex].GetValue(enumeratedCollection[rowIndex]), properties[columnIndex].PropertyType); row.Cells[columnIndex].CellStyle = GetCellStyle(columnFormatters[columnIndex], dataFontColorId, stylingOptions.DataFontSize, styleCache); } } for (var i = 0; i < properties.Count; i++) { worksheet.AutoSizeColumn(i); worksheet.SetColumnWidth(i, worksheet.GetColumnWidth(i) + 1000); } }
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> /// <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 { result.IsSuccess = true; } return result; }
private void AutoSizeColumn(ISheet sheet, int columnNum) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum) == null ? sheet.CreateRow(rowNum) : sheet.GetRow(rowNum); if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = System.Text.Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) columnWidth = length; } } sheet.SetColumnWidth(columnNum, columnWidth * 256); }
protected static int GetColumnWidth(ISheet sheet, int columnIndex) { return ExcelToHtmlUtils.GetColumnWidthInPx(sheet.GetColumnWidth(columnIndex)); }