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
{
}
}
void evaluator_OnBeforeCompile(object sender, EventArgs e)
{
spreadsheet.BeginUpdate();
codeEditor.BeforeCompile();
IWorkbook workbook = spreadsheet.Document;
workbook.Options.Culture = defaultCulture;
bool loaded = workbook.LoadDocument("Document.xlsx");
Debug.Assert(loaded);
}
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();
}
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();
}
}
}
