public CellValue GetCellValue(CellAddress addr)
{
var cell = sheet.GetCell(addr.Row, addr.Col);
CellValue v = new CellValue();
_GetCellValue(cell, v);
return(v);
}
/// <summary>
/// 填写表格数据
/// </summary>
/// <param name="tabelName"></param>
/// <param name="data"></param>
public void WriteTable(string tabelName, List <object> datas)
{
//找到所有表格标签
var labels = Labels.Where(x => x is TableLabel label && label.Table == tabelName).Cast <TableLabel>().ToList();
if (labels.Count == 0)
{
return;
}
//获得模板行
//var tempRow = Sheet.GetRow(labels.First().Cell.RowIndex);
var tempRow = labels.First().Cell.Row;
//根据表格行数量创建空表格
for (int i = 0; i < datas.Count - 1; i++)
{
Sheet.CopyRow(tempRow.RowNum, tempRow.RowNum + 1);
}
//填充数据
for (int dataRowIndex = 0; dataRowIndex < datas.Count; dataRowIndex++)
{
var properties = datas[dataRowIndex].GetType().GetProperties();
foreach (var label in labels)
{
var currentCell = Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex);
if (currentCell == null)
{
continue;
}
if (string.IsNullOrWhiteSpace(label.Formula))
{//没有公式，那么只是普通的数据替换
FillValue(currentCell, label.Name, datas[dataRowIndex], properties);
}
else
{//进入公式编辑方式
FillFormula(currentCell, label.Formula, label => Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex)?.Address?.FormatAsString());
}
}
}
//计算每列的区域
foreach (var label in labels)
{
label.CellRange = new CellRangeAddress(tempRow.RowNum, tempRow.RowNum + datas.Count - 1, label.Cell.ColumnIndex, label.Cell.ColumnIndex);
}
}
public static void CopyCell(this ISheet sheet, ICell sourceCell, CellLocation targetLocation)
{
if (sourceCell == null)
{
sheet.RemoveCell(targetLocation);
return;
}
var targetCell = sheet.GetCell(targetLocation);
if (targetCell == null)
{
targetCell = sheet.GetCellWithPromise(targetLocation);
if (targetCell.ColumnIndex != sourceCell.ColumnIndex)
{
sheet.SetColumnWidth(targetCell.ColumnIndex, sheet.GetColumnWidth(sourceCell.ColumnIndex));
}
}
if (sourceCell.CellStyle != null)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
if (sourceCell.CellComment != null)
{
targetCell.CellComment = sourceCell.CellComment;
}
if (sourceCell.Hyperlink != null)
{
targetCell.Hyperlink = sourceCell.Hyperlink;
}
targetCell.SetCellType(sourceCell.CellType);
switch (sourceCell.CellType)
{
case CellType.Numeric:
targetCell.SetCellValue(sourceCell.NumericCellValue);
break;
case CellType.String:
targetCell.SetCellValue(sourceCell.RichStringCellValue);
break;
case CellType.Formula:
targetCell.SetCellFormula(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
break;
}
}
public static ICell SetCellValue(this ISheet ISheet1, int rowId, int columnId, object value)
{
CellType type = CellType.String;
Type valueType = value.GetType();
if (valueType == typeof(double) ||
valueType == typeof(float) ||
valueType == typeof(int) ||
valueType == typeof(short) ||
valueType == typeof(long) ||
valueType == typeof(ushort) ||
valueType == typeof(uint) ||
valueType == typeof(ulong)
)
{
type = CellType.Numeric;
}
else if (valueType == typeof(bool))
{
type = CellType.Boolean;
}
var cell = ISheet1.GetCell(rowId, columnId, type);
if (cell != null)
{
cell.SetCellValueEx(value);
}
return(cell);
}
public static void SetCellValue(this ISheet sheet, Int32 x, Int32 y, DateTime val)
{
var cell = sheet.GetCell(x, y);
if (cell != null)
{
cell.SetCellValue(val);
}
}
/// <summary>获得单元字符串值</summary>
public static string GetCellValueAsString(this ISheet sheet, int rowIndex, int colIndex)
{
var cell = sheet.GetCell(rowIndex, colIndex);
if (cell == null)
{
return(null);
}
return(GetCellValue(cell, cell.CellType).ToString());
}
/// <summary>
/// 填充工作表
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="data">数据</param>
/// <param name="mapping">映射</param>
protected static void FillSheet(ISheet sheet, object data, ExcelEntityMapping mapping)
{
foreach (var propMapping in mapping)
{
object value;
if (propMapping.PropertyName == null)
{
value = propMapping.Value;
var cell = sheet.GetCell(propMapping.CellName);
cell.SetCellValue(value.ToString());
}
else
{
var propertyInfo = data.GetType().GetProperty(propMapping.PropertyName);
value = propertyInfo.GetValue(data);
if (value == null)
{
value = propMapping.Value;
}
if (propMapping.Items != null && propMapping.Items.Count != 0)
{
var listValue = (IList)value;
for (int i = 0; i < propMapping.Items.Count; i++)
{
var itemMapping = propMapping.Items[i];
// 找到对应的数据
var itemData = listValue[i];
FillSheet(sheet, itemData, itemMapping);
}
}
else
{
var cell = sheet.GetCell(propMapping.CellName);
var type = propertyInfo.PropertyType;
cell.SetCellValue(value, type);
}
}
}
}
private static void PreencherInformacoesDespesas(ICollection <Despesa> despesas)
{
ISheet sheetCatalogo = _documento.GetSheet("Despesas");
int numeroProximaLinha = 3;
foreach (Despesa despesa in despesas)
{
sheetCatalogo.GetCell(numeroProximaLinha, 1).SetCellValue(despesa.Data.ToShortDateString());
sheetCatalogo.GetCell(numeroProximaLinha, 2).SetCellValue(despesa.PegarTipo());
sheetCatalogo.GetCell(numeroProximaLinha, 3).SetCellValue(despesa.Fornecedor.Nome);
sheetCatalogo.GetCell(numeroProximaLinha, 4).SetCellValue(despesa.Item.Descricao);
sheetCatalogo.GetCell(numeroProximaLinha, 5).SetCellValue(despesa.Quantidade);
sheetCatalogo.GetCell(numeroProximaLinha, 6).SetCellValue(despesa.ValorUnitario);
sheetCatalogo.GetCell(numeroProximaLinha, 7).SetCellValue(despesa.ValorTotal);
if (despesa.Animal != null)
{
sheetCatalogo.GetCell(numeroProximaLinha, 8).SetCellValue(despesa.Animal.Especie);
}
numeroProximaLinha++;
}
}
private void PreencherInformacoesProdutos()
{
ISheet sheetCatalogo =
_workbookCatalogo.GetSheet("Catálogo");
int numeroProximaLinha = 3;
foreach (Produto produto in _produtos)
{
sheetCatalogo.GetCell(numeroProximaLinha, 1)
.SetCellValue(produto.CodigoBarras);
sheetCatalogo.GetCell(numeroProximaLinha, 2)
.SetCellValue(produto.NomeProduto);
sheetCatalogo.GetCell(numeroProximaLinha, 3)
.SetCellValue(produto.Categoria);
sheetCatalogo.GetCell(numeroProximaLinha, 4)
.SetCellValue(produto.DataCadastro);
sheetCatalogo.GetCell(numeroProximaLinha, 5)
.SetCellValue(produto.QtdEstoque);
sheetCatalogo.GetCell(numeroProximaLinha, 6)
.SetCellValue(produto.PrecoVenda);
numeroProximaLinha++;
}
}
private void PreencherInformacoesComparacao()
{
ISheet sheetCatalogo =
_workbookCatalogo.GetSheet("Comparativo");
int numeroProximaLinha = 3;
foreach (Comparacao compara in _comparacao)
{
sheetCatalogo.GetCell(numeroProximaLinha, 1)
.SetCellValue(compara.Cnpj);
sheetCatalogo.GetCell(numeroProximaLinha, 2)
.SetCellValue(compara.Nota);
sheetCatalogo.GetCell(numeroProximaLinha, 3)
.SetCellValue(compara.ValorSefaz);
sheetCatalogo.GetCell(numeroProximaLinha, 4)
.SetCellValue(Math.Round(Convert.ToDouble(compara.ValorEscritura), 2));
sheetCatalogo.GetCell(numeroProximaLinha, 5)
.SetCellValue(compara.Cfop);
sheetCatalogo.GetCell(numeroProximaLinha, 6)
.SetCellValue(compara.Resultado);
numeroProximaLinha++;
}
}
private static ICell GetCell(this ISheet worksheet, int rowPosition, string columnLetter)
{
int columnPosition = columnLetter.ToCharArray().Select(c => c - 'A' + 1).Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum() - 1;
return(worksheet.GetCell(rowPosition, columnPosition));
}