public void AddAndEditTerm(IEntry term, string source, string target)
{
var dataGrid = new ExcelDataGrid
{
Term = target,
Approved = null
};
AddAndEditAction?.Invoke(term, dataGrid);
}
/// <summary>
/// 填充 Excel
/// </summary>
/// <param name="dataGrid">填充的 Excel 数据网格</param>
/// <param name="sheetIndex">指定填充的 sheet</param>
private void Fill(ExcelDataGrid dataGrid, int sheetIndex, string sheetName)
{
var targetFileName = this.FileName;
var fi = new FileInfo(targetFileName);
var templateFileName = $"{fi.Name} - Template{fi.Extension}";
File.Copy(targetFileName, templateFileName, true);
Write(targetFileName, templateFileName, dataGrid, sheetIndex, sheetName, true);
File.Delete(templateFileName);
}
/// <summary>
/// 打开指定的 Excel 文件
/// </summary>
/// <param name="fileName">指定要打开的 Excel 文件名</param>
/// <param name="sheetIndex">指定要打开的 Sheet 索引</param>
/// <returns>Excel 文件数据表格</returns>
internal ExcelDataGrid Open(string fileName, int sheetIndex = 0)
{
if (string.IsNullOrWhiteSpace(fileName))
{
throw new ArgumentNullException(nameof(fileName));
}
var sourceFs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
var extensions = new FileInfo(fileName).Extension;
IWorkbook workbook = null;
IFormulaEvaluator evaluator = null;
if (extensions.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new XSSFWorkbook(sourceFs);
evaluator = new XSSFFormulaEvaluator(workbook);
}
else if (extensions.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new HSSFWorkbook(sourceFs);
evaluator = new HSSFFormulaEvaluator(workbook);
}
else
{
throw new ArgumentException("不支持的文件扩展名");
}
var sheet = workbook.GetSheetAt(sheetIndex);
if (sheet == null)
{
throw new FileLoadException("打开的 Excel 文件没有 Sheet.");
}
var result = new ExcelDataGrid();
var rowIndex = -1;
var columnIndex = -1;
foreach (var row in GetRows(sheet))
{
rowIndex++;
columnIndex = -1;
foreach (var column in row.Cells)
{
columnIndex++;
result[rowIndex, columnIndex] = GetCellValue(evaluator, column);
}
}
sourceFs.Close();
workbook.Close();
return(result);
}
/// <summary>
/// C.统计日降水量
/// </summary>
/// <param name="records">降水记录</param>
private void RainfallDaySum(IEnumerable <Record> records)
{
var list = new List <DaySumRecord>();
var lst = records.Where(p => p.Precipitation30 > 0)
.GroupBy(p => p.DateTime.Date)
.Select(p =>
{
var sum = p.Sum(c => c.Precipitation30);
foreach (var item in p)
{
list.Add(new DaySumRecord
{
Date = p.Key,
DateTime = item.DateTime,
Precipitation15 = item.Precipitation15,
Precipitation30 = item.Precipitation30,
PrecipitationSum = sum
});
}
return(0);
}).ToList();
var dataGrid = new ExcelDataGrid();
dataGrid[0, 0] = "日期";
dataGrid[0, 1] = "时间";
dataGrid[0, 2] = "I30";
dataGrid[0, 3] = "I15";
dataGrid[0, 4] = "降水量";
var rowIndex = 0;
var lastDate = new DateTime();
foreach (var item in list)
{
rowIndex++;
dataGrid[rowIndex, 0] = item.Date == lastDate ? CellValue.Skip : new CellValue(item.Date, "yyyy-MM-dd");
dataGrid[rowIndex, 1] = item.DateTime;
dataGrid[rowIndex, 2] = item.Precipitation30;
dataGrid[rowIndex, 3] = item.Precipitation15;
dataGrid[rowIndex, 4] = item.Date == lastDate ? CellValue.Skip : item.PrecipitationSum;
lastDate = item.Date;
}
Fill(dataGrid, 3, "日降水量");
}
/// <summary>
/// 加载记录, 将<see cref="ExcelDataGrid"/>转换为<see cref="Record"/>
/// </summary>
private IEnumerable <Record> Load(ExcelDataGrid dataGrid)
{
var list = new List <Record>();
if (dataGrid == null)
{
return(list);
}
foreach (var row in dataGrid.Rows)
{
if (DateTime.TryParse(row.Columns[0].Value.ToString(), out var dateTime) &&
decimal.TryParse(row.Columns[1].Value.ToString(), out var p30) &&
decimal.TryParse(row.Columns[2].Value.ToString(), out var p15))
{
list.Add(new Record()
{
DateTime = dateTime,
Precipitation30 = p30,
Precipitation15 = p15
});
}
}
return(list);
}
public void AddAndEdit(IEntry entry, ExcelDataGrid excelDataGrid)
{
try
{
if (_providerSettings.IsReadOnly)
{
MessageBox.Show("Terminology Provider is configured as read only!", "Read Only", MessageBoxButtons.OK);
return;
}
if (!_providerSettings.IsFileReady())
{
MessageBox.Show(
@"The excel file configured as a terminology provider appears to be also opened in the Excel application. Please close the file!",
@"Excel file is used by another process",
MessageBoxButtons.OK);
return;
}
var terms = sourceListView.Objects.Cast <ExcelEntry>().ToList();
var selectedTerm = terms.FirstOrDefault(item => item.Id == entry.Id);
var excelTerm = new ExcelTerm
{
SourceCulture = _providerSettings.SourceLanguage,
TargetCulture = _providerSettings.TargetLanguage,
Target = excelDataGrid.Term
};
var source = (ExcelEntry)entry;
source.IsDirty = true;
excelTerm.Source = source.SearchText;
var exist = false;
var targetlanguage = selectedTerm?.Languages.Cast <ExcelEntryLanguage>()
.FirstOrDefault(x => !x.IsSource);
if (targetlanguage != null)
{
foreach (var term in targetlanguage.Terms)
{
if (term.Value == excelDataGrid.Term)
{
exist = true;
}
}
if (exist == false)
{
var termToAdd = new EntryTerm
{
Value = excelDataGrid.Term
};
targetlanguage.Terms.Add(termToAdd);
terms[entry.Id].Languages = selectedTerm.Languages;
}
}
JumpToTerm(entry);
Task.Run(Save);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// A.划分次降水
/// </summary>
/// <param name="records">降水记录</param>
private List <PartitionRecord> RinfallPartition(IEnumerable <Record> records)
{
//1. 降水间歇 > 6h, 分为两次降水
//2. 降水没有间歇, 但是6之后降水量< 1.3 mm, 分为两次降水
var list = new List <PartitionRecord>();
//降水序号
var number = 0;
//是否正在降水
var raining = false;
//首次降水时间
var firstRain = default(DateTime?);
//首次非降水时间
var firstURain = default(DateTime?);
//最近降水时间
var lastRain = default(DateTime?);
foreach (var record in records)
{
//降雨
if (record.Precipitation30 > 0)
{
lastRain = record.DateTime;
if (!raining)
{
firstRain = record.DateTime;
if ((firstRain - firstURain).Value.TotalHours > 6)
{
number = number == 0 ? 1 : number + 1;
}
raining = true;
}
else
{
var h = (lastRain - firstRain).Value.TotalHours;
if (h >= 6)
{
var sum = list.Where(p => p.Number == number).Sum(p => p.Precipitation30);
if (sum > 1.3m)
{
number++;
}
}
}
list.Add(new PartitionRecord()
{
Number = number,
DateTime = record.DateTime,
Precipitation15 = record.Precipitation15,
Precipitation30 = record.Precipitation30
});
}
//未降雨
else
{
if (raining)
{
raining = false;
firstURain = record.DateTime;
}
else if (firstURain == null)
{
firstURain = record.DateTime;
}
}
}
var dataGrid = new ExcelDataGrid();
dataGrid[0, 0] = "序号";
dataGrid[0, 1] = "时间";
dataGrid[0, 2] = "I30";
dataGrid[0, 3] = "I15";
dataGrid[0, 4] = "降水量";
var rowIndex = 0;
var lastNumber = 0;
foreach (var item in list)
{
rowIndex++;
dataGrid[rowIndex, 0] = item.Number == lastNumber ? CellValue.Skip : item.Number;
dataGrid[rowIndex, 1] = item.DateTime;
dataGrid[rowIndex, 2] = item.Precipitation30;
dataGrid[rowIndex, 3] = item.Precipitation15;
dataGrid[rowIndex, 4] = item.Number == lastNumber ? CellValue.Skip : list.Where(p => p.Number == item.Number).Sum(p => p.Precipitation30);
lastNumber = item.Number;
}
Fill(dataGrid, 2, "划分次降水");
return(list);
}
/// <summary>
/// 将数据写入到指定的 Excel 文件中
/// </summary>
/// <param name="targetFileName">指定的文件名</param>
/// <param name="sheetIndex">指定要打开的 Sheet 索引</param>
/// <param name="mergeSamColumn">同一行中相邻列的值相同, 则合并</param>
/// <param name="mergeSameRow">同一列中相邻行的值相同, 则合并</param>
/// <param name="dataGrid">指定的数据</param>
internal void Write(string targetFileName, string templateFileName, ExcelDataGrid dataGrid, int sheetIndex = 0, string sheetName = null, bool mergeSameRow = false, bool mergeSamColumn = false)
{
if (string.IsNullOrWhiteSpace(targetFileName))
{
throw new ArgumentNullException(nameof(targetFileName));
}
var sourceFs = new FileStream(templateFileName, FileMode.Open, FileAccess.Read);
var targetFs = new FileStream(targetFileName, FileMode.Create, FileAccess.ReadWrite);
var extensions = new FileInfo(templateFileName).Extension;
IWorkbook workbook;
if (extensions.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new XSSFWorkbook(sourceFs);
}
else if (extensions.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new HSSFWorkbook(sourceFs);
}
else
{
throw new ArgumentException("不支持的文件扩展名");
}
ISheet sheet;
try
{
sheet = workbook.GetSheetAt(sheetIndex);
if (!string.IsNullOrEmpty(sheetName))
{
workbook.SetSheetName(sheetIndex, sheetName);
}
}
catch (ArgumentException)
{
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet();
}
else
{
sheet = workbook.CreateSheet(sheetName);
}
}
var rowIndex = -1;
foreach (var row in dataGrid.Rows)
{
rowIndex++;
var columnIndex = -1;
foreach (var column in row.Columns)
{
columnIndex++;
var value = column;
if (value != null && value.Value != null && value != Model.CellValue.Skip)
{
var excelRow = sheet.GetRow(rowIndex);
if (excelRow == null)
{
excelRow = sheet.CreateRow(rowIndex);
}
var excelColumn = excelRow.GetCell(columnIndex);
if (excelColumn == null)
{
excelColumn = excelRow.CreateCell(columnIndex);
}
if (excelColumn.CellStyle == null)
{
excelColumn.CellStyle = workbook.CreateCellStyle();
}
excelColumn.CellStyle.Alignment = HorizontalAlignment.Center;
excelColumn.CellStyle.VerticalAlignment = VerticalAlignment.Center;
var @string = value.Value as string;
if (@string != null)
{
excelColumn.SetCellValue(@string);
}
var @bool = value.Value as bool?;
if (@bool != null)
{
excelColumn.SetCellValue(@bool.Value);
}
var dateTime = value.Value as DateTime?;
if (dateTime != null)
{
excelColumn.SetCellValue(dateTime.Value);
var cellStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.DataFormat = format.GetFormat(value.Format ?? "yyyy-MM-dd HH:mm:ss");
excelColumn.CellStyle = cellStyle;
sheet.AutoSizeColumn(columnIndex);
}
if (value.Value is double ||
value.Value is float ||
value.Value is int ||
value.Value is decimal)
{
excelColumn.SetCellValue((double)value.Value);
}
}
}
}
if (mergeSameRow)
{
var columnCount = dataGrid.Rows.Max(p => p.Columns.Count);
for (var columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
var firstRowIndex = 0;
var lastRowIndex = 0;
for (rowIndex = 0; rowIndex < dataGrid.Rows.Count; rowIndex++)
{
var curtRow = dataGrid.Rows[rowIndex].Columns[columnIndex];
if (curtRow == null || curtRow.Value == null || string.IsNullOrEmpty(curtRow.Value as string))
{
lastRowIndex = rowIndex;
}
else
{
if (lastRowIndex - firstRowIndex > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRowIndex, lastRowIndex, columnIndex, columnIndex));
}
firstRowIndex = rowIndex;
}
}
}
}
workbook.Write(targetFs);
sourceFs.Close();
targetFs.Close();
workbook.Close();
}
