public Export(string embedded)
{
string tmp = String.Empty;
try
{
//получаем шаблон из прикладных ресурсов
Stream template = GetResourceFileStream(embedded);
//создаем временный файл
tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
//сохраняем шаблон во временный файл
using (var fileStream = File.Create(tmp))
{
template.CopyTo(fileStream);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Создаем приложение и открываем в нём временный файл
objApp = new Excel.Application();
objBook = objApp.Workbooks.Open(tmp);
objBook.Activate();
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public void CreateWorkbook()
{
var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
@"Templates\TemplateWorkBook.xlsx");
MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
//mWorkbook = mApplication.Workbooks.Add(Missing.Value);
mWorkbook = mApplication.Workbooks.Add(templateFile);
mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
//var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];
//Rename first and second worksheet, delete third worksheet
//mMeasurementDataWorksheet.Name = "Measurement Data";
//mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
//sheet3.Delete();
//Set measurement data worksheet as active worksheet
mMeasurementDataWorksheet.Activate();
//Display windows in splitview
mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);
MainForm.HeadersWritten = false;
MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
}
public static void WriteFormula(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, out bool success)
{
if (_excelDoc != null)
{
try
{
MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
_excelRge.FormulaLocal = wValue;
success = true;
return;
}
catch (Exception ex)
{
success = false;
Log.LogHelper.AddLog(@"异常36", ex.Message, true);
Log.LogHelper.AddLog(@"异常37", " " + ex.TargetSite.ToString(), true);
return;
}
}
else
{
success = false;
Log.LogHelper.AddLog(@"异常38", @"文件没有正常打开，无法读取数据", true);
return;
}
}
private void btnExportToExcel_Click(object sender, EventArgs e)
{
Excel._Application app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Setups-QuickCode";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("c:\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
/***********************功能函数****************************/
private void initialize()
{
form_main_program.CheckForIllegalCrossThreadCalls = false;
excel_app = new MSExcel.ApplicationClass(); //初始化
book = excel_app.Workbooks.Add(Missing.Value);
excel_app.Visible = false;
excel_app.DisplayAlerts = false;
if (!File.Exists("data.txt"))
{
TheFile = new FileStream("data.txt", FileMode.Create);
TheFile.Close();
}
//EXCEL COM口
try
{
sheet = (MSExcel._Worksheet)book.Worksheets.get_Item(1);
}
catch (System.Exception ex)
{
sheet = (MSExcel._Worksheet)book.Worksheets.Add(Missing.Value, book.Worksheets[0], 0, Missing.Value);
}
/*zed1曲线设置*/
zeggraph_init();
}
//获取投放周期、投放开始日期、结束日期
public string[] Excel_cyc(string path)
{
excel.Application ex = new excel.Application();
excel._Workbook tb = ex.Workbooks.Open(path);
excel.Worksheet wkst = tb.Worksheets[1];
try
{
string cyc = wkst.Cells[2, 1].value;
List <string> ls = new List <string>();
ls.Add(cyc.Substring(0, cyc.IndexOf(")") + 1));
//ls.Add(cycformat(cyc));
ls.Add(cyc.Substring(0, cyc.IndexOf("年") + 1));
ls.Add(cyc.Substring(cyc.IndexOf("年") + 1, cyc.IndexOf("月") - cyc.IndexOf("年")));
ls.Add(cyc.Substring(cyc.IndexOf("第"), cyc.IndexOf("周") - cyc.IndexOf("第") + 1));
ls.Add(BeginDate(cyc).ToString());
ls.Add(EndDate(cyc).ToString());
ls.Add(cycformat(cyc));
return(ls.ToArray());
}
catch (Exception dsex)
{
MessageBox.Show(dsex.Message);
string[] err = { "err" };
return(err);
}
finally
{
wkst = null;
tb.Close();
tb = null;
ex.Quit();
ex = null;
}
}
/**
* if previous logs exist we open it and update it
**/
public bool OpenWorkBook()
{
if (!File.Exists(m_logName))
{
return false;
}
try
{
m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Open(m_logName));
m_Sheet = (Excel._Worksheet)m_WorkBook.ActiveSheet;
}
catch (Exception e)
{
throw new LoggerException("Cannot open log file: " + e.Message);
}
if (m_Sheet == null)
{
return false;
}
m_columnsMap = new Dictionary<string, int>();
string projectName;
int lastRow = m_Sheet.UsedRange.Rows.Count;
for (int column = 2; column < m_Sheet.UsedRange.Columns.Count + 1; column++)
{
projectName = GetStringFromCell(1, column);
m_columnsMap.Add(projectName, column);
}
return true;
}
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());
}
}
public void PrintWskazniki(List <Wskazniki> listaWskaznikow, string nazwaSpolki)
{
oWB = (Excel._Workbook)(oXL.Workbooks.Open("E:\\raporty\\wyniki.xlsx"));
oSheet = (Excel._Worksheet)oWB.Sheets.Add();
oSheet.Name = nazwaSpolki;
//oRng = oSheet.UsedRange;
oSheet.Activate();
// worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();
for (int j = 0; j < 5; j++)
{
int startRok = 2010;
oSheet.Cells[1, j + 4] = (startRok + j).ToString();
}
for (int i = 0; i < listaWskaznikow.Count(); i++)
{
oSheet.Cells[i + 2, 1] = listaWskaznikow[i].category;
oSheet.Cells[i + 2, 2] = listaWskaznikow[i].name;
oSheet.Cells[i + 2, 3] = listaWskaznikow[i].wzor;
for (int j = 0; j < 5; j++)
{
oSheet.Cells[i + 2, j + 4] = listaWskaznikow[i].value[j];
}
}
oWB.Save();
}
public static void WriteValue(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, string numberFormat, out bool success)
{
if (_excelDoc != null)
{
try
{
bool checkSta = true;
MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex];
_excelSht.Cells[position.RowIndex, position.ColumnIndex] = wValue;
if (!string.IsNullOrWhiteSpace(numberFormat))
{
MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
_excelRge.NumberFormatLocal = numberFormat;
}
success = checkSta;
return;
}
catch (Exception ex)
{
success = false;
Log.LogHelper.AddLog(@"异常33", ex.Message, true);
Log.LogHelper.AddLog(@"异常34", " " + ex.TargetSite.ToString(), true);
return;
}
}
else
{
success = false;
Log.LogHelper.AddLog(@"异常35", @"文件没有正常打开，无法读取数据", true);
return;
}
}
/// <summary>
/// Closes Excel Application and clears Excel Objects used
/// </summary>
public static void Close()
{
UsingExcel = false;
if (oXL != null)
{
Marshal.ReleaseComObject(oChartRSSIs);
Marshal.ReleaseComObject(oChartTags);
Marshal.ReleaseComObject(oSheetCharts);
Marshal.ReleaseComObject(oSheetDataRSSIs);
Marshal.ReleaseComObject(oSheetDataTags);
Marshal.ReleaseComObject(oRng);
if (oWB != null)
{
oWB.Close(false, null, null); // closes workbook
}
Marshal.ReleaseComObject(oWB);
oWB = null;
oXL.Quit(); // exit excel application
Marshal.ReleaseComObject(oXL);
oXL = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void StartUp()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
}
public static void exportAsXLS(string path, string filename)
{
object miss = Type.Missing;
XLS app = new Excel.Application();
app.DisplayAlerts = false;
app.Visible = false;
Workbook wb = app.Workbooks.Add(miss);
Worksheet ws = (Worksheet)wb.Sheets[1];
//ws = wb.Sheets[0];
//ws = wb.Sheets["Sheet1"];
ws = wb.ActiveSheet;
ws.Name = "Detail";
/*
* for (int i = 0; i < 5; i++)
* {
* for (int j = 0; j < 3; j++)
* {
* ws.Cells[i, j] = "i = " + i.ToString() + "j = " + j.ToString();
* }
* }
*/
wb.SaveAs(path + filename);
wb.Close();
//wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive);
app.Quit();
}
/// <summary>
/// 将DataSet写入excel中，dataset.table的表名作为sheet名，dataset.table的列标题作为每一个sheet的首行
/// </summary>
/// <param name="fileExcel"></param>
/// <param name="dsInfo"></param>
public static void WriteExcel(string fileExcel, DataSet dsInfo)
{
DataTable dt = new DataTable();
excel = new Excel.Application();
excel.Visible = false; //不显示excel前台界面
Excel._Workbook book = null; //定义工作表
Excel._Worksheet sheet = null; //定义worksheet
//Excel.Range range = null;//定义range
//添加内容
try
{
book = excel.Workbooks.Open(fileExcel, 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);
}
catch (Exception ex)
{
Logs.WriteLog("error", ex.Message);
throw;
}
finally
{
book.Close();
//excel.Quit();//好像无法结束进程
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
Process p = Process.GetProcessById(k);
p.Kill();
}
}
private void checkFiles(String folderPath)
{
this.Visible = false;
if (System.IO.Directory.Exists(folderPath))
{
excelApp = new Excel.Application();
if (EXCEL_PATH.Exists)
{
workBook = excelApp.Workbooks.Open(EXCEL_PATH.FullName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
workBook = excelApp.Workbooks.Add("");
}
System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(folderPath);
taskCount = di.GetFiles().Length;
Console.Write("Started scanning {0}files. 0/{0}", taskCount);
foreach (var file in di.GetFiles())
{
System.IO.FileInfo fi = new System.IO.FileInfo(file.FullName);
getFileReport(fi);
}
}
}
private void Tool_Click(object sender, RoutedEventArgs e)
{
MSWord.Application EXCL1 = new MSWord.Application(); //新建一个应用程序EXC1
EXCL1.Visible = true; //设置EXC1打开后可见
MSWord.Workbooks wbs = EXCL1.Workbooks;
MSWord._Workbook wb = wbs.Add(GlobalData.gFileName); //打开EXCEL
MSWord._Worksheet mySheet;
mySheet = wb.Sheets[wantUseSheetIndex(GlobalData.gMySheetName, wb)]; //找到我要操作的sheet
mySheet.Activate();
//tiaoshi.Text = excelElementRead(mySheet, 1, 5).Interior.Color.ToString();
System.Data.DataTable dt = readNonredunOneColumn(mySheet, GlobalData.gWantUseColumn);
MSWord._Worksheet testPointSheet = wb.Sheets[wantUseSheetIndex(GlobalData.gTestPointSheetName, wb)];
testPointSheet.Activate();
excelElementWrite(testPointSheet, 1, 1, "测试点序号");
excelElementWrite(testPointSheet, 1, 2, "测试点");
for (int i = 0; i < dt.Rows.Count; i++)
{
excelElementWrite(testPointSheet, i + 2, 1, dt.Rows[i][0].ToString());
excelElementWrite(testPointSheet, i + 2, 2, dt.Rows[i][1].ToString());
}
writeTestPointCount(mySheet, GlobalData.gTestPointRange, GlobalData.gWantUseColumn, dt);
wb.Save();
}
public ExcelHandlerModel2()
{
SummaryTableTest.Clear();
SummaryTableTest.Columns.Add("Файл");
SummaryTableTest.Columns.Add("Статус");
SummaryTableTest.Columns.Add("Количество контрактов", System.Type.GetType("System.Int32"));
app = new Excel.Application();
workbook = app.Workbooks.Add(Type.Missing);
worksheet = workbook.ActiveSheet;
table = worksheet.ListObjects.Add();
table.Name = "Результат";
Excel.Range header = table.HeaderRowRange;
header[1, 1].Value = "ОСВ";
header[1, 2].Value = "КФО";
header[1, 3].Value = "Контрагент";
header[1, 4].Value = "Договор";
header[1, 5].Value = "Дебет";
header[1, 6].Value = "Кредит";
table.ListColumns.Item[5].Range.NumberFormatLocal = @"# ##0,00";
table.ListColumns.Item[6].Range.NumberFormatLocal = @"# ##0,00";
//table.ListColumns.Item[5].Range.NumberFormat = @"@";
}
void printLable(bool flag)
{
string path = Directory.GetCurrentDirectory();
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
//File.Copy(@"../../xls/Extrusion/吹膜标签.xlsx", path + @"/label.xlsx", true);
Microsoft.Office.Interop.Excel._Workbook wb = null;
if (flag)
{
wb = oXL.Workbooks.Open(path + @"/../../xls/CSBag/标签 CS 制袋 内标签.xlsx");
}
else
{
wb = oXL.Workbooks.Open(path + @"/../../xls/CSBag/标签 CS 制袋 外包标签.xlsx");
}
_Worksheet my = wb.Worksheets[wb.Worksheets.Count];
;
my.Select();
my.Cells[1, 2].Value = tc产品名称.Text;
my.Cells[2, 2].Value = tc产品编码.Text;
my.Cells[3, 2].Value = tc产品规格.Text;
my.Cells[4, 2].Value = tc产品批号.Text;
my.Cells[5, 2].Value = dc生产日期.Value.ToString("yyyy/MM/dd");
my.Cells[6, 2].Value = dc有效期至.Value.ToString("yyyy/MM");
my.Cells[7, 2].Value = tc数量.Text;
my.Cells[8, 2].Value = tc包装序号.Text;
my.Cells[9, 2].Value = tc毛重.Text;
my.Cells[10, 2].Value = tc箱体规格.Text;
my.Cells[1, 5].Value = teName.Text;
my.Cells[2, 5].Value = teCode.Text;
my.Cells[3, 5].Value = teSize.Text;
my.Cells[4, 5].Value = teBatch.Text;
my.Cells[5, 5].Value = deMfg.Value.ToString("yyyy/MM/dd");
my.Cells[6, 5].Value = deExpiry.Value.ToString("yyyy/MM");
my.Cells[7, 5].Value = teQuantity.Text;
my.Cells[8, 5].Value = tePack.Text;
my.Cells[9, 5].Value = tegross.Text;
my.Cells[10, 5].Value = teCarton.Text;
my = wb.Worksheets[c标签模板.SelectedIndex + 1];
my.Select();
oXL.Visible = true;
//try
//{
// my.PrintOut();
//}
//catch
//{
//}
//wb.Close(false);
//oXL.Quit();
//Marshal.ReleaseComObject(wb);
//Marshal.ReleaseComObject(oXL);
//wb = null;
//oXL = null;
}
public Sprawozdanie ReadRaport(string path)
{
string[,] dane = new string[30, 300];
Sprawozdanie raportSpolkiNotoria = new Sprawozdanie();
oWB = (Excel._Workbook)(oXL.Workbooks.Open(path));
//oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet = (Excel._Worksheet)oWB.Worksheets[6];
oRng = oSheet.UsedRange;
for (int row = 1; row < 231; row++)
{
Pozycja node = new Pozycja();
node.name = (string)(oRng.Cells[row, 2] as Excel.Range).Value2;
node.pos = row;
for (int col = 17; col < 22; col++) // w 17 kolumnie mamy 2010 rok
{
try
{
node.value[col - 17] = (int)(oRng.Cells[row, col] as Excel.Range).Value2;
}
catch { }
}
raportSpolkiNotoria.dane[row] = node;
}
oWB.Close();
return(raportSpolkiNotoria);
}
public void CreateWorkbook()
{
var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
@"Templates\TemplateWorkBook.xlsx");
MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
//mWorkbook = mApplication.Workbooks.Add(Missing.Value);
mWorkbook = mApplication.Workbooks.Add(templateFile);
mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
//var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];
//Rename first and second worksheet, delete third worksheet
//mMeasurementDataWorksheet.Name = "Measurement Data";
//mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
//sheet3.Delete();
//Set measurement data worksheet as active worksheet
mMeasurementDataWorksheet.Activate();
//Display windows in splitview
mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);
MainForm.HeadersWritten = false;
MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
}
private void btnExportar_Click(object sender, EventArgs e)
{
if (dgvMaterias.Rows.Count == 0)
{
MessageBox.Show("Sin datos por EXPORTAR", "Aviso", MessageBoxButtons.OK);
}
else
{
Excel._Application app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Horarios-Asignados";
for (int i = 1; i < dgvMaterias.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvMaterias.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgvMaterias.Rows.Count; i++)
{
for (int j = 0; j < dgvMaterias.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvMaterias.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("C:\\Users\\AbelFH\\Desktop\\Horarios-Asignados.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
MessageBox.Show("EXCEL generado exitosamente", "Aviso", MessageBoxButtons.OK);
}
}
/**//// <summary>
/// 打开没有模板的操作。
/// </summary>
public void Open()
{
this.Open(String.Empty);
}
/**//// <summary>
/// 功能：实现Excel应用程序的打开
/// </summary>
/// <param name="TemplateFilePath">模板文件物理路径</param>
public void Open(string TemplateFilePath)
{
//打开对象
m_objExcel = new Excel.Application();
m_objExcel.Visible = false;
m_objExcel.DisplayAlerts = false;
m_objExcel.ScreenUpdating = false; //停止更新屏幕，加快速度
//if (m_objExcel.Version != "11.0")
//{
// // MessageBox.Show("您的 Excel 版本不是 11.0 （Office 2003），操作可能会出现问题。");
// m_objExcel.Quit();
// return;
//}
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
if (TemplateFilePath.Equals(String.Empty))
{
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
}
else
{
m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objExcel.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
}
protected void ClearObject()
{
if (this.xlWorkSheet != null)
{
ReleaseObj.Marshal.ReleaseComObject(xlWorkSheet);
this.IsWorkSheetOpened = false;
this.xlWorkSheet = null;
}
if (this.xlWorkBook != null)
{
if (this.IsWorkBookOpened)
{
xlWorkBook.Close(false, misValue, misValue);
this.IsWorkBookOpened = false;
}
ReleaseObj.Marshal.ReleaseComObject(xlWorkBook);
this.xlWorkBook = null;
}
if (this.xlApp != null)
{
if (this.IsAppOpened)
{
xlApp.Quit();
this.IsAppOpened = false;
}
ReleaseObj.Marshal.ReleaseComObject(xlApp);
this.xlApp = null;
}
this.ClearnGarbage();
}
/// <summary>
/// Crea objeto Excel
/// </summary>
/// <param name="workSheetName">Nombre de hoja</param>
/// <param name="isVisible">True = es visible para el usuario la creación del documento</param>
public void CreateExcelObject(string workSheetName, bool isVisible)
{
try
{
oApp = new oExcel.Application();
oApp.Visible = isVisible;
try
{
oWorkbook = (oExcel._Workbook)(oApp.Workbooks.Add(Missing.Value));
}
catch
{
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("en-US");
oWorkbook = (oExcel._Workbook)(oApp.Workbooks.Add(Missing.Value));
}
oWorksheet = (oExcel._Worksheet)oWorkbook.ActiveSheet;
oApp.DisplayAlerts = false;
oWorksheet.Name = workSheetName;
IsObjectCreated = true;
}
catch
{
IsObjectCreated = false;
}
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return(sheetName);
}
private void btnExcel_Click(object sender, EventArgs e)
{
//open excel and transfer data from datagridview
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet
worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from Ticket Manager";
for (int i = 1; i < dgvClosedTickets.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvClosedTickets.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgvClosedTickets.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvClosedTickets.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvClosedTickets.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("c:\\ClosedTickets.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
private void ExportToExcel()
{
Excel._Application excel = new Excel.Application();
Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
barExcel.Value = 0;
for (int b = 0; b < 100; b++)
{
barExcel.Value += 1;
}
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Control de Horas";
int cellRowIndex = 1;
int cellColumnIndex = 1;
for (int i = -1; i < dgvHoras.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvHoras.Columns.Count; j++)
{
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvHoras.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvHoras.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 1;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Descarga Completa.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void Dispose()
{
if (Mws_Sheet != null)
{
Marshal.ReleaseComObject(Mws_Sheet);
Mws_Sheet = null;
}
if (Mwb_Book != null)
{
Mwb_Book.Close(false, null, null);
Marshal.ReleaseComObject(Mwb_Book);
Mwb_Book = null;
}
if (Mxl_Excel != null)
{
Mxl_Excel.Workbooks.Close();
Mxl_Excel.Quit();
Marshal.ReleaseComObject(Mxl_Excel);
Mxl_Excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
public static Hashtable GetMacros(Excel._Workbook wDestination)
{
Hashtable pak = new Hashtable();
VBA.VBProject prj;
VBA.CodeModule code;
string composedFile;
prj = wDestination.VBProject;
foreach (VBA.VBComponent comp in prj.VBComponents)
{
if (comp.Type == VBA.vbext_ComponentType.vbext_ct_StdModule)
{
code = comp.CodeModule;
composedFile = comp.Name + Environment.NewLine;
for (int i = 0; i < code.CountOfLines; i++)
{
composedFile +=
code.get_Lines(i + 1, 1) + Environment.NewLine;
}
pak.Add(comp.Name, composedFile);
}
}
return(pak);
}
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
}
}
}
//Excel.Range wRange;
public DataExcel()
{
// 開啟一個新的應用程式
excelApp = new Excel.Application
{
// 讓Excel文件可見
//Visible = true,
// 停用警告訊息
DisplayAlerts = false
};
// 加入新的活頁簿
excelApp.Workbooks.Add(Type.Missing);
// 引用第一個活頁簿
wBook = excelApp.Workbooks[1];
// 設定活頁簿焦點
wBook.Activate();
// 引用第一個工作表
wSheet = (Excel._Worksheet)wBook.Worksheets[1];
// 命名工作表的名稱
wSheet.Name = "result";
// 設定工作表焦點
wSheet.Activate();
}
private void button2_Click_3(object sender, RibbonControlEventArgs e)
{
Microsoft.Office.Interop.Excel._Workbook wbook = Globals.ThisAddIn.Application.ActiveWorkbook; //获取激活的工作簿
// group1.Label = "当前共有 "+wbook.Sheets.Count.ToString()+" 张表\r\n自动引导至第一张表，表名为："+wbook.Sheets[1].Name;//获取第一个工作表;
// Microsoft.Office.Interop.Excel.Worksheet newWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
// newWorksheet.Name = "Sheet1";
Worksheet worksheet = wbook.Worksheets[1];//获取名为sheet1的工作表
// Worksheet worksheet = wbook.Worksheets[1];//获取名为sheet1的工作表
string tag = worksheet.get_Range("A1", Missing.Value).Value2 + "###";
if (tag.Length <= 0 || tag.IndexOf("日计表") == -1)
{
MessageBox.Show("未识别到有效数据", "错误");
return;
}
int num = worksheet.Range["A1:H" + worksheet.UsedRange.Rows.Count].Find("100103000000").Row; //第一次出现空单元格的行数
string str1 = worksheet.get_Range("E" + num, Missing.Value).Value2;
string str2 = worksheet.get_Range("F" + num, Missing.Value).Value2;
string str3 = worksheet.get_Range("G" + num, Missing.Value).Value2;
int num1 = worksheet.Range["A1:H" + worksheet.UsedRange.Rows.Count].Find("100102000000").Row; //第一次出现空单元格的行数
string str11 = worksheet.get_Range("E" + num1, Missing.Value).Value2;
string str21 = worksheet.get_Range("F" + num1, Missing.Value).Value2;
string str31 = worksheet.get_Range("G" + num1, Missing.Value).Value2;
double sum = double.Parse(str1) + double.Parse(str11);
double sum1 = double.Parse(str2) + double.Parse(str21);
double sum2 = double.Parse(str3) + double.Parse(str31);
// MessageBox.Show(sum.ToString());
MessageBox.Show(" 现金收付\r\n----------------------------\r\n 收入：" + (sum).ToString() + "\r\n 付出：" + (sum1).ToString() + "\r\n 余额：" + (sum2).ToString());
}
public static void Test()
{
// 创建一个excel应用
MSExcel._Application app = new MSExcel.Application();
// 打开一个excel文档
MSExcel.Workbooks wbks = app.Workbooks;
MSExcel._Workbook _wbk = wbks.Add(DEFAULT_CONTEMP_PATH + "statistic.xls");
// 获取到excel中的一个sheet
MSExcel.Sheets shs = _wbk.Sheets;
MSExcel._Worksheet _wsh = (MSExcel._Worksheet)shs.get_Item(1);
//Object Missing = System.Reflection.Missing.Value;
String strValue = ((MSExcel.Range)_wsh.Cells[2, 1]).Text;
Console.WriteLine(strValue);
// 屏蔽掉系统跳出的Alert
app.AlertBeforeOverwriting = false;
//保存到指定目录
// _wbk.SaveAs("./1.xls", 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);
_wbk.SaveAs("./1.xls", 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);
}
private void bt_Exp_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wrkbk = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet wrksheet = null;
app.Visible = true;
wrksheet = wrkbk.ActiveSheet;
for (int i = 1; i < personneDataGridView.Columns.Count + 1; i++)
{
wrksheet.Cells[1, i + 1] = personneDataGridView.Columns[i - 1].HeaderText;
}
for (int i = 0; i < personneDataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < personneDataGridView.Columns.Count; j++)
{
if (personneDataGridView.Rows[i].Cells[j].Value != null)
{
wrksheet.Cells[i + 2, j + 2] = personneDataGridView.Rows[i].Cells[j].Value.ToString();
}
else
{
wrksheet.Cells[i + 2, j + 21] = "";
}
}
}
for (int i = 0; i < personneDataGridView.Rows.Count; i++)
{
wrksheet.Cells[i + 2, 1] = personneDataGridView.Rows[i].HeaderCell.Value;
}
}
public override void Dispose()
{
if (_workbook != null)
{
Marshal.ReleaseComObject(_workbook);
_workbook = null;
}
}
public ExcelManipulation()
{
_excelApp = new Excel.Application();
_flagexcelapp = 1;
_excelApp.Visible = true;
//Создаём новую книгу
_workBook = (Excel._Workbook)(_excelApp.Workbooks.Add(Missing.Value));
_workSheet = (Excel._Worksheet)_workBook.ActiveSheet;
}
public Export()
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public void generatExcelNoSubmit(string f)
{
string file_Name = f;
m_oExcelApp = new Excel.Application();
m_oExcelApp.Visible = false;
m_oExcelApp.UserControl = false;
m_oSheet = null;
excelRange = null;
try
{
m_oBook = m_oExcelApp.Workbooks.Add(missing);
Excel.Sheets sheets = m_oBook.Worksheets;
//Add new 4 Sheet
//sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
//Product Sheet [Sheet1]
m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
m_oSheet.Activate();
m_oSheet.Name = "รายการเสนอซื้อ";
SetData_to_SheetNoSubmit();
string template = Application.StartupPath;
string strRunReport = file_Name;
//string strPass = "******"; password
m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
m_oExcelApp.Visible = true;
}
catch (interop.COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
if (m_oExcelApp == null)
{
m_oExcelApp.Quit();
m_oExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
public ExcelWorksheet()
{
excelApp.Visible = true;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelWorkBook = excelApp.ActiveWorkbook;
if (excelWorkBook == null)
excelWorkBook = (Excel._Workbook) excelApp.Workbooks.Add(Missing.Value);
excelSheet = (Excel._Worksheet) excelWorkBook.ActiveSheet;
excelSheet.Name = "SpaceClaim";
}
public Workbook( Excel._Workbook oWB)
{
this.oWB = oWB;
this.workbookSheets = oWB.Sheets;
foreach (Excel._Worksheet openedWorkSheet in workbookSheets)
{
sheetCounter += 1;
Sheet openedSheet = new Sheet(openedWorkSheet);
setIndex(openedSheet);
Sheets.Add(openedSheet);
sheetDict[openedSheet] = openedWorkSheet;
}
this.ActiveSheet = Sheets[0];
}
//Method to close excel connection
private void ExcelClose()
{
if (xlApp != null) {
try {
xlWorkBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
xlWorkBook = null;
} catch (Exception ex) {
xlApp = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
} finally {
GC.Collect();
}
}
}
/**
* if no log file exists this method creates new spreadsheet
**/
private bool CreateWorkBook()
{
m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Add(Missing.Value));
m_Sheet = (Excel._Worksheet) m_WorkBook.ActiveSheet;
if (m_WorkBook == null || m_Sheet == null)
{
return false;
}
m_Sheet.Cells[1, 1] = "Date";
m_columnsMap = new Dictionary<string, int>();
foreach (KeyValuePair<string, ProjectData> entry in m_currProjectWarnings)
{
AddNewProjectColumn(entry.Key);
}
//addColumn("Total");
ApplyStyle();
return true;
}
private void tmrData_Tick(object sender, EventArgs e)
{
int i;
uint recLength;
recLength = 25;
// Get the Live data, to show in gauge
txBuf[0] = (byte)CMD.GET_LIVE_DATA;
txBuf[1] = 2;
//USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
string port = cbxPorts.SelectedItem.ToString();
if (port == "")
return;
System.IO.Ports.SerialPort myPort = new System.IO.Ports.SerialPort(port);
if (myPort.IsOpen == false) //if not open, open the port
myPort.Open();
//do your work here
if (myPort.IsOpen == false) //if not open, open the port
return;
String data;
data = myPort.ReadLine();
myPort.Close();
string [] dht=data.Split(',');
//s.dataPtr = rxBuf[18];
//i = 19;
//convertData(ref currData, ref i);
//gTemp.Value = currData.temp;
//gPressure.Value = currData.pressure;
//gHumid.Value = currData.humidity;
if (dht.Length != 3)
return;
gHumid.Value = float.Parse(dht[0]);
gTemp.Value = float.Parse(dht[1]);
gPressure.Value = int.Parse(dht[2]);
if (stt == 0)
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
}
//Add table headers going cell by cell.
oSheet.Cells[stt + 1, 1] = gTemp.Value.ToString();
oSheet.Cells[stt + 1, 2] = gPressure.Value.ToString();
oSheet.Cells[stt + 1, 3] = gHumid.Value.ToString();
//oSheet.Cells[1, 4] = "Salary";
DateTime timeNow = DateTime.Now;
w[stt].humidity = gHumid.Value;
w[stt].temp = gTemp.Value;
w[stt].pressure = gPressure.Value;
w[stt++].time = timeNow;
if (stt == 999)
stt = 0;
Redraw(0);
goLive = false;
if (goLive) // Only load data to w if graph shows live data from PIC
{
recLength = 64;
txBuf[0] = (byte)CMD.GET_FIRST_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData(10);
for (i = 0; i < 5; i++)
{
txBuf[0] = (byte)CMD.GET_NEXT_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData(10);
}
txBuf[0] = (byte)CMD.GET_CURR_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData((int)s.dataPtr);
}
}
public void CloseBook()
{
object strFilePath = strOutputPath + "\\Data_all_summary";
object oMissing = System.Reflection.Missing.Value;
object xfFormat = Excel.XlFileFormat.xlXMLSpreadsheet;
oWB.SaveAs(strFilePath,
Excel.XlFileFormat.xlWorkbookDefault, oMissing, oMissing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
oMissing, oMissing, oMissing, oMissing, oMissing);
oWB.Close(false, oMissing, oMissing);
oWB = null;
oXL.Quit();
oXL = null;
}
// Event Handler for the middle browse button.
private void mBook2Load_Click(object sender, EventArgs e)
{
// Create a new OpenFileDialog object that will be used to select the second
// workbook.
System.Windows.Forms.OpenFileDialog fWin = new OpenFileDialog();
// Set the extensions and default folder path to be used by the dialog.
fWin.DefaultExt = "xlsx";
fWin.Filter = "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv";
fWin.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
// Open the dialog and do addional processing if a valid result is returned.
if (fWin.ShowDialog() == DialogResult.OK)
{
// If a workbook is currently open close it.
mBook2Sheets = null;
if (mBook2 != null)
{
mBook2.Close(false);
mBook2 = null;
}
if (fWin.FileName.Equals(mBook1File.Text) && fWin.FileName.EndsWith(".csv"))
{
MessageBox.Show("Cannot compare a csv file to itself.");
return;
}
// Open the workbook selected in the dialog.
mBook2File.Text = fWin.FileName;
mBook2 = (Excel._Workbook)(mExcel.Workbooks.Add(fWin.FileName));
mBook2Sheets = (Excel.Sheets)(mBook2.Sheets);
// Enable and clear the combobox for the second workbook's worksheets.
mBook2SheetBox.Enabled = true;
mBook2SheetBox.Items.Clear();
// Populate the combobox for the second workbook.
int numSheets = mBook2Sheets.Count;
for (int i = 1; i <= numSheets; i++)
{
mBook2SheetBox.Items.Add(((Excel._Worksheet)(mBook2Sheets.Item[i])).Name);
}
}
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
// Program entry point.
static void Main(string[] args)
{
oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
Console.WriteLine("Excel worksheet created.");
List<string> phonePages = GetListOfPhonePages();
List<string> specPages = FindPhoneSpecPages(phonePages);
int i = 1;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
foreach (string specPage in specPages)
{
// Specs are extracted on multiple threads to bypass
// the time it takes to make web requests and load the DOM on the page.
new Thread(() =>
{
ExtractSpecs(specPage, i);
}).Start();
Random random = new Random();
Thread.Sleep(random.Next(1000,3000));
i++;
}
for (int j = 0; j < 1000; j++)
{
Thread.Sleep(1000);
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Console.WriteLine("RunTime " + elapsedTime);
}
}
public ExcelActiveDocument(IOfficeApplication application, MsExcel._Workbook workbook)
: base(application, workbook.FullName)
{
_workbook = workbook;
InitializeWsDocument(FullPath);
}
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);
}
public Workbook OpenWorkbook(string path = null)
{
if (path != null)
{
operatorWB = oXL.Workbooks.Open(path);
Workbook _workbook = new Workbook(operatorWB);
return _workbook;
}
else
{
operatorWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
Workbook _workbook = new Workbook(operatorWB);
return _workbook;
}
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0) showMan = true;
// set default values
string infile = "";
string paste = "";
List<string> macro = new List<string>();
int[] cellA = new int[2] { 0, 1 };
int[] cellB = new int[2] { 0, 1 };
int[] outCellA = new int[2] { 0, 1 };
int[] outCellB = new int[2] { 0, 0 };
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List<string> charts = new List<string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length;i++ )
{
if (args[i].StartsWith("-")) {
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?") showMan = true;
// specify macro to run
if (args[i] == "-m") {
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.",1);
}
}
// paste input as text?
if (args[i] == "-t") asText = true;
// dont save
if (args[i] == "-n") save = false;
// hide warnings
if (args[i] == "-w") warning = true;
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i+1)
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#") outErr = false;
}
else {
// excel file
if (iarg == 0) infile = args[i];
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1) {
cellA = ExcelCellRef(cellArr[0]);
}
else {
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "") Error("No file given.", 1);
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n"+errLine+"\n\n"+e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List<string[]> strings = new List<string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
strings.Add(line.Split(' '));
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings) if (str.Length > C) C = str.Length;
// convert input data to a format the Excel-interop understands: var[,]
if (asText) sCells = new string[N, C];
else cells = new double[N, C];
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText) sCells[i, j] = strings[i][j];
else cells[i, j] = double.Parse(strings[i][j]);
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning) oXL.DisplayAlerts = false;
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning) oXL.DisplayAlerts = true;
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText) oSheet.get_Range(startCell, endCell).Value2 = sCells;
else oSheet.get_Range(startCell, endCell).Value2 = cells;
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name)) chartName = cSheet.Name + "." + oChart.Name;
else if (charts.Contains(oChart.Name)) chartName = oChart.Name;
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts) list += s + ", ";
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0) outCellB[1] = oSheet.UsedRange.Columns.Count;
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else arr = r.Cells.Value2 as object[,];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List<string> results = new List<string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281) e = "#DIV/0!";
else if (eCode == -2146826246) e = "#N/A";
else if (eCode == -2146826259) e = "#NAME?";
else if (eCode == -2146826288) e = "#NULL!";
else if (eCode == -2146826252) e = "#NUM!";
else if (eCode == -2146826265) e = "#REF!";
else if (eCode == -2146826273) e = "#VALUE!";
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else e = "#ERR" + eCode.ToString();
s = s + e + " " + cellSpacer;
}
else s = s + " " + cellSpacer;
}
else if (arr[i, j] != null) s = s + arr[i, j].ToString() + cellSpacer;
else s = s + " " + cellSpacer;
}
// remove cellspacer from last column
if (C > 0) results.Add(s.Remove(s.Length - 1).TrimEnd());
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0) last = results.Count();
}
// write output to console
for (int i = 0; i < last; i++)
Console.WriteLine(results[i]);
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
//private bool createJobCostingSheet(ServiceSubmissionModel submission, string filename)
private int createJobCostingSheet(ServiceSheetViewModel submission, string filename, bool firstSubmission, int lineNumber)
{
Excel.Range range;
//First Open excel
if (firstSubmission)
{
excelApplication = new Excel.Application();
excelApplication.Visible = true;
excelWorkbook = excelApplication.Workbooks.Open(filename);
excelWorksheet = excelWorkbook.ActiveSheet;
//Add the job title
string customer = submission.Customer;
string machineMake = submission.MachineMakeModel;
string serialNumber = submission.MachineSerial;
string jobDescription = submission.JobDescription;
range = excelWorksheet.Cells[13, 1];
range.Value2 = string.Concat(customer, " - ", machineMake, " - S/N: ", serialNumber, " - ", jobDescription);
range = excelWorksheet.Cells[11, 9];
range.Value2 = submission.MttJobNumber;
}
//Load all the days and loop through them. Output to the sheet
AllServiceDayViewModels serviceDays = submission.AllServiceDays;
//Need the engineers initials for each row
string engFirstName = submission.UserFirstName;
string engSurname = submission.UserSurname;
string initials = string.Concat(engFirstName[0], engSurname[0]);
int sheetNo = submission.SubmissionNumber;
int currentSpreadsheetRow;
if (lineNumber == -1)
{
currentSpreadsheetRow = 17;
}
else
{
currentSpreadsheetRow = lineNumber++;
}
foreach (ServiceDayViewModel currentDay in serviceDays.AllServiceDayVMs)
{
range = excelWorksheet.Cells[currentSpreadsheetRow, 1];
range.Value2 = currentDay.DtReport;
string day = currentDay.DtReport.DayOfWeek.ToString();
range = excelWorksheet.Cells[currentSpreadsheetRow, 2];
range.Value2 = day;
double standardHours = calculateStandardHours(currentDay);
range = excelWorksheet.Cells[currentSpreadsheetRow, 3];
range.Value2 = standardHours;
double overtimeHours = calculateOvertimeHours(currentDay);
range = excelWorksheet.Cells[currentSpreadsheetRow, 4];
range.Value2 = overtimeHours;
range = excelWorksheet.Cells[currentSpreadsheetRow, 5];
range.Value2 = currentDay.TotalTravelTime;
bool dailyAllowance = currentDay.DailyAllowance;
int dailyAllowanceValue = convertBoolToIntForAllowances(dailyAllowance);
range = excelWorksheet.Cells[currentSpreadsheetRow, 6];
range.Value2 = dailyAllowanceValue;
bool overNight = currentDay.OvernightAllowance;
int overnightAllowanceValue = convertBoolToIntForAllowances(overNight);
range = excelWorksheet.Cells[currentSpreadsheetRow, 7];
range.Value2 = overnightAllowanceValue;
range = excelWorksheet.Cells[currentSpreadsheetRow, 8];
range.Value2 = currentDay.Mileage;
range = excelWorksheet.Cells[currentSpreadsheetRow, 9];
range.Value2 = initials;
range = excelWorksheet.Cells[currentSpreadsheetRow, 10];
range.Value2 = sheetNo;
currentSpreadsheetRow++;
}
MessageBox.Show("Need to handle bank holidays");
return currentSpreadsheetRow;
}
static void Main(string[] args)
{
oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
Console.WriteLine("Excel worksheet created.");
List<string> reviewPages = GrabDataPages();
Console.WriteLine("Data pages found.");
List<string> specPages = NavigateToSpecs(reviewPages);
Console.WriteLine("Spec pages found.");
ExtractSpecs(specPages);
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
public void QuitBook()
{
object oMissing = System.Reflection.Missing.Value;
if (oWB != null)
{
oWB.Close(false, oMissing, oMissing);
oWB = null;
}
if (oXL != null)
{
oXL.Quit();
oXL = null;
}
}
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 void OpenBook()
{
if (oXL != null || oWB != null)
{
return;
}
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = false;
//Get a new workbook.
object oTemplate = strExcelTemplate; //"c:\\Site Comparison Data (for auto poulation).xls";
oWB = oXL.Workbooks.Add(oTemplate);
//AppendSheet(oWB);
//oWB.Save();
//oXL.Quit();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
throw theException;
}
}
//Method to initialize opening Excel
private void ExcelInit(String path, out List<Product> idinfo)
{
xlApp = new Excel.Application();
string dsSheet = "datasheet";
string idinfoSheet = "idinfo";
idinfo = null;
if (System.IO.File.Exists(path)) {
// then go and load this into excel
xlWorkBook = xlApp.Workbooks.Open(path,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
//read info datasheet first to generate product specs list
info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
//read datasheet and load the data into product specs list
//ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
//ReadDatasheetIntoDataTable(ds_xlWorkSheet);
} else {
MessageBox.Show("Unable to open excel file!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.Windows.Forms.Application.Exit();
}
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
// Refrences to the first workbook to be diffed.
mBook1Sheets = null;
if (mBook1 != null)
{
mBook1.Close();
mBook1 = null;
}
// Refrences to the second workbook to be diffed.
mBook2Sheets = null;
if (mBook2 != null)
{
mBook2.Close();
mBook2 = null;
}
// Refrence to the instance of Microsoft Excel being used.
mExcel.Quit();
mExcel = null;
}