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);
}
}
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] = "余额";
}
}
