public void PasteFromBuffer(string headerText, int rowCount, int columnCount, bool wrapText)
{
SetCurrentCell(1, 1, 1, columnCount);
CreateStandartHeader(headerText);
SetCurrentCell(2, 1, rowCount + 2, columnCount);
Worksheet.Paste(CurrentCells, false);
CurrentCells.Font.Name = "Times News Roman";
CurrentCells.Font.Size = 10;
CurrentCells.Borders.Value = 1;
CurrentCells.Borders.Weight = 2;
((Excel.Range)Worksheet.Cells[2, 1]).Value2 = "№ п/п";
((Excel.Range)Worksheet.Cells[2, 1]).Font.Bold = true;
for (int i = 0; i < rowCount; i++)
{
((Excel.Range)Worksheet.Cells[i + 3, 1]).Value2 = (i + 1);
}
SetCurrentCell(3, 1, rowCount + 2, columnCount);
CurrentCells.WrapText = wrapText;
CurrentCells.EntireColumn.AutoFit();
CurrentCells.EntireRow.AutoFit();
//Application.Visible = true;
Finally();
}
public void copyRange(Excel.Worksheet source, Excel.Worksheet dest, String startCell, String endCell, String startDest)
{
// get the length of the range - this might need to change later, figured it would be
// easier if this just adapted by itself.
int end = System.Convert.ToInt32(endCell.Substring(1)) + System.Convert.ToInt32(startDest.Substring(1));
String col = startDest.Substring(0, 1);
String row = end.ToString();
String destEnd = col + row;
// select the source worksheet
source.Select();
// get the cell range
Excel.Range reel = source.get_Range(startCell, endCell);
// copy our data
reel.Copy();
// select the destination worksheet
dest.Select();
// pick our destination cell
Excel.Range targetCell = dest.get_Range(startDest, destEnd);
try
{
targetCell.Select();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message.ToString(), "Error - target cells not empty", System.Windows.Forms.MessageBoxButtons.OK);
return;
}
// paste our data
dest.Paste(targetCell);
}
public void addImage(int row, int col, Bitmap image)
{
//Tamaño de celda
try
{
Excel.Range range = (Excel.Range)worksheet.Cells[row, col];
float width = image.Width / 7 > 255 ? 255 : image.Width / 7;
if (float.Parse(range.ColumnWidth.ToString()) < width)
{
range.ColumnWidth = width;
}
range = (Excel.Range)worksheet.Rows[row];
range.RowHeight = 38.25;
}
catch
{
return;
}
//Pega la imagen
try
{
Excel.Range range = (Excel.Range)worksheet.Cells[row, col];
Clipboard.SetDataObject(image);
worksheet.Paste(range, image);
}
catch
{
return;
}
}
public void copy(String copyRange, String copyTo)
{
workSheet.Range[copyRange].Select();
workSheet.Range[copyRange].Copy();
workSheet.Range[copyTo].Select();
workSheet.Paste();
}
private void BtnExcel_Click(object sender, RoutedEventArgs e)
{
Excel.Application app = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
var process = Process.GetProcessesByName("EXCEL");
SaveFileDialog openDlg = new SaveFileDialog();
openDlg.FileName = "Заявка №";
openDlg.Filter = "Excel (.xls)|*.xls |Excel (.xlsx)|*.xlsx |All files (*.*)|*.*";
openDlg.FilterIndex = 2;
openDlg.RestoreDirectory = true;
if (openDlg.ShowDialog() == true)
{
app = new Excel.Application();
app.Visible = true;
app.DisplayAlerts = false;
wb = app.Workbooks.Add();
ws = wb.ActiveSheet;
string path = openDlg.FileName;
DgridMyPage_Copy.SelectAllCells();
DgridMyPage_Copy.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, DgridMyPage_Copy);
ws.Paste();
ws.Range["A1", "L1"].Font.Bold = true;
int number1 = ws.UsedRange.Rows.Count;
Microsoft.Office.Interop.Excel.Range myRange = ws.Range["A1", "L" + number1];
myRange.Borders.LineStyle = XlLineStyle.xlContinuous;
myRange.WrapText = false;
ws.Columns.EntireColumn.AutoFit();
wb.SaveAs(path);
}
}
internal void CopyRange(string copingCell1, string copingCell2, string pastingCell)
{
xlSh.Range[copingCell1, copingCell2].Copy();
xlSh.Range[pastingCell, System.Type.Missing].Select();
xlSh.Paste();
xlApp.CutCopyMode = 0;
}
public static void exportToExcel(System.Windows.Controls.DataGrid dg, string table_name, string last_excel_row)
{
try {
Excel.Application xcel = runExcel();
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
SaveFileDialog openDlg = new SaveFileDialog();
openDlg.FileName = "Выгрузка из " + table_name;
openDlg.Filter = "Excel Таблицы (.xls)|*.xls |Excel Таблицы (.xlsx)|*.xlsx";
openDlg.FilterIndex = 2;
openDlg.RestoreDirectory = true;
if (openDlg.ShowDialog() == DialogResult.OK)
{
xcel.DisplayAlerts = false;
wb = xcel.Workbooks.Add();
ws = wb.ActiveSheet;
dg.SelectAllCells();
dg.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dg);
ws.Paste();
ws.Range["A1", "H1"].Font.Bold = true;
ws.Range["A1", "H1"].HorizontalAlignment = Excel.Constants.xlCenter;
int number1 = ws.UsedRange.Rows.Count;
Microsoft.Office.Interop.Excel.Range r = ws.Range["A1", last_excel_row + number1];
r.Borders.LineStyle = XlLineStyle.xlDouble;
r.WrapText = false;
r.Font.Size = 18;
ws.Columns.EntireColumn.AutoFit();
wb.SaveAs(openDlg.FileName);
xcel.Visible = true;
}
} catch (Exception) {}
}
private void sCopyRowTo(Microsoft.Office.Interop.Excel.Worksheet pInputSheet, Microsoft.Office.Interop.Excel.Worksheet pOutputSheet, int pRowIn, int pRowOut, int pErrorCell, DateTime pToDate, string pErrorMessage)
{
pErrorMessage = "";
pInputSheet.Activate();
pInputSheet.Rows[pRowIn].Copy();
pOutputSheet.Activate();
pOutputSheet.Rows[pRowOut].Select();
pOutputSheet.Paste();
if ((pErrorCell != 0))
{
pOutputSheet.Cells[pRowOut, pErrorCell].Interior.Color = Color.Red;
}
else
{
for (int i = 1; (i <= 40); i++)
{
string vDateString;
vDateString = (pToDate.Month.ToString() + ("/"
+ (pOutputSheet.Cells[pRowOut, i].Value + ("/" + pToDate.Year.ToString()))));
if (IsDate(vDateString))
{
pOutputSheet.Cells[(pRowOut + 1), i].Value = Convert.ToDateTime(vDateString).DayOfWeek.ToString();
}
}
}
if ((pErrorMessage != ""))
{
pOutputSheet.Cells[pRowOut, (pOutputSheet.UsedRange.Column
+ (pOutputSheet.UsedRange.Columns.Count - 1))].Value = pErrorMessage;
pOutputSheet.Cells[pRowOut, (pOutputSheet.UsedRange.Column
+ (pOutputSheet.UsedRange.Columns.Count - 1))].Interior.Color = Color.Red;
}
}
private void InsertImageIntoRange(ImageSource source, Excel.Worksheet currentSheet, Excel.Range range, int maxHeight = -1)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
var encoder = new System.Windows.Media.Imaging.BmpBitmapEncoder();
encoder.Frames.Add(System.Windows.Media.Imaging.BitmapFrame.Create(source as System.Windows.Media.Imaging.BitmapSource));
encoder.Save(ms);
ms.Flush();
System.Windows.Forms.Clipboard.SetDataObject(System.Drawing.Image.FromStream(ms), true);
currentSheet.Paste(range);
// Resize image to fit range
Excel.Shape shape = (Excel.Shape)currentSheet.Shapes.Item(currentSheet.Shapes.Count);
shape.LockAspectRatio = Office.MsoTriState.msoCTrue;
if (maxHeight == -1)
{
shape.Height = (int)range.Height;
}
else
{
shape.Height = (int)((source.Height / (double)maxHeight) * range.Height);
}
shape.Line.Visible = Office.MsoTriState.msoTrue;
ms.Close();
}
/// <summary>
/// シートに「SAMPLE」文字を挿入します。
/// </summary>
/// <param name="worksheet"></param>
private void AddSampleText(Excel.Worksheet worksheet, AddSample addSample)
{
// テンプレートから図形を取得
Excel.Worksheet sampleSheet = this.TemplateWorkBook.Sheets[addSample.SampleArtSheet];
System.Collections.IEnumerator index = sampleSheet.Shapes.GetEnumerator();
Excel.Shape shape;
while (index.MoveNext())
{
dynamic sampleShape = index.Current;
if (sampleShape.TextFrame2.TextRange.Count == 1)
{
// クリップボードの内容をクリア
Clipboard.Clear();
shape = sampleShape;
shape.Copy();
// ワークシートの所定の位置を選択しコピー
worksheet.Activate();
int blankCount = 0;
int i = addSample.OffsetT;
for (; i < addSample.EndOfRow; i++)
{
// 行が空でないかをチェック
bool blankFlag = true;
for (int j = 1; j < addSample.EndOfColumn; j++)
{
try
{
if (worksheet.Range[GetColumnLetter(j) + i].Value != null)
{
blankFlag = false;
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
if (blankFlag)
{
blankCount += 1;
if (blankCount > addSample.EndBlankLine)
{
break;
}
}
// サンプル画像を貼り付ける
if (i == addSample.OffsetT || (i - addSample.OffsetT) % addSample.Interval == 0)
{
worksheet.Cells[i, addSample.OffsetL].Select();
worksheet.Paste();
}
}
}
}
}
private void PasteDelimiter(Excel.Worksheet ws, ref int currentRow)
{
currentRow++;
ws.Range["A" + ROW_DELIMITER].Select();
xlApp.Selection.Copy();
ws.Range["A" + currentRow].Select();
ws.Paste();
currentRow++;
}
public void mergeMultiSheetToOneWorksheet()
{
string workPath = @"D:\1\VSTO_MergeExcelSheet\";
string mergeFileName = "combine.xlsx";
string PathHeader = $@"{workPath}result\";
if (Directory.Exists(PathHeader))
{
Directory.Delete(PathHeader, true);
}
if (!Directory.Exists(PathHeader))
{
Directory.CreateDirectory(PathHeader);
}
Excel.Application excelApp = new Excel.Application();
Excel.Workbook combineWorkBook = excelApp.Workbooks.Add();
Excel.Worksheet combineWorkSheet = combineWorkBook.Worksheets.Add();
WriteLine(workPath);
DirectoryInfo workDir = new DirectoryInfo(workPath);
FileInfo[] files = workDir.GetFiles("*.xls*");
foreach (FileInfo file in files)
{
if (FileAttributes.Hidden != (file.Attributes & FileAttributes.Hidden) && file.Name != mergeFileName)
{
WriteLine(file.FullName);
Excel.Workbook workbook = excelApp.Workbooks.Open(file.FullName);
Excel.Worksheet copyWorkSheet = workbook.Worksheets[1];
if (copyWorkSheet != null)
{
copyWorkSheet.UsedRange.Copy();
combineWorkSheet.Paste();
}
else
{
WriteLine($"Sheet1 Not Found: ${file.Name}");
}
workbook.Close(false);
}
}
combineWorkBook.SaveAs(Filename: $@"{workPath}{mergeFileName}");
combineWorkBook.Close();
}
/// <summary>
/// 在同一个 Sheet 范围内， 简单的复制粘贴处理.
/// </summary>
/// <param name="fromPlaceBegin"></param>
/// <param name="fromPlaceEnd"></param>
/// <param name="startPlace"></param>
public void SimpleCopyInOneSheet(
string fromPlaceBegin, string fromPlaceEnd,
string startPlace)
{
// 复制.
xlSheet.Range[fromPlaceBegin, fromPlaceEnd].Copy();
// 粘贴
xlSheet.Range[startPlace].Select();
xlSheet.Paste();
}
public static Excel.Range CopyPivotTable(Excel.PivotTable pt)
{
Excel.Application XlApp = pt.Application;
Excel.Worksheet sourceSheet = (Excel.Worksheet)pt.Parent;
sourceSheet.Select();
pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true);
Excel.Range sourceRange = (Excel.Range)XlApp.Selection;
sourceRange.Copy();
Excel.Worksheets sheets = (Excel.Worksheets)XlApp.Sheets;
Excel.Worksheet destSheet = (Excel.Worksheet)sheets.Add();
destSheet.Paste();
return(destSheet.Range["A1"]);
}
public static Excel.PivotTable CopyPivotTable(Excel.PivotTable pt)
{
Excel.Application excelApp = pt.Application;
var worksheet = (Excel.Worksheet)pt.Parent;
worksheet.Select();
pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true);
Excel.Range rng = (Excel.Range)excelApp.Selection;
rng.Copy();
Excel.Worksheet ws = (Excel.Worksheet)excelApp.Sheets.Add();
ws.Paste();
return(ws.Range["A1"].PivotTable);
}
private void Analize_WIN(String s)
{
FileStream open_File = new FileStream(s, FileMode.Open, FileAccess.Read);
StreamReader reader = new StreamReader(open_File, Encoding.Default);
ws.Activate();
int count = 0;
if (win_host != 0)
{
((Excel.Range)ws.Columns[6 + win_host]).Insert();
ws.get_Range(ws.Cells[87, 5 + win_host], ws.Cells[88, 5 + win_host]).Copy();
((Excel.Range)ws.Cells[87, 6 + win_host]).Select();
ws.Paste();
}
ws.Cells[3, 6 + win_host] = win_host + 1;
ws.Cells[4, 6 + win_host] = s.Split('_')[2];
System.Console.WriteLine(s);
while (!reader.EndOfStream)
{
String[] temp = reader.ReadLine().ToString().Split(' ');
if (temp[0].Length > 13 && temp[0].Substring(0, 6).Equals("OS-WIN"))
{
if (temp[temp.Length - 1].Substring(2, 4).Equals("PASS"))
{
ws.Cells[9 + count, 6 + win_host] = "양호";
}
else
{
ws.Cells[9 + count, 6 + win_host] = "예외합의";
}
count++;
}
if (count == 70)
{
break;
}
}
win_host++;
((Excel.Range)ws.Cells[9, 6]).Select();
}
} // cierre SignWordDocument
// ----------------------------------------------------------------------------------
private void SignExcelDocument(string documentPath, string signatureFile, bool openDoc)
{
EXCEL.Application excelApp = new EXCEL.ApplicationClass();
EXCEL.Workbook _workbook;
CultureInfo _prevCulture = Thread.CurrentThread.CurrentCulture;
// BUg de EXcel se soluciona con CUltureINfo VEr ENlace
// http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
try
{
excelApp.UserControl = true;
object _missing = System.Reflection.Missing.Value;
object _docPath = documentPath;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
_workbook = excelApp.Workbooks.Open(documentPath,
_missing, _missing, 5,
_missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing,
_missing, _missing, _missing);
EXCEL.Worksheet _sheet = (EXCEL.Worksheet)_workbook.Sheets[1];
_sheet.Cells[15, 1] = DateTime.Now.TimeOfDay.ToString();
EXCEL.Range _range = (EXCEL.Range)_sheet.Cells[17, 1];
Image _img = Image.FromFile(signatureFile);
// _range.set_Value(null, _img);
_range.set_Item(17, 1, _img);
_sheet.Paste(_range, signatureFile);
_workbook.Save();
excelApp.Visible = openDoc;
}
catch (Exception ex)
{
string _s = ex.ToString();
}
finally
{
excelApp.Quit();
excelApp = null;
_workbook = null;
Thread.CurrentThread.CurrentCulture = _prevCulture;
}
} // cierre SignExcelDocument
private void GenerateSummarySheet(Excel.Worksheet summarySheet)
{
summarySheet.Name = "收益图表汇总";
Excel.Worksheet curSheet = null;
Excel.ChartObject chartObj = null;
int rowNumOfPage = 43;
int sheetCount = _excelEdit._wb.Sheets.Count;
//设置summarySheet的打印范围
string startCell = "$A$1";
string endCell = "$P$" + ((sheetCount - 1) * rowNumOfPage).ToString();
summarySheet.PageSetup.PrintArea = startCell + ":" + endCell;
//拷贝所有SubjectSheet的合计收益Chart
for (int i = 2; i <= sheetCount; i++) //第一个Sheet为汇总Sheet，所以 i 初始值为2
{
curSheet = _excelEdit._wb.Sheets[i];
if (curSheet == null)
{
continue;
}
//合计收益Chart
chartObj = curSheet.ChartObjects(1);
if (chartObj == null)
{
continue;
}
int startRowIndex = rowNumOfPage * (i - 2) + 2;
chartObj.Chart.ChartArea.Copy();
Excel.Range range = summarySheet.Cells[startRowIndex, 2];
summarySheet.Paste(range, Type.Missing);
}
//设置summarySheet的ChartArea大小
Excel.ChartArea chartArea = null;
for (int i = 1; i < sheetCount; i++)
{
chartObj = summarySheet.ChartObjects(i);
chartArea = chartObj.Chart.ChartArea;
chartArea.Height = 510;
chartArea.Width = 780;
}
}
public static void DgvtoExcel(DataGridView dgv)
{
if (dgv.Rows.Count > 0)
{
string filename = "";
SaveFileDialog SV = new SaveFileDialog();
SV.Filter = "EXCEL FILES|*.xlsx;*.xls";
DialogResult result = SV.ShowDialog();
if (result == DialogResult.OK)
{
filename = SV.FileName;
bool multiselect = dgv.MultiSelect;
dgv.MultiSelect = true;
dgv.SelectAll();
dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
Clipboard.SetDataObject(dgv.GetClipboardContent());
var results = System.Convert.ToString(Clipboard.GetData(DataFormats.Text));
dgv.ClearSelection();
dgv.MultiSelect = multiselect;
Excel.Application XCELAPP = null;
Excel.Workbook XWORKBOOK = null;
Excel.Worksheet XSHEET = null;
object misValue = Missing.Value;
XCELAPP = new Excel.Application();
XWORKBOOK = XCELAPP.Workbooks.Add(misValue);
XCELAPP.DisplayAlerts = false;
XCELAPP.Visible = false;
XSHEET = XWORKBOOK.ActiveSheet;
XSHEET.Paste();
XWORKBOOK.SaveAs(filename, Excel.XlFileFormat.xlOpenXMLWorkbook);
XWORKBOOK.Close(false);
XCELAPP.Quit();
try
{
Marshal.ReleaseComObject(XSHEET);
Marshal.ReleaseComObject(XWORKBOOK);
Marshal.ReleaseComObject(XCELAPP);
}
catch (Exception ex)
{
notification.Error("Export Data Gagal", $"{ex.Message}");
}
notification.Ok("Export Berhasil", $"Data di Simpan di {SV.FileName}");
}
}
}
/// <summary>
/// Сохраняет указанную таблицу в файл excel;
/// </summary>
/// <param name="table"></param>
public void SaveDocExcel(DataGrid table)
{
Excel.Application application = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
var process = System.Diagnostics.Process.GetProcessesByName("EXCEL");
string date = DateTime.Now.ToString("yyyy-MM-dd");
SaveFileDialog openDialog = new SaveFileDialog();
openDialog.FileName = String.Format("Таблица продаж от {0}", date);
openDialog.Filter = "Excel (.xls)|*.xls |Excel (.xlsx)|*.xlsx |All files (*.*)|*.*";
openDialog.FilterIndex = 2;
openDialog.RestoreDirectory = true;
if (openDialog.ShowDialog() == true)
{
application = new Excel.Application();
application.DisplayAlerts = false;
workbook = application.Workbooks.Add();
worksheet = workbook.ActiveSheet;
table.SelectAllCells();
table.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
System.Windows.Input.ApplicationCommands.Copy.Execute(null, table);
worksheet.Paste();
worksheet.Range["A1", "I1"].Font.Bold = true;
int number1 = worksheet.UsedRange.Rows.Count;
Microsoft.Office.Interop.Excel.Range myRange = worksheet.Range["A1", "I" + number1];
myRange.Borders.LineStyle = XlLineStyle.xlContinuous;
myRange.WrapText = false;
worksheet.Columns.EntireColumn.AutoFit();
workbook.SaveAs(openDialog.FileName);
}
}
private void generateField(object sender, EventArgs e)
{
Excel.Application excelApp = null;
Excel.Workbooks books = null;
Excel.Workbook inputFile = null;
Excel.Workbook templateFile = null;
Excel.Worksheet inputSheets = null;
Excel.Worksheet templateSheet = null;
Excel.Worksheet exceedanceCurves = null;
Excel.Sheets templateSheets = null;
Excel.AddIn solver = null;
Excel.Range cellType1 = null;
Excel.Range cellType2 = null;
Excel.Range cellType3 = null;
notificationPanel.Show();
try {
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
books = excelApp.Workbooks;
inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\exceedance_model.xlsm"));
inputSheets = inputFile.Sheets["Input"];
solver = excelApp.AddIns["Solver Add-In"];
books.Open(solver.FullName);
cellType1 = inputSheets.UsedRange;
cellType2 = inputSheets.UsedRange;
cellType3 = inputSheets.UsedRange;
foreach (Control x in processUnitTable.Controls)
{
if (x is TextBox || x is ComboBox)
{
cellType1 = inputSheets.Cells[processUnitTable.GetRow(x) + 4, processUnitTable.GetColumn(x) + 1];
cellType1.Value = x.Text;
Marshal.FinalReleaseComObject(cellType1);
}
}
foreach (Control x in buildingDetailsTable.Controls)
{
if ((x is TextBox || x is ComboBox) && x.AccessibleName != "Ignore")
{
cellType2 = inputSheets.Cells[buildingDetailsTable.GetColumn(x), buildingDetailsTable.GetRow(x) + 8];
cellType2.Value = x.Text;
Marshal.FinalReleaseComObject(cellType2);
}
}
foreach (Control x in separationDistanceTable.Controls)
{
if ((x is TextBox || x is ComboBox) && x.AccessibleName != "Ignore")
{
cellType3 = inputSheets.Cells[separationDistanceTable.GetRow(x) + 4, separationDistanceTable.GetColumn(x) + 8];
cellType3.Value = x.Text;
Marshal.FinalReleaseComObject(cellType3);
}
}
excelApp.Run("'exceedance_model.xlsm'!exceedance");
templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Exceedance Results.xlsx"));
templateSheet = templateFile.Sheets["Exceedance_Data"];
templateSheets = templateFile.Sheets;
excelApp.DisplayAlerts = false;
inputSheets.Range["A1", "R24"].Copy(templateSheet.Range["A1", "R24"]);
exceedanceCurves = templateSheets.Add();
exceedanceCurves.Name = "Exceedance_Curves";
inputSheets.ChartObjects("Chart 2").Chart.CopyPicture();
exceedanceCurves.Paste();
templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Exceedance Results.xlsx"));
} finally {
// Clean up sheets
GC.Collect();
GC.WaitForPendingFinalizers();
if (cellType1 != null)
{
Marshal.FinalReleaseComObject(cellType1);
cellType1 = null;
}
if (cellType2 != null)
{
Marshal.FinalReleaseComObject(cellType2);
cellType2 = null;
}
if (cellType3 != null)
{
Marshal.FinalReleaseComObject(cellType3);
cellType3 = null;
}
Marshal.FinalReleaseComObject(templateSheets);
Marshal.FinalReleaseComObject(exceedanceCurves);
Marshal.FinalReleaseComObject(templateSheet);
Marshal.FinalReleaseComObject(inputSheets);
Marshal.FinalReleaseComObject(solver);
Marshal.FinalReleaseComObject(books);
templateSheets = null;
exceedanceCurves = null;
templateSheet = null;
inputSheets = null;
solver = null;
books = null;
inputFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
templateFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
Marshal.FinalReleaseComObject(inputFile);
Marshal.FinalReleaseComObject(templateFile);
inputFile = null;
templateFile = null;
excelApp.Application.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
notificationPanel.Controls.Find("btnClose", true)[0].Show();
};
}
public bool PasteText(string text, int sheetNum, int row, int col)
{
//xlApp = new Microsoft.Office.Interop.Excel.Application();
// check if file existed
if (!File.Exists(fileName))
{
Console.WriteLine("Auto create file");
object misValue = System.Reflection.Missing.Value;
xlApp.Workbooks.Add(misValue).SaveAs(fileName);
}
try
{
//Microsoft.Office.Interop.Excel.Worksheet preActiveSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNum);
if (String.IsNullOrEmpty(text))
{
text = "NULL content";
}
xlApp.Visible = true;
//~~> Add a new a workbook
//xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Open(fileName);
//~~> Set Sheet 1 as the sheet you want to work with
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNum);
xlWorkSheet.Cells.ClearContents();
xlWorkSheet.Select();
//~~> Set your range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[col, row];
CR.Select();
//System.Threading.Thread.CurrentThread.TrySetApartmentState(System.Threading.ApartmentState.STA);
if (InvokeRequired)
{
BeginInvoke((MethodInvoker) delegate
{
Clipboard.SetText(text);
});
}
else
{
Clipboard.SetText(text);
}
xlWorkSheet.Paste(CR, false);
// modify chart
int numsEnter = Regex.Matches(text, "\r\n").Count;
ModifyChart(numsEnter);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private void BuildCard(Func.RecordSet rsData)
{
try
{
//oSheet2.get_Range("A1:B6",Missing.Value).Copy(Type.Missing);
int rowSet1 = 2, colSet1 = 2, rowSet2 = 7, colSet2 = 3;
for (int x = 1; x < rsData.rows + 1; x++)
{
//copy and paste
oSheet2.get_Range("A1:B6", Missing.Value).Copy(oSheet.get_Range(oSheet.Cells[rowSet1, colSet1], oSheet.Cells[rowSet2, colSet2]));
//set value to card
BuildFooter(rowSet1 + 2, colSet1, colSet1, rsData.record(x - 1, 0)); // ten nv
BuildFooter(rowSet1 + 3, colSet2, colSet2, rsData.record(x - 1, 1)); // ma nv
BuildFooter(rowSet1 + 4, colSet2, colSet2, rsData.record(x - 1, 2)); // bo phan
BuildFooter(rowSet1 + 5, colSet2, colSet2, rsData.record(x - 1, 3)); // chuc vu
//set image
object bimap = ImageFunction.LoadImageObjectFromSQL(string.Format("Select PIC_DR from FILB01AB where EMP_ID=N'{0}'", rsData.record(x - 1, 1)));
if (bimap != null)
{
Bitmap bi = (Bitmap)bimap;
Excel.Range r = oSheet.get_Range(oSheet.Cells[rowSet1 + 1, colSet2], oSheet.Cells[rowSet1 + 1, colSet2]);
System.Windows.Forms.Clipboard.SetDataObject(bi, false);
oSheet.Paste(r, bi);
for (int y = 1; y < oSheet.Shapes.Count + 1; y++)
{
oSheet.Shapes.Item(y).LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoFalse;
oSheet.Shapes.Item(y).Width = 80;
oSheet.Shapes.Item(y).Height = 103;
}
}
rowSet1 = rowSet1;
colSet1 += 3;
rowSet2 = rowSet2;
colSet2 += 3;
if (x % 3 == 0) // x chia het cho 3
{
rowSet1 += 7; //xuong dong` 1 card
colSet1 = 2;
rowSet2 += 7;
colSet2 = 3;
}
if (x % 9 == 0) // x chia het cho 9 => break page
{
rowSet1 += 6;
rowSet2 += 6;
}
//change row height
oSheet.get_Range(oSheet.Cells[rowSet1, colSet1], oSheet.Cells[rowSet1, colSet1]).RowHeight = 43.5;
oSheet.get_Range(oSheet.Cells[rowSet1 + 1, colSet1], oSheet.Cells[rowSet1 + 1, colSet1]).RowHeight = 42;
oSheet.get_Range(oSheet.Cells[rowSet1 + 2, colSet1], oSheet.Cells[rowSet1 + 2, colSet1]).RowHeight = 61.5;
oSheet.get_Range(oSheet.Cells[rowSet1 + 3, colSet1], oSheet.Cells[rowSet1 + 3, colSet1]).RowHeight = 33;
oSheet.get_Range(oSheet.Cells[rowSet1 + 4, colSet1], oSheet.Cells[rowSet1 + 4, colSet1]).RowHeight = 33;
oSheet.get_Range(oSheet.Cells[rowSet1 + 5, colSet1], oSheet.Cells[rowSet1 + 5, colSet1]).RowHeight = 33;
}
oSheet2.Application.DisplayAlerts = false;
oSheet2.Delete();
//oSheet2.Application.DisplayAlerts = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static bool TableToExcel(Word.Range _ran, string RangeOnly = "table", Excel.Worksheet _sht = null)
{
Excel.Application _excel = null;
Excel.Workbook _book = null;
try
{
if (_sht == null)
{
_excel = new Excel.Application();
_excel.Visible = true;
_book = _excel.Workbooks.Add();
if (_book.Sheets.Count > 0)
{
_sht = _book.Sheets[_book.Sheets.Count];
}
else
{
_sht = _book.Sheets.Add(Type.Missing, _book.Sheets[_book.Sheets.Count], Type.Missing, Type.Missing);
}
}
/*foreach (Word.Cell _cell in _ran.Cells)
* {
*
* }*/
if (Properties.Settings.Default.XLSNewLine && RangeOnly != "range")
{
long row_count = _ran.Rows.Count;
long col_count = _ran.Columns.Count;
long i = 1; //row
long j = 1; //col
foreach (Word.Cell _cel in _ran.Cells)
{
String _val = String.Concat(_cel.Range.Text.Substring(0, _cel.Range.Text.Length - 1));
//_val.Replace(Environment.NewLine.ToCharArray()[0], '\v');
string _temp = (char)10 + "";
_val = _val.Replace(Environment.NewLine.ToCharArray()[0].ToString(), _temp);
_sht.Cells[i, j] = _val.Substring(0, _val.Length - 1);
if (j == col_count)
{
j = 1; i++;
}
else
{
j++;
}
}
}
else
{
_ran.Copy();
_sht.Paste();
}
_sht.get_Range("A1", "A1").Select();
}
catch
{
return(false);
}
finally
{
if (_book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_book);
}
if (_excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_excel);
}
GC.Collect();
}
return(true);
}
private void runModel(object sender, EventArgs e)
{
TextBox[] array = { data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11 };
notificationPanel.Show();
Excel.Application excelApp = null;
Excel.Workbooks books = null;
Excel.Workbook inputFile = null;
Excel.Workbook templateFile = null;
Excel.Worksheet inputSheets = null;
Excel.Worksheet templateSheet = null;
Excel.Worksheet liquidLinesTransect = null;
Excel.Range dataRange = null;
try {
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
books = excelApp.Workbooks;
inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\liquid_product_pipelines.xlsx"));
inputSheets = (Excel.Worksheet)inputFile.Sheets["Input Sheet"];
dataRange = inputSheets.Range["B1", "B11"];
int i = 0;
foreach (Excel.Range cell in dataRange)
{
cell.Value = array[i].Text;
i++;
}
templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Liquid Results.xlsx"));
templateSheet = (Excel.Worksheet)templateFile.Sheets["Liquid_Lines_Data"];
inputSheets.Range["B1", "B12"].Copy(templateSheet.Range["B1", "B12"]);
inputSheets.Range["B16", "J19"].Copy(templateSheet.Range["B16", "J19"]);
liquidLinesTransect = templateFile.Sheets.Add();
liquidLinesTransect.Name = "Liquid_Lines_Transect";
inputSheets.ChartObjects("Chart 1").Chart.CopyPicture();
liquidLinesTransect.Paste();
templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), data1.Text));
}
finally
{
// Clean up sheets
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(liquidLinesTransect);
Marshal.FinalReleaseComObject(templateSheet);
Marshal.FinalReleaseComObject(inputSheets);
Marshal.FinalReleaseComObject(books);
Marshal.FinalReleaseComObject(dataRange);
Marshal.FinalReleaseComObject(liquidLinesTransect);
liquidLinesTransect = null;
templateSheet = null;
inputSheets = null;
books = null;
dataRange = null;
inputFile.Close(false);
templateFile.Close(false);
Marshal.FinalReleaseComObject(inputFile);
Marshal.FinalReleaseComObject(templateFile);
inputFile = null;
templateFile = null;
excelApp.Application.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
notificationPanel.Controls.Find("btnClose", true)[0].Show();
};
}
public void saveExcel(DataGridView dataGridView1)
{
SaveFileDialog save = new SaveFileDialog();
save.Title = "Сохранить изображение как...";
save.OverwritePrompt = true;
save.CheckPathExists = true;
save.Filter = "Excel Files|.xlsx";
if (save.ShowDialog() == DialogResult.OK)
{
try
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.ActiveSheet;
for (int j = 1; j < dataGridView1.ColumnCount; j++)
{
if (j + 1 == dataGridView1.ColumnCount)
{
worksheet.Rows[1].Columns[j] = "Изображение";
}
else
{
worksheet.Rows[1].Columns[j] = dataGridView1.Columns[j - 1].HeaderText; // заполненине заголовков.
}
}
for (int i = 2; i < dataGridView1.RowCount + 1; i++)
{
for (int j = 1; j < dataGridView1.ColumnCount - 1; j++)
{
worksheet.Rows[i].Columns[j] = dataGridView1.Rows[i - 2].Cells[j - 1].Value; // заполнение остальных ячеек
if (j == 1 || j == 4)
{
worksheet.Cells[i, j].NumberFormat = "0";
}
if (j == 5)
{
worksheet.Cells[i, j].NumberFormat = "0,000";
}
if (j == 7)
{
worksheet.Cells[i, j].NumberFormat = "0,00";
}
}
}
worksheet.Columns.AutoFit();
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
Excel.Range rg = worksheet.get_Range("A" + 1, "A" + 1);
Dataproc obj1 = new Dataproc();
obj1.readimages(dataGridView1.Rows[i].Cells[0].Value.ToString());
if (obj1.images[0].Data != null)
{
object missing = System.Reflection.Missing.Value;
byte[] bytearray = obj1.images[0].Data;
ImageConverter converter = new ImageConverter();
Image img = (Image)converter.ConvertFrom(bytearray);
Bitmap objBitmap = new Bitmap(img, new Size(400, 400));
Clipboard.SetImage(objBitmap);
Excel.Range oRange = (Excel.Range)worksheet.Cells[i + 2, 8];
rg.Rows[i + 2].RowHeight = 300;
rg.Columns[8].ColumnWidth = 56.43F;
worksheet.Paste(oRange, missing);
}
rg.Columns[8].ColumnWidth = 56.43F;
}
excelApp.AlertBeforeOverwriting = false;
workbook.SaveAs(save.FileName);
excelApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка не удалось экспортировать БД в файл Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void GenerateReport(Excel.Workbook workbook, SheetItems items)
{
Excel.Worksheet worksheet = workbook.Worksheets.get_Item(1) as Excel.Worksheet;
var cell_dateTime = worksheet.Cells.Find("$DateTime");
if (cell_dateTime != null)
{
cell_dateTime.Value = items.DateTime;
}
var cell_name = worksheet.Cells.Find("$Name");
if (cell_name != null)
{
cell_name.Value = items.Name;
}
var cell_line = worksheet.Cells.Find("$Line");
if (cell_line != null)
{
cell_line.Value = items.Line;
}
var cell_machine = worksheet.Cells.Find("$Machine");
if (cell_machine != null)
{
cell_machine.Value = items.Machine;
}
var cell_point = worksheet.Cells.Find("$Ref");
if (cell_point != null)
{
cell_point.Value = items.Ref;
}
var cell_measure = worksheet.Cells.Find("$Value");
if (cell_measure != null)
{
cell_measure.Value = items.Value;
}
var cell_Analysis = worksheet.Cells.Find("$AnalysisType");
if (cell_Analysis != null)
{
cell_Analysis.Value = items.AnalysisType;
}
var cell_PlotImg = worksheet.Cells.Find("$PlotImage");
if (cell_PlotImg != null)
{
Clipboard.SetDataObject(items.Img, true);
worksheet.Paste(cell_PlotImg, false);
cell_PlotImg.Value = "";
}
var cell_bfTime = worksheet.Cells.Find("$BeforeTime");
if (cell_bfTime != null)
{
Clipboard.SetDataObject(items.Img_BeforTime, true);
worksheet.Paste(cell_bfTime, false);
cell_bfTime.Value = "";
}
var cell_bfFFT = worksheet.Cells.Find("$BeforeFFT");
if (cell_bfFFT != null)
{
Clipboard.SetDataObject(items.Img_BeforFFT, true);
worksheet.Paste(cell_bfFFT, false);
cell_bfFFT.Value = "";
}
var cell_afTime = worksheet.Cells.Find("$AfterTime");
if (cell_afTime != null)
{
Clipboard.SetDataObject(items.Img_AfterTime, true);
worksheet.Paste(cell_afTime, false);
cell_afTime.Value = "";
}
var cell_afFFT = worksheet.Cells.Find("$AfterFFT");
if (cell_afFFT != null)
{
Clipboard.SetDataObject(items.Img_AfterFFT, true);
worksheet.Paste(cell_afFFT, false);
cell_afFFT.Value = "";
}
var cell_DailyData = worksheet.Cells.Find("$DailyData");
if (cell_DailyData != null)
{
var range1 = worksheet.get_Range("A11", "M11");
int num = 10;
foreach (var item in items.DailyDatas)
{
object[,] values =
{ {
item.Machine,
item.Point,
item.Function,
item.Unit,
item.Caution,
item.Failure,
item.Repair,
item.Stop,
item.MIN.ToString("#.00"),
item.MAX.ToString("#.00"),
item.AVG.ToString("#.00"),
item.Status.Stat,
"",
} };
if (num < items.DailyDatas.Length + 8)
{
range1.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
}
var range2 = worksheet.get_Range("A" + num, "M" + num);
range2.Value2 = values;
Excel.Range range = worksheet.Cells[num, "L"] as Excel.Range;
range.Interior.Color = item.Status.StatColor;
range.Font.Color = item.Status.StatColor_Font;
num++;
ReleaseObject(ref range2);
}
ReleaseObject(ref range1);
}
ReleaseObject(ref worksheet);
}
private void runModel(object sender, EventArgs e)
{
TextBox[] array = { data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11 };
notificationPanel.Show();
Excel.Application excelApp = null;
Excel.Workbooks books = null;
Excel.Workbook inputFile = null;
Excel.Workbook templateFile = null;
Excel.Worksheet inputSheets = null;
Excel.Worksheet fireBallSheet = null;
Excel.Worksheet templateSheet = null;
Excel.Worksheet gasLineResults = null;
Excel.Range dataRange = null;
Excel.Range fireBallRange = null;
object value = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
books = excelApp.Workbooks;
inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\gaslines_model_public.xlsx"));
inputSheets = (Excel.Worksheet)inputFile.Sheets["Gas pipeline input sheet"];
fireBallSheet = (Excel.Worksheet)inputFile.Sheets["Fire Ball"];
dataRange = inputSheets.Range["B1", "B11"];
int i = 0;
foreach (Excel.Range cell in dataRange)
{
cell.Value = array[i].Text;
i++;
Marshal.FinalReleaseComObject(cell);
}
fireBallRange = fireBallSheet.Range["E4", "E104"];
foreach (Excel.Range fireCell in fireBallRange)
{
value = fireCell.Value;
InitPage.GasLineValues.publicGasLine.Add(value.ToString());
Marshal.FinalReleaseComObject(fireCell);
}
templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Gas Line Results Public.xlsx"));
templateSheet = (Excel.Worksheet)templateFile.Sheets["Gas_Lines_Data"];
inputSheets.Range["B1", "B11"].Copy(templateSheet.Range["B1", "B11"]);
inputSheets.Range["A1", "C17"].Copy(templateSheet.Range["A1", "C17"]);
inputSheets.Range["A23", "F25"].Copy(templateSheet.Range["A23", "F25"]);
gasLineResults = templateFile.Sheets.Add();
gasLineResults.Name = "Gas_Lines_Transect";
inputSheets.ChartObjects("Chart 1").Chart.CopyPicture();
gasLineResults.Paste();
templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), data1.Text));
}
finally
{
// Clean up sheets
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(gasLineResults);
Marshal.FinalReleaseComObject(templateSheet);
Marshal.FinalReleaseComObject(fireBallSheet);
Marshal.FinalReleaseComObject(inputSheets);
Marshal.FinalReleaseComObject(books);
Marshal.FinalReleaseComObject(dataRange);
Marshal.FinalReleaseComObject(fireBallRange);
gasLineResults = null;
templateSheet = null;
fireBallSheet = null;
inputSheets = null;
books = null;
dataRange = null;
fireBallRange = null;
value = null;
inputFile.Close(false);
templateFile.Close(false);
Marshal.FinalReleaseComObject(inputFile);
Marshal.FinalReleaseComObject(templateFile);
inputFile = null;
templateFile = null;
excelApp.Application.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
notificationPanel.Controls.Find("btnClose", true)[0].Show();
};
}
private void runModel(object sender, EventArgs e)
{
TextBox[] array = { data1, data2, data3, data4, data5, data6, data7, data8, data9 };
TextBox[] fireHole = { hole50, hole5 };
notificationPanel.Show();
Excel.Application excelApp = null;
Excel.Workbooks books = null;
Excel.Workbook inputFile = null;
Excel.Workbook templateFile = null;
Excel.Worksheet inputSheets = null;
Excel.Worksheet fireBallSheet = null;
Excel.Worksheet templateSheet = null;
Excel.Worksheet wellLinesTransect = null;
Excel.Sheets templateSheets = null;
Excel.Range dataRange = null;
Excel.Range holeRange = null;
Excel.Range fireBallRange = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
books = excelApp.Workbooks;
inputFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\well_lines_public.xlsx"));
inputSheets = inputFile.Sheets["Well lines input sheet"];
fireBallSheet = inputFile.Sheets["Fire Ball"];
dataRange = inputSheets.Range["B1", "B9"];
int i = 0;
foreach (Excel.Range cell in dataRange)
{
cell.Value = array[i].Text;
i++;
Marshal.FinalReleaseComObject(cell);
}
holeRange = inputSheets.Range["B17", "B18"];
int iter = 0;
foreach (Excel.Range cell in holeRange)
{
cell.Value = fireHole[iter].Text;
iter++;
Marshal.FinalReleaseComObject(cell);
}
int index = 0;
fireBallRange = fireBallSheet.Range["E4", "E104"];
foreach (Excel.Range cell in fireBallRange)
{
double step5 = double.Parse(step5factor.Text);
double value = double.Parse(InitPage.GasLineValues.publicGasLine[index]) * double.Parse(step5factor.Text);
cell.Value = value;
index++;
Marshal.FinalReleaseComObject(cell);
}
templateFile = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Well Results Public.xlsx"));
templateSheet = templateFile.Sheets["Well_Lines_Data"];
templateSheets = templateFile.Sheets;
inputSheets.Range["B1", "B11"].Copy(templateSheet.Range["B1", "B11"]);
inputSheets.Range["B17", "B18"].Copy(templateSheet.Range["B16", "B17"]);
wellLinesTransect = templateSheets.Add();
wellLinesTransect.Name = "Well_Lines_Transect";
inputSheets.ChartObjects("Chart 3").Chart.CopyPicture();
wellLinesTransect.Paste();
templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), data1.Text));
}
finally
{
// Clean up sheets
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(wellLinesTransect);
Marshal.FinalReleaseComObject(templateSheet);
Marshal.FinalReleaseComObject(fireBallSheet);
Marshal.FinalReleaseComObject(inputSheets);
Marshal.FinalReleaseComObject(books);
Marshal.FinalReleaseComObject(templateSheets);
Marshal.FinalReleaseComObject(dataRange);
Marshal.FinalReleaseComObject(holeRange);
Marshal.FinalReleaseComObject(fireBallRange);
wellLinesTransect = null;
templateSheet = null;
fireBallSheet = null;
inputSheets = null;
books = null;
templateSheets = null;
dataRange = null;
holeRange = null;
fireBallRange = null;
inputFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
templateFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
Marshal.FinalReleaseComObject(inputFile);
Marshal.FinalReleaseComObject(templateFile);
inputFile = null;
templateFile = null;
excelApp.Application.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
notificationPanel.Controls.Find("btnClose", true)[0].Show();
};
}
public async Task <List <string> > UpdateHrustyashki()
{
Rank.SetMaxRank(Rank.Tachyon);
List <string> filePaths = new List <string>();
const int minTotalMileage = 3000;
FileStream fs = new FileStream(xlsPath, FileMode.Open, FileAccess.Write);
fs.Close();
if (successService == null)
{
return(null);
}
await successService.UpdateSuccesses();
List <User> users = successService.Successes.Where(x => x.TotalMileage >= minTotalMileage).OrderByDescending(x => x.TotalMileage).Select(x => new User(x.Id, x.Nick)).ToList();
var threeLastVocs = HrustConstants.ModeIds.Skip(HrustConstants.ModeIds.Length - 3).ToList();
var threeLastVocsRacerRequirements = Enumerable.Range(22, 3).Select(x => HrustRequirements.GetRequirement(x, HrustRank.Racer)).ToList();
ModeStatSettings modeStatInput = new ModeStatSettings()
{
ModeIds = threeLastVocs, NeedPeriodStat = true
};
var tops = await topService.GetBulkTop(modeStatInput, Period.Week);
for (int i = 1; i < tops.Count; i++)
{
var topPlayers = tops[i].Where(x => x.TopResult >= threeLastVocsRacerRequirements[i]);
foreach (var player in topPlayers)
{
if (!users.Any(x => x.Id == player.UserId))
{
users.Add(new User(player.UserId, ""));
}
}
}
if (File.Exists(addPlayersPath))
{
var lines = File.ReadAllLines(addPlayersPath, Encoding.UTF8);
var players = lines.Select(x => int.TryParse(x, out int result) ? result : 0).Where(x => x > 0);
foreach (var playerId in players)
{
if (!users.Any(x => x.Id == playerId))
{
users.Add(new User(playerId, ""));
}
}
}
List <HrustPlayer> results = await GetHrustResults(users);
string hrTable = BuildTable(results);
File.WriteAllText(txtPath, hrTable, Encoding.UTF8);
// если что-то пошло не так, можно считать таблицу с txt файла
//string HrTable = File.ReadAllText(txtPath, Encoding.UTF8);
hrTable = hrTable.Replace('\t', ';').Replace(" / ", " / ");
Excel.Application excelapp = new Excel.Application();
excelapp.DisplayAlerts = false;
string xlsBigPath = AppDomain.CurrentDomain.BaseDirectory + "\\" + xlsPath;
Excel.Workbook excelworkbook = excelapp.Workbooks.Open(xlsBigPath, 0, false, 5,
"", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
excelapp.Calculation = Excel.XlCalculation.xlCalculationManual;
Excel.Worksheet wsh = (Excel.Worksheet)excelworkbook.Worksheets.get_Item("Статистика всех игроков");
wsh.Activate();
Excel.Range range1 = wsh.Range[wsh.Cells[5, 3], wsh.Cells[5, 3]];
range1.Select();
ClipboardService.SetText(hrTable);
wsh.Paste();
Excel.Range range2 = wsh.Range[wsh.Cells[5, 3], wsh.Cells[5 + results.Count - 1, 3]];
range2.TextToColumns(
range2,
Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone,
false,
false,
true
);
excelworkbook.Save();
// Копирование картинок из Excel
int firstPlaceRow = 4;
int imageHeight = 44; // lines
int passed_24 = results.Count(x => x.TotalExercisesRank.Index >= 5);
int passed_21_23 = results.Count(x => x.ExercisesStat.Count(y => y.Rank.Index >= 5) >= 21);
int imageCounter = 0;
for (int stRow = firstPlaceRow - 1, endRow = 0;
endRow != firstPlaceRow + passed_21_23;
stRow += imageHeight)
{
if (stRow < firstPlaceRow + passed_24)
{
endRow = stRow + imageHeight - 1;
if (endRow > passed_24 + firstPlaceRow)
{
endRow = firstPlaceRow + passed_24;
}
}
else
{
stRow = firstPlaceRow + passed_24 + 1;
endRow = firstPlaceRow + passed_21_23;
}
Excel.Range range3 = wsh.Range[wsh.Cells[stRow, 4], wsh.Cells[endRow, 38]];
range3.Copy();
Image image = ClipboardService.GetImage();
// Обрезка изображения
Bitmap bmp = new Bitmap(image);
Rectangle cropArea = new Rectangle(1, 1, bmp.Width - 1, bmp.Height - 1);
image = bmp.Clone(cropArea, bmp.PixelFormat);
image.Save(++imageCounter + ".png", ImageFormat.Png);
filePaths.Add(imageCounter + ".png");
}
excelworkbook.Close();
excelapp.Quit();
Rank.SetMaxRank(Rank.ExtraCyber);
return(filePaths);
}
