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 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();
}
public bool LimpiaExcel()
{
exHoja1 = null;
exApli = null;
exLibro = null;
GC.Collect();
return(true);
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Document Explorer---------------------------------------------------------
de = new DocumentExplorer();
ctp = this.CustomTaskPanes.Add(de, "Document Explorer");
ctp.VisibleChanged += new EventHandler(ctp_VisibleChanged);
Microsoft.Office.Interop.Excel.Application excelApp = Globals.ThisAddIn.Application;
//--------------------------------------------------------------------------
}
public Parse_Form(Microsoft.Office.Interop.Excel.Application excel)
{
_excel0 = excel;
_excel1 = excel;
_excel2 = excel;
_excel3 = excel;
_excel4 = excel;
InitializeComponent();
refedit1._Excel = _excel0;
refedit2._Excel = _excel1;
refedit3._Excel = _excel2;
refedit4._Excel = _excel3;
refedit5._Excel = _excel4;
}
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 AbreExcel(bool vAdd)
{
exApli = new Microsoft.Office.Interop.Excel.Application();
exApli.Visible = false;
if (_Fichero != "")
{
if (File.Exists(_Fichero))
{
if (vAdd)
{
exLibro = exApli.Workbooks.Open(_Fichero, vk_update_links, vk_No_read_only, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
}
else
{
File.Delete(_Fichero);
exLibro = exApli.Workbooks.Add(vk_missing);
}
}
else
{
exLibro = exApli.Workbooks.Add(vk_missing);
}
}
else
{
exLibro = exApli.Workbooks.Add(vk_missing);
}
//if (_Fichero != "")
//{
// exApli.Visible = true;
//}
//else
//{
// exApli.Visible = true;
//}
exApli.Visible = false;
return(true);
}
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 Exportara_Exel()// Método para exportar a excel.
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Libro1";
int cellRowIndex = 2; //ok
int cellColumnIndex = 1; //ok
//Pasa por cada fila y lee el valor de cada columna.
for (int i = -1; i < dgv_activos.Rows.Count - 0; i++) //Primera y ultima fila
{
for (int j = 0; j < dgv_activos.Columns.Count - 0; j++) //Columnas lado izquierdo y derecho
{
// El índice de Excel comienza desde 1,1. Como first Row tendría los encabezados de Columna, agregando una verificación de condición.
if (cellRowIndex == 2)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv_activos.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv_activos.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;//ok
cellRowIndex++;
}
excel.Visible = true;
}
catch (Exception error)
{
//MessageBox.Show("No se exportó correctamente" + error.Message);
}
}
private static void ShipToTransCost_01()
{
Cursor.Current = Cursors.WaitCursor;
isShipToTransCost_Running = true;
DateTime d = DateTime.Now;
CultureInfo provider = CultureInfo.InvariantCulture;
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
int lastR = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1;
string log = String.Format("Старт...\r\nВсего строк для обработки: {0}\r\n", lastR - 7);
int err = 0;
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
for (int curR = 10; curR <= lastR; curR++)
{
try
{
string sql = string.Format("EXEC [dbo].[ShipTo_GetTransportCost] @Code = N'{0}'", ws.Cells[curR, 1].Text.Trim());
using (SqlCommand cmd = new SqlCommand()
{
CommandType = CommandType.Text, Connection = conn, CommandText = sql
})
{
var r = cmd.ExecuteScalar();
decimal res = 0;
if (decimal.TryParse(r.ToString(), out res))
{
ws.Cells[curR, 17].Value = res;
}
else
{
ws.Cells[curR, 17].Value = "";
}
}
}
catch
{
err++;
log += String.Format("Ошибка импорта строки {0}\r\n", curR);
}
if (formProgress.InvokeRequired)
{
formProgress.BeginInvoke(
new System.Action(() =>
{
formProgress.SetProgress(curR, 7, lastR);
formProgress.SetLog(log);
}
));
}
}
conn.Close();
}
}
catch (Exception ex)
{
err++;
log += String.Format("Ошибка подключения к БД\r\n {0}\r\n", ex.Message);
}
DateTime d2 = DateTime.Now;
log += String.Format("Импорт завершен.\r\nВремя обработки {0:0} секунд.Количество ошибок: {1}", (d2 - d).TotalSeconds, err);
if (formProgress.InvokeRequired)
{
formProgress.BeginInvoke(
new System.Action(() =>
{
formProgress.CloseButtonEnable(true);
formProgress.SetLog(log);
}
));
}
Cursor.Current = Cursors.Default;
}
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;
}
}
public ExcelHelper()
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
}
public void AddVBAForExcel(string text, string newText, string excelPath)
{
VBIDE.VBComponent oModule;
Office.CommandBar oCommandBar;
Office.CommandBarButton oCommandBarButton;
String sCode;
Object oMissing = System.Reflection.Missing.Value;
Workbooks workbooks = excelApp.Workbooks;
Workbook workbook = excelApp.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Create a new VBA code module.
oModule = workbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
sCode =
"Function VBAMacro()\r\n" +
" Dim Cz As String\r\n" +
" Dim Th As String\r\n" +
" Cz = \"" + text + "\"\r\n" +
" Th = \"" + newText + "\"\r\n" +
" Cells.Replace What:=Cz, Replacement:=Th, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False\r\n" +
"VBAMacro=4 \r\n" +
"end Function ";
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
try
{
// Create a new toolbar and show it to the user.
oCommandBar = excelApp.CommandBars.Add("VBAMacroCommandBar", oMissing, oMissing);
oCommandBar.Visible = false;
// Create a new button on the toolbar.
oCommandBarButton = (Office.CommandBarButton)oCommandBar.Controls.Add(
Office.MsoControlType.msoControlButton,
oMissing, oMissing, oMissing, oMissing);
// Assign a macro to the button.
oCommandBarButton.OnAction = "VBAMacro";
// Set the caption of the button.
oCommandBarButton.Caption = "Call VBAMacro";
// Set the icon on the button to a picture.
oCommandBarButton.FaceId = 2151;
}
catch (Exception eCBError)
{
//已经存在了
}
excelApp.UserControl = true;
int strCount = 0;
Sheets sheets = workbook.Sheets;
ExcelMacroHelper ex = new ExcelMacroHelper(excelApp, workbook);
object outStr = null;
object[] objs = new object[0];
string cc = "";
foreach (Worksheet sheet in sheets)
{
string sheetName = sheet.Name;
sheet.Activate();
string vbaName = "VBAMacro";
ex.RunExcelMacro(excelPath, vbaName, objs, outStr, false);
cc += outStr;
}
workbook.Save();
workbook.Close(false, Type.Missing, Type.Missing);
// Release the variables.
oCommandBarButton = null;
oCommandBar = null;
oModule = null;
workbook = null;
excelApp = null;
// Collect garbage.
GC.Collect();
}
private static void ImportSPSR_Step01()
{
Cursor.Current = Cursors.WaitCursor;
isImportSPSR_Running = true;
DateTime d = DateTime.Now;
CultureInfo provider = CultureInfo.InvariantCulture;
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
int lastR = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1;
string log = String.Format("Старт...\r\nВсего строк для обработки: {0}\r\n", lastR - 7);
int err = 0;
for (int curR = 7; curR <= lastR; curR++)
{
try
{
Invoice inv = new Invoice();
inv.BoxQty = 1;
inv.Date = DateTime.FromOADate(ws.Cells[curR, 1].Value2);
inv.InvoiceNo = ws.Cells[curR, 2].Text.Trim();
inv.OrderCode = ws.Cells[curR, 3].Text.Trim();
inv.CityFrom = ws.Cells[curR, 4].Text.Trim().ToUpper();
inv.CityTo = ws.Cells[curR, 5].Text.Trim().ToUpper();
inv.Weight = Convert.ToDecimal(ws.Cells[curR, 6].Value2);
inv.VolumeWeight = Convert.ToDecimal(ws.Cells[curR, 7].Value2);
inv.Amount = Convert.ToDecimal(ws.Cells[curR, 9].Value2 * 1.18);
InsertInvoice(inv, "SPSR");
}
catch
{
err++;
log += String.Format("Ошибка импорта строки {0}\r\n", curR);
}
if (formProgress.InvokeRequired)
{
formProgress.BeginInvoke(
new System.Action(() =>
{
formProgress.SetProgress(curR, 7, lastR);
formProgress.SetLog(log);
}
));
}
}
DateTime d2 = DateTime.Now;
log += String.Format("Импорт завершен.\r\nВремя обработки {0:0} секунд.\r\nКоличество ошибок: {1}", (d2 - d).TotalSeconds, err);
if (formProgress.InvokeRequired)
{
formProgress.BeginInvoke(
new System.Action(() =>
{
formProgress.CloseButtonEnable(true);
formProgress.SetLog(log);
}
));
}
Cursor.Current = Cursors.Default;
}
//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);
}
