public static void FillDocumentSheet(SpreadsheetDocument document, SheetExportData sheetExportData)
{
Sheet sheet = SheetHelper.GetSheet(document, sheetExportData.SheetName);
var workbookPart = document.WorkbookPart;
if (sheetExportData.SheetName == "Крепление")
{
GenerateTemplate(workbookPart, sheet.Id.Value, sheetExportData.ColumnsBlockToInsert);
}
MoveAllCharts(workbookPart, sheet.Id.Value, sheetExportData.ChartsToInserts);
if (sheetExportData.SheetName == "КСБ-6.00")
{
// Общее количество строк в шаблоне
var templateRowsCount = sheetExportData.CopyRowIndexTo - sheetExportData.CopyRowIndexFrom;
for (int i = 0; i < sheetExportData.RowBlocksForCopyAndInsert - 1; i++)
{
var toRowIndex = sheetExportData.CopyRowIndexTo + (templateRowsCount * i);
CopyAndInsertRows(sheetExportData.CopyRowIndexFrom, toRowIndex, templateRowsCount, i + 2,
workbookPart, sheet.Id.Value, sheetExportData.ArraysToInserts);
}
}
FillFields(workbookPart, sheet.Id.Value, sheetExportData.FieldsToInserts);
FillTables(workbookPart, sheet.Id.Value, sheetExportData.ArraysToInserts);
FillCharts(workbookPart, sheet.Id.Value, sheetExportData.ChartsToInserts);
HideCharts(workbookPart, sheet.Id.Value, sheetExportData.ChartsToInserts);
FillChartsXY(workbookPart, sheet.Id.Value, sheetExportData.ChartsXYToInserts);
}
public async Task ClearApproveCellsAsync()
{
var sheet = await GetSheetAsync();
var players = SheetHelper.GetOrderedPlayers(sheet.Values);
var dateOfNextGame = DateHelper.GetNearestGameDateMoscowTime(DateTime.Now).AddDays(7);
var newValues = new List <IList <object> >
{
new List <object>()
{
dateOfNextGame.ToRussianDayMonthString()
}
};
foreach (var player in players)
{
newValues.Add(new List <object>()
{
string.Empty
});
}
await UpdateSheetAsync(newValues, SheetHelper.GetApproveColumnRange(sheet.Values, players.Count));
}
private static List <CoverageEntity> SheetRangeToEntityList(IList <IList <object> > sheetRange)
{
List <CoverageEntity> methodList = new List <CoverageEntity>();
if (sheetRange != null && sheetRange.Count > 0)
{
foreach (var row in sheetRange)
{
var methodEntity = new CoverageEntity
{
Repository = row[0].ToString().Trim(),
Project = row[1].ToString().Trim(),
Class = row[2].ToString().Trim(),
Method = row[3].ToString().Trim(),
Coverage = SheetHelper.ObjectToInt(row[4]),
Team = row[6].ToString().Trim(),
UpdatedDate = row.Count < 10 ? string.Empty : row[9].ToString().Trim(),
RawData = row,
};
if (string.IsNullOrWhiteSpace(methodEntity.Method) == false)
{
methodList.Add(methodEntity);
}
}
}
return(methodList);
}
public int DiffBetweenIdColAndDataCol()
{
long idCol = string.IsNullOrEmpty(IdCellRange) ? 0 : SheetHelper.ColToNumber(new RangeReference(IdCellRange).From.Col);
long startCol = string.IsNullOrEmpty(DataCellRange) ? 0 : SheetHelper.ColToNumber(new RangeReference(DataCellRange).From.Col);
return((int)(idCol - startCol));
}
private async Task <BatchUpdateValuesResponse> UpdateSheetAsync(IList <IList <object> > values, string range)
{
var updateDataRequest = SheetHelper.GetBatchUpdateRequest(values, range);
var request = _sheetsService.Spreadsheets.Values.BatchUpdate(updateDataRequest, AppSettings.GoogleDocSheetId);
return(await request.ExecuteAsync(new CancellationTokenSource(Constants.ASYNC_OPERATION_TIMEOUT).Token));
}
public UpdateUniqueRowValidator(WizardContext context, SheetHelper sheet)
{
//在 Insert 模式不進行此類型驗證。
_skip_validate = (context.CurrentMode != ImportMode.Update);
if (_skip_validate)
{
return;
}
_sheet = sheet;
_primary_condition = context.IdentifyField;
_check_conditions = new List <ImportCondition>();
List <ImportCondition> conditions = new List <ImportCondition>();
foreach (ImportCondition each in context.UpdateConditions)
{
if (each.ContainsAllField(context.SelectedFields.ToArray()))
{
conditions.Add(each);
}
}
_check_conditions = conditions;
if (!conditions.Contains(context.IdentifyField))
{
conditions.Add(context.IdentifyField); //連同 Primary Condition 一起加入。
}
XmlElement recordData = context.DataSource.GetUniqueFieldData();
_key_sets = new ConditionKeySetCollection(conditions, recordData, "Record");
}
private async Task <int> CreateNewPlayerRowAsync(IList <IList <object> > values, string range, string playerName)
{
if (SheetHelper.GetUserRowNumber(values, playerName) != -1)
{
throw new ArgumentException($"Player {playerName} already exists.");
}
var players = SheetHelper.GetOrderedPlayers(values, playerName);
var newValues = SheetHelper.GetNewValues(values, players);
// Add empty rows to clear unnecesary data
var endEmptyRows = 30;
while (--endEmptyRows != 0)
{
newValues.Add(new List <object> {
string.Empty, string.Empty
});
}
await UpdateSheetAsync(newValues, range);
await UpdateLastRowStyle(values.IndexOf(SheetHelper.GetTotalsRow(values)), newValues.IndexOf(SheetHelper.GetTotalsRow(values)));
return(SheetHelper.GetUserRowNumber(newValues, playerName));
}
private static void WriteData(SheetHelper excelWriter, ExcelReference reference)
{
try
{
if (excelWriter.ConfirmedOverwrite == false)
{
Common.StatusBar.AddMessage(Locale.English.WarningOverwriteNotAccepted);
}
else
{
ExcelAsyncUtil.QueueAsMacro(() =>
{
var range = Tools.ReferenceToRange(reference);
try
{
// do not attempt to lock because this is executing on foreground thread
excelWriter.PopulateData(range);
}
finally
{
Marshal.ReleaseComObject(range);
}
Common.StatusBar.AddMessage(Locale.English.UdfDataWritingSuccess);
});
}
}
catch (Exception e)
{
Common.StatusBar.AddMessage(Locale.English.UdfCompleteError);
Common.HandlePotentialQuandlError(e, false);
}
}
public DialogResult ShowDialog(Window parent)
{
int ret;
var docHandler = Document != null ? Document.Handler as PrintDocumentHandler : null;
if (docHandler != null)
{
Control.Options |= NSPrintPanelOptions.ShowsPreview;
ret = docHandler.Print(true, parent, Control) ? 1 : 0;
}
else
{
var printInfo = settings.ToNS();
if (parent != null)
{
var parentHandler = (IMacWindow)parent.Handler;
var closeSheet = new SheetHelper();
Control.BeginSheet(printInfo, parentHandler.Control, closeSheet, new Selector("printPanelDidEnd:returnCode:contextInfo:"), IntPtr.Zero);
ret = (int)NSApplication.SharedApplication.RunModalForWindow(parentHandler.Control);
}
else
{
ret = (int)Control.RunModalWithPrintInfo(printInfo);
}
}
return(ret == 1 ? DialogResult.Ok : DialogResult.Cancel);
}
public async Task <string> GetApprovedPlayersMessageAsync()
{
var sheet = await GetSheetAsync();
var players = SheetHelper.GetOrderedPlayers(sheet.Values);
return(SheetHelper.GetApprovedPlayersString(players));
}
public void NewValues_Get_ReturnsCorrectCollection()
{
var newValues = SheetHelper.GetNewValues(_values, _players);
Assert.Equal(_values.Count, newValues.Count);
Assert.Equal("Всего", newValues.LastOrDefault()?[0]);
Assert.Equal("=SUM(B3:B22)", newValues.LastOrDefault()?[1]);
}
private static void ObjectToEnumAssert(object value, MethodLevel expected)
{
//// Act
var actual = SheetHelper.ObjectToEnum <MethodLevel>(value);
//// Assert
actual.Should().Be(expected);
}
private void ColumnLetterAssert(int column, string letter)
{
//// Target
var actual = SheetHelper.ColumnToLetter(column);
//// Assert
actual.Should().Be(letter);
}
public void GetTotalsRow_ReturnsCorrectRow()
{
var totalsRow = SheetHelper.GetTotalsRow(_values);
Assert.Equal(2, totalsRow.Count());
Assert.Equal("Всего", totalsRow[0]);
Assert.Equal("=SUM(B3:B22)", totalsRow[1]);
}
private void wpCollectKeyInfo_AfterPageDisplayed(object sender, WizardPageChangeEventArgs e)
{
try
{
if (e.PageChangeSource == eWizardPageChangeSource.BackButton)
{
return;
}
cboIdField.SelectedIndex = -1;
cboIdField.Items.Clear();
cboIdField.DisplayMember = "Name";
cboValidateField.SelectedItem = null;
cboValidateField.Items.Clear();
cboValidateField.DisplayMember = "FieldName";
cboValidateField.Items.Add(EmptyShiftField);
cboValidateField.SelectedIndex = 0;
SheetHelper sheet = new SheetHelper(Context.SourceFile);
IEnumerable <string> fields = sheet.Fields;
ImportFieldCollection acceptFields = Context.SupportFields.Intersect(fields);
List <ImportCondition> conditions = Context.UpdateConditions;
foreach (ImportCondition each in conditions)
{
if (each.ContainsAllField(fields))
{
cboIdField.Items.Add(each);
}
}
foreach (ImportField each in acceptFields)
{
if (each.ShiftCheckable)
{
cboValidateField.Items.Add(each);
}
}
if (cboIdField.Items.Count <= 0)
{
ctlerrors.SetError(cboIdField, "在來源資料中沒有任何可以當識別欄的欄位。");
}
else
{
ctlerrors.SetError(cboIdField, string.Empty);
}
wpCollectKeyInfo.NextButtonEnabled = eWizardButtonState.Auto;
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show(ex.Message);
//CurrentUser.ReportError(ex);
wpCollectKeyInfo.NextButtonEnabled = eWizardButtonState.False;
}
}
//统计微信每日活跃人数
public FileResult GetWeixinActivePeopleChart()
{
int times = 10;
Dictionary <string, List <int> > data = new Dictionary <string, List <int> >();
List <string> xData = new List <string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
List <int> advData = new List <int>();
MySqlConnection conn = new MySqlConnection(Config.ConfigManager.getConfig("mysql-connection"));
conn.Open();
string sql = string.Format("select count(*) times, date(update_time) dates from tbl_wx_user group by date(update_time) order by update_time desc limit {0};", times);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
Dictionary <DateTime, int> dateset = new Dictionary <DateTime, int>();
while (reader.Read())
{
dateset.Add(reader.GetDateTime("dates"), reader.GetInt32("times"));
}
conn.Close();
for (int i = times; i >= 0; i--)
{
int count = 0;
DateTime time = dt.AddDays(-i);
foreach (var item in dateset)
{
if (item.Key.Date.Equals(time.Date))
{
count = item.Value;
}
}
advData.Add(count);
}
data.Add("微信活跃人数", advData);
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Column;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return(new FileStreamResult(imageStream, "image/png"));
}
public async Task UpsertPlayerAsync(string playerName)
{
var sheet = await GetSheetAsync();
if (SheetHelper.GetUserRowNumber(sheet.Values, playerName) == -1)
{
await CreateNewPlayerRowAsync(sheet.Values, sheet.Range, playerName);
}
}
public void Test_GetSheet001()
{
Column columnA = new Column
{
Title = "TitleA",
Primary = false,
Type = ColumnType.CHECKBOX,
Symbol = Symbol.STAR
};
// Specify properties of the second column
Column columnB = new Column
{
Title = "TitleB",
Primary = true,
Type = ColumnType.TEXT_NUMBER
};
Column columnC = new Column
{
Title = "TitleC",
Primary = false,
Type = ColumnType.TEXT_NUMBER,
};
// Create sheet in "Sheets" folder (specifying the 2 columns to include in the sheet)
Sheet newSheet = _smartsheetClient.SheetResources.CreateSheet(new Sheet
{
Name = "newsheetTest002",
Columns = new Column[] { columnA, columnB, columnC }
}
);
long?sheetId = newSheet.Id;
_testSheetIds.Add((long)sheetId);
List <SheetLevelInclusion> sheetLevelInclusionList = new List <SheetLevelInclusion>();
sheetLevelInclusionList.Add(SheetLevelInclusion.COLUMN_TYPE);
List <long> columnIdList = new List <long>()
{
0, 3
};
//Sheet createdSheet = _smartsheetClient.SheetResources.GetSheet((long)sheetId, null, null, null, null, columnIdList, null, null); ;
Sheet gotSheet = _smartsheetClient.SheetResources.GetSheet((long)sheetId, null, null, null, null, null, null, null);
evaluate(
SheetHelper.CompareForCreateSheets(gotSheet, newSheet),
"Test_GetSheet001",
"Test_GetSheet001",
SheetHelper.DisplayText(gotSheet),
(SheetHelper.DisplayText(newSheet)));
}
public void GetUserRange_ReturnsCorrectRange()
{
var range1 = SheetHelper.GetUserRange(3);
var range2 = SheetHelper.GetUserRange(4);
var range3 = SheetHelper.GetUserRange(5);
Assert.Equal("Участие в играх!B3", range1);
Assert.Equal("Участие в играх!B4", range2);
Assert.Equal("Участие в играх!B5", range3);
}
public void GetUserRowNumber_ReturnsCorrectRowNumber()
{
var row1 = SheetHelper.GetUserRowNumber(_values, "User1");
var row2 = SheetHelper.GetUserRowNumber(_values, "User5");
var row3 = SheetHelper.GetUserRowNumber(_values, "User9");
Assert.Equal(3, row1);
Assert.Equal(7, row2);
Assert.Equal(11, row3);
}
private async Task UpdateLastRowStyle(int oldTotalsRowIndex, int newTotalsRowIndex)
{
if (oldTotalsRowIndex == newTotalsRowIndex)
{
return;
}
var cutPasteStyleRequest = SheetHelper.GetMoveCellsStyleRequest(oldTotalsRowIndex, newTotalsRowIndex);
var updateRequest = _sheetsService.Spreadsheets.BatchUpdate(cutPasteStyleRequest, AppSettings.GoogleDocSheetId);
await updateRequest.ExecuteAsync(new CancellationTokenSource(Constants.ASYNC_OPERATION_TIMEOUT).Token);
}
public void Test_CreateSheet006()
{
//Create Columns
Column columnA = new Column
{
Title = "TitleA",
Primary = false,
Type = ColumnType.CHECKBOX,
Symbol = Symbol.STAR
};
Column columnB = new Column
{
Title = "TitleB",
Primary = true,
Type = ColumnType.TEXT_NUMBER
};
Column columnC = new Column
{
Title = "TitleC",
Primary = false,
Type = ColumnType.PICKLIST,
Symbol = Symbol.PROGRESS
};
// Create sheet in "Sheets" folder
Sheet newSheet = _smartsheetClient.SheetResources.CreateSheet(new Sheet
{
Name = "newsheetTest001",
Columns = new Column[] { columnA, columnB, columnC }
}
);
//Add new sheet id to list for TestCleanup
_testSheetIds.Add((long)newSheet.Id);
// Manually creating a sheet with expected values to compare.
Sheet expectedSheet = new Sheet
{
Name = "newsheetTest001",
AccessLevel = AccessLevel.OWNER,
Columns = new Column[] { columnA, columnB, columnC }
};
//Force Failure
evaluate(
false,
"Test_CreateSheet006",
"Test_CreateSheet006",
SheetHelper.DisplayText(newSheet),
SheetHelper.DisplayText(expectedSheet));
}
private void Application_SheetActivate(object Sh)
{
//sheet = (Excel.Worksheet)Sh;
if (SheetHelper.isExisting("Содержание"))
{
if (CheckState() == States.Editing && isBordersEnabled)
{
drawer.EnableSheetChangeEvent();
drawer.DrawSheetBorders();
}
}
}
public void GetStartRows_ReturnsCorrectRows()
{
var startRows = SheetHelper.GetStartRows(_values);
Assert.Equal(2, startRows.Count());
Assert.Equal(0, startRows.First().Count);
Assert.Equal(4, startRows.Skip(1).First().Count);
Assert.Null(startRows.Skip(1).First()[0]);
Assert.Equal("5 февраля", startRows.Skip(1).First()[1]);
Assert.Null(startRows.Skip(1).First()[2]);
Assert.Equal("Поле:", startRows.Skip(1).First()[3]);
}
//获取公众号订阅总量报表
public FileResult GetTotalSubInfoChart()
{
int times = 10;
Dictionary <string, List <int> > data = new Dictionary <string, List <int> >();
List <string> xData = new List <string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
List <int> advData = new List <int>();
MySqlConnection conn = new MySqlConnection(Config.ConfigManager.getConfig("mysql-connection"));
conn.Open();
for (int i = times; i >= 0; i--)
{
string sql = string.Format("select count(*) times from tbl_wx_user where to_days(now()) - to_days(subscribe_time) >= {0};", i);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
advData.Add(reader.GetInt32("times"));
}
else
{
advData.Add(0);
}
reader.Close();
}
conn.Close();
data.Add("微信订阅人数", advData);
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Line;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return(new FileStreamResult(imageStream, "image/png"));
}
public void NewValues_AddPlayer_ReturnsCorrectCollection()
{
var newPlayers = new List <IList <object> >(_players)
{
new List <object> {
"User21"
}
};
var newValues = SheetHelper.GetNewValues(_values, newPlayers);
Assert.Equal(_values.Count + 1, newValues.Count);
Assert.Equal("Всего", newValues.LastOrDefault()?[0]);
Assert.Equal("=SUM(B3:B23)", newValues.LastOrDefault()?[1]);
Assert.True(newValues.Count(v => v?.Count > 0 && v[0]?.ToString() == "User21") == 1);
}
public void Test_CreateSheet002()
{
//Create Columns
Column columnA = new Column
{
Title = "TitleA",
Primary = false,
Type = ColumnType.CHECKBOX,
Symbol = Symbol.STAR
};
Column columnB = new Column
{
Title = "TitleB",
Primary = true,
Type = ColumnType.TEXT_NUMBER
};
// Create sheet in "Sheets" folder
Sheet newSheet = _smartsheetClient.SheetResources.CreateSheet(new Sheet
{
Name = "newsheetTest002",
Columns = new Column[] { columnA, columnB }
}
);
//Add new sheet id to list for TestCleanup
_testSheetIds.Add((long)newSheet.Id);
Sheet newSheet2 = _smartsheetClient.SheetResources.CreateSheet(new Sheet
{
Name = "newsheetTest002",
Columns = new Column[] { columnA, columnB }
}
);
//Add new sheet id to list for TestCleanup
_testSheetIds.Add((long)newSheet2.Id);
evaluate(
SheetHelper.CompareForCreateSheets(newSheet, newSheet2),
"Test_CreateSheet002",
"Test_CreateSheet002",
SheetHelper.DisplayText(newSheet),
(SheetHelper.DisplayText(newSheet2)));
}
public IList <CoverageEntity> GetList()
{
var startIndex = 1;
var maxRow = string.Empty;
var sheetName = "Coverage";
IList <CoverageEntity> sheetMethodList = new List <CoverageEntity>();
//// TODO J 須為動態
IList <IList <object> > values = this._googleSheetsService.GetValues(this._sheetsId, $"{sheetName}!A{startIndex + 1}:J{maxRow}");
var index = startIndex;
if (values != null && values.Count > 0)
{
foreach (var row in values)
{
index++;
if (row.Count < 10)
{
Console.WriteLine($"Pass Sheet Row:{index}, Value: {row.EnumerableToString()}");
continue;
}
var methodEntity = new CoverageEntity
{
Repository = row[0].ToString().Trim(),
Project = row[1].ToString().Trim(),
Namespace = row[2].ToString().Trim(),
Class = row[3].ToString().Trim(),
Method = row[4].ToString().Trim(),
Coverage = row.Count > 5 ? SheetHelper.ObjectToInt(row[5]) : 0,
TargetCoverage = row.Count > 7 ? SheetHelper.ObjectToInt(row[7]) : 0,
Level = SheetHelper.ObjectToEnum <MethodLevel>(row[8]),
Team = row[9].ToString().Trim(),
RawIndex = index,
RawData = row,
};
if (string.IsNullOrWhiteSpace(methodEntity.Method) == false)
{
sheetMethodList.Add(methodEntity);
}
}
}
return(sheetMethodList);
}
public async Task UpdateApproveCellAsync(string playerName, string cellValue)
{
var sheet = await GetSheetAsync();
var userRow = SheetHelper.GetUserRowNumber(sheet.Values, playerName);
if (userRow == -1)
{
userRow = await CreateNewPlayerRowAsync(sheet.Values, sheet.Range, playerName);
}
var range = SheetHelper.GetUserRange(userRow);
var request = _sheetsService.Spreadsheets.Values.Update(SheetHelper.GetValueRange(range, cellValue), AppSettings.GoogleDocSheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
await request.ExecuteAsync(new CancellationTokenSource(Constants.ASYNC_OPERATION_TIMEOUT).Token);
}
public static Excel.Worksheet GetListPage()
{
Excel.Worksheet sheet;
string name = "Содержание";
if (!SheetHelper.isExisting(name))
{
sheet = SheetHelper.getSheet(name);
FillDefault(sheet);
FormatCells(sheet);
}
else
{
sheet = SheetHelper.getSheet(name);
}
return(sheet);
}
Sheet5_Startup
(
object sender,
System.EventArgs e
)
{
m_oSheetHelper = new SheetHelper(this, this.Groups);
ThisWorkbook oThisWorkbook = Globals.ThisWorkbook;
oThisWorkbook.GraphLaidOut +=
new EventHandler<GraphLaidOutEventArgs>(
this.ThisWorkbook_GraphLaidOut);
oThisWorkbook.VerticesMoved +=
new EventHandler<VerticesMovedEventArgs2>(
this.ThisWorkbook_VerticesMoved);
oThisWorkbook.GroupsCollapsedOrExpanded +=
new EventHandler<GroupsCollapsedOrExpandedEventArgs>(
this.ThisWorkbook_GroupsCollapsedOrExpanded);
CommandDispatcher.CommandSent +=
new RunCommandEventHandler(this.CommandDispatcher_CommandSent);
}
/// <summary>
/// 将汇入讯息写入到最后的Excel中
/// </summary>
private void SaveImportMessage()
{
if (mImportWizard.ImportMessages.Positions.Count > 0)
{
string ImportFileName = Path.Combine(Constants.ValidationReportsFolder, Path.GetFileNameWithoutExtension(mImportOption.SelectedDataFile) + "(汇入报告).xls");
int MaxDataColumn = mValidatedInfo.ResultHelper.Sheet.Cells.MaxDataColumn;
SheetHelper helper = new SheetHelper(mImportOption.SelectedDataFile);
helper.SwitchSeet(mImportOption.SelectedSheetName);
InitialMessageHeader(helper.Sheet);
mImportWizard.ImportMessages.Positions.ForEach(x => helper.SetValue(x, MaxDataColumn, mImportWizard.ImportMessages[x]));
helper.Book.Save(ImportFileName);
btnViewResult.Visible = true;
btnViewResult.Click += (sender, e) =>
{
try
{
Process.Start(ImportFileName);
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show(ex.Message);
}
};
}
}
/// <summary>
/// 建构式，传入精灵选项
/// </summary>
/// <param name="args"></param>
public SelectSource(ArgDictionary args)
: base(args)
{
InitializeComponent();
//初始化参数
mArgs = args;
mImportWizard = args["ImportWizard"] as ImportWizard;
mImportOption = TryGetOption();
mImportName = mImportWizard.ValidateRule.Root.GetAttributeText("Name");
mImportWizard.ImportOption = mImportOption;
this.Text = mImportName + "-选择档案与汇入方式" + "(" + CurrentStep + "/" + TotalStep + ")";
//加载验证规则及XSLT
LoadValudateRule();
//在使用者选择数据表时，将数据表的字段都记录下来
lstSheetNames.SelectedIndexChanged += (sender, e) =>
{
mSheetHelper.SwitchSeet("" + lstSheetNames.SelectedItem);
mImportOption.SelectedSheetName = "" + lstSheetNames.SelectedItem;
mImportOption.SheetFields = mSheetHelper.Fields;
this.NextButtonEnabled = ValidateNext();
};
//检视验证规则
btnViewRule.Click += (sender, e) =>
{
XmlViewForm ViewForm = new XmlViewForm();
ViewForm.PopXml(mImportName, mImportOption.SelectedValidateFile);
ViewForm.ShowDialog();
};
//检视填表说明
btnViewRuleExcel.Click += (sender, e) =>
{
Workbook book = new Workbook();
string BookAndSheetName = mImportName + "(空白表格)";
if (!string.IsNullOrEmpty(BookAndSheetName))
book.Worksheets[0].Name = BookAndSheetName;
int Position = 0;
foreach (XElement Element in mImportWizard.ValidateRule.Root.Element("FieldList").Elements("Field"))
{
StringBuilder strCommentBuilder = new StringBuilder();
string Name = Element.GetAttributeText("Name");
bool Required = Element.GetAttributeBool("Required", false);
book.Worksheets[0].Cells[0, Position].PutValue(Name);
book.Worksheets[0].Cells[0, Position].Style.HorizontalAlignment = TextAlignmentType.Center;
book.Worksheets[0].Cells[0, Position].Style.VerticalAlignment = TextAlignmentType.Center;
if (Required)
{
book.Worksheets[0].Cells[0, Position].Style.BackgroundColor = System.Drawing.Color.Red;
strCommentBuilder.AppendLine("此为必要字段。");
}
foreach (XElement SubElement in Element.Elements("Validate"))
strCommentBuilder.AppendLine(SubElement.GetAttributeText("Description"));
book.Worksheets[0].Comments.Add(0, (byte)Position);
book.Worksheets[0].Comments[0, Position].Note = strCommentBuilder.ToString();
book.Worksheets[0].Comments[0, Position].WidthInch = 3;
Position++;
}
book.Worksheets[0].AutoFitColumns();
Campus.Report.ReportSaver.SaveWorkbook(book, Path.Combine(Constants.ValidationReportsFolder, BookAndSheetName));
};
//选择源数据档案
btnSelectFile.Click += (sender, e) =>
{
DialogResult dr = SelectSourceFileDialog.ShowDialog();
if (dr == DialogResult.OK)
{
try
{
//记录来源文件名
string FileName = SelectSourceFileDialog.FileName;
txtSourceFile.Text = SelectSourceFileDialog.FileName;
mImportOption.SelectedDataFile = FileName;
mSheetHelper = new SheetHelper(FileName);
//将数据表列表显示在画面上
lstSheetNames.Items.Clear();
foreach (Worksheet sheet in mSheetHelper.Book.Worksheets)
lstSheetNames.Items.Add(sheet.Name);
lstSheetNames.SelectedIndex = 0;
}
catch (Exception ve)
{
MsgBox.Show(ve.Message);
}
}
};
//将前一步不出现，下一步先失效
this.PreviousButtonVisible = false;
this.NextButtonEnabled = false;
}
//*************************************************************************
// Method: Sheet5_Startup()
//
/// <summary>
/// Handles the Startup event on the worksheet.
/// </summary>
///
/// <param name="sender">
/// Standard event argument.
/// </param>
///
/// <param name="e">
/// Standard event argument.
/// </param>
//*************************************************************************
private void Sheet5_Startup(
object sender,
System.EventArgs e
)
{
Globals.ThisWorkbook.SetVisualAttribute2 +=
new SetVisualAttributeEventHandler(
this.ThisWorkbook_SetVisualAttribute2);
// Create the helper object.
m_oSheetHelper = new SheetHelper(this, this.Groups);
}
IgnoreTableSelectionChange
(
SheetHelper oSheetHelper
)
{
Debug.Assert(oSheetHelper != null);
AssertValid();
return (
// Work around an Excel bug. See the
// ExcelUtil.SpecialCellsBeingCalled property for details.
ExcelUtil.SpecialCellsBeingCalled
||
m_bIgnoreSelectionEvents
||
!oSheetHelper.TableExists
);
}
