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