/// <summary>
/// Metod so koj se vrsi polnenje na cel red vo tabelata so pole od objekti
/// i soodvetni vrednosti vo rangot od pocetna do krajna kolona
/// </summary>
/// <param name="values"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
public void FillRowData_Mine(object[] values, string startColumn, string endColumn)
{
_excelRange = _excelSheet.get_Range(startColumn, endColumn);
_excelRange.set_Value(_value, values);
this.AddRow(startColumn, endColumn);
//_excelRange.EntireColumn.AutoFit();
}
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.ColumnWidth = 12;
_range.Columns.AutoFit();
}
private void ExportExcelFile(List <string[]> data, ref Excel._Worksheet objSheet)
{
int num = 0;
object missingType = Type.Missing;
int colCnt = detailEquipment.GetColumnCount;
int rowCnt = data.Count;
string[] headers = detailEquipment.GetHeaderText();
string[] columns = new string[colCnt];
for (int c = 0; c < colCnt; c++)
{
num = c + 65;
columns[c] = Convert.ToString((char)num);
}
try
{
Excel.Range range;
range = objSheet.get_Range(columns[0] + "1:" + columns[colCnt - 1] + "1", Missing.Value);
range.Value2 = headers;
for (int i = 0; i < rowCnt; i++)
{
string strRange = columns[0] + (i + 2) + ":" + columns[colCnt - 1] + (i + 2);
range = objSheet.get_Range(strRange, Missing.Value);
range.Value2 = data[i];
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void CargarEmpleados(Excel._Worksheet oSheet)
{
CLS_EmpleadosHis sel = new CLS_EmpleadosHis();
sel.MtdSeleccionarEmpleadosHisCuadrilla();
if (sel.Exito && sel.Datos.Rows.Count > 0)
{
int Fila = 6;
for (int i = 0; i < sel.Datos.Rows.Count; i++)
{
oRng = oSheet.get_Range("A" + Fila.ToString(), "A" + Fila.ToString());
oRng.Merge();
oRng.Value2 = sel.Datos.Rows[i]["Nombre_Empleado"].ToString();
oRng.Font.FontStyle = "Calibri";
oRng.Font.Size = 11;
oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.Font.Bold = false;
oRng = oSheet.get_Range("B" + Fila.ToString(), "B" + Fila.ToString());
oRng.Merge();
oRng.Value2 = sel.Datos.Rows[i]["Dias_Trabajados"].ToString();
oRng.Font.FontStyle = "Calibri";
oRng.Font.Size = 11;
oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.Font.Bold = false;
oRng.NumberFormat = "#,##0";
Fila++;
}
}
}
public static void UWriteListViewToExcel(ListView LView, string way, string strTitle)
{
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
object m_objOpt = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbooks ExcelBooks = (Microsoft.Office.Interop.Excel.Workbooks)ExcelApp.Workbooks;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)(ExcelBooks.Add(m_objOpt));
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
ExcelSheet.Columns.EntireColumn.AutoFit();//列宽自适应
//设置标题
ExcelApp.Caption = strTitle;
ExcelSheet.Cells[1, 1] = strTitle;
Range rg3 = ExcelSheet.get_Range("A1", "E1");
rg3.MergeCells = true;
rg3.set_Value(Type.Missing, strTitle);
rg3.Font.Bold = true;
rg3.Font.Size = 18;
rg3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//写入列名
for (int i = 1; i <= LView.Columns.Count; i++)
{
ExcelSheet.Cells[2, i] = LView.Columns[i - 1].Text;
}
//写入内容
for (int i = 3; i < LView.Items.Count + 3; i++)
{
ExcelSheet.Cells[i, 1] = LView.Items[i - 3].Text;
for (int j = 2; j <= LView.Columns.Count; j++)
{
ExcelSheet.Cells[i, j] = LView.Items[i - 3].SubItems[j - 1].Text;
}
}
for (int i = 1; i < LView.Items.Count + 3; i++)
{
string startcell = string.Format("A{0}", i.ToString());
string endcell = string.Format("E{0}", i.ToString());
Range r = ExcelSheet.get_Range(startcell, endcell);
r.Font.Bold = true;
r.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
Range range = ExcelSheet.Columns;
range.Columns.AutoFit();
ExcelBook.SaveAs(way, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//显示Excel
//ExcelApp.Visible = true;
ExcelApp.Quit();
GC.Collect();
MessageBox.Show("导出数据成功!", "系统信息");
}
catch (SystemException e)
{
MessageBox.Show(e.ToString());
}
}
private void izvozpodatkovbutton_Click(object sender, EventArgs e)
{
oXL = new Excel.Application();
oWB = (Excel.Workbook)oXL.Workbooks.Add();
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] = "Ime Lekarne";
oSheet.Cells[1, 2] = "Telefon";
oSheet.Cells[1, 3] = "Delovni Čas";
oSheet.Cells[1, 4] = "Naslov";
oSheet.Cells[1, 5] = "Kraj";
oSheet.Cells[1, 6] = "Število Delavcev";
oSheet.get_Range("A1", "F1").Font.Bold = true;
oSheet.get_Range("A1", "F1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
polnjenjeexcel();
try
{
oWB.Application.ActiveWorkbook.SaveAs(@"C:\Users\Jon\Desktop\Excel\" + imeexceltext.Text + ".xlsx");
MessageBox.Show("Uspešno ustvarjena excel datoteka");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// HTML转换为Excel
/// </summary>
/// <param name="IsMergeCells"></param>
/// <param name="tableStr"></param>
public void HtmlToExcel(bool IsMergeCells, string tableStr)
{
string a1 = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title></title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\n<style>\n</style>\n</head>\n<body>\n";
string a2 = "\n</body>\n</html>";
tableStr = a1 + tableStr + a2;
if (IsMergeCells)
{
//合并单元格
//m_objRange = m_objSheet.get_Range("A1", "K4");
//m_objRange.Merge(System.Reflection.Missing.Value);
m_objRange = m_objSheet.get_Range("A1", "A1");
//m_objRange.
}
m_objRange.Select();
m_objRange.Cells[1, 1] = tableStr;
m_objRange.Activate();
m_objRange.Copy(Missing.Value);
//m_objSheet.get_Range("B2", "B2")
//.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract
//, false, false);
m_objRange.EntireColumn.AutoFit();
}
} //End Extract Spectrums
public ITargetBlock <string> Run()
{
if (SynchronizationContext.Current == null)
{
SynchronizationContext.SetSynchronizationContext(new WindowsFormsSynchronizationContext());
}
TransformBlock <string, DataTable> loadWafers = new TransformBlock <string, DataTable>(
w => {
return(GetWaferSpectrum(w, TEST_TYPE.INITIAL));
});
TransformBlock <DataTable, IEnumerable <Spectrum> > convert = new TransformBlock <DataTable, IEnumerable <Spectrum> >(
dt => {
return(ExtractSpectrum(dt));
});
ActionBlock <IEnumerable <Spectrum> > display = new ActionBlock <IEnumerable <Spectrum> >(list => {
List <Spectrum> tmp = list.ToList <Spectrum>();
Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationManual;
Excel.Application ExcelApp = ((Excel.Application)Globals.ThisAddIn.Application);
var wb = ExcelApp.Workbooks.Add();
ExcelApp.Visible = true;
Excel._Worksheet ws = wb.Sheets.Add();
ws.Name = "TestBlock";
for (int i = 0; i < tmp.Count; i += 2)
{
int count = tmp[i].Wl.Length;
object[] wl_obj = new object[count];
object[] int_obj = new object[count];
for (int x = 0; x < count; x++)
{
wl_obj[x] = tmp[i].Wl[x];
int_obj[x] = tmp[i].Intensity[x];
}
Excel.Range wl = ws.get_Range((Excel.Range)(ws.Cells[1, i]), (Excel.Range)(ws.Cells[count, i]));
Excel.Range intensity = ws.get_Range((Excel.Range)(ws.Cells[1, i]), (Excel.Range)(ws.Cells[count, i]));
wl.Value = tmp[i].Wl;
intensity.Value = tmp[i].Intensity;
}
Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationSemiautomatic;
},
// Specify a task scheduler from the current synchronization context
// so that the action runs on the UI thread.
new ExecutionDataflowBlockOptions {
TaskScheduler = TaskScheduler.FromCurrentSynchronizationContext()
});
loadWafers.LinkTo(convert);
convert.LinkTo(display);
//loadWafers.Post(wafer);
return(loadWafers);
}
private void FormatConsolidatedSheet(Excel._Workbook workbook, Excel._Worksheet consolidatedPivot)
{
Excel.Range oRng = consolidatedPivot.get_Range("A1", "F2");
oRng.Interior.Color = ColorTranslator.ToOle(Color.LightBlue);
oRng.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
oRng = consolidatedPivot.get_Range("A3", "F" + getLastUsedRow(consolidatedPivot));
oRng.Interior.Color = ColorTranslator.ToOle(Color.White);
}
public void wrtie_data(double[] M, int stroka, int stolbec, int kolvo_strok, int kolvo_stolbcov)
{
string str = cifra_to_bukva(stolbec) + stroka.ToString();
m_objRange = m_objSheet.get_Range(str, m_objOpt);
m_objRange = m_objRange.get_Resize(kolvo_strok, kolvo_stolbcov);
m_objRange.set_Value(m_objOpt, M);
}
void frm_FormClosed(object sender, System.Windows.Forms.FormClosedEventArgs e)
{
if (frm.DialogResult == System.Windows.Forms.DialogResult.OK)
{
Excel._Worksheet a = (Excel._Worksheet)Application.ActiveWorkbook.ActiveSheet;
if (frm.Status == "C")
{
try
{
a.get_Range(frm.Pos, type).ClearComments();
}
catch { }
a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else if (frm.Status == "L")
{
try
{
//DataTable dt = frm.DataReturn;
System.Data.DataTable dt = frm.DataReturn;
//Microsoft.Office.Interop.Excel.DataTable dtEx;
//Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range currentRange = _wsheet.get_Range(_address, Type.Missing);
for (int i = 0; i < dt.Columns.Count; i++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[currentRange.Row, i + currentRange.Column];
_range.Font.Bold = true;
_range.set_Value(Type.Missing, dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[i + currentRange.Row + 1, j + currentRange.Column];
_range.set_Value(Type.Missing, dt.Rows[i][j]);
}
}
//string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
//Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
//string des = _wpivotsheet.Name + "!R3C1";
//_wbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10);
}
catch (Exception ex) { BUS.CommonControl.AddLog("ErroLog", __documentDirectory + "\\Log", "[Addin] [" + DateTime.Now.ToString() + "] : " + ex.Message + "\n\t" + ex.Source + "\n\t" + ex.StackTrace); }
//a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else
{
a.get_Range(frm.Pos, type).set_Value(type, frm.TTFormular);
}
}
}
/// <summary>
/// fill the header columns for the range specified and make it bold if specified
/// </summary>
/// <param name="headers"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
protected void FillHeaderColumn(object[] headers, string startColumn, string endColumn)
{
_excelRange = _excelSheet.get_Range(startColumn, endColumn);
_excelRange.set_Value(_value, headers);
if (BoldHeaders == true)
{
this.BoldRow(startColumn, endColumn);
}
_excelRange.EntireColumn.AutoFit();
}
public void AutoFit()
{
range = objSheet.get_Range("A1", "Z2000");
objSheet.Columns.AutoFit();
range.Select();
range = objSheet.get_Range("A1", "A1");
range.Select();
}
public void PutHeading(string Heading, string SheetName)
{
//AddExcelRows("A1", 1, 3, "sajdhsajkdn");
_sheet.Cells[1, 1] = Heading;
_sheet.Name = SheetName;
var font = _sheet.get_Range("a1").Font;
font.Bold = true;
font.Size = 16;
font.ColorIndex = 1;
font.FontStyle = "Times New Roman";
}
private void izvozButton_Click(object sender, EventArgs e)
{
oXL = new Excel.Application();
oWB = (Excel.Workbook)oXL.Workbooks.Add();
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] = "Naslov";
oSheet.Cells[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.Cells[1, 2] = "Avtor";
oSheet.Cells[1, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.Cells[1, 3] = "Leto";
oSheet.Cells[1, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.Cells[1, 4] = "Oddelek";
oSheet.Cells[1, 4].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.Cells[1, 5] = "Založnik";
oSheet.Cells[1, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.Cells[1, 6] = "Status";
oSheet.Cells[1, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
oSheet.get_Range("A1", "F1").Font.Bold = true;
oSheet.get_Range("A1", "F1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.Columns[1].ColumnWidth = 60;
oSheet.Columns[2].ColumnWidth = 40;
oSheet.Columns[3].ColumnWidth = 7;
oSheet.Columns[4].ColumnWidth = 30;
oSheet.Columns[5].ColumnWidth = 42;
List <knjige> knigger = baza.izpisvsehknjig();
foreach (knjige x in knigger)
{
//worksheet.Cells[i + 2, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
oSheet.Cells[naprej, 1] = x.Naslov;
oSheet.Cells[naprej, 2] = x.Avtor;
oSheet.Cells[naprej, 3] = x.Leto;
oSheet.Cells[naprej, 4] = x.Section;
oSheet.Cells[naprej, 5] = x.Zalozba;
if (x.Status == 0)
{
oSheet.Cells[naprej, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
else
{
oSheet.Cells[naprej, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
naprej++;
}
oWB.Application.ActiveWorkbook.SaveAs(@"C:\Users\Ziga\Desktop\Excel\" + izvozIme.Text + ".xlsx");
}
/// <summary>
/// Geçerli çalışma sayfasındaki bir hücrenin değerini ayarlayın.
/// </summary>
/// <param name="cellIndex"></param>
/// <param name="value"></param>
public void SetCellValue(string cellIndex, object value)
{
if (m_Worksheet != null)
{
object cell1 = cellIndex;
Excel.Range range = m_Worksheet.get_Range(cell1, missing);
if (range != null)
{
range.Value2 = value;
}
}
}
private void AddRow(Excel._Worksheet sheet, Orders.itemsItem orderItem, OrderProducts.itemsItem orderProduct, int birim)
{
var searchRange = sheet.get_Range("L1", "L" + sheet.Rows.Count);
Excel.Range findRng = searchRange.Find(orderProduct.order_product_id,
Missing.Value,
Excel.XlFindLookIn.xlFormulas,
Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows,
Excel.XlSearchDirection.xlNext,
false,
false,
Missing.Value);
if (findRng != null)
{
logTxt.AppendText("\r\n");
logTxt.AppendText("Zaten kayıtlı.");
return;
}
Excel.Range line = (Excel.Range)sheet.Rows[2];
line.Insert();
Excel.Range last = (Excel.Range)sheet.Rows[2];
Excel.Range range = sheet.get_Range("A1", last);
Excel.Range R1 = sheet.get_Range("A3", "M3");
R1.Copy(Type.Missing);
Excel.Range xlRange = sheet.UsedRange;
var lastRow = last.Row;
Excel.Range R2 = sheet.get_Range("A" + lastRow.ToString(), "M" + lastRow.ToString());
R2.PasteSpecial(Excel.XlPasteType.xlPasteFormats,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationMultiply, false, false);
sheet.Cells[last.Row, 1] = orderItem.date_added;
sheet.Cells[last.Row, 2] = orderItem.order_id;
sheet.Cells[last.Row, 3] = orderItem.firstname;
sheet.Cells[last.Row, 4] = orderItem.lastname;
sheet.Cells[last.Row, 5] = orderProduct.option_name;
sheet.Cells[last.Row, 6] = orderProduct.name;
sheet.Cells[last.Row, 7] = orderProduct.quantity;
sheet.Cells[last.Row, 12] = orderProduct.order_product_id;
sheet.Cells[last.Row, 13] = orderItem.order_status_id;
sheet.Cells[last.Row, 14] = orderProduct.option_code;
sheet.Cells[last.Row, 8] = birim;
sheet.Cells[last.Row, 9] = birim * orderProduct.quantity;
sheet.Cells[last.Row, 18] = orderProduct.product_id.ToString();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
public void basliklar(int count)
{
for (int j = 1; j <= count; j++)
{
ws.get_Range("A1", "N1").Font.Bold = true;
ws.Cells[1, j] = "x" + j;
if (j == count)
{
ws.Cells[1, (j + 1)] = "Toplam fonk";
ws.Cells[1, (j + 2)] = "Akt. fonk";
}
}
}
private void DisplayChart(Excel._Worksheet oSheet, int iStationIndex)
{
Excel._Workbook oWB;
Excel.Range oResizeRange;
Excel._Chart oChart;
string[,] sArray = new string[4, 2];
sArray[0, 0] = "A";
sArray[0, 1] = "C";
sArray[1, 0] = "E";
sArray[1, 1] = "G";
sArray[2, 0] = "I";
sArray[2, 1] = "K";
sArray[3, 0] = "M";
sArray[3, 1] = "O";
int[] iColor = { 36, 35, 34, 8 };
//Excel.Series oSeries;
int iUsedRow = oSheet.get_Range(sArray[iStationIndex, 0] + "1048576").get_End(Excel.XlDirection.xlUp).Row;
string sRange = string.Format("{0}1:{1}", sArray[iStationIndex, 0], sArray[iStationIndex, 0] + iUsedRow.ToString());
oResizeRange = oSheet.get_Range(sRange, Missing.Value).get_Resize(Missing.Value, 3);
oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
oResizeRange.Interior.ColorIndex = iColor[iStationIndex];
string sRange1 = string.Format("{0}2:{1}", sArray[iStationIndex, 1], sArray[iStationIndex, 1] + iUsedRow.ToString());
oResizeRange = oSheet.get_Range(sRange1, Missing.Value);
oResizeRange.NumberFormat = "0.00%";
sRange = string.Format("{0}2:{1},{2}2:{3}", sArray[iStationIndex, 0], sArray[iStationIndex, 0] + iUsedRow.ToString(), sArray[iStationIndex, 1], sArray[iStationIndex, 1] + iUsedRow.ToString());
oWB = (Excel._Workbook)oSheet.Parent;
oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oResizeRange = oSheet.get_Range(sRange, Missing.Value);
//Use the ChartWizard to create a new chart from the selected data.
oChart.ChartWizard(oResizeRange, Excel.XlChartType.xlPieExploded, Missing.Value,
Excel.XlRowCol.xlColumns, sArray[iStationIndex, 0], sArray[iStationIndex, 1], true,
oSheet.Cells[1, sArray[iStationIndex, 0]], Missing.Value, Missing.Value, Missing.Value);
oChart.ApplyDataLabels();
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, oSheet.Name);
//Move the chart so as not to cover your data.
string sChartName = string.Format("Chart {0}", iChartNum.ToString());
oResizeRange = (Excel.Range)oSheet.Rows.get_Item(5, Missing.Value);
oSheet.Shapes.Item(sChartName).Top = (float)(double)oResizeRange.Top;
oResizeRange = (Excel.Range)oSheet.Columns.get_Item(iStationIndex * 4 + 1, Missing.Value);
oSheet.Shapes.Item(sChartName).Left = (float)(double)oResizeRange.Left;
oSheet.Shapes.Item(sChartName).Width = 250;
iChartNum++;
}
void BuildFramerateAnalysis(Excel._Workbook oWB, Word.Document oDoc, GetBucketValue grv, string uniqueName, string bookmarkName, float k_ignoreTime, int maxFPS)
{
const float k_fpsSize = 400.0f;
const float aspect = 3.0f / 4.0f;
Excel._Worksheet oSheet = AddWorksheet(oWB, uniqueName);
FillBuckets(oSheet, grv, 0, maxFPS, k_ignoreTime);
Excel._Chart oChart = MakeChart(oWB, oSheet.get_Range("C1", "D61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false);
SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time Below", 0.0f, -1.0f);
Word.InlineShape oShape = InsertChart(oChart, oDoc, uniqueName + "acc", bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f);
oChart = MakeChart(oWB, oSheet.get_Range("A1", "B61"), Excel.XlChartType.xlXYScatterSmoothNoMarkers, Excel.XlRowCol.xlColumns, 1, 0, false);
SetupAxes(oChart, "Frame Rate", 0.0f, maxFPS, "Percentage of Time", 0.0f, -1.0f);
oShape = InsertChart(oChart, oDoc, uniqueName, bookmarkName, k_fpsSize, k_fpsSize * aspect, 0.5f);
}
void insertTable(Excel._Worksheet oSheet, int startCol = 1, int startRow = 1)
{
Excel.Range oRng;
string[,] data = new string[resultTable.Rows.Count, resultTable.Columns.Count];
CultureInfo curCI = new CultureInfo(CultureInfo.CurrentCulture.Name);
curCI.NumberFormat.NumberDecimalSeparator = Properties.Settings.Default.NumberDecimalSeparator;
System.Threading.Thread.CurrentThread.CurrentCulture = curCI;
Excel.Range rngToCopy = oSheet.get_Range($"{Convert.ToString(startRow + 1 )}:{Convert.ToString(startRow + resultTable.Rows.Count - 1 )}");
// rngToCopy.Copy();
rngToCopy.Insert();
for (int r = 0; r < resultTable.Rows.Count; r++)
{
for (int c = 0; c < resultTable.Columns.Count; c++)
{
if (resultTable.Rows[r][c].GetType().Name == "DateTime")
{
data[r, c] = ((DateTime)resultTable.Rows[r][c]).ToString(Properties.Settings.Default.ShortDatePattern);
}
else
{
data[r, c] = resultTable.Rows[r][c].ToString();
}
}
}
oRng = oSheet.get_Range(xlsAdress(startCol, startRow),
xlsAdress(startCol - 1 + data.GetLength(1), startRow - 1 + data.GetLength(0)));
oRng.Cells.Value2 = data;
for (int c = 0; c < data.GetLength(1); c++)
{
dynamic cell = oSheet.get_Range(xlsAdress(startCol + c, startRow),
xlsAdress(startCol + c, startRow));
if (cell.DisplayFormat.NumberFormatLocal != "General")
{
oRng = oSheet.get_Range(xlsAdress(startCol + c, startRow),
xlsAdress(startCol + c, startRow - 1 + data.GetLength(0)));
oRng.Cells.TextToColumns();
}
}
// oRng.EntireColumn.AutoFit();
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo(CultureInfo.CurrentCulture.Name);
}
void CreateDBFile()
{
try
{
//Start Excel and get Application object.
excelapp = new Excel.Application();
excelapp.Visible = true;
//Get a new workbook.
excelworkbook = excelapp.Workbooks.Add();
excelsheet = excelworkbook.Worksheets.get_Item(1) as Excel.Worksheet;
//Add table headers going cell by cell.
excelsheet.Cells[1, 1] = "물고기 번호";
excelsheet.Cells[1, 2] = "물고기 과/류";
excelsheet.Cells[1, 3] = "물고기 이름";
excelsheet.Cells[1, 4] = "물고기 크기";
excelsheet.Cells[1, 5] = "물고기 먹성";
excelsheet.Cells[1, 6] = "물고기 성격";
excelsheet.Cells[1, 7] = "물고기 서식층";
excelsheet.Cells[1, 8] = "적정수질";
excelsheet.Cells[1, 9] = "적정온도";
excelsheet.Cells[1, 10] = "사육 난이도";
excelsheet.Cells[1, 11] = "번식 난이도";
excelsheet.Cells[1, 12] = "합사 난이도";
excelsheet.Cells[1, 13] = "물고기 부과설명";
//Format A1:M1 as bold, vertical alignment = center.
excelsheet.get_Range("A1", "M1").Font.Bold = true;
excelsheet.get_Range("A1", "M1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
finally
{
ReleaseObject(excelsheet);
ReleaseObject(excelworkbook);
ReleaseObject(excelapp);
}
}
public void Function_Click(IRibbonControl control_)
{
Xl.Application xlApp = (Xl.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
String fname = control_.Id;
fname = "=" + fname + "()";
Xl.Range rg = xlApp.ActiveCell;
String cellName = ExcelUtil.ExcelColumnIndexToName(rg.Column) + rg.Row;
Xl._Worksheet sheet = (Xl.Worksheet)xlApp.ActiveSheet;
Xl.Range range = sheet.get_Range(cellName, System.Type.Missing);
string previousFormula = range.FormulaR1C1.ToString();
range.Value2 = fname;
range.Select();
syncContext_ = SynchronizationContext.Current;
if (syncContext_ == null)
{
syncContext_ = new System.Windows.Forms.WindowsFormsSynchronizationContext();
}
FunctionWizardThread othread = new FunctionWizardThread(range, syncContext_);
Thread thread = new Thread(new ThreadStart(othread.functionargumentsPopup));
thread.Start();
}
public int RowMinMax()
{
// New method written so the end user doesn't go over the amount of cells already used.
// However, this may be scratched as it could be that the end user wishes to fill new boxes.
int rowCounter = 0;
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
try
{
// Again, unlike 0.0.12, this should always be in a try/catch.
oXL = new Excel.Application();
oWB = oXL.Workbooks.Open(excelDocument);
oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); // Finds the last used cell.
Excel.Range range = oSheet.get_Range("A1", last);
rowCounter = last.Row;
}
catch (Exception ex)
{
Debug.Write(ex);
MessageBox.Show("Please open a valid .xlsx document!");
}
finally
{
if (oWB != null)
{
oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
}
}
return(rowCounter); // Return this max value.
}
//Exportar para Excel
private void exportar(DataGridView tabela)
{
btnExportar.Text = "Exportando...";
btnExportar.Enabled = false;
var excelApp = new Excel.Application();
//excelApp.Workbooks.Add();
Excel._Worksheet workSheet = excelApp.ActiveSheet;
int row = 1;
foreach (DataGridViewRow linha in tabela.Rows)
{
for (int column = 1; column < tabela.ColumnCount; column++)
{
if (linha.Cells[column].Value != null)
{
workSheet.Cells[row, column] = linha.Cells[column].Value.ToString();
}
}
row++;
}
// Define a largura das colunas automaticamente
Excel.Range aRange = workSheet.get_Range("A1", "H" + tabela.RowCount);
Console.WriteLine("A1 " + " H" + tabela.RowCount);
aRange.Columns.AutoFit();
btnExportar.Text = "Exportar";
btnExportar.Enabled = true;
excelApp.Visible = true;
}
public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
public void createExcelSumCategories(Excel._Worksheet oSheet, int rowInHands, int rowInflow, int rowOutflow)
{
oSheet.Cells[4, 5] = "SUMMARY";
oSheet.Cells[5, 5] = "Inflow = ";
oSheet.Cells[6, 5] = "Outflow = ";
oSheet.Cells[7, 5] = "In hands = ";
if (rowInHands == 4) /* Gdy nie znajdzie zadnych maili w IN-HANDS */
{
oSheet.Cells[7, 6].Value = 0;
}
else
{
oSheet.Cells[7, 6].Formula = "=ROWS(C5:C" + rowInHands + ")";
}
if (rowInflow == 4) /* Gdy nie znajdzie zadnych maili w INFLOW */
{
oSheet.Cells[5, 6].Value = 0;
}
else
{
oSheet.Cells[5, 6].Formula = "=ROWS(A5:A" + rowInflow + ")";
}
if (rowOutflow == 4) /* Gdy nie znajdzie zadnych maili w OUTFLOW */
{
oSheet.Cells[6, 6].Value = 0;
}
else
{
oSheet.Cells[6, 6].Formula = "=ROWS(B5:B" + rowOutflow + ")";
}
oSheet.get_Range("E5", "E7").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
private void build_comp_graphs(Excel.Worksheet worksheet, Excel._Worksheet worksheet2, int irows, int orows)
{
for (int i = 1; i <= irows; i++)
{
string col1 = String.Format("A{0}", i);
string col2 = String.Format("B{0}", i);
string col3 = String.Format("C{0}", i);
worksheet.Range[col1].Formula = String.Format("='yearly_steps'!{0}", col1);
worksheet.Range[col2].Formula = String.Format("='yearly_steps'!{0}", col2);
if (i == 4)
{
worksheet.Range[col3].Formula = String.Format("={0}", col2);
}
else if (i > 4)
{
worksheet.Range[col3].Formula = String.Format("=(({0} - {1}) * {2})+{3}",
col1, String.Format("A{0}", i - 1), col2, String.Format("C{0}", i - 1));
}
}
Excel.ChartObjects charts = worksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400);
Excel.Chart chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet2.get_Range("B4", String.Format("B{0}", orows)));
series.XValues = worksheet2.get_Range("A4", String.Format("A{0}", orows));
series.Name = "Original Data";
series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("B4:B{0}", irows)]);
series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)];
series.Name = "Interpolated Data";
series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash;
chartObject = charts.Add(200, 525, 750, 400);
chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows)));
series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows));
series.Name = "Original Data";
series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", irows)]);
series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)];
series.Name = "Interpolated Data";
series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash;
}
// A9: Number of classroom teachers by teaching level of education, employment status, type of institution and sex
void sheetA9(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 1; //row offset
const int PUBLIC = 14;
const int PRIVATE = 17;
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A9"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED, SCHOOLTYPE, GENDER, sum(COUNT) as COUNT
from #TeacherBaseTable
group by ISCED, SCHOOLTYPE, GENDER",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced = rdr.GetString(0);
string schoolType = rdr.GetString(1);
string gender = rdr.GetString(2);
int count = rdr.GetInt32(3);
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", isced, gender, schoolType, count.ToString()));
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row = schoolType == "PUBLIC" ? PUBLIC : PRIVATE + rowOffset;
List <string> columns = new List <string>();
if (isced == "ISCED 24" || isced == "ISCED 34")
{
columns.Add("ISCED 24+34");
columns.Add(isced.Substring(0, 7));
}
else if (isced == "ISCED 25" || isced == "ISCED 35")
{
columns.Add("ISCED 25+35");
columns.Add(isced.Substring(0, 7));
}
else
{
columns.Add(isced);
}
foreach (string column in columns)
{
workSheet.Cells[row, getCol(column)] = workSheet.get_Range(helpers.GetCellAddress(getCol(column), row)).Value2 + count;
}
}
}
}
//virtual method
/// <summary>
/// Create an wookSheet of the Excel
/// </summary>
/// <param name="dataTable"></param>
/// <param name="sheetName"></param>
public virtual void CreatSheet(SQL.DataTable dataTable, string sheetName)
{
/*-------------------------*/
//create work sheet name
#region create a woork sheet by name
WorkSheet = (Excel._Worksheet)_Excel.Worksheets.Add();
WorkSheet.Name = sheetName;
Console.WriteLine("\n----------------------------------------");
Console.WriteLine("Create a new work sheet[{0}]", sheetName);
#endregion
/*-------------------------*/
//Add column names to excel sheet
#region add sheet header and set sheet width and font
string[] colNames = new string[dataTable.Columns.Count];
int col = 0;
//fetch column names from dtData
foreach (SQL.DataColumn dc in dataTable.Columns)
{
colNames[col++] = dc.ColumnName;
}
//last column for english alphabet
//char lastColumn = (char)(65 + dataTable.Columns.Count - 1);
string lastColumnName = GetExcelColumnName(dataTable.Columns.Count);
Console.WriteLine("lastColumn: " + lastColumnName);
//assign data to column headers
WorkSheet.get_Range("A1", lastColumnName + "1").Value2 = colNames;
//set width
WorkSheet.Columns.AutoFit();
//set columns format to text format
WorkSheet.Columns.NumberFormat = "@";
//set column headers' font to bold
//WorkSheet.get_Range("A1", lastColumnName + "1").Font.Bold = true;
#endregion
/*-------------------------*/
// Add DataRows data to Excel
#region New method for add DataRows data to Excel --- Slower
Console.WriteLine("Generating data for Sheet[{0}]...", sheetName);
string data = null;
for (int i = 0; i <= dataTable.Rows.Count - 1; i++)
{
for (int j = 0; j <= dataTable.Columns.Count - 1; j++)
{
data = dataTable.Rows[i].ItemArray[j].ToString();
WorkSheet.Cells[i + 2, j + 1] = data;
}
}
#endregion
Console.WriteLine("Sheet [{0}] created!", sheetName);
Console.WriteLine("----------------------------------------\n");
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
private void automateExcelSpreadsheet_Click(object sender, EventArgs e)
{
Excel.Range oRng;
try
{
oXL = new Excel.Application();
oXL.Visible = true;
oWB = oXL.Workbooks.Add(Missing.Value);
oWB.Title = "Test";
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = "Test";
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "E-mail";
oSheet.Cells[1, 4] = "Salary";
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").RowHeight = 37;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[1, 1] = "Brown";
saNames[2, 0] = "Sue";
saNames[2, 1] = "Thomas";
saNames[3, 0] = "Jane";
saNames[3, 1] = "Jones";
saNames[4, 0] = "Adams";
saNames[4, 1] = "Johnson";
oSheet.get_Range("A2", "B6").Value2 = saNames;
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \".\" & B2 & \"@\" & \"gmail.com\"";
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*10000";
oRng.NumberFormat = "$0.00";
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
DisplayQuarterlySales(oSheet);
oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception exception)
{
string errorMessage = "Error :";
errorMessage = string.Concat(errorMessage, exception.Message);
errorMessage = string.Concat(errorMessage, " Line: ");
errorMessage = string.Concat(errorMessage, exception.Source);
MessageBox.Show(errorMessage, "Error");
}
}
public List<AccountExcel> readExcel(string sExcelPath)
{
var lReturn = new List<AccountExcel>();
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2
{
if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "")
{
var adAccount = new AccountExcel();
adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value);
adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value);
adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value);
adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value);
adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value);
adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value);
adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value);
adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value);
adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value);
adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value);
adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value);
adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value);
adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value);
lReturn.Add(adAccount);
}
}
//Close the Excel Object
objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objBooks.Close();
objExcelApp.Quit();
Marshal.ReleaseComObject(objSheet);
Marshal.ReleaseComObject(objSheets);
Marshal.ReleaseComObject(objBooks);
Marshal.ReleaseComObject(objBook);
Marshal.ReleaseComObject(objExcelApp);
objSheet = null;
objSheets = null;
objBooks = null;
objBook = null;
objExcelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
return (lReturn);
}
