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; }