菜鸟求助 VBA转C# 急急急

yc19901231 2017-08-22 02:56:30

公司服务器做迁移， server1迁移到server2，老服务器上的vba程序需要部署到server2上去，但是出错了，现在需要把以前的vba程序用c#来写！！！

具体的代码如下：
Private Sub Workbook_Open()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, row As Integer
Dim connstr As String
Dim ws As Worksheet
Dim rg As Range
Dim dt As Date
Dim scht As String
Dim schh As String
Dim sqlstr As String
Dim mem_list As String
Dim topic As String
Dim attach As String
Dim mypath As String
dt = Date + 1
scht = Year(dt) & Right("0" & Month(dt), 2) & Right("0" & Day(dt), 2)

'RUN_TIME OPER SCH_TIME CUST DEPARTMENT LOT_ID LOADING update_time

sqlstr = "SELECT '" + Right("0" & Hour(Time), 2) + ":30'as Run_time,a.oper,b.cust_device, a.create_cmf2, b.department, a.lot_id, a.qty1,sysdate "
sqlstr = sqlstr + " FROM wiplot a , wip_lotinf b"
sqlstr = sqlstr + " WHERE a.factory = 'ASSY' AND b.factory='ASSY'"
sqlstr = sqlstr + " AND b.test_flag IN ('AO', 'AT') AND a.lot_id<>'NHU3060.4JWA000'"
sqlstr = sqlstr + " AND b.department in( 'LDP','SIP','BALL ARRAY')"
sqlstr = sqlstr + " AND a.delete_flag = ' ' AND a.delete_flag = b.delete_flag AND a.factory=b.factory"
sqlstr = sqlstr + " AND a.qty1 > 0"
sqlstr = sqlstr + " AND a.lot_id = b.lot_id"
sqlstr = sqlstr + " AND b.eqa_sample_lot_cat = ' '"
sqlstr = sqlstr + " AND b.sch_time='" + scht + "' "
'Set ws = ThisWorkbook.Worksheets("Detail")
mypath = "F:\DP_E_Planning\DP_LOADING_TRACK.xls" '把路径赋值给一个字符串
Workbooks.Open Filename:=mypath '这句是打开文件
Set ws = ActiveWorkbook.Worksheets("Detail")
If Hour(Time) = 10 Then
ws.Range("A2:J65535") = ""
row = 1
Else
row = ws.Range("A65535").End(xlUp).row
End If
connstr = "Provider=MSDAORA.1;Data Source=MESRPT;User Id=SCC_MDB;Password=cbs#0490;Persist Security Info=True"
conn.Open (connstr)
rs.Open sqlstr, conn
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
ws.Cells(row + 1, i + 1) = rs.Fields(i).Value
Next
ws.Cells(row + 1, 9) = rs.Fields(0).Value + rs.Fields(4).Value
row = row + 1
rs.MoveNext
Loop
row = 0
rs.Close
conn.Close
ActiveWorkbook.Save
If Hour(Time) = 21 Then
If Dir("\\Sccfilesvr\sccsharefile\Groups&Users\Public\SHIWENWU\DP Daily Loading\") = "" Then
MkDir "\\Sccfilesvr\sccsharefile\Groups&Users\Public\SHIWENWU\DP Daily Loading\"
End If
ActiveWorkbook.SaveAs Filename:="\\Sccfilesvr\sccsharefile\Groups&Users\Public\SHIWENWU\DP Daily Loading\" & Format(Date, "YYYY-MM-DD") & ".xls"
End If
With ActiveWorkbook.Worksheets("Summary")
.Range("A1:O28").CopyPicture
.Paste
For i = 1 To .Shapes.Count
Set shap = .Shapes(i)
shap.Copy
With .ChartObjects.Add(0, 0, shap.Width, shap.Height).Chart
.Paste
.Export "F:\DP_E_Planning\DP.jpg"
.Parent.Delete
End With
Next
End With
ActiveWorkbook.Close False
mem_list = "hao.gong@statschippac.com;chuntao.xie@statschippac.com;@fang.wu@statschippac.com;chunyan.lu@statschippac.com;yincao.zhu@statschippac.com;shen.yan@statschippac.com;Luhui.cai@statschippac.com;Genghong.zhao@statschippac.com;LiuPing.chen@statschippac.com;Jian.feng@statschippac.com;Emma.he@statschippac.com;Ping.jiang@statschippac.com;LiFang.chen@statschippac.com;JingHui.yu@statschippac.com;MeiLing.shen@statschippac.com;XiaoLi.huang@statschippac.com;ChaoJun.di@statschippac.com;Hui.zou@statschippac.com;XiangMei.fei@statschippac.com;Jessie.wu@statschippac.com;Elisha.wang@statschippac.com;RenJun.jin@statschippac.com;Thomas.xue@statschippac.com;ZhiFang.jiang@statschippac.com;JingPing.zhu@statschippac.com;Lihong.lu@statschippac.com;tian.tian@statschippac.com;huakai.xie@statschippac.com"
'Luhui.cai@statschippac.com;Genghong.zhao@statschippac.com;LiuPing.chen@statschippac.com;Elsa.zhang@statschippac.com;Jian.feng@statschippac.com;Emma.he@statschippac.com;Ping.jiang@statschippac.com;LiFang.chen@statschippac.com;JingHui.yu@statschippac.com;MeiLing.shen@statschippac.com;XiaoLi.huang@statschippac.com;ChaoJun.di@statschippac.com;Bao.han@statschippac.com;Lily.sun@statschippac.com;Hui.zou@statschippac.com;XiangMei.fei@statschippac.com;Jessie.wu@statschippac.com;Elisha.wang@statschippac.com;RenJun.jin@statschippac.com;WenWu.shi@statschippac.com;Thomas.xue@statschippac.com;ZhiFang.jiang@statschippac.com;JingPing.zhu@statschippac.com
'mem_list = "tian.tian@statschippac.com"
topic = "DP daily loading information_" & scht
body = "Hi all, "
body = body + " DP Schedule loading information,for your reference! "

body = body + "<html><img src='image/DP_pic'></html> " 'RangetoHTML(Sheet1.Range("A1:O10"))
body = body + "Thanks & Regards! Hao Gong "

attach = "F:\DP_E_Planning\DP_LOADING_TRACK.xls"
JmailSend topic, body, mem_list, attach
Application.Quit
End Sub
求大神帮忙啊

...全文

1627 30 打赏收藏转发到动态举报

写回复

用AI写文章

30 条回复

切换为时间正序

请发表友善的回复…

发表回复

xhk008 2017-09-07

打赏
举报

protected void DataLoad() { uUserId = han_uinfo.getUserSession(); if (!string.IsNullOrEmpty(uUserId)) { Musers _users = han_uinfo.getInfoByPrimaryKey(Int32.Parse(uUserId)); if (_users != null) { Han_Popedompgroup han_infogp = new Han_Popedompgroup(); Popedompgroup _infogp = han_infogp.getInfoByPrimaryKey(_users.Adminpopedomgid); if (_infogp != null) { switch (_infogp.Title) { case "MH": this.Panel_Leader.Visible = true; break; case "Finance": this.Panel_Supervisor.Visible = true; _zeroflg++; break; case "View": _zeroflg += 2; break; case "Administrators": this.Panel_Leader.Visible = true; this.Panel_Supervisor.Visible = true; break; default: break; } } } } if (_zeroflg == notchecked) { utl.RedirectHelper("Data-Input.aspx", "_self", ""); } else { if (utl.IsEven(_zeroflg)) { utl.RedirectHelper("Data-Report.aspx", "_self", ""); } utl.RedirectHelper("Data-Check.aspx", "_self", ""); } this.TextBox_DateTime.Text = DateTime.Now.ToString(); ICriterion icrit = Expression.Eq("Ischecked", ischecked.ToString()); Order[] orders = new Order[] { Order.Asc("Ordernum"), Order.Asc("Modifydate") }; searchType = "Current Shift"; dpn.bindDropDownListDic(searchType, DropDownList_CurrentShift); searchType = "Shift"; dpn.bindDropDownListDic(searchType, DropDownList_Shift); Han_EworkingIteminfoclass han_infoclass = new Han_EworkingIteminfoclass(); if (Panel_Leader.Visible) { bindCotrl(uUserId, "Leader"); icrit = Expression.And(icrit, han_infoclass.getChildrenIdICriterionByFatherInfo("Title", "Leader", true)); } if (Panel_Supervisor.Visible) { bindCotrl(uUserId, "Supervisor"); icrit = Expression.And(icrit, han_infoclass.getChildrenIdICriterionByFatherInfo("Title", "Supervisor", true)); } }

xdashewan 2017-08-25

打赏
举报

引用 22 楼 yc19901231 的回复:

这段怎么翻译啊

c#没有With语法，所以要定义一个指向ActiveWorkbook.Worksheets("Summary")的变量，然后使用该变量调用方法

yc19901231 2017-08-25

打赏
举报

引用 21 楼 xdashewan 的回复:

[quote=引用 20 楼 yc19901231 的回复:] row = ws.Range("A65535").End(xlUp).row 这句怎么翻译啊， vba的意思我懂了，是A65535到鼠标往上非空的行数，在C#里面要怎么写啊？

照抄[/quote] With ActiveWorkbook.Worksheets("Summary") .Range("A1:O28").CopyPicture .Paste For i = 1 To .Shapes.Count Set shap = .Shapes(i) shap.Copy With .ChartObjects.Add(0, 0, shap.Width, shap.Height).Chart .Paste .Export "F:\DP_E_Planning\DP.jpg" .Parent.Delete End With Next End With 这段怎么翻译啊

xdashewan 2017-08-25

打赏
举报

引用 20 楼 yc19901231 的回复:

row = ws.Range("A65535").End(xlUp).row 这句怎么翻译啊， vba的意思我懂了，是A65535到鼠标往上非空的行数，在C#里面要怎么写啊？

照抄

yc19901231 2017-08-25

打赏
举报

引用 19 楼 xdashewan 的回复:

[quote=引用 18 楼 yc19901231 的回复:] Application app = new Application();这个类看不到他写的什么啊，调用不了啊

Microsoft.Office.Interop.Excel.Application[/quote] row = ws.Range("A65535").End(xlUp).row 这句怎么翻译啊， vba的意思我懂了，是A65535到鼠标往上非空的行数，在C#里面要怎么写啊？

xdashewan 2017-08-25

打赏
举报

引用 18 楼 yc19901231 的回复:

Application app = new Application();这个类看不到他写的什么啊，调用不了啊

Microsoft.Office.Interop.Excel.Application

yc19901231 2017-08-25

打赏
举报

引用 4 楼 xdashewan 的回复:

参照c#操作excel，http://www.cnblogs.com/wang_yb/articles/1750419.html

Application app = new Application();这个类看不到他写的什么啊，调用不了啊

yc19901231 2017-08-25

打赏
举报

引用 16 楼 qq_24112277 的回复:

http://www.cnblogs.com/powertoolsteam/p/3208036.html 葡萄城的

这个是vb转cs啊。有vba转c#的嘛？

xdashewan 2017-08-25

打赏
举报

引用 28 楼 yc19901231 的回复:

这句vba是什么意思啊？

for循环copy所有图形

yc19901231 2017-08-25

打赏
举报

引用 27 楼 xdashewan 的回复:

[quote=引用 26 楼 yc19901231 的回复:] 那段代码不是复制（"A1:O28“）这个范围的excel的值然后黏贴吗？

对啊，那你翻译呗，定变量和你实现这功能又没有冲突[/quote]、那句已经写出来了， For i = 1 To .Shapes.Count Set shap = .Shapes(i) shap.Copy 这句vba是什么意思啊？

xdashewan 2017-08-25

打赏
举报

引用 26 楼 yc19901231 的回复:

那段代码不是复制（"A1:O28“）这个范围的excel的值然后黏贴吗？

对啊，那你翻译呗，定变量和你实现这功能又没有冲突

yc19901231 2017-08-25

打赏
举报

引用 25 楼 xdashewan 的回复:

[quote=引用 24 楼 yc19901231 的回复:] 是复制图片啊，需要定义变量吗？

复制图片是功能和你翻译代码没什么直接关系吧？[/quote] 那段代码不是复制（"A1:O28“）这个范围的excel的值然后黏贴吗？

xdashewan 2017-08-25

打赏
举报

引用 24 楼 yc19901231 的回复:

是复制图片啊，需要定义变量吗？

复制图片是功能和你翻译代码没什么直接关系吧？

yc19901231 2017-08-25

打赏
举报

引用 23 楼 xdashewan 的回复:

[quote=引用 22 楼 yc19901231 的回复:] 这段怎么翻译啊

c#没有With语法，所以要定义一个指向ActiveWorkbook.Worksheets("Summary")的变量，然后使用该变量调用方法[/quote] 是复制图片啊，需要定义变量吗？

im战术 2017-08-24

打赏
举报


if (DateAndTime.Hour(System.Convert.ToDateTime(Time)) == 10)
{
	ws.Range["A2:J65535"] = "";
	row = 1;
}
else
{
	row = ws.Range("A65535").End(xlUp).row;
}
connstr = "Provider=MSDAORA.1;Data Source=MESRPT;User Id=SCC_MDB;Password=cbs#0490;Persist Security Info=True";
conn.Open(connstr);
rs.Open (sqlstr, conn);
while (!rs.EOF)
{
	for (i = 0; i <= rs.Fields.Count - 1; i++)
	{
		ws.Cells[row + 1, i + 1] = rs.Fields(i).Value;
	}
	ws.Cells[row + 1, 9] = rs.Fields(0).Value + rs.Fields(4).Value;
	row = row + 1;
	rs.MoveNext;
}
row = 0;
rs.Close;
conn.Close;
ActiveWorkbook.Save;

这是我用软件直翻的,好像你这里面并没有什么难点呀?

智者知已应修善业 2017-08-24

打赏
举报

引用 12 楼 yc19901231 的回复:

[quote=引用 11 楼 xianfajushi 的回复:] 逻辑部分参看原代码肯定的了,要看懂,或询问.别人怎么猜测?

逻辑那部分能看懂啊，但是用C#来写我不会啊要怎么写啊？[/quote] 查微软文档例子

yc19901231 2017-08-24

打赏
举报

引用 11 楼 xianfajushi 的回复:

逻辑部分参看原代码肯定的了,要看懂,或询问.别人怎么猜测?

逻辑那部分能看懂啊，但是用C#来写我不会啊要怎么写啊？

智者知已应修善业 2017-08-24

打赏
举报

逻辑部分参看原代码肯定的了,要看懂,或询问.别人怎么猜测?

yc19901231 2017-08-24

打赏
举报

引用 9 楼 xianfajushi 的回复:

[quote=引用 8 楼 yc19901231 的回复:] 对啊我们领导就是让我参照着这个vba用c#重写, 需要怎么写啊？

凡事从易入手则易成功. 自然是从第一行代码开始,即从写变量开始,如: //Dim conn As New ADODB.Connection var conn = New ADODB.Connection //Dim rs As New ADODB.Recordset var rs = New ADODB.Recordset // Dim i, row As Integer Integer i = 0, row = 0; String connstr ="", scht ="", schh ="", sqlstr ="", mem_list ="", topic ="", attach ="", mypath =""; var dt = Date + 1;//这句大概是一个日期变量，不知道哪里定义的，所以说别人是改不了的了 scht = Year(dt) & Right("0" & Month(dt), 2) & Right("0" & Day(dt), 2) 上句大概可以看出，调用函数合成年月日。[/quote] using System; using System.IO; using System.Data; using System.Text; using System.Data.OleDb; using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.HSSF.Util; using NPOI.SS.UserModel; namespace DP_E_Planning { class NPOIExcelwork { static HSSFWorkbook hssfworkbook; string scht = DateTime.Now.AddDays(1).ToString("yyyyMMdd"); int hour = DateTime.Now.Hour; public DataTable DB() { DataTable dt = new DataTable(); string connstr = "Provider=MSDAORA.1;Data Source=MESRPT;User Id=SCC_MDB;Password=cbs#0490;"; using (OleDbConnection oleConnp = new OleDbConnection(connstr)) { string SQL = @"SELECT '" + hour + @":30' as Run_time,a.oper,b.cust_device, a.create_cmf2, b.department, a.lot_id, a.qty1,sysdate FROM wiplot a , wip_lotinf b WHERE a.factory = 'ASSY' AND b.factory='ASSY' AND b.test_flag IN ('AO', 'AT') AND a.lot_id<>'NHU3060.4JWA000' AND b.department in( 'LDP','SIP','BALL ARRAY') AND a.delete_flag = ' ' AND a.delete_flag = b.delete_flag AND a.factory=b.factory AND a.qty1 > 0 AND a.lot_id = b.lot_id AND b.eqa_sample_lot_cat = ' ' AND b.sch_time='" + scht +"'"; OleDbDataAdapter oda = new OleDbDataAdapter(SQL, oleConnp); oda.Fill(dt); return dt; } } public void excelwork() { string mypath = @"E:\DP_E_Planning\DP_E_Planning\DP_LOADING_TRACK.xls"; string time = DateTime.Now.ToString("yyyyMMdd"); string SaveFilename = @"\\Sccfilesvr\sccsharefile\Groups&Users\Public\SHIWENWU\DP Daily Loading\" + time + ".xls"; #region//取得模板 // if (File.Exists(mypath) == true) if (hour==10) { FileStream file = new FileStream(mypath, FileMode.Open); //FileAccess.Read //hssfworkbook = new HSSFWorkbook(file); 创建excel文件 file.Close(); } else { return; } #endregion #region//建立Sheet，可以多个 ISheet sheet = hssfworkbook.GetSheetAt(0);//建立Sheet sheet.ForceFormulaRecalculation = true;//保持Sheet公式 #endregion #region//填值部分 DataTable DT = DB(); if (DT.Rows.Count > 0) { for (int i = 0; i < DT.Rows.Count; i++) { sheet.CreateRow(i + 1); for (int j = 0; j < DT.Columns.Count; j++) { judge(DT.Rows[i][j], i + 1, j, sheet); } } DT.Dispose(); } else { return; } #endregion #region//文件输出 try { FileStream FileTo = new FileStream(SaveFilename, FileMode.Create); hssfworkbook.Write(FileTo); FileTo.Close(); } catch { return; } #endregion } static void judge(object DT, int i, int j, ISheet sheet1) { sheet1.GetRow(i).CreateCell(j).SetCellValue(DT.ToString()); } } } 我已经写了这么多了，中间的逻辑不知道代码怎么写

智者知已应修善业 2017-08-24