private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Access vsto application
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
//Access workbook
Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook;
//Access worksheet
Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1];
//Access vsto worksheet
Microsoft.Office.Tools.Excel.Worksheet sheet = Globals.Factory.GetVstoObject(m_sheet);
//Place some text in cell A1 without wrapping
Microsoft.Office.Interop.Excel.Range cellA1 = sheet.Cells.get_Range("A1");
cellA1.Value = "Sample Text Unwrapped";
//Place some text in cell A5 with wrapping
Microsoft.Office.Interop.Excel.Range cellA5 = sheet.Cells.get_Range("A5");
cellA5.Value = "Sample Text Wrapped";
cellA5.WrapText = true;
//Save the workbook
workbook.SaveAs("OutputVsto.xlsx");
//Quit the application
app.Quit();
}
private void button2_Click(object sender, EventArgs e)
{
SFD.Filter = "Файлы Excel (*.xls; *.xlsx) | *.xls; *.xlsx";
if (SFD.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.ActiveSheet;
worksheet.Name = "Выручка";
worksheet.Cells[1, 1] = "Выручка";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[2, i] = dataGridView1[i - 1, 0].Value;
worksheet.Columns[i].ColumnWidth = 30;
}
for (int i = 1; i < dataGridView1.RowCount; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value;
}
}
workbook.SaveAs(SFD.FileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}
}
private void button2_Click(object sender, EventArgs e)
{
//получение значения ИТОГО
con = ClassSQL.GetConect();
string sc_selectID = "select sum(price) as 'Выручка за период' from ticket t join film_session s on s.session_id = t.session_id and t.payment = 1 and t.date_ticket <= dateadd(month," + znach + ", getdate()) and t.date_ticket >= dateadd(month, -" + znach + ", getdate());";
con.Open();
scom3 = new SqlCommand(sc_selectID, con);
itogo = scom3.ExecuteScalar().ToString();
con.Close();
con.Dispose();
SFD.Filter = "Файлы Excel (*.xls; *.xlsx) | *.xls; *.xlsx";
if (SFD.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.ActiveSheet;
worksheet.Name = "Выручка";
worksheet.Cells[1, 1] = "Фильм";
worksheet.Cells[1, 2] = "Количество проданных билетов";
worksheet.Cells[1, 3] = "Сумма(в руб.)";
worksheet.Cells[12, 1] = "Итого:";
worksheet.Cells[12, 3] = itogo;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[2, i] = dataGridView1[i - 1, 0].Value;
worksheet.Columns[i].ColumnWidth = 30;
}
for (int i = 1; i < dataGridView1.RowCount; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value;
}
}
workbook.SaveAs(SFD.FileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}
}
public bool CierraExcel(bool vSave)
{
if (vSave)
{
//if (File.Exists(_Fichero)) { File.Delete(_Fichero); }
exLibro.SaveAs(_Fichero + ".xls", Excel.XlFileFormat.xlWorkbookNormal, vk_missing, vk_missing, vk_missing, vk_missing, Excel.XlSaveAsAccessMode.xlExclusive, vk_missing, vk_missing, vk_missing, vk_missing, vk_missing);
//csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue
}
exLibro.Close(vSave, vk_missing, vk_missing);
exApli.Workbooks.Close();
exApli.Quit();
exHoja1 = null;
exApli = null;
exLibro = null;
GC.Collect();
return(true);
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Access vsto application
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
//Access workbook
Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook;
//Access worksheet
Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1];
//Access vsto worksheet
Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(m_sheet);
//Access cells A1, A2, A3 , A4
Microsoft.Office.Interop.Excel.Range cellA1 = worksheet.Range["A1"];
Microsoft.Office.Interop.Excel.Range cellA2 = worksheet.Range["A2"];
Microsoft.Office.Interop.Excel.Range cellA3 = worksheet.Range["A3"];
Microsoft.Office.Interop.Excel.Range cellA4 = worksheet.Range["A4"];
//Set integer values in cells A1, A2 and A3
cellA1.Value = 10;
cellA2.Value = 20;
cellA3.Value = 30;
//Add formula in cell A4
cellA4.Formula = "=Sum(A1:A3)";
//Set the font bold in cell A4
cellA4.Font.Bold = true;
//Set the background color to Yellow in cell A4
cellA4.Interior.Color = Excel.XlRgbColor.rgbYellow;
//Save the workbook
workbook.SaveAs("OutputVsto.xlsx");
//Quit the application
app.Quit();
}
private void exportToExcel_Click(DataGridView transcationTableDataGridView)
{
try
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Records";
try
{
/*for (int i = 0; i < transcationTableDataGridView.Columns.Count; i++)
* {
* worksheet.Cells[1, i + 1] = transcationTableDataGridView.Columns[i].HeaderText;
* }*/
for (int i = 0; i < transcationTableDataGridView.Rows.Count; i++)
{
for (int j = 0; j < transcationTableDataGridView.Columns.Count; j++)
{
if (transcationTableDataGridView.Rows[i].Cells[j].Value != null && !transcationTableDataGridView.Rows[i].Cells[j].Value.ToString().Equals(""))
{
worksheet.Cells[i + 1, j + 1] = transcationTableDataGridView.Rows[i].Cells[j].Value.ToString();
/*Excel.Range ColorMeMine= worksheet.Cells[i + 1, j + 1];
* int ind = -1,ptr=0,color;
* foreach (var index in transcationTableDataGridView.Rows[i].Cells[j].Value.ToString().findAll(Environment.NewLine))
* {
* switch (ptr) {
* case 0: color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
* break;
* case 1:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
* break;
* case 2:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
* break;
* case 3:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
* break;
* case 4:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
* break;
* case 5:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Cyan);
* break;
* case 6:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Magenta);
* break;
* case 7:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold);
* break;
* case 8:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
* break;
* case 9:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Fuchsia);
* break;
* case 10:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Coral);
* break;
* case 11:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Indigo);
* break;
* case 12:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Lime);
* break;
* case 13:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Firebrick);
* break;
* case 14:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
* break;
* default:
* color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
* break;
* }
* ColorMeMine.Characters[ind+1, index].Font.Color = color;
* ind = index;
* ptr = ptr + 1;
* }*/
}
else
{
worksheet.Cells[i + 1, j + 1] = "";
}
}
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
app.Quit();
workbook = null;
worksheet = null;
}
}
catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType) {
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i-1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i-1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
//Run through all the files in the directory, and find and replace
public void find_replace()
{
string path = "";
// prompt the user to select the folder path
using (var fbd = new FolderBrowserDialog())
{
DialogResult result = fbd.ShowDialog();
if (result == DialogResult.OK && !string.IsNullOrWhiteSpace(fbd.SelectedPath))
{
string[] files = Directory.GetFiles(fbd.SelectedPath);
path = fbd.SelectedPath;
System.Windows.Forms.MessageBox.Show("Files found: " + files.Length.ToString() + " @ path: " + path, "Message");
}
}
object m = Type.Missing;
// Interaction.Inputbox is a user prompt, the user chooses the string he/she would like to replace
string replace = Interaction.InputBox("Type the text you would like to replace", "Find text", "Default", -1, -1);
string replacement = Interaction.InputBox("Replace that text with", "Replace text", "Default", -1, -1);
var xlApp = new Microsoft.Office.Interop.Excel.Application();
// grab all the excel files at that directory
DirectoryInfo d = new DirectoryInfo(@path);
FileInfo[] listOfFiles_1 = d.GetFiles("*.xlsx").ToArray();
FileInfo[] listOfFiles_2 = d.GetFiles("*.xls").ToArray();
FileInfo[] listOfFiles_3 = d.GetFiles("*.xlsm").ToArray();
FileInfo[] listOfFiles_4 = d.GetFiles("*.xltx").ToArray();
FileInfo[] listOfFiles_5 = d.GetFiles("*.xltm").ToArray();
FileInfo[] listOfFiles_6 = d.GetFiles("*.xlt").ToArray();
// produce a list of files in an array
FileInfo[] listOfFiles = (listOfFiles_1.Concat(listOfFiles_2)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_3)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_4)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_5)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_6)).ToArray();
xlApp.DisplayAlerts = false;
// traverse through each file
int count = 0;
foreach (FileInfo file in listOfFiles)
{
var xlWorkBook = xlApp.Workbooks.Open(file.FullName);
string file_name = file.FullName.Remove(file.FullName.Length - 5);
string file_ext = file.FullName.Substring(file.FullName.Length - 4);
// traverse through each worksheet in each file
foreach (Excel.Worksheet xlWorkSheet in xlWorkBook.Worksheets)
{
// get the used range.
Excel.Range r = (Excel.Range)xlWorkSheet.UsedRange;
Excel.Range first = r.Find(replace, m, m, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, m, m, m);
if (first != null)
{
count++;
Excel.Range start = first;
do
{
start.Value = start.Value.Replace(replace, replacement);
count++;
start = r.FindNext(m);
// if file is xltx, has to save as to overwrite the new changes
if (file_ext == "xltx")
{
xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplate,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
// if file is xltm, has to save as to overwrite the new changes
else if (file_ext == "xltm")
{
xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplateMacroEnabled,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
else
{
xlWorkBook.Save();
}
}while (start != first && start != null);
}
/*
* // call the replace method to replace instances.
* bool success = (bool)r.Replace(
* replace,
* replacement,
* Excel.XlLookAt.xlPart,
* Excel.XlSearchOrder.xlByRows, false, m, m, m);
*/
}
xlWorkBook.Close();
}
MessageBox.Show(count + " replacements has been made");
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
