/// <summary>
/// Обработчик события - нажатие кнопки "Экспорт"
/// </summary>
private void panelManagement_OnEvtExportDo()
{
KEY_CONTROLS key_ctrl = KEY_CONTROLS.UNKNOWN;
HDataGridView dgv;
string pathRemoteTemplate = string.Empty
,pathUserTemplate = string.Empty
,nameFolderRemoteTemplate = NAMEFOLDER_TEMPLATE_SOTIASSO_DAY
,nameFolderUserTemplate = string.Empty
,nameFileTemplate = NAMEFILE_TEMPLATE_SOTIASSO_DAY;
ExcelFile excel;
System.Data.DataTable tableExportDo;
int iColumn = STARTCOLUMN_TEMPLATE_SOTIASSO_DAY
,iStartRow = STARTROW_TEMPLATE_SOTIASSO_DAY
,i_agregate = I_AGREGATE;
//Excel Application Object
Microsoft.Office.Interop.Excel.Application oExcelApp;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkbook;
try {
// определить путь к шаблону отчета
pathRemoteTemplate = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,Path.Combine(nameFolderRemoteTemplate,nameFileTemplate));
// определить каталог для размещения итогового отчета
nameFolderUserTemplate = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),@"Statistic");
// определить полный путь для размещения итогового отчета
pathUserTemplate = Path.Combine(nameFolderUserTemplate
,nameFileTemplate.Replace(@"TEC",(string)(findControl(KEY_CONTROLS.CBX_TEC_LIST.ToString()) as ComboBox).SelectedItem)
.Replace(@"DATE",m_panelManagement.CurDateTime.Date.ToString(@"yyyyMMdd")));
// создать при необходимости каталог на ПК пользователя
if (Directory.Exists(nameFolderUserTemplate) == false)
{
Directory.CreateDirectory(nameFolderUserTemplate);
}
else
{
;
}
// проверить: выполняется ли уже MS Excel
oExcelApp = null;
try {
oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
} catch {
} finally {
if (object.Equals(oExcelApp,null) == true)
{
// если не выполняется - создать объект MS Excel
oExcelApp = new Microsoft.Office.Interop.Excel.Application();
}
else
{
;
}
}
// проверить: существует ли уже файл с отчетом
if (File.Exists(pathUserTemplate) == true)
{
try {
// удалить файл, т.к. на его место запишем новый
File.Delete(pathUserTemplate);
} catch {
// нет доступа к файлу, возможно, он уже открыт в MS Excel - найти открытую книгу с известным наименованием
oExcelWorkbook = oExcelApp.Workbooks.Cast <Microsoft.Office.Interop.Excel.Workbook>().FirstOrDefault(book => { return(book.FullName.Equals(pathUserTemplate) == true); });
if (object.Equals(oExcelWorkbook,null) == false)
{
// закрыть - если была найдена
oExcelWorkbook.Close(false);
}
else
{
;
}
// книгу "освободили" - можно удалить
try {
// но только в том случае, если книга была занята действительно MS Excel
File.Delete(pathUserTemplate);
} catch {
// иначе - выход (книгу блокирует другая программа)
return;
}
}
}
else
{
;
}
// копируем шаблон на ПК пользователя
File.Copy(pathRemoteTemplate,pathUserTemplate);
// объект для работы с ячейками книги MS Excel
excel = new GemBox.Spreadsheet.ExcelFile();
excel.LoadXls(pathUserTemplate);
//excel.Worksheets[0].Rows[0].Cells[0].Value = i_agregate;
// устанавливаем дату
excel.Worksheets[0].Rows[0].Cells[1].Value = m_panelManagement.CurDateTime.Date.ToShortDateString();
excel.Worksheets[0].Rows[0].Cells[2].Value = m_panelManagement.CurDateTime.Date.AddDays(1).ToShortDateString();
foreach (CONN_SETT_TYPE conn_sett_type in _types)
{
key_ctrl = conn_sett_type == CONN_SETT_TYPE.DATA_AISKUE ? KEY_CONTROLS.DGV_AIISKUE_VALUE
: conn_sett_type == CONN_SETT_TYPE.DATA_SOTIASSO ? KEY_CONTROLS.DGV_SOTIASSO_VALUE
: KEY_CONTROLS.UNKNOWN;
if (!(key_ctrl == KEY_CONTROLS.UNKNOWN))
{
dgv = findControl(key_ctrl.ToString()) as HDataGridView;
try {
tableExportDo = dgv.GetValues();
if (tableExportDo.Columns.Count > 0)
{
excel.Worksheets[0].InsertDataTable(tableExportDo,iStartRow - 1,iColumn,true);
iColumn += tableExportDo.Columns.Count;
}
else
{
// нет столбцов - нет строк - нет значений для вставки
;
}
} catch (Exception e) {
Logging.Logg().Exception(e,string.Format(@"PanelSOTIASSODay::panelManagement_OnEvtExportDo () - conn_sett_type={0}, заполнение значениями...",conn_sett_type),Logging.INDEX_MESSAGE.NOT_SET);
}
}
else
{
Logging.Logg().Error(string.Format(@"PanelSOTIASSODay::panelManagement_OnEvtExportDo () - не удалось найти элемент графического интерфейса для {0}",conn_sett_type)
,Logging.INDEX_MESSAGE.NOT_SET);
}
}
excel.SaveXls(pathUserTemplate);
//System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(@"excel.exe", string.Format("\"{0}\"", pathUserTemplate)));
oExcelApp.Workbooks.Open(pathUserTemplate);
oExcelApp.Visible = true;
} catch (Exception e) {
Logging.Logg().Exception(e,string.Format(@"PanelSOTIASSODay::panelManagement_OnEvtExportDo () - подготовка шаблона..."),Logging.INDEX_MESSAGE.NOT_SET);
}
}
private void ExportExcel(DataTable exportDataTable)
{
string strSampleFileName = string.Empty;
if (dpQueryType.Value.Equals("Opex", StringComparison.CurrentCultureIgnoreCase) ||
dpQueryType.Value.Equals("Capex", StringComparison.CurrentCultureIgnoreCase))
{
strSampleFileName = "PaymentRequestReportSample.xls";
}
else
{
strSampleFileName = "ClaimReportSample.xls";
}
string sSaveFileName = new Random().Next() + "_Report" + ".xls";
string sFullPath = Server.MapPath("/tmpfiles/PaymentRequestReport/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
for (int i = 0; i < exportDataTable.Rows.Count; i++)
{
if (dpQueryType.Value.Equals("Opex", StringComparison.CurrentCultureIgnoreCase) ||
dpQueryType.Value.Equals("Capex", StringComparison.CurrentCultureIgnoreCase))
{
worksheet1.Rows[i + 1].Cells[0].Value = exportDataTable.Rows[i]["Applicant"].ToString();
worksheet1.Rows[i + 1].Cells[1].Value = exportDataTable.Rows[i]["Department"].ToString();
worksheet1.Rows[i + 1].Cells[2].Value = exportDataTable.Rows[i]["SAPNumber"].ToString();
worksheet1.Rows[i + 1].Cells[3].Value = exportDataTable.Rows[i]["Title"].ToString();
worksheet1.Rows[i + 1].Cells[4].Value = exportDataTable.Rows[i]["SystemPONo"].ToString();
//worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["PONo"].ToString();
worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["Created"].ToString();
worksheet1.Rows[i + 1].Cells[6].Value = exportDataTable.Rows[i]["VendorNo"].ToString();
worksheet1.Rows[i + 1].Cells[7].Value = exportDataTable.Rows[i]["VendorName"].ToString();
worksheet1.Rows[i + 1].Cells[8].Value = exportDataTable.Rows[i]["PaymentDesc"].ToString();
worksheet1.Rows[i + 1].Cells[9].Value = exportDataTable.Rows[i]["TotalAmount"].ToString();
worksheet1.Rows[i + 1].Cells[10].Value = exportDataTable.Rows[i]["Currency"].ToString();
worksheet1.Rows[i + 1].Cells[11].Value = exportDataTable.Rows[i]["Status"].ToString();
worksheet1.Rows[i + 1].Cells[12].Value = exportDataTable.Rows[i]["PaidDate"].ToString();
worksheet1.Rows[i + 1].Cells[13].Value = exportDataTable.Rows[i]["IsAttachedInvoice"].ToString();
}
else
{
worksheet1.Rows[i + 1].Cells[0].Value = exportDataTable.Rows[i]["Applicant"].ToString();
worksheet1.Rows[i + 1].Cells[1].Value = exportDataTable.Rows[i]["Department"].ToString();
worksheet1.Rows[i + 1].Cells[2].Value = exportDataTable.Rows[i]["SAPNumber"].ToString();
worksheet1.Rows[i + 1].Cells[3].Value = exportDataTable.Rows[i]["Title"].ToString();
worksheet1.Rows[i + 1].Cells[4].Value = exportDataTable.Rows[i]["Created"].ToString();
worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["VendorNo"].ToString();
worksheet1.Rows[i + 1].Cells[6].Value = exportDataTable.Rows[i]["VendorName"].ToString();
worksheet1.Rows[i + 1].Cells[7].Value = exportDataTable.Rows[i]["PaymentDesc"].ToString();
worksheet1.Rows[i + 1].Cells[8].Value = exportDataTable.Rows[i]["TotalAmount"].ToString();
worksheet1.Rows[i + 1].Cells[9].Value = exportDataTable.Rows[i]["Currency"].ToString();
worksheet1.Rows[i + 1].Cells[10].Value = exportDataTable.Rows[i]["Status"].ToString();
worksheet1.Rows[i + 1].Cells[11].Value = exportDataTable.Rows[i]["PaidDate"].ToString();
}
}
CellStyle cs = new CellStyle();
cs.Font.Weight = ExcelFont.BoldWeight;
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
private void ExportExcel(DataTable ReportNotContainNameColumn, DataTable ReportContainNameColumn,
DataTable ReportNotContainNameColumnDataTable, DataTable ReportContainNameColumnDataTable)
{
string strSampleFileName = "ExpensesClaimReportSample.xls";
string sSaveFileName = this.ddlModule.SelectedValue+" Report" + ".xls";
string sFullPath = Server.MapPath("/tmpfiles/ExpensesClaimReport/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
worksheet1.Rows[2].Cells[0].Value = ddlFrom.SelectedItem.Text;
worksheet1.Rows[2].Cells[1].Value = ddlTo.SelectedItem.Text;
worksheet1.Rows[2].Cells[2].Value = ddlModule.SelectedItem.Text;
worksheet1.Rows[2].Cells[3].Value = ddlExpenseType.SelectedItem.Text;
worksheet1.Rows[5].Cells[0].Value = "Expense Type - " + ddlExpenseType.SelectedItem.Text;
int iNotCon = ReportNotContainNameColumn.Rows.Count;
if (iNotCon > 1)
{
worksheet1.Rows[7].InsertCopy(iNotCon-1 , worksheet1.Rows[7]);
}
for (int i = 0; i < iNotCon; i++)
{
worksheet1.Rows[7 + i].Cells[0].Value = ReportNotContainNameColumn.Rows[i][0];
worksheet1.Rows[7 + i].Cells[2].Value = ReportNotContainNameColumn.Rows[i][1];
worksheet1.Rows[7 + i].Cells[3].Value = ReportNotContainNameColumn.Rows[i][2];
}
worksheet1.Rows[iNotCon + 7].Cells[0].Value
= ReportNotContainNameColumnDataTable
.Rows[ReportNotContainNameColumnDataTable.Rows.Count - 1]["Department"].ToString();
worksheet1.Rows[iNotCon + 7].Cells[2].Value
= ReportNotContainNameColumnDataTable
.Rows[ReportNotContainNameColumnDataTable.Rows.Count - 1]["TotalCount"].ToString();
worksheet1.Rows[iNotCon + 7].Cells[3].Value
= ReportNotContainNameColumnDataTable
.Rows[ReportNotContainNameColumnDataTable.Rows.Count - 1]["TotalAmount"].ToString();
int iDep = ReportContainNameColumn.Rows.Count;
int iCurrentIndedx = iNotCon + 7 + 7 - 1;
if (iDep > 1)
{
worksheet1.Rows[iCurrentIndedx].InsertCopy(iDep - 1, worksheet1.Rows[iCurrentIndedx]);
}
CellStyle cs = new CellStyle();
cs.Font.Weight = ExcelFont.BoldWeight;
for (int i = 0; i < iDep; i++)
{
worksheet1.Rows[iCurrentIndedx + i].Cells[0].Value = ReportContainNameColumn.Rows[i][0];
worksheet1.Rows[iCurrentIndedx + i].Cells[1].Value = ReportContainNameColumn.Rows[i][1];
worksheet1.Rows[iCurrentIndedx + i].Cells[2].Value = ReportContainNameColumn.Rows[i][2];
worksheet1.Rows[iCurrentIndedx + i].Cells[3].Value = ReportContainNameColumn.Rows[i][3];
if (ReportContainNameColumn.Rows[i][0].ToString() != ""
&& ReportContainNameColumn.Rows[i][1].ToString() == "")
{
worksheet1.Rows[iCurrentIndedx + i].Style = cs;
}
}
worksheet1.Rows[iCurrentIndedx - 2].Cells[0].Value = "Expense Type - " + ddlExpenseType.SelectedItem.Text;
worksheet1.Rows[iCurrentIndedx + iDep].Cells[0].Value
= ReportContainNameColumnDataTable
.Rows[ReportContainNameColumnDataTable.Rows.Count - 1]["Department"].ToString();
worksheet1.Rows[iCurrentIndedx + iDep].Cells[2].Value
= ReportContainNameColumnDataTable
.Rows[ReportContainNameColumnDataTable.Rows.Count - 1]["TotalCount"].ToString();
worksheet1.Rows[iCurrentIndedx + iDep].Cells[3].Value
= ReportContainNameColumnDataTable
.Rows[ReportContainNameColumnDataTable.Rows.Count - 1]["TotalAmount"].ToString();
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
