private void btnFilter_Click(object sender, EventArgs e)
{
fileName = Path.GetFileName(txtPath.Text);
extension = Path.GetExtension(fileName);
if (extension == ".xls")
{
spreadSheet.LoadDocument(txtPath.Text, DocumentFormat.Xls);
}
else if (extension == ".xlsx")
{
spreadSheet.LoadDocument(txtPath.Text, DocumentFormat.Xlsx);
}
else
{
MessageBox.Show("This file is not supported! Sorry!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
spreadSheet.Dock = DockStyle.Fill;
panelControl1.Controls.Add(spreadSheet);
}
public static string LoadExcel(SpreadsheetControl spreadsheet)
{
//SplashScreenManager ssManger = new SplashScreenManager();
string filename = "";
try
{
XtraOpenFileDialog Opendlg = new XtraOpenFileDialog();
Opendlg.Filter = "EXCEL 파일 (*.xlsx)|*.xlsx|모든파일(*.*)|*.*";
if (Opendlg.ShowDialog() == DialogResult.OK)
{
// ssManger.ShowWaitForm();
//ProgressPanel panel = CUtil.GetProgress("Data Loading", "EXCEL 파일을 읽는 중 입니다.");
//panel.Parent = this;
//this.Controls.Add(panel);
//panel.Show();
//panel.BringToFront();
filename = Opendlg.FileName;
using (FileStream stream = new FileStream(filename, FileMode.Open))
{
//progressPanel1.Parent = this;
//this.Controls.Add(progressPanel1);
//progressPanel1.Show();
//progressPanel1.BringToFront();
///EXCEL DATA를 Load 한다.
spreadsheet.LoadDocument(stream, DocumentFormat.Xlsx);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// ssManger.CloseWaitForm();
}
return(filename);
}
//根据路径打开文件
private void OpenFileByPath(string FilePath)
{
string ExtName = (System.IO.Path.GetExtension(FilePath)).Replace(".", "").ToLower();
int index = LandEvaluationBll.BindingDataTable.GetImageIndexByExtName(ExtName);
XtraTabPage tabPage = new XtraTabPage();
tabPage.Text = System.IO.Path.GetFileName(FilePath);
tabPage.ImageIndex = index;
Control fileViewerControl = null;
if (index == 1)
{
RichEditControl richEditControl = new RichEditControl();
richEditControl.LoadDocument(FilePath);
fileViewerControl = richEditControl;
}
else if (index == 2)
{
SpreadsheetControl spreadsheetControl = new SpreadsheetControl();
spreadsheetControl.LoadDocument(FilePath);
fileViewerControl = spreadsheetControl;
}
else if (index == 4)
{
PdfViewer pdfViewer = new PdfViewer();
pdfViewer.LoadDocument(FilePath);
fileViewerControl = pdfViewer;
}
else if (index == 5)
{
PdfViewer pdfViewer = new PdfViewer();
pdfViewer.LoadDocument(FilePath);
fileViewerControl = pdfViewer;
}
else if (index == 9)
{
this.axMapControl1.LoadMxFile(FilePath);
this.xtraTabControl2.SelectedTabPageIndex = 0;
this.xtraTabControl1.SelectedTabPageIndex = 1;
return;
}
tabPage.Controls.Add(fileViewerControl);
fileViewerControl.Dock = DockStyle.Fill;
this.xtraTabControl2.TabPages.Add(tabPage);
this.xtraTabControl2.SelectedTabPage = tabPage;
//this.xtraTabControl2.Refresh();
}
private void btn_saveClose_Click(object sender, EventArgs e)
{
if (taskExecuted)
{
if (checkEdit_addToView.CheckState == CheckState.Checked)
{
spreadsheetControl.LoadDocument(docPath);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
}
else
{
XtraMessageBox.Show("未执行成功任何任务！");
}
this.Close();
}
private void btn_saveClose_Click(object sender, EventArgs e)
{
if (thread != null && thread.ThreadState != ThreadState.Stopped)
{
return;
}
if (taskExecuted)
{
if (checkEdit_addToView.CheckState == CheckState.Checked)
{
spreadsheetControl.LoadDocument(docPathOriginal);
}
}
else
{
XtraMessageBox.Show("未执行成功任何任务！");
}
this.Close();
}
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();
}
void InitExcelFile(string outputFilePath)
{
// copy the template (stored in resources) to the selected path
File.WriteAllBytes(outputFilePath, Properties.Resources.ReleaseInfo1);
spreadsheetControl = new SpreadsheetControl();
if (!spreadsheetControl.LoadDocument(outputFilePath))
{
throw new Exception("Failed to load template Excel file.");
}
workbook = spreadsheetControl.Document;
string errMess = "Copying template failed - template does not have the expected format.";
if (workbook.Worksheets.Count != 9)
{
throw new Exception(errMess);
}
sheetRelease = workbook.Worksheets[0];
sheetYears = workbook.Worksheets[1];
sheetDatasets = workbook.Worksheets[2];
sheetPrivate = workbook.Worksheets[3];
sheetAddOns = workbook.Worksheets[4];
sheetGlobalExtensions = workbook.Worksheets[5];
sheetCountriesExtensions = workbook.Worksheets[6];
sheetExtensionsContent = workbook.Worksheets[7];
sheetBestMatches = workbook.Worksheets[8];
InsertYearColumns(sheetRelease, systemYearsPublic, out colDataPublic);
int dummy; InsertYearColumns(sheetYears, systemYears, out dummy);
InsertYearColumns(sheetBestMatches, systemYears, out dummy);
InsertExtensionsColumns(true, false);
InsertExtensionsColumns(false, true);
InsertExtensionsColumns(true, true);
colTrainingPublic = colDataPublic + 1;
colData = 1; colTraining = 2; colHypo = 3; colDataNA = 4;
colPrivData = 1; colPrivSys = 2; colPrivPol = 3; colPrivFun = 4; colPrivPar = 5;
colMTR = 1; colLMA = 2; colNRR = 3;
}
public void GenerateExcelReport(DoSoEmail email, object item, Session session, string folderPath)
{
var xml = Report?.Xml;
if (!string.IsNullOrEmpty(xml))
{
var control = new SpreadsheetControl();
using (var ms = new MemoryStream(Convert.FromBase64String(xml)))
control.LoadDocument(ms, DocumentFormat.OpenXml);
if (control.Document.MailMergeParameters.Any())
{
throw new InvalidOperationException("Need Parameters");
}
var docs = control.Document.GenerateMailMergeDocuments();
foreach (var doc in docs)
{
var fullName = Path.Combine(folderPath, HS.MyTempName + ".Xlsx");
doc.SaveDocument(fullName);
email.SourceFilePath += fullName + ";";
}
}
//if (ReportDefinition != null)
//{
// var fullName = Path.Combine(folderPath, HS.MyTempName + ".Xlsx");
// SetParameteValues(this, item, session);
// var newFileaName = FastExportingMethod.ExportData2Excel(ReportDefinition, session, fullName);
// if (!string.IsNullOrEmpty(newFileaName))
// email.SourceFilePath += newFileaName + ";";
// else
// email.EmailBody += string.Format("{0}{0}{1}", Environment.NewLine, AlternativeText);
//}
}
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 ucNaviFiles_TreeList_DoubleClick(object sender, EventArgs e)
{
TreeList tree = sender as TreeList;
TreeListHitInfo hi = tree.CalcHitInfo(tree.PointToClient(Control.MousePosition));
if (hi.Node == null)
{
return;
}
string nodeName = (string)hi.Node["name"];
Control openFileTool = null;
try
{
//如果已经有这个tabPage
XtraTabPage ifTabPage = ComponentOperator.IfHasTabPage(nodeName, this.xtraTabControl_Main);
if (ifTabPage != null)
{
this.xtraTabControl_Main.SelectedTabPage = ifTabPage;
return;
}
//如果是文件夹，则返回
string type = (string)hi.Node["type"];
if (type == "Folder")
{
return;
}
//如果文件不存在
string path = (string)hi.Node["path"];
if (!File.Exists(path))
{
MessageBox.Show("文件已丢失，请刷新文件目录后再尝试打开。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//如果不包含该TabPage，则新建
string extension = (string)hi.Node["ext"];
string fileType = ComponentOperator.GetFileTypeByExtension(extension);
switch (fileType)
{
case "":
if (MessageBox.Show("本系统暂不支持该格式[" + extension + "]的文件，是否尝试使用系统默认程序打开？", "提示", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(path);
}
return;
case "RichTextEdit":
RichEditControl rec = new RichEditControl();
rec.LoadDocument(path);
openFileTool = rec;
break;
case "SpreadSheet": //表格控件
SpreadsheetControl ssc = new SpreadsheetControl();
ssc.LoadDocument(path);
openFileTool = ssc;
break;
case "MapControl":
AxMapControl mapControl = new AxMapControl();
mapControl.BeginInit(); //必须有begin和end
mapControl.Location = new System.Drawing.Point(0, 0);
mapControl.Name = "mapControl";
mapControl.Dock = DockStyle.Fill;
//MapControl不支持先声明，后设置，故而直接设置
XtraTabPage xtp = new XtraTabPage();
xtp.Text = nodeName;
xtp.Controls.Add(mapControl);
mapControl.Dock = DockStyle.Fill;
this.xtraTabControl_Main.TabPages.Add(xtp);
this.xtraTabControl_Main.SelectedTabPage = xtp;
mapControl.EndInit(); //必须有begin和end
mapControl.Refresh();
xtp.Refresh();
this.xtraTabControl_Main.Refresh();
this.Refresh();
mapControl.LoadMxFile(path);
break;
default:
return;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
if (openFileTool != null)
{
//TabPage
XtraTabPage xtp = new XtraTabPage();
xtp.Text = nodeName;
xtp.Controls.Add(openFileTool);
openFileTool.Dock = DockStyle.Fill;
this.xtraTabControl_Main.TabPages.Add(xtp);
this.xtraTabControl_Main.SelectedTabPage = xtp;
openFileTool.Refresh();
xtp.Refresh();
this.xtraTabControl_Main.Refresh();
this.Refresh();
}
}
}
private void SheetFind_DisplayData()
{
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
_sdMAIN = new SpreadsheetControl();
_sdMAIN.Options.Behavior.ShowPopupMenu = DevExpress.XtraSpreadsheet.DocumentCapability.Disabled;
string strFTP_PATH = "";
string strUSE_TYPE = "";
string strFILE_NAME = "";
_pucWorkOrderInfoPopup_T50Entity.WINDOW_NAME = this.Name.ToString();
_pucWorkOrderInfoPopup_T50Entity.CRUD = "R";
//조회할거 프로시져
_dtList = new ucWorkOrderInfoPopup_T50Business().Sheet_Info_Sheet(_pucWorkOrderInfoPopup_T50Entity); //엑셀 시트 조회하기
//엑셀서식관리 되면 다시 설정하기
strUSE_TYPE = _dtList.Rows[0]["USE_TYPE"].ToString();
strFILE_NAME = _dtList.Rows[0]["FILE_NAME"].ToString();
switch (strUSE_TYPE)
{
case "PRINT":
strFTP_PATH = string.Format(@"{0}{1}/{2}/", _pFTP_PATH, "ORDER_FORM", _pWINDOW_NAME);
break;
case "VIEW":
strFTP_PATH = string.Format(@"{0}{1}/{2}/{3}/", _pFTP_PATH, "USER", "PROGRAM_VIEW", _pWINDOW_NAME);
break;
case "REGIT":
strFTP_PATH = string.Format(@"{0}{1}/{2}/{3}/", _pFTP_PATH, "USER", "PROGRAM_VIEW", _pWINDOW_NAME);
break;
}
string curfile = Application.StartupPath + "\\Template\\" + strFILE_NAME;
// 같은 것이 있으면, 폴더에서 해당 파일명을 불러와서 SpreadSheet에 띄우기 / 없으면, 폴더에 다운
if (!File.Exists(curfile))
{
CoFAS_FTPManager.FTPDownLoad(strFTP_PATH, strFILE_NAME, _pFTP_ID, _pFTP_PW, curfile, false);
}
//using (FileStream file = File.OpenWrite(curfile))
using (Stream file = File.Open(curfile, FileMode.Open))
{
if (file != null)
{
_sdMAIN.LoadDocument(file, DocumentFormat.Xlsx);
}
else
{
_sdMAIN.CloseCellEditor(CellEditorEnterValueMode.Default);
_sdMAIN.CreateNewDocument();
}
}
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager));
}
finally
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
}
//根据路径打开文件
private void OpenFileByPath(string FilePath)
{
string ExtName = (System.IO.Path.GetExtension(FilePath)).Replace(".", "").ToLower();
int index = LandEvaluationBll.BindingDataTable.GetImageIndexByExtName(ExtName);
XtraTabPage tabPage = new XtraTabPage();
tabPage.Text = System.IO.Path.GetFileName(FilePath);
tabPage.ImageIndex = index;
Control fileViewerControl = null;
if (index == 1)
{
RichEditControl richEditControl = new RichEditControl();
richEditControl.LoadDocument(FilePath);
fileViewerControl = richEditControl;
}
else if (index == 2)
{
SpreadsheetControl spreadsheetControl = new SpreadsheetControl();
spreadsheetControl.LoadDocument(FilePath);
fileViewerControl = spreadsheetControl;
}
else if (index == 4)
{
PdfViewer pdfViewer = new PdfViewer();
pdfViewer.LoadDocument(FilePath);
fileViewerControl = pdfViewer;
}
else if (index == 5)
{
PdfViewer pdfViewer = new PdfViewer();
pdfViewer.LoadDocument(FilePath);
fileViewerControl = pdfViewer;
}
else if (index == 9)
{
this.axMapControl1.LoadMxFile(FilePath);
this.xtraTabControl2.SelectedTabPageIndex = 0;
this.xtraTabControl1.SelectedTabPageIndex = 1;
return;
}
tabPage.Controls.Add(fileViewerControl);
fileViewerControl.Dock = DockStyle.Fill;
this.xtraTabControl2.TabPages.Add(tabPage);
this.xtraTabControl2.SelectedTabPage = tabPage;
//this.xtraTabControl2.Refresh();
}
private async void formulaPeekControl_Completed(object sender, FormulaPeekArgs e)
{
this.windowsUIButtonPanelActions.HidePeekForm();
if (!e.Write) return;
try
{
this.spreadsheetControl.BeginUpdate();
var messages = new List<string>();
using (new MarqueeProgressDialog(this.ParentForm, description: "Обработка данных"))
{
await Task.Run(() =>
{
using (var spreadsheet = new SpreadsheetControl {AllowDrop = false})
{
using (var ms = new MemoryStream(e.FormulaBytes))
{
var formulas = FormulaExpressionParser.ParseStream(ms); //TODO: rewrite
var formDatas = e.FormDataList;
for (var i = 0; i < formDatas.Count; i++)
{
var formData = formDatas[i];
var formBytes = formData.file.contents;
var format = formData.document_format.IsOpenXml()
? DocumentFormat.OpenXml
: DocumentFormat.Xls;
spreadsheet.LoadDocument(formBytes, format);
foreach (var formula in formulas)
{
formula.Source = spreadsheet.Document;
formula.Target = this.spreadsheetControl.Document;
try
{
if (i == 0)
formula.WriteToTarget();
else
formula.AppendToTarget();
}
catch
{
messages.AddRange(formula.ErrorMessages.Where(errorMessage => !messages.Contains(errorMessage)));
}
}
}
}
}
});
}
if (messages.Count > 0)
{
var countToTake = 10;
var takenMessages = messages.Take(countToTake).ToList();
if (takenMessages.Count > countToTake)
takenMessages.Add("...");
var message = string.Join("\n", takenMessages);
this.ShowFlyoutMessageBox("Ошибка заполнения", message);
}
}
catch (Exception ex)
{
this.ShowFlyoutMessageBox("Ошибка", ex.Message, FlyoutCommand.OK);
}
finally
{
this.spreadsheetControl.EndUpdate();
}
}
// a note on speed: this is quite slow, but this seems to be due to loading the Spreadsheet library
// most of the time is taken after the first two lines and it gets much faster if one calls the function again
internal static List <string> GetChanges(string releaseVersion)
{
try
{
SpreadsheetControl emLog = new SpreadsheetControl();
if (!emLog.LoadDocument(new EMPath(EM_AppContext.FolderEuromodFiles).GetEmLogFilePath()))
{
return(new List <string>());
}
// find the relevant worksheet
Worksheet log = null;
foreach (Worksheet ws in emLog.Document.Worksheets) // currently the name is 'F2.30-current'
{
if (ws.Name.ToLower().Contains("current"))
{
log = ws; break;
}
}
;
if (log == null)
{
return(new List <string>());
}
// collect the column-indices of all columns between 'Version' and 'Change', i.e. 'File', 'Policy', etc.
List <int> colInfo = new List <int>(); int colVersion = -1;
for (int c = 0; c < 100; ++c)
{
if (GetCellValue(log, 0, c) == "version")
{
colVersion = c; continue;
}
if (colVersion >= 0)
{
colInfo.Add(c);
}
if (GetCellValue(log, 0, c) == "change")
{
break;
}
}
if (colVersion < 0)
{
return(new List <string>());
}
// find the rows that belong to the release-version and compose change-report
int startRow = -1; List <string> changes = new List <string>();
for (int r = 1; r < 100000; ++r) // currently there are about 5000 rows
{
bool belongsToVersion = GetCellValue(log, r, colVersion) == releaseVersion.ToLower().Trim();
if (belongsToVersion)
{
if (startRow == -1)
{
startRow = r;
}
} // start collecting if column Version contains the release version
else
{
if (startRow != -1)
{
break;
}
} // finished collecting, if not - usually column Version should be empty
if (!belongsToVersion || GetCellValue(log, r, colVersion + 1).StartsWith("merged with"))
{
continue; // skip the row 'Merged with version xxx and saved as EuromodFiles_yyy' (which is not a change)
}
// compose change-rows like 'File: DE.XML; Policy: tco_de; System: all; Change: Update tco_de 2015-2017'
string change = string.Empty;
foreach (int c in colInfo)
{
string info = GetCellValue(log, r, c, false); if (info == string.Empty)
{
continue;
}
change += GetCellValue(log, 0, c, false) + ": " + info + "; ";
}
changes.Add(change.Trim(new char[] { ' ', ';' }));
}
return(changes);
}
catch { return(new List <string>()); }
}
public void SetTemplateData(SpreadsheetControl spc, DxSpreadsheetControlTemplateData value)
{
spc.LoadDocument(Base2e15.Decode(value.Content), DocumentFormat.Xlsx);
}
public static string ExportData2Excel(ReportDefinition selectedReport, /*SqlConnection sqlConnection*/ Session uow, string reportName)
{
var sqlQueryCollection = selectedReport.SqlQueryCollection.Where(x => x.ExpiredOn == null).OrderBy(x => x.SheetIndex);
var ds = new DataSet();
var list = new List <Tuple <DataTable, SqlQuery> >();
ExecutedQueries = string.Empty;
foreach (var query in sqlQueryCollection)
{
var commandTextWithParameterValues = query.Query;
commandTextWithParameterValues = ReplaceParameterValues(selectedReport, commandTextWithParameterValues);
ExecutedQueries += string.Format("{0}{1}{1}{2}{1}{1}", commandTextWithParameterValues, Environment.NewLine, "---------");
var data = uow.ExecuteQueryWithMetadata(commandTextWithParameterValues);
var resultSetColumnNames = data.ResultSet[0];
var resultSetData = data.ResultSet[1];
var rows = resultSetData.Rows;
var dt = new DataTable {
TableName = query.Name
};
foreach (var selectStatementResultRow in resultSetColumnNames.Rows)
{
Type type;
try
{
type = Type.GetType("System." + selectStatementResultRow.XmlValues[2]);
}
catch (Exception)
{
type = typeof(string);
}
dt.Columns.Add(selectStatementResultRow.Values[0].ToString(), type);
}
foreach (var row in rows)
{
var values = new List <object>();
for (var columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
{
var val = row.Values[columnIndex];
if (val is NullValue)
{
val = null;
}
values.Add(val);
}
dt.Rows.Add(values.ToArray());
}
ds.Tables.Add(dt);
list.Add(new Tuple <DataTable, SqlQuery>(dt, query));
}
var newControl = new SpreadsheetControl();
var template = new SpreadsheetControl();
//var stream = new FileStream(@"C:\Users\Beka\Desktop\Test.xlsx", FileMode.Open);
template.LoadDocument(@"C:\Users\Beka\Desktop\Test.xlsx");
newControl.CreateNewDocument();
for (int i = 0; i < ds.Tables.Count; i++)
{
if (i > 0)
{
newControl.Document.Worksheets.Add();
}
var worksheet = newControl.Document.Worksheets[i];
worksheet.CopyFrom(template.Document.Worksheets[i]);
var range = template.Document.Worksheets[i].GetUsedRange();
worksheet.Cells[range.TopRowIndex, range.LeftColumnIndex].CopyFrom(range, PasteSpecial.Borders | PasteSpecial.NumberFormats | PasteSpecial.ColumnWidths | PasteSpecial.Formats);
worksheet.Import(ds.Tables[i], true, 1, 1);
}
for (int i = 0; i < template.Document.Worksheets.Count; i++)
{
var sheet = template.Document.Worksheets[i];
var range = sheet.GetDataRange().Where(x => !x.Value.IsEmpty);
var worksheet = newControl.Document.Worksheets[i];
foreach (var item in range)
{
worksheet.Cells[item.RowIndex, item.ColumnIndex].Value = item.Value;
}
//worksheet.Cells[range.TopRowIndex, range.LeftColumnIndex].CopyFrom(range, PasteSpecial.Values);
//foreach (var item in range.Where(x => !x.Value.IsEmpty))
// worksheet.Cells.CopyFrom(range, PasteSpecial.All);
// worksheet.Range.
//worksheet.InsertCells(range);
//worksheet.Cells[item.RowIndex, item.ColumnIndex].SetValue(item.Value);
}
newControl.ActiveWorksheet.Workbook.SaveDocument(reportName);
//ExportToExcel(list, path);
return(reportName);
}
public virtual void Import()
{
Control targetControl = FocusedGrid();
if (targetControl == null)
{
return;
}
if (targetControl.GetType().Equals(typeof(GridControl)))
{
try
{
GridControl gd = (GridControl)targetControl;
GridView abv = (GridView)gd.MainView;
DataTable sdt = (DataTable)gd.DataSource;
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel|*.xls;*.xlsx";
if (openFileDialog.ShowDialog() != DialogResult.Cancel)
{
SharedFunc.ShowProcess(this, 0, 5);
SpreadsheetControl spreadsheetControl = new SpreadsheetControl();
spreadsheetControl.LoadDocument(openFileDialog.FileName);
SharedFunc.ShowProcess(1);
Worksheet worksheet = spreadsheetControl.Document.Worksheets.ActiveWorksheet;
CellCollection cellCollection = worksheet.Cells;
DataTable idt = worksheet.CreateDataTable(cellCollection.CurrentRegion, true);
//务必添加引用DevExpress.Docs.v16.2.dll
DataTableExporter exporter = worksheet.CreateDataTableExporter(cellCollection.CurrentRegion, idt, true);
exporter.Export();
SharedFunc.ShowProcess(1);
foreach (DataColumn dc in idt.Columns)
{
foreach (GridColumn bgc in abv.Columns)
{
if (bgc.Caption.Trim().Equals(dc.ColumnName.Trim()))
{
dc.ColumnName = bgc.FieldName;
break;
}
}
}
//克隆结构 变更数据类型
DataTable ndt = idt.Clone();
foreach (DataColumn dc in ndt.Columns)
{
foreach (DataColumn bgc in sdt.Columns)
{
if (bgc.Caption.Trim().Equals(dc.ColumnName.Trim()))
{
dc.ColumnName = bgc.ColumnName;
dc.DataType = bgc.DataType;
break;
}
}
}
foreach (DataRow dr in idt.Rows)
{
DataRow ndr = ndt.NewRow();
foreach (DataColumn dc in idt.Columns)
{
ndr[dc.ColumnName] = dr[dc.ColumnName];
}
ndt.Rows.Add(ndr);
}
SharedFunc.ShowProcess(1);
sdt.Merge(ndt, true);
SharedFunc.ShowProcess(5);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
SharedFunc.ShowProcess(int.MaxValue);
}
}
}
public static XtraReport CreateQueryReport(QueryRegionSummaryModel model)
{
var regions = model.RegionList;
var queries = model.QueryList;
var year = model.Year;
var form = model.Form;
var reportDataList = new QueryReportDataCollection();
using (var spreadsheetControl = new SpreadsheetControl() { AllowDrop = false })
{
foreach (var region in regions)
{
var formData = region.region_form_data.FirstOrDefault(t => t.form == form && t.send_date.Year == year);
if (formData != null)
{
var excelData = formData.file.contents;
var format = formData.document_format.IsOpenXml()
? DocumentFormat.OpenXml
: DocumentFormat.Xls;
spreadsheetControl.LoadDocument(excelData, format);
}
foreach (var query in queries)
{
var stringValue = "";
try
{
var content = query.content;
if (content.StartsWith("@"))
{
var propName = content.Substring(1);
var propInfo = typeof(region).GetProperty(propName);
var value = propInfo.GetValue(region);
var valueType = value.GetType();
if (valueType.GetInterface("System.Collections.IEnumerable") != null && valueType != typeof(String))
{
var collection = (IEnumerable<object>)value;
stringValue = collection.Join(", ");
}
else if (valueType == typeof(bool))
{
stringValue = (bool)value ? "Да" : "Нет";
}
else
{
stringValue = value.ToString();
}
}
else
{
var value = spreadsheetControl.Document.Range[content].Value;
stringValue = GetStringValueFromCell(value);
}
}
catch (Exception ex)
{
stringValue = "Не удалось";
}
reportDataList.Add(new QueryReportRegionData(region, query, stringValue));
}
}
}
var groupedList = reportDataList.Group().ToList();
var report = new QueryReport { ReportData = groupedList };
Reports.Add(report);
return report;
}