Exemple #1
0
        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();
        }
Exemple #2
0
        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!!!!");
        }
Exemple #3
0
        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);
 }
Exemple #7
0
        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");
        }
Exemple #8
0
        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");
        }
Exemple #10
0
        private void NewSheet()
        {
            int n = mWorksheets.Count;

            mWorksheets.Add(After: mWorksheets[n]);
            mActiveSheet = mWorksheets[n + 1];
        }
Exemple #11
0
        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);
        }
Exemple #12
0
        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);
            }
        }
Exemple #13
0
        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);
        }
Exemple #15
0
        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);
            }
        }
Exemple #16
0
 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();
 }
Exemple #17
0
        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;
        }
Exemple #18
0
        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);
 }
Exemple #20
0
 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);
 }
Exemple #21
0
        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);
        }
Exemple #22
0
 /// <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)
     {
     }
 }
Exemple #23
0
        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);
                }
            }
        }
Exemple #24
0
        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;
        }
Exemple #26
0
        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);
        }
Exemple #28
0
        /// <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();
        }
Exemple #29
0
        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();
        }
Exemple #30
0
            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);
                }
            }
            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);
                }
            }
Exemple #32
0
        //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();
        }
Exemple #33
0
 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] = "余额";
     }
 }