internal void GenerateInfo(string outputFilePath, List <string> _countries)
{
EM_UI_MainForm mainForm = EM_AppContext.Instance.GetActiveCountryMainForm(); if (mainForm != null)
{
mainForm.Cursor = Cursors.WaitCursor;
}
try
{
countries = _countries;
AssessCountryInfo();
InitExcelFile(outputFilePath);
FillExcelFile();
spreadsheetControl.SaveDocument();
UserInfoHandler.ShowSuccess(string.Format("Successfully generated Release info file '{0}'.", outputFilePath)); // perhaps open ???
}
catch (Exception exception)
{
UserInfoHandler.ShowException(exception, string.Format("Failed to generate Release info file '{0}'.", outputFilePath), false);
}
finally { if (mainForm != null)
{
mainForm.Cursor = Cursors.Default;
}
}
}
public DxSpreadsheetControlTemplateData GetTemplateData(SpreadsheetControl spc)
{
var data = new DxSpreadsheetControlTemplateData();
var arr = spc.SaveDocument(DocumentFormat.Xlsx);
data.Content = Base2e15.Encode(arr);
return(data);
}
private void Form1_Load(object sender, EventArgs e)
{
SpreadsheetControl spreadsheetControl = new SpreadsheetControl();
string path = Path.Combine(Application.StartupPath, "견적내역서_입력폼.xlsx");
spreadsheetControl.LoadDocument(path);
spreadsheetControl.BeginUpdate();
IWorkbook workbook = spreadsheetControl.Document;
string sheetName = $"Test_Test";
Worksheet worksheet = workbook.Worksheets["Sample"];
spreadsheetControl.SaveDocument();
workbook.Worksheets[sheetName].CopyFrom(worksheet);
//workbook.Worksheets.Add();
spreadsheetControl.SaveDocument();
spreadsheetControl.EndUpdate();
spreadsheetControl.Dispose();
}
public void SaveExcel()
{
SpreadsheetControl spread = new SpreadsheetControl();
Worksheet sheet = spread.Document.Worksheets[0];;
string excelName = Resource.Settings.Options.ExcelPath + "\\"
+ fName + "_Raw_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
FormSaveDataRaw form = new FormSaveDataRaw();
form.SavingProgress.Properties.Maximum = bufferedSeries.PointsCount;
form.SavingProgress.Position = 0;
form.Show();
spread.BeginUpdate();
try
{
for (int i = 0; i < bufferedSeries.Count; i++)
{
sheet[0, i].Value = bufferedSeries[i].Name;
}
for (int i = 0; i < bufferedSeries.PointsCount; i++)
{
for (int j = 0; j < bufferedSeries.Count; j++)
{
sheet[i + 1, j].Value = bufferedSeries[j].Points[i];
if (form.Cancel == true)
{
break;
}
Application.DoEvents();
Win32.SwitchToThread();
}
form.SavingProgress.Position = i + 1;
}
}
finally
{
spread.EndUpdate();
spread.SaveDocument(excelName);
form.Hide();
}
}
private static byte[] CreateSpreadsheetReport(IList<QueryReportDataGroup> reportData)
{
using (var spreadsheet = new SpreadsheetControl() {AllowDrop = false})
{
spreadsheet.BeginUpdate();
var workbook = spreadsheet.Document;
workbook.CreateNewDocument();
workbook.Worksheets.Add("Запросы");
var sheet = workbook.Worksheets.First();
var i = 0;
var j = 0;
foreach (var pair in reportData.First().Values) //шапка по горизонтали
{
sheet.Cells[0, j++].Value = pair.Key.title;
}
i = 1;
j = 0;
var grouped = reportData.GroupBy(t => t.GroupHead);
foreach (var group in grouped) //группы по вертикали
{
sheet.Cells[i++, j].Value = group.Key;
foreach (var data in group) //данные по вертикали
{
j = 0;
sheet.Cells[i, j++].Value = data.Head;
foreach (var pair in data.Values) //запрос-значение по горизонтали
{
sheet.Cells[i, j++].Value = pair.Value.ToString();
}
}
}
spreadsheet.EndUpdate();
using (var stream = new MemoryStream())
{
spreadsheet.SaveDocument(stream, DocumentFormat.Xlsx);
return stream.ToArray();
}
}
}
private void GenerateCaseBaseExcelReport(string sExportFile)
{
var listCase = ((ListView)View).SelectedObjects.Cast <CaseBase>().ToList();
var listReceiveCase = listCase.Where(c => c.dt_TransferDate == DateTime.MinValue).ToList();
var listTransferCase = listCase.Where(c => c.dt_TransferDate != DateTime.MinValue).ToList();
var dtMinTransfer = listTransferCase.Count == 0 ? DateTime.Today : listTransferCase.Min(c => c.dt_TransferDate);
var dtMinReceive = listReceiveCase.Count == 0 ? DateTime.Today : listReceiveCase.Min(c => c.dt_ReceiveDate);
var dtMin = dtMinTransfer > dtMinReceive ? dtMinReceive : dtMinTransfer;
if (dtMinTransfer == DateTime.MinValue)
{
dtMin = dtMinReceive;
}
if (dtMinReceive == DateTime.MinValue)
{
dtMin = dtMinTransfer;
}
var dtMaxTransfer = listTransferCase.Max(c => c.dt_TransferDate);
var dtMaxReceive = listTransferCase.Max(c => c.dt_ReceiveDate);
var dtMax = dtMaxTransfer > dtMaxReceive ? dtMaxTransfer : dtMaxReceive;
if (dtMaxTransfer == DateTime.MinValue)
{
dtMax = dtMaxReceive;
}
if (dtMaxReceive == DateTime.MinValue)
{
dtMax = dtMaxTransfer;
}
var dtMonthBegin = new DateTime(dtMin.Year, dtMin.Month, 1);
var listCorporationName = listReceiveCase.Select(c => new { c.Client?.Code, c.Client?.Name, c.Client?.Country?.s_Name }).ToList();
listCorporationName.AddRange(listTransferCase.Select(c => new { c.Agency?.Code, c.Agency?.Name, c.Agency?.Country?.s_Name }).ToList());
listCorporationName = listCorporationName.Where(c => !string.IsNullOrWhiteSpace(c.Name)).Distinct().OrderBy(c => c.Name).ToList();
var sscReport = new SpreadsheetControl();
File.Copy(System.Windows.Forms.Application.StartupPath + "\\Template\\外代交换案件.xlsx", sExportFile, true);
sscReport.LoadDocument(sExportFile);
var baseCell = sscReport.Document.Worksheets["全部"].Cells["G5"];
for (int row = 0; row < listCorporationName.Count; row++)
{
int nReceived = 0;
int nTransfer = 0;
for (int col = 0; dtMonthBegin.AddMonths(col) < dtMax; col++)
{
baseCell[-2, col * 2].Value = $"{dtMonthBegin.AddMonths(col).Year}年{dtMonthBegin.AddMonths(col).Month}月";
baseCell[-1, col * 2].Value = $"发出案件";
baseCell[-1, col * 2 + 1].Value = $"返回案件";
baseCell[row, -4].Value = listCorporationName[row].Name;
baseCell[row, -3].Value = listCorporationName[row].s_Name;
var listReceived = listReceiveCase.Where(c => c.dt_ReceiveDate >= dtMonthBegin.AddMonths(col) && c.dt_ReceiveDate < dtMonthBegin.AddMonths(col + 1) && c.Client?.Name == listCorporationName[row].Name);
var listTransfer = listTransferCase.Where(c => c.dt_TransferDate >= dtMonthBegin.AddMonths(col) && c.dt_TransferDate < dtMonthBegin.AddMonths(col + 1) && c.Agency?.Name == listCorporationName[row].Name);
if (listTransfer.Any())
{
baseCell[row, col * 2].Value = listTransfer.Count();
nTransfer += listTransfer.Count();
AddComments(sscReport.Document.Worksheets["全部"], baseCell[row, col * 2], "System", string.Join("\r\n", listTransfer.Select(t => t.s_OurNo)));
}
if (listReceived.Any())
{
baseCell[row, col * 2 + 1].Value = listReceived.Count();
nReceived += listReceived.Count();
AddComments(sscReport.Document.Worksheets["全部"], baseCell[row, col * 2 + 1], "System", string.Join("\r\n", listReceived.Select(t => t.s_OurNo)));
}
}
if (nTransfer > 0)
{
baseCell[row, -2].Value = nTransfer;
}
if (nReceived > 0)
{
baseCell[row, -1].Value = nReceived;
}
}
GenerateSubCaseReport(listCase.Where(c => c.b_IsAppDemand).ToList(), sscReport.Document.Worksheets["申请人指定"], true);
GenerateSubCaseReport(listCase.Where(c => c.b_IsApplication).ToList(), sscReport.Document.Worksheets["新申请"], false);
GenerateSubCaseReport(listCase.Where(c => c.b_IsSepcial).ToList(), sscReport.Document.Worksheets["特殊案"], false);
GenerateSubCaseReport(listCase.Where(c => c.b_IsMiddle).ToList(), sscReport.Document.Worksheets["转入案"], false);
GenerateSubCaseReport(listCase.Where(c => c.b_IsDivCase).ToList(), sscReport.Document.Worksheets["分案"], false);
sscReport.SaveDocument();
}
private void barManagerFolder_ItemClick(object sender, ItemClickEventArgs e)
{
if (e.Item.Caption.Equals("Create Folder"))
{
if (directoryName.Contains(staffName) || directoryName.Contains("Shared"))
{
if (directoryName != "")
{
MakeDirectory(directoryName);
treeList1.ClearNodes();
InitData();
}
}
else
{
MessageBox.Show("Sorry, Cant Create folder in other staff members Folders");
}
}
else if (e.Item.Caption.Equals("Delete Folder"))
{
DirectoryInfo di = new DirectoryInfo(directoryName);
if (!checkDirectoryRoot(di.Name) && directoryName.Contains(staffName))
{
if (directoryName != "")
{
RemoveDirectory(directoryName);
treeList1.ClearNodes();
InitData();
}
}
else
{
MessageBox.Show("Sorry, you can't remove folders of other staff members or the root Directory");
}
}
else if (e.Item.Caption.Equals("Import File(s)"))
{
if (directoryName.Contains(staffName) || directoryName.Contains("Shared"))
{
ImportFile(directoryName);
treeList1.ClearNodes();
InitData();
}
}
else if (e.Item.Caption.Equals("New File"))
{
if (directoryName.Contains(staffName) || directoryName.Contains("Shared"))
{
DialogResult dr = new DialogResult();
var formAddNewFile = new FormAddNewFile();
dr = formAddNewFile.ShowDialog();
if (dr == DialogResult.OK)
{
if (formAddNewFile.fileName.Contains(".docx"))
{
try
{
using (var richeditControl = new RichEditControl())
{
richeditControl.CreateNewDocument(false);
richeditControl.SaveDocument(directoryName + "\\" + formAddNewFile.fileName, DocumentFormat.OpenXml);
}
}
catch (Exception ex)
{
MessageBox.Show(MiscStuff.GetAllMessages(ex));
}
}
else if (formAddNewFile.fileName.Contains(".xlsx"))
{
try
{
using (var spreadsheetControl = new SpreadsheetControl())
{
spreadsheetControl.CreateNewDocument();
spreadsheetControl.SaveDocument(directoryName + "\\" + formAddNewFile.fileName, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
}
}
catch (Exception ex)
{
MessageBox.Show(MiscStuff.GetAllMessages(ex));
}
}
}
treeList1.ClearNodes();
InitData();
}
}
}