public static void WriteNodes(ISheet exSheet, IList <OutputNode> nodes)
{
if (nodes == null)
{
return;
}
foreach (var node in nodes)
{
int rowIndex = node.RowIndex;
IRow exRow = exSheet.GetRow(rowIndex) ?? exSheet.CreateRow(rowIndex);
ICell exCell = exRow.GetCell(node.ColumnIndex) ?? exRow.CreateCell(node.ColumnIndex);
if (node.IsRegion)
{
exSheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + node.RowSpan - 1, node.ColumnIndex, node.ColumnIndex + node.ColumnSpan - 1));
}
if (node.Convertor != null)
{
node.Convertor.OnSetValue(exCell, node.Content);
}
else
{
NPOIExcelUtil.SetCellValueByDataType(exCell, node.Content);
}
node.OnRender(exCell);
}
}
protected override void Writing(object sender, WriteEventArgs args)
{
ISheet exSheet = args.ExSheet;
RegionTable headerTable = args.Entity as RegionTable;
if (headerTable == null)
{
return;
}
int colIndex = this.ColIndex, rowIndex = this.RowIndex, colCount = this.ColCount, rowCount = this.RowCount;
int colHeaderLevel = headerTable.ColumnHeaderLevel;
int rowHeaderLevel = headerTable.RowHeaderLevel;
//1、Excel格式准备
ICell exCell = GetStandardCell(exSheet, RowIndex, colIndex, true);
NPOIExcelUtil.CopyCell(exSheet, exCell, exCell.ColumnIndex + 1, colCount - 1);
NPOIExcelUtil.CopyRow(exSheet, exCell.RowIndex, exCell.RowIndex + 1, rowHeaderLevel - 1);
exCell = GetStandardCell(exSheet, rowIndex + rowHeaderLevel, colIndex);
NPOIExcelUtil.CopyCell(exSheet, exCell, exCell.ColumnIndex + 1, colCount - 1);
NPOIExcelUtil.CopyRow(exSheet, exCell.RowIndex, exCell.RowIndex + 1, rowCount - rowHeaderLevel - 1);
//2、数据填充
IList <OutputNode> nodes = this.GetNodes();
foreach (var node in nodes)
{
IRow exRow = exSheet.GetRow(node.RowIndex) ?? exSheet.CreateRow(node.RowIndex);
//if (exRow == null) continue;
exCell = exRow.GetCell(node.ColumnIndex) ?? exRow.CreateCell(node.ColumnIndex);
//if (exCell == null) continue;
if (node.IsRegion)
{
exSheet.AddMergedRegion(new CellRangeAddress(node.RowIndex, node.RowIndex + node.RowSpan - 1, node.ColumnIndex, node.ColumnIndex + node.ColumnSpan - 1));
}
NPOIExcelUtil.SetCellValueByDataType(exCell, node.Content);
}
//3、自适应宽度
for (int i = 0; i < this.ColCount; i++)
{
int endRow = this.RowIndex + (i < colHeaderLevel || rowHeaderLevel == 0 ? this.RowCount : rowHeaderLevel) - 1;
NPOIExcelUtil.AutoFitColumnWidth(exSheet, colIndex + i, rowIndex, endRow);
}
//4、锁定标题区域(只有列标题、只有行标题、多行多列标题三种情况锁定不一样）
if (headerTable.Freeze)
{
NPOIExcelUtil.Freeze(exSheet, rowHeaderLevel > 0 ? rowIndex + rowHeaderLevel : 0, colHeaderLevel > 0 ? colIndex + colHeaderLevel : 0);
}
NPOIExcelUtil.SetAreaBorder(exSheet, rowIndex, colIndex, rowCount, colCount);
}
protected override void SetValue(ICell cell, object source)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题
}
protected override void Writing(object sender, WriteEventArgs args)
{
ISheet exSheet = args.ExSheet;
Table table = args.Entity as Table;
if (table == null)
{
return;
}
Source source = this.Source;
if (source == null)
{
return;
}
DataTable dt = source.Table;
if (dt == null)
{
return;
}
////根据XML指定区域与数据源，计算实际可填充区域(table根据字段数与字段位置或指定区域自动获得区域）
int rowCount = this.RowCount; //table.Location.RowCount == 0 ? dt.Rows.Count : table.Location.RowCount;
int colCount = this.ColCount; //列数必须由XML决定（指定区域或根据字段[位置或数量]计算）
int rowIndexBase = this.RowIndex; //table.Location.RowIndex + increasedRowCount;//XML是根据模板设置，要加上填充区域的基数
int colIndexBase = this.ColIndex;
IRow styleRow = exSheet.GetRow(rowIndexBase) ?? exSheet.CreateRow(rowIndexBase);
//if (styleRow == null) return;
//1、暂时移除区域中的合并单元格
Dictionary <int, int> dict = ClearMergeRegion(styleRow, colIndexBase, colIndexBase + colCount - 1);
if (table.CopyFill && rowCount > 1)
{
NPOIExcelUtil.CopyRow(exSheet, rowIndexBase, rowIndexBase + 1, rowCount - 1);
}
IList <OutputNode> nodes = this.GetNodes();
//2、根据合并单元格调整结点区域
for (int i = 0; i < nodes.Count; i++)
{
//在行区域范围内，有合并单元格且导出规则未指定合并时，需要以合并单元格为准
if (rowIndexBase <= nodes[i].RowIndex && nodes[i].RowIndex < rowIndexBase + rowCount &&
dict.ContainsKey(nodes[i].ColumnIndex) && nodes[i].ColumnSpan == 1)
{
nodes[i].ColumnSpan = dict[nodes[i].ColumnIndex];
}
}
//3、将数据写入Sheet
NodeWriter.WriteNodes(exSheet, nodes);
}
public void Render(ICell cell)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
PicArea != null?
draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
}
protected HyperlinkType GetLinkType(string type)
{
HyperlinkType linkType = HyperlinkType.Unknown;
if ("auto".Equals(type.ToLower()))
{
linkType = NPOIExcelUtil.GetLinkTypeByData(Address);
linkType = linkType == HyperlinkType.Unknown ? HyperlinkType.Url : linkType;
}
else if (!Enum.TryParse(type, true, out linkType))
{
//默认URL类型（Excel2003用Unknown时会报引用为空错误；Excel2007中如Address与LinkType不匹配会报错，即使调整为Unknown导出Excel打开异常）
linkType = HyperlinkType.Url; //: HyperlinkType.Unknown;
}
return(linkType);
}
public override void PreWrite(WriteEventArgs args)
{
base.PreWrite(args);
ISheet exSheet = args.ExSheet;
DynamicArea dynamicArea = args.Entity as DynamicArea;
if (dynamicArea == null)
{
return;
}
int baseRow = this.RowIndex;
for (int i = 1; i < this.Count; i++)
{
for (int j = 0; j < dynamicArea.Location.RowCount; j++)
{
NPOIExcelUtil.CopyRow(exSheet, baseRow + j, baseRow + j + dynamicArea.Location.RowCount * i);
}
}
}
private ICell GetStandardCell(ISheet sheet, int rowIndex, int colIndex, bool isTitle = false)
{
//如存在直接返回
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
ICell cell = row.GetCell(colIndex);
if (cell != null)
{
return(cell);
}
//如不存在创建
cell = row.CreateCell(colIndex);
cell.CellStyle = NPOIExcelUtil.CreateCellStyle(sheet.Workbook, 0, isTitle ? HSSFColor.LightTurquoise.Index : HSSFColor.White.Index, isTitle ? HorizontalAlignment.Center : HorizontalAlignment.Left);
cell.CellStyle.SetFont(NPOIExcelUtil.CreateFont(sheet.Workbook, isTitle ? "微软雅黑" : "宋体"));
return(cell);
}
protected override void Writing(object sender, WriteEventArgs args)
{
Cell cell = args.Entity as Cell;
ISheet exSheet = args.ExSheet;
if (cell == null)
{
return;
}
object tmpObject = this.GetValue();
if (tmpObject == null)
{
return;
}
IWorkbook book = exSheet.Workbook;
//IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();
IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();//只能有一个实例，否则只有最后一个对象生效
int rowIndex = this.RowIndex;
int colIndex = this.ColIndex;
IRow exRow = exSheet.GetRow(rowIndex) ?? exSheet.CreateRow(rowIndex);
//if (exRow != null)
//{
ICell exCell = exRow.GetCell(this.ColIndex) ?? exRow.CreateCell(this.ColIndex);
//if (exCell != null)
//{
//object tmpObject = sheet.IsDynamic ? sheet.GetValue(cell,sheetName) : dt.Rows[cell.DataIndex][cell.Field];
if (cell.ValueAppend)
{
tmpObject = exCell.StringCellValue + tmpObject;
}
if (tmpObject.GetType() == typeof(byte[]))//处理图片
{
CellRangeAddress region = NPOIExcelUtil.GetRange(exCell);
Image image = new Bitmap(new MemoryStream(tmpObject as byte[]));
Size size = image.Size;
if (size.IsEmpty)
{
return;
}
IClientAnchor anchor = region != null?
draw.CreateAnchor(0, 0, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, colIndex, rowIndex, colIndex + this.ColCount, rowIndex + this.RowCount);
IPicture pic = draw.CreatePicture(anchor, book.AddPicture((byte[])tmpObject, PictureType.JPEG));
switch (cell.FillType)
{
case FillType.Origin:
pic.Resize();
break;
case FillType.Scale:
float width = 0, height = 0;
for (int i = anchor.Col1; i < anchor.Col2; i++)
{
width += exSheet.GetColumnWidth(i) / 256f * 12;
}
for (int i = anchor.Row1; i < anchor.Row2; i++)
{
IRow row = exSheet.GetRow(i);
height += row != null ? row.HeightInPoints : exSheet.DefaultRowHeightInPoints;
}
float factor = Math.Min(width / (size.Width * 0.75f), height / (size.Height * 0.75f));
pic.Resize(factor);
break;
default:
break;
}
}
else
{
exCell.SetCellValue((tmpObject ?? "").ToString());
}
//}
//}
}
public void Render(ICell cell)
{
NPOIExcelUtil.AddComment(cell, Author, Comment);
}
