private void GetData() { SpreadsheetControl spreadsheetControl1 = new SpreadsheetControl(); try { spreadsheetControl1.CreateNewDocument(); spreadsheetControl1.BeginUpdate(); Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet; Range range = worksheet.Range["A1:D1"]; range.Merge(); range.Alignment.Vertical = DevExpress.Spreadsheet.SpreadsheetVerticalAlignment.Center; range.Alignment.Horizontal = DevExpress.Spreadsheet.SpreadsheetHorizontalAlignment.Center; range.Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin); worksheet.Cells["A1"].Value = "维修统计导出"; worksheet.Cells["A1"].RowHeight = 10 * 15; worksheet.Cells["A1"].ColumnWidth = 600; SpreadsheetHelper.SetSheetColumn(worksheet.Cells["A2"], "车牌号"); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["B2"], "所属部门"); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["C2"], "维修次数"); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["D2"], "维修费用"); for (int i = 0; i < _list.Count; i++) { SpreadsheetHelper.SetSheetColumn(worksheet.Cells["A" + (i + 2)], _list[i].CarNo); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["B" + (i + 2)], _list[i].DepartmentName); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["C" + (i + 2)], ValueConvert.ToString(_list[i].RepairCount)); SpreadsheetHelper.SetSheetColumn(worksheet.Cells["D" + (i + 2)], ValueConvert.ToString(_list[i].RepairExpenses)); } string dir = System.Environment.CurrentDirectory; string filePath = FileDialogHelper.SaveExcel("", dir); if (!string.IsNullOrEmpty(filePath)) { try { IWorkbook workbook = spreadsheetControl1.Document; if (!filePath.Contains(".xls")) { filePath += ".xls"; } workbook.SaveDocument(filePath); } catch (Exception ex) { } } spreadsheetControl1.EndUpdate(); } catch { } finally { } }
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(); } }
static void SelectedCell(SpreadsheetControl control) { control.BeginUpdate(); control.SelectedCell.FillColor = Color.LightGray; CellRange c = control.SelectedCell; c.FillColor = Color.Blue; CellRange currentSelection = control.Selection; Formatting rangeFormatting = currentSelection.BeginUpdateFormatting(); rangeFormatting.Borders.SetOutsideBorders(DevExpress.Utils.DXColor.Green, BorderLineStyle.MediumDashDot); currentSelection.EndUpdateFormatting(rangeFormatting); control.EndUpdate(); }
void evaluator_OnAfterCompile(object sender, OnAfterCompileEventArgs args) { IWorkbook workbook = spreadsheet.Document; foreach (Worksheet sheet in workbook.Worksheets) { sheet.PrintOptions.PrintGridlines = true; } Worksheet active = workbook.Worksheets.ActiveWorksheet; CellRange usedRange = active.GetUsedRange(); active.SelectedCell = usedRange[usedRange.RowCount * usedRange.ColumnCount - 1].Offset(1, 1); codeEditor.AfterCompile(args.Result); spreadsheet.EndUpdate(); }
static void FreezeRow(SpreadsheetControl control) { control.BeginUpdate(); try { //Access the active worksheet. Worksheet worksheet = control.Document.Worksheets.ActiveWorksheet; // Access the cell range that is currently visible. CellRange visibleRange = control.VisibleRange; //Range visibleRange = control.Document.Range.FromLTRB(10, 15, 15, 20); // Freeze the top visible row. worksheet.FreezeRows(0, visibleRange); } finally { control.EndUpdate(); } }
static void SetSelectedRanges(SpreadsheetControl control) { control.BeginUpdate(); Worksheet worksheet = control.ActiveWorksheet; CellRange r1 = worksheet.Range["A1:B10"]; CellRange r2 = worksheet.Range["E12"]; CellRange r3 = worksheet.Range["D4:E7"]; List <CellRange> rlist = new List <CellRange>() { r1, r2, r3 }; control.SetSelectedRanges(rlist); control.SelectedCell = worksheet.Cells["E5"]; control.EndUpdate(); }
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(); }
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(); } } }