Excel文档内容到webservice [英] Excel doc contents to webservice
问题描述
客户端：
private void CreateStaffMember_Click发件人，RoutedEventArgs e）
{
string uri =http：// localhost：8001 / Service / Staff;
StringBuilder sb = new StringBuilder（）;
sb.Append（< Staff>）;
sb.AppendLine（< FirstName>+ this.textBox1.Text +< / FirstName>）;
sb.AppendLine（< LastName>+ this.textBox2.Text +< / LastName>）;
sb.AppendLine（< Password>+ this.passwordBox1.Password +< / Password>）;
sb.AppendLine（< / Staff>）;
string NewStudent = sb.ToString（）;
byte [] arr = Encoding.UTF8.GetBytes（NewStudent）;
HttpWebRequest req =（HttpWebRequest）WebRequest.Create（uri）;
req.Method =POST;
req.ContentType =application / xml;
req.ContentLength = arr.Length;
Stream reqStrm = req.GetRequestStream（）;
reqStrm.Write（arr，0，arr.Length）;
reqStrm.Close（）;
HttpWebResponse resp =（HttpWebResponse）req.GetResponse（）;
MessageBox.Show（Staff Creation：Status+ resp.StatusDescription）;
reqStrm.Close（）;
resp.Close（）;
}
Web服务端：
#region POST
[OperationContract]
[WebInvoke（Method =POST，BodyStyle = WebMessageBodyStyle.Bare，RequestFormat = WebMessageFormat.Xml ，ResponseFormat = WebMessageFormat.Xml，UriTemplate =/ Staff）]
void AddStaff（Staff staff）;
#endregion
public void AddStaff（员工）
{
staff.StaffID =（++ eCount）.ToString（）;
staff.Salt = GenerateSalt（）;
byte [] passwordHash = Hash（staff.Password，staff.Salt）;
staff.Password = Convert.ToBase64String（passwordHash）;
staffmembers.Add（staff）;
}
所有罚款都在这边，但我希望导入员工的详细信息从excel电子表格中，不知道导入是否是正确的单词，但是我想要使用这样的电子表格中包含的名字和姓氏，并将它们从客户端wpf应用程序添加到Web服务中。
我该怎么办？我有我的打开文件对话框：
private void Import_Click（object sender，RoutedEventArgs e）
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog（）;
//显示打开的文件对话框
Nullable< bool> result = dlg.ShowDialog（）;
//进程打开文件对话框结果
if（result == true）
{
//打开文档
string filename = dlg.FileName ;
}
}
所以我打开我的excel电子表格然后我将如何去内部的内容并发送到Web服务？真的被困在代码上或如何去做：/
只需寻找一种自动添加员工的方式，而不是手动输入名称，但看到员工excel doc可以命名任何我想要的打开文件对话框。内部的结构将始终与名字相同。
首先，这里是我的测试Excel文件，其中包含您要导入的员工：
（列'A'如果是名字，列'B'是姓氏，列'C'是密码...）
好的，假设您的代码调用您的Web服务工作，这里是我的Import_Click方法的版本（以及保存新员工的通用方法）：
private void Import_Click（object sender，RoutedEventArgs e）
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog（）;
//显示打开的文件对话框
Nullable< bool> result = dlg.ShowDialog（）;
//进程打开文件对话框结果
if（result == true）
{
//打开文档
string filename = dlg.FileName ;
Microsoft.Office.Interop.Excel.Application vExcelObj = new Microsoft.Office.Interop.Excel.Application（）;
尝试
{
工作簿theWorkbook = vExcelObj.Workbooks.Open（filename，Type.Missing，true）;
工作表sheet = theWorkbook.Worksheets [1]; //这是假设工作人员列表在第一个工作表中
string vFirstName =temp;
string vLastName =temp;
string vPassword =temp;
int vIndex = 1;
while（vFirstName！=）
{
//在这里更改相应列的字母！
//在我的例子中，'A'是名字，'B'是姓氏，'C'是密码
vFirstName = sheet.get_Range（A+ vIndex.ToString（）） .Value.ToString（）;
vLastName = sheet.get_Range（B+ vIndex.ToString（））。Value.ToString（）;
vPassword = sheet.get_Range（C+ vIndex.ToString（））。Value.ToString（）;
this.SaveNewStaff（vFirstName，vLastName，vPassword）;
vIndex ++;
}
}
catch（Exception ex）
{
MessageBox.Show（处理excel文件时出错+ ex.Message）;
}
finally {
vExcelObj.Quit（）;
}
}
}
private void SaveNewStaff（string firstName，string lastName，string password）{
string uri =http：// localhost ：8001 /服务/工作人员;
StringBuilder sb = new StringBuilder（）;
sb.Append（< Staff>）;
sb.AppendLine（< FirstName>+ firstName +< / FirstName>）;
sb.AppendLine（< LastName>+ lastName +< / LastName>）;
sb.AppendLine（< Password>+ password +< / Password>）;
sb.AppendLine（< / Staff>）;
string NewStudent = sb.ToString（）;
byte [] arr = Encoding.UTF8.GetBytes（NewStudent）;
HttpWebRequest req =（HttpWebRequest）WebRequest.Create（uri）;
req.Method =POST;
req.ContentType =application / xml;
req.ContentLength = arr.Length;
Stream reqStrm = req.GetRequestStream（）;
reqStrm.Write（arr，0，arr.Length）;
reqStrm.Close（）;
HttpWebResponse resp =（HttpWebResponse）req.GetResponse（）;
//MessageBox.Show(\"Staff Creation：Status+ resp.StatusDescription）;
reqStrm.Close（）;
resp.Close（）;
}
注意：我已经在调用Web服务时清除了MessageBox如果列表很长，请确保您不会感到烦恼，但是如果您需要确认每个员工的创建，您可以自由地unREM。在教导的同一行中，没有验证创建是否成功发生。我需要更多细节来创建一个体面的验证过程。
另外非常重要的是，如果您正在保存的工作人员已经存在于列表中，则不会验证。如果您多次重新运行此导入过程，它可能（并且可能会）创建重复的条目。
干杯
I have a wpf staff creation window in which I can create basic information like first name, last name etc this creates the staff in my REST web service. An example:
Client side:
private void CreateStaffMember_Click(object sender, RoutedEventArgs e)
{
string uri = "http://localhost:8001/Service/Staff";
StringBuilder sb = new StringBuilder();
sb.Append("<Staff>");
sb.AppendLine("<FirstName>" + this.textBox1.Text + "</FirstName>");
sb.AppendLine("<LastName>" + this.textBox2.Text + "</LastName>");
sb.AppendLine("<Password>" + this.passwordBox1.Password + "</Password>");
sb.AppendLine("</Staff>");
string NewStudent = sb.ToString();
byte[] arr = Encoding.UTF8.GetBytes(NewStudent);
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
req.Method = "POST";
req.ContentType = "application/xml";
req.ContentLength = arr.Length;
Stream reqStrm = req.GetRequestStream();
reqStrm.Write(arr, 0, arr.Length);
reqStrm.Close();
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
MessageBox.Show("Staff Creation: Status " + resp.StatusDescription);
reqStrm.Close();
resp.Close();
}
Web Service side:
#region POST
[OperationContract]
[WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.Bare, RequestFormat = WebMessageFormat.Xml, ResponseFormat = WebMessageFormat.Xml, UriTemplate = "/Staff")]
void AddStaff(Staff staff);
#endregion
public void AddStaff(Staff staff)
{
staff.StaffID = (++eCount).ToString();
staff.Salt = GenerateSalt();
byte[] passwordHash = Hash(staff.Password, staff.Salt);
staff.Password = Convert.ToBase64String(passwordHash);
staffmembers.Add(staff);
}
All fine on that side, but Im looking to "import" the staff details from an excel spreadsheet, not sure if import is the correct word but I want to take the first names and last names contained in such n such spreadsheet and add them to the web service from the client side wpf application.
How would I go about it? I have my open file dialog:
private void Import_Click(object sender, RoutedEventArgs e)
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
// Show open file dialog box
Nullable<bool> result = dlg.ShowDialog();
// Process open file dialog box results
if (result == true)
{
// Open document
string filename = dlg.FileName;
}
}
So I open my excel spread sheet then how would I go about taking the inner contents and sending it to the web service? Really stuck on the code or how to go about it :/
Just looking for an automated way of adding staff members rather than manually typing the names, but seeing as the staff excel doc could be named anything I wanted the open file dialog box. The structure inside will always be the same first name then last name.
First, here is my test Excel file that contains the Staff you want to import:
(Column 'A' if first name, column 'B' is last name and column 'C' is the password...)
Ok, so assuming that your code calling your web service works, here is my version of the Import_Click method (and a generic method to save new staff):
private void Import_Click(object sender, RoutedEventArgs e)
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
// Show open file dialog box
Nullable<bool> result = dlg.ShowDialog();
// Process open file dialog box results
if (result == true)
{
// Open document
string filename = dlg.FileName;
Microsoft.Office.Interop.Excel.Application vExcelObj = new Microsoft.Office.Interop.Excel.Application();
try
{
Workbook theWorkbook = vExcelObj.Workbooks.Open(filename, Type.Missing, true);
Worksheet sheet = theWorkbook.Worksheets[1]; // This is assuming that the list of staff is in the first worksheet
string vFirstName = "temp";
string vLastName = "temp";
string vPassword = "temp";
int vIndex = 1;
while (vFirstName != "")
{
// Change the letters of the appropriate columns here!
// In my example, 'A' is first name, 'B' is last name and 'C' is the password
vFirstName = sheet.get_Range("A" + vIndex.ToString()).Value.ToString();
vLastName = sheet.get_Range("B" + vIndex.ToString()).Value.ToString();
vPassword = sheet.get_Range("C" + vIndex.ToString()).Value.ToString();
this.SaveNewStaff(vFirstName, vLastName, vPassword);
vIndex++;
}
}
catch (Exception ex)
{
MessageBox.Show("Error processing excel file : " + ex.Message);
}
finally {
vExcelObj.Quit();
}
}
}
private void SaveNewStaff(string firstName, string lastName, string password) {
string uri = "http://localhost:8001/Service/Staff";
StringBuilder sb = new StringBuilder();
sb.Append("<Staff>");
sb.AppendLine("<FirstName>" + firstName + "</FirstName>");
sb.AppendLine("<LastName>" + lastName + "</LastName>");
sb.AppendLine("<Password>" + password + "</Password>");
sb.AppendLine("</Staff>");
string NewStudent = sb.ToString();
byte[] arr = Encoding.UTF8.GetBytes(NewStudent);
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
req.Method = "POST";
req.ContentType = "application/xml";
req.ContentLength = arr.Length;
Stream reqStrm = req.GetRequestStream();
reqStrm.Write(arr, 0, arr.Length);
reqStrm.Close();
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
//MessageBox.Show("Staff Creation: Status " + resp.StatusDescription);
reqStrm.Close();
resp.Close();
}
Note: I have REMed out the MessageBox in the call to the web service to make sure you are not annoyed by it if the list is long, but you are free to "unREM" it if you need confirmation for every staff creation. In the same line of taught, there is not validation that the creation has occurred successfully. I would need more details to create a decent validation process. Also VERY important, this does not validate if the staff you are saving already exists in the list. If you re-run this import procedure multiple times, it may (and probably will) create duplicate entries.
Cheers
这篇关于Excel文档内容到webservice的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
