Excel import application/code
Перейти к навигации
Перейти к поиску
using System;using System.Collections;using System.Collections.Generic;using System.IO;using System.Linq;using System.Text;using DevExpress.Xpo;using Logy.Entities.Engine;using Logy.Entities.Import;using Logy.Entities.Localization;using Logy.Entities.Model;using Logy.MwAgent.DotNetWikiBot;using NPOI.HSSF.UserModel;
namespace Logy.Api.Mw
{
public class ExcelImporter : CategoryImporter
{
private const string TemplateName = "event";
private const string TemplateExcelParameter = "excel"; private ExcelManager _excelManager;
public ExcelImporter(Session session, Guid? wikiId = null) : base(session, wikiId)
{
}
public override string Url
{
get { return "Category:Excel import"; }
}
public override bool IsGroup { get { return true; } }
public override EntityType EntityType
{
get { return EntityType.None; }
}
public override Speed GetPagesSpeed
{
// too allow during Excel_ call of GetPages() and excel file to load
get { return Speed.Fast; }
}
public string Dir
{
get
{
return Path.Combine(
Environment.CurrentDirectory,
WikiSite.ShortPath.TrimStart('/'));
}
}
public string Filepath
{
get
{
return Path.Combine(Dir, Template.Url);
}
}
public ExcelManager ExcelManager
{
get
{
if (_excelManager == null)
{
_excelManager = new ExcelManager(Filepath);
_excelManager.Read();
}
return _excelManager;
}
}
public override IList GetPages(ImportStatus status = null)
{
string result = null;
if (WikiSite == null)
return null;
if (!Directory.Exists(Dir))
Directory.CreateDirectory(Dir);
if (!File.Exists(Filepath))
{
result = new Page(WikiSite, "File:" + Template.Url).DownloadImage(Filepath);
if (status != null)
{
status.Error = result;
}
}
if (result == null)
{
foreach (HSSFRow row in ExcelManager.Records)
{
var title = GetTitle(row);
var yearsInTitle = ExcelManager.GetYears(title);
var yearInColumn = ExcelManager.GetValue(
row,
JsonManager.GetJsonTranslation(ExcelFileColumns.Year));
if (yearsInTitle != null
&& !string.IsNullOrEmpty(yearInColumn)
&& !yearsInTitle.Contains(yearInColumn))
{
if (status != null)
{
status.Warning = string.Format(
"row #{0} has dates conflict: in description {1} but in year {2}",
row.RowNum,
yearsInTitle,
yearInColumn);
}
}
}
}
if (result == null)
{
if (status != null)
{
status.Result = ExcelManager.Columns;
}
return ExcelManager.Records;
}
return null;
}
public override void Import(object page, Job job)
{
var row = (HSSFRow)page;
var title = GetTitle(row);
if (!string.IsNullOrEmpty(title))
{
var wikiPage = new Page(WikiSite, title);
while (Encoding.UTF8.GetByteCount(title) > 255)
{
wikiPage.Title = title = title.Remove(title.Length - 1);
}
wikiPage.LoadTextOnly();
var wikiTemplateParametersModified = new List<string>();
var wikiPageCreated = false;
if (string.IsNullOrEmpty(wikiPage.Text))
{
wikiPageCreated = true;
wikiPage.Text = string.Format("{{{{{0}|{1}=}}}}", TemplateName, TemplateExcelParameter);
}
var thisExcelFile = "[[file:" + job.Template.Url + "]]";
var excelValue = wikiPage.GetFirstTemplateParameter(TemplateName, TemplateExcelParameter);
if (!excelValue.Contains(thisExcelFile))
{
excelValue = thisExcelFile;
/*if (!string.IsNullOrEmpty(excelValue))
excelValue += ";";
excelValue += thisExcelFile;*/
SetTemplateValue(wikiPage, TemplateExcelParameter, excelValue, wikiTemplateParametersModified);
}
foreach (var selectedColumn in job.Parameters.Split(';'))
{
var value = ExcelManager.GetValue(row, selectedColumn);
string selectedColumnEn = null;
foreach (Enum enumValue in typeof(ExcelFileColumns).GetEnumValues())
{
if (JsonManager.GetJsonTranslation(enumValue)
.Equals(selectedColumn, StringComparison.InvariantCultureIgnoreCase))
{
selectedColumnEn = enumValue.ToString();
if (selectedColumnEn == ExcelFileColumns.Description.ToString())
{
value = ExcelManager.GetDescription(value);
var yearsInTitle = ExcelManager.GetYears(title) ?? new[] { string.Empty };
int num;
var date = ExcelManager.GetValue(row, JsonManager.GetJsonTranslation(ExcelFileColumns.Year))
?? yearsInTitle[0];
var isCorrectDate = int.TryParse(date, out num);
var month = ExcelManager.GetValue(row, JsonManager.GetJsonTranslation(ExcelFileColumns.Month));
if (!string.IsNullOrEmpty(month))
{
date += "-" + month;
if (!int.TryParse(month, out num))
isCorrectDate = false;
}
var day = ExcelManager.GetValue(row, JsonManager.GetJsonTranslation(ExcelFileColumns.Day));
if (!string.IsNullOrEmpty(day))
{
date += "-" + day;
if (!int.TryParse(day, out num))
isCorrectDate = false;
}
if (isCorrectDate)
SetTemplateValue(
wikiPage,
"date",
date,
wikiTemplateParametersModified);
else
{
SetTemplateValue(
wikiPage,
"dateText",
yearsInTitle.Length > 1 ? yearsInTitle.Aggregate((c, s) => c + "-" + s) : date,
wikiTemplateParametersModified);
}
}
else if (selectedColumnEn == ExcelFileColumns.Source.ToString())
{
var url = ExcelManager.GetUrl(value);
if (url != null)
SetTemplateValue(
wikiPage,
"url",
url,
wikiTemplateParametersModified);
var language = ExcelManager.GetLanguage(value);
if (language != null)
SetTemplateValue(
wikiPage,
"language",
language,
wikiTemplateParametersModified);
/// |nameAtUrl=<how to calc?>
}
break;
}
}
if (selectedColumnEn != null)
SetTemplateValue(wikiPage, selectedColumnEn, value, wikiTemplateParametersModified, selectedColumn);
}
if (wikiPageCreated)
ObjectAdded(title);
if (wikiTemplateParametersModified.Count > 0)
ObjectUpdated(title);
if (wikiTemplateParametersModified.Count > 0 || wikiPageCreated)
wikiPage.Save(
thisExcelFile + "; columns: " + wikiTemplateParametersModified.Aggregate((c, s) => c + ", " + s),
true);
}
}
private string GetTitle(HSSFRow row)
{
var descriptionColumn = JsonManager.GetJsonTranslation(ExcelFileColumns.Description);
var titleValue = ExcelManager.GetValue(
row,
descriptionColumn);
return ExcelManager.TrimTitle(titleValue);
}
private void SetTemplateValue(
Page wikiPage,
string parameterEn,
string value,
List<string> wikiTemplateParametersModified,
string parameter = null)
{
var wasValue = wikiPage.GetFirstTemplateParameter(TemplateName, parameterEn);
///&& (string.IsNullOrEmpty(wasValue)
/// || (wasValue != value && parameter != null/*overwrite only values from Excel, not calculated*/)
if (!string.IsNullOrEmpty(value) && wasValue != value)
{
wikiPage.SetTemplateParameter(TemplateName, parameterEn, value, true);
wikiTemplateParametersModified.Add(parameter ?? parameterEn);
}
}
}
}