/// <param name="destSheet"> the sheet being copied/merged. </param>
/// <param name="newSheet"> the destination sheet being copied/merged into. </param>
/// <param name="copyStyle"> true copy the style. </param>
private static void AppendSheet(XSSFSheet destSheet, XSSFSheet srcSheet, bool copyStyle)
{
int maxColumnNum = 0;
IDictionary <int?, ICellStyle> styleMap = copyStyle ? new Dictionary <int?, ICellStyle>() : null;
int numRowsSrcSheet = GetLastRowWithData(srcSheet);
Console.WriteLine($"NPOIHelper.AppendSheet(): destination sheet {destSheet.SheetName} has {numRowsSrcSheet} rows.");
for (int i = srcSheet.FirstRowNum, j = destSheet.LastRowNum; i <= numRowsSrcSheet; i++, j++)
{
IRow srcRow = srcSheet.GetRow(i);
IRow destRow = destSheet.CreateRow(j);
Console.WriteLine($"NPOIHelper.AppendSheet(): copying row {i} of sheet {srcSheet.SheetName}.");
if (srcRow != null)
{
CopyRow(srcSheet, destSheet, srcRow, destRow, styleMap);
if (srcRow.LastCellNum > maxColumnNum)
{
maxColumnNum = srcRow.LastCellNum;
}
}
}
for (int i = 0; i <= maxColumnNum; i++)
{
destSheet.SetColumnWidth(i, srcSheet.GetColumnWidth(i));
}
}
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColList())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
/// <summary>
/// 统一设置列宽度
/// </summary>
/// <param name="sheet"></param>
/// <param name="fieldCount"></param>
public static void SetColumnWidth(this XSSFSheet sheet, int fieldCount)
{
for (int columnNum = 0; columnNum <= fieldCount; columnNum++)
{
//除以256才是一个字的长度
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 = currentCell != null?Encoding.Default.GetBytes(currentCell.ToString()).Count() : 0; //获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
int width = (((columnWidth > 50 ? columnWidth / 4 : columnWidth) + 3) * 256);
//The maximum column width for an individual cell is 255 characters
if (width > 255 * 256)
{
width = 255 * 256;
}
sheet.SetColumnWidth(columnNum, width);
}
}
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
CT_Col col = cols.AddNewCol();
col.min = (2 + 1);
col.max = (4 + 1);
col.width = (20);
col.customWidth = (true);
sheet.GroupColumn((short)1, (short)3);
cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols);
Assert.AreEqual(1, cols.GetColArray(0).outlineLevel);
Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based
Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based
Assert.AreEqual(false, cols.GetColArray(0).customWidth);
Assert.AreEqual(1, cols.GetColArray(1).outlineLevel);
Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based
Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(1).customWidth);
Assert.AreEqual(0, cols.GetColArray(2).outlineLevel);
Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based
Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(2).customWidth);
Assert.AreEqual(3, cols.sizeOfColArray());
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 2; i <= 4; i++)
{
Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1), "Unexpected width of column " + 1);
}
private static void CopySheets(XSSFSheet source, HSSFSheet destination, HSSFWorkbook retVal)
{
int maxColumnNum = 0;
Dictionary <int, XSSFCellStyle> styleMap = new Dictionary <int, XSSFCellStyle>();
for (int i = source.FirstRowNum; i <= source.LastRowNum; i++)
{
XSSFRow srcRow = (XSSFRow)source.GetRow(i);
HSSFRow destRow = (HSSFRow)destination.CreateRow(i);
if (srcRow != null)
{
CopyRow(source, destination, srcRow, destRow, styleMap, retVal);
if (srcRow.LastCellNum > maxColumnNum)
{
maxColumnNum = srcRow.LastCellNum;
}
}
}
for (int i = 0; i <= maxColumnNum; i++)
{
destination.SetColumnWidth(i, source.GetColumnWidth(i));
}
}
/// <param name="newSheet"> the sheet to create from the copy. </param>
/// <param name="sheet"> the sheet to copy. </param>
/// <param name="copyStyle"> true copy the style. </param>
private static void CopySheets(XSSFSheet newSheet, XSSFSheet sheet, bool copyStyle)
{
int maxColumnNum = 0;
IDictionary <int?, ICellStyle> styleMap = (copyStyle) ? new Dictionary <int?, ICellStyle>() : null;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow srcRow = sheet.GetRow(i);
IRow destRow = newSheet.CreateRow(i);
if (srcRow != null)
{
CopyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.LastCellNum > maxColumnNum)
{
maxColumnNum = srcRow.LastCellNum;
}
}
}
for (int i = 0; i <= maxColumnNum; i++)
{
newSheet.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
}
public int GetColumnWidth(int columnIndex)
{
return(_sh.GetColumnWidth(columnIndex));
}
private void PrintReport()
{
var firstRowNumber = 0;
var firstDataRowNumber = firstRowNumber + _headerLength;
var headerRow = _sheet.CreateRow(_headerLength) as XSSFRow;
var column = 0;
foreach (var item in _configurationList)
{
if (!item.IsVisible)
{
continue;
}
var titleCell = headerRow.CreateCell(column) as XSSFCell;
titleCell.SetCellType(CellType.String);
titleCell.SetCellValue(item.FriendlyName);
titleCell.CellStyle = _headerStyle;
for (var index = 0; index < _model.Count; index++)
{
XSSFRow dataRow;
if (column == 0)
{
dataRow = _sheet.CreateRow(_headerLength + index + 1) as XSSFRow;
}
else
{
dataRow = _sheet.GetRow(_headerLength + index + 1) as XSSFRow;
}
var dataCell = dataRow.CreateCell(column) as XSSFCell;
dataCell.SetCellType(CellType.String);
if (item.Name.IndexOf("Date", StringComparison.InvariantCulture) > -1)
{
dataCell.CellStyle = _dateGridStyle;
}
else
{
dataCell.CellStyle = _generalGridStyle;
}
var value = GetValue(index, item);
if (value != null)
{
dataCell.SetCellValue(value);
}
}
_sheet.AutoSizeColumn(column);
if (_sheet.GetColumnWidth(column) < 3000)
{
_sheet.SetColumnWidth(column, 3000);
}
if (_sheet.GetColumnWidth(column) > 13000)
{
_sheet.SetColumnWidth(column, 13000);
}
if (_sheet.GetColumnWidth(column) < 13000 - 512)
{
_sheet.SetColumnWidth(column, _sheet.GetColumnWidth(column) + 512);
}
column++;
}
BuildCriteriaHeader();
var filterRange = new CellRangeAddress(firstDataRowNumber, _sheet.LastRowNum, headerRow.FirstCellNum,
headerRow.LastCellNum - 1);
_sheet.SetAutoFilter(filterRange);
_sheet.CreateFreezePane(0, firstDataRowNumber + 1);
_sheet.DisplayGridlines = false;
}
public static Dictionary <string, string> printTypeDict = new Dictionary <string, string>();//打印类型字典(Key:打印类型，Value：相应打印类型的模板ExceJSON数据)
/// <summary>
/// 通过模板文件获取单元格集合
/// </summary>
/// <param name="workbook"></param>
/// <param name="qrCode">二维码Code</param>
/// <returns></returns>
public static List <ExcelCellModel> GetCellListByTemplateFile(XSSFWorkbook workbook)
{
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
List <ExcelCellModel> dataList = null;
ExcelCellModel data = null;
IRow row = null;
ICell cell = null;
ExcelPictureModel pictureModel = null;
var columnWith = 0;
BorderStyle[] border = new BorderStyle[4];
try
{
//总行数
int rowCount = sheet.LastRowNum;
if (rowCount > 0)
{
dataList = new List <ExcelCellModel>();
IRow firstRow = sheet.GetRow(0); //第一行
int cellCount = firstRow.LastCellNum; //总列数
//填充行
for (int i = 0; i <= rowCount; ++i)
{
//清空值
border = new BorderStyle[4];
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
}
else
{
#region cell赋值
//四个边框按照 上下左右
border[0] = cell.CellStyle.BorderTop;
border[1] = cell.CellStyle.BorderBottom;
border[2] = cell.CellStyle.BorderLeft;
border[3] = cell.CellStyle.BorderRight;
columnWith = sheet.GetColumnWidth(j);
data = new ExcelCellModel()
{
rowNum = cell.RowIndex,
cellNum = cell.ColumnIndex,
cellWidth = columnWith,
cellHeight = row.Height,
fontSize = cell.CellStyle.GetFont(workbook).FontHeightInPoints,
wrapText = cell.CellStyle.WrapText,
borderLine = border,
Boldweight = cell.CellStyle.GetFont(workbook).IsBold,
verticalAlignment = cell.CellStyle.VerticalAlignment,
horizontalAlignment = cell.CellStyle.Alignment,
fontName = cell.CellStyle.GetFont(workbook).FontName,
//cellColor=cell.CellStyle.GetFont(workbook).Color
location = "",
// regionCell=""
};
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
data.cellValue = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
{
data.cellValue = cell.DateCellValue.ToString();
}
else
{
data.cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
data.cellValue = cell.StringCellValue;
break;
}
//二维码单独处理
if (data.cellValue == PrintStruct.QRCODE)
{
pictureModel = new ExcelPictureModel()
{
endColNum = data.cellNum,
endRowNum = data.rowNum,
qrCode = PrintStruct.QRCODE,
CodeType = PrintStructFlag.QRCODE
};
data.excelPictureModel = pictureModel;
}
//一维码单独处理
if (data.cellValue == PrintStruct.BARCODE)
{
pictureModel = new ExcelPictureModel()
{
endColNum = data.cellNum,
endRowNum = data.rowNum,
qrCode = PrintStruct.BARCODE,
CodeType = PrintStructFlag.BARCODE
};
data.excelPictureModel = pictureModel;
}
#endregion
dataList.Add(data);
}
}
}
}
}
catch (Exception)
{
throw;
}
return(dataList);
}
private void PrintReport()
{
BuildCriteriaHeader();
var rowNumber = 0;
foreach (var item in (_detail as IHaveConfigurationList).ConfigurationList)
{
if (!item.IsVisible)
{
continue;
}
var row = _sheet.CreateRow(_headerLength + rowNumber) as XSSFRow;
var titleCell = row.CreateCell(0) as XSSFCell;
titleCell.SetCellType(CellType.String);
titleCell.SetCellValue(item.FriendlyName);
titleCell.CellStyle = _generalBoldStyle;
var dataCell = row.CreateCell(1) as XSSFCell;
dataCell.SetCellType(CellType.String);
if (item.Name.IndexOf("Date", StringComparison.InvariantCulture) > -1)
{
dataCell.CellStyle = _dateGridStyle;
}
else
{
dataCell.CellStyle = _generalGridStyle;
}
var value = GetValue(item);
if (value != null)
{
dataCell.SetCellValue(value);
}
rowNumber++;
var thinRow = _sheet.CreateRow(_headerLength + rowNumber) as XSSFRow;
thinRow.Height = 100;
rowNumber++;
}
for (var column = 0; column < 2; column++)
{
_sheet.AutoSizeColumn(column);
if (_sheet.GetColumnWidth(column) < 3000)
{
_sheet.SetColumnWidth(column, 3000);
}
if (_sheet.GetColumnWidth(column) > 26000)
{
_sheet.SetColumnWidth(column, 26000);
}
if (_sheet.GetColumnWidth(column) < 26000 - 512)
{
_sheet.SetColumnWidth(column, _sheet.GetColumnWidth(column) + 512);
}
}
_sheet.DisplayGridlines = false;
}