/// <summary>
/// HTML转换为Excel
/// </summary>
/// <param name="IsMergeCells"></param>
/// <param name="tableStr"></param>
public void HtmlToExcel(bool IsMergeCells, string tableStr)
{
string a1 = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title></title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\n<style>\n</style>\n</head>\n<body>\n";
string a2 = "\n</body>\n</html>";
tableStr = a1 + tableStr + a2;
if (IsMergeCells)
{
//合并单元格
//m_objRange = m_objSheet.get_Range("A1", "K4");
//m_objRange.Merge(System.Reflection.Missing.Value);
m_objRange = m_objSheet.get_Range("A1", "A1");
//m_objRange.
}
m_objRange.Select();
m_objRange.Cells[1, 1] = tableStr;
m_objRange.Activate();
m_objRange.Copy(Missing.Value);
//m_objSheet.get_Range("B2", "B2")
//.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract
//, false, false);
m_objRange.EntireColumn.AutoFit();
}
private void btnApplica_Click(object sender, EventArgs e)
{
Sheet.Protected = false;
foreach (Excel.Range rng in _origRng.Cells)
{
if (rng.Value != null)
{
double val = (double)rng.Value;
if (_percentage != null)
{
rng.Value = val + val * (_percentage.Value / 100);
}
else if (_increment != null)
{
rng.Value += _increment.Value;
}
}
}
Handler.StoreEdit(_origRng, tableName: MODIFICA);
_origRng.Select();
btnRipristina.Enabled = true;
Sheet.Protected = true;
}
public void consolidarArchivos(string ruta1, string ruta2, string nombre1, string nombre2)
{
Excel.Application miExcel = new Excel.Application();
miExcel.Visible = true;
miExcel.DisplayAlerts = false;
Excel.Workbook libro = miExcel.Workbooks.Open(ruta1);
((Excel.Worksheet)miExcel.Sheets[1]).Select();
Excel.Worksheet SheetExcel = (Excel.Worksheet)libro.ActiveSheet;
SheetExcel.Columns.EntireColumn.AutoFit();
Excel.Workbook librof = miExcel.Workbooks.Add();
((Excel.Worksheet)miExcel.Sheets[1]).Select();
Excel.Worksheet PestañaF = (Excel.Worksheet)librof.ActiveSheet;
Excel.Range rango = PestañaF.get_Range("A:Z");
rango.Select();
rango.NumberFormat = "@";
copiarArchivos(SheetExcel, PestañaF);
PestañaF.Name = nombre1;
PestañaF.Columns.EntireColumn.AutoFit();
rango = PestañaF.get_Range("1:1");
rango.Select();
rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
rango.Font.Bold = true;
libro.Close();
libro = miExcel.Workbooks.Open(ruta2);
((Excel.Worksheet)miExcel.Sheets[1]).Select();
SheetExcel = (Excel.Worksheet)libro.ActiveSheet;
SheetExcel.Columns.EntireColumn.AutoFit();
librof.Worksheets.Add();
librof.Activate();
((Excel.Worksheet)miExcel.Sheets[1]).Select();
PestañaF = (Excel.Worksheet)librof.ActiveSheet;
rango = PestañaF.get_Range("A:Z");
rango.Select();
rango.NumberFormat = "@";
copiarArchivos(SheetExcel, PestañaF);
PestañaF.Name = nombre2;
PestañaF.Columns.EntireColumn.AutoFit();
rango = PestañaF.get_Range("1:1");
rango.Select();
rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
rango.Font.Bold = true;
libro.Close();
if (!Directory.Exists(rutaEscritorio + @"\Archivos Generados\"))
{
Directory.CreateDirectory(rutaEscritorio + @"\Archivos Generados\");
}
string nombre = "Chedraui Estado de Cuenta " + nombreAleatorio() + ".xlsx";
librof.SaveAs(rutaEscritorio + @"\Archivos Generados\" + nombre);
File.Delete(ruta1);
File.Delete(ruta2);
}
public void AddMedianSummary(Excel.Worksheet TableSheet, string TableName, ListingStatus Status)
{
int lastRow = 0;
int firstRow = 0;
int avgRow = 0;
int medianRow = 0;
int lastCol = 0;
double avgRowHeight = 38;
double medianRowHeight = avgRowHeight;
Excel.PivotTable pvt = null;
TableSheet.Select();
pvt = TableSheet.PivotTables(TableName);
firstRow = pvt.TableRange1.Row + 2;
lastRow = pvt.TableRange1.Row + pvt.TableRange1.Rows.Count - 2;
avgRow = lastRow + 1;
medianRow = avgRow + 1;
int rw = 0;
foreach (Excel.PivotField pvf in pvt.RowFields)
{
rw++;
}
Excel.Range Cell = TableSheet.Range["A" + avgRow];
Cell.Value2 = "Average Values";
Cell = TableSheet.Range["A" + medianRow];
Cell.Value2 = "Median Values";
TableSheet.Cells[medianRow, rw + 1].Value = Library.GetCount(ListingSheet, "B", Status, "", "");
TableSheet.Cells[medianRow, rw + 2].Value = Library.GetMedianValue(ListingSheet, "G", Status, "", "");
TableSheet.Cells[medianRow, rw + 3].Value = Library.GetMedianValue(ListingSheet, "I", Status, "", "");
TableSheet.Cells[medianRow, rw + 4].Value = Library.GetMedianValue(ListingSheet, "L", Status, "", "");
TableSheet.Cells[medianRow, rw + 5].Value = Library.GetMedianValue(ListingSheet, "M", Status, "", "");
TableSheet.Cells[medianRow, rw + 6].Value = Library.GetMedianValue(ListingSheet, "O", Status, "", "");
TableSheet.Cells[medianRow, rw + 7].Value = Library.GetMedianValue(ListingSheet, "P", Status, "", "");
TableSheet.Cells[medianRow, rw + 8].Value = Library.GetMedianValue(ListingSheet, "R", Status, "", "");
TableSheet.Cells[medianRow, rw + 9].Value = Library.GetMedianValue(ListingSheet, "S", Status, "", "");
TableSheet.Select();
lastCol = pvt.TableRange1.Columns.Count;
Excel.Range rng = TableSheet.Range[TableSheet.Cells[medianRow, 1], TableSheet.Cells[medianRow, lastCol]];
rng.RowHeight = medianRowHeight;
Excel.Range rngSource = TableSheet.Range[TableSheet.Cells[avgRow, 1], TableSheet.Cells[avgRow, lastCol]];
rngSource.Select();
rngSource.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rngSource.Copy();
rng.Select();
rng.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);
rng.Font.Bold = true;
rngSource.Select();
Globals.ThisAddIn.Application.CutCopyMode = 0;
}
public void Format(Excel.Worksheet PivotSheet, string TableName, ListingStatus Status, string City)
{
Excel.PivotTable pvt = PivotSheet.PivotTables(TableName);
int FirstRow = 0;
int LastRow = 0;
int LastCol = 0;
int TitleRow = 0;
FirstRow = pvt.TableRange1.Row + 1;
LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2;
LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column;
TitleRow = pvt.TableRange2.Row - 1;
//Todo: Format Title
//Hide Values Row
Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)];
rng0.EntireRow.Hidden = true;
Excel.Range c1 = PivotSheet.Cells[FirstRow, 1];
Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol];
Excel.Range rng = PivotSheet.Range[c1, c2];
rng.Select();
//rng.Style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//rng.Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//rng.Style.Font.Name = "Roboto";
//rng.Style.Font.Size = 11;
//rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
rng.RowHeight = 38;
rng.WrapText = true;
rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
rng = PivotSheet.Range["A" + LastRow];
rng.Select();
rng.RowHeight = 36;
// Create the table style
//ListingBook.TableStyles.Add("Attached Report Style");
Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"];
//ptStyle.ShowAsAvailablePivotTableStyle = true;
// Set Table Style
pvt.TableStyle2 = ptStyle;
FormatColumnWidth();
HideComplexSubTotal(PivotSheet, TableName);
AddSubGroupBottomBorder(TableName);
FormatMaxCells();
FormatMinCells();
AddSectionTitle(PivotSheet, TableName, City + " " + Status + " Records:");
}
private void HideComplexSubTotal(Excel.Worksheet Sheet, string TableName)
{
Excel.Range Cell = null;
foreach (Excel.Range row in Sheet.PivotTables(TableName).RowRange.Rows)
{
if (Sheet.Range["A" + row.Row].Value?.IndexOf("Total") > 0 && Sheet.Range["A" + row.Row].Value?.IndexOf("Grand Total") < 0)
{
Cell = Sheet.Range["A" + row.Row];
Cell.Select();
Cell.Value = "SubTotal";
Cell.RowHeight *= 1.3;
Cell.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
if (Sheet.Range["B" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
if (Sheet.Range["C" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
}
}
private void ExcelButton_Click(object sender, EventArgs e)
{
application = new Excel.Application();
int i = 0;
workbook = application.Workbooks.Add();
application.Visible = true;
foreach (DataGridView d in listdgv)
{
d.MultiSelect = true;
d.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
d.SelectAll();
DataObject dataObject = d.GetClipboardContent();
if (dataObject != null)
{
Clipboard.SetDataObject(dataObject);
Excel.Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Name = listdgv.Count - i++ + " запрос";
Excel.Range range = (Excel.Range)worksheet.Cells[1, 1];
range.Select();
worksheet.PasteSpecial(range, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
worksheet = null;
}
}
workbook.BeforeClose += Workbook_BeforeClose;
}
private void button3_Click(object sender, EventArgs e)
{
try
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("لا توجد أي بيانات");
return;
}
this.Cursor = Cursors.WaitCursor;
copyAlltoClipboard();
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
this.Cursor = Cursors.Default;
MessageBox.Show("حصل خطأ ما");
}
}
private void SelectedGridOnlyToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
CopySelectedtoClipboard();
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
((Excel.Range)xlWorkSheet.Cells[1, 1]).Value = "Montly Sum and Average " + DateTime.Now.ToString("yyyy/MM/dd_HH:mm:ss");
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[3, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// xlWorkBook.Close();
// xlexcel.Quit();
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlWorkSheet);
MessageBox.Show("Export Completed Sucessfully.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void save_excel(string file, int primary, int qty)
{
try
{
Excel.Application excel = new Excel.Application();
Excel.Workbook sheet = excel.Workbooks.Open(file);
Excel.Worksheet x = excel.Sheets["GBD_Asia"] as Excel.Worksheet;
Excel.Range userrange = x.UsedRange;
x.Cells[2, qty] = 0;
x.Cells[2, qty] = "";
Excel.Range rng = x.Cells[2, primary] as Excel.Range;
rng.Select();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(userrange);
Marshal.ReleaseComObject(x);
sheet.Close(true, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(sheet);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
catch (Exception exHandle)
{
Console.WriteLine("Exception: " + exHandle.Message);
Console.ReadLine();
}
}
/// <summary>
/// Method to collect all data from datagridview and save to clipboard to export to Excel SS
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void updateBtn_Click(object sender, EventArgs e)
{
//copy items to clipboard method
if (skuGrid.Rows.Count != 0)
{
if (taskIsRunning == false)
{
copyAlltoClipboard();
//instantiate workbook
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
//paste copied items in workbook
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
}
else
{
MessageBox.Show("Scrape is still running, please wait");
}
}
else
{
MessageBox.Show("There is no information to export");
}
}
public bool Excel_Write_Formula(int num_of_sheet, string str_cell, string str_set)
{
bool is_write = false;
if (_access && _xls_App != null && _xls_Book != null && _xls_SetOfSheets
!= null && num_of_sheet > 0 && num_of_sheet <= _numOfSheets)
{
_xls_Sheet = _xls_SetOfSheets.get_Item(num_of_sheet);
_xls_Range = _xls_Sheet.get_Range(str_cell, str_cell);
if (_xls_Range != null)
{
_xls_Range.Select();
_xls_Range.Formula = str_set;
is_write = true;
}
else
{
MessageBox.Show(null, "Запись данных невозможно! Нет доступа к указанной ячейке", "Ошибка записи формулы", 0);
}
}
else
{
MessageBox.Show(null, "Запись данных невозможна! Указанный лист недоступен", "Ошибка записи формулы", 0);
}
return(is_write);
}
/// <summary>
/// Reverts the changes done to Excel cell values after the last commit.
/// </summary>
/// <param name="refreshFromDb">Flag indicating if instead of reverting the data back to the way it was when the editing session started, it is pulled to have the most recent version of it.</param>
private void RevertDataChanges(bool refreshFromDb)
{
try
{
if (!refreshFromDb)
{
_mySqlTable.RejectChanges();
}
else
{
_mySqlTable.RefreshData();
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.EditDataRefreshErrorText, ex.Message);
}
Globals.ThisAddIn.SkipSelectedDataContentsDetection = true;
EditingWorksheet.UnprotectEditingWorksheet(EditingWorksheet_Change, WorksheetProtectionKey);
_editDataRange.Clear();
ExcelInterop.Range topLeftCell = _editDataRange.Cells[1, 1];
topLeftCell.Select();
_editDataRange = _mySqlTable.ImportDataIntoExcelRange(topLeftCell);
CommitChangesButton.Enabled = false;
AddNewRowToEditingRange(false);
}
/// ﹤summary﹥ //C# 操作Excel常用组件及类
/// 将图片插入到指定的单元格位置。
/// 注意：图片必须是绝对物理路径
/// ﹤/summary﹥
/// ﹤param name="RangeName"﹥单元格名称，例如：B4﹤/param﹥
/// ﹤param name="PicturePath"﹥要插入图片的绝对路径。﹤/param﹥
public void InsertPicture(string RangeName, string PicturePath)
{
m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
pics.Insert(PicturePath, m_objOpt);
}
public bool Excel_Read_Cell(int num_of_sheet, string str_cell, ref string str_get)
{
bool is_read = false;
if (_access && _xls_App != null && _xls_Book != null &&
_xls_SetOfSheets != null && num_of_sheet > 0 && num_of_sheet <= _numOfSheets)
{
_xls_Sheet = _xls_SetOfSheets.get_Item(num_of_sheet);
_xls_Range = _xls_Sheet.get_Range(str_cell, str_cell);
if (_xls_Range != null)
{
_xls_Range.Select();
dynamic value = _xls_Range.Value2;
if (value == null)
{
str_get = "";
}
else
{
str_get = value.ToString();
}
is_read = true;
}
else
{
MessageBox.Show(null, "Чтение данных невозможно! Нет доступа к указанной ячейки", "Ошибка чтения ячейки", 0);
}
}
else
{
MessageBox.Show(null, " Чтение данных невозможно! Указанный лист недоступен", "Ошибка чтения ячейки", 0);
}
return(is_read);
}
private void button1_Click(object sender, EventArgs e)
{
copyAlltoClipboard();
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
RemoveColoumn(xlWorkSheet, "A1");
AddRow(xlWorkSheet, 1);
Formazas(xlWorkSheet);
JatekosMinoseg(xlWorkSheet);
Application.Exit();
}
private void btnExcel_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count != 0)
{
Excel.Application excel = new Excel.Application();
excel.Visible = true;
object Missing = Type.Missing;
Workbook workbook = excel.Workbooks.Add(Missing);
Worksheet sheet1 = (Worksheet)workbook.Sheets[1];
int StartCol = 1;
int StartRow = 1;
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
Excel.Range myRange = (Excel.Range)sheet1.Cells[StartRow, StartCol + j];
myRange.Value2 = dataGridView1.Columns[j].HeaderText;
}
StartRow++;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
Excel.Range myRange = (Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
myRange.Value2 = dataGridView1[j, i].Value == null ? "" : dataGridView1[j, i].Value;
myRange.Select();
}
}
}
}
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 static double GetMin(Excel.Worksheet WS, string TableName, long col)
{
Excel.Range c = null;
Excel.PivotTable PT = WS.PivotTables(TableName);
long i = 0;
long FirstRow = 0;
long LastRow = 0;
//FIND THE LAST NON-BLANK CELL IN COLUMNA
FirstRow = PT.TableRange1.Row + 2;
LastRow = FirstRow + PT.TableRange1.Rows.Count - 4;
double Min = 0;
i = FirstRow;
Min = (double)WS.Cells[i, col].Value;
for (i = FirstRow; i <= LastRow - 2; i++)
{
c = WS.Cells[i, col];
c.Select();
if (c.Value2 != null && i <= LastRow - 2 && !((bool)c.Rows.Hidden) && (double)c.Value < Min)
{
if (WS.Cells[i, 1].Value == null || WS.Cells[i, 1].Value != "SubTotal")
{
Min = (double)c.Value;
}
}
}
return(Min);
}
private void ExportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
CopyAlltoClipboard();
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[3, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// xlWorkBook.Close();
// xlexcel.Quit();
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlWorkSheet);
MessageBox.Show("Attribute Table Successfully Exported!");
}
catch
{
}
}
public void Function_Click(IRibbonControl control_)
{
Xl.Application xlApp = (Xl.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
String fname = control_.Id;
fname = "=" + fname + "()";
Xl.Range rg = xlApp.ActiveCell;
String cellName = ExcelUtil.ExcelColumnIndexToName(rg.Column) + rg.Row;
Xl._Worksheet sheet = (Xl.Worksheet)xlApp.ActiveSheet;
Xl.Range range = sheet.get_Range(cellName, System.Type.Missing);
string previousFormula = range.FormulaR1C1.ToString();
range.Value2 = fname;
range.Select();
syncContext_ = SynchronizationContext.Current;
if (syncContext_ == null)
{
syncContext_ = new System.Windows.Forms.WindowsFormsSynchronizationContext();
}
FunctionWizardThread othread = new FunctionWizardThread(range, syncContext_);
Thread thread = new Thread(new ThreadStart(othread.functionargumentsPopup));
thread.Start();
}
private List <string> _get_autofiltered_first_cell_addr()
{
var ash = excelObj.Application.ActiveSheet;
//データ入力範囲の処理
Excel.Range vrange = ash.Range["A1"].End[Excel.XlDirection.xlDown];
int nr = 0;
nr = vrange.Rows[vrange.Rows.Count].Row;
//データ入力範囲を自動選択
Excel.Range sa = ash.Range[ash.Cells[1, 1], ash.Cells[nr, 1]];
sa.Select();
int r1, r2 = 0;
r1 = sa.Row;
r2 = sa.Rows[sa.Rows.Count].Row;
List <string> data = new List <string>();
for (int i = r1; i <= r2; i++)
{
if (sa.Cells[i, 1].EntireRow.Hidden == true)
{
continue;
}
data.Add(sa.Cells[i, 1].Address);
}
return(data);
}
private void button1_Click(object sender, EventArgs e)
{
DateTime x1 = DateTime.Parse(fecha1.Value.ToString("yyyy-MM-dd"));
DateTime x2 = DateTime.Parse(fecha2.Value.ToString("yyyy-MM-dd"));
Cursor.Current = Cursors.WaitCursor;
Mantenimiento man = new Mantenimiento();
string query = @"select a.Nombre, a.TipoContrato, a.FechaIngreso, b.Inicio, b.Final, b.TipoVacacion,
datediff(day, b.Inicio, b.Final) as 'Dias Totales', c.Descripcion as TipoEmpleado
from Empleado as a
left join Vacaciones as b
on a.IDEmpleado = b.IDEmpleado
inner join TipoEmpleado c
on a.TipoEmpleado = c.IDTipo
where b.Inicio between '" + x1 + "' and '" + x2 + "'";
man.cargarDGgeneral(gridAdmin, query);
copyAlltoClipboard();
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
Cursor.Current = Cursors.Default;
this.Close();
}
public string GetNextNumber(int i)
{
int index = 0;
double value = 0;
Excel.Range r = (Excel.Range)Application.ActiveCell;
if (i == 8)
{
i = r.Row - 1;
}
else if (i == 2)
{
i = r.Row + 1;
}
Math.DivRem(i, totalRange.Rows.Count + 1, out index);
try
{
Excel.Range cr = activeWorksheet.get_Range(string.Concat("G", index.ToString()));
value = cr.Value;
cr.Select();
palabra = value.ToString("N0");
return(palabra);
}
catch (Exception)
{
throw;
return("not text");
}
}
public string GetFirstWord()
{
// book= (Workbook)Application.ActiveWorkbook;
//book.Close(true);
try
{
this.Application.ActiveWorkbook.Close(false, missing, missing);
}
catch (Exception)
{
throw;
}
activeWorkbook = this.Application.Workbooks.Open(@"C:\Users\GMateusDP\Documents\SandBox\WindingNumbers.xlsx");
activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
totalRange = activeWorksheet.UsedRange;
Excel.Range range = activeWorksheet.get_Range("G9");
try
{
palabra = range.Value;
range.Select();
return(palabra);
}
catch (Exception)
{
return("not text");
throw;
}
}
/// <summary>
/// Export button allows for opening the populated gridview in excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonExport_Click(object sender, EventArgs e)
{
try
{
serialDataGridView.SelectAll();
DataObject dataObj = serialDataGridView.GetClipboardContent();
if (dataObj != null)
{
Clipboard.SetDataObject(dataObj);
}
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
private void button1_Click(object sender, EventArgs e)
{
//~~> Change Your String here
String textString = "I'm trying to add a pretty long text into the Excel sheet by using sheet. I use this code:" + Environment.NewLine +
"worksheet.Cells[1, 1] = textString;" + Environment.NewLine +
"The result is here:";
Clipboard.SetText(textString);
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
//~~> Add a new a workbook
xlWorkBook = xlexcel.Workbooks.Add(misValue);
//~~> Set Sheet 1 as the sheet you want to work with
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//~~> Set your range
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.Paste(CR, false);
// xlWorkBook.Close(true, misValue, misValue);
// xlexcel.Quit();
// releaseObject(xlWorkSheet);
// releaseObject(xlWorkBook);
// releaseObject(xlexcel);
}
private void btnExcel_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = "My_Watchlist.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
// Copy DataGridView results to clipboard
copyAlltoClipboardFromGrid();
object misValue = System.Reflection.Missing.Value;
Excel.Application xlexcel = new Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Format column C as text before pasting results, this was required for my data
Excel.Range rng = xlWorkSheet.get_Range("C:C").Cells;
rng.NumberFormat = "@";
// Paste clipboard results to worksheet range
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// Add header text from columns in datagridview to worksheet
for (int i = 0; i < gridMovies.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = gridMovies.Columns[i].HeaderText;
}
// Delete blank column K
Excel.Range delRng = xlWorkSheet.get_Range("K:K").Cells;
delRng.Delete(Type.Missing);
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
gridMovies.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
private void ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
copyAlltoClipboard();
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Worksheet xlWorkSheet1;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[2, 1];
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
xlWorkSheet1.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
xlWorkSheet.Columns[i].ColumnWidth = 18;
for (int j = 1; j < dataGridView1.Rows.Count + 1; j++)
{
AllBorders(xlWorkSheet.Cells[j, i].Borders);
xlWorkSheet.Cells[j, i].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
}
private void AddSubGroupBottomBorder(string PivotTableName)
{
Excel.PivotTable pt = this.PivotSheet.PivotTables(PivotTableName);
Excel.Range c = null;
Excel.Range c1 = null;
Excel.Range c2 = null;
Excel.Range row = null;
long LastCol = 0;
int i = 0;
int FirstRow = pt.TableRange1.Row;
int LastRow = FirstRow + pt.TableRange1.Rows.Count - 1;
LastCol = pt.TableRange1.Columns.Count;
for (i = 1; i < LastRow - FirstRow; i++)
{
c = pt.TableRange1.Cells[i, 3];
c.Select();
if (!c.EntireRow.Hidden && c.Value != null && c.Value.ToString().IndexOf("Total") > 0)
{
c1 = pt.TableRange1.Cells[i, 1];
c2 = pt.TableRange1.Cells[i, LastCol];
row = this.PivotSheet.Range[c1, c2];
row.Select();
row.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RoyalBlue);
row.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
row.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
row.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RoyalBlue);
row.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
row.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDash;
row.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
}
}
/// <summary>
/// Метод добавляет пустую строку.
/// </summary>
/// <param name="rowNum">Номер строки</param>
public void AddRow(int rowNum)
{
_range = (Excel.Range)_xlWorkSheet.Rows[rowNum, _misValue];
_range.Select();
_range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, _misValue);
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
private void btnExcel_Click(object sender, EventArgs e)
{
if ( !timer1.Enabled )
btnStart_Click(sender, e);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlFunction = xlApp.WorksheetFunction;
xlWorkSheetData = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheetData.Name = "Pendulum";
xlWorkSheetData.Activate();
xlWorkSheetData.Cells[1, 1] = "Constants used, mass: " + tbm.Text + ", gravity: " + tbG.Text + ", Spring Constant: " + tbk.Text + ", Length: " + tbH.Text;
xlRng = xlWorkSheetData.get_Range("A1", "N1");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[2, 1] = "Initial Values used, Intial X: " + tbXi.Text + ", Initial Y: " + tbYi.Text + ", Initial X Velocity: " + vx0.Text + ", Initial Y Velocity: " + vy0.Text;
xlRng = xlWorkSheetData.get_Range("A2", "N2");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[lastRowExcel, 1] = "t"; // changes these to whatever you want
xlWorkSheetData.Cells[lastRowExcel, 2] = "X";
xlWorkSheetData.Cells[lastRowExcel, 3] = "Y";
xlWorkSheetData.Cells[lastRowExcel, 4] = "Vx";
xlWorkSheetData.Cells[lastRowExcel, 5] = "Vy";
lblTransfer.Visible = true;
for (int i = 0; i < excelData.Count; i++)
{
xlWorkSheetData.Cells[i + 4, 1] = (excelData[i].time / 1000.00).ToString();
xlWorkSheetData.Cells[i + 4, 2] = excelData[i].x.ToString();
xlWorkSheetData.Cells[i + 4, 3] = excelData[i].y.ToString();
xlWorkSheetData.Cells[i + 4, 4] = excelData[i].vx.ToString();
xlWorkSheetData.Cells[i + 4, 5] = excelData[i].vy.ToString();
}
lblTransfer.Visible = false;
try //essaye le sauvegarde
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//sauvegarde le classeur courant
xlWorkBook.SaveAs(saveFileDialog1.FileName,
Excel.XlFileFormat.xlWorkbookDefault, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue,
misValue, misValue, misValue);
xlWorkBook.Close();
}
}
catch //en cas d'erreur affiche le message
{
MessageBox.Show("Impossible de sauvegarder le fichier.", "Erreur de sauvegarde de fichier Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
