/// <summary>
/// Создает общий шаблон для таблицы
/// </summary>
/// <param name="Parent">Родительская форма</param>
/// <param name="Table">Исходная таблица</param>
public static void CreateTemplate(BankLab Parent, DataGridView Table)
{
Excel.Application ExcelApp;
ExcelApp = new Excel.Application();
ResFile Maket = new ResFile(bl_res_doc.Resource1.Maket, AppDomain.CurrentDomain.BaseDirectory + "~tmp.xlsx");
Maket.CreateFile();
Parent.CurrentProgressBar.SetProgressBarValue(35);
ExcelApp.Workbooks.Open(Maket.FileName);
Parent.CurrentProgressBar.SetProgressBarValue(50);
Table.Columns.Add("Year", ExcelApp.Cells[1, 1].Value);
Table.ColumnCount = 19;
Parent.CurrentProgressBar.SetProgressBarValue(60);
for (int i = 1; i <= 18; i++) {
Table.Columns[i].Name = "X" + i.ToString();
Table.Columns[i].HeaderText = ExcelApp.Cells[1, i + 1].Value;
Table.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
}
Parent.CurrentProgressBar.SetProgressBarValue(90);
SetTablePropertyToCreateTamplate(Table);
Parent.CurrentSideBar.AddTables();
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
Maket.FreeFile();
Parent.CurrentMenu.ChangeMenuItemsEnable(true);
}
private void MainForm_Load(object sender, EventArgs e)
{
this.DoubleBuffered = true;
/* set start prefix */
prefix = "profit_";
ResFile DbFile = new ResFile(bl_res_doc.Resource1.optimization_db,AppDomain.CurrentDomain.BaseDirectory+
"\\optimization_db.accdb");
DbFile.CreateFile();
DBFunc = new DBBankLabFunctions(
AppDomain.CurrentDomain.BaseDirectory+
"\\optimization_db.accdb", Parent);
}
public void ReadDataToCache()
{
ResFile tmp = new ResFile(
bl_res_doc.Resource1.prognoz,
AppDomain.CurrentDomain.BaseDirectory+"for.xlsx");
tmp.CreateFile();
Excel.Application ExApp = new Excel.Application();
ExApp.Workbooks.Open(tmp.FileName);
int RowCount = ExApp.Sheets[1].UsedRange.Rows.Count;
int ColCount = ExApp.Sheets[1].UsedRange.Columns.Count;
Cache = new float[RowCount,ColCount];
for (int i=0;i<RowCount;i++){
for (int j = 0; j < ColCount; j++) {
Cache[i,j] = Convert.ToSingle(ExApp.Cells[i+1,j+1].Value);
}
}
ExApp.Workbooks.Close();
ExApp.Quit();
tmp.FreeFile();
}
public static int SaveCoefficientTableToExcelFile(DataGridView Table, String Path)
{
if ((Table != null)&&(Table.RowCount > 0))
{
if (File.Exists(Path)) {
ResFile FileCommander = new ResFile(null, Path);
FileCommander.FreeFile();
FileCommander = new ResFile(null, null);
}
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Workbooks.Add();
for (int i = 0; i < Table.Rows[0].Cells.Count; i++) {
ExcelApp.Cells[1, i + 1] = Table.Columns[i].HeaderText;
ExcelApp.Workbooks[1].Worksheets[1].Columns[i + 1].ColumnWidth = Table.Columns[i].Width / 6;
}
ExcelApp.Workbooks[1].Worksheets[1].Rows[1].RowHeight = Table.ColumnHeadersHeight;
for (int i = 0; i < Table.RowCount; i++) {
for (int j = 0; j < Table.Rows[i].Cells.Count; j++) {
ExcelApp.Cells[i + 2, j + 1] = Table.Rows[i].Cells[j].Value;
}
}
ExcelApp.Columns.WrapText = true;
ExcelApp.Workbooks[1].Close(true, Path);
ExcelApp.Quit();
return 1;
}
return 0;
}
public void AddTestSheets(ref Excel.Application ExApp)
{
ResFile testFile = new ResFile(
(byte[])bl_res_doc.Resource1.ResourceManager.GetObject(prefix+"test"),
AppDomain.CurrentDomain.BaseDirectory+"test.xlsx");
testFile.CreateFile();
ExApp.Workbooks.Open(testFile.FileName);
for (int i = 0; i < 3; i++) {
ExApp.Workbooks[2].Worksheets[i+1].Copy(ExApp.Workbooks[1].Worksheets[i+2]);
}
ExApp.Workbooks[2].Close(false);
testFile.FreeFile();
}
public Excel.Application GetApplicationWithTemplate(ref bool newFile,string fileName)
{
Excel.Application ExApp = new Excel.Application();
ResFile template = new ResFile(
bl_res_doc.Resource1.templates,
AppDomain.CurrentDomain.BaseDirectory+"tmp.xlsx");
if ((File.Exists(fileName)) && (fileName.IndexOf("test") >= 0)) {
ResFile tmpFile = new ResFile(null,fileName);
tmpFile.FreeFile();
}
if (File.Exists(fileName)) newFile = false;
if (newFile) {
ExApp.Workbooks.Add();
}
else {
ExApp.Workbooks.Open(fileName);
}
template.CreateFile();
ExApp.Workbooks.Open(template.FileName);
ExApp.Workbooks[2].Worksheets[prefix].Copy(ExApp.Workbooks[1].Worksheets[1]);
ExApp.Workbooks[2].Close();
template.FreeFile();
return ExApp;
}