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)
{
IWorkbook document = spreadsheetControl.Document;
document.BeginUpdate();
codeEditor.BeforeCompile();
spreadsheetControl.CreateNewDocument();
document.Unit = DevExpress.Office.DocumentUnit.Document;
document.EndUpdate();
}
public string GenerateXmlFromTemplate()
{
if (Template == null)
{
return("");
}
var spreadsheetControl = new SpreadsheetControl();
spreadsheetControl.CreateNewDocument();
//existingDatas = new List<ExistingData>();
var tempName = Path.GetTempFileName() + @".Xlsx";
//tempPath = Path.Combine(Path.GetTempPath(), tempName);
//using (var stream = new FileStream(tempPath, FileMode.Create))
// Template.SaveToStream(stream);
//object o = Missing.Value;
//excelWorkbook = excelApp.Workbooks.Open(tempPath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
var dataList = GetExistingData(/*excelWorkbook*/);
if (dataList.Count == 0)
{
return("");
}
var xmlSerializer = new XmlSerializer(dataList.GetType());
using (var writer = new StringWriter())
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Indent = true, NewLineChars = "\r\n"
}))
{
xmlSerializer.Serialize(xmlWriter, dataList);
var data = writer.ToString();
//excelWorkbook.Close(false);
//File.Delete(tempPath);
return(data);
}
}
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);
}
}
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);
}
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();
}
}
}