338 lines
13 KiB
C#
338 lines
13 KiB
C#
using OfficeOpenXml;
|
||
using OfficeOpenXml.Drawing;
|
||
using OfficeOpenXml.Style;
|
||
using WorkCameraExport.Models;
|
||
using WorkCameraExport.Services.Interfaces;
|
||
|
||
namespace WorkCameraExport.Services
|
||
{
|
||
/// <summary>
|
||
/// Excel 服务 - 负责生成 Excel 文件
|
||
/// </summary>
|
||
public class ExcelService
|
||
{
|
||
private readonly ILogService? _logService;
|
||
|
||
// Excel 图片配置
|
||
private const int ImageWidth = 100;
|
||
private const int ImageHeight = 60;
|
||
private const int ImageSpacing = 5;
|
||
|
||
static ExcelService()
|
||
{
|
||
// 设置 EPPlus 许可证（非商业用途）
|
||
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
||
}
|
||
|
||
public ExcelService(ILogService? logService = null)
|
||
{
|
||
_logService = logService;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出工作记录到 Excel
|
||
/// </summary>
|
||
public async Task ExportWorkRecordsToExcelAsync(
|
||
List<WorkRecordExportItem> records,
|
||
string outputPath,
|
||
CancellationToken cancellationToken = default)
|
||
{
|
||
try
|
||
{
|
||
_logService?.Info($"[Excel] 开始生成 Excel，共 {records.Count} 条记录");
|
||
|
||
// 确保输出目录存在
|
||
var outputDir = Path.GetDirectoryName(outputPath);
|
||
if (!string.IsNullOrEmpty(outputDir) && !Directory.Exists(outputDir))
|
||
{
|
||
Directory.CreateDirectory(outputDir);
|
||
_logService?.Info($"[Excel] 创建输出目录: {outputDir}");
|
||
}
|
||
|
||
using var package = new ExcelPackage();
|
||
var worksheet = package.Workbook.Worksheets.Add("工作记录");
|
||
|
||
// 设置表头
|
||
SetWorkRecordHeaders(worksheet);
|
||
|
||
// 填充数据
|
||
var row = 2;
|
||
foreach (var record in records)
|
||
{
|
||
cancellationToken.ThrowIfCancellationRequested();
|
||
await FillWorkRecordRowAsync(worksheet, row, record, cancellationToken);
|
||
row++;
|
||
}
|
||
|
||
// 自动调整列宽（除了施工图片列，第10列）
|
||
AutoFitColumns(worksheet, excludeColumn: 10);
|
||
|
||
// 保存文件
|
||
var fileInfo = new FileInfo(outputPath);
|
||
_logService?.Info($"[Excel] 准备保存文件: {fileInfo.FullName}");
|
||
|
||
await package.SaveAsAsync(fileInfo, cancellationToken);
|
||
|
||
// 验证文件
|
||
if (fileInfo.Exists)
|
||
{
|
||
fileInfo.Refresh();
|
||
_logService?.Info($"[Excel] 文件保存成功: {fileInfo.FullName}, 大小: {fileInfo.Length} 字节");
|
||
}
|
||
else
|
||
{
|
||
_logService?.Error($"[Excel] 文件保存后不存在: {fileInfo.FullName}");
|
||
}
|
||
|
||
_logService?.Info($"[Excel] Excel 导出成功: {outputPath}, 共 {records.Count} 条记录");
|
||
}
|
||
catch (OperationCanceledException)
|
||
{
|
||
_logService?.Info("[Excel] Excel 导出已取消");
|
||
throw;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logService?.Error($"[Excel] Excel 导出失败: {ex.Message}", ex);
|
||
throw;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出月报表到 Excel
|
||
/// </summary>
|
||
public async Task ExportMonthlyReportToExcelAsync(
|
||
List<MonthlyReportDto> data,
|
||
string outputPath,
|
||
CancellationToken cancellationToken = default)
|
||
{
|
||
try
|
||
{
|
||
using var package = new ExcelPackage();
|
||
var worksheet = package.Workbook.Worksheets.Add("月报表");
|
||
|
||
// 设置表头
|
||
worksheet.Cells[1, 1].Value = "序号";
|
||
worksheet.Cells[1, 2].Value = "时间";
|
||
worksheet.Cells[1, 3].Value = "部门名称";
|
||
worksheet.Cells[1, 4].Value = "人员名称";
|
||
worksheet.Cells[1, 5].Value = "工作天数";
|
||
|
||
// 设置表头样式
|
||
using (var range = worksheet.Cells[1, 1, 1, 5])
|
||
{
|
||
range.Style.Font.Bold = true;
|
||
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
|
||
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
|
||
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
}
|
||
|
||
// 填充数据
|
||
for (int i = 0; i < data.Count; i++)
|
||
{
|
||
cancellationToken.ThrowIfCancellationRequested();
|
||
var row = i + 2;
|
||
var item = data[i];
|
||
|
||
worksheet.Cells[row, 1].Value = i + 1;
|
||
worksheet.Cells[row, 2].Value = item.YearMonth;
|
||
worksheet.Cells[row, 3].Value = item.DeptName;
|
||
worksheet.Cells[row, 4].Value = item.WorkerName;
|
||
worksheet.Cells[row, 5].Value = item.WorkDays;
|
||
}
|
||
|
||
// 自动调整列宽
|
||
worksheet.Cells.AutoFitColumns();
|
||
|
||
// 保存文件
|
||
var fileInfo = new FileInfo(outputPath);
|
||
await package.SaveAsAsync(fileInfo, cancellationToken);
|
||
|
||
_logService?.Info($"月报表导出成功: {outputPath}, 共 {data.Count} 条记录");
|
||
}
|
||
catch (OperationCanceledException)
|
||
{
|
||
_logService?.Info("月报表导出已取消");
|
||
throw;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logService?.Error($"月报表导出失败: {ex.Message}", ex);
|
||
throw;
|
||
}
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 设置工作记录表头
|
||
/// </summary>
|
||
private void SetWorkRecordHeaders(ExcelWorksheet worksheet)
|
||
{
|
||
var headers = new[]
|
||
{
|
||
"序号", "部门名称", "拍照时间", "经度", "纬度", "位置",
|
||
"工作内容", "施工人员", "状态", "施工图片", "创建时间", "更新时间"
|
||
};
|
||
|
||
for (int i = 0; i < headers.Length; i++)
|
||
{
|
||
worksheet.Cells[1, i + 1].Value = headers[i];
|
||
}
|
||
|
||
// 设置表头样式
|
||
using (var range = worksheet.Cells[1, 1, 1, headers.Length])
|
||
{
|
||
range.Style.Font.Bold = true;
|
||
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
|
||
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
|
||
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
}
|
||
|
||
// 设置施工图片列宽度（第10列）
|
||
worksheet.Column(10).Width = 60;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 填充工作记录行数据
|
||
/// </summary>
|
||
private async Task FillWorkRecordRowAsync(
|
||
ExcelWorksheet worksheet,
|
||
int row,
|
||
WorkRecordExportItem record,
|
||
CancellationToken cancellationToken)
|
||
{
|
||
// 调试日志：记录写入 Excel 时的 Workers 数据
|
||
var workersValue = record.Workers != null
|
||
? $"Workers数量={record.Workers.Count}, 值=[{string.Join(", ", record.Workers)}]"
|
||
: "Workers=null";
|
||
_logService?.Info($"[Excel写入] 行{row}, 记录ID={record.Id}, {workersValue}");
|
||
|
||
worksheet.Cells[row, 1].Value = row - 1; // 序号
|
||
worksheet.Cells[row, 2].Value = record.DeptName;
|
||
worksheet.Cells[row, 3].Value = record.RecordTime?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
|
||
worksheet.Cells[row, 4].Value = record.Longitude;
|
||
worksheet.Cells[row, 5].Value = record.Latitude;
|
||
worksheet.Cells[row, 6].Value = record.Address;
|
||
worksheet.Cells[row, 7].Value = record.Content;
|
||
worksheet.Cells[row, 8].Value = string.Join(",", record.Workers);
|
||
worksheet.Cells[row, 9].Value = record.StatusName;
|
||
// 第10列是施工图片，稍后处理
|
||
worksheet.Cells[row, 11].Value = record.CreateTime?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
|
||
worksheet.Cells[row, 12].Value = record.UpdateTime?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
|
||
|
||
// 设置行高以容纳图片
|
||
worksheet.Row(row).Height = ImageHeight + 10;
|
||
|
||
// 嵌入图片到第10列
|
||
if (record.ImagePaths != null && record.ImagePaths.Count > 0)
|
||
{
|
||
await EmbedImagesAsync(worksheet, row, 10, record.ImagePaths, cancellationToken);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 嵌入图片到单元格（水平排列）
|
||
/// </summary>
|
||
private async Task EmbedImagesAsync(
|
||
ExcelWorksheet worksheet,
|
||
int row,
|
||
int column,
|
||
List<string> imagePaths,
|
||
CancellationToken cancellationToken)
|
||
{
|
||
var validPaths = imagePaths.Where(p => !string.IsNullOrEmpty(p) && File.Exists(p)).ToList();
|
||
if (validPaths.Count == 0) return;
|
||
|
||
// 计算所需的列宽
|
||
var totalWidth = validPaths.Count * ImageWidth + (validPaths.Count - 1) * ImageSpacing + 10;
|
||
var currentWidth = worksheet.Column(column).Width * 7; // 大约转换为像素
|
||
if (totalWidth > currentWidth)
|
||
{
|
||
worksheet.Column(column).Width = totalWidth / 7.0 + 5;
|
||
}
|
||
|
||
var xOffset = 5; // 起始偏移
|
||
|
||
for (int i = 0; i < validPaths.Count; i++)
|
||
{
|
||
cancellationToken.ThrowIfCancellationRequested();
|
||
|
||
try
|
||
{
|
||
var imagePath = validPaths[i];
|
||
var imageData = await File.ReadAllBytesAsync(imagePath, cancellationToken);
|
||
|
||
using var stream = new MemoryStream(imageData);
|
||
var picture = worksheet.Drawings.AddPicture(
|
||
$"img_{row}_{column}_{i}",
|
||
stream);
|
||
|
||
// 设置图片位置和大小
|
||
picture.SetPosition(row - 1, 5, column - 1, xOffset);
|
||
picture.SetSize(ImageWidth, ImageHeight);
|
||
|
||
xOffset += ImageWidth + ImageSpacing;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logService?.Warn($"嵌入图片失败: {validPaths[i]}, {ex.Message}");
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 自动调整列宽
|
||
/// </summary>
|
||
private void AutoFitColumns(ExcelWorksheet worksheet, int excludeColumn = -1)
|
||
{
|
||
for (int col = 1; col <= worksheet.Dimension?.End.Column; col++)
|
||
{
|
||
if (col != excludeColumn)
|
||
{
|
||
worksheet.Column(col).AutoFit();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 工作记录导出项 - 包含本地图片路径
|
||
/// </summary>
|
||
public class WorkRecordExportItem
|
||
{
|
||
public int Id { get; set; }
|
||
public string DeptName { get; set; } = "";
|
||
public DateTime? RecordTime { get; set; }
|
||
public string Longitude { get; set; } = "";
|
||
public string Latitude { get; set; } = "";
|
||
public string Address { get; set; } = "";
|
||
public string Content { get; set; } = "";
|
||
public string StatusName { get; set; } = "";
|
||
public List<string> Workers { get; set; } = new(); // 工作人员名称列表
|
||
public List<string> ImagePaths { get; set; } = new(); // 本地图片路径
|
||
public DateTime? CreateTime { get; set; }
|
||
public DateTime? UpdateTime { get; set; }
|
||
|
||
/// <summary>
|
||
/// 从 WorkRecordExportDto 创建
|
||
/// </summary>
|
||
public static WorkRecordExportItem FromDto(WorkRecordExportDto dto)
|
||
{
|
||
return new WorkRecordExportItem
|
||
{
|
||
Id = dto.Id,
|
||
DeptName = dto.DeptName,
|
||
RecordTime = dto.RecordTime,
|
||
Longitude = dto.Longitude,
|
||
Latitude = dto.Latitude,
|
||
Address = dto.Address,
|
||
Content = dto.Content,
|
||
StatusName = dto.StatusName,
|
||
Workers = dto.WorkerNames, // 使用 WorkerNames 获取字符串列表
|
||
CreateTime = dto.CreateTime,
|
||
UpdateTime = dto.UpdateTime
|
||
};
|
||
}
|
||
}
|
||
}
|