public void writeRows(string Filename, string [] [] ary)
{
//===================================================
//打開已經存在的EXCEL工件簿文件
if (!System.IO.File.Exists(Filename))
{
xlBook = xlApp.Workbooks.Add(true);
}
else
{
xlBook = xlApp.Workbooks.Open(Filename, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
}
//停用警告訊息
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlBook.Activate();
xlSheet = (Worksheet )xlBook.Worksheets[1];
xlSheet.Activate();
//===================================================
for (Int32 i = 0; i <= ary.Length - 1; i++)
{
for (Int32 k = 0; k <= ary[i].Length - 1; k++)
{
xlSheet.Cells[i, k] = ary[i][k].ToString();
}
}
if (!System.IO.File.Exists(Filename))
{
xlBook.SaveAs(Filename, XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
xlBook.Save();
}
xlBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
xlBook = null;
xlSheet = null;
xlRange = null;
GC.Collect();
}
/// <summary> 以隐藏的方式在后台开启一个新的Excel程序，并在其中打开指定的Excel工作簿 </summary>
/// <param name="workbookPath"> 要打开的工作簿的绝对路径 </param>
/// <param name="sheetName"> 指定要打开的工作表的名称 </param>
/// <param name="readOnly"> 是否要以只读的方式打开工作簿 </param>
/// <param name="visible"> 新创建的Excel程序是否要可见 </param>
public ExcelApplication(string workbookPath, string sheetName, bool readOnly = true, bool visible = false)
{
// Application
var app = new Application {
Visible = visible
};
Application = app;
// ActiveWorkbook
_activeWorkbook = app.Workbooks.Open(workbookPath, ReadOnly: readOnly);
_activeWorkbook.Activate();
// ActiveWorksheet
_activeWorksheet = null;
foreach (Worksheet sht in ActiveWorkbook.Worksheets)
{
if (string.Compare(sht.Name, sheetName, StringComparison.OrdinalIgnoreCase) == 0)
{
_activeWorksheet = sht;
break;
}
}
if (_activeWorksheet == null)
{
throw new NullReferenceException("未找到指定名称的工作表");
}
else
{
_activeWorksheet.Activate();
}
}
public IExcelWorkbook CreateAndActivateNewWorkbook()
{
Workbook workbook = _application.Workbooks.Add(string.Empty);
workbook.Activate();
return(CreateNewWorkbookWrapper(workbook));
}
public override byte[] Convert(byte[] bDocument)
{
string filenameInput = Path.GetTempFileName();
string filenameOutput = Path.GetTempFileName();
Application excel = new Application();
excel.Visible = false;
excel.ScreenUpdating = false;
File.WriteAllBytes(filenameInput, bDocument);
Workbook workbook = excel.Workbooks.Open(filenameInput);
workbook.Activate();
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, filenameOutput);
workbook = null;
// excel has to be cast to type _Application so that it will find the correct Quit method.
((_Application)excel).Quit();
excel = null;
byte[] bPdf = File.ReadAllBytes(filenameOutput);
File.Delete(filenameInput);
File.Delete(filenameOutput);
return(bPdf);
}
private void ShowStartDocument()
{
_app.Visible = true;
_wbs = _app.Workbooks;
_wb = _wbs.Open(Path.GetFullPath(PathResolver.GenerateExcelPracticePath(DocumentPath)));
_wb.Activate();
_wb.BeforeClose += OnBeforeCloseExcel;
}
public Range ReadColumn(int rowstart, int rowstop, int column)
{
_workbook.Activate();
_currentWorksheet.Range[Cell(rowstart, column)].EntireColumn.Select();
var range = _currentWorksheet.Range[Cell(rowstart, column), Cell(rowstop, column)];
return(range);
}
private static void RemoveExtraWorksheet(Workbook workbook)
{
var extraWorksheetIndex = workbook.Worksheets.Count;
workbook.Activate();
workbook.Worksheets[extraWorksheetIndex].Activate();
workbook.Worksheets[extraWorksheetIndex].Delete();
workbook.Worksheets[1].Activate();
}
private void OnExportSerialesSeleccion(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Workbook wb = null;
object missing = Type.Missing;
Worksheet ws = null;
Range rng = null;
try
{
int filas_Seleccion = View.ListadoBusquedaCambioClasificacion.SelectedItems.Count;
if (filas_Seleccion > 0)
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Add();
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
for (int Idx = 0; Idx < View.GridViewListaSerialesClasificacion.Columns.Count; Idx++)
{
ws.Range["A1"].Offset[0, Idx].Value = View.GridViewListaSerialesClasificacion.Columns[Idx].Header.ToString();
ws.Range["A1"].Offset[0, Idx].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
}
int cont = 0;
foreach (DataRowView Registros in View.ListadoSerialesCambioClasificacion.Items)
{
ws.get_Range("A1", "H" + cont + 1).EntireColumn.NumberFormat = "@";
ws.Range["A2"].Offset[cont].Resize[1, View.GridViewListaSerialesClasificacion.Columns.Count].Value =
Registros.Row.ItemArray;
cont++;
}
rng = ws.get_Range("A1", "H" + cont + 1);
rng.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Columns.AutoFit();
excel.Visible = true;
wb.Activate();
}
else
{
Util.ShowMessage("Debe seleccionar uno o varios seriales para generar el archivo");
}
}
catch (Exception ex)
{
Util.ShowMessage("Problema al exportar la información a Excel: " + ex.ToString());
}
}
/// <summary> 以隐藏的方式在后台开启一个新的Excel程序，并在其中打开指定的Excel工作簿 </summary>
/// <param name="workbookPath"> 要打开的工作簿的绝对路径 </param>
/// <param name="sheetIndex"> 指定要打开的工作表的在集合中的下标，第一个工作表的下标为1 </param>
/// <param name="readOnly"> 是否要以只读的方式打开工作簿 </param>
/// <param name="visible"> 新创建的Excel程序是否要可见 </param>
public ExcelApplication(string workbookPath, int sheetIndex, bool readOnly = true, bool visible = false)
{
// Application
var app = new Application {
Visible = visible
};
Application = app;
// ActiveWorkbook
_activeWorkbook = app.Workbooks.Open(workbookPath, ReadOnly: readOnly);
_activeWorkbook.Activate();
// ActiveWorksheet
_activeWorksheet = ActiveWorkbook.Worksheets.Item[sheetIndex] as Worksheet;
_activeWorksheet.Activate();
}
/// <summary>
/// Initializes a new instance of the <see cref="WorkbookOperator"/> class.
/// </summary>
/// <param name="application">
/// The excel application object that contains the <see cref="Workbook"/> that is being operated on.
/// </param>
/// <param name="workbook">
/// The <see cref="Workbook"/> that is being operated on.
/// </param>
/// <param name="dialogNavigationService">
/// The instance of <see cref="IDialogNavigationService"/> that orchestrates navigation to dialogs
/// </param>
public WorkbookOperator(Application application, Workbook workbook, IDialogNavigationService dialogNavigationService)
{
if (application == null)
{
throw new ArgumentNullException(nameof(application), "the Excel application may not be null");
}
if (workbook == null)
{
throw new ArgumentNullException(nameof(workbook), "the workbook may not be null");
}
workbook.Activate();
this.workbook = workbook;
this.application = application;
this.DialogNavigationService = dialogNavigationService;
}
private void buttonStart_Click(object sender, RoutedEventArgs e)
{
//Excel.Application excelapp = new Excel.Application();
//Workbooks excelappallworkbooks = excelapp.Workbooks;
//string CurDir = Environment.CurrentDirectory;
OpenFileDialog openFile = new OpenFileDialog();
openFile.DefaultExt = ".xlsx"; // Default file extension
openFile.Filter = "Excel documents (.xlsx)|*.xlsx"; // Filter files by extension
openFile.InitialDirectory = CurDir;
openFile.ShowDialog();
filterInXlsxFile.IsEnabled = true;
//Открываем книгу и получаем на нее ссылку
excelworkbook = excelapp.Workbooks.Open(openFile.FileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelallsheets = excelworkbook.Worksheets;
//Получаем ссылку на лист 1
excelworksheet = (Excel.Worksheet)excelallsheets.get_Item(1);
//получаем номер последней ячейки
lastcell = excelworksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
lastcellnumber = lastcell.Row;
excelapp.Visible = true;
excelworkbook.Activate();
//выбираем первую ячейку по ней будем фильтровать
var excelcells = excelworksheet.get_Range("A1", Type.Missing);
List <string> UniqValues = GetUniqValues(1, excelworksheet, lastcellnumber);
foreach (var item in UniqValues)
{
listBox1.Items.Add(item);
}
}
/// <summary> 以隐藏的方式在后台开启一个新的Excel程序，并在其中打开指定的Excel工作簿 </summary>
/// <param name="workbookPath"> 要打开的工作簿的绝对路径 </param>
/// <param name="readOnly"> 是否要以只读的方式打开工作簿 </param>
/// <param name="visible"> 新创建的Excel程序是否要可见 </param>
public ExcelApplication(string workbookPath, bool readOnly = true, bool visible = false)
{
// Application
var app = new Application {
Visible = visible
};
Application = app;
// ActiveWorkbook
_activeWorkbook = app.Workbooks.Open(workbookPath, ReadOnly: readOnly);
_activeWorkbook.Activate();
// ActiveWorksheet
var shts = ActiveWorkbook.Worksheets;
_activeWorksheet = shts.Count == 0
? shts.Add() as Worksheet
: ActiveWorkbook.ActiveSheet as Worksheet;
}
/// <summary>
/// Initializes a new instance of the <see cref="WorkbookOperator"/> class.
/// </summary>
/// <param name="application">
/// The excel application object that contains the <see cref="Workbook"/> that is being operated on.
/// </param>
/// <param name="workbook">
/// The <see cref="Workbook"/> that is being operated on.
/// </param>
/// <param name="workbookMetadata">
/// The <see cref="WorkbookMetadata"/> that was saved.
/// </param>
public WorkbookOperator(Application application, Workbook workbook, WorkbookMetadata workbookMetadata)
{
if (application == null)
{
Logger.Error("The Excel application may not be null");
return;
}
if (workbook == null)
{
Logger.Error("The workbook may not be null");
return;
}
workbook.Activate();
this.application = application;
this.workbook = workbook;
this.workbookMetadata = workbookMetadata;
}
static void ExcelToPDF(string sourcePath, string ExportPath)
{
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWorkBook = null;
string paramSourceBookPath = sourcePath; //@"C:\Users\Lucky s\Desktop\New folder (2)\StocksOnTheMove.xls";
object paramMissing = Type.Missing;
string paramExportFilePath = ExportPath; // @"C:\Users\Lucky s\Desktop\New folder (2)\Test.pdf";
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = true;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = false;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
object oMissing = System.Reflection.Missing.Value;
// object Format=XlFixedFormatType
XlSaveAsAccessMode Mode = XlSaveAsAccessMode.xlNoChange;
//excelWorkBook.Application.DisplayAlerts = false;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
excelWorkBook.Activate();
// Save it in the target format.
if (excelWorkBook != null)
{
//excelWorkBook.SaveAs(paramExportFilePath);
// excelWorkBook.SaveAs(paramExportFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
}
catch (Exception ex)
{
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void Print_Button_Click(object sender, RoutedEventArgs e)
{
try
{
// open config
string configPath = Directory.GetCurrentDirectory() + @"\CatalogPrinter.config";
if (!File.Exists(configPath))
{
throw new Exception($"Config file " + configPath + " not found!");
}
ExeConfigurationFileMap configMap = new ExeConfigurationFileMap();
configMap.ExeConfigFilename = configPath;
Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configMap, ConfigurationUserLevel.None);
var appSettings = config.GetSection("appSettings") as AppSettingsSection;
// get config values
string hash = appSettings.Settings["password"]?.Value;
string masterCatalog = appSettings.Settings["masterCatalog"]?.Value;
string clientCatalog = appSettings.Settings["clientCatalog"]?.Value;
string outputPath = appSettings.Settings["outputPath"]?.Value;
if (hash == null)
{
throw new Exception($"Could not find 'password' key in " + configPath + "!");
}
if (masterCatalog == null)
{
throw new Exception($"Could not find 'masterCatalog' key in " + configPath + "!");
}
if (clientCatalog == null)
{
throw new Exception($"Could not find 'clientCatalog' key in " + configPath + "!");
}
if (outputPath == null)
{
throw new Exception($"Could not find 'outputPath' key in " + configPath + "!");
}
// check if files exists
if (!File.Exists(masterCatalog))
{
throw new Exception($"Workbook " + masterCatalog + " not found!");
}
if (!File.Exists(clientCatalog))
{
throw new Exception($"Workbook " + clientCatalog + " not found!");
}
// open master workbook
string password = HashUtil.Decrypt(hash);
Workbook = ExcelUtility.GetWorkbook(masterCatalog, password);
// get catalog type
string catalogType = CatalogTypeComboBox.SelectedItem.ToString();
// open temp workbook to which the sheets of interest are copied to
Workbook2Print = ExcelUtility.XlApp.Workbooks.Add();
if (!Directory.Exists(_tmpWorkbookDir))
{
Directory.CreateDirectory(_tmpWorkbookDir);
}
Workbook2Print?.SaveAs(_tmpWorkbookDir + _tmpWokbookName);
// get sheet order to print
var sheetOrder = GetSheetOrder(catalogType, clientCatalog);
string leftHeader = "null", centerHeader = "null", rightHeader = "null", leftFooter = "null", rightFooter = "null";
// copy necessary sheets to temp workbook and put sheets in correct order
foreach (var shName in sheetOrder)
{
if (ExcelUtility.GetWorksheetByName(Workbook, shName) == null)
{
throw new Exception($"Sheet " + shName + " not found in workbook " + masterCatalog + "!" +
"\nPlease check the sheet order input in " + clientCatalog + " for " + catalogType + ".");
}
// set catalog type
Workbook.Sheets[shName].Cells[11, 2] = catalogType;
leftHeader = (Workbook.Sheets[shName].Cells[16, 2] as Range).Value as string ?? "null";
centerHeader = (Workbook.Sheets[shName].Cells[17, 2] as Range).Value as string ?? "null";
var rightHeaderDate = ((Workbook.Sheets[shName].Cells[18, 2] as Range).Value);
rightHeader = "null";
if (rightHeaderDate != null)
{
rightHeader = rightHeaderDate.ToString("dd/MM/yyyy");
}
leftFooter = (Workbook.Sheets[shName].Cells[19, 2] as Range).Value as string ?? "null";
rightFooter = (Workbook.Sheets[shName].Cells[20, 2] as Range).Value as string ?? "null";
// copy sheet
if (catalogType == "Particulier")
{
//SetBtwField(Workbook.Sheets[shName], true);
Workbook.Sheets[shName].Copy(After: Workbook2Print.Sheets[Workbook2Print.Sheets.Count]);
Workbook2Print.Sheets[Workbook2Print.Sheets.Count].Cells[8, 2] = "ja";
//SetBtwField(Workbook.Sheets[shName], false);
Workbook.Sheets[shName].Copy(After: Workbook2Print.Sheets[Workbook2Print.Sheets.Count]);
Workbook2Print.Sheets[Workbook2Print.Sheets.Count].Cells[8, 2] = "neen";
}
else
{
Workbook.Sheets[shName].Copy(After: Workbook2Print.Sheets[Workbook2Print.Sheets.Count]);
}
}
// delete default first sheet on creation of workbook
Workbook2Print.Activate();
Workbook2Print.Worksheets[1].Delete();
// format and print sheets
string outputFile = outputPath + @"\catalog.pdf";
if (ExcelUtility.IsFileInUse(outputFile))
{
throw new Exception(outputFile + " is open, please close it and press 'Print' again.");
}
foreach (Worksheet sh in Workbook2Print.Worksheets)
{
FormatSheet(sh, leftHeader, centerHeader, rightHeader, leftFooter, rightFooter);
}
Workbook2Print.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, outputFile, OpenAfterPublish: true);
MessageBox.Show("Done!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
ExcelUtility.CloseWorkbook(Workbook, false);
ExcelUtility.CloseWorkbook(WorkbookSheetOrder, false);
ExcelUtility.CloseWorkbook(Workbook2Print, true);
File.Delete(_tmpWorkbookDir + _tmpWokbookName);
ExcelUtility.CloseExcel();
}
}
private void buttonDuzenle_Click(object sender, EventArgs e)
{
StringBuilder builder = new StringBuilder();
string fname = "";
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Yorumlar";
fdlg.InitialDirectory = @"d:\Ders Notları\Proje&Bitirme\Proje2 - bilvideo\Source\Python\classification\";
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fname = fdlg.FileName;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
int rowCount = Convert.ToInt32(numericBitis.Value) - Convert.ToInt32(numericBaslangic.Value);
int colCount = xlRange.Columns.Count;
progressBar3.Maximum = rowCount * colCount;
string bosluk = " ";
//progressBar2.Maximum = rowCount;
try
{
for (int i = Convert.ToInt32(numericBaslangic.Value); i <= Convert.ToInt32(numericBitis.Value); i++)
{
//progressBar1.Maximum = colCount;
for (int k = 1; k < colCount; k++)
{
if (xlRange.Cells[i, k].Value2 != null)
{
builder.Append(xlRange.Cells[i, k].Value2.ToString());
builder.Append(bosluk);
label4.Text = i.ToString() + ". Satırdaki " + k.ToString() + ". Sütun Dolu...";
//progressBar1.Increment(1);
xlWorksheet.Cells[i, k] = "";
}
labelSutun.Text = k.ToString();
progressBar3.Increment(1);
}
labelSatir.Text = i.ToString();
//progressBar1.Value = 0;
xlWorksheet.Cells[i, 1] = builder.ToString();
builder.Clear();
//progressBar2.Increment(1);
}
xlWorkbook.Activate();
xlWorkbook.Save();
}
catch (Exception exc)
{
}
//progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("Tamamlandı.");
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
//Guarda el nuevo formato de extracción de excel
public ActionResult AnalisisPlantillaExcel(Boolean IsModificacion = false)
{
//Inicializamos variable
rutaDocumento = "";
Resultado <HojasExcel> resultado = new Resultado <HojasExcel>();
try
{
resultado.Lista = new List <HojasExcel>();
//rutaDocumento = HostingEnvironment.MapPath("~/Temporal/" + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(Request.Files[0].FileName));
if (!IsModificacion)
{
//Validamos tipo de archivo
if (!Request.Files[0].FileName.ToUpper().EndsWith(".XLS") && !Request.Files[0].FileName.ToUpper().EndsWith(".XLSX"))
{
throw new Exception("Seleccione un archivo correcto en formato Excel.");
}
//Cargamos el archivo seleccionado
rutaDocumento = HostingEnvironment.MapPath("~/Temporal/" + Guid.NewGuid().ToString() + Request.Files[0].FileName);
Request.Files[0].SaveAs(rutaDocumento);
string[] columns = Request.Files[0].FileName.Split('_');
resultado.Cadena = columns[0];
}
else
{
resultado.Numero = mapaTrabajo.hoja;
//Obtenemos el archivo que se encuentra configurado.
rutaDocumento = HostingEnvironment.MapPath("~/Temporal/" + mapaTrabajo.dsarchivo);
}
//Inicializamos la aplicacion de Excel
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
// Asignamos propiedades del ejecucion
appExcel.Visible = false;
appExcel.ScreenUpdating = false;
Object filename = (Object)rutaDocumento;
//Abrimos el libro de trabajo
Workbook doc = appExcel.Workbooks.Open(rutaDocumento, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
doc.Activate();
//Obtenemos las hojas del documento
String[] excelSheets = new String[doc.Worksheets.Count];
//int i = 0;
foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in doc.Worksheets)
{
HojasExcel datosHoja = new HojasExcel();
datosHoja.Nombre = wSheet.Name;
datosHoja.noHoja = wSheet.Index;
resultado.Lista.Add(datosHoja);
}
Microsoft.Office.Interop.Excel._Worksheet hojas = (Microsoft.Office.Interop.Excel._Worksheet)doc.Sheets[1];
hojas.get_Range("A1", Missing.Value);
Microsoft.Office.Interop.Excel.Range last = hojas.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
// icrosoft.Office.Interop.Excel.Range last = hojas.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.cell);
Microsoft.Office.Interop.Excel.Range celdasDatos = hojas.get_Range("A1", last);
System.Array myvalues = (System.Array)celdasDatos.Cells.Value2;
List <CeldasExcel> datosExcel = new List <CeldasExcel>();
//foreach (Range dato in celdasDatos.Cells)
//{
// CeldasExcel filaExel = new CeldasExcel();
// filaExel.fila = dato.Row;
// filaExel.columna = dato.Row;
// filaExel.valor = Convert.ToString(dato.Value);
//}
int columnas = celdasDatos.Columns.Count;
int filas = celdasDatos.Rows.Count;
string[,] celdas = new string[columnas, filas];
//for(int x = 1; x<= myvalues.Length; x++)
//{
// myvalues.GetValue()
//}
//Guardamos y cerramos la aplicacion
object paramMissing = Type.Missing;
if (doc != null)
{
doc.Close(false, paramMissing, paramMissing);
doc = null;
}
// Quit Excel and release the ApplicationClass object.
if (appExcel != null)
{
appExcel.Quit();
appExcel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
resultado.ProcesoExitoso = 1;
}
catch (Exception ex)
{
resultado.ProcesoExitoso = 0;
resultado.Cadena = ex.Message;
}
return(Json(resultado, JsonRequestBehavior.DenyGet));
}
//Активация книги, делает книгу активным окном, работает и в Excel 2013
public static void ActivateBook(this Workbook wb)
{
wb.Application.ScreenUpdating = true;
wb.Activate();
wb.Application.ScreenUpdating = true;
}
//makeSheet - fills in a new sheet with option prices for the provided option dates
public void makeSheet(List <string> OptionDates)
{
if (OptionDates.Count == 0)
{
return;
}
try
{
ochain = TDAmeritrade.GetOptionChain(stockSymbol, $"{optionType}").Result;
}
catch (Exception e)
{
string sz = $"ERROR: {e.Message} ";
if (e.InnerException != null)
{
sz += e.InnerException.Message;
}
MessageBox.Show(sz, "Error");
//sheet.Cells[SheetRows.dataStart, SheetColumn.symbol].value = sz; //x
return;
}
if (ochain == null)
{
return;
}
app = sourceWorkbook.Application;
outputWorkbook = openOrCreateWorkbook(sourceWorkbook);
outputWorkbook.Activate();
sourceWorkbook.Sheets[templateSheetName].Copy(outputWorkbook.Sheets[1]);// .ActiveSheet);
sheet = outputWorkbook.Sheets[templateSheetName];
try
{
sheet.Name = stockSymbol + $"-{optionType}-" + DateTime.Now.ToString("d.h.m");
}
catch
{
sheet.Name = stockSymbol + $"-{optionType}-" + DateTime.Now.ToString("d.h.m.s");
}
sheet.Cells[SheetRows.underlying, SheetColumn.symbol].value = stockSymbol;
sheet.Cells[SheetRows.underlying, SheetColumn.last].Formula = @"= RTD(""tos.rtd"", , ""LAST"", Symbol)";
int Row = FirstDataRow;
foreach (string s in OptionDates)
{
Row = AddOptionData(stockSymbol, s, Row) + 2;
}
/*
* int iYear = DateTime.Today.Year;
* int iMonth = DateTime.Today.Month;
*
*
* Row = AddOptionData(stockSymbol, iYear + 2, 1, Row);
* Row += 2;
* Row = AddOptionData(stockSymbol, iYear + 1, 1, Row);
* Row += 2;
*
* if (iMonth != 12) //january is already done above
* {
* Row = AddOptionData(stockSymbol, (iMonth == 12 ? iYear + 1 : iYear), (iMonth + 1) % 12, Row);
* Row += 2;
* }
* Row = AddOptionData(stockSymbol, iYear, iMonth, Row);
*
*/
foreach (Range r2 in sheet.Range["N5:Y300"].Cells)
{
r2.Errors[XlErrorChecks.xlInconsistentFormula].Ignore = true;
}
sheet.Range["A3"].Select();
}
public void Run()
{
Workbook targetBook = null;
try
{
string dataFileFullPath = Path.Combine(Directory.GetCurrentDirectory(), CSVMergeToolDefine.ExcelFileName);
string dataFileFullPathNoExt = Path.Combine(Directory.GetCurrentDirectory(), CSVMergeToolDefine.ExcelFileName);
if (File.Exists(dataFileFullPath))
{
File.Delete(dataFileFullPath);
}
this.app = new Microsoft.Office.Interop.Excel.Application();
this.app.DisplayAlerts = false;
if (app == null)
{
Logger.Error("Excel is not properly installed");
return;
}
var csvFiles = GetCSVFiles();
if (csvFiles.Count < 0)
{
Logger.Error("Have no .csv files");
return;
}
targetBook = app.Workbooks.Add(missValue);
// Copy sheets
Logger.Warn("Progress");
foreach (var path in csvFiles)
{
CopyTargetCSVSheet(path, targetBook);
}
Logger.Warn("Removing empty sheet...");
Worksheet emptySheet = targetBook.Worksheets["Sheet1"];
emptySheet.Delete();
Logger.Warn("Save data.xlsx...");
targetBook.Activate();
targetBook.SaveAs(
dataFileFullPathNoExt,
Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
Missing.Value,
Missing.Value,
false,
false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution,
true,
Missing.Value, Missing.Value, Missing.Value);
Logger.Log("Success");
}
catch (Exception exception)
{
Logger.Error(exception.ToString());
Logger.Error("Fail");
}
finally
{
Release();
}
}
public void showWorkbook()
{
application.ScreenUpdating = true;
application.Visible = true;
workbook.Activate();
}
//Carga los datos de excel seleccionando nuevo mapa y buscar desde carpeta para ingresarlo a la bd como NUEVO MAPA
public ActionResult CargarDatosExcel(int noHoja, int noFila)
{
Resultado <CeldasExcel> resultado = new Resultado <CeldasExcel>();
try
{
if (rutaDocumento == "")
{
throw new Exception("Seleccione un archivo correcto en formato Excel");
}
resultado.Lista = new List <CeldasExcel>();
//Inicializamos la aplicacion de Excel
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
// Asignamos propiedades del ejecucion
appExcel.Visible = false;
appExcel.ScreenUpdating = false;
Object filename = (Object)rutaDocumento;
//Abrimos el libro de trabajo
Workbook doc = appExcel.Workbooks.Open(rutaDocumento, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
doc.Activate();
Microsoft.Office.Interop.Excel._Worksheet hojaActiva = (Microsoft.Office.Interop.Excel._Worksheet)doc.Sheets[noHoja];
hojaActiva.get_Range("A" + noFila, Missing.Value);
Microsoft.Office.Interop.Excel.Range last = hojaActiva.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
Microsoft.Office.Interop.Excel.Range rangeFin = hojaActiva.Cells[last.Row, (last.Column > 99 ? 100 : last.Column)];
Microsoft.Office.Interop.Excel.Range celdasDatos = hojaActiva.get_Range("A" + noFila, rangeFin);
int inicio = 0;
int filaAnterior = 0;
int topFilas = 0;
foreach (Range dato in celdasDatos.Cells)
{
CeldasExcel filaExel = new CeldasExcel();
filaExel.fila = dato.Row;
filaExel.columna = dato.Column;
filaExel.valor = Convert.ToString(dato.Value);
if (inicio == 0)
{
filaAnterior = filaExel.fila;
inicio = 1;
}
if (filaAnterior != filaExel.fila)
{
topFilas = topFilas + 1;
filaAnterior = filaExel.fila;
}
if (topFilas > 30)
{
break;
}
resultado.Lista.Add(filaExel);
}
//Guardamos y cerramos la aplicacion
object paramMissing = Type.Missing;
if (doc != null)
{
doc.Close(false, paramMissing, paramMissing);
doc = null;
}
// Quit Excel and release the ApplicationClass object.
if (appExcel != null)
{
appExcel.Quit();
appExcel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
resultado.ProcesoExitoso = 1;
}
catch (Exception ex)
{
resultado.ProcesoExitoso = 0;
resultado.Cadena = ex.Message;
}
return(Json(resultado, JsonRequestBehavior.DenyGet));
}
void ExcelToPDF(string sourcePath, string ExportPath)
{
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWorkBook = null;
string paramSourceBookPath = sourcePath;
object paramMissing = Type.Missing;
string paramExportFilePath = ExportPath;// @"C:\Users\Lucky s\Desktop\New folder (2)\Test.pdf";
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = false;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
object oMissing = System.Reflection.Missing.Value;
// XlSaveAsAccessMode Mode = XlSaveAsAccessMode.xl;
// excelWorkBook.Application.DisplayAlerts = false;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
excelWorkBook.Activate();
//Formating
Worksheet sheet = (Worksheet)excelWorkBook.Worksheets[1];
sheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
// Zoom property must be false, otherwise the "FitToPages" properties
// are ignored.
sheet.PageSetup.Zoom = false;
// these set the number of pages tall or wide the worksheet will be
// scaled to when printed.
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = 1;
// sheet.DisplayPageBreaks = true;
sheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
sheet.UsedRange.Font.Size = 14;
//sheet.UsedRange.Borders.Weight = 2;
//sheet.UsedRange.Cells.Borders.Weight = 2;
sheet.UsedRange.Font.Name = "Arial Narrow";
sheet.PageSetup.LeftMargin = 0.5;
sheet.PageSetup.RightMargin = 0.5;
sheet.PageSetup.TopMargin = 1;
sheet.PageSetup.BottomMargin = 0.5;
//End
// Save it in the target format.
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
}
catch (Exception ex)
{
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void readfile_Click(object sender, EventArgs e)
{
string fname = "";
string[] splits;
int number = 0;
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Yorumlar";
fdlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
//fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.Filter = "XML files (*.xml)|*.xml|CSV files (*.csv)|*.csv";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fname = fdlg.FileName;
}
if (fname != "")
{
yorumsay = ysy.Find(x => x.Id == 1);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int olumluSayisi = Convert.ToInt32(yorumsay.olumluSayisi);
int toplamYorumSayisi = Convert.ToInt32(yorumsay.olumluSayisi + yorumsay.olumsuzSayisi);
if (radioOlumlu.Checked)
{
progressBar1.Maximum = Convert.ToInt32(toplamYorumSayisi - olumluSayisi + 1);
Range line = (Range)xlWorksheet.Rows[olumluSayisi + 1];
line.Insert();
xlWorksheet.Cells[olumluSayisi + 1, 1] = (olumluSayisi).ToString() + (char)34 + " " + richTextBox1.Text;
for (int i = olumluSayisi + 2; i <= toplamYorumSayisi + 1; i++)
{
splits = new string[5];
splits = Regex.Split(xlRange.Cells[i, 1].Value2.ToString(), @"" + (char)34 + "");
number = Convert.ToInt32(splits[0]) + 1;
xlWorksheet.Cells[i, 1] = number.ToString() + (char)34 + splits[1];
progressBar1.Increment(1);
}
yorumsay.olumluSayisi = yorumsay.olumluSayisi + 1;
ysy.Update(yorumsay);
labelOlumlu.Text = yorumsay.olumluSayisi.ToString();
xlWorkbook.Activate();
xlWorkbook.Save();
progressBar1.Value = 0;
MessageBox.Show("Tamamlandı.");
}
else if (radioOlumsuz.Checked)
{
progressBar1.Maximum = 100;
if (xlRange.Cells[toplamYorumSayisi, 1] != null && xlRange.Cells[toplamYorumSayisi, 1].Value2 != null)
{
progressBar1.Increment(10);
//xlRange.Cells[toplamYorumSayisi + 1, 1].Value2 = toplamYorumSayisi.ToString() + (char)34 + " " + richTextBox1.Text;
xlWorksheet.Cells[toplamYorumSayisi + 1, 1] = toplamYorumSayisi.ToString() + (char)34 + " " + richTextBox1.Text;
progressBar1.Increment(10);
xlWorkbook.Activate();
progressBar1.Increment(10);
xlWorkbook.Save();
progressBar1.Increment(10);
yorum.yorumlar = toplamYorumSayisi.ToString() + (char)34 + " " + richTextBox1.Text;
progressBar1.Increment(10);
yorumsay.olumsuzSayisi = yorumsay.olumsuzSayisi + 1;
progressBar1.Increment(10);
ysy.Update(yorumsay);
progressBar1.Increment(40);
//progressBar1.Increment(1);
}
labelOlumsuz.Text = yorumsay.olumsuzSayisi.ToString();
progressBar1.Value = 0;
MessageBox.Show("Tamamlandı.");
//yorumsay = ysy.Find(x => x.Id == 1);
//yorumsay.olumsuzSayisi = 9997;
//ysy.Update(yorumsay);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close(true);
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
else
{
MessageBox.Show("Dosya seçiniz...");
}
}
