private void btnTest2_Click(object sender, EventArgs e)
{
Excel.Application m_excel;
Excel.Workbook m_workbook;
m_excel = new Excel.Application();//对象实例化
m_workbook = m_excel.Workbooks.Open("D:\\test.xlsx", 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);
//m_workbook = m_excel.Workbooks.Open("D:\\test.xls", 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);
m_excel.Visible = true;//显示Excel内容(为false时，excel.exe进程在后台运行，未找到关闭方法)
Range rng;
object obj;
String str;
rng = (Excel.Range)m_excel.Cells[2, 2];
obj = rng.Value2;
System.Diagnostics.Debug.WriteLine(obj.ToString()); //输出调试信息
str = rng.NumberFormatLocal;
System.Diagnostics.Debug.WriteLine(str); //输出调试信息
this.txtTest2.Text = obj.ToString();
m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
m_excel.AlertBeforeOverwriting = false;
m_excel.Workbooks.Close();
//object oV = System.Reflection.Missing.Value; //反复用到
//System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_excel);
//GC.Collect();
m_excel.Quit();
}
private void QuitExcel(ref Excel.Application eps, ref Workbook wkCur)
{
try
{
if (wkCur != null)
{
wkCur.Save();
wkCur.Close(false);
}
//无论是否成功，都退出
if (eps != null)
{
PrintLog("Convert Excel file To PDF end");
eps.Quit();
}
}
catch (Exception ex)
{
PrintLog(ex.Message.ToString());
//m_nConvertStatus = EErrorType.OTP_EXCEPTION_FAILED;
}
finally
{
eps = null;
wkCur = null;
}
}
private void exp_excel(bool print)
{
try
{
int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "bcngay");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Cộng";
for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString();
}
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").Orientation = 90;
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").RowHeight = 30;
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG";
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile()
{
if (xlsApp != null)
{
//检查文件是否存在
if (xlsOpenFileName == "")
{
throw new Exception("请选择文件!");
}
}
if (!File.Exists(xlsOpenFileName))
{
throw new Exception(xlsOpenFileName + "该文件不存在!");
}
try
{
xlsApp = new Excel.ApplicationClass();
xlsWbs = xlsApp.Workbooks;
xlsWb = ((Excel.Workbook)xlsWbs.Open(xlsOpenFileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value));
xlsWs = (Excel.Worksheet)xlsWb.Worksheets[excelActiveWorkSheetIndex];
xlsApp.Visible = false;
return(true);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception("(1)没有安装Excel 2003;\n(2)或没有安装Excel 2003 .NET 可编程性支持;\n详细信息:\n" + e.Message);
}
}
private void butExcel_Click(object sender, System.EventArgs e)
{
get_data();
DataSet tmp = new DataSet();
tmp = ds.Copy();
tmp.Clear();
tmp.Merge(ds.Tables[0].Select("true", "tenbd"));
if (tmp.Tables[0].Rows.Count > 0)
{
d.check_process_Excel();
string tenfile = d.Export_Excel(tmp, "dmthuoc");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
for (int i = 0; i < 8; i++)
{
osheet.Cells[1, i + 1] = get_ten(i);
}
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
int row = tmp.Tables[0].Rows.Count + 1;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(tmp.Tables[0].Columns.Count - 1) + row.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
oxl.Visible = true;
}
}
public void Open(string file)
{
deactivateevents = true;
try
{
if (xlApp == null)
{
xlApp = new Excel.Application();
}
if (xlsWnd == 0)
{
xlsWnd = FindWindow("XLMAIN", null);
}
try
{
xlApp.CommandBars.AdaptiveMenus = false;
xlApp.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(OnClose);
SetParent(xlsWnd, this.Handle.ToInt32());
xlApp.Visible = true;
xlApp.UserControl = true;
SetWindowPos(xlsWnd, this.Handle.ToInt32(), 0, 0, this.Bounds.Width, this.Bounds.Height, SWP_NOZORDER | SWP_NOMOVE | SWP_DRAWFRAME | SWP_NOSIZE);
OnResize();
}
catch { }
xlApp.Workbooks.Open(file, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
UnLock();
workbook = xlApp.Workbooks.get_Item(1);
}
finally
{
deactivateevents = false;
}
}
private void butExcel_Click(object sender, System.EventArgs e)
{
if (butExcel.Enabled == false)
{
return;
}
d.check_process_Excel();
string tenfile = d.Export_Excel(dt, ReportFile.Substring(0, ReportFile.Length - 4));
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
try
{
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
}
catch { }
oxl.Visible = true;
}
private void butIn_Click(object sender, System.EventArgs e)
{
d.check_process_Excel();
string sql = "select a.ma,a.tenhc,trim(a.ten)||' '||a.hamluong as ten,a.dang as dvt,a.gia_bh from " + user + ".d_dmbd a," + user + ".d_dmnhom c where a.manhom=c.id and a.nhom=" + i_nhom + " and gia_bh>0 and bhyt<>0";
sql += "order by ";
if (d.bSort_mabd)
{
sql += " a.ma";
}
else
{
sql += " a.ten";
}
d.check_process_Excel();
string tenfile = d.Export_Excel(d.get_data(sql), "dmthuoc");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
oxl.Visible = true;
}
public void CreateExcel(System.Data.DataTable dt, string FilePath, string targetFileName)
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
catch { }
}
}
//保存
SaveExcelFile(excel, FilePath, targetFileName);
//打开显示
excel.Visible = true;
// excel.Quit();
// excel=null;
// GC.Collect();//垃圾回收
}
/// <summary>
/// Excel文件打印预览
/// </summary>
/// <param name="ExcelFile">文件路径包含文件名称</param>
public static void ExcelPreview(string ExcelFile)
{
try
{
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
throw new Exception("无法创建Excel对象，可能您的计算机未安装Excel");
}
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Excel文件操作失败");
}
}
//--------------------------------------------------------------------------------------------------------
/// <summary>
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
/// </summary>
/// <param></param>
public void CloseExcelApplication()
{
try
{
xlsWbs = null;
xlsWb = null;
xlsWs = null;
xlsRg = null;
if (xlsApp != null)
{
xlsApp.ActiveWorkbook.Close(false, null, null);
xlsApp.Workbooks.Close();
//Object missing = Type.Missing;
xlsApp.Quit();
xlsApp = null;
//ReleaseAllRef(excelApplication);//Error
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 基于excel.dll打开excel文件
/// </summary>
/// <param name="FileName">文件名</param>
public void OpenExcel(string FileName)
{
//打开excel
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
app.Visible = true;
}
/// <summary>
/// 关闭Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 关闭Excel
/// </summary>
/// <param name="isSave">是否保存</param>
/// <param name="fileName">存储文件名</param>
public void Close(bool isSave, string fileName)
{
myWorkBook.Close(isSave, fileName, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
public ClsAllExcel()
{
xlsApp = null;
xlsWbs = null;
xlsWb = null;
xlsWs = null;
ActiveSheetIndex = 1;
}
public static void doit(Excel.Application app, ArrayList excelnames, string command)
{
ArrayList prods = new ArrayList();
ArrayList words = new ArrayList();
Hashtable nts = new Hashtable();
foreach (string excelname in excelnames)
{
Excel.Workbook wb = app.Workbooks.Open(
excelname, // Filename
0, // UpdateLinks
true, // ReadOnly
5, // Format
"", // Password
"", // WriteResPassword
true, // IgnoreReadOnlyRecommended
Excel.XlPlatform.xlWindows, // Origin
"\t", // Delimiter
false, // Editable
false, // Notify
0, // Converter
true, // AddToMru
true, // Local
true // CorruptLoad
);
prods = process(wb, prods);
nts = processTypes(wb, nts);
words = processKeyword(wb, words);
}
switch (command)
{
default:
System.Console.WriteLine("usage: a.out (grammar|keywords) excelfile");
break;
case "gram":
prods = addOpts(prods, nts);
foreach (production p in prods)
{
System.Console.WriteLine(p.prod);
}
break;
case "rewrite":
prods = addOpts(prods, nts);
emitParse2AST(nts, prods);
break;
case "keywords":
foreach (string s in words)
{
System.Console.WriteLine(s);
}
break;
}
}
public void Open(string FileName)//打开一个Excel文件
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
{
app = Application as Application;
app.WorkbookOpen += AppOnWorkbookOpen;
app.WorkbookBeforeSave += AppOnWorkbookBeforeSave;
app.WorkbookBeforeClose += AppOnWorkbookBeforeClose;
app.SheetSelectionChange += AppOnSheetSelectionChange;
app.SheetActivate += AppOnSheetActivate;
}
private static void KillSpecialExcel(Excel.Application m_objExcel)
{
if (m_objExcel != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
public void CreateExcel(IDictionary <string, System.Data.DataTable> iDicDT, string FilePath, bool Visible)
{
Excel.Application excel = new Excel.Application(); //引用Excel对象
Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用
Excel.Worksheet sheet3 = (Excel.Worksheet)book.Worksheets["Sheet3"];//选中
object nothing = System.Reflection.Missing.Value;
book.Worksheets.Add(nothing, sheet3, iDicDT.Count - 3, nothing);
int index = 0;
foreach (KeyValuePair <string, System.Data.DataTable> val in iDicDT)
{
index++;
Excel.Worksheet tempExcel = (Excel.Worksheet)book.Worksheets["Sheet" + index];//选中
tempExcel.Name = val.Key;
System.Data.DataTable tempdt = val.Value;
for (int i = 0; i < tempdt.Columns.Count; i++)
{
tempExcel.Cells[1, i + 1] = tempdt.Columns[i].ColumnName;
}
for (int i = 0; i < tempdt.Rows.Count; i++)
{
for (int j = 0; j < tempdt.Columns.Count; j++)
{
try
{
tempExcel.Cells[i + 2, j + 1] = tempdt.Rows[i][j].ToString();
}
catch { }
}
}
}
//保存
if (!string.IsNullOrEmpty(FilePath))
{
SaveExcelFile(excel, FilePath);
}
//打开显示
excel.Visible = Visible;
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
private void exp_excel(bool print)
{
try
{
ttb.check_process_Excel();
ds = dsxml.Copy();
int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "dutru");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be - 1) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "3", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 7;
foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
//osheet.Cells[dong-1,pos]="Tồn "+makho.Text;
for (int i = 0; i < 6; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "TỔNG HỢP DÙ TRÙ";
string s_title = ttb.title(tu.Value.ToString(), tu.Value.ToString()) + " năm " + yyyy.Value.ToString();
osheet.Cells[2, 4] = s_title;
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch { }
}
public void StartExcel()
{
// Create new Excel instance and add a workbook.
// Using the Excel Wrapper for EPLAN Scripts this looks like COM Interop:
Excel.Application xlsApp = new Excel.Application();
xlsApp.Visible = true;
xlsApp.Workbooks.Add();
// Your code here...
}
//关闭Excel
public void CloseExcel()
{
wb.Close();
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
private void exp_excel()
{
try
{
string s;
int be = 3, dong = 6, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 2;
tenfile = d.Export_Excel(ds, "biendong");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.Cells[dong - 2, 3] = "Tên hoạt chất";
osheet.Cells[dong - 2, 4] = "Tên thuốc - hàm lượng";
osheet.Cells[dong - 2, 5] = "ĐVT";
osheet.Cells[dong - 2, 6] = "Diễn giải";
for (int i = 0; i < i_rec; i++)
{
s = ds.Tables[0].Columns[i + 6].ColumnName.ToString();
osheet.Cells[dong - 2, i + 7] = "'" + s.Substring(4, 2) + "/" + s.Substring(2, 2) + "/" + s.Substring(0, 2);
}
osheet.Cells[dong - 2, i_rec + 7] = "Tỷ lệ";
osheet.Cells[dong - 2, i_rec + 8] = "Ghi chú";
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
osheet.Cells[1, 3] = "BIẾN ĐỘNG GIÁ THUỐC";
osheet.Cells[2, 3] = "Từ ngày " + tu.Text + " đến " + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
oxl.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "cosotutruc");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(be - 1) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "3", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < 8; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv;
osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG NỘI TRÚ";
string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text;
if (tu.Text == den.Text)
{
s_title = "Ngày " + tu.Text;
}
osheet.Cells[2, 4] = s_title;
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
//关闭一个Excel对象，销毁对象
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
public static void Kill(Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch { }
}
public void Export2DatasetDSPhongThi(System.Data.DataSet i_DataSet, string i_TableName, int i_iSheetStartRow)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int i_iExcelRow = 0;
bool v_bol_stop = false;
while (!v_bol_stop)
{
int i_iExcelCol = 0;
System.Data.DataRow v_iDataRow = default(System.Data.DataRow);
v_iDataRow = i_DataSet.Tables[i_DataSet.Tables[i_TableName].TableName].NewRow();
v_iDataRow[i_iExcelCol] = i_iExcelCol + 1;
for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[i_TableName].Columns.Count - 2; i_iExcelCol++)
{
if (!object.ReferenceEquals(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 4]/*.Value()*/, null))
{
if (!(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/ == null))
{
v_iDataRow[i_iExcelCol + 1] = m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/;
}
}
else
{
v_bol_stop = true;
}
}
if (!v_bol_stop)
{
i_DataSet.Tables[i_TableName].Rows.InsertAt(v_iDataRow, i_iExcelRow);
i_iExcelRow++;
}
}
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
public CExcelReport(string i_strTemplateFileWithoutPath, int i_iSheetStartRow, int i_iSheetStartCol)
{
InitPaths();
m_strTemplateFileNameWithPath = m_strTemplatesPath + i_strTemplateFileWithoutPath;
m_iSheetStartCol = i_iSheetStartCol;
m_iSheetStartRow = i_iSheetStartRow;
m_objExcelApp = new Excel.Application();
FindAndReplaceCollection = new Hashtable();
m_init_successful = false;
m_b_haved_show = false;
init_excel();
}
/// <summary>
/// 直接创建Excel表
/// </summary>
/// <param name="dtSource">数据源DataTable</param>
/// <param name="filePath">保存路径</param>
/// <param name="drTitle">标题DataRow</param>
/// <param name="TableName">Excel表头</param>
/// <returns></returns>
public static bool Save(System.Data.DataTable dtSource, string filePath, System.Data.DataRow drTitle, string TableName)
{
Excel.Application app = new Excel.Application();//EXCEL实例
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (dtSource.Rows.Count > 0)
{
int row = dtSource.Rows.Count + 1;
int col = dtSource.Columns.Count + 1;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = dtSource.Rows[i][j].ToString();
wSheet.Cells[i + 3, j + 1] = str;
}
}
}
int size = dtSource.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[2, 1 + i] = drTitle[i].ToString();
}
wSheet.get_Range(wSheet.Cells[1, 0], wSheet.Cells[1, size]).Merge(Type.Missing);
wSheet.Cells[1, 1] = TableName;
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Save();
//保存excel文件
app.Save(filePath);
app.SaveWorkspace(filePath);
app.Quit();
app = null;
return(true);
}
catch
{
return(false);
}
finally
{
}
}
private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int dong = 2, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "cosotutruc");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(3) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = format_soluong;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 5;
foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
for (int i = 0; i < 4; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text;
if (tu.Text == den.Text)
{
s_title = "Ngày " + tu.Text;
}
osheet.PageSetup.LeftHeader = d.Syte + "\n" + d.Tenbv;
osheet.PageSetup.CenterHeader = "&\"Arial,Bold\"&14BÁO CÁO CƠ SỐ TỦ TRỰC\n" + s_title;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
public Excel.Application StartApplication()
{
if (this.app != null)
{
app.Quit();
app = null;
return app = new Excel.Application();
}
else
{
return app = new Excel.Application();
}
}
public Class1(string strXL, object extraMenuItem, SqlConnection conn)
{
Conn = conn;
try
{
excelApp = new Excel.ApplicationClass();
workbook = excelApp.Workbooks.Open(strXL, 0, false, 5, "", "", false,
Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
}
catch(Exception ex)
{
MessageBox.Show("在打开Excel文件{0}时产生异常: " + ex.ToString());
}
}
/// <summary>
/// ����һ��Excelʾ��
/// </summary>
public void CreateExcel()
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "��1�е�1��";
excel.Cells[1, 2] = "��1�е�2��";
excel.Cells[2, 1] = "��2�е�1��";
excel.Cells[2, 2] = "��2�е�2��";
excel.Cells[3, 1] = "��3�е�1��";
excel.Cells[3, 2] = "��3�е�2��";
//����
excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//����ʾ
excel.Visible = true;
// excel.Quit();
// excel=null;
// GC.Collect();//��������
}
private DateTime afterTime; //Excel启动之后时间
#region 创建一个Excel示例
/// <summary>
/// 创建一个Excel示例
/// </summary>
public void CreateExcel()
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "第1行第1列";
excel.Cells[1, 2] = "第1行第2列";
excel.Cells[2, 1] = "第2行第1列";
excel.Cells[2, 2] = "第2行第2列";
excel.Cells[3, 1] = "第3行第1列";
excel.Cells[3, 2] = "第3行第2列";
//保存
excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//打开显示
excel.Visible = true;
// excel.Quit();
// excel=null;
// GC.Collect();//垃圾回收
}
public ExcelController(bool excelVisible, IReport report)
{
dtBefore = System.DateTime.Now;
CurExcel = new Excel.ApplicationClass();
CurExcel.DisplayAlerts = false;
dtAfter = System.DateTime.Now;
CurExcel.Visible = excelVisible;
//CurExcel.Workbooks.Add(true);
CurExcel.Workbooks.Add(mValue);
sheetIndex = 1;
CurSheet = CurExcel.Worksheets[sheetIndex] as Excel.Worksheet;
CurBook = CurExcel.Workbooks[1];
pictureIndex = 0;
log = new EasilyReportLog("Excel Report", this.GetType().FullName, LogFileInfo.logFileName, report);
}
protected override void Dispose(bool disposing)
{
ApplicationControllerTrace trace = new ApplicationControllerTrace("Dispose requested");
lock (m_lock)
{
if (m_disposed)
return;
m_disposed = true;
trace.Log("Dispose started");
try
{
Quit();
}
catch (System.Exception e)
{
trace.Log(e.Message);
// ignore.
}
finally
{
trace.Log("Releasing host application");
if (null != m_hostApplication)
Marshal.FinalReleaseComObject(m_hostApplication);
m_hostApplication = null;
trace.Log("Dispose completed");
}
base.Dispose(disposing);
}
}
/// <summary>
/// �ͷ��ڴ�ռ�
/// </summary>
public void Dispose()
{
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)
{
// MessageBox.Show("���ͷ�Excel�ڴ�ռ�ʱ������һ������"+ex.Message);
}
finally
{
//foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
// if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
// {
// try
// {
// pro.Kill();
// }
// catch { }
// }
}
System.GC.SuppressFinalize(this);
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.Application();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
afterTime = DateTime.Now;
//显示效果
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return filename;
}
private void btnXZYB_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"F:\昆钢项目\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
txtYBWJ.Text = openFileDialog.FileName;
}
}
/// <summary>
/// 读取并保存Excel中的信息到数据库中
/// </summary>
private void ReadAndSaveExcelInfo()
{
//Workbook wBook = excel.Workbooks.Add(true);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet;
//Worksheet worksheet = (Excel.Worksheet)wBook.ActiveSheet;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
string strTmpTable, strTmpField, strTmpValue;
string val1 = "";
string val2 = "";
string val3 = "";
string val4 = "";
string val5 = "";
string val6 = "";
string val7 = "";
string val8 = "";
string val9 = "";
string val10 = "";
string val11 = "";
int num = 0;
Excel.Range range = worksheet.get_Range("A2", "P1000");//A2、O1000表示到Excel从第A列的第2行到第O列的第1000-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行
System.Array values = (System.Array)range.Formula;
num = values.GetLength(0);
for (int i = 1; i <= num; i++)
{
if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "")
{
break;
}
else
{
val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期
val2 = values.GetValue(i, 2).ToString().Trim(); //订单号
val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号
val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码
val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称
val6 = values.GetValue(i, 6).ToString().Trim(); //批号
val7 = values.GetValue(i, 7).ToString().Trim(); //收货数量
val8 = values.GetValue(i, 8).ToString().Trim(); //计量单位
val9 = values.GetValue(i, 9).ToString().Trim(); //工厂
val10 = values.GetValue(i, 10).ToString().Trim(); //库存地点
val11 = values.GetValue(i, 11).ToString().Trim(); //抬头文本
string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName();
string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment();
strTmpTable = "DT_SAP261";
strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_MATERIALNAME,FS_STOVENO,"
+ "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR";
strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','"
+ val4 + "','" + val5 + "','" + val6 + "'," + val7 + ",'" + val9 + "','" + val10
+ "','" + val11 + "'," + "'261',";
/* if ((CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1001") || (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1002"))
{
strTmpValue += "'棒线厂一作',";
}
if (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC2001")
{
strTmpValue += "'棒线厂二作',";
}*/
strTmpValue += "'" + strLRD + "','" + strLRR + "'";
CoreClientParam ccp = new CoreClientParam();
ccp.ServerName = "ygjzjl.base.QueryData";
ccp.MethodName = "insertDataInfo";
ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue };
try
{
this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
}
catch(Exception ex1)
{
lstHint1.Items.Add(ex1.Message + "第" + i.ToString() + "行信息导入成功！");
}
}
}
app.Quit();
app = null;
lstHint1.Items.Add("信息导入成功！");
System.Diagnostics.Process[] myProcesses;
myProcesses = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process myProcess in myProcesses)
{
if(myProcess.ProcessName == "EXCEL")
myProcess.Kill();
}
}
private void DataGridViewToExcel()
{
bool flag = false;
if (this._GrdData != null)
{
Exception exception2;
this.dBegin = DateTime.Now;
if (this.appExcel == null)
{
this.appExcel = new ApplicationClass();
}
this.wkBook = this.appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
this.wkSheet = (Worksheet) this.wkBook.Worksheets[1];
this.appExcel.Visible = false;
this.dEnd = DateTime.Now;
this.nCols = this._GrdData.ColumnCount;
this.nRows = this._GrdData.Rows.Count;
this.nCurrR = 1;
this.nCurrC = 1;
if (this._RptTitle != "")
{
this.SetControlText(this.lblState, "正在处理报表头......");
this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._RptTitle;
this.range = this.wkSheet.get_Range(this.wkSheet.Cells[this.nCurrR, 1], this.wkSheet.Cells[this.nCurrR, this.nCols]);
this.range.MergeCells = true;
this.range.Font.Bold = true;
this.range.Font.Size = 0x12;
this.range.Font.Name = "宋体";
this.range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
this.range.VerticalAlignment = XlVAlign.xlVAlignCenter;
this.range.EntireRow.AutoFit();
this.nRows++;
this.nCurrR++;
}
this.nRows++;
this.SetProgressMaxMin(this.prgMain, this.nRows, 0);
this.SetProgressValue(this.prgMain, this.nCurrR);
this.SetControlText(this.lblState, "正在处理列头......");
this.nCurrC = 1;
while (this.nCurrC <= this.nCols)
{
this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._GrdData.Columns[this.nCurrC - 1].HeaderText;
this.nCurrC++;
}
this.nCurrR++;
try
{
foreach (DataGridViewRow row in (IEnumerable) this._GrdData.Rows)
{
this.nCurrC = 0;
while (this.nCurrC < this.nCols)
{
Exception exception;
string str3;
if (row.Cells[this.nCurrC].Value != null)
{
try
{
string str2 = row.Cells[this.nCurrC].Value.ToString().Trim();
double a;
if (str2.StartsWith("=") || double.TryParse(str2,out a))
{
str2 = "'" + str2;
}
this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = str2;
}
catch (Exception exception1)
{
exception = exception1;
str3 = this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1].ToString();
string str4 = row.Cells[this.nCurrC].Value.ToString();
MessageBox.Show(string.Format("在：nCurrC=[{3}] nCurrR=[{4}]==[{0}]=:=[{1}]=:=[{2}] nCols=[{5}]", new object[] { str3, exception.Message, str4, this.nCurrC, this.nCurrR, this.nCols }));
}
}
else
{
try
{
this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = "";
}
catch (Exception exception3)
{
exception = exception3;
str3 = this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1].ToString();
}
}
this.nCurrC++;
}
this.SetControlText(this.lblState, "正在处理数据......(" + this.nCurrR.ToString() + "/" + this.nRows.ToString() + ")");
this.SetProgressValue(this.prgMain, this.nCurrR);
this.nCurrR++;
}
}
catch (Exception exception4)
{
exception2 = exception4;
MessageBox.Show(exception2.Source + "\r\n" + exception2.Message + exception2.StackTrace + "\r\n" + exception2.TargetSite.ToString());
}
this.range = null;
this.range = this.wkSheet.get_Range(this.wkSheet.Cells[1, 1], this.wkSheet.Cells[this.nRows, this.nCols]);
this.range.Cells.Borders.LineStyle = XlLineStyle.xlContinuous;
this.range.Borders.Weight = XlBorderWeight.xlThin;
this.range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;
if (this._FileName.Trim() == "")
{
this._FileName = Path.Combine(System.Windows.Forms.Application.StartupPath, "datagrid.xls");
}
if (this._FileName.ToLower().IndexOf(".xls") <= 0)
{
this._FileName = this._FileName + ".xls";
}
if (File.Exists(this._FileName))
{
try
{
File.Delete(this._FileName);
}
catch (Exception exception5)
{
exception2 = exception5;
MessageBox.Show(exception2.Message);
}
}
try
{
this.wkBook.SaveAs(this._FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
flag = true;
}
catch (Exception exception6)
{
exception2 = exception6;
flag = false;
MessageBox.Show(exception2.Message);
}
try
{
this.appExcel.Workbooks.Close();
this.appExcel.Quit();
DataIE.KillProgress((IntPtr) this.appExcel.Hwnd);
this.appExcel = null;
}
catch (Exception exception7)
{
exception2 = exception7;
flag = false;
MessageBox.Show(exception2.Message);
}
if (flag)
{
MessageBox.Show("已经成功导出：" + this._FileName);
}
base.Close();
}
}
public int GetCountRow()
{
try
{
m_objExcelApp = new Excel.Application();
System.Globalization.CultureInfo oldCI =
System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("en-US");
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
return m_objExcelWorksheet.UsedRange.Rows.Count;
}
catch (Exception v_e)
{
throw (v_e);
}
}
public override void Quit()
{
base.Quit();
lock (m_lock)
{
if (null == m_hostApplication)
return;
ApplicationControllerTrace trace = new ApplicationControllerTrace("Quit started");
try
{
if (m_hostApplication.Visible)
return;
WaitForExcelToRespond();
m_hostApplication.DisplayAlerts = false;
m_hostApplication.Quit();
trace.Log("Quit complete");
}
catch (COMException e)
{
trace.Log(e);
if (RPC_E_DISCONNECTED != e.ErrorCode)
throw e;
}
finally
{
trace.Log("Releasing application");
if (null != m_hostApplication)
Marshal.FinalReleaseComObject(m_hostApplication);
m_hostApplication = null;
}
}
}
private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
{
switch (e.Tool.Key.ToString())
{
case "Query":
if (!Decision())//判断时间区间//杨滔添加
{
return;
}
{
Query();
break;
}
case "Update":
{
if (this.ultraGrid3.ActiveRow == null || this.ultraGrid3.ActiveRow.Selected == false || this.ultraGrid3.Rows.Count <= 0)
{
MessageBox.Show("请选择一条信息", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (DialogResult.Yes == MessageBox.Show("您确认要修改该条记录吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
{
Update();
Query();
}
break;
}
case "Add":
{
Add();
Query();
break;
}
case "Delete":
{
if (DialogResult.Yes == MessageBox.Show("是否确认要删除当前数据?", "删除提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
{
Delete();
Query();
}
break;
}
case "Print":
{
Print();
Query();
break;
}
case "Print1":
{
Print1();
Query();
break;
}
case "ChooseExcel":
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"C:\桌面\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
txtYBWJ.Text = openFileDialog.FileName;
}
break;
}
case "BatchUpload":
{
//this.ClearData();
if (this.txtYBWJ.Text == "")
{
MessageBox.Show("请选择要导入的预报信息！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.ReadAndSaveExcelInfo();
Query();
this.txtYBWJ.Text = "";
break;
}
default:
break;
}
}
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(filePath));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
_excelSheet.Name = sheetName;
}
public void WritePlantSchedule(IOrderedEnumerable<KeyValuePair<string, MRPlantScheduleData>> data, string savepath)
{
app = StartApplication();
app.Visible = false;
app.UserControl = false;
app.ScreenUpdating = false;
var path = Directory.GetCurrentDirectory();
var wb = app.Workbooks.Open(path + @"\Excel Templates\PlantSchedule.xlsx");
Excel.Worksheet ws = wb.Worksheets["Data"];
var datavalues = data.ToDictionary<KeyValuePair<string, MRPlantScheduleData>,string>(x=>x.Key).Values;
var r = 5;
foreach(var x in datavalues)
{
var fields = x.Value.GetType().GetFields();
for(var i = 0; i < fields.Count<FieldInfo>(); i++)
{
ws.Cells[r, i + 1].Value = fields[i].GetValue(x.Value);
}
r += 1;
}
string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx";
wb.SaveAs(fileName);
wb.Close();
wb = app.Workbooks.Open(fileName);
app.Visible = true;
app.UserControl = true;
app.ScreenUpdating = true;
}
/// <summary>
/// 从EXCEL导入数据
/// </summary>
private void openExcel()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
// openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
}
if (openFileDialog.FileName == "")
{
MessageBox.Show("请选择导入的Excel文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
ReadAndSaveExcelInfo();
}
public void Export2Grid(C1FlexGrid i_fg, int i_iSheetStartRow, int i_iSheetCol, int i_iGridCol)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int v_iGridRow = 0;
for (v_iGridRow = i_fg.Rows.Fixed; v_iGridRow <= i_fg.Rows.Count - 1; v_iGridRow++)
{
i_fg[v_iGridRow, i_iGridCol] = m_objExcelWorksheet.Cells[i_iSheetStartRow + v_iGridRow - i_fg.Rows.Fixed, i_iSheetCol];//.Value
}
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
/// <summary>
/// 把DataTable中的数据导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void DataTableToExcel(System.Data.DataTable srcDt,string savename)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = srcDt;
if (dt == null) return;
string saveFileName = "";
bool fileSaved = false;
/*SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = "导出文件";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < 12; i++)
{
if (dt.Rows[i-2][3].ToString() == "E")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
}
if (dt.Rows[i - 2][3].ToString() == "W")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
}
}
//写入字段
worksheet.Cells[1, 1] = "参数名称";
worksheet.Cells[1, 2] = "参数值";
worksheet.Cells[1, 3] = "推荐值";
worksheet.Cells[1, 4] = "报警值";
worksheet.Cells[1, 5] = "详情";
worksheet.Cells[1, 6] = "参数类型";
worksheet.Cells[1, 7] = "重要级别";
worksheet.Cells[1, 8] = "历史变化";
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
/*string md5 = "";
for (int r = 0; r < 5; r++)
{
for (int i = 0; i < 8; i++)
{
md5 += dt.Rows[r][i].ToString();
worksheet.Cells[2, 9] = md5;
}
}
*/
//worksheet.Cells.Width = 39;
//if (saveFileName != "")
//{
try
{
workbook.Saved = true;
saveFileName = savename;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
MessageBox.Show("导出完成！");
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
//}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
}
/// <summary>
/// 读取并保存Excel中的信息到数据库中
/// </summary>
/// <param name="iOpt">1-101,2-261</param>
/// <param name="sCzdw">导入单位名称</param>
/// <returns>返回错误列表</returns>
private ArrayList ReadAndSaveExcelInfo(int iOpt, string sCzdw)
{
ArrayList lstTmp = new ArrayList();
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
string strTmpTable, strTmpField, strTmpValue;
string[] arrayTmp;
arrayTmp = new string[16];
strTmpTable = "";
strTmpField = "";
strTmpValue = "";
int num = 0;
Excel.Range range = worksheet.get_Range("A2", "P65535");//A2、O65535表示到Excel从第A列的第2行到第O列的第65535-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行
System.Array values = (System.Array)range.Formula;
num = values.GetLength(0);
for (int i = 1; i <= num; i++)
{
if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "")
{
break;
}
else
{
string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName();
string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment();
switch(iOpt)
{
case 1:
arrayTmp[0] = values.GetValue(i, 1).ToString().Trim(); //记帐日期
arrayTmp[1] = values.GetValue(i, 2).ToString().Trim(); //订单号
arrayTmp[2] = values.GetValue(i, 3).ToString().Trim(); //订单行项目号
arrayTmp[3] = values.GetValue(i, 4).ToString().Trim(); //物料编码
//arrayTmp[4] = values.GetValue(i, 5).ToString().Trim(); //物料名称
arrayTmp[5] = values.GetValue(i, 5).ToString().Trim(); //批号
arrayTmp[6] = values.GetValue(i, 6).ToString().Trim(); //收货数量
arrayTmp[7] = values.GetValue(i, 7).ToString().Trim(); //计量单位
arrayTmp[8] = values.GetValue(i, 8).ToString().Trim(); //工厂
arrayTmp[9] = values.GetValue(i, 9).ToString().Trim(); //库存地点
arrayTmp[10] = values.GetValue(i, 10).ToString().Trim(); //抬头文本
strTmpTable = "DT_SAP261";
strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_STOVENO,"
+ "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR";
strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + arrayTmp[0] + "','" + arrayTmp[1] + "','"
+ arrayTmp[2] + "','" + arrayTmp[3] + "','" + arrayTmp[5] + "',"
+ arrayTmp[6] + ",'" + arrayTmp[8] + "','" + arrayTmp[9] + "','" + arrayTmp[10] + "',"
+ "'261','" + sCzdw + "','" + strLRR + "'";
break;
case 2:
arrayTmp[0] = values.GetValue(i, 1).ToString().Trim(); //记帐日期
arrayTmp[1] = values.GetValue(i, 2).ToString().Trim(); //订单号
arrayTmp[2] = values.GetValue(i, 3).ToString().Trim(); //订单行项目号
arrayTmp[3] = values.GetValue(i, 4).ToString().Trim(); //物料编码
//arrayTmp[4] = values.GetValue(i, 5).ToString().Trim(); //物料名称
arrayTmp[5] = values.GetValue(i, 5).ToString().Trim(); //批号
arrayTmp[6] = values.GetValue(i, 6).ToString().Trim(); //收货数量
arrayTmp[7] = values.GetValue(i, 7).ToString().Trim(); //计量单位
arrayTmp[8] = values.GetValue(i, 8).ToString().Trim(); //工厂
arrayTmp[9] = values.GetValue(i, 9).ToString().Trim(); //库存地点
arrayTmp[10] = values.GetValue(i, 10).ToString().Trim(); //抬头文本
strTmpTable = "DT_SAP261";
strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_STOVENO,"
+ "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR";
strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + arrayTmp[0] + "','" + arrayTmp[1] + "','"
+ arrayTmp[2] + "','" + arrayTmp[3] + "','" + arrayTmp[5] + "',"
+ arrayTmp[6] + ",'" + arrayTmp[8] + "','" + arrayTmp[9] + "','" + arrayTmp[10] + "',"
+ "'261','" + sCzdw + "','" + strLRR + "'";
break;
}
CoreClientParam ccp = new CoreClientParam();
ccp.ServerName = "ygjzjl.base.QueryData";
ccp.MethodName = "insertDataInfo";
ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue };
try
{
this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
}
catch (Exception ex1)
{
lstTmp.Add(ex1.Message + "第" + i.ToString() + "行信息导入失败！");
}
}
}
app.Quit();
app = null;
System.Diagnostics.Process[] myProcesses;
myProcesses = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process myProcess in myProcesses)
{
if (myProcess.ProcessName == "EXCEL")
myProcess.Kill();
}
if (lstTmp.Count == 0)
lstTmp.Add("Excel导入成功！");
return lstTmp;
}
public void WriteSalesTaxReport(IList<SalesWithTax> data, string savepath)
{
app = StartApplication();
app.Visible = false;
app.UserControl = false;
app.ScreenUpdating = false;
var path = Directory.GetCurrentDirectory();
var wb = app.Workbooks.Open(path + @"\Excel Templates\SalesWithTax.xlsx");
Excel.Worksheet ws = wb.Worksheets["Source"];
for(var i = 0; i < data.Count; i++)
{
var fields = data[i].GetType().GetFields();
for(var j = 0; j < fields.Count<FieldInfo>(); j++)
{
ws.Cells[i+2, j+1].Value = fields[j].GetValue(data[i]);
}
}
ws = wb.Worksheets["Report"];
ws.Activate();
ws.PivotTables("PivotTable1").PivotCache.Refresh();
ws.PivotTables("PivotTable1").PivotFields("Location").PivotItems("(blank)").Visible = false;
string fileName = savepath + "\\SalesTax_" + Guid.NewGuid().ToString() + ".xlsx";
wb.SaveAs(fileName);
wb.Close();
wb = app.Workbooks.Open(fileName);
app.Visible = true;
app.UserControl = true;
app.ScreenUpdating = true;
}
/// <summary>
/// 执行EXCEL导入
/// </summary>
/// <param name="iOpt">1-101,2-261</param>
/// <param name="sCzdw">导入单位名称</param>
/// <returns>返回错误列表</returns>
public ArrayList ExcelToDatabase(int iOpt, string sCzdw)
{
ArrayList lstTmp = new ArrayList();
OpenFileDialog openFileDialog = new OpenFileDialog();
//openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
}
string strTmpFile = openFileDialog.FileName.ToLower();
if (strTmpFile == "")
{
lstTmp.Add("请选择导入的Excel文件！");
return lstTmp;
}
string strTmp = chkExcute(iOpt, strTmpFile);
if (strTmp != "")
{
lstTmp.Add(strTmp);
return lstTmp;
}
lstTmp = ReadAndSaveExcelInfo(iOpt, sCzdw);
return lstTmp;
}
public void WriteVendorSalesReport(IList<VendorSales> data, string savepath)
{
app = StartApplication();
app.Visible = false;
app.UserControl = false;
app.ScreenUpdating = false;
var path = Directory.GetCurrentDirectory();
var wb = app.Workbooks.Open(path + @"\Excel Templates\VendorSales.xlsx");
Excel.Worksheet ws = wb.Worksheets["Source"];
for (var i = 0; i < data.Count; i++)
{
var fields = data[i].GetType().GetFields();
for (var j = 0; j < fields.Count<FieldInfo>(); j++)
{
ws.Cells[i + 2, j + 1].Value = fields[j].GetValue(data[i]);
}
}
string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx";
wb.SaveAs(fileName);
wb.Close();
wb = app.Workbooks.Open(fileName);
app.Visible = true;
app.UserControl = true;
app.ScreenUpdating = true;
}
private void DataTableToExcel()
{
if (this._TbData != null)
{
this.dBegin = DateTime.Now;
if (this.appExcel == null)
{
this.appExcel = new ApplicationClass();
}
this.wkBook = this.appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
this.wkSheet = (Worksheet) this.wkBook.Worksheets[1];
this.appExcel.Visible = false;
this.dEnd = DateTime.Now;
this.nCols = this._TbData.Columns.Count;
this.nRows = this._TbData.Rows.Count;
this.nCurrR = 1;
this.nCurrC = 1;
if (this._RptTitle != "")
{
this.SetControlText(this.lblState, "正在处理表头......");
this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._RptTitle;
this.range = this.wkSheet.get_Range(this.wkSheet.Cells[this.nCurrR, 1], this.wkSheet.Cells[this.nCurrR, this.nCols]);
this.range.MergeCells = true;
this.range.Font.Bold = true;
this.range.Font.Size = 0x12;
this.range.Font.Name = "宋体";
this.range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
this.range.VerticalAlignment = XlVAlign.xlVAlignCenter;
this.range.EntireRow.AutoFit();
this.nRows++;
this.nCurrR++;
}
this.nRows++;
this.SetProgressMaxMin(this.prgMain, this.nRows, 0);
this.SetProgressValue(this.prgMain, this.nCurrR);
this.SetControlText(this.lblState, "正在处理列头......");
this.nCurrC = 1;
while (this.nCurrC <= this.nCols)
{
this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._TbData.Columns[this.nCurrC - 1].ColumnName;
this.nCurrC++;
}
this.nCurrR++;
this.SetProgressValue(this.prgMain, this.nCurrR);
foreach (DataRow row in this._TbData.Rows)
{
this.nCurrC = 0;
while (this.nCurrC < this.nCols)
{
if (row[this.nCurrC] != null)
{
string str = row[this.nCurrC].ToString().Trim();
double a;
if (str.StartsWith("=") || double.TryParse(str, out a))
{
str = "'" + str;
}
this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = str;
}
else
{
this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = "";
}
this.nCurrC++;
}
this.SetControlText(this.lblState, "正在处理数据......(" + this.nCurrR.ToString() + "/" + this.nRows.ToString() + ")");
this.SetProgressValue(this.prgMain, this.nCurrR);
this.nCurrR++;
}
this.range = null;
this.range = this.wkSheet.get_Range(this.wkSheet.Cells[1, 1], this.wkSheet.Cells[this.nRows, this.nCols]);
this.range.Cells.Borders.LineStyle = XlLineStyle.xlContinuous;
this.range.Borders.Weight = XlBorderWeight.xlThin;
this.range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium;
this.range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;
if (this._FileName.Trim() == "")
{
this._FileName = Path.Combine(System.Windows.Forms.Application.StartupPath, this._TbData.TableName + ".xls");
}
if (this._FileName.ToLower().IndexOf(".xls") <= 0)
{
this._FileName = this._FileName + ".xls";
}
this.wkBook.SaveAs(this._FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
this.appExcel.Workbooks.Close();
this.appExcel.Quit();
DataIE.KillProgress((IntPtr) this.appExcel.Hwnd);
this.appExcel = null;
MessageBox.Show("已经成功导出：" + this._FileName);
base.Close();
}
}
public void WritePlantInventory(string savepath, string datapath)
{
app = StartApplication();
app.Visible = true;
app.UserControl = true;
app.ScreenUpdating = true;
var path = Directory.GetCurrentDirectory();
var wb = app.Workbooks.Open(path + @"\Excel Templates\PlantInventoryTemplate.xlsx");
Excel.Worksheet ws = wb.Worksheets.Add(After:wb.Worksheets[wb.Worksheets.Count]);
ws.Name = "Data";
var qt = ws.QueryTables.Add(String.Format("TEXT;{0}", datapath), ws.Range["A1"]);
qt.FieldNames = true;
qt.RowNumbers = false;
qt.FillAdjacentFormulas = false;
qt.PreserveFormatting = true;
qt.RefreshOnFileOpen = false;
qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
qt.SavePassword = false;
qt.SaveData = true;
qt.AdjustColumnWidth = true;
qt.RefreshPeriod = 0;
qt.TextFilePromptOnRefresh = false;
qt.TextFilePlatform = Excel.XlPlatform.xlWindows;
qt.TextFileStartRow = 1;
qt.TextFileParseType = Excel.XlTextParsingType.xlDelimited;
qt.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
qt.TextFileConsecutiveDelimiter = false;
qt.TextFileTabDelimiter = false;
qt.TextFileSemicolonDelimiter = false;
qt.TextFileCommaDelimiter = true;
qt.TextFileSpaceDelimiter = false;
qt.TextFileColumnDataTypes = new int[] { 1, 1, 2, 1, 1, 1, 1, 1 };
qt.Refresh();
for (var i = 1; i <= wb.Worksheets.Count; i++)
{
wb.Worksheets[i].Activate();
foreach (Excel.PivotTable p in wb.Worksheets[i].PivotTables())
{
p.RefreshTable();
switch (i)
{
case 1:
p.PivotFields("Location").AutoSort(XlSortOrder.xlAscending, "Location");
p.PivotFields("Location").PivotItems("(blank)").Visible = false;
break;
case 2:
p.PivotFields("Location").PivotItems("(blank)").Visible = false;
p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false;
p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
p.PivotFields("Location").PivotItems("Penrose").Visible = false;
p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false;
p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false;
p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false;
p.PivotFields("Location").ShowDetail = false;
break;
case 3:
p.PivotFields("Location").PivotItems("(blank)").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
p.PivotFields("Location").PivotItems("Penrose").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false;
p.PivotFields("Phase").PivotItems("(blank)").Visible = false;
p.PivotFields("Phase").PivotItems("Clone").Visible = false;
p.PivotFields("Phase").PivotItems("Harvesting").Visible = false;
p.PivotFields("Phase").PivotItems("Germination").Visible = false;
p.PivotFields("Room").ShowDetail = false;
break;
case 4:
switch (p.Name)
{
case "PivotTable2":
p.PivotFields("Location").PivotItems("(blank)").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = false;
p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
p.PivotFields("Location").PivotItems("Penrose").Visible = false;
p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false;
p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false;
p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false;
break;
//case "PivotTable11":
// p.PivotFields("Location").PivotItems("(blank)").Visible = false;
// p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible
// = false;
// p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false;
// p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
// p.PivotFields("Location").PivotItems("Penrose").Visible = false;
// p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false;
// p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false;
// p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false;
// p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false;
// p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false;
// p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false;
// p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false;
// p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible =
// false;
// break;
//case "PivotTable12":
// p.PivotFields("Location").PivotItems("(blank)").Visible = false;
// p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible
// = false;
// p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false;
// p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
// p.PivotFields("Location").PivotItems("Penrose").Visible = false;
// p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false;
// p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible =
// false;
// p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false;
// p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false;
// p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false;
// p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false;
// p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false;
// p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false;
// p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible =
// false;
// break;
case "PivotTable13":
p.PivotFields("Location").PivotItems("(blank)").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible
= false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible =
false;
p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible =
false;
p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false;
p.PivotFields("Location").PivotItems("Penrose").Visible = false;
p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible =
false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false;
p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false;
p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false;
p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false;
p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false;
p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible =
false;
break;
}
break;
}
}
}
wb.Worksheets[1].Activate();
string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx";
wb.SaveAs(fileName);
wb.Close();
wb = app.Workbooks.Open(fileName);
app.Visible = true;
app.UserControl = true;
app.ScreenUpdating = true;
}
public void OpenExcelApp(bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}
private void Unmount()
{
m_objExcelWorksheet = null;
m_objExcelApp = null;
}
