//public void AddWorkSheet(string sheetName)
//{
// xl.Worksheet newWorkSheet;
// newWorkSheet = (xl.Worksheet)Globals.ThisWorkbook.Worksheets.Add();
//}
public void CreateExcelFileFromDataTableOriginal(DataTable tbl)
{
xl._Worksheet oSheet;
xl.Range oRng;
object misvalue = System.Reflection.Missing.Value;
oSheet = (xl._Worksheet)oWB.ActiveSheet;
int idxpp = 1;
int columnCount = 0;
List<string> listColumn = new List<string>();
foreach (DataColumn column in tbl.Columns)
{
columnCount += 1;
listColumn.Add(column.ToString());
}
string columnCountString;
columnCountString = GetExcelColumnName(columnCount);
foreach (String val in listColumn)
{
oSheet.Cells[1, idxpp] = val;
idxpp += 1;
}
oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).Font.Bold = true;
oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).VerticalAlignment =
xl.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
var totalRow = tbl.Rows.Count;
int rowno = 0;
string[,] saNames = new string[totalRow, columnCount];
Debug.Print("totalrow" + totalRow.ToString());
if (tbl.Rows.Count > 0)
{
foreach (DataRow dr in tbl.Rows)
{
for (int idx = 0; idx < columnCount; idx += 1)
{
saNames[rowno, idx] = dr[idx].ToString();
}
rowno += 1;
}
//Debug.Print("rowcount: "+tbl.Rows.Count+ " colcount: "+tbl.Columns.Count+" columncount: "+columnCountString +" total row: "+totalRow);
//Debug.Print(string.Format("{0}{1}", columnCountString, totalRow));
oSheet.get_Range("A2", string.Format("{0}{1}", columnCountString, totalRow+1)).Value2 = saNames;
}
oRng = oSheet.get_Range("A1", string.Format("{0}1", columnCountString));
oRng.EntireColumn.AutoFit();
oWB.SaveAs(FileName, xl.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, xl.XlSaveAsAccessMode.xlNoChange);
oWB.Close();
oXL = null;
}
/// <summary>
/// Close the current workbook
/// </summary>
/// <param name="saveChanges">Should the changes be saved to the document.</param>
protected void CloseWorkbook(bool saveChanges = false)
{
if (_workbook != null)
{
object refSaveChanges = saveChanges;
// Close the document
// Close the workbook
_workbook.Close(
refSaveChanges,
_refMissing,
_refMissing);
}
if (_worksheet != null)
{
_worksheet = null;
}
if (_sheets != null)
{
_sheets = null;
}
if (_workbooks != null)
{
_workbooks.Close();
}
_workbook = null;
_workbooks = null;
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
}
System.GC.SuppressFinalize(this);
}
/// <summary>
/// Inchide toate procesele ce tin de excel si cheama garbage collector-ul
/// </summary>
public void Terminate()
{
wbXls.Close(null, null, null);
appXls.Workbooks.Close();
appXls.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appXls);
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.activeWsXls);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbXls);
this.activeWsXls = null;
wbXls = null;
appXls = null;
GC.Collect(); // colectarea componentelor
//omoara toate procesele excel
System.Diagnostics.Process [] p1 = System.Diagnostics.Process.GetProcessesByName("excel");
foreach (System.Diagnostics.Process proc in p1)
{
if (!proc.CloseMainWindow())
{
try
{
proc.Kill();
}
catch {}
}
}
}
private void SaveClick(object sender, RoutedEventArgs e)
{
string pathFile = "D:\\Desktop\\Hello";
try
{
//另存活頁簿
wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
}
//關閉活頁簿
wBook.Close(false, Type.Missing, Type.Missing);
//關閉Excel
excelApp.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
wBook = null;
excelApp = null;
wSheet = null;
GC.Collect();
}
//获取投放周期、投放开始日期、结束日期
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;
}
}
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 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();
}
private void Writetoexcel_Click(object sender, EventArgs e)
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell
oSheet.Cells[1, 1] = "voltage value is : ";
oSheet.Cells[1, 2] = textBox2.Text;
oXL.Visible = false;
oXL.UserControl = false;
string date = Convert.ToString(DateTime.UtcNow);
string[] date2 = date.Split('/', ':');
string filename = "C:\\Users\\ayoubexo.CEBONGROUP\\source\\repos\\JCD\\JCD\\excel files\\" + string.Join("-", date2) + "_test result.xlsx";
oWB.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
}
private void UpdateExcel(int row, int col, string data)
{
// UpdateExcel() is the method used to replace cells inside the specified
// Excel spreadsheet, using the Interop.Excel library.
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
try
{
// This try block opens up your specified document in the File Chooser,
// and attempts to write to cells with the specified data given (string data).
oXL = new Excel.Application();
oWB = oXL.Workbooks.Open(excelDocument);
oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
oSheet.Cells[row, col] = data;
oWB.Save();
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
if (oWB != null)
{
oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
}
}
}
/// <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 string DataString(int row, int col)
{
// This method extracts data from cells using the .ToString() function.
// However, as this simply hands the instructions off, Excel is extremely
// slow reading and sending data and causes a bottleneck. Looking for a better
// solution.
Excel.Application oXL = new Excel.Application();
Excel._Workbook oWB = null;
string data = "";
try
{
// Why in 0.0.12, I forgot to encase this in a try/catch, is beyond me.
oWB = oXL.Workbooks.Open(excelDocument);
Excel._Worksheet oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
data = oSheet.Cells[row, col].Value.ToString();
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
if (oWB != null)
{
oWB.Close();
}
}
return(data); // Return the data inside the cell.
}
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.
}
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);
}
void printLable()
{
string path = Directory.GetCurrentDirectory();
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = null;
wb = oXL.Workbooks.Open(path + @"/../../xls/cleancut/7 标签-清洁分切.xlsx");
_Worksheet my = wb.Worksheets[wb.Worksheets.Count];
my.Select();
my.Cells[2, 2].Value = cb膜代码.Text;
my.Cells[3, 2].Value = tb批号.Text;
my.Cells[4, 2].Value = tb米.Text + "米； " + tbKg.Text + "Kg";
my.Cells[5, 2].Value = cb原膜代码.Text;
my.Cells[6, 2].Value = String.Format("{0} {1}", dtp分切日期.Value.ToString("yyyy/MM/dd"),
cb白班.Checked ? "白班☑ 夜班□" : "白班□ 夜班☑");
my = wb.Worksheets[1];
my.Select();
oXL.Visible = false;
my.PrintOut();
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
static void Main(string[] args)
{
string WorkDir = @"D:\气象\";
string filename = WorkDir + "河北.xlsx";
// string[] RawDataStr_A = File.ReadAllLines(i+ ".xlsx" + ".csv", Encoding.UTF8);
// RawDataStr_A = File.ReadAllLines(i + ".xlsx" + ".csv", Encoding.Default);
Microsoft.Office.Interop.Excel.Application appExcel = new Application();
Microsoft.Office.Interop.Excel._Workbook ExcelBooks = null;
ExcelBooks = Shashade(appExcel);
if (File.Exists((string)filename)) //判断文件已经是否存在
{
File.Delete((string)filename); //若已存在，则删除
}
ExcelBooks.SaveAs(filename);
//stopWatch.Stop();
//TimeSpan ts3 = stopWatch.Elapsed;
//string elapsedTime3 = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts3.Hours, ts3.Minutes, ts3.Seconds, ts3.Milliseconds / 10);
//Console.WriteLine("RunTime " + elapsedTime3);
ExcelBooks.Close();
//appExcel.Quit();
ExcelBooks = null;
appExcel = null;
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
// HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
}
System.GC.SuppressFinalize(this);
}
private void button1_Click(object sender, EventArgs e)
{
object oMissing = System.Reflection.Missing.Value;
//Create an instance of Microsoft Excel, make it visible and open invoice.xls
Excel.Application oExcel = new Excel.Application();
oExcel.Visible = true;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
int index = comboBox1.SelectedIndex;
oBook = oBooks.Open(files[index], oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
//Run the macro sub CSV() in the PERSONAL.XLSB file
oExcel.Run("'c:\\csv\\PERSONAL.XLSB'!CSV");
//Quit Excel and clean up
oBook.Close(false, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
GC.Collect();
}
/// <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();
}
}
//adds project to SEQR workbook
private static void AddToWorkbook(string parkName, string projectName, bool newProject)
{
string region = albPath.Substring(0, albPath.Length - 1);
region = region.Substring(region.LastIndexOf("\\") + 1);
string smRegion = region.Replace(" ", "");
string workbook = albPath + smRegion + "SEQRWorkbook.xlsx";
//workbook = "D:\\CentralSEQRWorkbook.xlsx";
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = false;
Excel._Workbook wBk = excelApp.Workbooks.Open(workbook, 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);
Excel._Worksheet wSht = (Excel.Worksheet)wBk.Worksheets[1];
int rowNum = 0;
string searchStr = newProject ? "" : projectName;
int searchCol = newProject ? 1 : 3;
foreach (Excel.Range row in wSht.Rows)
{
Excel.Range rng = (Excel.Range)wSht.Cells[row.Row, searchCol];
if (rng.Text.ToString().Equals(searchStr))
{
rowNum = row.Row;
break;
}
}
if (newProject)
{
PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
UserPrincipal user = UserPrincipal.Current;
string fullName = user.GivenName + " " + user.Surname;
wSht.Cells[rowNum, 1] = region;
wSht.Cells[rowNum, 2] = parkName;
wSht.Cells[rowNum, 3] = projectName;
wSht.Cells[rowNum, 5] = DateTime.Today.Year;
wSht.Cells[rowNum, 7] = fullName;
}
else
{
wSht.Hyperlinks.Add(wSht.Cells[rowNum, 4], albPath + parkName + "\\" + projectName, Type.Missing, Type.Missing, parkName + "\\" + projectName);
wSht.Cells[rowNum, 13] = DateTime.Today;
}
wBk.Save();
wBk.Close();
excelApp.Quit();
excelApp = null;
GC.Collect();
}
//public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
//{
// bool ret_val = false;
// string sql = "DELETE FROM [Sheet1$] WHERE [" + p_Col + "] LIKE '" + p_Cond + "%';";
// ret_val = dB_Update(p_dB, sql);
// return ret_val;
//}
public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
{
bool ret_val = false;
int col_idx = 0, row_idx = 0;
string filename = DataDir + p_dB + dbFileExt;
XL.Application xlApp = new XL.Application();
XL._Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
XL._Worksheet xlWorksheet = xlWorkbook.Worksheets["Sheet1"];
XL.Range xlRange = xlWorksheet.UsedRange;
int row_cnt = xlRange.Rows.Count;
int col_cnt = xlRange.Columns.Count;
// First find the column number
for (int i = 1; i < col_cnt; i++)
{
string val = xlRange.Cells[1, i].Value2.ToString();
if (val == p_Col)
{
col_idx = i;
break;
}
}
if (col_idx > 0)
{
// Next find the row number to delete
for (int i = 2; i <= row_cnt; i++)
{
string val = xlRange.Cells[i, col_idx].Value2.ToString();
if (val == p_Cond)
{
row_idx = i;
break;
}
}
if (row_idx > 0)
{
(xlWorksheet.Rows[row_idx, System.Reflection.Missing.Value] as XL.Range).Delete(XL.XlDeleteShiftDirection.xlShiftUp);
ret_val = true;
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
xlApp.DisplayAlerts = false;
xlWorkbook.Save();
xlWorkbook.Close();
xlApp.Quit();
// release COM objects
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlApp);
return(ret_val);
}
/// <summary>
/// save the excel sheet to the location with file name
/// </summary>
/// <param name="fileName"></param>
protected virtual void SaveExcel(string fileName)
{
_workBook.SaveAs(fileName, _value, _value,
_value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
_value, _value, _value, _value, null);
_workBook.Close(false, _value, _value);
_excelApplication.Quit();
}
// 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);
}
}
}
public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result)
{
try
{
Cursor.Current = Cursors.WaitCursor;
app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Open(formPath);
//Excel._Worksheet worksheet = null;
Excel._Worksheet worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Оцінка якості";
worksheet.Cells.Replace("{OpDateTime}", opDate);
worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi);
worksheet.Cells.Replace("{CallType}", callType);
worksheet.Cells.Replace("{Contragent}", contragent);
worksheet.Cells.Replace("{FilePath}", filePath);
worksheet.Cells.Replace("{Ocinyvach}", ocinyvach);
worksheet.Cells.Replace("{CriticalError}", criticalError);
worksheet.Cells.Replace("{Result}", result);
int startRow = 14;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < dt.Rows.Count - 1)
{
worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy();
worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert();
}
worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString();
worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString();
worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString();
}
worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit();
if (fileFormat.Contains("xlsx"))
{
workbook.SaveAs(savePath);
app.Visible = true;
}
else if (fileFormat.Contains("pdf"))
{
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath);
workbook.Close(false);
app.Quit();
System.Diagnostics.Process.Start(savePath);
}
Cursor.Current = Cursors.Default;
//MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (app.Workbooks.Count == 1)
{
app.Quit();
}
app = null;
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void CloseExcel()
{
if (oXL == null)
{
return;
}
oWB.Close();
oXL.Quit();
}
public void saveWorkbook(String fileName)
{
//save workbook
oWB.SaveAs(fileName);
//close workbook
oWB.Close();
oXL.Quit();
}
/// <summary>
/// Metod koj vrsi zacuvuvanje na dokumentot pod opredeleno ime -
/// momentalno vo direktoriumot C:\TEMP\NBOFILES
/// </summary>
/// <param name="fileName"></param>
public void SaveExcel(string fileName)
{
object fileNameToSave = FILE_PATH + "\\" + fileName;
_workBook.SaveAs(fileNameToSave, _value, _value,
_value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
_value, _value, _value, _value, null);
_workBook.Close(false, _value, _value);
_excelApplication.Quit();
}
private static void ExcelClose(Excel._Workbook workbook, Excel._Application application)
{
var missingObject = System.Reflection.Missing.Value;
workbook.Close(false, missingObject, missingObject);
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
GC.Collect();
}
public static string externAsXLS(string path)
{
string dateNow = DateTime.Now.ToShortDateString();
string filename = dateNow + "_" + "extern_list";
object miss = Type.Missing;
List <List <String> > externs = new List <List <string> >();
try
{
List <object[]> list = Database.Database.database.RequestTable("f_get_extern_user", 6);
foreach (var o in list)
{
externs.Add(new List <string> {
(string)o[0], (string)o[1], (string)o[2], (string)o[3], (string)o[4], (string)o[5]
});
}
}
catch (Exception) { }
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 = "Liste des externes";
ws.Cells[1, 1] = "Login";
ws.Cells[1, 2] = "Adresse mél";
ws.Cells[1, 3] = "Prénom";
ws.Cells[1, 4] = "Nom";
ws.Cells[1, 5] = "Adresse";
ws.Cells[1, 6] = "Numéro de téléphone";
for (int y = 2; y <= externs.Count + 1; y++)
{
for (int x = 1; x <= externs[y - 2].Count; x++)
{
ws.Cells[y, x] = externs[y - 2][x - 1];
}
}
ws.Columns.AutoFit();
wb.SaveAs(path + filename);
wb.Close();
//wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive);
app.Quit();
return(filename + ".xlsx");
}
void printLable(bool flag)
{
string path = Directory.GetCurrentDirectory();
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = null;
if (flag)
{
wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋内包标签.xlsx");
}
else
{
wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋外包标签.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[11, 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("dd/MM/yyyy");
my.Cells[6, 5].Value = deExpiry.Value.ToString("MM/yyyy");
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 = false;
my.PrintOut();
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
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);
}
