private void PrepareWorksheetReadyForData() { Excel.Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Workbook workbook = workbooks.get_Item(workbooks.Count); Excel.Sheets sheets = workbook.Worksheets; Excel.Worksheet sheet = null; // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerHeapSize = new Comparers.HeapSize(sheet, iCol1FileName, iCol2FileName); iComparerHeapSize.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerCellCounts = new Comparers.CellCounts(sheet, iCol1FileName, iCol2FileName); iComparerCellCounts.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerLargestCells = new Comparers.LargestCells(sheet, iCol1FileName, iCol2FileName); iComparerLargestCells.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerFragmentation = new Comparers.Fragmentation(sheet, iCol1FileName, iCol2FileName); iComparerFragmentation.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerSlackSpace = new Comparers.SlackSpace(sheet, iCol1FileName, iCol2FileName); iComparerSlackSpace.PrepareWorksheetReadyForData(); }
public void WriteResultToExcelAttendance() { this.xlApp = new Microsoft.Office.Interop.Excel.Application(); if (this.xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); } this.xlApp.Visible = false; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1); Excel.Sheets xlWorksheet = xlWorkBook.Worksheets; var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]); int row = 1; foreach (Attendance student in attendanceList) { xlNewSheet.Cells[row, 1] = student.StudentNo; xlNewSheet.Cells[row, 2] = student.Name; xlNewSheet.Cells[row, 3] = student.Visa; xlNewSheet.Cells[row, 4] = student.NewWarning; xlNewSheet.Cells[row, 5] = student.CourseCode; xlNewSheet.Cells[row, 6] = student.StartDate; xlNewSheet.Cells[row, 7] = student.EndDate; xlNewSheet.Cells[row, 8] = student.CurrentAttendace; xlNewSheet.Cells[row, 9] = student.OverallAttendace; //foreach (Payment payment in payments) //{ // xlNewSheet.Cells[row, 1] = student.StNo; // if (payment.DueDate < System.DateTime.Today) // { // Excel.Range cell = xlNewSheet.Cells[row, 7]; // cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // } // xlNewSheet.Cells[row, 7] = payment.DueDate; // xlNewSheet.Cells[row, 8] = payment.PaymentAmount; // xlNewSheet.Cells[row, 9] = payment.Note; row++; //} } //Excel.Range range = xlNewSheet.UsedRange; //range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls"); if (string.IsNullOrEmpty(filename)) { filename = "result.xls"; } xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWorkBook.Close(true, Type.Missing, Type.Missing); xlApp.Quit(); MessageBox.Show("Done!!!!"); }
public static void printDataSetToExcel(Excel.Workbook workbook, ListSerializableDataStruct lsds, int sheetNumber) { Excel.Worksheet newDataSetSheet = null; Excel.Sheets excel_sheets = workbook.Worksheets; newDataSetSheet = excel_sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); newDataSetSheet.Name = "New Data Set" + sheetNumber; for (int i = 4; i < lsds.dataTypesLabels.Count; i++) { newDataSetSheet.Cells[1, i - 3].Value2 = lsds.dataTypesLabels[i]; } for (int i = 2; i < lsds.getCount() + 2; i++) { int j = 1; SerializableDataStruct sds = lsds.getSDS(i - 2); newDataSetSheet.Cells[i, j++].Value2 = sds.wind_speed; newDataSetSheet.Cells[i, j++].Value2 = sds.cross_wind; newDataSetSheet.Cells[i, j++].Value2 = sds.head_wind; newDataSetSheet.Cells[i, j++].Value2 = sds.temp; newDataSetSheet.Cells[i, j++].Value2 = sds.wind_chill; newDataSetSheet.Cells[i, j++].Value2 = sds.rel_hum; newDataSetSheet.Cells[i, j++].Value2 = sds.heat_index; newDataSetSheet.Cells[i, j++].Value2 = sds.dew_point; newDataSetSheet.Cells[i, j++].Value2 = sds.wet_bulb; newDataSetSheet.Cells[i, j++].Value2 = sds.bar; newDataSetSheet.Cells[i, j++].Value2 = sds.alt; newDataSetSheet.Cells[i, j++].Value2 = sds.den_alt; } }
private static void Loop() { for (int i = 0; i < 5; i++) { GC.WaitForPendingFinalizers(); GC.Collect(); } List <(int, int, int, float)> data = GetData(new List <(int, int, int, float)>()); if (opened) { CloseExcel(); Thread.Sleep(1000); } Console.WriteLine("Writing to Excel file"); string fileName = "C:\\Users\\sietz\\Desktop\\Onderzoeksmethoden\\ExcelWriter\\ExcelWriter\\bin\\Debug\\data.xlsx"; Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(fileName); Excel.Sheets xlSheets = xlWorkBook.Sheets as Excel.Sheets; Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlWorkSheet.Name = GetName(); WriteData(data, xlWorkSheet); xlWorkBook.Save(); CloseExcel(ref xlWorkBook, ref xlSheets, ref xlWorkSheet); }
/// <summary> /// Creates a worksheet with a given name. If a worksheet with that name /// exists, an ApplicationException is thrown. /// </summary> /// <param name="name">The name for the new worksheet.</param> /// <returns>The new worksheet.</returns> internal Excel.Worksheet CreateWorksheet(string name) { Excel.Sheets sheetCollection = Globals.ThisWorkbook.Worksheets; foreach (object item in sheetCollection) { if (ExcelHelpers.GetName(item) == name) { throw new ArgumentException( String.Format( CultureInfo.CurrentUICulture, Properties.Resources.WorksheetExistsError, name), "name"); } } object after; if (sheetCollection.Count != 0) { after = sheetCollection[sheetCollection.Count]; } else { after = missing; } Excel.Worksheet sheet; sheet = (Excel.Worksheet)sheetCollection.Add(missing, after, missing, missing); sheet.Name = name; return(sheet); }
public Excel.Worksheet CreateSheet(string name) { if (!IsOpen) { return(null); } Excel.Worksheet temp = FindSheet(name); if (temp != null) { return(temp); } try { temp = excelSheets.Add(); temp.Name = name; } catch (Exception ex) { Console.WriteLine("ExcelWrapper CreateSheet: Error, " + ex.Message); temp = null; } excelWorksheet = temp; curRow = curCol = 1; return(temp); }
private void Button3_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.DisplayAlerts = false; string filePath = @"d:\csharp-Excel.xlsx"; Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets xlWorkSheets = xlWorkBook.Worksheets; var xlNewSheet = (Excel.Worksheet)xlWorkSheets.Add(xlWorkSheets[1], Type.Missing, Type.Missing, Type.Missing); xlNewSheet.Name = "Welcome new"; xlNewSheet.Cells[1, 1] = "Welcome to New Sheet"; xlNewSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); xlNewSheet.Select(); xlWorkBook.Save(); xlWorkBook.Close(); releaseObject(xlNewSheet); releaseObject(xlWorkSheets); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Sheet Created"); }
private void createNewSheet(Excel.Worksheet template, double[,] data, int counter, string fileName) { string templateRange = "A1:EX10100"; int cellRow = 2; int cellCol = 1; Excel.Worksheet sheet; if (counter == 1) { sheet = (Excel.Worksheet)outputWorkBook.Worksheets.get_Item(counter); } else { sheet = (Excel.Worksheet)worksheets.Add(Type.Missing, worksheets[counter - 1], Type.Missing, Type.Missing); } sheet.Name = counter.ToString(); template.get_Range(templateRange).Copy(sheet.get_Range(templateRange)); sheet.Cells[1, 1].Value = fileName; var startCell = sheet.Cells[cellRow, cellCol]; var endCell = sheet.Cells[cellRow + data.GetLength(0) - 1, cellCol + data.GetLength(1) - 1]; var writeRange = (Excel.Range)sheet.Range[startCell, endCell]; writeRange.Value = data; releaseObject(sheet); }
private void PrepareWorksheetReadyForData( ) { Excel.Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Workbook workbook = workbooks.get_Item(workbooks.Count); Excel.Sheets sheets = workbook.Worksheets; iSheetChunkSize = (Excel.Worksheet)sheets.get_Item(1); CreateSheet(iSheetChunkSize, "Chunk Size"); iSheetFree = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); CreateSheet(iSheetFree, "Free Size"); iSheetAlloc = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); CreateSheet(iSheetAlloc, "Alloc Size"); }
private void NewSheet() { int n = mWorksheets.Count; mWorksheets.Add(After: mWorksheets[n]); mActiveSheet = mWorksheets[n + 1]; }
private static void DrillThroughThreadSafe(QueryClient queryClient) { Excel.Range rngCell = queryClient.RangeCell; // create sheet Excel.Sheets sheets = xlApp.Sheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add(); // show message to user we are retrieving records Excel.Range rngHead = sheet.Range["A1"]; int maxDrillThroughRecords = ExcelHelper.GetMaxDrillthroughRecords(rngCell); rngHead.Value2 = string.Format("Retrieving TOP {0} records", maxDrillThroughRecords); // set up connection var connString = ExcelHelper.GetConnectionString(rngCell); var commandText = queryClient.GetDAXQuery(connString); var daxClient = new DaxClient(); var cnnStringBuilder = new TabularConnectionStringBuilder(connString); var cnn = new ADOMD.AdomdConnection(cnnStringBuilder.StrippedConnectionString); // retrieve result var dtResult = daxClient.ExecuteTable(commandText, cnn); // output result to sheet Excel.Range rngOut = sheet.Range["A3"]; ExcelHelper.FillRange(dtResult, rngOut); ExcelHelper.FormatRange(dtResult, rngOut); rngHead.Value2 = string.Format("Retrieved TOP {0} records", maxDrillThroughRecords); }
public bool AddWorkSheetAtTheEnd(string name, out string outMessage) { outMessage = string.Empty; try { var xlNewSheet = (Excel.Worksheet)ws.Add(Type.Missing, ws[ws.Count], Type.Missing, Type.Missing); xlNewSheet.Name = name; //xlNewSheet.Cells[1, 1] = "New 555555 content"; return(true); } catch (Exception err) { outMessage = err.Message; return(false); } }
static void Main(string[] args) { Console.WriteLine("Interop Assemblies Performance Test - 10000 Cells."); Console.WriteLine("Write simple text, change Font, NumberFormat and do a BorderArround."); // start excel, and get a new sheet reference Excel.Application excelApplication = CreateExcelApplication(); Excel.Workbooks books = excelApplication.Workbooks; Excel.Workbook book = books.Add(Missing.Value); Excel.Sheets sheets = book.Worksheets; Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet; // do test 10 times List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>(); List <TimeSpan> timeElapsedList = new List <TimeSpan>(); for (int i = 1; i <= 10; i++) { DateTime timeStart = DateTime.Now; for (int y = 1; y <= 10000; y++) { string rangeAdress = "$A" + y.ToString(); Excel.Range cellRange = sheet.Range[rangeAdress]; cellRange.Value = "value"; Excel.Font font = cellRange.Font; font.Name = "Verdana"; cellRange.NumberFormat = "@"; cellRange.BorderAround(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 0); comReferencesList.Add(font as MarshalByRefObject); comReferencesList.Add(cellRange as MarshalByRefObject); } TimeSpan timeElapsed = DateTime.Now - timeStart; // display info and dispose references Console.WriteLine("Time Elapsed: {0}", timeElapsed); timeElapsedList.Add(timeElapsed); foreach (var item in comReferencesList) { Marshal.ReleaseComObject(item); } comReferencesList.Clear(); } // display info & log to file TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-Interop.log"); Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine); Console.Read(); // release & quit Marshal.ReleaseComObject(sheet); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(book); Marshal.ReleaseComObject(books); excelApplication.Quit(); Marshal.ReleaseComObject(excelApplication); }
public static void ExportToExcel(string fileName, string startDate, string endDate) { CDNURnvoiceGenerate(startDate, endDate); Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.DisplayAlerts = false; string filePath = fileName; Excel.Workbook xlWorkBook = oXL.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets worksheets = xlWorkBook.Worksheets; Excel.Range oRng; var oSheet = (Excel.Worksheet)worksheets.Add(worksheets[oXL.ActiveWorkbook.Worksheets.Count], Type.Missing, Type.Missing, Type.Missing); oSheet.Name = "CDNUR"; #region cotent int i = 0; int j = 0; for (i = 0; i <= mDt.Rows.Count - 1; i++) { for (j = 0; j <= mDt.Columns.Count - 1; j++) { oSheet.Cells[i + 1, j + 1] = mDt.Rows[i][j];// cell.Value; } } oXL.StandardFont = "Cambria"; oSheet.get_Range("A1", "A1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244)))))); oSheet.get_Range("A2", "M2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244)))))); oSheet.get_Range("A1", "M2").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); oSheet.get_Range("A4", "M4").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Bisque); oSheet.get_Range("I3", "L" + (mDt.Rows.Count + 1)).NumberFormat = "#,###,###0.00"; oSheet.get_Range("A1", "M" + (mDt.Rows.Count + 1)).Font.FontStyle = "Cambria"; oSheet.get_Range("A1", "M2").Font.Bold = true; oSheet.get_Range("A1", "M2").Font.Size = 12; oSheet.get_Range("A1", "H" + (mDt.Rows.Count + 1)).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oSheet.get_Range("A1", "H" + (mDt.Rows.Count + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //BorderLine oSheet.get_Range("A1", "M3").Borders.LineStyle = Excel.XlLineStyle.xlContinuous; oRng = oSheet.get_Range("A1", "M3"); oRng.EntireColumn.AutoFit(); #endregion oSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); oSheet.Select(); xlWorkBook.Save(); xlWorkBook.Close(); releaseObject(oSheet); releaseObject(worksheets); releaseObject(xlWorkBook); releaseObject(oXL); }
private void OnEditPallets(object sender, EventArgs e) { try { // get the collection of work sheets Excel.Sheets sheets = Globals.StackBuilderAddIn.Application.Worksheets; Excel.Worksheet worksheet = null; // find the "Pallets" worksheet try { worksheet = (Excel.Worksheet)sheets["Pallets"]; } catch (System.Runtime.InteropServices.COMException) { worksheet = (Excel.Worksheet)sheets.Add(); worksheet.Name = "Pallets"; // modify header worksheet.get_Range("a" + 1, "a" + 1).Value = "Name"; worksheet.get_Range("b" + 1, "b" + 1).Value = "Description"; worksheet.get_Range("c" + 1, "c" + 1).Value = "Length"; worksheet.get_Range("d" + 1, "d" + 1).Value = "Width"; worksheet.get_Range("e" + 1, "e" + 1).Value = "Height"; worksheet.get_Range("f" + 1, "f" + 1).Value = "Weight"; worksheet.get_Range("g" + 1, "g" + 1).Value = "Form factor"; Excel.Range headerRange = worksheet.get_Range("a" + 1, "g" + 1); headerRange.Font.Bold = true; // initialize pallet sheet string[] palletTypes = PalletData.TypeNames; int i = 2; foreach (string typeName in palletTypes) { PalletData palletData = PalletData.GetByName(typeName); worksheet.get_Range("a" + i, "a" + i).Value = palletData.Name; worksheet.get_Range("b" + i, "b" + i).Value = palletData.Description; worksheet.get_Range("c" + i, "c" + i).Value = palletData.Length; worksheet.get_Range("d" + i, "d" + i).Value = palletData.Width; worksheet.get_Range("e" + i, "e" + i).Value = palletData.Height; worksheet.get_Range("f" + i, "f" + i).Value = palletData.Weight; worksheet.get_Range("g" + i, "g" + i).Value = palletData.Name; ++i; } // fit column width worksheet.get_Range("a" + 1, "g" + (i - 1)).Columns.AutoFit(); worksheet.Activate(); } OnRefreshPallets(sender, e); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void DataSetToExcel(DataSet dataSet) { Excel.Workbook activeWorkBook = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Sheets sheets = activeWorkBook.Sheets; sheets.Add(); activeWorkBook.XmlMaps.Add(dataSet.GetXmlSchema(), dataSet.DataSetName); activeWorkBook.XmlImportXml(dataSet.GetXml(), out _, true, "$A1"); activeWorkBook.RefreshAll(); }
public void addWorksheet(String worksheetName, int idx) { //Get current workbook sheets Excel.Sheets xlSheets = oWB.Sheets as Excel.Sheets; //The first argument below inserts the new worksheet as the first one Excel.Worksheet xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[idx], Type.Missing, Type.Missing, Type.Missing); xlNewSheet.Name = worksheetName; }
public IWorksheet NewWorksheet() { AssertNotDisposed(); var _worksheet = _sheets.Add(After: worksheets[worksheets.Count - 1]._worksheet); var worksheet = new Worksheet(ExcelApplication, this, _worksheet, worksheetDisposeCallback, false); worksheets.Add(worksheet); return(worksheet); }
public bool saveData(string path) { app = new Excel.Application(); workbook = app.Workbooks.Open(path); sheets = workbook.Sheets; try { testSetSheet = sheets.Add(Type.Missing, sheets[sheets.Count] as Excel.Worksheet, Type.Missing, Type.Missing) as Excel.Worksheet; coverageSheet = sheets.Add(Type.Missing, sheets[sheets.Count] as Excel.Worksheet, Type.Missing, Type.Missing) as Excel.Worksheet; saveTestSetToExcel(); saveCoverageToExcel(); workbook.Save(); } finally { releaseResource(); } return(true); }
public static Excel.Worksheet AddSheet(Excel.Worksheet sh1) { Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet sh2 = null; workbook = (Excel.Workbook)sh1.Parent; sheets = workbook.Sheets; sh2 = sheets.Add(); return(sh2); }
static void Main(string[] args) { Console.WriteLine("Interop Assemblies Performance Test - 5000 Cells."); Console.WriteLine("Write simple text."); // start excel, and get a new sheet reference Excel.Application excelApplication = CreateExcelApplication(); Excel.Workbooks books = excelApplication.Workbooks; Excel.Workbook book = books.Add(Missing.Value); Excel.Sheets sheets = book.Worksheets; Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet; // do test 10 times List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>(); List <TimeSpan> timeElapsedList = new List <TimeSpan>(); for (int i = 1; i <= 10; i++) { DateTime timeStart = DateTime.Now; for (int y = 1; y <= 5000; y++) { string rangeAdress = "$A" + y.ToString(); Excel.Range range = sheet.get_Range(rangeAdress); range.Value = "value"; comReferencesList.Add(range as MarshalByRefObject); } TimeSpan timeElapsed = DateTime.Now - timeStart; // display info and dispose references Console.WriteLine("Time Elapsed: {0}", timeElapsed); timeElapsedList.Add(timeElapsed); foreach (var item in comReferencesList) { Marshal.ReleaseComObject(item); } comReferencesList.Clear(); } // display info & log to file TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test1-Interop.log"); Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine); Console.Read(); // release & quit Marshal.ReleaseComObject(sheet); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(book); Marshal.ReleaseComObject(books); excelApplication.Quit(); Marshal.ReleaseComObject(excelApplication); }
/// <summary> /// Добавление нового листа в текущую книгу /// </summary> /// <param name="sheetName">Имя нового листа</param> public void AddNewSheet(string sheetName) { try { excelsheets.Add(Type.Missing); excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelworksheet.Name = sheetName; } catch (Exception ex) { } }
static void ReleaseCOM() { Excel.Application app = null; Excel.Workbooks books = null; Excel.Workbook book = null; Excel.Sheets sheets = null; Excel.Worksheet sheet = null; Excel.Range range = null; try { app = new Excel.Application(); books = app.Workbooks; book = books.Add(); sheets = book.Sheets; sheet = sheets.Add(); range = sheet.Range["A1"]; range.Value = "Lorem Ipsum"; book.SaveAs(@"C:\Temp\ExcelBook" + DateTime.Now.Millisecond + ".xlsx"); book.Close(); app.Quit(); } finally { if (range != null) { Marshal.ReleaseComObject(range); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } if (sheets != null) { Marshal.ReleaseComObject(sheets); } if (book != null) { Marshal.ReleaseComObject(book); } if (books != null) { Marshal.ReleaseComObject(books); } if (app != null) { Marshal.ReleaseComObject(app); } } }
public void WriteResultToExcel() { this.initApplication(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1); Excel.Sheets xlWorksheet = xlWorkBook.Worksheets; var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]); int row = 1; foreach (CheckLog check in resultList1) { xlNewSheet.Cells[row, 1] = check.Barcode; xlNewSheet.Cells[row, 2] = check.ItemDescription; xlNewSheet.Cells[row, 3] = check.UserInit; if (check.Status) { xlNewSheet.Cells[row, 4] = ""; xlNewSheet.Cells[row, 5] = "IN"; } else { xlNewSheet.Cells[row, 4] = check.StudentNo; xlNewSheet.Cells[row, 5] = "Out"; } xlNewSheet.Cells[row, 6] = check.CheckOutDate; xlNewSheet.Cells[row, 7] = check.ExpireDate; row++; } //Excel.Range range = xlNewSheet.UsedRange; //range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); var filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls"); bool temp; if (Boolean.TryParse(filename, out temp)) { MessageBox.Show("Cancelled!!"); } else { xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlWorkBook.Close(true, Type.Missing, Type.Missing); xlApp.Quit(); MessageBox.Show("Done!!!!"); } }
public KestralDataManip(Excel.Worksheet sheet, Excel.Workbook workbook, KestrelDataAddInRibbon ribbon, bool dataOnSheet) { this.sheet = sheet; this.workbook = workbook; this.excelRibbon = ribbon; whm = new WindowHandlerManager(); liveList = new ListSerializableDataStruct(); Excel.Worksheet AutoCollectDataSheet = null; Excel.Sheets excel_sheets = workbook.Worksheets; AutoCollectDataSheet = excel_sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); AutoCollectDataSheet.Name = "Auto Collect"; InitializeComponent(); if (!dataOnSheet) readData.Enabled = false; }
private void ExportDataSetToExcel(DataSet ds) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. DataTableCollection collection = ds.Tables; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// Exports the specified items. /// </summary> /// <typeparam name="T">Type of the item to export.</typeparam> /// <param name="items">The items.</param> /// <param name="sheetName">Name of the sheet.</param> /// <exception cref="System.ArgumentNullException">items;items cannot be null or empty.</exception> /// <exception cref="System.ApplicationException">Unable to add new worksheet.</exception> public void ExportToSheet <T>(IEnumerable <T> items, String sheetName = null) where T : class { if (items == null || items.Count() == 0) { throw new ArgumentNullException("items", "items cannot be null or empty."); } // Excel always provides one empty sheet with a new workbook. Attempt to use this // sheet first before going to create a new one. Excel.Worksheet xlSheet = this.xlBook.ActiveSheet as Excel.Worksheet; if (xlSheet == null) { throw new ApplicationException("Unable to retrieve current worksheet."); } Excel.Range usedRange = xlSheet.UsedRange as Excel.Range; if (usedRange.Count > 1) { Excel.Sheets xlSheets = this.xlBook.Sheets as Excel.Sheets; if (xlSheets == null) { throw new ApplicationException("Unable to fetch sheets to add new worksheet."); } xlSheet = xlSheets.Add() as Excel.Worksheet; Marshal.ReleaseComObject(xlSheets); } if (xlSheet == null) { throw new ApplicationException("Unable to add new worksheet."); } if (!String.IsNullOrEmpty(sheetName)) { xlSheet.Name = sheetName; } PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); this.AddTitleToSheet(xlSheet, properties); this.AddItemsToSheet <T>(xlSheet, properties, items); this.AddFinalTouches(xlSheet); Marshal.ReleaseComObject(usedRange); Marshal.ReleaseComObject(xlSheet); }
/// <summary> /// Parses a string and converts data into a worksheet /// </summary> /// <param name="iWorksheetName">Worksheet to create</param> /// <param name="iDisplayText">Text data to be converted as a worksheet</param> /// <param name="iRowStart">Starting row to write the data</param> /// <param name="iColStart">Starting column to write the data</param> /// <param name="iRowDelim">Row delimiter for each cell block</param> /// <param name="iColDelim">Column delimiter for each cell block</param> public static void stringToWorksheet(string iWorksheetName, string iDisplayText, int iRowStart, int iColStart, string iRowDelim, string iColDelim) { Excel.Workbook aWorkbook = Globals.ThisAddIn.Application.Workbooks.get_Item(1); Excel.Sheets aCurrentSheets = aWorkbook.Sheets as Excel.Sheets; Excel.Worksheet aWorksheet; Excel.Range aRange; int aRowIndex = iRowStart; int aColIndex = iColStart; int aRowLength = 0; int aColLength = 0; string[] aRowArray = iDisplayText.Split(new string[] { iRowDelim }, System.StringSplitOptions.RemoveEmptyEntries); aRowLength = aRowArray.Length; if (aRowLength > 0) { // Count the columns string[] aColArray = aRowArray[0].Split(new string[] { iColDelim }, System.StringSplitOptions.RemoveEmptyEntries); aColLength = aColArray.Length; } // Create a 2 dimensional array with the string object[,] aWorksheetArray = new object[aRowLength, aColLength]; for (int aRow = 0; aRow < aRowLength; aRow++) { string aColText = aRowArray[aRow]; string[] aColArray = aColText.Split(new string[] { iColDelim }, System.StringSplitOptions.RemoveEmptyEntries); for (int aCol = 0; aCol < aColArray.Length; aCol++) { aWorksheetArray[aRow, aCol] = aColArray[aCol]; } } try { aWorksheet = aCurrentSheets.get_Item(iWorksheetName) as Excel.Worksheet; } catch (Exception) { // Worksheet does not exist, create a new one aWorksheet = (Excel.Worksheet)aCurrentSheets.Add(aCurrentSheets[1], Type.Missing, Type.Missing, Type.Missing); aWorksheet.Name = iWorksheetName; } aRange = aWorksheet.get_Range(aWorksheet.Cells[iRowStart, iColStart], aWorksheet.Cells[iRowStart, iColStart]); aRange = aRange.get_Resize(aRowLength, aColLength); aRange.set_Value(Missing.Value, aWorksheetArray); aRange.Columns.AutoFit(); }
private static void NotReleasingExcelComObjects() { string filename = @"C:\Temp\BucketList.xlsx"; Excel.Application application = new Excel.Application(); application.Visible = false; Excel.Workbook workbook = application.Workbooks.Add(); Excel.Sheets sheets = workbook.Sheets; Excel.Worksheet worksheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); worksheet.Range["A1"].Value = "Bucket List"; worksheet.Range["A2"].Value = "Visit New Zealand"; worksheet.Range["A1"].Value = "Visit Australia"; if (File.Exists(filename)) { File.Delete(filename); } workbook.SaveAs(filename); workbook.Close(); application.Quit(); }
public MockWorkbook() { // worksheet indices; watch out! the second index here is the NUMBER of elements, NOT the max value! var e = Enumerable.Range(1, 10); // new Excel instance app = new Excel.Application(); // create new workbook wb = app.Workbooks.Add(); // get a reference to the worksheet array // By default, workbooks have three blank worksheets. ws = wb.Worksheets; // add some worksheets foreach (int i in e) { ws.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); } }
//This method wirtes the sim data to the excel file. public void WriteToExcel() { int iSimRunNumber = 0; int linenumber = 0; // Start Excel and get Application object. oXL = new Excel.Application(); // Set some properties oXL.Visible = false; oXL.DisplayAlerts = false; // Get a new workbook. oWB = oXL.Workbooks.Add(Missing.Value); //Add a new sheets object. oXLSheets = oXL.Sheets as Excel.Sheets; foreach (DOE22SimFile simfile in DOESimFiles) { iSimRunNumber++; oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); oSheet.Name = "RUN-" + iSimRunNumber.ToString(); //oSheet.Name = Path.GetFileName(simfile.filepath); linenumber = 0; // Output BEPS to excel Sheet. oSheet.Cells[linenumber = 1, 1] = Path.GetFileName(simfile.filepath); linenumber++; oSheet.Cells[linenumber, 1] = "BEPS"; linenumber++; //print bpes report. PrintTableToExcel(linenumber, simfile.bepsTable, oSheet); linenumber = linenumber + simfile.bepsTable.Rows.Count + 1; linenumber++; oSheet.Cells[linenumber, 1] = "ES-D"; linenumber++; //Print es-d report. PrintTableToExcel(linenumber, simfile.esdTable, oSheet); // Resize the columns oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[simfile.bepsTable.Rows.Count, simfile.bepsTable.Columns.Count]); oRange.EntireColumn.AutoFit(); } //reset linenumber for All sheet. linenumber = 0; oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); oSheet.Name = "ALL"; foreach (DOE22SimFile simfile in DOESimFiles) { linenumber++; // Output Filename to excel Sheet. oSheet.Cells[linenumber, 1] = Path.GetFileName(simfile.filepath); linenumber++; if (bWriteBEPS == true) { // Output Filename to excel Sheet. oSheet.Cells[linenumber, 1] = "BEPS"; linenumber++; //print beps report. PrintTableToExcel(linenumber, simfile.bepsTable, oSheet); linenumber = linenumber + simfile.bepsTable.Rows.Count + 1; } //Print ES-D if (bWriteESD == true) { linenumber++; oSheet.Cells[linenumber, 1] = "ES-D"; linenumber++; //Print es-d report. PrintTableToExcel(linenumber, simfile.esdTable, oSheet); linenumber = linenumber + simfile.esdTable.Rows.Count + 1; } //Print Zone Annual Data if (bWriteZoneAnnualData == true) { linenumber++; oSheet.Cells[linenumber, 1] = "Zone Annual Data"; linenumber++; //Print Zone Annual Data report. PrintTableToExcel(linenumber, simfile.ZoneAnnualTable, oSheet); linenumber = linenumber + simfile.ZoneAnnualTable.Rows.Count + 1; } //Print System Annual Data if (bWriteSystemAnnualData == true) { linenumber++; oSheet.Cells[linenumber, 1] = "System Annual Data"; linenumber++; //Print Zone Annual Data report. PrintTableToExcel(linenumber, simfile.SystemAnnualTable, oSheet); linenumber = linenumber + simfile.SystemAnnualTable.Rows.Count + 1; } // Resize the columns oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[simfile.bepsTable.Rows.Count, simfile.bepsTable.Columns.Count]); oRange.EntireColumn.AutoFit(); } // Save the sheet and close oSheet = null; oRange = null; oWB.SaveAs(sFoldername + @"\test.xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Close(Missing.Value, Missing.Value, Missing.Value); oWB = null; // Clean up // NOTE: When in release mode, this does the trick GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); }
private void PrepareRecord() { objRecordExcel = new Excel.Application(); objRecordBooks = objRecordExcel.Workbooks; if (File.Exists(txtStorage.Text)) objRecordBook = objRecordBooks.Open(txtStorage.Text); else objRecordBook = objRecordBooks.Add(true); objRecordSheets = objRecordBook.Sheets; objRecordSheet1 = objRecordSheets[1]; // 收件箱 objRecordSheet2 = objRecordSheets[1]; // 已发短信 bool bSheet1Exists = false, bSheet2Exists = false; foreach (Excel.Worksheet objSheet in objRecordSheets) { if (objSheet.Name == "收件箱") { objRecordSheet1 = objSheet; bSheet1Exists = true; } else if (objSheet.Name == "已发短信") { objRecordSheet2 = objSheet; bSheet2Exists = true; } } if (!bSheet1Exists) { objRecordSheet1 = objRecordSheets.Add(); objRecordSheet1.Name = "收件箱"; objRecordSheet1.Cells[1, 1] = "发件人"; objRecordSheet1.Cells[1, 2] = "短信内容"; objRecordSheet1.Cells[1, 3] = "发送时间"; } if (!bSheet2Exists) { objRecordSheet2 = objRecordSheets.Add(); objRecordSheet2.Name = "已发短信"; objRecordSheet2.Cells[1, 1] = "收件人"; objRecordSheet2.Cells[1, 2] = "短信内容"; objRecordSheet2.Cells[1, 3] = "发送时间"; objRecordSheet2.Cells[1, 4] = "结果"; objRecordSheet2.Cells[1, 5] = "余额"; } }