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); }